Data fetch

Hi,

I'm using a remote SQL Server Express database with a C# app, and to do so as most of you already know, there's no DataSource available, it's all around SQL. This poses a problem as when I want to browse (1 by 1 in my app, with search utility) the contents of a given table, I have to perform a Select command. Well the problem is when I do this it loads all records into a DataSet (or DataTable), which is fine by me, but one of my tables is expected to reach 400 or 500 records in a few months time. This will mean a lot of loading from the db once the app is launched. Is there a way to make this connection more efective? Thanks

hi,

yes, it's possible... you just have to change your mind about how you fetch and work with data... do no longer "think" in terms of "tables" like in older JET paradgm where you opened a table and navigate via seek methods to the required "record", but "think" in terms of compacted recordsets.. thus filter the data you really need both in horizzontal and vertical terms..

in horizzontal term, returns only the actual columns you need, so provide the restricted column list of the projection you want (do not write SELECT * FROM but SELECT colX, colY, colZ FROM)..

in vertical term, provide a filter condition to the FROM clause in order to return just the "bunch" of rows you are interested with, so write

SELECT <col_list> FROM dbo.Customers WHERE CustomerID = theCustomerYouAreLookingFor

instead of

SELECT * FROM dbo.Customers

and the navigate to the customer row via client side Ado.Net/ADO/whatever seek methods..

regards

Data Fetch

Hello..

i develope a web projects of horoscope or astrology(http://demo.reallianzbussimart.com/allzodiac.aspx), there is an 12 Zodiaz sign and all the data call on this page through the Database , in this page i there is an one sql query -- (Select Top 1 col from colour order by newid(),Select Top 1 num from number order by newid() ,Select Top 1 days from day order by newid() )

then all data call one by one change ..

when the refresh the page the value of all zodiac is change that is wrong ,,,

so what is the quary that one time in day the value off all zodiac is same next day the value has been change.................................

so please help me /.........

tell what is the process to not data change into the page refresh

Ashwnai

Hi Ashwnai,

You do the databinding in the load event of the page right?

You should do the databinding inside a "Not IsPostBack" like:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf Not IsPostBackThen'Do your databinding hereEnd If End Sub

I hope this answers your question ...
Otherwise please reply!

Kind regards,
Wim

|||

This is not solution of my problem

what is databinding , i am using the datagrid in the pages

so please help me

http://demo.reallianzbussimart.com/allzodiac.aspx

when u refresh the page all the zodiac value also change , but this is wrong

Ashwani

|||

ashwanikumar:

select top 1 days from dayorder by newid()

the emphasized generates random number(guid to be exact), so everytime the page is refreshed the order is random.

hth

|||

Ashwani,here are your queries

Select Top 1 col from colour order by newid()

Select Top 1 num from number order by newid()

Select Top 1 days from day order by newid()

if you are keep calling to storeprocedure / query by refreshing it. IT will give you an different result on every refresh click.

Remove order by NewID() because everytime it will generate GUID random ID.

Select Top 1 col from colour

Select Top 1 num from number

Select Top 1 days from day

Please send me the table with data atleast with three rows.

Then i can give you better solution.

DB Master

http://codeproject.wordpress.com

Data access From sqlserver 2000

Hello

i have one prob . i m using sql server 2000 . and i have write a store procedure to fetch data from multiple table using cursor . this query exculate in sql server . but i can't fetch this data from in our page . how to fetch data from using multiple table .plz help

thnx

Hi shruram.gore,

You don't need to use cursor for this unless you need to do some operations (processess) before displaying the data.
Cursor will not help you in displying the data, to display the data use SELECT and if you need to fetch the data from more than one table, use JOIN.

Example:

SELECT a.col1, a.col2, b.col2FROM table1 aINNERJOIN table2 bon (a.id = b.id)

Good luck.

|||

thnx , for suggestion

But dont have fix table name . in my prob my table name is variable plz check the query . this query we will use to bind treeView to show parent child releation . i have execute data from this query but we cant fetch data in dataset

thanx in advance

CREATE PROCEDURE [dbo].GetRootChild
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
declare @.parentId int
declare @.TableName varchar (50)
declare @.RelIDName char(10),@.s int,@.sql varchar(4000)

declare comments_list CURSOR FOR
select distinct [PcRelation].[ChID],[ObjectType].[ObjName],[ObjectType].[RelIdName] from PcRelation,ObjectType where [PcRelation].[ChObjectID]=[ObjectType].[ID] and [PcRelation].[PID]='0' and [PcRelation].[PObjectID]='0'
OPEN comments_list
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE product_cursor CURSOR FOR
select @.TableName as TableName
--select * from Company
select @.sql ='select * from [dbo].['+ @.TableName +'] where ['+ @.TableName +'].['+@.RelIDName+']= '+convert(char(10),@.parentId)+''
OPEN product_cursor
FETCH NEXT FROM product_cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
--select @.TableName as TableName
exec(@.sql)
FETCH NEXT FROM product_cursor
END
CLOSE product_cursor
DEALLOCATE product_cursor
FETCH NEXT FROM comments_list INTO @.parentId,@.TableName,@.RelIDName
END
CLOSE comments_list
DEALLOCATE comments_list
GO

and fetch data from sql

if cant image refer this url

http://shriramgore.ifastnet.com/?q=node/41

thnx

|||

Hi,

Based on my understing, since you didn't do any row-based operations on the table(after you have fetched each row, seems you didn't perform any operations), there is no need for you to use cursors. If what you want is just to fetch data from multi tables, a simpleJoin will work in your case. Use dynamic query if you cannot determine your table name until run-time. Like:

declare @.str nvarchar(100) ,@.str1 varchar(100)set @.str='table_1'set @.str1='table_2'exec ('select * from '+@.str+' as a inner join '+ @.str1+' as b on a.id=b.id')

Hope my suggestion helps|||

thnx for replyBo Chen

but i have following like ......... please check example ...

I have data from multiple table, those table's Names are store as Variable in a table . in this Case i have written 1 store procedure using Cursor . this query Execute in server like (Please check this Url -http://shriramgore.ifastnet.com/?q=node/41&size=_original ) . after executing storeprocedure i get data in different tabels this data i can't fetch in single dataset .if i will fetch all tables(results of storeprocedure) then it eaiser to me for next part .

e.g
DataSet ds = new DataSet();
ds = proc.GetDataSet();

for (int i= 0; i< ds.Tables.Count; i++)
{
for (int y= 0; y< ds.Tables [i].Rows.Count; y++)
{
String str=ds.tabels[i].rows[y]["ColumnName"].toString();
}
}

in this example i enable to get other tabels (dsParent1.Tables[int] ).

please help

thnx in advance

|||

Hi shriram.gore,

I can see your problem. In my opinion, i think you can try to revise your stored procedure, like this: pass three or four parameters(as you need) into your stored procedure and make them as output paramters. In your stored procedure, you can assign them the table-name values during sql clause execution. For example:

create procedure sp_get_tbl_name (@.str1 varchar(100) output,
@.str2 varchar(100) output, @.str3 varchar(100) output...)
as
select @.str1=tablename
from yourtablename
where .....
go
select @.str2=tablename
from yoursecondtablename
where...
go
..

Hope my suggestion helps

|||

Hello ,

thnx for Reply

Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .

excuse i m not using sqlserver 2005

thnx

shriram

|||

Yes uropinion is correct . That query iwould like to use in for loop to fetch data from multiple table . m i rt . in my case i can't use for loop in my page bcoz we have 200 record's from 5 table (Varible table - those declare in other table).if i had use for loop then those query fire 200 time . m i rt .

If i understand you correct, that's not a problem.

Since now you have got those table names, you can user your sldataadpater to perform a sql data retrieval against each data table name--after that you will get server datasets, say, ds1, ds2,ds3,ds4..etc. Generally speaking each dataset you get contains only one table(it depends on your select command)

Then you can create a new dataset named ds, run : ds.tables.add(ds1.table[0]); ds.tables.add(ds2.table[0]),ds.tables.add(ds3.table[0])...etc..

Now your dataset ds contains all the tables you need. You can perform your loop on it.

BTW, what does "m i rt. " stand for??

thanks

|||

HelloBo Chen ,

thank u very much . my prob is solved .

thnx

shriram