SqlTransaction in C# issue

[mkhurram92]

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.

1. 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 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,

 

[Skydiver]

I've not looked closely yet, but I've got a few questions:

1. Why are you calling ExecuteReader() instead of ExecuteNonQuery() to perform the UPDATE?
2. What SqlConnection is being used by AddToDatabase()?
3. Why are you not setting the @name parameter in AddToDatabase()?
4. Why do you even need a transaction for a single INSERT? All modern databases provide ACID operations, so that INSERT operation will be atomic.

 

[mkhurram92]

I've not looked closely yet, but I've got a few questions:

1. Why are you calling ExecuteReader() instead of ExecuteNonQuery() to perform the UPDATE?
2. What SqlConnection is being used by AddToDatabase()?
3. Why are you not setting the @name parameter in AddToDatabase()?
4. Why do you even need a transaction for a single INSERT? All modern databases provide ACID operations, so that INSERT operation will be atomic.

Thanks for your response.

1: I was not well aware of the difference of ExecuteReader and ExecuteNonQuery. Just checked on google and you are right I must use ExecuteNonQuery and I have already adjusted it. Thanks once again for that.

2: AddToDatabase is one form1 and I am using sqlConnection on this. While UpdateBarcode is on form 2 and using different sqlConnection on that form.
3: Sorry it was a typo and I am using @name in parameters.
4: I have multiple methods in AddToDatabase

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

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.

 

[jmcilhinney]

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

Then your data access code is poorly designed and you're all but stuffed because a SqlTransaction is specific to a SqlConnection so you cannot use a single transaction across multiple connections. You could use a distributed transaction but that would be a complex solution to a problem of your own making. The appropriate solution would be to redesign your data access code so that related operations take place together, over the same connection. That might be as simple as allowing an existing connection to be passed into that AddToDatabase method but I fear that there's more to fix than that.

 

[mkhurram92]

What is normal approach of your in this type of program . It's a POS with different method of sale. Actually we have over 1 Million customers. We have a specific quantity of the product for each customer during specific time duration.

 

[jmcilhinney]

If you have a calling form and a called form and there's related data access taking place, it should all be done in one form or the other, not broken over the two. It's hard to be more specific without significantly more information about the application, which might not be appropriate here.

There's also the possibility of creating a dedicated data access layer (DAL). You might then be able to create an instance of a dedicated DAL object that can be passed between forms and used in both, thus creating a single point for data access and thus the ability to use a single connection object.

 

[mkhurram92]

If you have a calling form and a called form and there's related data access taking place, it should all be done in one form or the other, not broken over the two. It's hard to be more specific without significantly more information about the application, which might not be appropriate here.

There's also the possibility of creating a dedicated data access layer (DAL). You might then be able to create an instance of a dedicated DAL object that can be passed between forms and used in both, thus creating a single point for data access and thus the ability to use a single connection object.

Thank you. Any tutorial or guide on DAL you suggest ?

 

[jmcilhinney]

Thank you. Any tutorial or guide on DAL you suggest ?

Well, this was the first result when I searched the web for ".net how to build a data access layer", so there's that. I wonder why you didn't find that when you searched.

 

[mkhurram92]

Well, this was the first result when I searched the web for ".net how to build a data access layer", so there's that. I wonder why you didn't find that when you searched.

Thanks for that. Let me explore it... Thanks