Issue in sqltransaction in C#

See more:

SQL

C#3.5

transaction

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)
        {
            MesageBox.Show(exp.Message.ToString(), "Transaction Failed");
            sqlTransaction.Rollback();
        }
    }

AddToDatabase is one form1 and I am using sqlConnection on this. While UpdateBarcode is on form 2 and using different sqlConnection on that form.

I have multiple methods in AddToDatabase

1: GenerateBarcodeTemp("Barcode")-> Getting next avaialble barcode sequence stored
2: GetCustomerType()-> Get current customer type
3: PrintInvoice()-> Difference form printing has multiple methods
4: UpdateQty(qty, id)-> Updating quantity of the customer

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

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

All methods should use one transation to fulfill my requirements.

private void AddToDatabase()
{
    sqlCommand = new SqlCommand("Insert Into Sales(barcode_id,customer_id, pos_id,qty)values(@barcode_id,@customer_id, 1,@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();
                }
                 catch (Exception exp)
                 {
                     MessageBox.Show(exp.Message.ToString(), "Error");
                 }

My software is hanging On UpdatBarcode method on printInvoice. Any suggestion ?

What I have tried:

I want suggestion how to avoid transaction locks