Updating datasource from DataSet question

omessian

hi, hopefully this isn't a loaded question. 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: //************************************************************ string sSQL = "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' "; SqlConnection con = new SqlConnection("...some connection string"); SqlDataAdapter adpt = new SqlDataAdapter(sSQL, con); con.Open() DataSet ds = new DataSet(); adpt.Fill(ds, "tblHybrid"); // ... mess around with values in tblHybrid (add, change, delete, et al) // ... assume the changes are a.o.k., so update the datasource adpt.Update(ds); //************************************************************* 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? does a scenario like this require that i build custom statements for the UPDATE, INSERT, and DELETE and attach the commands appropriately to the adapter? 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 on these tables. this seems like 10-fold the amount of work necessary, however. sorry for the length of the question -- just getting my feet wet here. any thoughts, advice, suggestions? greatly appreciated, and thanks in advance!

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

itaym0222y ago
Why not using the dataset as copy of datasource, and using dataview to see queries with join in them?
0
omessian22y ago
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)", con); adpt.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.
0