How to fill dataset from SqlDataReader

[german]

Hi All,
I'm trying to find a way to fill dataset from datareader. Why?
I have typed dataset with multiple tables and relations. I want to fill
dataset without using SqlDataAdapter.Fill method for each table in the
dataset. Some of the Sql statements are redundant in meaning of their
reiteration. I'm wondering about way to use stored procedure for getting
multiple record set from and then using DataReader to populate the dataset
tables with information.

Thanks in advance,
Yaroslav.

 

[Miha Markic]

Hi,

Hi All,
I'm trying to find a way to fill dataset from datareader. Why?
I have typed dataset with multiple tables and relations. I want to fill
dataset without using SqlDataAdapter.Fill method for each table in the
dataset. Some of the Sql statements are redundant in meaning of their
reiteration. I'm wondering about way to use stored procedure for getting
multiple record set from and then using DataReader to populate the dataset
tables with information.

Assign to adapter a select command you wish (even one of those who returns
more resultsets).
And do a fill of dataset. It uses reader internally and fetches all
resultsets.

 

[William Ryan]

You'll have to walk through the data reader, and manually populate the
datatable item by item. It's pretty easy b/c you just use the
while(dr.Read()){
DataTable.Rows(x).Column(x) = dr.Getxxxxx

}

 

[William Ryan]

I almost forgot though, just because you can do it this way and it will work
pretty quickly, based on what you've written below, I'm not sure it's the
way to go. Pulling over redundant data isn't necessary or efficient, so I'd
just pull the tables over individually and bind them with a DataRelation
object

 

[german]

I've tryed to use multiselect statement as the select command, but I it
doesnt work. Probaby because of dataadapter have no idea how to correspond
select statements and the datatables in the datareader.

The only option as for me is to use the datareader and populate dataset
tables manually. Or to use dataadapter.Fill() for each datatable in
dataset. Could you please reference articles where I can read how to map
the datatable and the select statement?

Thanks,
Yaoslav

 

[Miha Markic]

Hi german,

It is my understanding that the relevant table is first in FROM list.
IOW it uses first table in FROM list as a target table.
If this doesn't suit you than you'll have to do it manually.

 

[german]

Here is the answer:
Before executing Fill method we should set TableMappings for the dataadapter. This will tell to adapter that we want to map our
tables with tables from our select select comand.

Here is the sample:
dsCommunities commDadaset = new dsCommunities();
commDadaset.EnforceConstraints = false;

string sqlSdtring = "CCPSelect_sp";
SqlCommand cmd = new SqlCommand(sqlSdtring, Connection);
cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(cmd);

da.TableMappings.Add("Table", "Donor");
da.TableMappings.Add("Table1", "Recipient");
da.TableMappings.Add("Table2", "Link");
da.TableMappings.Add("Table3", "Warehouse");
da.TableMappings.Add("Table4", "DonorStorage");

da.Fill(commDadaset);
commDadaset.AcceptChanges();
commDadaset.EnforceConstraints = true;
return commDadaset;

 

[Miha Markic]

Hi,
Yes, I've found it, too.

 

[german]

Yes, It works great, but now I found some other issues. I'm redesigning
code from populating dataset manually through datareader looping. It works
great but code looks not really great, and there are some foreign keys
relation which adds some additional code routin while performing dataset
filling.

After redesigning code into using dataadpter fill method I've got the
result in performance reducing.

Does anyone know is it natural that populating dataset manually through
datareader looping is more efficiet than using dataadapter.fill method.


Thanks in advance,
Yaorslav

 

[Miha Markic]

Hi,

Yes, It works great, but now I found some other issues. I'm redesigning
code from populating dataset manually through datareader looping. It works
great but code looks not really great, and there are some foreign keys
relation which adds some additional code routin while performing dataset
filling.

After redesigning code into using dataadpter fill method I've got the
result in performance reducing.

Does anyone know is it natural that populating dataset manually through
datareader looping is more efficiet than using dataadapter.fill method.

Fill method uses DataReaders internally.

 

[Anders Borum]

Hello!

Isn't this what the SqlDataAdapter does internally anyway?

Regarding the problem of SelectCommands, I'm also facing some of the same
problems. I have a set of views that I'm selecting from, but the name of the
views doesn't correspond with the names of the strongly typed DataTables I'm
(planning on) using.

I have a view called CMS_VS_Page in the SQLServer. I'd like a particular
SelectCommand that selects this view to actually put the data in the typed
'Page' DataTable.

Any ideas?

 

[Rajesh Patel]

I think, dataadpater.fill(dataset, "Page") will solve your problem.

Just keep in mind that 2nd argument is case-sensitive.

Rajesh Patel