public static bool ExecuteQueryTrans(string cmdtext, params SqlParameter[] parameters)
{
int i = 0;
using (SqlConnection conn = new SqlConnection(connStr))
{
System.Data.SqlClient.SqlCommand cm = new System.Data.SqlClient.SqlCommand();
cm.Connection = conn;
conn.Open();
System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();
try
{
cm.CommandText = cmdtext;
cm.Parameters.AddRange(parameters);
cm.Transaction = trans;
i = cm.ExecuteNonQuery();
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
conn.Close();
trans.Dispose();
conn.Dispose();
}
}
return(i > 0);
}
public static void TryForceDisposeTransactionAndConnection(SqlTransaction transaction)
{
if (transaction != null)
{
SqlConnection connection = null;
try
{
connection = transaction.Connection;
transaction.Dispose();
}
catch (Exception) { }
if (connection != null)
{
try
{
connection.Close();
}
catch (Exception) { }
try
{
connection.Dispose();
}
catch (Exception) { }
}
}
}
public DataTable ReadEMPTable()
{
ThrowIfDisposed();
DataTable ds = new DataTable();
try
{
//クエリーの生成
SqlCommand sqlCom = new SqlCommand();
//クエリー送信先及びトランザクションの指定
sqlCom.Connection = this.sqlConn;
sqlCom.Transaction = this.sqlTran;
sqlCom.CommandText = "SELECT * FROM EMP_MASTER left outer join EMP_POST_MASTER on EMP_POST = EMP_POST_ID";
SqlDataAdapter sqlAda = new SqlDataAdapter();
sqlAda.SelectCommand = sqlCom;
sqlAda.Fill(ds);
}catch (Exception ex) {
MessageBox.Show("データベース読み取りエラー " + ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
sqlTran.Rollback();
sqlTran.Dispose();
sqlConn.Close();
sqlConn.Dispose();
Application.Exit();
}
ds.PrimaryKey = new DataColumn[] { ds.Columns["EMP_CODE"] };
return(ds);
}
public SubmitOrderResult SaveOrder(SubmitOrderRequest request)
{
var result = new SubmitOrderResult();
orderNumber = request.OrderNumber;
try
{
trans = cn.BeginTransaction();
var orderId = InsertOrderRecord(request);
foreach (var orderLineItem in request.LineItems)
{
InsertLineItems(orderId, orderLineItem);
}
trans.Commit();
trans.Dispose();
trans = null;
result = GetOrderResults();
}
catch (SqlException ex)
{
result.HasException = true;
result.Exception = ExceptionFactory.BuildSqlException(ex);
}
catch (Exception ex)
{
result.HasException = true;
result.Exception = ExceptionFactory.BuildSystemException(ex);
}
finally
{
if (trans != null)
{
trans.Rollback();
trans.Dispose();
}
cn.Close();
cn.Dispose();
}
return result;
}
/// <summary>
/// 保存前执行的方法
/// </summary>
/// <returns></returns>
public override bool DoBeforeSave()
{
bool result = false;
//非空验证
if (dsMain.Current != null)
{
//主表非空验证
foreach (DataRow dr in DynamicMasterTableData.Select("bSaveData=1 AND bNotNull=1"))
{
if (string.IsNullOrEmpty(((DataRowView)dsMain.Current).Row[dr["sFieldName"].ToString()].ToString()))
{
string sMsg = string.Format("{0} {1}", LangCenter.Instance.IsDefaultLanguage ? dr["sCaption"].ToString() : dr["sEngCaption"].ToString(),
LangCenter.Instance.GetSystemMessage("NotNull"));
Public.SystemInfo(sMsg);
return false;
}
}
}
if (SqlTrans != null)
SqlTrans.Dispose();
SqlTrans = ConnectSetting.SysSqlConnection.BeginTransaction();
try
{
result = DoBeforceSaveInTrans(SqlTrans);
}
catch
{
SqlTrans.Rollback();
//回收Trans
if (SqlTrans != null)
SqlTrans.Dispose();
return false;
}
return result;
}
public void ExecuteInTransaction(Action work)
{
_connection.Open();
_transaction = _connection.BeginTransaction();
try
{
work();
_transaction.Commit();
}
catch
{
_transaction.Rollback();
throw;
}
finally
{
_connection.Close();
_transaction.Dispose();
_transaction = null;
}
}
public void Connection_Transaction_Disposed ()
{
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
trans.Dispose ();
Assert.IsNull (trans.Connection);
}
public int Execute(bool commit = false)
{
SqlConnection connection = new SqlConnection(ConnectionString);
try
{
connection.Open();
IsolationLevel iso = IsolationLevel.ReadCommitted;
if (!commit)
{
iso = IsolationLevel.ReadUncommitted;
}
SqlTransaction transaction = connection.BeginTransaction(iso);
try
{
SqlCommand command = new SqlCommand(CommandText, connection, transaction);
try
{
foreach (KeyValuePair <string, object> k in Parameters)
{
Tuple <object, SqlDbType> p = k.Value as Tuple <object, SqlDbType>;
if (p == null)
{
command.Parameters.AddWithValue(k.Key,
k.Value == null ? DBNull.Value : k.Value);
}
else
{
command.Parameters.Add(new SqlParameter(k.Key, p.Item2)
{
Value = p.Item1 == null ? DBNull.Value : p.Item1
});
}
}
int res = command.ExecuteNonQuery();
if (commit)
{
transaction.Commit();
}
return(res);
}
finally
{
command.Dispose();
}
}
finally
{
transaction.Dispose();
}
}
finally
{
connection.Dispose();
}
}
public void postDeleteTemp(int RequestorID)
{
deletedTemp = false;
sqlStr = "DELETE FROM RequestDetailsTemp WHERE EmpID=@EmpID";
SqlCommand sqlCmd = new SqlCommand(sqlStr);
sqlCmd.Parameters.Add("@EmpID", SqlDbType.Int);
sqlCmd.Parameters["@EmpID"].Value = RequestorID;
sqlCon = new SqlConnection(cnStr);
sqlCon.Open();
SqlTransaction sTrn;
sTrn = sqlCon.BeginTransaction();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlCon;
sqlCmd.Transaction = sTrn;
sqlCmd.ExecuteNonQuery();
sTrn.Commit();
sTrn.Dispose();
deletedTemp = true;
sqlCon.Close();
sqlCon.Dispose();
}
public void postRequestDetails(int RequestorID, int LineOrder, string Narrative, string Account,
string CostCenter, string Project, string SOF, string DEA, string Analysis, double Amount)
{
if (deletedTemp == false) return;
hasDetails = false;
sqlStr = "INSERT INTO RequestDetailsTemp (EmpID, LineOrder, Narrative, " +
" Account, CostCenter, Project, SOF, DEA, Analysis, Amount)" +
" VALUES (@EmpID, @LineOrder, @Narrative, @Account," +
" @CostCenter, @Project, @SOF, @DEA, @Analysis, @Amount)";
sqlCmd = new SqlCommand(sqlStr);
sqlCmd.Parameters.Add("@EmpID", SqlDbType.Int);
sqlCmd.Parameters["@EmpID"].Value = RequestorID;
sqlCmd.Parameters.Add("@LineOrder", SqlDbType.Int);
sqlCmd.Parameters["@LineOrder"].Value = LineOrder;
sqlCmd.Parameters.Add("@Narrative", SqlDbType.VarChar);
sqlCmd.Parameters["@Narrative"].Value = Narrative;
sqlCmd.Parameters.Add("@Account", SqlDbType.VarChar);
sqlCmd.Parameters["@Account"].Value = Account;
sqlCmd.Parameters.Add("@CostCenter", SqlDbType.VarChar);
sqlCmd.Parameters["@CostCenter"].Value = CostCenter;
sqlCmd.Parameters.Add("@Project", SqlDbType.VarChar);
sqlCmd.Parameters["@Project"].Value = Project;
sqlCmd.Parameters.Add("@SOF", SqlDbType.VarChar);
sqlCmd.Parameters["@SOF"].Value = SOF;
sqlCmd.Parameters.Add("@DEA", SqlDbType.VarChar);
sqlCmd.Parameters["@DEA"].Value = DEA;
sqlCmd.Parameters.Add("@Analysis", SqlDbType.VarChar);
sqlCmd.Parameters["@Analysis"].Value = Analysis;
sqlCmd.Parameters.Add("@Amount", SqlDbType.Decimal);
sqlCmd.Parameters["@Amount"].Value = Amount;
sqlCon = new SqlConnection(cnStr);
sqlCon.Open();
//SqlTransaction sTrn;
sTrn = sqlCon.BeginTransaction();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlCon;
sqlCmd.Transaction = sTrn;
sqlCmd.ExecuteNonQuery();
sTrn.Commit();
sTrn.Dispose();
hasDetails = true;
sqlCon.Close();
sqlCon.Dispose();
}
public void IsolationLevel_Transaction_Rolledback ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
trans.Rollback ();
try {
IsolationLevel iso = trans.IsolationLevel;
Assert.Fail ("#A1:" + iso);
} catch (InvalidOperationException ex) {
// This SqlTransaction has completed; it is no
// longer usable
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
} finally {
trans.Dispose ();
}
trans = conn.BeginTransaction ();
trans.Save ("SAVE1");
trans.Rollback ("SAVE1");
Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel, "#B1");
}
private void TryDisposeTransactionAndConnection(SqlTransaction transaction)
{
if (transaction != null)
{
try
{
transaction.Dispose();
}
catch (Exception) { }
if (transaction.Connection != null)
{
try
{
transaction.Connection.Dispose();
}
catch (Exception) { }
}
}
}
public void IsolationLevel_Reader_Open ()
{
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
try {
SqlCommand cmd = new SqlCommand ("select @@version", conn, trans);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel);
}
} finally {
if (trans != null)
trans.Dispose ();
}
}
public void IsolationLevel_Transaction_Disposed ()
{
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
trans.Dispose ();
try {
IsolationLevel iso = trans.IsolationLevel;
Assert.Fail ("#1:" + iso);
} catch (InvalidOperationException ex) {
// This SqlTransaction has completed; it is no
// longer usable
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
Assert.IsNull (ex.InnerException, "#3");
Assert.IsNotNull (ex.Message, "#4");
}
}
public void Dispose_Reader_Open ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
SqlCommand cmd = new SqlCommand ("select * from employee", conn, trans);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
try {
trans.Dispose ();
Assert.Fail ("#A1");
} catch (InvalidOperationException ex) {
// There is already an open DataReader
// associated with this Connection
// which must be closed first
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
}
try {
trans.Dispose ();
Assert.Fail ("#B1");
} catch (InvalidOperationException ex) {
// There is already an open DataReader
// associated with this Connection
// which must be closed first
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#B2");
Assert.IsNull (ex.InnerException, "#B3");
Assert.IsNotNull (ex.Message, "#B4");
}
}
trans.Dispose ();
} finally {
if (trans != null)
trans.Dispose ();
if (conn != null)
conn.Close ();
}
}
public void Dispose ()
{
string sql;
SqlCommand cmd = null;
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Save ("SAVE1");
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6667, 'BangaloreNovell', '1999-03-10', '2006-08-23')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Dispose ();
trans.Dispose ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#1");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6667", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#2");
}
} finally {
if (cmd != null)
cmd.Dispose ();
if (trans != null)
trans.Dispose ();
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
public void Connection_Transaction_Rolledback ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
try {
trans.Rollback ();
Assert.IsNull (trans.Connection);
} finally {
trans.Dispose ();
}
trans = conn.BeginTransaction ();
trans.Save ("SAVE1");
try {
trans.Rollback ("SAVE1");
Assert.AreSame (conn, trans.Connection);
} finally {
trans.Dispose ();
}
}
/// <summary>
/// 开始事务。如果读取时要锁定行，请用枚举：IsolationLevel.Serializable。
/// </summary>
/// <param name="isolationLevel">事务锁定行为</param>
public void BeginTransaction(IsolationLevel isolationLevel)
{
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
try
{
_trans = _conn.BeginTransaction(isolationLevel);
}
catch
{
_conn.Close();
_trans.Dispose();
}
}
[Test] // Rollback ()
public void Rollback1 ()
{
string sql;
SqlCommand cmd = null;
SqlConnection connA = null;
SqlConnection connB = null;
try {
connA = new SqlConnection (connectionString);
connA.Open ();
connB = new SqlConnection (connectionString);
connB.Open ();
using (trans = connA.BeginTransaction ()) {
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
cmd = new SqlCommand (sql, connA, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", connA, trans);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsTrue (reader.Read (), "#A1");
Assert.AreEqual ("NovellBangalore", reader.GetString (0), "#A2");
Assert.IsFalse (reader.Read (), "#A3");
}
trans.Rollback ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", connA);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#B1");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", connB);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#C1");
}
}
} finally {
if (cmd != null)
cmd.Dispose ();
if (connA != null)
connA.Close ();
if (connB != null)
connB.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Save ("SAVE1");
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6667, 'BangaloreNovell', '1999-03-10', '2006-08-23')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Save ("SAVE2");
sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6668, 'Novell', '1997-04-07', '2003-06-25')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Rollback ();
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#D1");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6667", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#E1");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6668", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#F1");
}
} finally {
if (cmd != null)
cmd.Dispose ();
if (trans != null)
trans.Dispose ();
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
trans.Rollback ();
} finally {
if (trans != null)
trans.Dispose ();
}
}
public void Save_TransactionName_Null ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
try {
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
SqlCommand cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
try {
trans.Save ((string) null);
Assert.Fail ("#A1");
} catch (ArgumentException ex) {
// Invalid transaction or invalid name
// for a point at which to save within
// the transaction
Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
Assert.IsNull (ex.ParamName, "#A5");
}
trans.Commit ();
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsTrue (reader.Read (), "#B1");
Assert.AreEqual ("NovellBangalore", reader.GetString (0), "#B2");
Assert.IsFalse (reader.Read (), "#B3");
}
} finally {
if (trans != null)
trans.Dispose ();
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
/// <summary>
/// Save the profiled data list
/// </summary>
private void SaveProfiledData()
{
DataImportUtility.DeleteProfiledData(m_objImportList.id);
m_objBrightPlatformEntity = null;
m_objBrightPlatformEntity = new BrightPlatformEntities(UserSession.EntityConnection);
try
{
m_objConnection = new SqlConnection(UserSession.ProviderConnection);
m_objConnection.Open();
m_objTransaction = m_objConnection.BeginTransaction();
DataImportUtility.ExecuteBulkProcessing("vw_profiled_data", m_objProfiledDataList, m_objConnection, m_objTransaction);
m_objTransaction.Commit();
//MessageBox.Show("Successfully saved profiled data to database", m_MessageBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
m_objTransaction.Rollback();
MessageBox.Show("Transaction rolled back due to the ff:" + Environment.NewLine + ex.Message, m_MessageBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
m_objTransaction.Dispose();
m_objTransaction = null;
m_objConnection.Close();
m_objConnection.Dispose();
m_objConnection = null;
}
public void Commit_Reader_Open ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
SqlCommand cmd;
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
try {
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
cmd = new SqlCommand ("select @@version", conn, trans);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
try {
trans.Commit ();
Assert.Fail ("#A1");
} catch (InvalidOperationException ex) {
// There is already an open DataReader
// associated with this Command which
// must be closed first
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
}
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn, trans);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsTrue (reader.Read (), "#B1");
}
trans.Dispose ();
conn.Close ();
conn.Open ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#C1");
}
} finally {
if (trans != null)
trans.Dispose ();
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
/// <summary>
/// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid SqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>A dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset_Trans(SqlTransaction transaction, CommandType commandType,
string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
DataSet ds = new DataSet();
// Create the DataAdapter & DataSet
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
try
{
// Fill the DataSet using default values for DataTable names, etc
da.Fill(ds);
transaction.Commit();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
if (mustCloseConnection)
transaction.Connection.Close();
// Return the dataset
return ds;
}
catch
{
transaction.Rollback();
ds.Clear();
throw;
}
finally
{
transaction.Dispose();
}
}
}
public void postRequest(int RequestorID, int SupervisorID, int VoucherType, string Descriptions,
int Currency, double InvoiceAmount, double AdvanceUsed, double CashReturn, DateTime RequiredDate,
DateTime ExpectedReturnDate, string VendorPartnerID, string VendorPartnerName, string InvoiceNo,
DateTime InvoiceDate, string PONumber, string OnBehalfID, string OnBehalfName, int Location,
int BudgetHolderID)
{
if (hasDetails == false) return;
sqlStr = "INSERT INTO Request (RequestorID, SupervisorID, VoucherTypeID," +
" Descriptions, CurrencyID, InvoiceAmount, AdvanceUsed, CashReturn, RequiredDate," +
" ExpectedReturnDate, VendorPartnerID, VendorPartnerName, InvoiceNo, InvoiceDate," +
" PONumber, OnBehalfID, OnBehalfName, LocationID, BudgetHolderID)" +
" VALUES (@RequestorID, @SupervisorID, @VoucherTypeID, @Descriptions," +
" @CurrencyID, @InvoiceAmount, @AdvanceUsed, @CashReturn, @RequiredDate," +
" @ExpectedReturnDate, @VendorPartnerID, @VendorPartnerName, @InvoiceNo, @InvoiceDate," +
" @PONumber, @OnBehalfID, @OnBehalfName, @LocationID, @BudgetHolderID)";
sqlCmd = new SqlCommand(sqlStr);
sqlCmd.Parameters.Add("@RequestorID", SqlDbType.Int);
sqlCmd.Parameters["@RequestorID"].Value = RequestorID;
sqlCmd.Parameters.Add("@SupervisorID", SqlDbType.Int);
sqlCmd.Parameters["@SupervisorID"].Value = SupervisorID;
sqlCmd.Parameters.Add("@VoucherTypeID", SqlDbType.Int);
sqlCmd.Parameters["@VoucherTypeID"].Value = VoucherType;
sqlCmd.Parameters.Add("@Descriptions", SqlDbType.VarChar);
sqlCmd.Parameters["@Descriptions"].Value = Descriptions;
sqlCmd.Parameters.Add("@CurrencyID", SqlDbType.Int);
sqlCmd.Parameters["@CurrencyID"].Value = Currency;
sqlCmd.Parameters.Add("@InvoiceAmount", SqlDbType.Decimal);
sqlCmd.Parameters["@InvoiceAmount"].Value = InvoiceAmount;
sqlCmd.Parameters.Add("@AdvanceUsed", SqlDbType.Decimal);
sqlCmd.Parameters["@AdvanceUsed"].Value = AdvanceUsed;
sqlCmd.Parameters.Add("@CashReturn", SqlDbType.Decimal);
sqlCmd.Parameters["@CashReturn"].Value = CashReturn;
sqlCmd.Parameters.Add("@RequiredDate", SqlDbType.DateTime);
sqlCmd.Parameters["@RequiredDate"].Value = RequiredDate;
sqlCmd.Parameters.Add("@ExpectedReturnDate", SqlDbType.DateTime);
sqlCmd.Parameters["@ExpectedReturnDate"].Value = ExpectedReturnDate;
sqlCmd.Parameters.Add("@VendorPartnerID", SqlDbType.VarChar);
sqlCmd.Parameters["@VendorPartnerID"].Value = VendorPartnerID;
sqlCmd.Parameters.Add("@VendorPartnerName", SqlDbType.VarChar);
sqlCmd.Parameters["@VendorPartnerName"].Value = VendorPartnerName;
sqlCmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar);
sqlCmd.Parameters["@InvoiceNo"].Value = InvoiceNo;
sqlCmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime);
sqlCmd.Parameters["@InvoiceDate"].Value = InvoiceDate;
sqlCmd.Parameters.Add("@PONumber", SqlDbType.VarChar);
sqlCmd.Parameters["@PONumber"].Value = PONumber;
sqlCmd.Parameters.Add("@OnBehalfID", SqlDbType.VarChar);
sqlCmd.Parameters["@OnBehalfID"].Value = OnBehalfID;
sqlCmd.Parameters.Add("@OnBehalfName", SqlDbType.VarChar);
sqlCmd.Parameters["@OnBehalfName"].Value = OnBehalfName;
sqlCmd.Parameters.Add("@LocationID", SqlDbType.Int);
sqlCmd.Parameters["@LocationID"].Value = Location;
sqlCmd.Parameters.Add("@BudgetHolderID", SqlDbType.Int);
sqlCmd.Parameters["@BudgetHolderID"].Value = BudgetHolderID;
sqlCon = new SqlConnection(cnStr);
sqlCon.Open();
//SqlTransaction sTrn;
sTrn = sqlCon.BeginTransaction();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlCon;
sqlCmd.Transaction = sTrn;
sqlCmd.ExecuteNonQuery();
string RID = excScalar("SELECT IDENT_CURRENT('[PowerPayment].[dbo].[Request]')");
sqlStr = "INSERT INTO [PowerPayment].[dbo].RequestDetails " +
"SELECT " + RID + ", LineOrder, Narrative, Account, CostCenter, Project, SOF, " +
"DEA, Analysis, Amount FROM PowerPayment.dbo.RequestDetailsTemp WHERE EmpID = '" + RequestorID + "'";
sqlCmd = new SqlCommand(sqlStr);
sqlCmd.CommandType = CommandType.Text;
sqlCmd.Connection = sqlCon;
sqlCmd.Transaction = sTrn;
sqlCmd.ExecuteNonQuery();
sTrn.Commit();
sTrn.Dispose();
sqlCon.Close();
sqlCon.Dispose();
//string sSQL = "DELETE FROM [PowerPayment].[dbo].[RequestDetailsTemp] WHERE EmpID='" + Convert.ToInt32(hfEID.Value) + "'";
//dbo.ExectuteQuery(sSQL);
// string sqlStr = "INSERT INTO PowerPayment.dbo.RequestDetailsTemp" +
//"(EmpID, LineOrder, Narrative, Account, CostCenter, Project, SOF, DEA, Analysis, Amount) VALUES" +
//"('" + Convert.ToInt32(hfEID.Value) + "','" + lOrder + "','" + tbxNarrative.Text.Replace("'", "") + "'," +
//"'" + tbxAccount.Text.Replace("'", "") + "','" + cbxCostCenter.SelectedValue.Replace("'", "") + "','" + cbxProject.SelectedValue.Replace("'", "") + "'," +
//"'" + cbxSOF.SelectedValue.Replace("'", "") + "','" + cbxDEA.SelectedValue.Replace("'", "") + "','" + tbxAnalysis.Text.Replace("'", "") + "','" + tbxAmount.Text + "')";
// dbo.ExectuteQuery(sqlStr);
}
public void Execute(Action <SqlDataReader> f, bool commit = false)
{
SqlConnection connection = new SqlConnection(ConnectionString);
try
{
connection.Open();
IsolationLevel iso = IsolationLevel.ReadCommitted;
if (!commit)
{
iso = IsolationLevel.ReadUncommitted;
}
SqlTransaction transaction = connection.BeginTransaction(iso);
try
{
SqlCommand command = new SqlCommand(CommandText, connection, transaction);
try
{
foreach (KeyValuePair <string, object> k in Parameters)
{
Tuple <object, SqlDbType> p = k.Value as Tuple <object, SqlDbType>;
if (p == null)
{
command.Parameters.AddWithValue(k.Key,
k.Value == null ? DBNull.Value : k.Value);
}
else
{
command.Parameters.Add(new SqlParameter(k.Key, p.Item2)
{
Value = p.Item1 == null ? DBNull.Value : p.Item1
});
}
}
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
if (f != null)
{
f(reader);
}
}
}
finally
{
reader.Close();
}
if (commit)
{
transaction.Commit();
}
}
finally
{
command.Dispose();
}
}
finally
{
transaction.Dispose();
}
}
finally
{
connection.Dispose();
}
}
/// <summary>
/// 执行一条返回第一条记录第一列的SqlCommand命令，通过已经存在的数据库连接。
/// 使用参数数组提供参数
/// </summary>
/// <remarks>
/// 使用示例：
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">一个已经存在的数据库连接</param>
/// <param name="commandType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
/// <param name="commandText">存储过程的名字或者 T-SQL 语句</param>
/// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
/// <returns>返回一个object类型的数据，可以通过 Convert.To{Type}方法转换类型</returns>
public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
return cmd.ExecuteScalar();
}
catch
{
cmd.Parameters.Clear();
trans.Dispose();
throw;
}
}
public void Commit_Transaction_Disposed ()
{
try {
conn = new SqlConnection (connectionString);
conn.Open ();
using (trans = conn.BeginTransaction ()) {
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
SqlCommand cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Dispose ();
try {
trans.Commit ();
Assert.Fail ("#A1");
} catch (InvalidOperationException ex) {
// This SqlTransaction has completed; it is no
// longer usable
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
Assert.IsNull (ex.InnerException, "#A3");
Assert.IsNotNull (ex.Message, "#A4");
}
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#B1");
}
}
} finally {
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the specified SqlTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">A valid SqlTransaction</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery_Trans(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("transaction");
}
if (transaction != null && transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
bool mustCloseConnection = false;
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
int retval;
try
{
// Finally, execute the command
retval = cmd.ExecuteNonQuery();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
finally
{
transaction.Dispose();
}
return retval;
}
[Test] // Rollback ()
public void Rollback1_Transaction_Disposed ()
{
try {
conn = new SqlConnection (connectionString);
conn.Open ();
using (trans = conn.BeginTransaction ()) {
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
SqlCommand cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
trans.Dispose ();
trans.Rollback ();
cmd = new SqlCommand ("SELECT fname FROM employee WHERE id=6666", conn);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
Assert.IsFalse (reader.Read (), "#B1");
}
}
} finally {
if (conn != null)
conn.Close ();
conn = new SqlConnection (connectionString);
conn.Open ();
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
public T ExecuteQuery <T>(CallbackDelegate callback, bool commit = false)
{
SqlConnection connection = new SqlConnection(ConnectionString);
try
{
connection.Open();
IsolationLevel iso = IsolationLevel.ReadCommitted;
if (!commit)
{
iso = IsolationLevel.ReadUncommitted;
}
SqlTransaction transaction = connection.BeginTransaction();
try
{
SqlCommand command = new SqlCommand(CommandText, connection, transaction);
try
{
foreach (KeyValuePair <string, object> k in Parameters)
{
Tuple <object, SqlDbType> p = k.Value as Tuple <object, SqlDbType>;
if (p == null)
{
command.Parameters.AddWithValue(k.Key,
k.Value == null ? DBNull.Value : k.Value);
}
else
{
command.Parameters.Add(new SqlParameter(k.Key, p.Item2)
{
Value = p.Item1 == null ? DBNull.Value : p.Item1
});
}
}
T result = default(T);
SqlDataReader reader = command.ExecuteReader();
try
{
result = callback(reader);
}
finally
{
reader.Close();
}
if (commit)
{
transaction.Commit();
}
return(result);
}
finally
{
command.Dispose();
}
}
finally
{
transaction.Dispose();
}
}
finally
{
connection.Dispose();
}
}
/// <summary>
/// Traverse excel data table and save data
/// </summary>
public void SaveExcelData()
{
if (InvokeRequired)
{
// do not proceed if has not importable data
if (m_objFileDataTable.Rows.Count < 1)
{
MessageBox.Show("No records to import", m_MessageBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
int TotalRecordCount = m_objFileDataTable.Rows.Count;
int RowIndexer = 0;
int ColumnIndexer = 0;
string LogRecorder = string.Empty;
prbImport.Minimum = 0;
this.Invoke(new MethodInvoker(delegate { prbImport.Maximum = 8; })); // represents the blocks traversed, not the actual # of records
this.Invoke(new MethodInvoker(delegate { prbImport.Value = 0; }));
// begin transaction
//m_objConnection = new SqlConnection(ConfigurationManager.AppSettings["DatabaseConnectionString"].ToString());
m_objConnection = new SqlConnection(UserSession.ProviderConnection);
m_objConnection.Open();
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Opening and beginning the import transaction ..."); }));
m_objTransaction = m_objConnection.BeginTransaction();
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Done ..."); }));
this.Invoke(new MethodInvoker(delegate { prbImport.Value = 1; }));
try
{
// import file
this.Invoke(new MethodInvoker(delegate { prbImport.Value = 2; }));
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Inserting import file information. Please kindly wait ..."); }));
this.Invoke(new MethodInvoker(delegate {
m_ImportFileId = DataImportUtility.SaveImportFile(txtImportFile.Text, txtImportListName.Text, (int)cboCustomer.EditValue, (int)cboCampaign.EditValue, (int)cboCountry.EditValue, m_objConnection, m_objTransaction);
}));
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Done ..."); }));
this.Invoke(new MethodInvoker(delegate { prbImport.Value = 3; }));
// import file headers
m_objFileHeaderIdList = null;
m_objImportFileHeaderTable = null;
m_objImportFileHeaderTable = DataImportUtility.CreateImportFileHeaderTable();
m_objFileHeaderIdList = new List<DataImportUtility.ImportFileHeaderIdInstance>();
ColumnIndexer = 1;
LogRecorder = string.Empty;
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Queueing import file headers. Please kindly wait ..."); }));
foreach (DataColumn Item in m_objFileDataTable.Columns)
{
m_objTableRow = null;
m_objTableRow = m_objImportFileHeaderTable.NewRow();
m_objTableRow["imported_file_id"] = m_ImportFileId;
m_objTableRow["column_order"] = ColumnIndexer;
m_objTableRow["column_name"] = Item.ColumnName;
m_objImportFileHeaderTable.Rows.Add(m_objTableRow);
ColumnIndexer ++;
LogRecorder = LogRecorder + "Queueing column header: " + Item.ColumnName + Environment.NewLine;
}
this.Invoke(new MethodInvoker(delegate { this.WriteLog(LogRecorder); }));
this.Invoke(new MethodInvoker(delegate { prbImport.Value = 4; }));
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Batch inserting import file column headers. Please kindly wait ..."); }));
m_objFileHeaderIdList = DataImportUtility.SaveImportFileHeaders(m_ImportFileId, m_objImportFileHeaderTable, m_objConnection, m_objTransaction);
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Done ..."); }));
this.Invoke(new MethodInvoker(delegate { prbImport.Value = 5; }));
// import file details
RowIndexer = 1;
m_objImportFileDetailTable = null;
m_objImportFileDetailTable = DataImportUtility.CreateImportFileDetailTable();
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Queueing import file records. Please kindly wait ..."); }));
//DateTime start = DateTime.Now;
foreach (DataRow Item in m_objFileDataTable.Rows)
{
if (Item == null)
continue;
ColumnIndexer = 1;
string ItemDetail = string.Empty;
LogRecorder = string.Empty;
for (int i = 0; i < Item.ItemArray.Count(); i++)
{
ItemDetail = string.Empty;
ItemDetail = Item.ItemArray[i].ToString();
if (ItemDetail.Length < 1 || ItemDetail == null)
ItemDetail = "";
//{
// //ColumnIndexer++;
// //continue;
//}
m_objTableRow = null;
m_objTableRow = m_objImportFileDetailTable.NewRow();
m_objTableRow["imported_file_header_id"] = (int)m_objFileHeaderIdList[ColumnIndexer - 1].id;
m_objTableRow["row_order"] = (int)RowIndexer;
m_objTableRow["column_value"] = ItemDetail;
m_objImportFileDetailTable.Rows.Add(m_objTableRow);
ColumnIndexer ++;
}
LogRecorder = "Queueing record " + RowIndexer.ToString() + " of " + TotalRecordCount.ToString();
this.Invoke(new MethodInvoker(delegate { this.WriteLog(LogRecorder); }));
RowIndexer++;
}
//DateTime endtime = DateTime.Now;
//TimeSpan elapsedtime = endtime - start;
//MessageBox.Show("total time: " + elapsedtime.ToString());
//this.Invoke(new MethodInvoker(delegate { this.WriteLog(LogRecorder); }));
this.Invoke(new MethodInvoker(delegate { prbImport.Value = 6; }));
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Batch inserting import file row items. Please kindly wait ..."); }));
DataImportUtility.ExecuteBulkProcessing("vw_import_file_details", m_objImportFileDetailTable, m_objConnection, m_objTransaction);
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Done ..."); }));
this.Invoke(new MethodInvoker(delegate { prbImport.Value = 7; }));
// commit transaction
this.Invoke(new MethodInvoker(delegate { cmdCancel.Enabled = false; }));
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Committing transaction ..."); }));
this.Invoke(new MethodInvoker(delegate {
if (File.Exists(m_FileName))
File.Delete(m_FileName);
}));
m_objTransaction.Commit();
this.Invoke(new MethodInvoker(delegate { prbImport.Value = 8; }));
MessageBox.Show("Importing successful!", m_MessageBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information);
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Done ..."); }));
this.Invoke(new MethodInvoker(delegate { this.ReloadImportListView(); }));
}
catch (Exception ex)
{
// rollback transaction
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Rolling back transaction ..."); }));
m_objTransaction.Rollback();
MessageBox.Show("Transaction rolled back due to the following: " + ex.Message, m_MessageBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Warning);
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Exception message:\r\n" + ex.Message); }));
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Done ..."); }));
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Transaction cancelled ..."); }));
}
finally
{
this.Invoke(new MethodInvoker(delegate { this.WriteLog("Disposing transaction object and closing connection ..."); }));
m_objTransaction.Dispose();
m_objTransaction = null;
m_objConnection.Close();
m_objConnection.Dispose();
m_objConnection = null;
this.Invoke(new MethodInvoker(delegate { this.CreateImportLogFile(); }));
this.Invoke(new MethodInvoker(delegate { this.ParentForm.Close(); }));
}
}
}
public void CommitTransaction(SqlTransaction transaction)
{
if (Equals(transaction, null)) return;
var connection = transaction.Connection;
transaction.Commit();
transaction.Dispose();
connection.Close();
}
public void RollbackTransaction(SqlTransaction transaction)
{
if (Equals(transaction, null)) return;
var connection = transaction.Connection;
transaction.Rollback();
transaction.Dispose();
connection.Close();
}
[Test] // Rollback ()
public void Rollback1_Reader_Open ()
{
if (RunningOnMono)
Assert.Ignore ("NotWorking");
SqlCommand cmd;
conn = new SqlConnection (connectionString);
conn.Open ();
trans = conn.BeginTransaction ();
try {
string sql = "INSERT INTO employee (id, fname, dob, doj) VALUES (6666, 'NovellBangalore', '1989-02-11', '2005-07-22')";
cmd = new SqlCommand (sql, conn, trans);
cmd.ExecuteNonQuery ();
cmd.Dispose ();
cmd = new SqlCommand ("select @@version", conn, trans);
using (SqlDataReader reader = cmd.ExecuteReader ()) {
try {
trans.Rollback ();
Assert.Fail ("#1");
} catch (InvalidOperationException ex) {
// There is already an open DataReader
// associated with this Command which
// must be closed first
Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
Assert.IsNull (ex.InnerException, "#3");
Assert.IsNotNull (ex.Message, "#4");
}
}
} finally {
if (trans != null)
trans.Dispose ();
}
}