C# SqlConnection.BeginTransaction方法代码示例

        /// <summary>
        /// Constructs an instance of an atom transaction scope.
        /// </summary>
        /// <param name="kind">Type of transaction scope.</param>
        /// <param name="conn">Connection to use for the transaction scope.</param>
        protected internal SqlStoreTransactionScope(StoreTransactionScopeKind kind, SqlConnection conn)
        {
            this.Kind = kind;
            _conn = conn;

            switch (this.Kind)
            {
                case StoreTransactionScopeKind.ReadOnly:
                    SqlUtils.WithSqlExceptionHandling(() =>
                    {
                        _tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                    });
                    break;
                case StoreTransactionScopeKind.ReadWrite:
                    SqlUtils.WithSqlExceptionHandling(() =>
                    {
                        _tran = conn.BeginTransaction(IsolationLevel.RepeatableRead);
                    });
                    break;
                default:
                    // Do not start any transaction.
                    Debug.Assert(this.Kind == StoreTransactionScopeKind.NonTransactional);
                    break;
            }
        }

        static void Main(string[] args)
        {
            using (var sqlConnection = new SqlConnection("connectionString"))
            {
                sqlConnection.Open();

                // Create the Service Broker Service and Queue if they don't exist.
                using (var sqlTransaction = sqlConnection.BeginTransaction())
                {
                    ServiceBrokerWrapper.CreateServiceAndQueue(sqlTransaction, @"[\\Example\Service]", "ExampleServiceQueue");
                    sqlTransaction.Commit();
                }

                // Send a single message to a Service endpoint and immediately
                // end this side of the conversation
                using (var sqlTransaction = sqlConnection.BeginTransaction())
                {
                    var messageData = new byte[1000];
                    var conversationHandle =
                        ServiceBrokerWrapper.SendOne(
                        sqlTransaction
                        , @"[\\Example\Service2]"
                        , @"[\\Example\Service]"
                        , "MessageContractName"
                        , "MessageType"
                        , messageData);
                    sqlTransaction.Commit();
                }

                // Wait for a message to be available on the queue. We will
                // wait for some number of milliseconds. If the timeout expires
                // then the method returns "null". Otherwise it will contain
                // the message received from the queue.
                using (var sqlTransaction = sqlConnection.BeginTransaction())
                {
                    Message message = null;
                    while (message == null)
                    {
                        message = ServiceBrokerWrapper.WaitAndReceive(sqlTransaction, "ExampleServiceQueue", 60 * 60 * 1000);
                    }
                    // ...handle message...

                    // If we rollback the transaction then the message will
                    // return to the queue to be handled again.
                    // If we commit the transaction then we're done.
                    sqlTransaction.Commit();
                }
            }
        }

        private void OpenConnection()
        {
            connection = new SqlConnection(AppSettings.AdminConnectionString);
            connection.Open();

            transaction = connection.BeginTransaction();
        }

        public void Import(string connectionString, StatbookModel statbook, bool assumeATeams)
        {
            _connection = new SqlConnection(connectionString);
            try
            {
                _connection.Open();
                _transaction = _connection.BeginTransaction();

                // insert leagues
                LeagueGateway leagueGateway = new LeagueGateway(_connection, _transaction);
                var leagues = leagueGateway.GetAllLeagues();
                League homeLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.HomeTeam.LeagueName.ToLower());
                League awayLeague = leagues.FirstOrDefault(l => l.Name.ToLower() == statbook.AwayTeam.LeagueName.ToLower());
                int maxID = leagues.Select(l => l.ID).Max();
                if(homeLeague == null)
                {
                    homeLeague = leagueGateway.GetLeague(maxID + 1, statbook.HomeTeam.LeagueName, statbook.Date, false);
                    maxID++;
                }
                if(awayLeague == null)
                {
                    awayLeague = leagueGateway.GetLeague(maxID + 1, statbook.AwayTeam.LeagueName, statbook.Date, false);
                    maxID++;
                }

                // insert teams
                TeamGateway teamGateway = new TeamGateway(_connection, _transaction);
                Team homeTeam, awayTeam;
                if (assumeATeams)
                {
                    homeTeam = teamGateway.GetATeam(homeLeague.ID);
                    awayTeam = teamGateway.GetATeam(awayLeague.ID);

                }
                else
                {
                    homeTeam = teamGateway.GetTeam(statbook.HomeTeam.Name, homeLeague.ID, "A", false);
                    awayTeam = teamGateway.GetTeam(statbook.AwayTeam.Name, awayLeague.ID, "A", false);
                }

                // insert bout
                BoutGateway boutGateway = new BoutGateway(_connection, _transaction);
                if(!boutGateway.DoesBoutExist(homeTeam.ID, awayTeam.ID, statbook.Date))
                {
                    Bout bout = boutGateway.GetBout(homeTeam.ID, awayTeam.ID, statbook.Date);
                    BoutDataImport(statbook, bout, homeTeam, awayTeam);
                }
                else
                {
                    // bout already exists
                    Console.WriteLine(string.Format("Bout between {0} and {1} on {2} already exists.", homeTeam.Name, awayTeam.Name, statbook.Date));
                }

                _transaction.Commit();
            }
            finally
            {
                _connection.Close();
            }
        }

 /// <summary>
 /// 运行SQL语句
 /// </summary>
 /// <param name="sql">SQL语句</param>
 /// <returns></returns>
 public string RunSql(string sql)
 {
     if (!string.IsNullOrWhiteSpace(sql))
     {
         SqlConnection conn = new SqlConnection(RDBSHelper.ConnectionString);
         conn.Open();
         using (SqlTransaction trans = conn.BeginTransaction())
         {
             string[] sqlList = StringHelper.SplitString(sql, "-sqlseparator-");
             foreach (string item in sqlList)
             {
                 if (!string.IsNullOrWhiteSpace(item))
                 {
                     try
                     {
                         RDBSHelper.ExecuteNonQuery(CommandType.Text, item);
                         trans.Commit();
                     }
                     catch (Exception ex)
                     {
                         trans.Rollback();
                         return ex.Message;
                     }
                 }
             }
         }
         conn.Close();
     }
     return string.Empty;
 }

 private void BtnUpdate_Click(object sender, EventArgs e)
 {
     using (SqlConnection Con = new SqlConnection(FXFW.SqlDB.SqlConStr))
     {
         SqlCommand Cmd = new SqlCommand("", Con);
         SqlTransaction trn = null;
         try
         {
             Con.Open();
             trn = Con.BeginTransaction();
             Cmd.Transaction = trn;
             Cmd.CommandText = string.Format("Delete From UserRoles Where UserId = {0}", LUEItems.EditValue);
             Cmd.ExecuteNonQuery();
             foreach (DataRow row in RolesTbl.Rows)
             {
                 if (row["Active"].ToString() == "1")
                 {
                     Cmd.CommandText = String.Format("INSERT INTO UserRoles (UserId, RoleId) VALUES ({0}, {1})", LUEItems.EditValue, row["RoleID"].ToString());
                     Cmd.ExecuteNonQuery();
                 }
             }
             trn.Commit();
             LoadRoles(LUEItems.EditValue.ToString());
             Program.ShowMsg("تم التعديل ..", false, this);
             Program.Logger.LogThis("تم التعديل ..", Text, FXFW.Logger.OpType.success, null, null, this);
         }
         catch (Exception ex)
         {
             trn.Rollback();
             MessageBox.Show(ex.Message);
             Program.Logger.LogThis(null, Text, FXFW.Logger.OpType.fail, ex, null, this);
         }
     }
 }

 public static void SetUp()
 {
     Console.WriteLine("Assembly Setup beginning...");
     if (ConfigurationManager.AppSettings["connectionStringName"] == "subtextExpress")
     {
         //For use with SQL Express. If you use "subtextData", we assume you already have the database created.
         DatabaseHelper.CreateAndInstallDatabase(Config.ConnectionString, Config.ConnectionString.Database,
                                                 "App_Data");
     }
     else
     {
         using (var connection = new SqlConnection(Config.ConnectionString))
         {
             connection.Open();
             using (SqlTransaction transaction = connection.BeginTransaction())
             {
                 try
                 {
                     ScriptHelper.ExecuteScript("StoredProcedures.sql", transaction);
                     transaction.Commit();
                 }
                 catch (Exception)
                 {
                     transaction.Rollback();
                 }
             }
         }
     }
 }

 /// <summary>
 /// Creates new ability in database.
 /// </summary>
 /// <param name="abilityname">Name of ability.</param>
 /// <returns>ID of new database record.</returns>
 public override int CreateAbility(string abilityname)
 {
     int id = -1;
     using (SqlConnection connection = new SqlConnection(this.ConnectionString))
     {
         connection.Open();
         SqlTransaction transaction = connection.BeginTransaction();
         SqlCommand command = connection.CreateCommand();
         command.Transaction = transaction;
         command.CommandText =
             string.Format(
                 "INSERT INTO {0} (Name) VALUES  (@Ability) SELECT @@IDENTITY",
                 DBAbilityTableName);
         command.Parameters.Add("@Ability", SqlDbType.NVarChar).Value =
             abilityname;
         try
         {
             id = Convert.ToInt32(ExecuteScalar(command));
             transaction.Commit();
         }
         catch
         { transaction.Rollback(); }
     }
     return id;
 }

        public static bool AddTaskReplyAttachments(string taskid, string replyid, List<Attachment> attachments,string userid,string clientid)
        {
            SqlConnection conn = new SqlConnection(TaskDAL.ConnectionString);
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();


            foreach (var attachment in attachments)
            {
                if (!TaskDAL.BaseProvider.AddTaskReplyAttachment(taskid,replyid,attachment.Type,
                    attachment.ServerUrl,attachment.FilePath,attachment.FileName,attachment.OriginalName,attachment.ThumbnailName,attachment.Size,
                    userid,clientid, tran))
                {
                    tran.Rollback();
                    conn.Dispose();

                    return false;
                }
            }

            tran.Commit();
            conn.Dispose();

            return true;
        }

 public bool AddProduct(ProductsDto productsDto)
 {
     var cn = new SqlConnection(GetConnection());
     SqlTransaction trx = null;
     var isInsert = false;
     try
     {
         cn.Open();
         trx = cn.BeginTransaction();
         string cmdText = " insert into Products(ProductName,ProductCategory,MemberAnaylst,OrganizationName,TenureId,StyleResearchId,StrategyId,FrequencyCall,FrequencyCallType,Logo,CreateUserId,CreateTimeStamp,ModifiedUserId,ModifiedTimeStamp) " +
                          "values('" + productsDto.ProductName + "'," + productsDto.ProductCategory + "," + productsDto.MemberAnaylst + ",'" + productsDto.OrganizationName + "'," + productsDto.TenureId + "," + productsDto.StyleResearchId + "," + productsDto.StrategyId + "," + productsDto.FrequencyCall + ",'" + productsDto.FrequencyCallType + "','" + productsDto.Logo + "'," + productsDto.CreateUserId + ", '" + DateTime.Now.ToString("yyyy-MM-dd") + "'," + productsDto.ModifiedUserId + ",'" + DateTime.Now.ToString("yyyy-MM-dd") + "') select Scope_Identity();";
         var cmd = new SqlCommand(cmdText, cn) { Transaction = trx };
         var productId = Convert.ToInt32(cmd.ExecuteScalar());
         foreach (var documents in productsDto.ProductDocumentDtos)
             (new SqlCommand("insert into ProductDocument(ProductId,FileName,DocumentName) values( " + productId + ",'" + documents.FileName + "','" + documents.DocumentName + "')", cn) { Transaction = trx }).ExecuteNonQuery();
         trx.Commit();
         isInsert = true;
         cn.Close();
     }
     catch (Exception)
     {
         if (trx != null) trx.Rollback();
         cn.Close();
     }
     return isInsert;
 }

        public async Task<IReadOnlyCollection<Entry>> Query()
        {
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var command = new SqlCommand(@"
SELECT [Owner], [Type], [Value] FROM [Data] WHERE [Owner] <> @Owner
", connection, transaction))
                    {
                        command.Parameters.AddWithValue("Owner", owner).DbType = DbType.AnsiString;

                        var results = new List<Entry>();
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            while (reader.Read())
                            {
                                results.Add(new Entry((string)reader[0], (string)reader[1], (string)reader[2]));
                            }
                        }
                        return results;
                    }
                }
            }
        }

        protected void btnGuardar_Click(object sender, EventArgs e)
        {
            if (!currentUser.ValidarPrivilegio("Insertar Faltas"))
                return;

            bool exitoooooooo = true;
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            SqlTransaction trans = con.BeginTransaction();
            try
            {
                guardarAlumno(con,trans);
                guargarFalta(con,trans);
                guargarEvidencias(con, trans);
                trans.Commit();
                limpiar();
            }
            catch (Exception ex)
            {
                exitoooooooo = false;
                trans.Rollback();
                Session["error"] = ex.StackTrace;

            }
            con.Close();

            if(!exitoooooooo)
                Response.Redirect("~/Seguridad/Error.aspx");
            else
                Response.Redirect("ListaFaltas.aspx");
        }

        private IList<Project> getUserProjects(int userID, String condition)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                SqlCommand command = connection.CreateCommand();
                command.Transaction = transaction;

                String subQuery = String.IsNullOrEmpty(condition)
                                      ? String.Empty
                                      : String.Format(" AND {0}", condition);

                StringBuilder query = new StringBuilder();
                query.AppendFormat(" SELECT {0}.ID, {0}.Name, {0}.Description", DBProjectsTableName);
                query.AppendFormat(" FROM {0} INNER JOIN {1} ON {0}.ID = {1}.ProjectID", DBProjectsTableName, DBProjectsUserTableName);
                query.AppendFormat(" WHERE {0}.UserID = @userID {1}",
                                   DBProjectsUserTableName,
                                   subQuery);
                query.AppendFormat(" ORDER BY {0}.Name", DBProjectsTableName);

                command.CommandText = query.ToString();
                command.Parameters.Add("@userID", SqlDbType.Int).Value = userID;
                command.CommandText = query.ToString();

                using (IDataReader reader = ExecuteReader(command))
                {
                    return getAllProjectsDataFromReader(reader);
                }
            }
        }

 private void BtnDelete_Click(object sender, EventArgs e)
 {
     if (MessageBox.Show("هل ترغب في الاستمرار؟", "تأكــــيد", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == System.Windows.Forms.DialogResult.No)
     {
         return;
     }
     SqlTransaction Trn = null;
     using (SqlConnection Con = new SqlConnection(FXFW.SqlDB.SqlConStr))
     {
         SqlCommand Cmd = new SqlCommand(String.Format("DELETE FROM CustomersSuppliers WHERE (PERSONID = {0})", LUEItems.EditValue), Con);
         try
         {
             Con.Open();
             Trn = Con.BeginTransaction();
             Cmd.Transaction = Trn;
             Cmd.ExecuteNonQuery();
             if (CustomerTbl.Rows[LUEItems.ItemIndex]["AccountId"].ToString() != string.Empty)
             {
                 Cmd.CommandText = "DELETE FROM TBL_Accountes Where AccountId = " + CustomerTbl.Rows[LUEItems.ItemIndex]["AccountId"].ToString();
                 Cmd.ExecuteNonQuery();
             }
             Trn.Commit();
             LoadData();
             GetNewAccountID();
             LUEItems.ItemIndex = -1;
             MessageBox.Show("تم الحـــذف ", "حـــــذف", MessageBoxButtons.OK, MessageBoxIcon.Information);
         }
         catch (Exception ex)
         {
             Trn.Rollback();
             MessageBox.Show(ex.Message);
         }
     }
 }

        protected void ButInput_Click(object sender, System.EventArgs e)
        {
            //���浽���ݿ�
            int i=0;
            string strConn=ConfigurationSettings.AppSettings["strConn"];
            SqlConnection ObjConn =new SqlConnection(strConn);
            ObjConn.Open();
            SqlTransaction ObjTran=ObjConn.BeginTransaction();
            SqlCommand ObjCmd=new SqlCommand();
            ObjCmd.Transaction=ObjTran;
            ObjCmd.Connection=ObjConn;
            try
            {
                ObjCmd.CommandText="Update UserInfo set DeptID=0 where DeptID="+intDeptID+"";
                ObjCmd.ExecuteNonQuery();

                for(i=0;i<LBSelected.Items.Count;i++)
                {
                    ObjCmd.CommandText="Update UserInfo set DeptID="+intDeptID+" where UserID="+LBSelected.Items[i].Value+"";
                    ObjCmd.ExecuteNonQuery();
                }

                ObjTran.Commit();
            }
            catch
            {
                ObjTran.Rollback();
            }
            finally
            {
                ObjConn.Close();
                ObjConn.Dispose();
            }
            this.RegisterStartupScript("newWindow","<script language='javascript'>window.close();</script>");
        }