c#事务

简单记录一下 自己项目中用到的技术

C#实现的事务
1、创建事务的结构

SqlConnection   sqlConnection   =   new   SqlConnection();  
  ...初始化连接  
  //   开启事务  
  SqlTransaction   sqlTransaction   =   sqlConnection.BeginTransaction();  
  //   将事务应用于Command  
  SqlCommand   sqlCommand   =   new   SqlCommand();  
  sqlCommand.Connection   =   sqlConnection;   
  sqlCommand.Transaction   =   sqlTransaction;     
  try  
  {  
  //   利用sqlcommand进行数据操作  
  ...  
  //   成功提交  
  sqlTransaction.Commit();  
  }  
  catch(Exception   ex)  
  {  
  //   出错回滚  
  sqlTransaction.Rollback();  
  }   

2、简单例子

          {   
                  DataTable   dt   =   new   DataTable();   
                  System.Data.SqlClient.SqlConnection   cnn   =   new   System.Data.SqlClient.SqlConnection("连接字符串");  
                  System.Data.SqlClient.SqlCommand   cm   =   new   System.Data.SqlClient.SqlCommand();  
                  cm.Connection   =   cnn;  
                  cnn.Open();  
                  System.Data.SqlClient.SqlTransaction   trans   =   cnn.BeginTransaction();  
                  try  
                  {  
                          foreach(DataRow   dr   in   dt.Rows)  
                          {  
                                  cm.CommandText   =   "update   [表]   set   [数量]   =   @amount   where   productID   =   @productID";  
                                  cm.Parameters.Add("@amount",SqlDbType.Int);  
                                  cm.Parameters["@amount"].Value   =   Convert.ToInt32(dr["amount"]);  
                                  cm.Parameters.Add("@productID",SqlDbType.VarChar);  
                                  cm.Parameters["@productID"].Value   =   dr["productID"].ToString();  
                                  cm.ExecuteNonQuery();  
                          }  
                          trans.Commit();  
                  }  
                  catch  
                  {  
                          trans.Rollback();  
                  }  
                  finally  
                  {  
                          cnn.Close();  
                          trans.Dispose();  
                          cnn.Dispose();  
                  }  
          }

另外一个例子，稍微复杂一点，注意每次执行的时候要清除上一次的参数

// <summary>
    /// 添加，编辑问卷
    /// </summary>
    /// <param name="questionnaire"></param>
    /// <returns></returns>
    public int AddQuestionnaire(Entity.Questionnaire.Questionnaire questionnaire)
    {
        int identity = 0;
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnectionStringTrainingOA"].ConnectionString);
        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;
        connection.Open();
        transaction = connection.BeginTransaction();
        command.Transaction = transaction;
        command.CommandType = CommandType.StoredProcedure;

        try
        {
            //1写入问卷
            command.CommandText = "Questionnaire_ADD";
            SqlParameter[] parms = new SqlParameter[] { 
            new SqlParameter("@ID",SqlDbType.Int),
            new SqlParameter("@QuestionnaireTitle",SqlDbType.NVarChar)
            };
            parms[0].Value = questionnaire.ID;
            parms[1].Value = questionnaire.QuestionnaireTitle;
            command.Parameters.AddRange(parms);

            int questionnaireID = Convert.ToInt32(command.ExecuteScalar());
            identity = questionnaireID;

            //2写入题目
            for(int i = 0; i < questionnaire.Questions.Count; i++)
            {
                command.CommandText = "Question_ADD";
                command.Parameters.Clear();
                parms = new SqlParameter[] { 
                new SqlParameter("@ID",questionnaire.ID),
                new SqlParameter("@QuestionTitle",questionnaire.Questions[i].QuestionTitle),
                new SqlParameter("@Sequence",questionnaire.Questions[i].Sequence),
                new SqlParameter("@QuestionTypeID",questionnaire.Questions[i].QuestionTypeID),
                new SqlParameter("@QuestionnaireID",questionnaireID)};
                command.Parameters.AddRange(parms);

                int questionID = Convert.ToInt32(command.ExecuteScalar());
                identity = questionID;

                if((questionnaire.Questions[i].QuestionType == QuestionType.singlechoice) || (questionnaire.Questions[i].QuestionType == QuestionType.multiplechoice))
                {
                    for(int j = 0; j < questionnaire.Questions[i].ChoiceItems.Count; j++)
                    {
                        command.CommandText = "ChoiceItem_ADD";
                        command.Parameters.Clear();
                        parms = new SqlParameter[]{
                        new SqlParameter("@ID",questionnaire.Questions[i].ChoiceItems[j].ID),
                        new SqlParameter("@ItemContent",questionnaire.Questions[i].ChoiceItems[j].ItemContent),
                        new SqlParameter("@Options",questionnaire.Questions[i].ChoiceItems[j].Option),
                        new SqlParameter("@QuestionID",questionID)};
                        command.Parameters.AddRange(parms);

                        identity = Convert.ToInt32(command.ExecuteScalar());
                    }

                }

            }

            transaction.Commit();
        }
        catch(Exception ex)
        {
            transaction.Rollback();
            throw;
        }
        finally
        {
            connection.Close();
            transaction.Dispose();
            connection.Dispose();
        }

        return identity;
    }

3、SQl server中的事务例子

begin transaction
save transaction A

insert into demo values('BB','B term')
rollback TRANSACTION A

create table demo2(name varchar(10),age int)
insert into demo2(name,age) values('lis',1)
rollback transaction

  insert into demo values('BB','B term')

 commit TRANSACTION A

 commit TRANSACTION  

4、注意

1。事务必须在连接打开后BeginTransaction();

2.事务添加到SqlCommand（sqlCommand.Transaction = sqlTransaction; ）

3、其他数据库对应做相应调整

4、可以用微软提供的一个dll,很方便.

原文转载 ：https://www.cnblogs.com/tylerdonet/archive/2013/04/06/3002920.html