Search Results

Search found 18 results on 1 pages for 'sqlcommandbuilder'.

Page 1/1 | 1 

  • SqlCommandBuilder.DeriveParameters(command) and IsNullable

    - by Andrey
    I have written an O/R database wrapper that generates some wrapper methods for stored procs it reads from the database. Now I need to produce some custom wrapper code if an input parameter of a stored proc is defaulted to NULL. The problem is - I get stored proc parameters using: SqlCommandBuilder.DeriveParameters(command) and it doesn't bring parameter defaults. Is there any way to look up those defaults? Are they stored anywhere in the database? BTW, I'm using SQL Server 2008

    Read the article

  • SqlCommandBuilder.DeriveParameters(command) and IsNullable

    - by Andrey
    I have written an O/R database wrapper that generates some wrapper methods for stored procs it reads from the database. Now I need to produce some custom wrapper code if an input parameter of a stored proc is defaulted to NULL. The problem is - I get stored proc parameters using: SqlCommandBuilder.DeriveParameters(command) and it doesn't bring parameter defaults. Is there any way to look up those defaults? Are they stored anywhere in the database? BTW, I'm using SQL Server 2008

    Read the article

  • WinForms DataGridView - update database

    - by Geo Ego
    I know this is a basic function of the DataGridView, but for some reason, I just can't get it to work. I just want the DataGridView on my Windows form to submit any changes made to it to the database when the user clicks the "Save" button. I populate the DataGridView according to a function triggered by a user selection in a DropDownList as follows: using (SqlConnection con = new SqlConnection(conString)) { con.Open(); SqlDataAdapter ruleTableDA = new SqlDataAdapter("SELECT rule.fldFluteType AS [Flute], rule.fldKnife AS [Knife], rule.fldScore AS [Score], rule.fldLowKnife AS [Low Knife], rule.fldMatrixScore AS [Matrix Score], rule.fldMatrix AS [Matrix] FROM dbo.tblRuleTypes rule WHERE rule.fldMachine_ID = '1003'", con); DataSet ruleTableDS = new DataSet(); ruleTableDA.Fill(ruleTableDS); RuleTable.DataSource = ruleTableDS.Tables[0]; } In my save function, I basically have the following (I've trimmed out some of the code around it to get to the point): using (SqlDataAdapter ruleTableDA = new SqlDataAdapter("SELECT rule.fldFluteType AS [Flute], rule.fldKnife AS [Knife], rule.fldScore AS [Score], rule.fldLowKnife AS [Low Knife], rule.fldMatrixScore AS [Matrix Score], rule.fldMatrix AS [Matrix] FROM dbo.tblRuleTypes rule WHERE rule.fldMachine_ID = '1003'", con)) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder(ruleTableDA); DataTable dt = new DataTable(); dt = RuleTable.DataSource as DataTable; ruleTableDA.Fill(dt); ruleTableDA.Update(dt); } Okay, so I edited the code to do the following: build the commands, create a DataTable based on the DataGridView (RuleTable), fill the DataAdapter with the DataTable, and update the database. Now ruleTableDA.Update(dt) is throwing the exception "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."

    Read the article

  • C#.NET DataGridView - update database

    - by Geo Ego
    I know this is a basic function of the DataGridView, but for some reason, I just can't get it to work. I just want the DataGridView on my Windows form to submit any changes made to it to the database when the user clicks the "Save" button. On form load, I populate the DataGridView and that's working fine. In my save function, I basically have the following (I've trimmed out some of the code around it to get to the point): using (SqlDataAdapter ruleTableDA = new SqlDataAdapter("SELECT rule.fldFluteType AS [Flute], rule.fldKnife AS [Knife], rule.fldScore AS [Score], rule.fldLowKnife AS [Low Knife], rule.fldMatrixScore AS [Matrix Score], rule.fldMatrix AS [Matrix] FROM dbo.tblRuleTypes rule WHERE rule.fldMachine_ID = '1003'", con)) { SqlCommandBuilder commandBuilder = new SqlCommandBuilder(ruleTableDA); DataTable dt = new DataTable(); dt = RuleTable.DataSource as DataTable; ruleTableDA.Fill(dt); ruleTableDA.Update(dt); } Okay, so I edited the code to do the following: build the commands, create a DataTable based on the DataGridView (RuleTable), fill the DataAdapter with the DataTable, and update the database. Now ruleTableDA.Update(dt) is throwing the exception "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." Thanks very much!

    Read the article

  • CommandBuilder and SqlTransaction to insert/update a row

    - by Jesse
    I can get this to work, but I feel as though I'm not doing it properly. The first time this runs, it works as intended, and a new row is inserted where "thisField" contains "doesntExist" However, if I run it a subsequent time, I get a run-time error that I can't insert a duplicate key as it violate the primary key "thisField". static void Main(string[] args) { using(var sqlConn = new SqlConnection(connString) ) { sqlConn.Open(); var dt = new DataTable(); var sqlda = new SqlDataAdapter("SELECT * FROM table WHERE thisField ='doesntExist'", sqlConn); sqlda.Fill(dt); DataRow dr = dt.NewRow(); dr["thisField"] = "doesntExist"; //Primary key dt.Rows.Add(dr); //dt.AcceptChanges(); //I thought this may fix the problem. It didn't. var sqlTrans = sqlConn.BeginTransaction(); try { sqlda.SelectCommand = new SqlCommand("SELECT * FROM table WITH (HOLDLOCK, ROWLOCK) WHERE thisField = 'doesntExist'", sqlConn, sqlTrans); SqlCommandBuilder sqlCb = new SqlCommandBuilder(sqlda); sqlda.InsertCommand = sqlCb.GetInsertCommand(); sqlda.InsertCommand.Transaction = sqlTrans; sqlda.DeleteCommand = sqlCb.GetDeleteCommand(); sqlda.DeleteCommand.Transaction = sqlTrans; sqlda.UpdateCommand = sqlCb.GetUpdateCommand(); sqlda.UpdateCommand.Transaction = sqlTrans; sqlda.Update(dt); sqlTrans.Commit(); } catch (Exception) { //... } } } Even when I can get that working through trial and error of moving AcceptChanges around, or encapsulating changes within Begin/EndEdit, then I begin to experience a "Concurrency violation" in which it won't update the changes, but rather tell me it failed to update 0 of 1 affected rows. Is there something crazy obvious I'm missing?

    Read the article

  • C# ASP.NET Update database with datatable

    - by Sir Graystar
    Scenario: I'm just trying to update my database with the changes made by the user to their information. Here is my code: SqlCommandBuilder cb = new SqlCommandBuilder(da); dt.Rows[0][2] = txtname.Text; dt.Rows[0][3] = txtinterests.Text; dt.Rows[0][4] = txtlocation.Text; da.SelectCommand = new SqlCommand(sqlcommand, conn); da.Update(dt); I know its going to be something obvious, but what have I missed? There are no errors, everything compiles correctly, but nothing happens. The record remains unchanged.

    Read the article

  • Get the identity value from an insert via a .net dataset update call

    - by DeveloperMCT
    Here is some sample code that inserts a record into a db table: Dim ds As DataSet = New DataSet() da.Fill(ds, "Shippers") Dim RowDatos As DataRow RowDatos = ds.Tables("Shippers").NewRow RowDatos.Item("CompanyName") = "Serpost Peru" RowDatos.Item("Phone") = "(511) 555-5555" ds.Tables("Shippers").Rows.Add(RowDatos) Dim custCB As SqlCommandBuilder = New SqlCommandBuilder(da) da.Update(ds, "Shippers") It inserts a row in the Shippers Table, the ShippersID is a Indentity value. My question is how can i retrieve the Identity value generated when the new row is inserted in the Shippers table. I have done several web searches and the sources I've seen on the net don't answer it speccifically or go on to talk about stored procedures. Any help would be appreciated. Thanks!

    Read the article

  • How to determine if DataGridView contains uncommitted changes when bound to a SqlDataAdapter

    - by JYelton
    I have a form which contains a DataGridView, a BindingSource, a DataTable, and a SqlDataAdapter. I populate the grid and data bindings as follows: private BindingSource bindingSource = new BindingSource(); private DataTable table = new DataTable(); private SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT * FROM table ORDER BY id ASC;", ClassSql.SqlConn()); private void LoadData() { table.Clear(); dataGridView1.AutoGenerateColumns = false; dataGridView1.DataSource = bindingSource; SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter); table.Locale = System.Globalization.CultureInfo.InvariantCulture; dataAdapter.Fill(table); bindingSource.DataSource = table; } The user can then make changes to the data, and commit those changes or discard them by clicking either a save or cancel button, respectively. private void btnSave_Click(object sender, EventArgs e) { // save everything to the displays table dataAdapter.Update(table); } private void btnCancel_Click(object sender, EventArgs e) { // alert user if unsaved changes, otherwise close form } I would like to add a dialog if cancel is clicked that warns the user of unsaved changes, if unsaved changes exist. Question: How can I determine if the user has modified data in the DataGridView but not committed it to the database? Is there an easy way to compare the current DataGridView data to the last-retrieved query? (Note that there would not be any other threads or users altering data in SQL at the same time.)

    Read the article

  • Getting identity from Ado.Net Update command

    - by rboarman
    My scenario is simple. I am trying to persist a DataSet and have the identity column filled in so I can add child records. Here's what I've got so far: using (SqlConnection connection = new SqlConnection(connStr)) { SqlDataAdapter adapter = new SqlDataAdapter("select * from assets where 0 = 1", connection); adapter.MissingMappingAction = MissingMappingAction.Passthrough; adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; SqlCommandBuilder cb = new SqlCommandBuilder(adapter); var insertCmd = cb.GetInsertCommand(true); insertCmd.Connection = connection; connection.Open(); adapter.InsertCommand = insertCmd; adapter.InsertCommand.CommandText += "; set ? = SCOPE_IDENTITY()"; adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters; var param = new SqlParameter("RowId", SqlDbType.Int); param.SourceColumn = "RowId"; param.Direction = ParameterDirection.Output; adapter.InsertCommand.Parameters.Add(param); SqlTransaction transaction = connection.BeginTransaction(); insertCmd.Transaction = transaction; try { assetsImported = adapter.Update(dataSet.Tables["Assets"]); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); // Log an error } connection.Close(); } The first thing that I noticed, besides the fact that the identity value is not making its way back into the DataSet, is that my change to add the scope_identity select statement to the insert command is not being executed. Looking at the query using Profiler, I do not see my addition to the insert command. Questions: 1) Why is my addition to the insert command not making its way to the sql being executed on the database? 2) Is there a simpler way to have my DataSet refreshed with the identity values of the inserted rows? 3) Should I use the OnRowUpdated callback to add my child records? My plan was to loop through the rows after the Update() call and add children as needed. Thank you in advance. Rick

    Read the article

  • How can I synchronise two datatables and update the target in the database?

    - by Craig
    I am trying to synchronise two tables between two databases. I thought that the best way to do this would be to use the DataTable.Merge method. This seems to pick up the changes, but nothing ever gets committed to the database. So far I have: string tableName = "[Table_1]"; string sql = "select * from " + tableName; using (SqlConnection sourceConn = new SqlConnection(ConfigurationManager.ConnectionStrings["source"].ConnectionString)) { SqlDataAdapter sourceAdapter = new SqlDataAdapter(); sourceAdapter.SelectCommand = new SqlCommand(sql, sourceConn); sourceConn.Open(); DataSet sourceDs = new DataSet(); sourceAdapter.Fill(sourceDs); using (SqlConnection targetConn = new SqlConnection(ConfigurationManager.ConnectionStrings["target"].ConnectionString)) { SqlDataAdapter targetAdapter = new SqlDataAdapter(); targetAdapter.SelectCommand = new SqlCommand(sql, targetConn); SqlCommandBuilder builder = new SqlCommandBuilder(targetAdapter); targetAdapter.InsertCommand = builder.GetInsertCommand(); targetAdapter.UpdateCommand = builder.GetUpdateCommand(); targetAdapter.DeleteCommand = builder.GetDeleteCommand(); targetConn.Open(); DataSet targetDs = new DataSet(); targetAdapter.Fill(targetDs); targetDs.Tables[0].TableName = tableName; sourceDs.Tables[0].TableName = tableName; targetDs.Tables[0].Merge(sourceDs.Tables[0]); targetAdapter.Update(targetDs.Tables[0]); } } At the present time, there is one row in the source that is not in the target. This row is never transferred. I have also tried it with an empty target, and nothing is transferred.

    Read the article

  • transaction handling in dataset based insert/update in c#

    - by user3703611
    I am trying to insert bulk records in a sql server database table using dataset. But i am unable to do transaction handling. Please help me to apply transaction handling in below code. I am using adapter.UpdateCommand.Transaction = trans; but this line give me an error of Object reference not set to an instance of an object. Code: string ConnectionString = "server=localhost\\sqlexpress;database=WindowsApp;Integrated Security=SSPI;"; SqlConnection conn = new SqlConnection(ConnectionString); conn.Open(); SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable); SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Test ORDER BY Id", conn); SqlCommandBuilder builder = new SqlCommandBuilder(adapter); adapter.UpdateCommand.Transaction = trans; // Create a dataset object DataSet ds = new DataSet("TestSet"); adapter.Fill(ds, "Test"); // Create a data table object and add a new row DataTable TestTable = ds.Tables["Test"]; for (int i=1;i<=50;i++) { DataRow row = TestTable.NewRow(); row["Id"] = i; TestTable .Rows.Add(row); } // Update data adapter adapter.Update(ds, "Test"); trans.Commit(); conn.Close();

    Read the article

  • DataRelation Insert and ForeignKey

    - by Steve
    Guys, I have a winforms application with two DataGridViews displaying a master-detail relationship from my Person and Address tables. Person table has a PersonID field that is auto-incrementing primary key. Address has a PersonID field that is the FK. I fill my DataTables with DataAdapter and set Person.PersonID column's AutoIncrement=true and AutoIncrementStep=-1. I can insert records in the Person DataTable from the DataGridView. The PersonID column displays unique negative values for PersonID. I update the database by calling DataAdapter.Update(PersonTable) and the negative PersonIDs are converted to positive unique values automatically by SQL Server. Here's the rub. The Address DataGridView show the address table which has a DataRelation to Person by PersonID. Inserted Person records have the temporary negative PersonID. I can now insert records into Address via DataGridView and Address.PersonID is set to the negative value from the DataRelation mapping. I call Adapter.Update(AddressTable) and the negative PersonIDs go into the Address table breaking the relationship. How do you guys handle primary/foreign keys using DataTables and master-detail DataGridViews? Thanks! Steve EDIT: After more googling, I found that SqlDataAdapter.RowUpdated event gives me what I need. I create a new command to query the last id inserted by using @@IDENTITY. It works pretty well. The DataRelation updates the Address.PersonID field for me so it's required to Update the Person table first then update the Address table. All the new records insert properly with correct ids in place! Adapter = new SqlDataAdapter(cmd); Adapter.RowUpdated += (s, e) => { if (e.StatementType != StatementType.Insert) return; //set the id for the inserted record SqlCommand c = e.Command.Connection.CreateCommand(); c.CommandText = "select @@IDENTITY id"; e.Row[0] = Convert.ToInt32( c.ExecuteScalar() ); }; Adapter.Fill(this); SqlCommandBuilder sb = new SqlCommandBuilder(Adapter); sb.GetDeleteCommand(); sb.GetUpdateCommand(); sb.GetInsertCommand(); this.Columns[0].AutoIncrement = true; this.Columns[0].AutoIncrementSeed = -1; this.Columns[0].AutoIncrementStep = -1;

    Read the article

  • radgridview delete update in asp.net

    - by abhi
    i have written the follwing to display data from the datagrid and den insert new rows but how do i perform update and delete plss help here's my code using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using Telerik.Web.UI; using System.Data.SqlClient; public partial class Default6 : System.Web.UI.Page { string strQry, strCon; SqlDataAdapter da; SqlConnection con; DataSet ds; protected void Page_Load(object sender, EventArgs e) { strCon = "Data Source=MINETDEVDATA; Initial Catalog=ML_SuppliersProd; User Id=sa; Password=@MinetApps7;"; con = new SqlConnection(strCon); strQry = "SELECT * FROM table1"; da = new SqlDataAdapter(strQry, con); SqlCommandBuilder cmdbuild = new SqlCommandBuilder(da); ds = new DataSet(); da.Fill(ds, "table1"); RadGrid1.DataSource = ds.Tables["table1"]; RadGrid1.DataBind(); Label3.Visible = false; Label4.Visible = false; Label5.Visible = false; txtFname.Visible = false; txtLname.Visible = false; txtDesignation.Visible = false; } protected void Submit_Click(object sender, EventArgs e) { Label3.Visible = true; Label4.Visible = true; Label5.Visible = true; txtFname.Visible = true; txtLname.Visible = true; txtDesignation.Visible = true; } protected void Button2_Click(object sender, EventArgs e) { DataSet ds = new DataSet("EmployeeSet"); da.Fill(ds, "table1"); DataTable EmployeeTable = ds.Tables["table1"]; DataRow row = EmployeeTable.NewRow(); row["Fname"] = txtFname.Text.ToString(); row["Lname"] = txtLname.Text.ToString(); row["Designation"] = txtDesignation.Text.ToString(); EmployeeTable.Rows.Add(row); da.Update(ds, "table1"); //RadGrid1.DataSource = ds.Tables["table1"]; //RadGrid1.DataBind(); txtFname.Text = ""; txtLname.Text = ""; txtDesignation.Text = ""; } protected void RadGrid1_DeleteCommand(object source, GridCommandEventArgs e) { } } }

    Read the article

  • Binding Data to DataGridView in VB.Net

    - by Peter
    Hi, I have a bit of code which loads data from a stored procedure in MS SQL Server and then loads the data to a datagridview, which works fine. What i want is for the code that connects / loads the data to sit in my Database Class and then everything associated with the datagridview to be stored in my Form but i am having problems passing the contents of the bindingsource over to the Form from the Database Class. Form1 Public Class Form1 Dim myDatabaseObj As New Class1() Dim bindingSource1 As New BindingSource() Dim connectString As New SqlConnection Dim objDataAdapter As New SqlDataAdapter Dim table As New DataTable() Dim tabletest As New DataTable() Private Sub loadCompanyList() Try Me.dgv_CompanyList.DataSource = Me.bindingSource1 getCompanyList() Catch ex As NullReferenceException End Try End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load loadCompanyList() End Sub End Class Database Class When i try place the getCompanyList() in a class and then create a new object that references the Form() it does not seem to return any value from the table to the MyForm.BindingSource1.Datasource meaning my datagridview displays not data. ..... Private Sub getCompanyList() Try Dim myForm as new Form() connect_Transaction_Database() objDataAdapter.SelectCommand = New SqlCommand() objDataAdapter.SelectCommand.Connection = connectString objDataAdapter.SelectCommand.CommandText = "sp_GetCompanyList" objDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure Dim commandBuilder As New SqlCommandBuilder(Me.objDataAdapter) Dim table As New DataTable() table.Locale = System.Globalization.CultureInfo.InvariantCulture Me.objDataAdapter.Fill(table) **MyForm.bindingSource1.DataSource = table** Catch ex As DataException MsgBox(ex.Message) Catch ex As NullReferenceException MsgBox(ex.Message) End Try disconnect_Transaction_Database() End Sub If anyone could help. Thank you. Peter

    Read the article

  • DataGridView in VB.net will not allow me to update

    - by Marc
    I have a datagridview with a dataTable as the dataSource. The user can add new rows to the datadridview, but I dont display the primary key column (for obvious reasons) and set it to .visible = false. When I need to update the information in the datagridview to the database, I use the sqlClient.SqlCommandBuilder to then update the underlying datasource (the dataTable mentioned above). Now, because the hidden column is the primary key, I loop through the datagridview and programically add the required primary key field to each new row that does not already contain a primary key (user added rows). This works great 95% of the time... The problem is when the user somehow gives focus at some point (any point) to that bottom row on the datagridview, below their added rows, that is used to add new rows. The update command gives me an error stating that it cannot insert null into the primary key field, even though when checking all the values in every row, it is definitely NOT null for any of them. I have tried to trap for row.isNewRow (as the field never shows null) and deleting that row, but I get an error stating I can not delete an uncommitted row. If the focus is never given to that empty row beneath the existing rows and user added rows, the update works fine. What is going on?!

    Read the article

  • .Net: What is your confident approach in "Catch" section of try-catch block, When developing CRUD op

    - by odiseh
    hi, I was wondering if there would be any confident approach for use in catch section of try-catch block when developing CRUD operations(specially when you use a Database as your data source) in .Net? well, what's your opinion about below lines? public int Insert(string name, Int32 employeeID, string createDate) { SqlConnection connection = new SqlConnection(); connection.ConnectionString = this._ConnectionString; try { SqlCommand command = connection.CreateCommand(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "UnitInsert"; if (connection.State != ConnectionState.Open) connection.Open(); SqlCommandBuilder.DeriveParameters(command); command.Parameters["@Name"].Value = name; command.Parameters["@EmployeeID"].Value = employeeID; command.Parameters["@CreateDate"].Value = createDate; int i = command.ExecuteNonQuery(); command.Dispose(); return i; } catch { **// how do you "catch" any possible error here?** return 0; // } finally { connection.Close(); connection.Dispose(); connection = null; } }

    Read the article

  • TextBox change is not saved to DataTable

    - by SeaDrive
    I'm having trouble with a simple table edit in a Winform application. I must have missed a step. I have a DataSet containing a DataTable connected to a database with a SqlDataAdapter. There is a SqlCommandBuilder on the SqlDataAdapter. On the form, there are TextBoxes which are bound to the DataTable. The binding was done in the Designer and it machine-produced statements like this: this.tbLast.DataBindings.Add(new System.Windows.Forms.Binding("Text", this.belkData, "belk_mem.last", true)); When I fill the row in the DataTable, the values from the database appear in the textboxes, but when I change contents of the TextBox, the changes are apparently not being going to the DataTable. When I try to save change both of the following return null: DataTable dtChanges = dtMem.GetChanges(); DataSet dsChanges = belkData.GetChanges(); What did I forget? Edit - response to mrlucmorin: The save is under a button. Code is: BindingContext[belkData, "belk_mem"].EndCurrentEdit(); try { DataSet dsChanges = belkData.GetChanges(); if (dsChanges != null) { int nRows = sdaMem.Update(dsChanges); MessageBox.Show("Row(s) Updated: " + nRows.ToString()); belkData.AcceptChanges(); } else { MessageBox.Show("Nothing to save.", "No changes"); } } catch (Exception ex) { MessageBox.Show("Error: " + ex.Message); } I've tried putting in these statements without any change in behavior: dtMem.AcceptChanges(); belkData.AcceptChanges();

    Read the article

  • Sending changes from multiple tables in disconnected dataset to SQLServer...

    - by Stecy
    We have a third party application that accept calls using an XML RPC mechanism for calling stored procs. We send a ZIP-compressed dataset containing multiple tables with a bunch of update/delete/insert using this mechanism. On the other end, a CLR sproc decompress the data and gets the dataset. Then, the following code gets executed: using (var conn = new SqlConnection("context connection=true")) { if (conn.State == ConnectionState.Closed) conn.Open(); try { foreach (DataTable table in ds.Tables) { string columnList = ""; for (int i = 0; i < table.Columns.Count; i++) { if (i == 0) columnList = table.Columns[0].ColumnName; else columnList += "," + table.Columns[i].ColumnName; } var da = new SqlDataAdapter("SELECT " + columnList + " FROM " + table.TableName, conn); var builder = new SqlCommandBuilder(da); builder.ConflictOption = ConflictOption.OverwriteChanges; da.RowUpdating += onUpdatingRow; da.Update(ds, table.TableName); } } catch (....) { ..... } } Here's the event handler for the RowUpdating event: public static void onUpdatingRow(object sender, SqlRowUpdatingEventArgs e) { if ((e.StatementType == StatementType.Update) && (e.Command == null)) { e.Command = CreateUpdateCommand(e.Row, sender as SqlDataAdapter); e.Status = UpdateStatus.Continue; } } and the CreateUpdateCommand method: private static SqlCommand CreateUpdateCommand(DataRow row, SqlDataAdapter da) { string whereClause = ""; string setClause = ""; SqlConnection conn = da.SelectCommand.Connection; for (int i = 0; i < row.Table.Columns.Count; i++) { char quoted; if ((row.Table.Columns[i].DataType == Type.GetType("System.String")) || (row.Table.Columns[i].DataType == Type.GetType("System.DateTime"))) quoted = '\''; else quoted = ' '; string val = row[i].ToString(); if (row.Table.Columns[i].DataType == Type.GetType("System.Boolean")) val = (bool)row[i] ? "1" : "0"; bool isPrimaryKey = false; for (int j = 0; j < row.Table.PrimaryKey.Length; j++) { if (row.Table.PrimaryKey[j].ColumnName == row.Table.Columns[i].ColumnName) { if (whereClause != "") whereClause += " AND "; if (row[i] == DBNull.Value) whereClause += row.Table.Columns[i].ColumnName + "=NULL"; else whereClause += row.Table.Columns[i].ColumnName + "=" + quoted + val + quoted; isPrimaryKey = true; break; } } /* Only values for column that is not a primary key can be modified */ if (!isPrimaryKey) { if (setClause != "") setClause += ", "; if (row[i] == DBNull.Value) setClause += row.Table.Columns[i].ColumnName + "=NULL"; else setClause += row.Table.Columns[i].ColumnName + "=" + quoted + val + quoted; } } return new SqlCommand("UPDATE " + row.Table.TableName + " SET " + setClause + " WHERE " + whereClause, conn); } However, this is really slow when we have a lot of records. Is there a way to optimize this or an entirely different way to send lots of udpate/delete on several tables? I would really much like to use TSQL for this but can't figure a way to send a dataset to a regular sproc. Additional notes: We cannot directly access the SQLServer database. We tried without compression and it was slower.

    Read the article

1