strongly typed multi-table dataset only fills the first table.

[Caleb]

My stored procedure returns three different result sets, but I can
only get the first returned result set into my strongly-typed dataset.
My strongly typed dataset was created through visual studio by
dragging and dropping the stored proc through the design tools. There
are three matching tables in my strongly typed dataset.

Even if I set the tablemappings, I can not get it to fill all three
tables when I call sqlDataAdapter.Fill(userDS). I can get it to fill
the first table if I call sqlDataAdapter.Fill(userDS,"User"), but then
if I try to call sqlDataAdapter.Fill(userDS,"Role") it fails with the
error message "Failed to enable constraints. One or more rows contains
values violating non-null, unique, or foriegn-key constraints.". If I
change the stored proc to return my "Role" record set first then I can
do a sqlDataAdapter.Fill(userDS,"Role") and it will work, but then
when I try to fill the "User" table it throws the same exception.

In summary I can only get the first result set into my strongly typed
dataset, and only if I use the
sqlDataAdapter.Fill(dataset,"tablename"). I can not get the
tablemappings to work at all. Please help!


string dbConnectionString =
System.Configuration.ConfigurationSettings.AppSettings[APPLICATION_SETTING_DBCONNECTIONSTRING];
this.sqlConnection = new SqlConnection(dbConnectionString);
this.sqlConnection.Open();
this.sqlDataAdapter = new
SqlDataAdapter(selectCommandText,this.sqlConnection);
this.sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);

string selectCommandText = "MRC_RetrieveUser";
this.ConnectDB(selectCommandText);
this.sqlDataAdapter.SelectCommand.CommandType =
System.Data.CommandType.StoredProcedure;

this.sqlDataAdapter.TableMappings.Add("User","User");
this.sqlDataAdapter.TableMappings.Add("Role","Role");
this.sqlDataAdapter.TableMappings.Add("Action","Action");

//no exception is thrown, but nothing makes it into the dataset:
this.sqlDataAdapter.Fill(userDS);

//or I can try:
this.sqlDataAdapter.Fill(userDS,"User");
//throws an exception here:
this.sqlDataAdapter.Fill(userDS,"Role");
this.sqlDataAdapter.Fill(userDS,"Action");

Any help is much appreciated.

 

[David Browne]

My stored procedure returns three different result sets, but I can
only get the first returned result set into my strongly-typed dataset.
My strongly typed dataset was created through visual studio by
dragging and dropping the stored proc through the design tools. There
are three matching tables in my strongly typed dataset.

Even if I set the tablemappings, I can not get it to fill all three
tables when I call sqlDataAdapter.Fill(userDS). I can get it to fill
the first table if I call sqlDataAdapter.Fill(userDS,"User"), but then
if I try to call sqlDataAdapter.Fill(userDS,"Role") it fails with the
error message "Failed to enable constraints. One or more rows contains
values violating non-null, unique, or foriegn-key constraints.". If I
change the stored proc to return my "Role" record set first then I can
do a sqlDataAdapter.Fill(userDS,"Role") and it will work, but then
when I try to fill the "User" table it throws the same exception.

Your procedure returns 3 result sets but you are only asking the
sqlDataAdapter to fill one table.

Try
sqlDataAdapter.Fill(userDS)
instead.

If that doesn't work, do what I do. Fill your 3 tables with 3 different
commands. You must update them one table at a time. Why not fill them one
table at a time?

David

 

[Caleb]

Thanks but I already tried what you suggested...see the code above in
my original post.

I figured out my problem. I can now get it to fill all three tables
with ONE call to the database and ONE SqlDataAdapter and ONE dataset.

You simply need to map the tables like this:

this.sqlDataAdapter.TableMappings.Add("Table","User");
this.sqlDataAdapter.TableMappings.Add("Table1","Role");
this.sqlDataAdapter.TableMappings.Add("Table2","Action");

I thought that the "sourcetable" in the TableMapping was actually
pulling the DB table name, but I figured out that the source table is
just named "Table". To figure it out, I set
"sqlDataAdapter.MissingMappingAction = MissingMappingAction.Error;"
and then looked at the exception discription to see which table
mappings were missing/not working.

This is for a multi-threaded, hi traffic, DB web application so using
multiple SQLDataAdapters and/or datasets was not an option. I needed
to find a way that was sure to only make one round-trip to the DB. I
confirmed this by adding some auditing logic to my stored procedure.
It is indeed only called once. I also confirmed that the DB is called
each time you call the SQLDataAdapter.Fill method so filling different
datasets/tables and then merging would be a big performance hit.

 

[David Browne]

Thanks but I already tried what you suggested...see the code above in
my original post.

I figured out my problem. I can now get it to fill all three tables
with ONE call to the database and ONE SqlDataAdapter and ONE dataset.

You simply need to map the tables like this:

this.sqlDataAdapter.TableMappings.Add("Table","User");
this.sqlDataAdapter.TableMappings.Add("Table1","Role");
this.sqlDataAdapter.TableMappings.Add("Table2","Action");

I thought that the "sourcetable" in the TableMapping was actually
pulling the DB table name, but I figured out that the source table is
just named "Table". To figure it out, I set
"sqlDataAdapter.MissingMappingAction = MissingMappingAction.Error;"
and then looked at the exception discription to see which table
mappings were missing/not working.

This is for a multi-threaded, hi traffic, DB web application so using
multiple SQLDataAdapters and/or datasets was not an option. I needed
to find a way that was sure to only make one round-trip to the DB. I
confirmed this by adding some auditing logic to my stored procedure.
It is indeed only called once. I also confirmed that the DB is called
each time you call the SQLDataAdapter.Fill method so filling different
datasets/tables and then merging would be a big performance hit.

In fact no.

The way you are doing this is fine. But your belief that that a stored
procedure which returns three different resultsets uses significantly fewer
resources than three seperate stored procedures each returning one resultset
is unfounded. Resultsets are expensive to return; stored procedures are
cheap to invoke. So three resultsets plus three procedure calls is hardly
different from three resultsets and one procedure calls.

David

 

[Caleb]

It would be interesting to do some benchmarks. I don't see any way
possible that making 3 different DB round-trips and handling two
additional dataset objects and 2 additional merge tasks could be a
faster solution than doing it all at the same time. Is there something
else that I am missing here? I would appreciate any additional insite.

 

[David Browne]

It would be interesting to do some benchmarks. I don't see any way
possible that making 3 different DB round-trips and handling two
additional dataset objects and 2 additional merge tasks could be a
faster solution than doing it all at the same time.

You don't have aditional dataset objects or merge tasks. There are 3 result
sets, 3 datareaders, and 3 datatables either way. The only difference is
whether you have one stored procedure invocation or three. And I never said
it was faster, just that the difference usually isn't worth worrying about.

David