SqlTransaction in C# issue

Question

Hi Everyone,

I have a method which is storing values in database and after that it is print invoice. I'm printing a form for invoice printing. I want to make sure that if it's saving it should print. Otherwise rollback all chanages.

Following is syntex of the method.

private void DatabaseAddedUpdateBarcodeSalesToday() {
       sqlConnection.Open();
       sqlTransaction = sqlConnection.BeginTransaction();
              try{
                       AddToDatabase();
                       sqlTransaction.Commit();
                   }catch (Exception exp) {
                                 MessageBox.Show(exp.Message.ToString(), "Transaction Failed");
        sqlTransaction.Rollback();
           }
}

AddToDatabase() is saving values in sales table. After that it's calling method printInvoice();

private void AddToDatabase()
{
sqlCommand = new SqlCommand("Insert Into Sales(barcode_id,customer_id, pos_id,qty)values(@barcode_id,@customer_id, (Select id From POS Where Name=@name),@qty);

sqlCommand.Parameters.AddWithValue("@barcode_id", temp_barcode);
sqlCommand.Parameters.AddWithValue("@customer_id", 1);
sqlCommand.Parameters.AddWithValue("@qty", 1);

sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Close();  
}

Here is PrintInvoice Method

private void printInvoice()
{
       Invoice invi = new Invoice();
       invi.invi_barcode = temp_barcode;
       invi.user_id = userId;
       invi.Show();
}

On the other hand in Invoice I have a method which is updating sales table during same transaction. On that method my program is hanged and giving me timeout error. Here is method on Invoice

private void UpdateBarcode(string p, string strI){
           try{
                sqlCommandInvoice = new SqlCommand("Update Sales Set mgroupId = @mGroup Where Barcode_Id = @Barcode_Id", sqlConnectionInvoice);
                sqlCommandInvoice.Parameters.AddWithValue("@mGroup", strI);
                sqlCommandInvoice.Parameters.AddWithValue("@Barcode_Id", p);

                sqlConnectionInvoice.Open();
                sqlDataReaderInvoice = sqlCommandInvoice.ExecuteReader();
                sqlDataReaderInvoice.Close();
                sqlConnectionInvoice.Close();

                BARCODE_UPDATE = true;
            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message.ToString(), "Error");
            }

Is there any other way to achieve this without having any issue in transaction ?

Thanks in Advance,

Regards,

Answer 1

Hello,

The code was done in .NET Core 5, C#9 in VS2019. If using VS2017 then you will need to add the following NuGet package and add {} to both the connection and command objects. Not sure if I have the SQL correct but separated the insert and the read/

Data class

using System;  
using System.Data.SqlClient;  
  
namespace WorkingWithSqlServer.Classes  
{  
    public class DataOperations1  
    {  
        private static string _connectionString =  
            "Data Source=.\\sqlexpress;" +  
            "Initial Catalog=NorthWind2020;" +  
            "Integrated Security=True";  
  
        public static (bool success, Exception exception, int? id) AddToDatabase(int barCodeIdentifier, int customerIdentifier, int quantity, string name)  
        {  
            var insertStatement =  
                "INSERT INTO Sales(barcode_id,customer_id, pos_id,qty) " +  
                "VALUES (@barcode_id,@customer_id) ";  
  
  
            using var cn = new SqlConnection(_connectionString);  
            using var cmd = new SqlCommand { Connection = cn, CommandText = insertStatement };  
  
            cmd.Parameters.AddWithValue("@barcode_id", barCodeIdentifier);  
            cmd.Parameters.AddWithValue("@customer_id", customerIdentifier);  
            cmd.Parameters.AddWithValue("@qty", quantity);  
  
            var trans = cn.BeginTransaction("Ops1");  
              
            try  
            {  
                cn.Open();  
                  
                cmd.ExecuteNonQuery();  
                  
                cmd.Parameters.Clear();  
                cmd.Parameters.AddWithValue("@Name", name);  
                cmd.CommandText = "Select id From POS Where Name=@name";  
                  
                cmd.ExecuteReader();  
                  
                var identifier = Convert.ToInt32(cmd.ExecuteScalar());  
                  
                return (true, null, identifier);  
            }  
            catch (Exception e)  
            {  
                try  
                {  
                    trans.Rollback();  
                }  
                catch (Exception transEx)  
                {  
                    return (false, transEx, null);  
                }  
                return (true, e, null);  
            }  
  
        }  
    }  
}  

Form code

    private void InsertButton_Click(object sender, EventArgs e)  
    {  
        var (success, exception, identifier) = DataOperations1.AddToDatabase(temp_barcode,  1,1, "some name");  
          
        if (success)  
        {  
            PrintInvoice(identifier.Value);  
        }  
        else  
        {  
            MessageBox.Show($"Insert failed\n{exception}", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Error);  
        }  
    }  
  
    private void PrintInvoice(int identifier)  
    {  
  
    }  

Answer 2

IMHO, you need to take any reading out of the situation and place in its onw read situation separately, becuase I don't see how the reading of data can be part of Transaction that is for data persistence.

Answer 3

It seems that you get multiple connections which are blocking each other.

One way to address this is to use the TransactionScope class, but it may be easier to simple pass the SqlConnection object as a parameter.

There are a few more problems in your code. You use ExecuteReader for the update operations. That's plain wrong. For operations that do not read any data, you should use ExecuteNonQuery.

Another is AddWithValue. It looks very simple from a client-side perspective, but your DBA will beat you up if he catches you using it. Read Dan Guzman's blog post AddWithValue is evil to learn why.

Answer 4

Thanks for your response.

1: AddToDatabase is one form1 and I am using sqlConnection on this. While UpdateBarcode is on form 2 and using different sqlConnection on that form.
2: I have multiple methods in AddToDatabase

GenerateBarcodeTemp("Barcode") ---> Getting value from next avaialble barcode sequence stored in table
GetCustomerType() ---> Get current customer type
PrintInvoice() ---> Difference form printing has multiple method in that as well.
UpdateQty(qty, id) ---> Updating quantity of the customer depending on their id and type.

On Invoice form there are multiple methods as well...

VATChecker() ---> Check the value of VAT
UpdateBarcode(string p, string strI) ---> Updating Sales using Barcod

All methods should use one trasaction.