Multiple Queries Execution at the same time in C#

Question

Hi I want to execute two queries at the same time if one fails the other must discard. I tried this in a simple way i want if there is any efficient way to do it. code is in attached file. Thanks

Upendra Pratap Shahi · Answer

Hello,   Go throgh below link-     http://www.codeproject.com/Articles/4451/SQL-Server-Transactions-and-Error-Handling   http://blog.sqlauthority.com/2010/06/02/sql-server-stored-procedure-and-transactions/

Raja T · Answer

Hi Ifra, Please checkout below url for about  TransactionScope   http://www.codeproject.com/Articles/522039/A-Beginners-Tutorial-for-Understanding-Transaction     and also try like this     string query; string query2; query = " UPDATE Ticket SET TStatus_ID='2' ,AssignedTo='" + assignee.SelectedValue + "',AssignedBy='" + Session["UserId"] + "' WHERE Ticket_ID='" + value + "'"; query2 = "INSERT INTO Ticket_Thread(User_ID,Comments,Ticket_ID,Date) Values('" + Session["ID"] + "','" + assigntext.Text + "','" + value + "','" + DateTime.Now.ToString() + "')"; myConnection.Open(); SqlCommand myCommand = new SqlCommand(query2, myConnection); myCommand.ExecuteNonQuery(); myConnection.Close(); myConnection.Open(); SqlCommand myCommand1 = new SqlCommand(query, myConnection); myCommand1.ExecuteNonQuery(); myConnection.Close(); and also try to jignesh commented also like string query; query = " UPDATE Ticket SET TStatus_ID='2' ,AssignedTo='" + assignee.SelectedValue + "',AssignedBy='" + Session["UserId"] + "' WHERE Ticket_ID='" + value + "';INSERT INTO Ticket_Thread(User_ID,Comments,Ticket_ID,Date) Values('" + Session["ID"] + "','" + assigntext.Text + "','" + value + "','" + DateTime.Now.ToString() + "')"; myConnection.Open(); SqlCommand myCommand1 = new SqlCommand(query, myConnection); myCommand1.ExecuteNonQuery(); myConnection.Close();     If you facing any error in above code please show me here.

Ifra Shaheen · Answer

please guide how to to use transaction scope for these queries.     Thanks

Khan Abrar Ahmed · Answer

Hi, if you are using the database insert then use the transaction. Transaction is failed then it will rollback the record, or   if you are using some other logic or method so use try catch block if any exception occurs in the 1st method it will go to the catch block and 2nd method will not execute.

Narasimha Reddy Chennupalli · Answer

Hi,   Use transaction scope.

Upendra Pratap Shahi · Answer

Use transaction and share here if you faced any problem.

Ifra Shaheen · Answer

hi Jignesh, I want to try transaction scope so that i can roll back if one query fails to execute.. Help me to try this for these two queries.     Thanks In advance

Upendra Pratap Shahi · Answer

use transaction scope for this.

Raja T · Answer

Hi Ifra, try like this    string query; string query2; query = " UPDATE Ticket SET TStatus_ID='2' ,AssignedTo='" + assignee.SelectedValue + "',AssignedBy='" + Session["UserId"] + "' WHERE Ticket_ID='" + value + "'";   query2 = "INSERT INTO Ticket_Thread(User_ID,Comments,Ticket_ID,Date) Values('" + Session["ID"] + "','" + assigntext.Text + "','" + value + "','" + DateTime.Now.ToString() + "')";   myConnection.Open(); SqlCommand myCommand = new SqlCommand(query2, myConnection);   myCommand.ExecuteNonQuery();   myConnection.Close();   myConnection.Open(); SqlCommand myCommand1 = new SqlCommand(query, myConnection); myCommand1.ExecuteNonQuery();   myConnection.Close();   and also try to  jignesh commented also like      string query;   query = " UPDATE Ticket SET TStatus_ID='2' ,AssignedTo='" + assignee.SelectedValue + "',AssignedBy='" + Session["UserId"] + "' WHERE Ticket_ID='" + value + "';INSERT INTO Ticket_Thread(User_ID,Comments,Ticket_ID,Date) Values('" + Session["ID"] + "','" + assigntext.Text + "','" + value + "','" + DateTime.Now.ToString() + "')";   myConnection.Open(); SqlCommand myCommand1 = new SqlCommand(query, myConnection); myCommand1.ExecuteNonQuery(); myConnection.Close();

Jignesh Trivedi · Answer

Hi,

you have two choice, either Pass both the query in single command with comma (;) separated or use transaction scope.

like

query = " UPDATE Ticket SET TStatus_ID='2' ,AssignedTo='" + assignee.SelectedValue + "',AssignedBy='" + Session["UserId"] + "' WHERE Ticket_ID='" + value + "' ;" + "INSERT INTO Ticket_Thread(User_ID,Comments,Ticket_ID,Date) Values('" + Session["ID"] + "','" + assigntext.Text + "','" + value + "','" + DateTime.Now.ToString() + "')";

pass this query to command

hope this will help you.