Small Example of What is Happening
SqlCommandbuilder builds 100 plus Columns and when used does not work
SqlDataAdapter Ad = new SqlDataAdapter();
Ad.SelectCommand = new SqlCommand("Select top 0 * from MyTable With
(nolock)");
//use Sqlcommand builder
SqlCommandBuilder cmd = new SqlCommandBuilder(Ad);
Ad.SelectCommand.Connection=oConn;
Ad.InsertCommand = cmd.GetInsertcommand();
SqlDataSet ds = new SqlDataSet();
oConn.Open()
Ad.Fill(ds,"MyTable")
oConn.Close()
//at this point i have 0 rows/records
//i parse a Text file
//adding rows to this ds.Tables[0] mytable
//.....
//......
// looping through a text file and adding rows
//......
//......
//at this point i have 100 rows in the Table i want to insert them all and
have ds.tables[0] reflect the identity key
// add scope identity to the insert command
Ad.InsertCommand+="; Select Scope_identity() as MyIdentity";
oConn.Open()
Ad.Update(ds,"MyTable")
oConn.Close()
ds.AcceptChanges();
// does not work using Sqlcommandbuilder()
console.writeline(ds.tables[0]["MyIdentity") printed values = 0
//if i change the insertcommand to the below than it works
// the below reflects 100 new identities in the data set table[0]
// so im wondering, is there a switch in SqlCommandBuilder or somthing that
i am missing
//if i change the above Insert command to
Ad.InsertCommand.CommandText="Insert into MyTable (Field1>
values(@Field1);Select Scope_identity() as MyIdentity";
//
//
Ad.InsertCommand.Parameters.Add("@Field1", SqlDbType.Int, 0,"Field1");
