Updating DataSet to datasource question

[mirirom]

hi,

i'm working on an app that uses a dataset who's tables are filled with some hybrid SELECT queries, meaning, the tables in the dataset are hybrids of mixed data from the datasource (SQL Server btw), or better, no single table in the dataset is a modeled copy of a table in the datasource.

here's an example query:

SELECT tblA.pKeycol, tblA.val1, tblA.valX, tblB.valX, tblC.valX
FROM tblA INNER JOIN tblB ON (tlbA.pKeyCol = tblB.fKeyCol), tblC
WHERE tblA.pKeyCol = 'someValue'

for fun, say a one-to-many relationship exists between tblA and tblB as well as cascade update/delete constraints.

so after making changes to the table in the dataset, what effect does a call to update on this table do? is the SqlDataAdapter smart enough to figure out what goes where and how?

i guess for clarity it's possible for me to create other tables in the dataset that are replicas of what's in the SQL datasource -- and somehow reflect the changes here and then make a call to Update. this seems like 10-fold the amount of work necessary, however.

any thoughts, advice, suggestions? greatly appreciated, and thanks in advance!

..:: mirirom ::..

 

[mirirom]

well, after digging a bit further, i found a clear cut piece of documentation about this in Andrew Troelsen's "C# and the .Net Platform".

basically it states that in order for an adapter of any type to generate its own SQL statements to pass data back to a source, the SELECT statement for the adapter (i.e. its SelectCommand) can only contain data from a single table. also, the data extracted must also contain a primary key.

this makes total sense.

(btw, the autogenerated SQL statements are created by declaring an object of type SqlCommandBuilder and passing the dataset in its constructor).

in the event that you've extracted data using multiple joins in a query, you must define the UpdateCommand, the InsertCommand, and the DeleteCommand for the adapter. parameters for the INSERT, UPDATE, and DELETE statements should also be included.

for example, the following will only add rows to tblA when new rows are added to the DataTable from the query in the first post (above).

string sSQL = "INSERT INTO tblA (val1, val2, valX) "
     + "VALUES(@val1, @val2, @valX)", connectionObj);
mySqlAdapter.InsertCommand = new SqlCommand(sSQL);
SqlParamter p = 
     mySqlAdapter.InsertCommand.Paramters.Add(
          new SqlParameter("@val1",
          SqlDbType.approprateType));
p.SourceColumn = "col1";
p.SourceVersion = DataRowVersion.Current;
//add params for the other cols as well

when the update() method is called by the adapter, new rows are inserted to the datasource using the above command and values.


..:: mirirom ::..