Using SqlDataAdapter.Update() with Parameters

Question

How trying to figure out how to use SqlDataAdapter.Update(DataTable) method using a parameterised query. How can I add the values to the command without iterating over the whole DataTable?

And how can I execute the SqlDataAdapter insert and update methods within a transaction?

What I have so far is the following:

internal void InsertUpdate(DataTable dt){
    using(var con = new SqlConnection(ConnectionString)){
         var sb = new StringBuilder();
         sb.AppendLine("UPDATE myTable");
         sb.AppendLine("SET prop1 = @p1, prop2 = qp2");
         sb.AppendLine("WHERE id = @id");
         var cmd = new SqlCommand(sb.ToString());
         cmd.Parameters.AddWithValue("p1", ????);
         cmd.Parameters.AddWithValue("p2", ????);
         cmd.Parameters.AddWithValue("id", ????);
         var adapter = new SqlDataAdapter(selectQuery, con);
         adapter.UpdateCommand = cmd;
         adapter.Update(dt);
    }
}

Best Regards

Jay

score 1 · Accepted Answer · 2011-08-27 01:11:03Z

1

Instead of using Parameters.AddWithValue(...), use Parameters.Add(new SqlParameter(...)) and use one of the last three constructors for SqlParameter documents here: SqlParameter Class MSDN documentation. They allow you to specify a column which will be used at runtime to fill the parameter. For example, here is a line from one of my current programs:

UpdateCommand.Parameters.Add(new OleDbParameter("FK_CustomerID", global::System.Data.OleDb.OleDbType.Integer, 0, global::System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "FK_CustomerID", global::System.Data.DataRowVersion.Current, false, null));

Note the second use of "FK_CustomerID", which indicates to use this DataTable column for the parameter.

Regarding using transactions, take a look at this for some ideas: Transactions with TableAdapters, a lazy man's approach. Regards, Drew

Collectives™ on Stack Overflow

Using SqlDataAdapter.Update() with Parameters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related