发布：2014/9/19 10:27:57作者：管理员 来源：本站 浏览次数：1417

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();  
                          }  
                  }

•        

  SqlConnection con = new SqlConnection(ConnectionDb.conStr);//获取数据库连接
          con.Open();//打开连接
          SqlTransaction sqltra = con.BeginTransaction();//开始事务
          SqlCommand cmd = new SqlCommand();//实例化
          cmd.Connection = con;//获取数据连接
          cmd.Transaction = sqltra;//，在执行SQL时，
          try
          {
              string sql = "insert into OA_MEETING(meeting_id,meeting_name,meeting_title,start_time,end_time,announce_time,explain,reg_emp_id,emc_emp_id,par_emp_id,rep_emp_id,participate,MEETING_TYPE,PROJ_ID,WORK_ID)values('" + id + "',N'" + meetname + "',N'" + meettitle + "','" + starttime + "','" + endtime + "',N'" + djtime + "',N'" + meetinfo + "','" + empid + "','" + meetperson + "','" + lname + "','" + writer + "','" + ccname + "','" + hidsort.Value + "','" + proid + "','" + workid + "')";
              cmd.CommandText = sql;
              cmd.ExecuteNonQuery();
  
              //string meetid = id;
              int size = 0;
              HttpFileCollection files = HttpContext.Current.Request.Files;
              //遍历上传文件窗体所有Html控件
              foreach (string NameId in Request.Form)
              {
                  //找到控件ID名前四位为"txt_"的文件说明文本框
                  if (NameId.Substring(0, 4) == "ttx_")
                  {
                      //获取文本说明
                      if (Request.Form[NameId] != "")
                      {
                          string Explain = Request.Form[NameId];//获取txt文本说明
  
                          HttpPostedFile pstfile = files["fil_" + NameId.Substring(4)];
                          string fileName = "";
                          string fileExtension = "";
                          fileName = System.IO.Path.GetFileName(pstfile.FileName);//上传的文件全名
                          fileExtension = System.IO.Path.GetExtension(fileName);//扩展名
                          string NewName = fileName.Remove(fileName.LastIndexOf("."));//去掉后缀名的文件名
                          string sql1 = "select isnull(max(app_id),0) from OA_MEETING_APPENDIX";
                         
                          int idd = Convert.ToInt32(objConnction.getString(sql1)) + 1;
                          ua.Folder_Exists("Meeting");
                          string filepath = "..\\AtthFiles\\Meeting\\Meeting_" + idd.ToString() + fileExtension;
                          string sqlstr1 = "insert into OA_MEETING_APPENDIX(APP_ID,MEETING_ID,EXPLAIN,APP_PATH) values('" + idd + "','" + id + "',N'" + Explain + "',N'" + filepath + "')";
                          cmd.CommandText = sqlstr1;
  
                          int num = cmd.ExecuteNonQuery();
                          if (num > 0)
                          {
                              pstfile.SaveAs(Server.MapPath("..\\AtthFiles\\Meeting\\Meeting_" + idd.ToString() + fileExtension));
                              size = pstfile.ContentLength / 1024;
                              objDocument.InsertDoc(5, idd, Explain, "", "", DateTime.Now.ToString("yyyy-MM-dd"), Convert.ToInt32(HempID.Value), filepath, size, hidwork.Value, hidpro.Value);
                          }
                      }
                  }
              }
              sqltra.Commit();
          }catch(Exception ex)
          {
              sqltra.Rollback();
          }

• // <summary>
          /// 执行多条SQL语句，实现数据库事务。
          /// </summary>
          /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
          public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
          {
              using (SqlConnection conn = new SqlConnection(connectionString))
              {
                  conn.Open();
                  using (SqlTransaction trans = conn.BeginTransaction())
                  {
                      SqlCommand cmd = new SqlCommand();
                      try
                      {
                          int indentity = 0;
                          //循环
                          foreach (DictionaryEntry myDE in SQLStringList)
                          {
                              string cmdText = myDE.Key.ToString();
                              SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
                              foreach (SqlParameter q in cmdParms)
                              {
                                  if (q.Direction == ParameterDirection.InputOutput)
                                  {
                                      q.Value = indentity;
                                  }
                              }
                              PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                              int val = cmd.ExecuteNonQuery();
                              foreach (SqlParameter q in cmdParms)
                              {
                                  if (q.Direction == ParameterDirection.Output)
                                  {
                                      indentity = Convert.ToInt32(q.Value);
                                  }
                              }
                              cmd.Parameters.Clear();
                          }
                          trans.Commit();
                      }
                      catch
                      {
                          trans.Rollback();
                          throw;
                      }
                  }
              }
          }

  
  

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,很方便.