C# OleDbConnection.BeginTransaction方法代码示例

		public void run()
		{
			Exception exp = null;

			OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
			OleDbTransaction txn = null;

			try
			{
				BeginCase("BeginTransaction - connection close");
				try
				{
					txn = con.BeginTransaction();
				}
				catch (Exception ex) {exp = ex;}
				Compare(exp.GetType().FullName ,typeof(InvalidOperationException).FullName );
				exp=null;
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			con.Open();

			try
			{
				BeginCase("BeginTransaction - connection close");
				txn = con.BeginTransaction();
				Compare(txn == null,false );
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			if (con.State == ConnectionState.Open) con.Close();

		}

        /// <summary>
        /// Test OleDbTransaction class
        /// </summary>    
        public static void Test_Transaction()
        {
            using (OleDbConnection conn = new OleDbConnection())
            {
                OleDbTransaction transaction = null;
                conn.ConnectionString = TestCases.connString;
                conn.Open();

                TestCases.ExecuteSQL("drop table if exists t", conn);

                transaction = conn.BeginTransaction();

                string sql = "create table t(idx integer)";
                using (OleDbCommand command = new OleDbCommand(sql, conn))
                {
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                }

                int tablesCount = TestCases.GetTablesCount("t", conn, transaction);
                Assert.IsTrue(tablesCount == 1);

                transaction.Rollback();

                //Verify the table does not exist
                tablesCount = GetTablesCount("t", conn, transaction);
                Assert.IsTrue(tablesCount == 0);

                transaction = conn.BeginTransaction();

                sql = "create table t(idx integer)";
                using (OleDbCommand command = new OleDbCommand(sql, conn))
                {
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                }

                tablesCount = GetTablesCount("t", conn, transaction);
                Assert.IsTrue(tablesCount == 1);

                transaction.Commit();

                tablesCount = GetTablesCount("t", conn, transaction);
                Assert.IsTrue(tablesCount == 1);

                transaction = conn.BeginTransaction();

                TestCases.ExecuteSQL("drop table t", conn, transaction);

                transaction.Commit();

                tablesCount = GetTablesCount("t", conn);
                Assert.IsTrue(tablesCount == 0);
            }
        }

	public void run()
	{

		Exception exp = null;
		OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
		con.Open();
		OleDbTransaction txn = con.BeginTransaction();
		OleDbCommand cmd = new OleDbCommand("Select * From Employees", con);

		try
		{
			BeginCase("check Transaction property - default");
			Compare(cmd.Transaction==null , true);
		} 
		catch(Exception ex){exp = ex;}
		finally{EndCase(exp); exp = null;}

		cmd.Transaction = txn;

		try
		{
			BeginCase("check Transaction property");
			Compare(cmd.Transaction , txn);
		} 
		catch(Exception ex){exp = ex;}
		finally{EndCase(exp); exp = null;}
		
		if (con.State == ConnectionState.Open) con.Close();

	}

 public RowCounts ApplyTableChanges(TableConf table, TableConf archiveTable, string dbName, long CTID, string CTDBName, bool isConsolidated)
 {
     var cmds = new List<InsertDelete>();
     cmds.Add(BuildApplyCommand(table, dbName, CTDBName, CTID));
     if (archiveTable != null) {
         cmds.Add(BuildApplyCommand(archiveTable, dbName, CTDBName, CTID));
     }
     var connStr = buildConnString(dbName);
     var rowCounts = new RowCounts(0, 0);
     using (var conn = new OleDbConnection(connStr)) {
         conn.Open();
         var trans = conn.BeginTransaction();
         foreach (var id in cmds) {
             id.delete.Transaction = trans;
             id.delete.Connection = conn;
             id.delete.CommandTimeout = Config.QueryTimeout;
             logger.Log(id.delete.CommandText, LogLevel.Trace);
             int deleted = id.delete.ExecuteNonQuery();
             logger.Log(new { Table = table.Name, message = "Rows deleted: " + deleted }, LogLevel.Info);
             id.insert.Transaction = trans;
             id.insert.Connection = conn;
             id.insert.CommandTimeout = Config.QueryTimeout;
             logger.Log(id.insert.CommandText, LogLevel.Trace);
             int inserted = id.insert.ExecuteNonQuery();
             logger.Log(new { Table = table.Name, message = "Rows inserted: " + inserted }, LogLevel.Info);
             rowCounts = new RowCounts(rowCounts.Inserted + inserted, rowCounts.Deleted + deleted);
         }
         trans.Commit();
     }
     return rowCounts;
 }

        public override bool Delete()
        {
            var sqlStatement = "Delete From Path ";
            using (var conn = new OleDbConnection(this.ConnectionString))
            {
                conn.Open();
                var trans = conn.BeginTransaction();
                try
                {
                    AccessHelper.ExecuteNonQuery(trans, sqlStatement);
                    trans.Commit();

                    return true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    Logger.Error(ex.Message, ex);
                    return false;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

 /// <summary>
 /// 执行多条SQL语句，实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">多条SQL语句</param>		
 public static void ExecuteSqlTran(ArrayList SQLStringList)
 {
     using (OleDbConnection conn = new OleDbConnection(connectionString))
     {
         conn.Open();
         OleDbCommand cmd = new OleDbCommand();
         cmd.Connection = conn;
         OleDbTransaction tx = conn.BeginTransaction();
         cmd.Transaction = tx;
         try
         {
             for (int n = 0; n < SQLStringList.Count; n++)
             {
                 string strsql = SQLStringList[n].ToString();
                 if (strsql.Trim().Length > 1)
                 {
                     cmd.CommandText = strsql;
                     cmd.ExecuteNonQuery();
                 }
             }
             tx.Commit();
         }
         catch (System.Data.OleDb.OleDbException E)
         {
             tx.Rollback();
             throw new Exception(E.Message);
         }
     }
 }

		public void run()
		{
			OleDbConnection con = null;
			OleDbTransaction txn;
			Exception exp = null;
			try
			{
				BeginCase("OleDbTransaction Rollback");

				//
				//prepare data
				base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);

				string Result = "";
				con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
				con.Open();
				txn = con.BeginTransaction();
				OleDbCommand cmd = new OleDbCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn);
				cmd.ExecuteNonQuery();
				txn.Rollback();

				//
				//
				cmd = new OleDbCommand("Select LastName From Employees Where EmployeeID = 100", con);
				Result = cmd.ExecuteScalar().ToString();
				Compare(Result,"Last100" );
				this.Log(Result);

			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			if (con.State == ConnectionState.Open) con.Close();
		}

        public int product_service(string service_id, string product_id, int customer_id)
        {
            int row=0;

               cn = new OleDbConnection("Provider=MSDAORA;Data Source=192.168.0.217/orcl;Persist Security Info=True;User ID=hr;Password=hr;");

            cmd = cn.CreateCommand();
            try
            {
                cn.Open();

                string s = "update product_services set product_id='" + product_id + "' where service_id=(select service_id from customer_services where customer_id=" + customer_id + " and service_id='" + service_id + "')";
                Trans = cn.BeginTransaction();
                cmd = new OleDbCommand(s, cn,Trans);
                cmd.Transaction = Trans;
                row= cmd.ExecuteNonQuery();
                Trans.Commit();

            }
            catch (Exception e)
            {
                string s = e.Message;
                Trans.Rollback();
            }
            finally
            {
                cn.Close();
            }
            return row;
        }

    public bool DeleteContentType(string id)
    {
        String strCmd1 = string.Format("DELETE FROM ContentType WHERE ID = {0}" , id);

        String strCmd2 = string.Format("DELETE FROM Contents WHERE TypeCode = (SELECT TypeCode FROM ContentType WHERE ID = {0})",id);

        OleDbConnection conn = new OleDbConnection(StrConn);
        OleDbTransaction transaction = null;
        bool flag = false;
        try
        {
            conn.Open();
            transaction = conn.BeginTransaction();

            OleDbCommand contentCommand = new OleDbCommand(strCmd2, conn, transaction);
            contentCommand.ExecuteNonQuery();

            OleDbCommand typeCommand = new OleDbCommand(strCmd1, conn,transaction);
            typeCommand.ExecuteNonQuery();

            transaction.Commit();
            flag = true;
        }
        catch (Exception ex)
        {
            flag = false;
            transaction.Rollback();
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
        return flag;
    }

        public bool Execute(string qry)
        {
            ConnectionString cnString = new ConnectionString();
            IDbConnection cn = new OleDbConnection(cnString.GetConnString());
            IDbCommand cmd = new OleDbCommand(qry, (OleDbConnection)cn);
            cmd.CommandType = CommandType.StoredProcedure;

            try
            {
                cn.Open();
                IDbTransaction tran = cn.BeginTransaction();
                cmd.Transaction = tran;

                int affectedRows = cmd.ExecuteNonQuery();
                Console.WriteLine(affectedRows);
                if (affectedRows > 0)
                {
                    tran.Commit();
                    return true;
                }
                else
                {
                    tran.Rollback();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                cn.Close();
            }
            return false;
        }

        protected void BtnInsertJjd_Click(object sender, EventArgs e)
        {
            string jjdNo_;
            string psQty_;
            string reqObjid_;
            string reqVer_ = "";
            string reqMsg_;

            jjdNo_ = TxtJjdNo.Text;
            //�򽻽ӵ����� jjd_line,�� ����jjd_no����jjd_line
            using (OleDbConnection conn = new OleDbConnection(DBHelper.OleConnectionString))
            {
                conn.Open();
                OleDbTransaction tr = conn.BeginTransaction();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Transaction = tr;
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;

                if (jjdNo_ == "")
                {
                    Misc.Message(this.GetType(),ClientScript, "err01 �������ӵ�ʧ��!");
                    return;
                }

                cmd.CommandText = "jp_jjd_line_api.new_";
                cmd.Parameters.Clear();

                cmd.Parameters.Add("v_jjd_no", OleDbType.VarChar).Value = jjdNo_;
                cmd.Parameters.Add("v_req_objid", OleDbType.VarChar);
                cmd.Parameters.Add("v_req_rowversion", OleDbType.VarChar);
                cmd.Parameters.Add("v_qty", OleDbType.Single);
                cmd.Parameters.Add("v_msg", OleDbType.VarChar, 1000).Direction = ParameterDirection.Output;

                foreach (GridViewRow grv in GVReqData.Rows)
                {
                    psQty_ = ((TextBox)(grv.FindControl("TxtJjdLineQty"))).Text;
                    if (psQty_ == "") continue;
                    reqObjid_ = grv.Cells[13].Text;
                    reqVer_ = grv.Cells[14].Text;
                    cmd.Parameters["v_req_objid"].Value = reqObjid_;
                    cmd.Parameters["v_req_rowversion"].Value = reqVer_;
                    cmd.Parameters["v_qty"].Value = Convert.ToSingle(psQty_);

                    cmd.ExecuteNonQuery();

                    reqMsg_ = cmd.Parameters["v_msg"].Value.ToString();

                    if (reqMsg_ != "1")
                    {
                        Misc.Message(this.GetType(),ClientScript, string.Format("err02 �������ӵ�ʧ�ܣ�{0}", reqMsg_));
                        return;
                    }
                }
                tr.Commit();
            }

            DisplayJjd(jjdNo_);
        }

 /// <summary>
 /// Initializes a new instance.
 /// </summary>
 /// <param name="connectionString">OleDB Connection string</param>
 /// <param name="useTransaction">True if you want to use a transaction</param>
 public SqlHelper(string connectionString, bool useTransaction) {
     _connection = new OleDbConnection(connectionString);
     _connection.Open();
  
     if (useTransaction) {
         _transaction = _connection.BeginTransaction();
     }
 }

        /// <summary>
        /// 增加一条数据,及其子表数据
        /// </summary>
        public int Add(Model.manager_role model)
        {
            int newId;
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into dt_manager_role(");
                        strSql.Append("role_name,role_type)");
                        strSql.Append(" values (");
                        strSql.Append("@role_name,@role_type)");

                        OleDbParameter[] parameters = {
					            new OleDbParameter("@role_name", OleDbType.VarChar,100),
					            new OleDbParameter("@role_type", OleDbType.TinyInt,1)};
                        parameters[0].Value = model.role_name;
                        parameters[1].Value = model.role_type;

                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        //取得新插入的ID
                        newId = GetMaxId(conn, trans);

                        StringBuilder strSql2;
                        foreach (Model.manager_role_value models in model.manager_role_values)
                        {
                            strSql2 = new StringBuilder();
                            strSql2.Append("insert into dt_manager_role_value(");
                            strSql2.Append("role_id,channel_name,channel_id,action_type)");
                            strSql2.Append(" values (");
                            strSql2.Append("@role_id,@channel_name,@channel_id,@action_type)");
                            OleDbParameter[] parameters2 = {
						            new OleDbParameter("@role_id", OleDbType.Integer,4),
						            new OleDbParameter("@channel_name", OleDbType.VarChar,255),
						            new OleDbParameter("@channel_id", OleDbType.Integer,4),
						            new OleDbParameter("@action_type", OleDbType.VarChar,100)};
                            parameters2[0].Value = newId;
                            parameters2[1].Value = models.channel_name;
                            parameters2[2].Value = models.channel_id;
                            parameters2[3].Value = models.action_type;

                            DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return newId;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.manager_role model)
        {
            using (OleDbConnection conn = new OleDbConnection(DbHelperOleDb.connectionString))
            {
                conn.Open();
                using (OleDbTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into " + databaseprefix + "manager_role(");
                        strSql.Append("role_name,role_type,is_sys)");
                        strSql.Append(" values (");
                        strSql.Append("@role_name,@role_type,@is_sys)");
                        OleDbParameter[] parameters = {
                                new OleDbParameter("@role_name", OleDbType.VarChar,100),
                                new OleDbParameter("@role_type", OleDbType.Integer,4),
                                new OleDbParameter("@is_sys", OleDbType.Integer,4)};
                        parameters[0].Value = model.role_name;
                        parameters[1].Value = model.role_type;
                        parameters[2].Value = model.is_sys;
                        DbHelperOleDb.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        //取得新插入的ID
                        model.id = GetMaxId(conn, trans);

                        StringBuilder strSql2;
                        if (model.manager_role_values != null)
                        {
                            foreach (Model.manager_role_value modelt in model.manager_role_values)
                            {
                                strSql2 = new StringBuilder();
                                strSql2.Append("insert into " + databaseprefix + "manager_role_value(");
                                strSql2.Append("role_id,nav_name,action_type)");
                                strSql2.Append(" values (");
                                strSql2.Append("@role_id,@nav_name,@action_type)");
                                OleDbParameter[] parameters2 = {
                                        new OleDbParameter("@role_id", OleDbType.Integer,4),
                                        new OleDbParameter("@nav_name", OleDbType.VarChar,100),
                                        new OleDbParameter("@action_type", OleDbType.VarChar,50)};
                                parameters2[0].Value = model.id;
                                parameters2[1].Value = modelt.nav_name;
                                parameters2[2].Value = modelt.action_type;
                                DbHelperOleDb.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);
                            }
                        }
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        return -1;
                    }
                }
            }
            return model.id;
        }

 void Generate(OleDbConnection connection, DateTime startDate, int daysCount) {
     using(var transaction = connection.BeginTransaction()) {
         for(int n = 0; n < daysCount; n++) {
             Console.Write("{0} of {1}\r", n + 1, daysCount);
             GenerateDay(connection, transaction, n, startDate.AddDays(n));
             RaiseProgress((double)n / (double)daysCount);
         }
         transaction.Commit();
     }
 }