Call multiple SQL Server stored procedures in a transaction

Question

For usage in my current project I've created a class that allows me to call SQL Server async.

My code looks like this:

internal class CommandAndCallback<TCallback, TError>
{
    public SqlCommand Sql { get; set; }
    public TCallback Callback { get; set; }
    public TError Error { get; set; }
}

class MyCodes:SingletonBase<MyCodes>
{
    private static string _connString = @"Data Source=MyDB;Initial Catalog=ED;Integrated Security=True;Asynchronous Processing=true;Connection Timeout=0;Application Name=TEST";

    private MyCodes() { }

    public void SetSystem(bool production)
    {
        _connString =
            string.Format(@"Data Source=MyDB;Initial Catalog={0};Integrated Security=True;Asynchronous Processing=true;Connection Timeout=0;Application Name=TEST", production ? "ED" : "TEST_ED");
    }

    public void Add(string newCode, Action<int> callback, Action<string> error)
    {
        var conn = new SqlConnection(_connString);
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = @"ADD_CODE";
        cmd.Parameters.Add("@NEW", SqlDbType.NVarChar).Value = newCode;
        cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;

        try
        {
            cmd.Connection.Open();
        }
        catch (Exception ex)
        {
            error(ex.ToString());
            return;
        }

        var ar = new CommandAndCallback<Action<int>, Action<string>> { Callback = callback, Error = error, Sql = cmd };
        cmd.BeginExecuteReader(Add_Handler, ar, CommandBehavior.CloseConnection);
    }

    private static void Add_Handler(IAsyncResult result)
    {
        var ar = (CommandAndCallback<Action<int>, Action<string>>)result.AsyncState;
        if (result.IsCompleted)
        {
            try
            {
                ar.Sql.EndExecuteReader(result);
                ar.Callback(Convert.ToInt32(ar.Sql.Parameters["@NewId"].Value));
            }
            catch (Exception ex)
            {
                ar.Error(ex.Message);
            }
        }
        else
        {
            ar.Error("Error executing SQL");
        }
    }

public void Update(int codeId, string newCode, Action callback, Action<string> error)
    {
        var conn = new SqlConnection(_connString);
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = @"UPDATE_CODE";
        cmd.Parameters.Add("@CODE_ID", SqlDbType.Int).Value = codeId;
        cmd.Parameters.Add("@NEW", SqlDbType.NVarChar).Value = newCode;

        try
        {
            cmd.Connection.Open();
        }
        catch (Exception ex)
        {
            error(ex.ToString());
            return;
        }

        var ar = new CommandAndCallback<Action, Action<string>> { Callback = callback, Error = error, Sql = cmd };
        cmd.BeginExecuteReader(Update_Handler, ar, CommandBehavior.CloseConnection);
    }

    private static void Update_Handler(IAsyncResult result)
    {
        var ar = (CommandAndCallback<Action, Action<string>>)result.AsyncState;
        if (result.IsCompleted)
        {
            try
            {
                ar.Sql.EndExecuteReader(result);
                ar.Callback();
            }
            catch (Exception ex)
            {
                ar.Error(ex.Message);
            }
        }
        else
        {
            ar.Error("Error executing SQL");
        }
    }

}

This may look like too much of code, but it lets me call it as so:

private void Add_Click(object sender, EventArgs e)
{
   MyCodes.Instance.Add("Test",Success,Error)
}

private void Success(int newId)
{
   MessageBox.Show(newId.ToString(), "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}

private void Error(string error)
{
   MessageBox.Show(error, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Above code works just fine for me, I'm able to do every call async.

Problem that I have right now is to do multiple calls as transaction - I would like to update 2 codes and add one new.

Normally I would call update, then in success handler call second update, and in handler to second update I would call add that would return new id.

Something like:

-UPDATE CODE
 |-UPDATE CODE
   |-ADD CODE (only this one return something)

But I would like to call all of those as transaction, so if add code would break updates would rollback.

Question:

Is it possible to call multiple async queries as a transaction?

Can I call my above methods as transaction or do I must create separate method to call my procedures as one? (I would like to avoid this one because it's just copying the same code from one method to another)

I would like to add that I use .NET 3.5 so await and other nice features aren't an option.

Answer 1

Yes, it is possible. Simply call SqlConnection.BeginTransaction before your first call. Make sure you assign the returned SqlTransaction object to each SqlCommand.Transaction in the chain and call SqlTransaction.Commit() at the end.

Answer 2

public class Command
{
    public string sql { get; set; }
    public CommandType cmdType { get; set; }
    public Dictionary<string, object> parameter { get; set; } = null;
}

    private Command insertInvoice(Invoice invoice)
    {
        try
        {
            Dictionary<string, object> parameterLocal = new Dictionary<string, object>();

            parameterLocal.Add("p_customerId", invoice.customerId);
            parameterLocal.Add("p_invoiceNo", invoice.invoiceNo);
            parameterLocal.Add("p_invoiceDate", invoice.invoiceDate);
            parameterLocal.Add("p_invoiceAmount", invoice.invoiceAmount);                
            parameterLocal.Add("p_withInvoice", invoice.withInvoice);

            return (new Command { sql = "sp_insertInvoice", cmdType = CommandType.StoredProcedure, parameter = parameterLocal });
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private Command insertInvoiceModel(InvoiceModel invoiceModel)
    {
        try
        {
            Dictionary<string, object> parameterLocal = new Dictionary<string, object>();

            parameterLocal.Add("p_invoiceNo", invoiceModel.invoiceNo);
            parameterLocal.Add("p_model", invoiceModel.model);
            parameterLocal.Add("p_quantity", invoiceModel.quantity);
            parameterLocal.Add("p_unitPrice", invoiceModel.unitPrice);

            return (new Command { sql = "sp_insertInvoiceModel", cmdType = CommandType.StoredProcedure, parameter = parameterLocal });
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

 List<Command> commandList = new List<Command>();

 cmd = insertInvoice(invoicesave);

 commandList.Add(cmd);

 cmd = insertInvoiceModel(invoiceModelSave);

 commandList.Add(cmd);

        try
        {
            erplibmain.erpDac.runOleDbTransaction(commandList);
        }
        catch (Exception ex)
        {
            throw ex;
        }

    public void runOleDbTransaction(List<Command> commandList)
    {
        OleDbConnection erpConnection = new OleDbConnection(ErpDalMain.connectionstring);
        erpConnection.Open();

        OleDbCommand erpCommand = erpConnection.CreateCommand();
        OleDbTransaction erpTrans;

        // Start a local transaction
        erpTrans = erpConnection.BeginTransaction();
        // Assign transaction object for a pending local transaction
        erpCommand.Connection = erpConnection;
        erpCommand.Transaction = erpTrans;

        try
        {
            foreach (Command cmd in commandList)
            {
                erpCommand.CommandText = cmd.sql;
                erpCommand.CommandType = cmd.cmdType;

                foreach (KeyValuePair<string, object> entry in cmd.parameter)
                {
                    erpCommand.Parameters.AddWithValue(entry.Key, entry.Value);
                }

                erpCommand.ExecuteNonQuery();

                erpCommand.Parameters.Clear();
            }

            erpTrans.Commit();
        }
        catch (Exception e)
        {
            try
            {
                erpTrans.Rollback();
            }
            catch (OleDbException ex)
            {
                if (erpTrans.Connection != null)
                {
                    throw ex;
                }
            }

            throw e;
        }
        finally
        {
            erpConnection.Close();
        }
    }

Answer 3

  string cnnString =WebConfigurationManager.ConnectionStrings["MyString"].ConnectionString;
    SqlConnection cnn = new SqlConnection(cnnString);
    SqlTransaction transaction;

    cnn.Open();
    transaction = cnn.BeginTransaction();

    try
    {

        // Command Objects for the transaction
        SqlCommand cmd1 = new SqlCommand("sproc1", cnn);
        SqlCommand cmd2 = new SqlCommand("sproc2", cnn);

        cmd1.CommandType = CommandType.StoredProcedure;
        cmd2.CommandType = CommandType.StoredProcedure;

        cmd1.Parameters.Add(new SqlParameter("@Param1", SqlDbType.NVarChar, 50));
        cmd1.Parameters["@Param1"].Value = paramValue1;

        cmd1.Parameters.Add(new SqlParameter("@Param2", SqlDbType.NVarChar, 50));
        cmd1.Parameters["@Param2"].Value = paramValue2;

        cmd2.Parameters.Add(new SqlParameter("@Param3", SqlDbType.NVarChar, 50));
        cmd2.Parameters["@Param3"].Value = paramValue3;

        cmd2.Parameters.Add(new SqlParameter("@Param4", SqlDbType.NVarChar, 50));
        cmd2.Parameters["@Param4"].Value = paramValue4;

        cmd1.ExecuteNonQuery();
        cmd2.ExecuteNonQuery();

        transaction.Commit();
    }

    catch (SqlException sqlEx)
    {
        transaction.Rollback();
    }

    finally
    {
        cnn.Close();
        cnn.Dispose();
    }