public virtual void InsertUpdateDataTable(string tableSchema, string tableName, System.Data.DataTable dt) { string strSQL = "SELECT * FROM "; if (tableSchema != null) { strSQL += this.QuoteObjectIfNecessary(tableSchema) + "."; } strSQL += this.QuoteObjectIfNecessary(tableName) + " WHERE (1 = 2) "; using (System.Data.Common.DbConnection connection = this.Connection) { using (System.Data.Common.DbDataAdapter daInsertUpdate = this.m_factory.CreateDataAdapter()) { using (System.Data.Common.DbCommand cmdSelect = connection.CreateCommand()) { cmdSelect.CommandText = strSQL; System.Data.Common.DbCommandBuilder cb = this.m_factory.CreateCommandBuilder(); cb.DataAdapter = daInsertUpdate; daInsertUpdate.SelectCommand = cmdSelect; daInsertUpdate.InsertCommand = cb.GetInsertCommand(); daInsertUpdate.UpdateCommand = cb.GetUpdateCommand(); daInsertUpdate.DeleteCommand = cb.GetDeleteCommand(); daInsertUpdate.Update(dt); } // End Using cmdSelect } // End Using daInsertUpdate } // End Using connection } // End Sub InsertUpdateDataTable
/// <summary> /// Persists on the database the datatable changes. /// </summary> public void Update() { bool exception = false; DateTime sqlTime = DateTime.Now; try { if (_ActiveConnection.State == ConnectionState.Closed) { Helper.OpenConnection(_ActiveConnection); } if (_Ad.DeleteCommand != null) { _Ad.DeleteCommand.CommandTimeout = _Cmd.CommandTimeout; } if (_Ad.InsertCommand != null) { _Ad.InsertCommand.CommandTimeout = _Cmd.CommandTimeout; } if (_Ad.UpdateCommand != null) { _Ad.UpdateCommand.CommandTimeout = _Cmd.CommandTimeout; } sqlTime = DateTime.Now; _Ad.Update(this); } catch (Exception up) { throw (up); } finally { try { if (exception) { System.Diagnostics.Trace.TraceInformation("DataTable.Update Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec. (SqlException)"); } else { System.Diagnostics.Trace.TraceInformation("DataTable.Update Time: " + (DateTime.Now - sqlTime).TotalSeconds.ToString() + " sec."); } } catch { } if (_ActiveConnection.State != ConnectionState.Closed) { _ActiveConnection.Close(); } } }
/// <summary> /// Write Data Table /// </summary> /// <param name="pFileString">File String</param> /// <param name="pSelectSQL">Select statement</param> /// <param name="pDestinationTableName">Destination Table name</param> /// <param name="pDatatable">Data Table</param> /// <returns></returns> public static bool WriteDataTable(string pFileString, string pSelectSQL, string pDestinationTableName, DataTable pDatatable) { bool result = false; System.Data.Common.DbDataAdapter Adapter = null; System.Data.Common.DbCommandBuilder builder = null; DataSet dataSet = new DataSet(); string connString = ParseConnectionString(pFileString); if (DataSource != null) { IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder()); driver.ConnectionString = connString; Adapter = driver.GetDbAdapter(pSelectSQL); builder = driver.GetDbCommandBuilder(Adapter); } //code to modify data in DataSet here builder.GetInsertCommand(); //Without the SqlCommandBuilder this line would fail Adapter.Update(dataSet, pDestinationTableName); /* * OleDbCommand insertCommand = dataAdapter.InsertCommand; * foreach (DataRow row in origTable.Rows) * { * foreach (OleDbParameter param in insertCommand.Parameters) * { * param.Value = row[param.ParameterName]; * } * insertCommand->ExecuteNonQuery(); * } */ result = true; return(result); }
public static bool UpdateDataSource(System.Data.DataTable data, System.Collections.Specialized.ListDictionary addressbook, anmar.SharpWebMail.IEmailClient client) { bool error = false; if (data == null || addressbook == null || !addressbook.Contains("connectionstring") || !addressbook.Contains("searchstring") || !addressbook.Contains("allowupdate") || !((bool)addressbook["allowupdate"])) { return(false); } System.String connectstring = addressbook["connectionstring"].ToString(); System.String connectusername = null, connectpassword = null; if (addressbook.Contains("connectionusername") && addressbook.Contains("connectionpassword")) { connectusername = addressbook["connectionusername"].ToString(); connectpassword = addressbook["connectionpassword"].ToString(); } else if (client != null) { connectusername = client.UserName; connectpassword = client.Password; } System.String searchfilter = addressbook["searchstring"].ToString(); if (client != null) { searchfilter = searchfilter.Replace("$USERNAME$", client.UserName); } else { searchfilter = searchfilter.Replace("$USERNAME$", System.String.Empty); } System.Data.Common.DbDataAdapter adapter = GetDataAdapter(addressbook["type"].ToString(), connectstring, connectusername, connectpassword, searchfilter); if (adapter != null) { try { if (addressbook["type"].Equals("odbc")) { System.Data.Odbc.OdbcCommandBuilder builder = new System.Data.Odbc.OdbcCommandBuilder(adapter as System.Data.Odbc.OdbcDataAdapter); adapter.Update(data); builder = null; } else if (addressbook["type"].Equals("oledb")) { System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(adapter as System.Data.OleDb.OleDbDataAdapter); adapter.Update(data); builder = null; } } catch (System.Exception e) { if (log.IsErrorEnabled) { log.Error(System.String.Concat("Error updating address book [", addressbook["name"], "] for user [", client.UserName, "]"), e); } error = true; } } else { error = true; } adapter = null; return(!error); }