SqlTransaction in C# issue

Khurram 46 Reputation points

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() {
       sqlTransaction = sqlConnection.BeginTransaction();
                   }catch (Exception exp) {
                                 MessageBox.Show(exp.Message.ToString(), "Transaction Failed");

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();

Here is PrintInvoice Method

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

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){
                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);

                sqlDataReaderInvoice = sqlCommandInvoice.ExecuteReader();

                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,


An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
8,237 questions
0 comments No comments
{count} votes

Accepted answer
  1. Karen Payne MVP 31,076 Reputation points


    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");  
                    cmd.Parameters.AddWithValue("@Name", name);  
                    cmd.CommandText = "Select id From POS Where Name=@name";  
                    var identifier = Convert.ToInt32(cmd.ExecuteScalar());  
                    return (true, null, identifier);  
                catch (Exception e)  
                    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)  
                MessageBox.Show($"Insert failed\n{exception}", "Alert", MessageBoxButtons.OK, MessageBoxIcon.Error);  
        private void PrintInvoice(int identifier)  
    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Duane Arnold 3,216 Reputation points

    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.

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 78,581 Reputation points MVP

    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.

    1 person found this answer helpful.

  3. Khurram 46 Reputation points

    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.

    0 comments No comments