Adapter

  • Thread starter Thread starter juli jul
  • Start date Start date
J

juli jul

Hello,
I am running in a loop ,doing some sql query (each time from another
table) and the result of each query is int result.
The problem is that the results suppose to be transfered to dataset ,and
the question how do I actovate the sql query command in Adapter in a
loop?
Thanks a lot!
 
Hi juli jul,

I would recommend doing this as a stored procedure and returning the results
as multiple select statements or multiple values.

<code>
CREATE PROCEDURE GetMutlipleTables
AS

SELECT Col1 FROM Table1

SELECT Col1 FROM Table2

SELECT Col1 FROM Table3

GO
</code>

This will return you a 3 table dataset that you can use any way you feel
fit. You can also return multiple values from multiple tables in one select
call also.

<code>
CREATE PROCEDURE GetMutlipleValues
AS

SELECT Table1.Col1 as 'FirstValue', Table2.Col1 as 'SecondValue',
Table3.Col3 as 'ThirdValue' FROM Table1, Table2, Table3

GO
</code>

Doing this on the C# side is not suggested.

HTH,
~d
 
Hello,
The problem is that I suppose to run over 200 tables.
I have two querries:
resolve all tables:
select name from objects where name like '%table%'

get rows count:
SELECT rows FROM indexes WHERE id = ID('<table_name>')

The first query returns me something like 200 table_names,how can I do
the secound in a loop (for each table) in stored procedure?
Thanks a lot!
 
You could use the 'sp_msforeachtable'
(http://www.databasejournal.com/features/mssql/article.php/1490661) stored
procedure to do it.

<code>
exec sp_msforeachtable @command1='select count(*) as ''RowCount'' from ?'
</code>

If you run that in a stored procedure, you will still get over 200 tables
back into a dataset, but it should be 100X faster than looping through C#
and issuing commands to a SqlDataAdapter. Also, moving it to the database
server saves your client application from having to do all that ugly work.

HTH,
~d
 
Hello,
but the problem is that I want specific tables than when I get the
almost 200 names (but there are only part of the tables in database) , I
need to enter another table and for each name to get the field with
number of rows (I don't need the count).
How can I imlement this? Thanks a lot!
 
Back
Top