C#事务

来源：网络整理时间：2023/2/14 0:10:27 共 3647 浏览

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

版权说明：
本网站凡注明“广州松河原创”的皆为本站原创文章，如需转载请注明出处！
本网转载皆注明出处，遵循行业规范，如发现作品内容版权或其它问题的，请与我们联系处理！
欢迎扫描右侧微信二维码与我们联系。

·上一条：C#综合揭秘——细说事务 | ·下一条：MS_SQL模糊查询like和charindex的对比