Dumb question about strongly typed datasets

  • Thread starter Thread starter Thomas Hill
  • Start date Start date
T

Thomas Hill

Okay, I am sure this is right in front of my nose, but I cannot figure out
how to do this...
I have a strongly typed dataset (STDS) generated by xsd in CSharp. I have
the code in place to populate the various tables (7 of them) with datarows.
How do I now update this back to the database? Do I need 7 SqlDataAdapters,
each one with the appropriate Command members? If so, where can I get the 7
SELECT statements? They are not stored in the STDS anywhere. If I have to
remember the queries used to generate the STDS, then where is the benefit?
I should be able to tell the STDS to 'save yourself' back to the table(s)
you were designed from'.
 
Hi Thomas,

Thomas Hill said:
Okay, I am sure this is right in front of my nose, but I cannot figure out
how to do this...
I have a strongly typed dataset (STDS) generated by xsd in CSharp. I have
the code in place to populate the various tables (7 of them) with datarows.
How do I now update this back to the database? Do I need 7 SqlDataAdapters,
each one with the appropriate Command members?
Click to expand...

Yes.

If so, where can I get the 7
SELECT statements? They are not stored in the STDS anywhere. If I have to
remember the queries used to generate the STDS, then where is the benefit?
I should be able to tell the STDS to 'save yourself' back to the table(s)
you were designed from'.
Click to expand...

How did you generate STDS in first place? I suppose with help of
dataadapters?
If so, just use them for updating.
Otherwise, you might recreate them (if tables in STDS are copies of tables
in database, you might drag & drop adjacent tables from Server Explorer
which will result in dataadapters generation)
 
Uhm yes and no <g>...

Yes you need to create the Insert/Update/Delete statements, but you can use
a CommandBuilder to automatically generate them for you. Not optimal as it
queries schema info and only works with a single table that has a PK, but it
more or less works.

You can do this once then hang on to the DataAdapter and reuse it later. The
code for this is pretty straight forward and you can make it generic easily
enough. Here's an example from my Data Access layer's SaveDataSet method:

public override int SaveDataSet(DataSet ds, string Tablename, string
DatabaseTablename)
{
int RowsUpdated = -1;

this.SetError();

if (!this.Open())
return -1;

this.LoadDataAdapterCommands( new SqlCommand("SELECT * FROM " +
DatabaseTablename),ref this.DataAdapter);

if (this.Transaction != null)
{
this.DataAdapter.InsertCommand.Transaction = this.Transaction;
this.DataAdapter.UpdateCommand.Transaction = this.Transaction;
this.DataAdapter.DeleteCommand.Transaction = this.Transaction;
}

/// Build the Delete, Update, and Insert commands
try
{
/// Issue the update and return the number of rows updated
RowsUpdated = ((SqlDataAdapter) this.DataAdapter).Update(ds,
ds.Tables[Tablename].ToString());
}
catch(Exception ex)
{
this.SetError(ex.Message);
return -1;
}


return RowsUpdated;
}

public override bool LoadDataAdapterCommands(IDbCommand SelectCommand,ref
IDbDataAdapter Adapter)
{
if (Adapter == null)
Adapter = this.CreateDataAdapter(SelectCommand);

SqlCommandBuilder CommandBuilder= new SqlCommandBuilder((SqlDataAdapter)
Adapter);

Adapter.SelectCommand = SelectCommand;
Adapter.DeleteCommand = CommandBuilder.GetDeleteCommand();
Adapter.UpdateCommand = CommandBuilder.GetUpdateCommand();
Adapter.InsertCommand = CommandBuilder.GetInsertCommand();

return true;
}

With this sort of code you can now simply call SaveDataSet() with a
tablename and it will save it assuming the rules for using the
CommandBuilder apply. Otherwise you still have to manually build the
commands (and in my case I use a generator to pre-gen the commands anyway).

+++ Rick ---


+++ Rick ---


--

Rick Strahl
West Wind Technologies
http://www.west-wind.com/
http://www.west-wind.com/blog/
 
Back
Top