如何在进行事务时使用存储过程?

Question

我现在只有c#事物执行多条SQL语句``不知道怎么样在C#事物中执行带参数存储过程``````SOS`代码该怎么样写呀

Answer 1

protected void btnExecuteProcParm_Click(object sender, EventArgs e)
{
SqlParameter myparm = new SqlParameter();
myparm.ParameterName = "";
myparm.SqlDbType = SqlDbType.VarChar; 
myparm.Size = 100;
myparm.Value = "ExecuteNonQuery";string sqlexec = "";
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
{conn.Open();SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, sqlexec, myparm);
}
}

Answer 2

ALTER PROCEDURE [dbo].存储过程名
@Message nvarchar(max) output
AS
BEGIN BEGIN TRAN
BEGIN TRY
--这里面写SQL语句 --提交事务
COMMIT TRAN
SET @Message='交易完成'
END TRY
BEGIN CATCH
--回滚事务
ROLLBACK TRAN
SET @Message='交易失败'
END CATCH
END

Answer 3

2楼的正解 多参数只要多个SqlParameter 就可以了

Answer 4

SqlConnection connection = conn.GetConn();        SqlTransaction transinsert = connection.BeginTransaction("trans2");
        SqlCommand cmd = new SqlCommand();        cmd.Connection = connection;
        cmd.Transaction = transinsert;             try
        {             cmd.CommandText = 存储过程名称;            System.Data.SqlClient.SqlParameter[] para = new SqlParameter[4];
            para[0] = new SqlParameter("@title", SqlDbType.VarChar);
            para[0].Value = DocTitle.Text;            para[1] = new SqlParameter("@qbid", SqlDbType.VarChar);
            para[1].Value = bianhao;//编号              para[2] = new SqlParameter("@docname", SqlDbType.VarChar);
            para[2].Value =DocName;            para[3] = new SqlParameter("@qbxz", SqlDbType.VarChar);
            para[3].Value = ddlxingzhi.SelectedItem.Text.ToString();
            for (int j = 0; j < para.Length;j++ )
            {
                cmd.Parameters.Add(para[j]);
            }
            cmd.ExecuteNonQuery();

Answer 5

给你一个用sqlhelper的例子try
            {
                string sqlStr = @"SP_RUN_STOPTIME_TOTAL";
                SqlParameter[] _param ={
       new SqlParameter("@LINE_NAME",SqlDbType.VarChar),
       new SqlParameter("@PlanCode",SqlDbType.VarChar) ,
       new SqlParameter("@Prescription",SqlDbType.VarChar),
                   new SqlParameter("@CLASS",SqlDbType.VarChar),
       new SqlParameter("@START_TIME",SqlDbType.DateTime) ,
       new SqlParameter("@END_TIME",SqlDbType.DateTime),
                   new SqlParameter("@Tag",SqlDbType.Int)
    };
                _param[0].Value = lineName;
                _param[1].Value = planCode;
                _param[2].Value = prescription;
                _param[3].Value = classTime;
                _param[4].Value = startTime;
                _param[5].Value = endTime;
                _param[6].Value = tag;
                DataTable dt = SqlHelper.ExecuteDataset(Conn.SqlConn, CommandType.StoredProcedure, sqlStr, _param).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }