public void UpdateRegistrazioneDS(string tablename, RilevazioniDS ds) { string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", tablename); using (DbDataAdapter a = BuildDataAdapter(query)) { a.ContinueUpdateOnError = false; DataTable dt = ds.Tables[tablename]; DbCommandBuilder cmd = BuildCommandBuilder(a); a.UpdateCommand = cmd.GetUpdateCommand(); a.DeleteCommand = cmd.GetDeleteCommand(); a.InsertCommand = cmd.GetInsertCommand(); a.Update(dt); } }
public DatabaseReporter(DbProviderFactory factory, DbConnection connection) { this.connection = connection; watch = System.Diagnostics.Stopwatch.StartNew(); DbCommand command = factory.CreateCommand(); command.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "output"; command.Connection = connection; kopadapter = factory.CreateDataAdapter(); kopadapter.SelectCommand = command; DbCommandBuilder builder = factory.CreateCommandBuilder(); builder.ConflictOption = ConflictOption.OverwriteChanges; builder.DataAdapter = kopadapter; // Wanneer : Additional information: De Microsoft.Jet.OLEDB.4.0-provider is niet geregistreerd op de lokale computer. // ==> draaien als x86 kopadapter.InsertCommand = builder.GetInsertCommand(); kopadapter.UpdateCommand = builder.GetUpdateCommand(); kopadapter.DeleteCommand = builder.GetDeleteCommand(); command = factory.CreateCommand(); command.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "outputline"; command.Connection = connection; regeladapter = factory.CreateDataAdapter(); regeladapter.SelectCommand = command; builder = factory.CreateCommandBuilder(); builder.ConflictOption = ConflictOption.OverwriteChanges; builder.DataAdapter = regeladapter; regeladapter.InsertCommand = builder.GetInsertCommand(); regeladapter.UpdateCommand = builder.GetUpdateCommand(); regeladapter.DeleteCommand = builder.GetDeleteCommand(); }
public virtual bool UpDateTable(DataTable table, string tableName) { DbCommand command = GetDbCommand(); command.CommandText = string.Format("SELECT * FROM [{0}]", tableName); command.Connection = GetDbConnection(); DbDataAdapter adapter = GetDbDataAdapter(command); DbCommandBuilder builder = GetDbCommandBuilder(adapter); adapter.InsertCommand = builder.GetInsertCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); int cou = adapter.Update(table); return(cou > 0); }
public bool SaveDataTable(DataTable dataTable, string tableName) { string sql = string.Format("select * from {0}", tableName); DbDataAdapter sda = CurrentDBConnection.CreateDbDataAdapter(sql, GetConnection()); DbCommandBuilder scb = CurrentDBConnection.CreateCommandBuilder(); sda.InsertCommand = scb.GetInsertCommand(); sda.UpdateCommand = scb.GetUpdateCommand(); sda.DeleteCommand = scb.GetDeleteCommand(); sda.Fill(dataTable); var r = sda.Update(dataTable); return(true); }
private void frmshirt_Load(object sender, EventArgs e) { DbCommand idbCommand = Connection.provideConnection().CreateCommand(); idbCommand.CommandText = "select * from `shirt`"; ad = returnAdapter(); DbCommandBuilder builder = returnBuilder(); builder.DataAdapter = ad; ad.SelectCommand = idbCommand; ad.Fill(this.newDataSet.shirt); ad.DeleteCommand = builder.GetDeleteCommand(); ad.UpdateCommand = builder.GetUpdateCommand(); ad.InsertCommand = builder.GetInsertCommand(); MySqlDataAdapter ad3; }
/// <summary> /// 更新table到数据库 /// </summary> /// <param name="dt">要更新的表</param> /// <param name="sql">要执行的查询语句</param> /// <returns></returns> public int UpdateTable(DataTable dt, string sql) { ErrorMessage = ""; int affect = 0; DbConnection connection = providerFactory.CreateConnection(); using (DbCommand command = CreateDbCommand(sql, null, CommandType.Text)) { using (DbDataAdapter adapter = providerFactory.CreateDataAdapter()) { command.CommandType = CommandType.Text; //command.CommandText = sql; command.CommandText = dt.ExtendedProperties["SQL"].ToString(); adapter.SelectCommand = command; adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; using (DbCommandBuilder cb = providerFactory.CreateCommandBuilder()) { try { cb.DataAdapter = adapter; adapter.InsertCommand = cb.GetInsertCommand(); adapter.UpdateCommand = cb.GetUpdateCommand(); adapter.DeleteCommand = cb.GetDeleteCommand(); adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None; adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None; adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None; command.Connection.Open(); if (dt.GetChanges() != null) { affect = adapter.Update(dt.GetChanges()); dt.AcceptChanges(); } command.Connection.Close(); } catch (Exception ex) { ErrorMessage = ex.Message; affect = -1; } } } } return(affect); }
//Erzeugt einen DbDataAdapter für die Verbindung und den SQL-Befehl //Benötigt eine DbProviderFactory, Eine Verbindung und ein SQL-Befehl public static DbDataAdapter CreateDbDataAdapter(DbProviderFactory dbProviderFactory, DbConnection dbConnection, string sql) { DbCommand dbSelectCommand = dbProviderFactory.CreateCommand(); dbSelectCommand.Connection = dbConnection; dbSelectCommand.CommandText = sql; DbDataAdapter dbDataAdapter = dbProviderFactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = dbSelectCommand; DbCommandBuilder dbCommandBuilder = dbProviderFactory.CreateCommandBuilder(); dbCommandBuilder.DataAdapter = dbDataAdapter; dbDataAdapter.InsertCommand = dbCommandBuilder.GetInsertCommand(); dbDataAdapter.UpdateCommand = dbCommandBuilder.GetUpdateCommand(); dbDataAdapter.DeleteCommand = dbCommandBuilder.GetDeleteCommand(); return(dbDataAdapter); }
private void PerformDelete(DataSet pDs, List <string> pTableOrder) { foreach (string MyTableName in pTableOrder) { DataRow[] MyRows = pDs.Tables[MyTableName].Select("", "", DataViewRowState.Deleted); if (MyRows.Length > 0) { m_CurrentUpdating = MyTableName; DbDataAdapter MyDa = CreateDataAdapter(pDs, MyTableName); DbCommandBuilder MyCB = CreateCommandBuilder(MyDa); MyDa.DeleteCommand = MyCB.GetDeleteCommand(); MyDa.DeleteCommand.Transaction = m_Trans; MyDa.UpdateBatchSize = m_BatchSize; MyDa.Update(MyRows); } } }
public void UpdateDistintaBaseTable(ArticoliDS ds) { string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", ds.DIBA.TableName); using (DbDataAdapter a = BuildDataAdapter(query)) { InstallRowUpdatedHandler(a, UpdateDistintaBaseHander); a.ContinueUpdateOnError = false; DataTable dt = ds.DIBA; DbCommandBuilder cmd = BuildCommandBuilder(a); a.AcceptChangesDuringFill = true; a.UpdateCommand = cmd.GetUpdateCommand(); a.DeleteCommand = cmd.GetDeleteCommand(); a.InsertCommand = cmd.GetInsertCommand(); a.Update(dt); } }
/// <summary> /// 批量更新 /// </summary> /// <param name="dt"></param> /// <returns></returns> public bool UpdateDataTable(DataTable dt, string selectSql) { bool resule = true; if (dt == null || dt.Rows.Count <= 0) { return(resule); } this.Open(); DbTransaction trans = connection.BeginTransaction(); try { DbProviderFactory dbfactory = GetFactory(); DbCommand cmd = connection.CreateCommand(); cmd.CommandText = selectSql; DbDataAdapter dr = dbfactory.CreateDataAdapter(); dr.SelectCommand = cmd; dr.SelectCommand.Transaction = trans; DbCommandBuilder builder = dbfactory.CreateCommandBuilder(); builder.QuotePrefix = "["; builder.QuoteSuffix = "]"; builder.ConflictOption = ConflictOption.OverwriteChanges; builder.SetAllValues = false; builder.DataAdapter = dr; dr.InsertCommand = builder.GetInsertCommand(); dr.UpdateCommand = builder.GetUpdateCommand(); dr.DeleteCommand = builder.GetDeleteCommand(); resule = dr.Update(dt) > 0; // dt.AcceptChanges(); trans.Commit(); return(resule); } catch (Exception ex) { trans.Rollback(); throw new Exception(ex.Message); } finally { this.Close(); } }
public void UpdateTableSPControlli(SchedeProcessoDS ds) { string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", ds.SPCONTROLLI.TableName); using (DbDataAdapter a = BuildDataAdapter(query)) { InstallRowUpdatedHandler(a, UpdateSPCOntrolloHander); a.ContinueUpdateOnError = false; DataTable dt = ds.SPCONTROLLI; DbCommandBuilder cmd = BuildCommandBuilder(a); a.AcceptChangesDuringFill = true; a.UpdateCommand = cmd.GetUpdateCommand(); a.DeleteCommand = cmd.GetDeleteCommand(); a.InsertCommand = cmd.GetInsertCommand(); a.Update(dt); } }
/// <summary> /// Gets the adapter. /// </summary> /// <param name="factory">The factory.</param> /// <param name="connectionConfiguration">The connection configuration.</param> /// <param name="query">The query.</param> /// <returns></returns> /// <exception cref="System.ArgumentNullException">factory;The expected provider factory is not here.</exception> /// <exception cref="System.ArgumentException"> /// The DBMS Connection string was not specified. /// or /// The DBMS query was not specified. /// </exception> public static DbDataAdapter GetAdapter(DbProviderFactory factory, string connectionConfiguration, string query) { if (factory == null) { throw new ArgumentNullException("factory", "The expected provider factory is not here."); } if (string.IsNullOrEmpty(connectionConfiguration)) { throw new ArgumentException("The DBMS Connection string was not specified."); } if (string.IsNullOrEmpty(query)) { throw new ArgumentException("The DBMS query was not specified."); } DbDataAdapter adapter = factory.CreateDataAdapter(); if (string.IsNullOrEmpty(connectionConfiguration) && string.IsNullOrEmpty(query)) { return(adapter); } DbConnection connection = factory.CreateConnection(); connection.ConnectionString = connectionConfiguration; DbCommand selectCommand = factory.CreateCommand(); selectCommand.CommandText = query; selectCommand.Connection = connection; DbCommandBuilder builder = factory.CreateCommandBuilder(); builder.DataAdapter = adapter; adapter.SelectCommand = selectCommand; adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.InsertCommand = builder.GetInsertCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); return(adapter); }
static void Main(string[] args) { //DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OracleClient"); DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection connection = factory.CreateConnection(); connection.ConnectionString = SQL_CONN; using (connection) { //runClassicSelect(connection, factory); string sql = "SELECT id, name FROM People"; DbCommand command = connection.CreateCommand(); command.CommandText = sql; DbDataAdapter adapter = factory.CreateDataAdapter(); adapter.SelectCommand = command; DbCommandBuilder builder = factory.CreateCommandBuilder(); builder.DataAdapter = adapter; adapter.InsertCommand = builder.GetInsertCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); DataTable people = new DataTable("People"); adapter.Fill(people); print(people); people.Rows.Add(23, "Albert"); print(people); Console.ReadKey(); adapter.Update(people); foreach (DataRow row in people.Select("id = 23")) { row.Delete(); } print(people); Console.ReadKey(); adapter.Update(people); } Console.ReadKey(); }
protected virtual DbDataAdapter CreateDbDataAdapter(string tableName) { DbCommand dbSelectCommand = _aData.ProviderFactory.CreateCommand(); dbSelectCommand.Connection = _dbConnection; dbSelectCommand.CommandText = string.Format("SELECT * FROM {0}", tableName); DbDataAdapter dbDataAdapter = _aData.ProviderFactory.CreateDataAdapter(); dbDataAdapter.SelectCommand = dbSelectCommand; // --- fertig, wenn keine schreibenden Datenbankzugriffe erforderlich ---- DbCommandBuilder dbCommandBuilder = _aData.ProviderFactory.CreateCommandBuilder(); dbCommandBuilder.DataAdapter = dbDataAdapter; dbDataAdapter.InsertCommand = dbCommandBuilder.GetInsertCommand(); dbDataAdapter.UpdateCommand = dbCommandBuilder.GetUpdateCommand(); dbDataAdapter.DeleteCommand = dbCommandBuilder.GetDeleteCommand(); return(dbDataAdapter); }
public void UpdateTable(DataSet dataSet) { int iAffected = 0; try { ObjDataAdapter.SelectCommand.CommandText = MstrQuery; ObjCommand.CommandType = CommandType.StoredProcedure; ObjCommandBuilder.DataAdapter = ObjDataAdapter; ObjDataAdapter.InsertCommand = ObjCommandBuilder.GetInsertCommand(); ObjDataAdapter.UpdateCommand = ObjCommandBuilder.GetUpdateCommand(); ObjDataAdapter.DeleteCommand = ObjCommandBuilder.GetDeleteCommand(); iAffected = ObjDataAdapter.Update(dataSet); } catch (Exception ex) { throw new SystemException(ex.Message, ex); } }
internal TableManager(string tableName) { try { _da = TableManager._DbProviderFactory.CreateDataAdapter(); _cmd = TableManager._DbProviderFactory.CreateCommand(); DbCommandBuilder cb = TableManager._DbProviderFactory.CreateCommandBuilder(); _cmd.Connection = TableManager.Connection; cb.ConflictOption = ConflictOption.OverwriteChanges; cb.DataAdapter = _da; _dt = new DataTable(); _temp = new DataTable(); _dt.TableName = _temp.TableName = tableName; _cmd.CommandText = "Select * from Voll_" + Table.TableName; _da.SelectCommand = _cmd; _da.InsertCommand = cb.GetInsertCommand(); _da.DeleteCommand = cb.GetDeleteCommand(); _da.UpdateCommand = cb.GetUpdateCommand(); Recharge("1 = 2"); } catch { } }
/// <summary> /// 保持数据表 /// </summary> /// <param name="dtSource">数据源 (数据源中的表名必须是实际的数据表名)</param> /// <param name="conn"></param> /// <param name="tran"></param> public static void UpdateDataTable(DataTable dtSource, DbConnection conn, DbTransaction tran) { if (dtSource == null || conn == null || tran == null) { return; } try { string sqlCmd = string.Format("SELECT * FROM [{0}]", dtSource.TableName); DbCommand dbCommand = CreateCommand(sqlCmd, conn, tran); DbDataAdapter adapter = CreateAdapter(dbCommand); DbCommandBuilder cmdBuilder = CreateCommandBuilder(adapter); adapter.DeleteCommand = cmdBuilder.GetDeleteCommand(); adapter.InsertCommand = cmdBuilder.GetInsertCommand(); adapter.UpdateCommand = cmdBuilder.GetUpdateCommand(); adapter.Update(dtSource); } catch (Exception e) { throw e; } }
/// <summary> /// 生成Command /// </summary> /// <param name="commandText"></param> /// <returns></returns> /// <summary> /// 生成DbDataAdapter /// </summary> /// <param name="selectCommand"></param> /// <param name="type"></param> /// <returns></returns> public IDbDataAdapter CreateDbDataAdapter(IDbCommand selectCommand, DbCommandType type) { var adapt = DbProviderFactory.CreateDataAdapter(); adapt.SelectCommand = selectCommand as DbCommand; DbCommandBuilder builder = DbProviderFactory.CreateCommandBuilder(); builder.DataAdapter = adapt; adapt.InsertCommand = ((type & DbCommandType.InsertCommand) == DbCommandType.InsertCommand) ? builder.GetInsertCommand() : adapt.InsertCommand; adapt.UpdateCommand = ((type & DbCommandType.UpdateCommand) == DbCommandType.UpdateCommand) ? builder.GetUpdateCommand() : adapt.UpdateCommand; adapt.DeleteCommand = ((type & DbCommandType.DeleteCommand) == DbCommandType.DeleteCommand) ? builder.GetDeleteCommand() : adapt.DeleteCommand; adapt.SelectCommand = ((type & DbCommandType.SelectCommand) == DbCommandType.SelectCommand) ? adapt.SelectCommand : null; return(adapt); }
public int Update(DataTable dt) { if (String.IsNullOrEmpty(dt.TableName)) { throw new Exception("没有表名"); } //貌似能自动打开释放连接 DbDataAdapter adapter = null; DataTable tempDt = GetDataTable(dt.TableName, 1); //为了获取表的列数 if (dt.PrimaryKey.Length == 0 || (tempDt.Columns.Count != dt.Columns.Count)) //如果没有主键或者传入的表列不完整则调用自定义的构造 { adapter = CreateAdapter(dt); } else { DbCommand cmd = this.DbProviderFactoryInstance.CreateCommand(); cmd.CommandText = "select * from " + dt.TableName; adapter = this.DbProviderFactoryInstance.CreateDataAdapter(); adapter.SelectCommand = cmd; DbCommandBuilder commandBuilder = this.DbProviderFactoryInstance.CreateCommandBuilder(); commandBuilder.DataAdapter = adapter; adapter.DeleteCommand = commandBuilder.GetDeleteCommand(true); adapter.InsertCommand = commandBuilder.GetInsertCommand(true); adapter.UpdateCommand = commandBuilder.GetUpdateCommand(true); } adapter.InsertCommand.CommandText = adapter.InsertCommand.CommandText; using (TransactionScope ts = new TransactionScope()) { int result = adapter.Update(dt); ts.Complete(); dt.AcceptChanges(); return(result); } }
public DbDataAdapter create_synch_adapter(string sel_sql, string qte_prefix = "[", string qte_suff = "]", bool only_insert = false , DbCommand del_cmd = null, DbCommand upd_cmd = null) { DbCommand cmd_upd = null, cmd_del = null; DbCommandBuilder cmdb = null; try { DbCommand cc = _conn.CreateCommand(); cc.CommandText = sel_sql; cc.CommandType = CommandType.Text; DbProviderFactory fctry = DbProviderFactories.GetFactory(_prov_name); DbDataAdapter da = fctry.CreateDataAdapter(); da.SelectCommand = cc; cmdb = fctry.CreateCommandBuilder(); cmdb.DataAdapter = da; cmdb.QuotePrefix = qte_prefix; cmdb.QuoteSuffix = qte_suff; if (!only_insert) { cmd_upd = upd_cmd != null ? upd_cmd : cmdb.GetUpdateCommand(); da.UpdateCommand = cmd_upd; } if (!only_insert) { cmd_del = del_cmd != null ? del_cmd : cmdb.GetDeleteCommand(); da.DeleteCommand = cmd_del; } da.InsertCommand = cmdb.GetInsertCommand(); return(da); } catch (Exception ex) { logInfo("command builder: " + obj_dump.dump(cmdb)); logInfo("update command: " + obj_dump.dump(cmd_upd)); logInfo("delete command: " + obj_dump.dump(cmd_del)); logErr(ex); throw ex; } finally { } }
/// <summary> /// 更新单张表,无需添加事务 /// </summary> /// <param name="dt"></param> /// <returns></returns> public int Update(DataTable dt) { using (DbConnection connection = provider.CreateConnection()) { connection.ConnectionString = connectionString; using (DbCommand cmd = provider.CreateCommand()) { string relTableName = dt.TableName; string SQLString = string.Format("select * from {0} ", relTableName); cmd.Connection = connection; cmd.CommandText = SQLString; try { DbDataAdapter adapter = provider.CreateDataAdapter(); DbCommandBuilder objCommandBuilder = provider.CreateCommandBuilder(); adapter.SelectCommand = cmd; objCommandBuilder.DataAdapter = adapter; adapter.DeleteCommand = objCommandBuilder.GetDeleteCommand(); adapter.InsertCommand = objCommandBuilder.GetInsertCommand(); adapter.UpdateCommand = objCommandBuilder.GetUpdateCommand(); foreach (DataRow Row in dt.Rows) { Row.EndEdit(); } int count = adapter.Update(dt); dt.AcceptChanges(); return(count); } catch (DbException ex) { connection.Close(); connection.Dispose(); throw new Exception(ex.Message); } } } }
public void UpdateComponentiTable(string tablename, DataRow[] drs) { try { string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", tablename); using (DbDataAdapter a = BuildDataAdapter(query)) { InstallRowUpdatedHandler(a, UpdateComponentiHander); a.ContinueUpdateOnError = false; DbCommandBuilder cmd = BuildCommandBuilder(a); a.AcceptChangesDuringFill = true; a.UpdateCommand = cmd.GetUpdateCommand(); a.DeleteCommand = cmd.GetDeleteCommand(); a.InsertCommand = cmd.GetInsertCommand(); a.Update(drs); } } catch (Exception ex) { throw ex; } }
public void VerwijderCursussen(List <int> ids) { string query = "SELECT * FROM dbo.cursus"; DataSet ds = new DataSet(); DbConnection connection = getConnection(); using (DbDataAdapter adapter = sqlFactory.CreateDataAdapter()) { try { DbCommandBuilder builder = sqlFactory.CreateCommandBuilder(); builder.DataAdapter = adapter; adapter.SelectCommand = sqlFactory.CreateCommand(); adapter.SelectCommand.CommandText = query; adapter.SelectCommand.Connection = connection; adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.FillSchema(ds, SchemaType.Source, "cursus"); adapter.Fill(ds, "cursus"); foreach (int id in ids) { DataRow r = ds.Tables["cursus"].Rows.Find(id); r.Delete(); } adapter.Update(ds, "cursus"); } catch (Exception ex) { Console.WriteLine(ex); } finally { connection.Close(); } } }
public bool runQuery(string target, string queryType, Dictionary <string, string[]> values, string condition = "") { try { DbProviderFactory factory = DbProviderFactories.GetFactory(DbProvider); DbConnection conn = factory.CreateConnection(); DataTable table = new DataTable(); conn.ConnectionString = connectionStringPrime; using (conn) { string queryString = "SELECT * FROM " + target; DbCommand dbcommand = factory.CreateCommand(); dbcommand.CommandText = queryString; dbcommand.Connection = conn; DbDataAdapter adapter = factory.CreateDataAdapter(); adapter.SelectCommand = dbcommand; DbCommandBuilder builder = factory.CreateCommandBuilder(); builder.DataAdapter = adapter; adapter.InsertCommand = builder.GetInsertCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.Fill(table); if (queryType == "INSERT") { DataRow newRow = table.NewRow(); foreach (var item in values) { if (item.Value[0] == "STRING") { newRow[item.Key] = item.Value[1]; } else if (item.Value[0] == "DOUBLE") { newRow[item.Key] = Convert.ToDouble(item.Value[1]); } else if (item.Value[0] == "INT") { newRow[item.Key] = Convert.ToInt32(item.Value[1]); } else if (item.Value[0] == "DATETIME") { newRow[item.Key] = Convert.ToDateTime(item.Value[1]); } else if (item.Value[0] == "BOOL") { newRow[item.Key] = Convert.ToBoolean(item.Value[1]); } } table.Rows.Add(newRow); adapter.Update(table); } else if (queryType == "UPDATE") { DataRow[] editRow = table.Select(condition); int length = editRow.Length; for (int i = 0; i < length; i++) { foreach (var item in values) { if (item.Value[0] == "STRING") { editRow[i][item.Key] = item.Value[1]; } else if (item.Value[0] == "DOUBLE") { editRow[i][item.Key] = Convert.ToDouble(item.Value[1]); } else if (item.Value[0] == "INT") { editRow[i][item.Key] = Convert.ToInt32(item.Value[1]); } else if (item.Value[0] == "DATETIME") { editRow[i][item.Key] = Convert.ToDateTime(item.Value[1]); } else if (item.Value[0] == "BOOL") { editRow[i][item.Key] = Convert.ToBoolean(item.Value[1]); } } } adapter.Update(table); } else if (queryType == "DELETE") { DataRow[] deleteRow = table.Select(condition); foreach (DataRow row in deleteRow) { row.Delete(); } adapter.Update(table); } } return(true); } catch (Exception ex) { ErrorHandler e = new ErrorHandler(ex); e.PrintError(); return(false); } }
public DataTable dynamicQueries <T>(string queryType, object objectClass, KeyValuePair <string, string[, ]> table) { //return dalk n Datatable. As dit insert, delete, of update is, return null DataTable returnTable = null; try { StringBuilder queryString = new StringBuilder(); queryString.Append("SELECT "); for (int i = 0; i < table.Value.GetLength(0); i++) { if (i != table.Value.GetLength(0) - 1) { queryString.Append(table.Value[i, 0] + ", "); } else { queryString.Append(table.Value[i, 0] + " "); } } queryString.Append("FROM "); queryString.Append(table.Key); DbProviderFactory factory = DbProviderFactories.GetFactory(providerName); DbConnection connection = factory.CreateConnection(); connection.ConnectionString = connectionString; // Create the DbCommand. DbCommand command = factory.CreateCommand(); command.CommandText = queryString.ToString(); command.Connection = connection; DbDataAdapter adapter = factory.CreateDataAdapter(); adapter.SelectCommand = command; if (queryType == "SELECT") { // Fill the DataTable. returnTable = new DataTable(); adapter.Fill(returnTable); return(returnTable); } if (queryType != "SELECT") { // Create the DbCommandBuilder. DbCommandBuilder builder = factory.CreateCommandBuilder(); builder.DataAdapter = adapter; // Cast it to itself T spesificClass = (T)Convert.ChangeType(objectClass, typeof(T)); if (queryType == "INSERT") { // Get the insert commands. adapter.InsertCommand = builder.GetInsertCommand(); System.Diagnostics.Debug.WriteLine(adapter.InsertCommand.CommandText); // Fill the DataTable. returnTable = new DataTable(); adapter.Fill(returnTable); // Insert a new row. DataRow newRow = returnTable.NewRow(); for (int i = 0; i < table.Value.GetLength(0); i++) { //PropertyInfo info = spesificClass.GetType().GetProperty(table.Value[i,1]); //newRow[table.Value[i, 0]] = info.GetValue(spesificClass); string dbColumn = table.Value[i, 0]; string propName = table.Value[i, 1]; var value = spesificClass.GetType().GetProperty(propName).GetValue(spesificClass); newRow[dbColumn] = value; } returnTable.Rows.Add(newRow); adapter.Update(returnTable); return(returnTable); } else if (queryType == "UPDATE") { // Get the update commands. adapter.UpdateCommand = builder.GetUpdateCommand(); System.Diagnostics.Debug.WriteLine(adapter.UpdateCommand.CommandText); // Fill the DataTable. returnTable = new DataTable(); adapter.Fill(returnTable); // Edit an existing row. //DataRow[] editRow = returnTable.Select("CustomerID = 'XYZZZ'"); //editRow[0]["CompanyName"] = "XYZ Corporation"; // Update the row based on GUID DataRow[] editRow = returnTable.Select(string.Format("globalUniqueID = '{0}'", spesificClass.GetType().GetProperty("GUID").GetValue(spesificClass))); //editRow[0]["CompanyName"] = "XYZ Corporation"; for (int i = 0; i < table.Value.GetLength(0); i++) { editRow[0][table.Value[i, 0]] = spesificClass.GetType().GetProperty(table.Value[i, 1]).GetValue(spesificClass); } adapter.Update(returnTable); return(returnTable); } else if (queryType == "DELETE") { // Get the delete commands. adapter.DeleteCommand = builder.GetDeleteCommand(); System.Diagnostics.Debug.WriteLine(adapter.DeleteCommand.CommandText); // Fill the DataTable. returnTable = new DataTable(); adapter.Fill(returnTable); // Delete a row. DataRow[] deleteRow = returnTable.Select(string.Format("globalUniqueID = '{0}'", spesificClass.GetType().GetProperty("GUID").GetValue(spesificClass))); foreach (DataRow row in deleteRow) { row.Delete(); } adapter.Update(returnTable); return(returnTable); } } } catch (Exception e) { FileHandlerTxt FHandler = new FileHandlerTxt(); FHandler.appendDataToTextFile(new List <string> { string.Format("Exception {0} on {1}", e.Message, DateTime.UtcNow.ToLongDateString()) }); } return(returnTable); }
public DbCommand GetDeleteCommand(DbTransaction transaction, string tableName) { return(_commandBuilder.GetDeleteCommand(transaction, tableName)); }
/// <summary> /// Saves changes made to table data to the database. /// </summary> /// <param name="table">DataTable with changes.</param> /// <param name="selectQuery">SELECT SQL query which was used to read this table.</param> /// <returns>Returns true if save was successful and returns false otherwise.</returns> public bool UpdateTable(DataTable table, string selectQuery) { // Validate inputs if (table == null) { throw new ArgumentNullException("table"); } if (String.IsNullOrEmpty(selectQuery)) { throw new ArgumentException(Resources.Error_EmptyString, "selectQuery"); } Debug.Assert(Connection != null, "Connection is not initialized!"); // Extract changes from table DataTable changes = table.GetChanges(); // If no changes detected, return true if (changes == null) { return(true); } // Retrieving connection DbConnection conn = GetConnection(); DbTransaction transaction = null; try { // Ensure the connection is open EnsureConnectionIsOpen(); // Start transaction transaction = conn.BeginTransaction(); // Create command object DbCommand comm = conn.CreateCommand(); comm.CommandText = selectQuery; comm.Transaction = transaction; // Create a data adapter and attach it to command DbDataAdapter adapter = CreateDataAdapter(); adapter.SelectCommand = comm; // Create a command builder and attach it to adapter DbCommandBuilder builder = CreateCommandBuilder(adapter); // Build update commands // If there are deleted rows, create delete command DataRow[] selection = table.Select(String.Empty, String.Empty, DataViewRowState.Deleted); if (selection != null && selection.Length > 0) { adapter.DeleteCommand = builder.GetDeleteCommand(); } // If there are modified rows, create update command selection = table.Select(String.Empty, String.Empty, DataViewRowState.ModifiedCurrent); if (selection != null && selection.Length > 0) { adapter.UpdateCommand = builder.GetUpdateCommand(); } // If there are new rows, create insert command selection = table.Select(String.Empty, String.Empty, DataViewRowState.Added); if (selection != null && selection.Length > 0) { adapter.InsertCommand = builder.GetInsertCommand(); } // Attach adapter commands to transactions if (adapter.UpdateCommand != null) { adapter.UpdateCommand.Transaction = transaction; } if (adapter.InsertCommand != null) { adapter.InsertCommand.Transaction = transaction; } if (adapter.DeleteCommand != null) { adapter.DeleteCommand.Transaction = transaction; } // Saves data adapter.Update(changes); // Release resources builder.Dispose(); adapter.Dispose(); // Commit transaction transaction.Commit(); // Return results return(true); } catch { // Try to ping connection after error to force socket recreation TryToPingConnection(conn); // On any error rolback transaction if any if (transaction != null) { transaction.Rollback(); } throw; } finally { Connection.UnlockProviderObject(); } }
/// <summary> /// Update. /// </summary> /// <param name="database"></param> /// <param name="tables"></param> /// <param name="tableNames"></param> /// <param name="identity"></param> /// <param name="transaction"></param> private static void Update(Database database, DataTable[] tables, string[] tableNames, string identity, DbTransaction transaction) { #region Validations if (transaction == null) { throw new ArgumentNullException("transaction"); } if (tables == null) { throw new ArgumentNullException("ds"); } if (tableNames == null) { throw new ArgumentNullException("tableNames"); } if (tables.Length != tableNames.Length) { throw new ArgumentException("tables and tableNames parameters cannot differ in size"); } #endregion // retrieve connection string cnName = GetDbFactory(database); // retrieve connection ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[cnName]; DbProviderFactory factory = DbProviderFactories.GetFactory(settings.ProviderName); // create adapter using (DbDataAdapter adapter = factory.CreateDataAdapter()) { // create commands for (int i = 0; i < tableNames.Length; ++i) { using (DbCommand select = factory.CreateCommand()) { string tableName = tableNames[i]; select.CommandText = string.Format("SELECT * FROM {0}", tableName); select.Connection = transaction.Connection; select.Transaction = transaction; using (DbCommandBuilder builder = factory.CreateCommandBuilder()) { // get table DataTable table = tables[i]; adapter.SelectCommand = select; builder.DataAdapter = adapter; adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); adapter.InsertCommand = builder.GetInsertCommand(); if (!string.IsNullOrEmpty(identity)) { GenerateIdentityUpdate(factory, table, adapter, identity); } // update adapter.Update(table); } } } } }
public void BuildCommands() { var dbFactory = new DbFactory(SqlClientFactory.Instance); var cmdGenerator = new DbCommandBuilder(dbFactory); var q = new Query(new QTable("test", "t")); q.Condition = createTestQuery(); q.Fields = new QField[] { "name", "t.age", new QField("age_months", "t.age*12") }; // SELECT TEST with prefixes and expressions IDbCommand cmd = cmdGenerator.GetSelectCommand(q); string masterSQL = "SELECT name,t.age,t.age*12 as age_months FROM test t WHERE (((name LIKE @p0) Or (NOT(age>=@p1))) And ((weight=@p2) And (type IN (@p3,@p4)))) Or ((name<>@p5) And (type IS NOT NULL))"; Assert.Equal(masterSQL, cmd.CommandText.Trim()); // SELECT WITH TABLE ALIAS TEST cmd = cmdGenerator.GetSelectCommand( new Query("accounts.a", new QConditionNode((QField)"a.id", Conditions.In, new Query("dbo.accounts.b", (QField)"a.id" != (QField)"b.id")))); masterSQL = "SELECT * FROM accounts a WHERE a.id IN (SELECT * FROM dbo.accounts b WHERE a.id<>b.id)"; Assert.Equal(masterSQL, cmd.CommandText); var testData = new Dictionary <string, object> { { "name", "Test" }, { "age", 20 }, { "weight", 75.6 }, { "type", "staff" } }; // INSERT TEST cmd = cmdGenerator.GetInsertCommand("test", testData); masterSQL = "INSERT INTO test (name,age,weight,type) VALUES (@p0,@p1,@p2,@p3)"; Assert.Equal(cmd.CommandText, masterSQL); Assert.Equal(cmd.Parameters.Count, 4); // UPDATE TEST cmd = cmdGenerator.GetUpdateCommand(new Query("test", (QField)"name" == (QConst)"test"), testData); masterSQL = "UPDATE test SET name=@p0,age=@p1,weight=@p2,type=@p3 WHERE name=@p4"; Assert.Equal(cmd.CommandText, masterSQL); Assert.Equal(cmd.Parameters.Count, 5); // UPDATE TEST (by query) var changes = new Dictionary <string, IQueryValue>() { { "age", (QConst)21 }, { "name", (QConst)"Alexandra" } }; cmd = cmdGenerator.GetUpdateCommand(new Query("test", (QField)"id" == (QConst)1), changes); masterSQL = "UPDATE test SET age=@p0,name=@p1 WHERE id=@p2"; Assert.Equal(masterSQL, cmd.CommandText); Assert.Equal(3, cmd.Parameters.Count); // DELETE BY QUERY TEST cmd = cmdGenerator.GetDeleteCommand(new Query("test", (QField)"id" == (QConst)5)); masterSQL = "DELETE FROM test WHERE id=@p0"; Assert.Equal(cmd.CommandText, masterSQL); Assert.Equal(cmd.Parameters.Count, 1); // ------- escape identifiers asserts -------- dbFactory.IdentifierFormat = "[{0}]"; Assert.Equal( "SELECT [name],[t].[age],t.age*12 as [age_months] FROM [test] [t] WHERE ((([name] LIKE @p0) Or (NOT([age]>=@p1))) And (([weight]=@p2) And ([type] IN (@p3,@p4)))) Or (([name]<>@p5) And ([type] IS NOT NULL))", cmdGenerator.GetSelectCommand(q).CommandText.Trim()); Assert.Equal( "INSERT INTO [test] ([name],[age],[weight],[type]) VALUES (@p0,@p1,@p2,@p3)", cmdGenerator.GetInsertCommand("test", testData).CommandText); Assert.Equal( "UPDATE [test] SET [name]=@p0,[age]=@p1,[weight]=@p2,[type]=@p3 WHERE [name]=@p4", cmdGenerator.GetUpdateCommand(new Query("test", (QField)"name" == (QConst)"test"), testData).CommandText); Assert.Equal( "DELETE FROM [test] WHERE [id]=@p0", cmdGenerator.GetDeleteCommand(new Query("test", (QField)"id" == (QConst)5)).CommandText); }
static void CreateDataAdapter2(DbProviderFactory factory, string connectionString) { try { // Create the DbProviderFactory and DbConnection. //DbProviderFactory factory = // DbProviderFactories.GetFactory(providerName); DbConnection connection = factory.CreateConnection(); connection.ConnectionString = connectionString; using (connection) { // Define the query. string queryString = "SELECT CustomerID, CompanyName FROM Customers"; // Create the select command. DbCommand command = factory.CreateCommand(); command.CommandText = queryString; command.Connection = connection; // Create the DbDataAdapter. DbDataAdapter adapter = factory.CreateDataAdapter(); adapter.SelectCommand = command; // Create the DbCommandBuilder. DbCommandBuilder builder = factory.CreateCommandBuilder(); builder.DataAdapter = adapter; // Get the insert, update and delete commands. adapter.InsertCommand = builder.GetInsertCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); // Display the CommandText for each command. Trace.WriteLine("InsertCommand: {0}", adapter.InsertCommand.CommandText); Trace.WriteLine("UpdateCommand: {0}", adapter.UpdateCommand.CommandText); Trace.WriteLine("DeleteCommand: {0}", adapter.DeleteCommand.CommandText); // Fill the DataTable. DataTable table = new DataTable(); adapter.Fill(table); // Insert a new row. DataRow newRow = table.NewRow(); newRow["CustomerID"] = "XYZZZ"; newRow["CompanyName"] = "XYZ Company"; table.Rows.Add(newRow); adapter.Update(table); // Display rows after insert. Trace.WriteLine(string.Empty); Trace.WriteLine("----List All Rows-----"); foreach (DataRow row in table.Rows) { Trace.WriteLine(row[0] + " " + row[1]); } Trace.WriteLine("----After Insert-----"); // Edit an existing row. DataRow[] editRow = table.Select("CustomerID = 'XYZZZ'"); editRow[0]["CompanyName"] = "XYZ Corporation"; adapter.Update(table); // Display rows after update. Trace.WriteLine(string.Empty); foreach (DataRow row in table.Rows) { Trace.WriteLine(row[0] + " " + row[1]); } Trace.WriteLine("----After Update-----"); // Delete a row. DataRow[] deleteRow = table.Select("CustomerID = 'XYZZZ'"); foreach (DataRow row in deleteRow) { row.Delete(); } adapter.Update(table); // Display rows after delete. Trace.WriteLine(string.Empty); foreach (DataRow row in table.Rows) { Trace.WriteLine(row[0] + " " + row[1]); } Trace.WriteLine("----After Delete-----"); Trace.WriteLine("Customer XYZZZ was deleted."); } } catch (Exception ex) { Console.WriteLine(ex.Message); } }