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.

Ifra Shaheen · Answer

my code is: //code behind SqlConnection myConnection = SqlManager.openConnection(); try { 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() + "')"; SqlCommand myCommand = new SqlCommand(query2, myConnection); SqlCommand myCommand1 = new SqlCommand(query, myConnection); myCommand.ExecuteNonQuery(); myCommand1.ExecuteNonQuery(); myConnection.Close(); }

Raja T · Answer

Hi Ifra, Try like and also please attached your code file. protected void LoginButton_Click(object sender, EventArgs e) { SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["con"].ToString()); SqlCommand cmd; cmd = new SqlCommand("select * from tbl_users where uid=" + UserName.Text + " and pwd=" + Password.Text + "",con); con.Open(); int i =Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); if (i > 0) { cmd = new SqlCommand("insert into tbl_login histery(uid,pwd)values('" + UserName.Text + "' ,'" + Password.Text + "')",con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); } }

Manas Mohapatra · Answer

Hi, As per your requirement, Please find code below: public void GetResult() { SqlCommand command1, command2; string connectionString1 = "Server=192.168.10.2;Database=DB1;Trusted_Connection=true; Asynchronous Processing = true"; SqlConnection connection1 = new SqlConnection(connectionString1); command1 = new SqlCommand("SELECT * FROM table1", connection1); string connectionString2 = "Server=192.168.10.2;Database=DB2;Trusted_Connection=true; Asynchronous Processing=true"; SqlConnection connection2 = new SqlConnection(connectionString2); command2 = new SqlCommand("SELECT * FROM table2", connection2); using (TransactionScpoe ts = new TransactionScpoe() { // Asynchronous example IAsyncResult iar1 = command1.BeginExecuteReader(); IAsyncResult iar2 = command2.BeginExecuteReader(); SqlDataReader dr1 = command1.EndExecuteReader(iar1); SqlDataReader dr2 = command2.EndExecuteReader(iar2); ts.complete(); } }

Amitesh Verma · Answer

1.there is no attached file 2.you can try using jquery for this, $.getJSON(url, function (wunderdata) { alert("Success"); }) .error(function (response) { alert("error"); })