c# - 将 SqlCommand 作为参数传递给函数

Question

我有一个名为 DbVms 的类，如下所示:

public class DbVms
{       
    private bool insertApprovals(List<Approval> approvals, string user, SqlCommand command, out string resultDesc)
    {
        resultDesc = "";
        bool result = false;

        command.Parameters.Clear();
        command.Parameters.Add("@estNo", SqlDbType.NVarChar);
        command.Parameters.Add("@custID", SqlDbType.Int);
        command.Parameters.Add("@brandName", SqlDbType.NVarChar);
        command.Parameters.Add("@createdBy", SqlDbType.NVarChar);

        try
        {
            // Now add the approvals back based on whats passed in.
            foreach (Approval approval in approvals) // loop through approvals and do insert.
            {
                command.CommandText =
                "INSERT INTO [Approvals] (vendorEstNo, customerID, brand, createdBy) VALUES (@estNo, @custID, @brandName, @createdBy)";

                command.Parameters["@estNo"].Value = approval.vendorEstNo;
                command.Parameters["@custID"].Value = Convert.ToInt32(approval.customerID);
                command.Parameters["@brandName"].Value = approval.brandName;
                command.Parameters["@createdBy"].Value = user;

                command.ExecuteNonQuery();
            }

            result = true;
            resultDesc = "All records are written to database.";
        }
        catch (Exception ex)
        {
            resultDesc = "insertApprovals - Threw an exception of type: " + ex.GetType();
        }

        return result;
    }

    private bool insertQualifications(List<Qualification> qualifications, string user,SqlCommand command, out string resultDesc)
    {
        resultDesc = "";
        bool result = false;

        command.Parameters.Clear();
        command.Parameters.Add("@estNo", SqlDbType.NVarChar);
        command.Parameters.Add("@qualityID", SqlDbType.Int);
        command.Parameters.Add("@brandName", SqlDbType.NVarChar);
        command.Parameters.Add("@createdBy", SqlDbType.NVarChar);

        try
        {           
            // Now add the qualifications back based on whats passed in.
            foreach (Qualification qualification in qualifications) // loop through qualifications and do insert.
            {
                command.CommandText =
                "INSERT INTO [Qualifications] (vendorEstNo, qualityID, brand, createdBy) VALUES (@estNo, @qualityID, @brandName, @createdBy)";

                command.Parameters["@estNo"].Value = qualification.vendorEstNo;
                command.Parameters["@qualityID"].Value = Convert.ToInt32(qualification.qualityID);
                command.Parameters["@brandName"].Value = qualification.brandName;
                command.Parameters["@createdBy"].Value = user;

                command.ExecuteNonQuery();
            }

            result = true;
            resultDesc = "All records are written to database.";
        }
        catch (Exception ex)
        {
            resultDesc = "insertQualifications - Threw an exception of type: " + ex.GetType();
        }      

        return result;
    }

    public bool updateVendorData(string estNo, List<Approval> approvals, List<Qualification> qualifications, string user, out string resultDesc)
    {
        bool result = false;
        resultDesc = "";

        string connectionString = WebConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
        using(SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            SqlCommand cmd = connection.CreateCommand();
            SqlTransaction transaction = connection.BeginTransaction("transcation1");
            cmd.Connection = connection;
            cmd.Transaction = transaction;

            result = insertApprovals(approvals, user, cmd, out resultDesc); 

            if(result) // if approvals got inserted successfully insert qualifications
                result = insertQualifications(qualifications, user, cmd, out resultDesc);

            if (result) // if both approvals and qualifications got inserted successfully - commit transcation
            {
                transaction.Commit();
            }
            else // rollback otherwise.
            {
                try
                {
                    transaction.Rollback();
                    resultDesc += " -- Successfully rolled back!";
                }
                catch (Exception ex2)
                {
                    // This catch block will handle any errors that may have occurred
                    // on the server that would cause the rollback to fail, such as
                    // a closed connection.
                    resultDesc = "Rollback Exception Type: {0}" + ex2.GetType();
                }
            }
        }
        return result;
    }
}

将 SqlCommand 作为函数参数传递有任何问题吗？我需要将其设为 ref/out 变量吗？

我的目标是在两个单独的函数中执行两个插入语句，如果其中任何一个失败，我希望能够回滚整个事务。

这就是为什么我将相同的 SqlCommand 变量传递给两个函数，并在其中任何一个失败时回滚事务。但是我有点困惑是否应该通过引用传递 SqlCommand 。

Answer 1

My goal here is to do the two insert statements in two separate functions and if any of them fails I want to be able to roll back the whole transaction. Which is why I am passing the same SqlCommand variable to both functions and rolling back the Transaction if any of them fails.

不需要使用相同的命令。您所需要的只是相同的 DbConnection，并将相同的 DbTransaction 分配给每个命令。实际命令可能应该是方法的本地命令 - 它们不需要转义该上下文。