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! :)

Answer 1

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;
    }