//public bool UpdateDbData(string command, DataSet ds) //{ // if (UpdateDbData(ds.Tables[0], "SystemOption") >= 0) // return true; // else // return false; //} public bool UpdateDbData(string commandText, DataTable table) { int affect = -1; try { OleDbConnection connection = new OleDbConnection(Url); using (connection) { // Create the select command. OleDbCommand command = new OleDbCommand(commandText, connection); // Create the DbDataAdapter. OleDbDataAdapter adapter = new OleDbDataAdapter(command); // Create the DbCommandBuilder. OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter); builder.RefreshSchema(); // Get the insert, update and delete commands. adapter.InsertCommand = builder.GetInsertCommand(); adapter.UpdateCommand = builder.GetUpdateCommand(); adapter.DeleteCommand = builder.GetDeleteCommand(); var dd = table.GetChanges(); affect = adapter.Update(table); } } catch (Exception ex) { Console.WriteLine(ex.Message); } return(affect > 0 ? true : false); }
/// <summary> /// 更新数据库 /// </summary> public static void UpdateSourceTable(OleDbConnection conn, DataTable tb) { OleDbDataAdapter data = new OleDbDataAdapter("Select * From [" + tb.TableName + "]", conn); OleDbCommandBuilder cb = new OleDbCommandBuilder(data); cb.RefreshSchema(); data.Update(tb); }
private static OleDbCommandBuilder PrepareCommandBuilder(OleDbDataAdapter dataAdapter) { OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter); commandBuilder.QuotePrefix = "["; commandBuilder.QuoteSuffix = "]"; commandBuilder.RefreshSchema(); return(commandBuilder); }
/// <summary> /// 批量操作表 /// </summary> /// <param name="cmdType"></param> /// <param name="dt"></param> /// <param name="strTblName">数据库表名</param> /// <param name="where">除主键外的条件</param> /// <returns></returns> private int UpdateDbData(DataTable dt, string strTblName, string where = null) { int affect = -1; StringBuilder cmdFields = new StringBuilder(); foreach (DataColumn column in dt.Columns) { cmdFields.Append(column.ColumnName + ","); } if (cmdFields.Length > 0) { cmdFields = cmdFields.Remove(cmdFields.Length - 1, 1); } using (OleDbConnection oldCon = new OleDbConnection(Url)) { OleDbCommand myCommand = new OleDbCommand(string.Format("select {0} from {1}", cmdFields, strTblName), oldCon); try { OleDbDataAdapter myAdapter = new OleDbDataAdapter(); myAdapter.SelectCommand = myCommand; OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder { DataAdapter = myAdapter, SetAllValues = true, ConflictOption = ConflictOption.OverwriteChanges, }; myCommandBuilder.RefreshSchema(); myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand(true); myAdapter.InsertCommand.Connection = oldCon; myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand(true); myAdapter.DeleteCommand = myCommandBuilder.GetDeleteCommand(true); if (!string.IsNullOrEmpty(where)) { myAdapter.UpdateCommand.CommandText = myAdapter.UpdateCommand.CommandText.Replace("WHERE", "WHERE " + where + " and "); } var dd = dt.GetChanges(); affect = myAdapter.Update(dt); } catch (Exception ex) { throw ex; } finally { } } return(affect); }
/// <summary> /// 更新表中的数据至数据库,仅能更新由该实例获取的DataTable对象 /// </summary> /// <param name="dataTable">要执行更新的表</param> public void Update(DataTable dataTable) { if (connection.State == ConnectionState.Closed) { connection.Open(); } int key = dataTable.GetHashCode(); if (!sqlList.ContainsKey(key)) { throw new Exception("该DataTable不是由本" + ToString() + "创建,无法更新!"); } string sql = sqlList[key]; command.CommandText = sql; adapter.SelectCommand = command; commandBuilder.RefreshSchema(); adapter.Update(dataTable); if (odt == null && isdisposeConn) { connection.Close(); } }
/// <summary> /// 更新表,带事务 /// </summary> /// <param name="dt">表</param> /// <param name="con">数据库连接</param> public void Update(DataTable dt, IDbConnection con, IDbTransaction ts) { OleDbDataAdapter sda = new OleDbDataAdapter(); OleDbCommandBuilder scb = new OleDbCommandBuilder(sda); try { sda.SelectCommand = new OleDbCommand("SELECT * FROM " + dt.TableName, con as OleDbConnection); sda.SelectCommand.Transaction = ts as OleDbTransaction; scb.RefreshSchema(); sda.Update(dt); } catch (OleDbException ex) { throw (ex); } }