如何使用sqltransaction c#3.5和sql-server 2005运行storeprocedure [英] how to run storeprocedure with sqltransaction c#3.5 and sql-server 2005

问题描述

你好，

我想在一个sqlTransaction的帮助下运行两个存储过程. 我已经完成了一些代码，但是运行得不好.可以解决我的错误吗?

以下是我的代码:

Hello,

I want to run two stored procedure with the help of one sqlTransaction.
I have done some code but that is not running well. Can any on solve my error?

Below is my code:

//For Form 1
private void button4_Click(object sender, EventArgs e)
{
            SqlTransaction trn = null;
            cmd = new SqlCommand();

            SqlParameter[] par = new SqlParameter[2];
            par[0] = new SqlParameter("@custno", SqlDbType.Int);
            par[1] = new SqlParameter("@custname", SqlDbType.VarChar);
            par[0].Value = Convert.ToInt32(textBox1.Text);
            par[1].Value = textBox2.Text;

            SqlParameter[] par1 = new SqlParameter[2];
            par1[0] = new SqlParameter("@partno", SqlDbType.VarChar);
            par1[1] = new SqlParameter("@partname", SqlDbType.VarChar);
            par1[0].Value = textBox3.Text;
            par1[1].Value = textBox4.Text;
  
            try
            {
                cmd = clsobj.getCommand("SP_EXAMPLE_CUST_INSERT", par);
                cn.Open();
                trn = cn.BeginTransaction();
                cmd.Connection = cn;
                cmd.Transaction = trn;
                cmd.ExecuteNonQuery();
                cn.Close();

              //cmd = new SqlCommand();
                cmd = clsobj.getCommand("SP_EXAMPLE_PART_INSERT", par1);
                cn.Open();
                cmd.Connection = cn;
                cmd.Transaction = trn;
                cmd.ExecuteNonQuery();
                
                trn.Commit();
                cn.Close();                
            }
            catch (Exception ex)
            {
                if (trn != null)
                    trn.Rollback();
            }
            finally
            {
                cn.Close();
            }
}

//From Clas side clsobj.getcommand();
public SqlCommand getCommand(string spName, SqlParameter[] param)
 {
     try
     {
         command = new SqlCommand();
         command.CommandType = CommandType.StoredProcedure;
         command.CommandText = spName;
         command.Connection = con;
         if (param != null)
         {
             if (param.Length > 0)
             {
                 int i;
                 for (i = 0; i <= param.Length - 1; i++)
                 {
                     command.Parameters.Add(param[i]);
                 }
             }
         }
         return command;
     }
     catch (Exception ex)
     {
         return null;
     }
}

注意:还有其他方法吗?我也想学习和实施.请帮助我.

Note: Is there any other way to do? I would like to learn and implement that also. Please help me.

推荐答案

在这里看看如何实现

Have a look here of how to implement SQL transactions using ADO.NET[^].

您需要两次打开和关闭连接，因此在生命中应该使用一个连接交易.否则，您必须在新连接上注册事务

you are opening and closing the connection twice, you should use one connection in the life of the transaction. other wise you have to do enlist Transaction on the new connection

cn.Close();

我认为这就是这里的问题.

祝您好运

I think that is the issue here.

good luck,

首先，您遇到什么错误?

First of all, what error are you getting?

这篇关于如何使用sqltransaction c#3.5和sql-server 2005运行storeprocedure的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！