internal void event_CommitDataTableChanges(string tableName, DataTable dataTable)
{
try {
event_OpenConnection();
string query = "SELECT * FROM [" + tableName + "]";
SQLiteDataAdapter dataAdapter;
dataAdapter = new SQLiteDataAdapter(query, connection);
SQLiteCommandBuilder commandBuilder;
commandBuilder = new SQLiteCommandBuilder(dataAdapter);
commandBuilder.QuotePrefix = "[";
commandBuilder.QuoteSuffix = "]";
dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
dataAdapter.Update(dataTable);
commandBuilder.Dispose();
dataAdapter.Dispose();
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
}
public void Dispose()
{
IsDisposed = true;
DataTable.Dispose();
CommandBuilder.Dispose();
DataAdapter.Dispose();
}
public bool UpdateTable(DataTable table, string tableName)
{
try
{
TableHelper.SetDefaultColumnValues(table);
//var con = CONNECTION.OpenCon();
using (var con = new SQLiteConnection(Settings.ConnectionString))
{
using (var adapter = new SQLiteDataAdapter(string.Format(@"SELECT * FROM {0} WHERE 1=0", tableName), con))
{
using (var cmd = new SQLiteCommandBuilder(adapter))
{
adapter.Update(table);
cmd.Dispose();
}
adapter.Dispose();
}
}
//CONNECTION.CloseCon(con);
return(true);
}
catch (DBConcurrencyException cex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable DBConcurrencyError!",
Ex = cex,
});
if (Settings.ThrowExceptions)
{
throw new DBConcurrencyException("UpdateTable Error!", cex);
}
return(false);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable Error!",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTable Error!", ex);
}
return(false);
}
}
public bool UpdateTable(DataTable table, string tableName, bool setInsertOn = true, bool setModifyOn = true, string additionalMessage = "")
{
try
{
TableHelper.SetDefaultColumnValues(table, setInsertOn, setModifyOn);
using (var con = new SQLiteConnection(Settings.ConnectionString))
{
using (var adapter = new SQLiteDataAdapter(string.Format(@"SELECT * FROM {0} WHERE 1=0", tableName), con))
{
using (var cmd = new SQLiteCommandBuilder(adapter))
{
adapter.Update(table);
cmd.Dispose();
}
adapter.Dispose();
}
}
return(true);
}
catch (DBConcurrencyException cex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable DBConcurrencyError!",
AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}",
Ex = cex,
});
if (Settings.ThrowExceptions)
{
throw new DBConcurrencyException("UpdateTable Error!", cex);
}
return(false);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateTable Error!",
AdditionalMessage = $"Table: {tableName}{Environment.NewLine}AdditionalMessage: {additionalMessage}",
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateTable Error!", ex);
}
return(false);
}
}
/// <summary>
/// Allows the programmer to easily update DataTable fill and set to DB.
/// </summary>
/// <param name="query">SQL query string, get another data similar DataTable</param>
/// <param name="dTable">A DataTable source to set new values.</param>
/// <returns>A boolean true or false to signify success or failure.</returns>
public bool Update(string query, DataTable dTable)
{
if (!this._Check())
{
throw new ArgumentException(thisClass + Properties.Resources.DBNotOpen);
}
if (
((dTable == null) || (dTable.Rows.Count == 0)) ||
(string.IsNullOrWhiteSpace(query))
)
{
return(false);
}
SQLiteDataAdapter adp = null;
SQLiteCommandBuilder bcmd = null;
try
{
adp = new SQLiteDataAdapter(query, this._dbConn);
bcmd = new SQLiteCommandBuilder(adp);
adp.Update(dTable);
return(true);
}
catch (Exception e)
{
#if DEBUG
throw new ArgumentException(
thisClass +
e.Message +
Environment.NewLine +
stCore.stConsole.GetTabString(3, 0) +
"[" + query + "]"
);
#else
throw e;
#endif
}
finally
{
if (adp != null)
{
adp.Dispose();
}
if (bcmd != null)
{
bcmd.Dispose();
}
}
}
//修改数据表记录
public static bool UpdateTable(DataTable srcTable, string tableName)
{
bool isok = false;
SQLiteConnection con = null;
SQLiteCommand command = null;
SQLiteDataAdapter oda = null;
SQLiteCommandBuilder ocb = null;
//string conString = "Data Source=" + System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Trips";
try
{
con = new SQLiteConnection(conString);
command = con.CreateCommand();
command.CommandText = "SELECT * FROM " + tableName;
oda = new SQLiteDataAdapter(command);
ocb = new SQLiteCommandBuilder(oda);
oda.UpdateCommand = ocb.GetUpdateCommand();
oda.InsertCommand = ocb.GetInsertCommand();
oda.DeleteCommand = ocb.GetDeleteCommand();
oda.Update(srcTable);
isok = true;
}
catch
{ }
finally
{
if (ocb != null)
{
ocb.Dispose();
}
if (oda != null)
{
oda.Dispose();
}
if (command != null)
{
command.Dispose();
}
if (con != null)
{
con.Close();
}
}
return(isok);
}
internal void event_CommitDataTableChanges(string tableName, DataTable dataTable)
{
event_OpenConnection();
string query = "SELECT * FROM [" + tableName + "]";
SQLiteDataAdapter dataAdapter;
dataAdapter = new SQLiteDataAdapter(query, connection);
SQLiteCommandBuilder commandBuilder;
commandBuilder = new SQLiteCommandBuilder(dataAdapter);
dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
dataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
dataAdapter.Update(dataTable);
commandBuilder.Dispose();
dataAdapter.Dispose();
event_CloseConnection();
}
public bool UpdateDataSet(DataSet dataSet, bool setInsertOn = true, bool setModifyOn = true, string additionalMessage = "")
{
try
{
using (var con = new SQLiteConnection(Settings.ConnectionString))
{
foreach (DataTable tbl in dataSet.Tables)
{
using (var adapter = new SQLiteDataAdapter(string.Format(@"SELECT * FROM {0} WHERE 1=0", tbl.TableName), con))
{
using (var cmd = new SQLiteCommandBuilder(adapter))
{
adapter.Update(tbl);
cmd.Dispose();
}
adapter.Dispose();
}
}
}
return(true);
}
catch (Exception ex)
{
SLLog.WriteError(new LogData
{
Source = ToString(),
FunctionName = "UpdateDataSet Error!",
AdditionalMessage = additionalMessage,
Ex = ex,
});
if (Settings.ThrowExceptions)
{
throw new Exception("UpdateDataSet Error!", ex);
}
return(false);
}
}
//修改数据表记录
public static void UpdateDeviceTable()
{
SQLiteConnection con = null;
SQLiteCommand command = null;
SQLiteDataAdapter oda = null;
SQLiteCommandBuilder ocb = null;
try
{
con = new SQLiteConnection(conString);
command = con.CreateCommand();
command.CommandText = "select * from TestDevices";
oda = new SQLiteDataAdapter(command);
ocb = new SQLiteCommandBuilder(oda);
oda.UpdateCommand = ocb.GetUpdateCommand();
oda.InsertCommand = ocb.GetInsertCommand();
oda.DeleteCommand = ocb.GetDeleteCommand();
byte[] rcv = ACBTester.genDeviceInfo();
DataTable dt = TableCenter.getDeviceTable();
DataRow deviceRow = dt.NewRow();
deviceRow["DeviceCode"] = "DW45";
deviceRow["FactoryCode"] = "Shihlin Electirc";
deviceRow["ProductNumber"] = (rcv[7] * 256 + rcv[8]).ToString("D4") + (rcv[9] * 256 + rcv[10]).ToString("D4");
deviceRow["ProductDate"] = new DateTime(rcv[11] * 256 + rcv[12], rcv[13], rcv[14]);
deviceRow["Inm"] = rcv[17] * 256 + rcv[18];
deviceRow["In"] = rcv[19] * 256 + rcv[20];
deviceRow["Imcr"] = rcv[21] + rcv[22];
int Inm = rcv[17] * 256 + rcv[18];
int nInm = Inm == 2000 ? 1 : (Inm == 3200 ? 2 : 3);
deviceRow["BreakType"] = string.Format("XSIC-P{0}G{1}", rcv[16], nInm);
deviceRow["Version"] = "";
deviceRow["ControllerType"] = "";
deviceRow["Fn"] = rcv[27] * 256 + rcv[28];
deviceRow["SaveTime"] = DateTime.Now;
dt.Rows.Add(deviceRow);
int n = oda.Update(dt);
}
catch
{ }
finally
{
if (ocb != null)
{
ocb.Dispose();
}
if (oda != null)
{
oda.Dispose();
}
if (command != null)
{
command.Dispose();
}
if (con != null)
{
con.Close();
}
}
}
