Updating multiple tables using SqlDataAdapter

Question

I've been trawling through pages and pages on the internet for days now trying different approaches and I'm still not sure how I should be doing this.

On my third InsertCommand, I'd like to reference a column on the other 2 tables.

// Populate a DataSet from multiple Tables... Works fine
sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = new SqlCommand("SELECT * FROM hardware", sqlConn);
sqlDA.Fill(ds, "Hardware");
sqlDA.SelectCommand.CommandText = "SELECT * FROM software";
sqlDA.Fill(ds, "Software");
sqlDA.SelectCommand.CommandText = "SELECT * FROM join_hardware_software";
sqlDA.Fill(ds, "HS Join");

// After DataSet has been changed, perform an Insert on relevant tables...
updatedDs = ds.GetChanges();
SqlCommand DAInsertCommand = new SqlCommand();
DAInsertCommand.CommandText = "INSERT INTO hardware (host, model, serial) VALUES (@host, @model, @serial)";
DAInsertCommand.Parameters.AddWithValue("@host", null).SourceColumn = "host";
DAInsertCommand.Parameters.AddWithValue("@model", null).SourceColumn = "model";
DAInsertCommand.Parameters.AddWithValue("@serial", null).SourceColumn = "serial";
sqlDA.InsertCommand = DAInsertCommand;
sqlDA.Update(updatedDs, "Hardware"); // Works Fine

DAInsertCommand.Parameters.Clear(); // Clear parameters set above
DAInsertCommand.CommandText = "INSERT INTO software (description) VALUES (@software)";
DAInsertCommand.Parameters.AddWithValue("@software", null).SourceColumn = "description";
sqlDA.InsertCommand = DAInsertCommand;
sqlDA.Update(updatedDs, "Software"); // Works Fine

DAInsertCommand.Parameters.Clear(); // Clear parameters set above
DAInsertCommand.CommandText = "INSERT INTO join_hardware_software (hardware_id, software_id) VALUES (@hardware_id, @software_id)";
// *****
DAInsertCommand.Parameters.AddWithValue("@hardware_id", null).SourceColumn = "?"; // I want to set this to be set to my 'hardware' table to the 'id' column.
DAInsertCommand.Parameters.AddWithValue("@software_id", null).SourceColumn = "?"; // I want to set this to be set to my 'software' table to the 'id' column.
// *****
sqlDA.InsertCommand = DAInsertCommand;
sqlDA.Update(updatedDs, "HS Join");

Could somebody please tell me where I am going wrong and how I could potentially overcome this? Many thanks! :)

If your inserts into Hardware and Software returned the ids then you could use them. Maybe this link might help? http://msdn.microsoft.com/en-us/library/59x02y99(v=vs.100).aspx — Daniel Hollinrake, Jan 17 '13 at 10:00
Surely you need some values in the insert into your 'join' table. I guess that's what you're trying to do with SourceColumn but I'm not certain if you can use it that way. — Daniel Hollinrake, Jan 17 '13 at 10:16
Sorry, updatedDs = ds.getChanges()... Yes Daniel, I need to somehow retrieve the IDs of the colums in the first 2 tables. I just read that article, haven't used Parameter Directions before but I'll read into it. I'm pretty sure I'm doing all of this completely wrong but I just don't know how the average .NET Developer would read data from multiple SQL tables into a DataSet, modify the DataSet using a DataGridView and then post the DataSet back to the relevant SQL tables. Thanks for your inputs! :) — Robula, Jan 17 '13 at 10:37
Try adding an output parameter to each of the first two inserts. Then you can use their returned values in the final insert. Please let me know how it goes. — Daniel Hollinrake, Jan 17 '13 at 10:51
I'm really lost in all of this... I did what you said and added this just above the sqlDA.Update(updatedDs, "Hardware"); ............... SqlParameter testOutParam = new SqlParameter("@hardware_id", SqlDbType.Int); testOutParam.Direction = ParameterDirection.Output; testOutParam.SourceColumn = "id"; DAInsertCommand.Parameters.Add(testOutParam); I tried a MessageBox.Show(testOutParam.Value.ToString()); which returned null. — Robula, Jan 17 '13 at 11:22
If you add an Output Parameter to each of the first two inserts then read their values, you'll be able to use them in the last insert. I think you'll need to add the Output Parameter first. Let me know how it goes. I think you've got caught up with using Source Column. When I've used Parameters.AddWithValue I've not used SourceColumn. If I get chance I'll try and replicate your code on my machine and let you know how I get on. — Daniel Hollinrake, Jan 17 '13 at 11:24
I dumped the latest here http://pastebin.com/P1qkYLMV. The problem is now is that in order to perform each sqlDA.Update I need to clear the previous Parameters otherwise the next Insert will be expecting them and throws an error. — Robula, Jan 17 '13 at 11:43
It seems I'm not getting anywhere with SqlDataAdaptor. The reason I wanted to use it is because I wanted users to be able to have the flexability with using a DataGridView to view and change data. I thought this is why DataSets were supposed to be useful and everything was working great until the spec changed and I needed to link hardware id's to software id's... If there is an easier way I'd love to know! Thanks again — Robula, Jan 17 '13 at 12:21

Daniel Hollinrake · Accepted Answer · 2013-12-16T09:12:32.807

With regards to your comments this seems to be one of those occasions where if you and I were sat next to each other we'd get this sorted but it's a bit tricky.

This is code I've used when working with SqlConnection and SqlCommand. There might be stuff here that would help you.

    public static void RunSqlCommandText(string connectionString, string commandText) {
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand comm = conn.CreateCommand();

        try {
            comm.CommandType = CommandType.Text;
            comm.CommandText = commandText;

            comm.Connection = conn;
            conn.Open();
            comm.ExecuteNonQuery();
        } catch (Exception ex) {
            System.Diagnostics.EventLog el = new System.Diagnostics.EventLog();
            el.Source = "data access class";
            el.WriteEntry(ex.Message + ex.StackTrace + " SQL '" + commandText + "'");
        } finally {
            conn.Close();
            comm.Dispose();
        }
    }


    public static int RunSqlAndReturnId(string connectionString, string commandText) {
        SqlConnection conn = new SqlConnection(connectionString);
        SqlCommand comm = conn.CreateCommand();
        int id = -1;

        try {
            comm.CommandType = CommandType.Text;
            comm.CommandText = commandText;

            comm.Connection = conn;
            conn.Open();
            var returnvalue = comm.ExecuteScalar();
            if (returnvalue != null) {
                id = (int)returnvalue;                
            }
        } catch (Exception ex) {
            System.Diagnostics.EventLog el = new System.Diagnostics.EventLog();
            el.Source = "data access class";
            el.WriteEntry(ex.Message + ex.StackTrace + " SQL '" + commandText + "'");
        } finally {
            conn.Close();
            comm.Dispose();
        }

        return id;
    }

Thanks, this kind of approach may be the way I have to go which is a shame because I would have prefered to work with a DataSet since my app will be heavily dependant on a relational SQL backend and I feel that DataSets are the best thing for that. The app is going to eventually be a kind of asset tracking program heavily tailored for my department. Thank you for your help Daniel, you've been most patient! :) — Robula, Jan 17 '13 at 14:26
Don't give up on the other method. Once you've got it working this way then it might give you clues to get the other method working. — Daniel Hollinrake, Jan 17 '13 at 14:36
I found this: http://stackoverflow.com/a/549226/1648429. It seems a bit over the top having to specify my foreign keys explicitly, but heck I'll give anything a go... — Robula, Jan 17 '13 at 14:58
Maybe it's not that over the top. I've always thought of DataSets as being snapshots of a database so if you're using it to transfer info from a web form to an actual database then you'll probably want the DataSet to know this information so it can enact constraints and so on. — Daniel Hollinrake, Jan 17 '13 at 15:58
[http://blogs.msdn.com/b/smartclientdata/archive/2005/10/31/returnidentityvaluequery.aspx ] describes one way to achieve this, although it's using strongly-typed datasets — peterG, Jan 18 '13 at 22:17

Updating multiple tables using SqlDataAdapter

1 Answers1