/// <summary> /// Insert Data /// </summary> /// <param name="pFileString">File String</param> /// <param name="pSelectSQL">Select SQL</param> /// <param name="pDataReader">Data Reader</param> /// <returns>bool</returns> public static bool InsertData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader) { bool result = false; System.Data.Common.DbConnection Conn = null; System.Data.Common.DbDataAdapter Adapter = null; System.Data.Common.DbCommandBuilder builder = null; System.Data.Common.DbCommand cmd = null; DataSet dataSet = new DataSet(); DataTable Temp = new DataTable(); string connString = ParseConnectionString(pFileString); if (DataSource != null) { IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder()); driver.ConnectionString = connString; Conn = (System.Data.Common.DbConnection)driver.GetConnection(); Adapter = driver.GetDbAdapter(pSelectSQL); Adapter.FillSchema(dataSet, SchemaType.Source); Adapter.Fill(Temp); builder = driver.GetDbCommandBuilder(Adapter); builder.QuotePrefix = "["; builder.QuoteSuffix = "]"; try { Conn.Open(); cmd = builder.GetInsertCommand(true); cmd.CommandTimeout = 1500; cmd.Connection = Conn; //builder.GetInsertCommand((true); while (pDataReader.Read()) { foreach (System.Data.Common.DbParameter param in cmd.Parameters) { //string FieldName = param.ParameterName.TrimStart(new char[] { '@' }); string FieldName = param.SourceColumn; if (FieldName.ToUpperInvariant() == "UNIQUEKEY") { param.Value = null; } else { param.Value = pDataReader[FieldName]; } } cmd.ExecuteNonQuery(); } } finally { Conn.Close(); } } result = true; return(result); }
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> /// Insert Data /// </summary> /// <param name="pFileString">File String</param> /// <param name="pSelectSQL">Select Statement</param> /// <param name="pDataRows">DataRows</param> /// <returns>bool</returns> public static bool InsertData(string pFileString, string pSelectSQL, System.Data.DataRow[] pDataRows) { bool result = false; System.Data.Common.DbConnection Conn = null; System.Data.Common.DbDataAdapter Adapter = null; System.Data.Common.DbCommandBuilder builder = null; System.Data.Common.DbCommand cmd = null; DataSet dataSet = new DataSet(); DataTable Temp = new DataTable(); string connString = ParseConnectionString(pFileString); if (DataSource != null) { IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder()); driver.ConnectionString = connString; Conn = (System.Data.Common.DbConnection)driver.GetConnection(); Adapter = driver.GetDbAdapter(pSelectSQL); Adapter.FillSchema(dataSet, SchemaType.Source); Adapter.Fill(Temp); builder = driver.GetDbCommandBuilder(Adapter); builder.QuotePrefix = "["; builder.QuoteSuffix = "]"; try { Conn.Open(); //cmd = builder.GetInsertCommand(true); cmd = Conn.CreateCommand(); cmd.CommandTimeout = 1500; //builder.GetInsertCommand((true); foreach (DataRow R in pDataRows) { //string SQL = GetInsertSQL(pSelectSQL.Substring(pSelectSQL.LastIndexOf (" From ") + 6, pSelectSQL.Length - pSelectSQL.LastIndexOf(" From ") - 6),R); string SQL = GetInsertSQL(R.Table.TableName, R); cmd.CommandText = SQL; cmd.ExecuteNonQuery(); } } finally { Conn.Close(); } } result = true; return(result); }
public override System.Data.Common.DbCommandBuilder CreateDbCommandBuilder(System.Data.Common.DbDataAdapter adapter) { Type type = _Assembly.GetType("Oracle.DataAccess.Client.OracleCommandBuilder"); System.Data.Common.DbCommandBuilder builder = Activator.CreateInstance(type) as System.Data.Common.DbCommandBuilder; if (builder != null) { builder.DataAdapter = adapter; } //if (builder == null) // throw new ApplicationException("动态创建DbCommandBuilder失败"); return(builder); }
public string UpdateRowToDatabase(DataTable tb, bool WithoutBinding) { if (WithoutBinding) { return(""); } try { System.Data.Common.DbCommandBuilder cmdbldr = DbCurrent.GetCommandBuilder(Adapter); Adapter.Update(tb); return(""); } catch (Exception ex) { return(ex.Message); } }
public bool UpdateData(DataSet ds, string TableName) { using (MySqlConnection cnn = DataConnection) { MySqlCommand cmd = new MySqlCommand("Select * From " + TableName, cnn); using (MySqlDataAdapter da = new MySqlDataAdapter(cmd)) { System.Data.Common.DbCommandBuilder cb = MySqlClientFactory.Instance.CreateCommandBuilder(); cb.DataAdapter = da; cb.ConflictOption = ConflictOption.OverwriteChanges; cb.QuotePrefix = "`"; cb.QuoteSuffix = "`"; return(da.Update(ds, TableName) > 0); } } }
/// <summary> /// Update Data /// </summary> /// <param name="pFileString">File String</param> /// <param name="pSelectSQL">Select Statement</param> /// <param name="pDataReader">DataReader</param> /// <returns>bool</returns> public static bool UpdateData(string pFileString, string pSelectSQL, System.Data.Common.DbDataReader pDataReader) { bool result = false; System.Data.Common.DbConnection Conn = null; System.Data.Common.DbDataAdapter Adapter = null; System.Data.Common.DbCommandBuilder builder = null; System.Data.Common.DbCommand cmd = null; DataSet dataSet = new DataSet(); DataTable Temp = new DataTable(); string connString = ParseConnectionString(pFileString); if (DataSource != null) { IDbDriver driver = DataSource.CreateDatabaseObject(new System.Data.Common.DbConnectionStringBuilder()); driver.ConnectionString = connString; Conn = (System.Data.Common.DbConnection)driver.GetConnection(); Adapter = driver.GetDbAdapter(pSelectSQL); Adapter.FillSchema(dataSet, SchemaType.Source); Adapter.Fill(Temp); builder = driver.GetDbCommandBuilder(Adapter); } try { Conn.Open(); cmd = builder.GetInsertCommand(true); cmd.CommandTimeout = 1500; while (pDataReader.Read()) { foreach (System.Data.Common.DbParameter param in cmd.Parameters) { param.Value = pDataReader[param.SourceColumn]; } cmd.ExecuteNonQuery(); } } finally { Conn.Close(); } result = true; return(result); }
public bool UpdateData(DataSet ds, string TableName, MySqlTransaction ts) { // Dim da As DbDataAdapter = db.DbProviderFactory.CreateDataAdapter() var cmd = new MySqlCommand("Select * From " + TableName, ts.Connection); cmd.Transaction = ts; using (var da = new MySqlDataAdapter(cmd)) { System.Data.Common.DbCommandBuilder cb = MySqlClientFactory.Instance.CreateCommandBuilder(); cb.DataAdapter = da; cb.ConflictOption = ConflictOption.OverwriteChanges; cb.QuotePrefix = "`"; cb.QuoteSuffix = "`"; return(da.Update(ds, TableName) > 0); } }
/// <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); }
private void Base_InitializeCommandBuilder(ref System.Data.Common.DbCommandBuilder Value) { Value = new System.Data.SQLite.SQLiteCommandBuilder(); }