/// <SUMMARY>
/// Checks if there is a row with changes and
/// writes it to the database
/// </SUMMARY>
private void UpdateRowToDatabase()
{
try
{
if (_lastDataRow != null)
{
if (_lastDataRow.RowState == DataRowState.Modified ||
_lastDataRow.RowState == DataRowState.Added ||
_lastDataRow.RowState == DataRowState.Deleted)
{
DataRow[] rows = new DataRow[1];
rows[0] = _lastDataRow;
_adapter.Update(rows);
}
}
}
catch (DBConcurrencyException)
{
//Ignored
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseInfo.DatabaseType, false);
}
}
public void UpdateRow(string file, Dictionary <string, FxField> row)
{
bool isChanged = false;
int id = int.Parse(row["id"].Value.ToString());
// Refind record...
var table = FindById(file, id);
_dataAdapter.UpdateCommand = _commandBuilder.GetUpdateCommand();
foreach (var field in row)
{
if (field.Value.ChangedValue != null)
{
isChanged = true;
table.Rows[0][field.Value.Name] = field.Value.ChangedValue;
}
}
if (isChanged)
{
_dataAdapter.Update(table);
}
}
/// <summary>
/// Saves Changes to a DataTable
/// </summary>
/// <param name="dt"></param>
public void Update(DataTable dt)
{
DataRow[] rows = null;
try
{
rows = Select(dt, DataViewRowState.Added | DataViewRowState.ModifiedCurrent);
if (rows.Length == 0)
{
return;
}
Dump(rows);
if (this.timeStampColumn != null)
{
Guid newTs = Guid.NewGuid();
foreach (DataRow r in rows)
{
r[timeStampColumn] = newTs;
}
}
dataAdapter.Update(rows);
}
catch (System.Data.DBConcurrencyException dbex)
{
if (this.ConcurrencyError != null)
{
// This is a Firebird Hack because Fb doesn't set the row
if (dbex.Row == null)
{
foreach (DataRow r in rows)
{
if (r.RowState == DataRowState.Added ||
r.RowState == DataRowState.Modified)
{
dbex.Row = r;
break;
}
}
}
ConcurrencyError(dbex);
}
else
{
throw dbex;
}
}
catch (System.Exception ex)
{
string text = "Exception of type " + ex.GetType().Name + " while updating or inserting data rows: " + ex.Message + "\n";
if ((ex.Message.IndexOf("Die Variable") > -1 && ex.Message.IndexOf("muss deklariert") > -1) || (ex.Message.IndexOf("Variable") > -1 && ex.Message.IndexOf("declared") > -1))
{
text += "Check the field names in the mapping file.\n";
}
text += "Sql Update statement: " + updateCommand.CommandText + "\n";
text += "Sql Insert statement: " + insertCommand.CommandText;
throw new NDOException(37, text);
}
}
public static void InsertUpdateDeleteForOneRecord(DbDataAdapter dbAdapter)
{
// add new employee by POCO model
var newEmployee = new Employee()
{
EmployeeID = 1000, FirstName = "John", LastName = "Smith"
};
dbAdapter.Insert("Employees", newEmployee);
Console.WriteLine("Added new employee: John Smith (ID=1000)");
// add new employee by dictionary
dbAdapter.Insert("Employees", new Dictionary <string, object>()
{
{ "EmployeeID", 1001 },
{ "FirstName", "Jim" },
{ "LastName", "Gordon" }
});
Console.WriteLine("Added new employee: Jim Gordon (ID=1001)");
// update employee by poco model
newEmployee.FirstName = "Bart";
var newEmployeeByIdQuery = new Query("Employees", (QField)"EmployeeID" == (QConst)newEmployee.EmployeeID);
dbAdapter.Update(newEmployeeByIdQuery, newEmployee);
Console.WriteLine("New first name for EmployeeID=1000: {0}",
dbAdapter.Select(new Query("Employees", (QField)"EmployeeID" == (QConst)1000).Select("FirstName")).Single <string>());
// update employee by dictionary
dbAdapter.Update(
new Query("Employees", (QField)"EmployeeID" == (QConst)1001),
new Dictionary <string, object>()
{
{ "FirstName", "Bruce" },
{ "LastName", "Wayne" }
}
);
var employee_1001_data = dbAdapter.Select(new Query("Employees", (QField)"EmployeeID" == (QConst)1001)).ToDictionary();
Console.WriteLine("New name for EmployeeID=1001: {0} {1}", employee_1001_data["FirstName"], employee_1001_data["LastName"]);
// update only some fields from model
newEmployee.LastName = "Simpson";
newEmployee.FirstName = "Homer";
dbAdapter.Update(newEmployeeByIdQuery, new { LastName = "LastName" });
var newEmployeeNameFromDb = dbAdapter.Select(new Query(newEmployeeByIdQuery).Select("FirstName", "LastName")).ToDictionary();
Console.WriteLine("First+Last for EmployeeID=1000 after update: {0} {1}",
newEmployeeNameFromDb["FirstName"], newEmployeeNameFromDb["LastName"]);
}
public void submitAddRow(DataRow aRow)
{
DataRow[] drA = { aRow };
try
{
adapter.Update(drA);
aRow.AcceptChanges();
}
catch (Exception e1)
{
submitUpdateError += e1.Message;
}
}
public bool TryAddColumn(string column)
{
CreateCommands();
try
{
_table.Columns.Add(column.ToUpper(), typeof(string));
_adapter.Update(_table);
}
catch
{
return(false);
}
return(true);
}
public void Start(string vergelijkingnaam, string referentiename, string analysename, string configuration, string referencesql, string analysesql, string rapporttype)
{
// get our next id
DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT MAX(outputid) FROM " + Properties.Settings.Default.databaseprefix + "output ";
object result = command.ExecuteScalar();
long outputid = DBNull.Value == result ? 0 : Convert.ToUInt32(result);
outputid++;
ds = new DataSet();
kopadapter.Fill(ds, "output");
koprow = ds.Tables["output"].NewRow();
koprow["outputid"] = outputid;
koprow["tijdstip"] = DateTime.Now;
koprow["vergelijkingnaam"] = vergelijkingnaam;
koprow["configuratie"] = configuration;
koprow["rapporttype"] = rapporttype;
koprow["referentienaam"] = referentiename;
koprow["analysenaam"] = analysename;
koprow["referentiequery"] = referencesql;
koprow["analysequery"] = analysesql;
koprow["referentieapplicatie"] = "";
koprow["analyseapplicatie"] = "";
koprow["referentiegemeentecode"] = "";
koprow["analysegemeentecode"] = "";
koprow["percentage"] = 0;
koprow["referentieaantal"] = 0;
koprow["analyseaantal"] = 0;
koprow["gelijkaantal"] = 0;
koprow["afwijkingaantal"] = 0;
koprow["nietgevondenaantal"] = 0;
//koprow["ongeldigaantal"] = 0;
koprow["looptijd"] = watch.Elapsed.TotalSeconds;
ds.Tables["output"].Rows.Add(koprow);
kopadapter.Update(ds, "output");
regeladapter.SelectCommand.CommandText = "SELECT * FROM " + Properties.Settings.Default.databaseprefix + "outputline WHERE outputid = " + outputid;
regeladapter.Fill(ds, "outputline");
match = 0;
nomatch = 0;
missing = 0;
//invalid = 0;
}
public int Update(DataTable dt, DbCommand cmd)
{
try
{
string relTableName = dt.TableName;
string SQLString = string.Format("select * from {0} ", relTableName);
cmd.CommandText = SQLString;
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 (Exception ex)
{
throw new Exception(ex.Message);
}
}
//excel
public void Update_EXCEL_Table(CDCDS ds)
{
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM $T<{0}>", ds.CDC_EXCEL.TableName);
using (DbDataAdapter a = BuildDataAdapter(query))
{
try
{
InstallRowUpdatedHandler(a, Update_EXCEL_ElementHandler);
a.ContinueUpdateOnError = false;
DataTable dt = ds.CDC_EXCEL;
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(dt);
}
catch (DBConcurrencyException ex)
{
}
catch
{
throw;
}
}
}
public int DeleteDataTable(DataTable dt, string sql)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentNullException("sql", "A SQL query or stored procedure name is required");
}
if (dt == null)
{
throw new ArgumentNullException("dt", "DataSet cannot be null.");
}
DbDataAdapter adapter = null;
try
{
adapter = _dbProviderFactory.CreateDataAdapter();
adapter.DeleteCommand = Command;
adapter.DeleteCommand.CommandText = sql;
return(adapter.Update(dt));
}
finally
{
if (adapter.DeleteCommand != null)
{
adapter.DeleteCommand.Dispose();
}
adapter.Dispose();
}
}
public void UpdateDataTable(string sql, DataTable dt)
{
using (DbCommand cmd = _cn.CreateCommand())
{
cmd.Connection = _cn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.CommandTimeout = CommandTimeout;
using (DbDataAdapter da = _factory.CreateDataAdapter())
{
da.SelectCommand = cmd;
DbCommandBuilder cb = _factory.CreateCommandBuilder();
cb.DataAdapter = da;
da.InsertCommand = cb.GetInsertCommand();
da.UpdateCommand = cb.GetUpdateCommand();
da.DeleteCommand = cb.GetDeleteCommand();
da.Update(dt);
}
}
return;
}
/// <summary>
/// Updates the specified data set.
/// </summary>
/// <param name="dataSet">The data set.</param>
/// <param name="sourceTable">The source table.</param>
public void Update(DataSet dataSet, string sourceTable)
{
TraceStart("Update");
try {
using (DbDataAdapter adapter = DataAccess.Provider.CreateDataAdapter() as DbDataAdapter) {
if (dataSet.HasChanges(DataRowState.Added))
{
adapter.InsertCommand = Command as DbCommand;
}
if (dataSet.HasChanges(DataRowState.Modified))
{
adapter.UpdateCommand = Command as DbCommand;
}
if (dataSet.HasChanges(DataRowState.Deleted))
{
adapter.DeleteCommand = Command as DbCommand;
}
this.Prepare();
adapter.Update(dataSet, sourceTable);
}
}
catch (Exception ex) {
this.HandleException(ex);
//this.Rollback();
throw; // not handled here
}
finally {
this.CloseConnection(false);
TraceEnd("Update");
}
}
/// <summary>
/// Performs all update operations for changed rows in a table
/// </summary>
public int SaveChanges(DataTable dataTable)
{
if (dataTable == null)
{
throw new ArgumentNullException("dataTable");
}
DataTableCommandBuilder builder = CreateCommandBuilder(dataTable, Connection);
builder.SequenceName = SequenceName;
DbDataAdapter dataAdapter = _providerFactory.CreateDataAdapter();
dataAdapter.UpdateCommand = builder.UpdateCommand;
dataAdapter.DeleteCommand = builder.DeleteCommand;
dataAdapter.InsertCommand = builder.InsertCommand;
// enroll in transactions
EnrollInTransaction(dataAdapter.UpdateCommand);
EnrollInTransaction(dataAdapter.DeleteCommand);
EnrollInTransaction(dataAdapter.InsertCommand);
if (BeforeSaveChanges != null)
{
var args = new TableSaveEventArgs(dataAdapter, dataTable);
BeforeSaveChanges(this, args);
if (args.Cancel)
{
throw new OperationCanceledException("Save Changes Canceled");
}
}
return(dataAdapter.Update(dataTable));
}
//--пока этот метод вызывается из CreateTable, проверку на _conn.Open делать не надо, но как только будет отдельно, надо будет делать!
protected virtual void InsertDataToDb(DataTable table, string parameters_prefix, bool PrepareTableForInsert)
{
string insert_sql = string.Format("insert into {0} values(", table.TableName);
Array insert_params = new DbParameter[table.Columns.Count];
for (int i = 0; i < table.Columns.Count; i++)
{
insert_sql = string.Format("{0}{2}{1}", insert_sql, table.Columns[i].ColumnName + (i + 1 != table.Columns.Count ? "," : ")"), parameters_prefix);
DbParameter par = CreateParameter(string.Format("{1}{0}", table.Columns[i].ColumnName, parameters_prefix), Utilities.SystemTypeToDbTypeConverter.Convert(table.Columns[i].DataType), table.Columns[i].MaxLength);
par.SourceColumn = table.Columns[i].ColumnName;
insert_params.SetValue(par, i);
}
_DA = CreateDataAdapter("");
var ins_cmd = CreateCommand(insert_sql);
ins_cmd.Parameters.AddRange(insert_params);
if (PrepareTableForInsert)
{
MarkTableRowsAsAdded(table);
}
try
{
_DA.InsertCommand = ins_cmd;
_DA.Update(table);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
/// <summary>
/// 将DataTable插入数据库。（注：使用DataAdapter实现，数据量大时可能效率较低）
/// </summary>
/// <param name="insertDataTable">要插入的数据</param>
/// <param name="insertTableName">要插入的表名，如果DataTable有名字可以不填</param>
/// <returns></returns>
public bool InsertDataTable(DataTable insertDataTable, string insertTableName = "")
{
try
{
if (insertTableName.IsNullOrWhiteSpace())
{
insertTableName = insertDataTable.TableName;
}
OpenConnection();
// TODO:insertTableName未做防SQL注入
using (DbDataAdapter adapter = CreateDataAdapter($"select * from {insertTableName} where 1=0")) // 只获取表结构
using (CreateCommandBuilder(adapter))
{
DataTable dbTable = new DataTable();
adapter.Fill(dbTable);
insertDataTable.CopyTo(dbTable);
adapter.Update(dbTable);
}
}
catch (Exception ex)
{
Logger.log("InsertDataTable", ex.Message);
return(false);
}
finally
{
CloseConnection();
}
return(true);
}
private void AddAdres(DbConnection connection, DbDataAdapter adapter, List <Adres> adresses)
{
string queryAdres = "SELECT * FROM dbo.AdresTest";
DbCommand commandAdres = sqlFactory.CreateCommand();
commandAdres.CommandText = queryAdres;
commandAdres.Connection = connection;
adapter.SelectCommand = commandAdres;
DbCommandBuilder builder = sqlFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
DataTable table = new DataTable();
adapter.Fill(table);
DataColumn[] keyColumns = new DataColumn[1]; // primary key voor deze kolom instellen
keyColumns[0] = table.Columns["ID"]; //idem
table.PrimaryKey = keyColumns; //idem
foreach (Adres adres in adresses)
{
if (!table.Rows.Contains(adres.ID))
{
DataRow row = table.NewRow();
row["ID"] = adres.ID;
row["straatnaamID"] = adres.straatnaam.ID;
row["huisnummer"] = adres.huisnummer;
row["appartementnummer"] = adres.appartementnummer;
row["busnummer"] = adres.busnummer;
row["huisnummerlabel"] = adres.huisnummerlabel;
row["adreslocatieID"] = adres.locatie.ID;
table.Rows.Add(row);
}
}
adapter.Update(table);
}
private void AddStraatnaam(DbConnection connection, DbDataAdapter adapter, List <Adres> adresses)
{
string queryStraatnaam = "SELECT * FROM dbo.straatnaamTest";
DbCommand commandStraatnaam = sqlFactory.CreateCommand();
commandStraatnaam.CommandText = queryStraatnaam;
commandStraatnaam.Connection = connection;
adapter.SelectCommand = commandStraatnaam;
DbCommandBuilder builder = sqlFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
DataTable table = new DataTable();
adapter.Fill(table);
DataColumn[] keyColumns = new DataColumn[1]; // primary key voor deze kolom instellen
keyColumns[0] = table.Columns["ID"]; //idem
table.PrimaryKey = keyColumns; //idem
foreach (Adres adres in adresses)
{
if (!table.Rows.Contains(adres.straatnaam.ID))
{
DataRow row = table.NewRow();
row["ID"] = adres.straatnaam.ID;
row["straatnaam"] = adres.straatnaam.straatnaam;
row["NIScode"] = adres.straatnaam.gemeente.NIScode;
table.Rows.Add(row);
}
}
adapter.Update(table);
}
private void AddLocaties(DbConnection connection, DbDataAdapter adapter, List <Adres> adresses)
{
string queryLocatie = "SELECT * FROM dbo.adresLocatieTest";
DbCommand commandLocatie = sqlFactory.CreateCommand();
commandLocatie.CommandText = queryLocatie;
commandLocatie.Connection = connection;
adapter.SelectCommand = commandLocatie;
DbCommandBuilder builder = sqlFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
DataTable table = new DataTable();
adapter.Fill(table);
DataColumn[] keyColumns = new DataColumn[1]; // primary key voor deze kolom instellen
keyColumns[0] = table.Columns["Id"]; //idem
table.PrimaryKey = keyColumns; //idem
foreach (Adres adres in adresses)
{
if (!table.Rows.Contains(adres.locatie.ID))
{
DataRow row = table.NewRow();
row["Id"] = adres.locatie.ID;
row["x"] = adres.locatie.x;
row["y"] = adres.locatie.y;
table.Rows.Add(row);
}
}
adapter.Update(table);
}
public void UpdateTable(string tablename, SchedeProcessoDS ds)
{
string tablenameDB = tablename;
string query = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM {0}", tablenameDB);
using (DbDataAdapter a = BuildDataAdapter(query))
{
try
{
a.ContinueUpdateOnError = false;
DataTable dt = ds.Tables[tablename];
DbCommandBuilder cmd = BuildCommandBuilder(a);
a.AcceptChangesDuringFill = true;
a.UpdateCommand = cmd.GetUpdateCommand();
a.DeleteCommand = cmd.GetDeleteCommand();
a.InsertCommand = cmd.GetInsertCommand();
a.Update(dt);
}
catch (DBConcurrencyException ex)
{
}
catch
{
throw;
}
}
}
/// <summary>
/// 执行一个查询语句，返回一个包含查询结果的DataTable
/// </summary>
/// <param name="sql">要执行的查询语句</param>
/// <param name="parameters">执行SQL查询语句所需要的参数</param>
/// <param name="commandType">执行的SQL语句的类型</param>
/// <returns></returns>
public int UpdateDataTable(DataTable data, string sqlInsert, string sqlUpdate, string sqlDelete
, IList <DbParameter> paraInsert, IList <DbParameter> paraUpdate, IList <DbParameter> paraDelete, CommandType commandType)
{
try
{
using (DbDataAdapter adapter = CreateDataAdapter())
{
using (DbCommand cmdInsert = CreateDbCommand(sqlInsert, paraInsert))
{
using (DbCommand cmdUpdate = CreateDbCommand(sqlUpdate, paraUpdate))
{
using (DbCommand cmdDelete = CreateDbCommand(sqlDelete, paraDelete))
{
adapter.InsertCommand = cmdInsert;
adapter.UpdateCommand = cmdUpdate;
adapter.DeleteCommand = cmdDelete;
adapter.ContinueUpdateOnError = true;
return(adapter.Update(data));
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="tableName">需要更新的数据库表名</param>
public void UpdateData(DataTable datatable)
{
using (DbConnection connection = provider.CreateConnection())
{
connection.ConnectionString = connectionString;
using (DbCommand cmd = provider.CreateCommand())
{
cmd.Connection = connection;
cmd.CommandText = "select * from " + datatable.TableName;
try
{
DbDataAdapter adapter = provider.CreateDataAdapter();
System.Data.Common.DbCommandBuilder dcb = provider.CreateCommandBuilder();
adapter.SelectCommand = cmd;
dcb.DataAdapter = adapter;
adapter.Update(datatable);
datatable.AcceptChanges();
}
catch (DbException ex)
{
connection.Close();
connection.Dispose();
throw new Exception(ex.Message);
}
}
}
}
public int UpdateData(DataTable dt)
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
DbDataAdapter adapter = this.CreateAdapter();
if (this.CommandType == CommandType.StoredProcedure)
{
this.CommandType = CommandType.Text;
}
this.BuilderCommand(adapter);
int r = adapter.Update(dt);
if (!IsInTransaction)
{
dbConnection.Close();
}
return(r);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void buttonSave_Click(object sender, RoutedEventArgs e)
{
try
{
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SQLite");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = "Data Source =D:\\person.db";
using (connection)
{
DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM person";
command.CommandType = CommandType.Text;
command.Connection = connection;
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();
adapter.Update(table);
}
MessageBox.Show("Saved/");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 要求先设置dt.TableName 为需要插入或者更新的表名称
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public bool UpdateDB(DataTable dt)
{
if (_dataAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
var commandBuilder = DBFactory.CreateCommandBuilder();
commandBuilder.DataAdapter = _dataAdapter;
_dbcmd.CommandType = CommandType.Text;
_dbcmd.CommandText = " select * from " + dt.TableName + " where 1=2 ";
_dataAdapter.SelectCommand = _dbcmd;
_dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
_dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
_dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
_dataAdapter.Update(dt);
if (dt.HasErrors)
{
dt.GetErrors()[0].ClearErrors();
return(false);
}
else
{
dt.AcceptChanges();
return(true);
}
}
private void DoSaveDataRow(DbDataAdapter adap, DbCommandBuilder dbCmdBuilder, string tablename, DataRow row)
{
if (row == null ||
row.RowState == DataRowState.Unchanged ||
row.RowState == DataRowState.Detached)
{
return;
}
try
{
dbCmdBuilder.ConflictOption = ConflictOption.OverwriteChanges;
dbCmdBuilder.DataAdapter = adap;
adap.SelectCommand.CommandText = string.Format("SELECT TOP 1 * FROM [{0}]", tablename);
adap.Update(new DataRow[] { row });
}
catch (DbException ex)
{
throw HandleDbAccessException(adap.SelectCommand.Connection, ex, adap.SelectCommand, adap.InsertCommand, adap.UpdateCommand, adap.DeleteCommand);
}
catch (Exception ex)
{
throw new DbAccessException("执行保存DataRow操作失败", ex);
}
}
private void Button_accept_changes_Click(object sender, RoutedEventArgs e)
{
grid_tabels.IsReadOnly = true;
grid_tabels.CanUserAddRows = false;
grid_ok_cancel.Visibility = Visibility.Hidden;
DbConnection connection = DbHelper.CreateConnection();
DbCommandBuilder builder = DbHelper.CreateDbCommandBuilder(connection);
DbCommand command = connection.CreateCommand();
try
{
builder.DataAdapter = DataAdapter;
DataAdapter.Update(DataTable);
DataTable.Clear();
DataAdapter.Fill(DataTable);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
EnableTableActions();
UpdateDataTable();
}
public void DataAdapterTest2_Delete(DbConnection conn)
{
Log("================================");
Log("=== Adapter Delete =============");
Log("================================");
DbTransaction trans = conn.BeginTransaction(IsolationLevel.Chaos);
Log(" Create adapter...");
DbCommand selectCmd = conn.CreateCommand();
selectCmd.Transaction = trans;
selectCmd.CommandText = "SELECT * FROM mono_adapter_test";
DbDataAdapter da = GetDataAdapter();
da.SelectCommand = (DbCommand)selectCmd;
// CommandBuilder to perform the delete
DbCommandBuilder cmdBuilder = GetCommandBuilder(da);
Log(" Create data set ...");
DataSet ds = new DataSet();
Log("Fill data set via adapter...");
da.Fill(ds, "mono_adapter_test");
Log("delete row...");
ds.Tables["mono_adapter_test"].Rows[0].Delete();
Log("da.Update(table...");
da.Update(ds, "mono_adapter_test");
Log("Commit...");
trans.Commit();
cmdBuilder.Dispose();
}
internal DaoErrMsg InsertDateTable(DataTable dt, string InsertTableName)
{
DbCommand DbCmd = DbProviderFactories.GetFactory(m_DbConnection.ToString().RemoveLastIndexof('.')).CreateCommand();
DbCmd.CommandText = "SELECT * FROM " + InsertTableName;
DbCmd.Connection = m_DbConnection;
DbDataAdapter DbAdapter = DbProviderFactories.GetFactory(m_DbConnection.ToString().RemoveLastIndexof('.')).CreateDataAdapter();
DbAdapter.SelectCommand = DbCmd;
//DbCmd.ExecuteNonQuery();
try
{
using (DbCommandBuilder Builder = DbProviderFactories.GetFactory(m_DbConnection.ToString().RemoveLastIndexof('.')).CreateCommandBuilder())
{
Builder.DataAdapter = DbAdapter;
DbAdapter.InsertCommand = Builder.GetInsertCommand();
DbAdapter.Update(dt);
}
}
catch (Exception Ex)
{
PrintErrorLog(Ex.Message, DbCmd.CommandText);
return(new DaoErrMsg(true, Ex.Message));
}
return(new DaoErrMsg());
}
private int DoUpdateDataSet(DataSet dataSet, string tableName, DbCommand insertCommand, DbCommand updateCommand, DbCommand deleteCommand)
{
int rows = 0;
using (DbDataAdapter adpter = this.GetDataAdapter())
{
IDbDataAdapter explicitAdapter = (IDbDataAdapter)adpter;
if (insertCommand != null)
{
explicitAdapter.InsertCommand = insertCommand;
}
if (updateCommand != null)
{
explicitAdapter.UpdateCommand = updateCommand;
}
if (deleteCommand != null)
{
explicitAdapter.DeleteCommand = deleteCommand;
}
try
{
rows = adpter.Update(dataSet.Tables[tableName]);
}
catch (Exception ex)
{
throw ex;
}
}
return(rows);
}
public void UpdateTable()
{
if (this.GetChanges() != null)
{
DataAdapter.Update(this);
}
}