private void DisposeResources()
{
if (_connectionOpened)
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
try
{
_connection.Dispose();
}
catch (ObjectDisposedException) {}
}
if (_builder != null)
{
try
{
_builder.Dispose();
}
catch (ObjectDisposedException) {}
}
if (_transaction != null)
{
try
{
_transaction.Dispose();
}
catch (ObjectDisposedException) {}
}
}
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();
}
public override void Dispose()
{
if (_commandBuilder != null)
{
_commandBuilder.Dispose();
}
if (commands.Count == 0)
{
return;
}
using (var con = _providerFactory.CreateConnection())
{
con.ConnectionString = _connectionString;
con.Open();
using (var tx = con.BeginTransaction())
{
IDbCommand result;
while (commands.TryDequeue(out result))
{
result.Connection = con;
result.Transaction = tx;
result.ExecuteNonQuery();
}
tx.Commit();
}
}
}
public void Dispose()
{
if (_commandBuilder != null)
{
_commandBuilder.Dispose();
_commandBuilder.DataAdapter?.Dispose();
}
}
public void DataAdapterTest2_Update(DbConnection conn)
{
Log("================================");
Log("=== Adapter Update =============");
Log("================================");
DbTransaction trans = conn.BeginTransaction(IsolationLevel.Chaos);
Log(" Create adapter...");
DbCommand selectCmd = GetCommandForUpdate(conn);
selectCmd.Transaction = trans;
selectCmd.CommandText = "SELECT * FROM mono_adapter_test";
DbDataAdapter da = GetDataAdapter();
da.SelectCommand = selectCmd;
// CommandBuilder to perform the update
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("Tables Count: " + ds.Tables.Count.ToString());
DataTable table = ds.Tables["mono_adapter_test"];
DataRowCollection rows;
rows = table.Rows;
Log(" Row Count: " + rows.Count.ToString());
DataRow myRow = rows[0];
byte[] bytes = new byte[] { 0x62, 0x63, 0x64, 0x65, 0x66, 0x67 };
Log(" Set values in the new DataRow...");
myRow["varchar2_value"] = "Super Power!";
myRow["number_scaled_value"] = 12.35;
myRow["number_integer_value"] = 457;
myRow["float_value"] = 198.76;
myRow["date_value"] = new DateTime(2002, 08, 09);
myRow["char_value"] = "Juliet";
myRow["clob_value"] = "this is a clob";
myRow["blob_value"] = bytes;
Log("da.Update(ds...");
da.Update(ds, "mono_adapter_test");
trans.Commit();
cmdBuilder.Dispose();
}
public void DataAdapterTest2_Insert(DbConnection conn)
{
Log("================================");
Log("=== Adapter Insert =============");
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 insert
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(" New Row...");
DataRow myRow;
myRow = ds.Tables["mono_adapter_test"].NewRow();
byte[] bytes = new byte[] { 0x45,0x46,0x47,0x48,0x49,0x50 };
Log(" Set values in the new DataRow...");
myRow["varchar2_value"] = "OracleClient";
myRow["number_whole_value"] = 22;
myRow["number_scaled_value"] = 12.34;
myRow["number_integer_value"] = 456;
myRow["float_value"] = 98.76;
myRow["date_value"] = new DateTime(2001, 07, 09);
myRow["char_value"] = "Romeo";
myRow["clob_value"] = "clobtest";
myRow["blob_value"] = bytes;
Log(" Add DataRow to DataTable...");
ds.Tables["mono_adapter_test"].Rows.Add(myRow);
Log("da.Update(ds...");
da.Update(ds, "mono_adapter_test");
trans.Commit();
cmdBuilder.Dispose();
}
public void Dispose()
{
if (dataAdapter != null)
{
dataAdapter.Dispose();
dataAdapter = null;
}
if (commandBuilder != null)
{
commandBuilder.Dispose();
commandBuilder = null;
}
}
public void Dispose()
{
ObjConnection.Dispose();
ObjConnection = null;
ObjCommand.Parameters.Clear();
ObjCommand.Dispose();
ObjCommand = null;
ObjDataAdapter.Dispose();
ObjDataAdapter = null;
ObjCommandBuilder.Dispose();
ObjDataAdapter = null;
}
private void DisposeDbProvider()
{
if (cmd != null)
{
cmd.Dispose();
}
if (adapter != null)
{
adapter.Dispose();
}
if (builder != null)
{
builder.Dispose();
}
}
/// <summary>
/// 利用DataTable更新数据库里面的记录
/// </summary>
/// <param name="strTableName"></param>
/// <param name="pRecordset"></param>
/// <param name="pConnection"></param>
public static bool UpdateTable(string strTableName, DataTable pRecordset, IDbConnection pConnection)
{
DbDataAdapter oleDataAdapter = null;
DbCommandBuilder cb = null;
try
{
if (pConnection == null)
{
return(false);
}
if (pConnection.State == ConnectionState.Closed)
{
pConnection.Open();
}
string str = string.Format("select * from {0}", strTableName);
oleDataAdapter = GetDbDataAdapter(pConnection, str);
cb = m_Dbfactory.CreateCommandBuilder();
cb.DataAdapter = oleDataAdapter;
oleDataAdapter.Update(pRecordset);
return(true);
}
catch (Exception exp)
{
Hy.Common.Utility.Log.OperationalLogManager.AppendMessage(exp.ToString());
return(false);
}
finally
{
if (oleDataAdapter != null)
{
oleDataAdapter.Dispose();
}
if (cb != null)
{
cb.Dispose();
}
}
}
protected DbCommandBuilder CreateCommandBuilder()
{
if (IsSingleton)
{
if (dbCommandBuilder == null)
{
dbCommandBuilder = dbFactory.CreateCommandBuilder();
}
}
else
{
if (dbCommandBuilder != null)
{
dbCommandBuilder.Dispose();
}
dbCommandBuilder = dbFactory.CreateCommandBuilder();
}
return(dbCommandBuilder);
}
public DataSet ExecuteKDDataSet(string string_2)
{
DataSet dataSet = null;
DbConnection dbConnection = null;
DbCommand dbCommand = null;
DbDataAdapter dbDataAdapter = null;
DbCommandBuilder dbCommandBuilder = null;
dbDataAdapter = this.GetDbDataAdapter();
dbConnection = this.GetDbConnection();
dbConnection.ConnectionString = this.ConnectionString;
dbCommand = this.GetDbCommand();
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = string_2;
dbDataAdapter.SelectCommand = dbCommand;
dbCommandBuilder = this.GetDbCommandBuilder();
dbCommandBuilder.DataAdapter = dbDataAdapter;
dataSet = new DataSet();
dbDataAdapter.FillSchema(dataSet, SchemaType.Source);
dbDataAdapter.Fill(dataSet);
if (dbConnection != null)
{
dbConnection.Dispose();
}
dbConnection = null;
if (dbCommand != null)
{
dbCommand.Dispose();
}
dbCommand = null;
dbDataAdapter = null;
if (dbCommandBuilder != null)
{
dbCommandBuilder.Dispose();
}
dbCommandBuilder = null;
return(dataSet);
}
public bool UpdateTable(string strTable, DataTable dtData)
{
Verify();
DbDataAdapter dataAdapter = null;
DbCommandBuilder cmdBuilder = null;
try
{
dataAdapter = CreateAdapter(string.Format("select * from {0}", strTable));
//DataTable dtTemp=new DataTable();
//oleDataAdapter.Fill(dtTemp);
//dtTemp.Merge(dtData);
cmdBuilder = m_ProviderFactory.CreateCommandBuilder();
cmdBuilder.DataAdapter = dataAdapter;
dataAdapter.InsertCommand = cmdBuilder.GetInsertCommand(true);
dataAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand(true);
dataAdapter.Update(dtData);
return(true);
}
catch //(Exception exp)
{
return(false);
}
finally
{
if (dataAdapter != null)
{
dataAdapter.Dispose();
}
if (cmdBuilder != null)
{
cmdBuilder.Dispose();
}
}
}
public void Dispose()
{
builder.Dispose();
connect.Dispose();
}
public void Dispose()
{
tx.Dispose();
commandBuilder.Dispose();
connection.Dispose();
}
/// <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>
/// DataTable資料表(弱型別)更新資料方式
/// </summary>
/// <param name="varDataTable">資料表</param>
/// <param name="varTableName">資料表名稱</param>
/// <returns></returns>
public int Update(DataTable varDataTable, string varTableName)
{
RESPONSE_MSG.message = "";
int cnt = 0;
try
{
if (!string.IsNullOrWhiteSpace(varDataTable.TableName))
{
varTableName = varDataTable.TableName;
}
if (string.IsNullOrWhiteSpace(varTableName))
{
RESPONSE_MSG.status = RESPONSE_STATUS.ERROR;
RESPONSE_MSG.message = "無法取得資料表名稱，無法更新";
return(cnt);
}
varDataTable.TableName = varTableName;
DbCommand _dbCommand = this.getDBCommand(string.Concat("Select * from ", varTableName));
if (!this.dbTransUsing)
{
this.dbTrans = this.dbConnection.BeginTransaction();
}
_dbCommand.Transaction = this.dbTrans;
DbDataAdapter _da = this.getDBDataAdapter(_dbCommand);
DbCommandBuilder dbCommandBuilder = dbProvider.CreateCommandBuilder();
dbCommandBuilder.DataAdapter = _da;
try
{
cnt = _da.Update(varDataTable);
}
catch (Exception ex)
{
if (logger != null)
{
logger.Error(ex);
}
RESPONSE_MSG.status = RESPONSE_STATUS.ERROR;
RESPONSE_MSG.message = ex.Message;
}
finally
{
this.releaseAdapter(_da);
try
{
dbCommandBuilder.DataAdapter = null;
dbCommandBuilder.Dispose();
dbCommandBuilder = null;
}
catch (Exception ex)
{
if (logger != null)
{
logger.Error(ex);
}
RESPONSE_MSG.status = RESPONSE_STATUS.ERROR;
RESPONSE_MSG.message = ex.Message;
}
}
}
catch (Exception ex)
{
if (logger != null)
{
logger.Error(ex);
}
RESPONSE_MSG.status = RESPONSE_STATUS.ERROR;
RESPONSE_MSG.message = ex.Message;
}
finally
{
checkIsCanClose();
}
return(cnt);
}
