SqlTransaction object
-
Thank you very much for your reply..:)
using (SqlConnection connection = DbConnection("SerConstr")) { using (SqlCommand command = connection.CreateCommand()) { SqlTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); command.Transaction = transaction; // i need to perform this in con1 command.CommandText = "Insert ..."; command.ExecuteNonQuery(); // Execute i need to perform this in con2 command.CommandText = "Update..."; command.ExecuteNonQuery(); transaction.Commit(); } catch { transaction.Rollback(); throw; } finally { connection.Close(); }how I ll do this??They laugh at me; they think I’m different. I laugh at them, ‘coz they are all same!!
CrazySankerVipin.d wrote:
// i need to perform this in con1 command.CommandText = "Insert ..."; command.ExecuteNonQuery(); // Execute i need to perform this in con2 command.CommandText = "Update..."; command.ExecuteNonQuery();
The insert and update queries refer to the same database, within the same server?
I will use Google before asking dumb questions
-
Why do you need 2 connection strings? What is the purpose..changing the database while the transaction is open?
I will use Google before asking dumb questions
Dear friend, my rquiremnt is like 1. SELECT some data from servr1.db1.Tbl1 2. INSERT that to server2.db2.Tbl1. 3. UPDATE the status filed of selected data in servr1.db1.Tbl1 If either step 2 or 3 fail I need to Roll back that transaction. any advice please..
They laugh at me; they think I’m different. I laugh at them, ‘coz they are all same!!
CrazySanker -
Thank you very much for your reply..:)
using (SqlConnection connection = DbConnection("SerConstr")) { using (SqlCommand command = connection.CreateCommand()) { SqlTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); command.Transaction = transaction; // i need to perform this in con1 command.CommandText = "Insert ..."; command.ExecuteNonQuery(); // Execute i need to perform this in con2 command.CommandText = "Update..."; command.ExecuteNonQuery(); transaction.Commit(); } catch { transaction.Rollback(); throw; } finally { connection.Close(); }how I ll do this??They laugh at me; they think I’m different. I laugh at them, ‘coz they are all same!!
CrazySankerCan you please elaborate "Insert..." & "Update..." bit more? :) Like for what purpose you need to have two CSs? Do you want to change the database? If yes, why not do it as an intermediate SQL statement between insert & update?
-
Vipin.d wrote:
// i need to perform this in con1 command.CommandText = "Insert ..."; command.ExecuteNonQuery(); // Execute i need to perform this in con2 command.CommandText = "Update..."; command.ExecuteNonQuery();
The insert and update queries refer to the same database, within the same server?
I will use Google before asking dumb questions
yes i know that , my requirement is I need to change the connection string before doing UPDATE process.is it have any solution??
They laugh at me; they think I’m different. I laugh at them, ‘coz they are all same!!
CrazySanker -
Can you please elaborate "Insert..." & "Update..." bit more? :) Like for what purpose you need to have two CSs? Do you want to change the database? If yes, why not do it as an intermediate SQL statement between insert & update?
Dear friend, my rquiremnt is like 1. SELECT some data from servr1.db1.Tbl1 2. INSERT that to server2.db2.Tbl1. 3. UPDATE the status filed of selected data in servr1.db1.Tbl1 If either step 2 or 3 fail I need to Roll back that transaction. any advice please.. Let me check intermediate SQL statement in my Google Uncle:)
They laugh at me; they think I’m different. I laugh at them, ‘coz they are all same!!
CrazySanker -
Dear friend, my rquiremnt is like 1. SELECT some data from servr1.db1.Tbl1 2. INSERT that to server2.db2.Tbl1. 3. UPDATE the status filed of selected data in servr1.db1.Tbl1 If either step 2 or 3 fail I need to Roll back that transaction. any advice please.. Let me check intermediate SQL statement in my Google Uncle:)
They laugh at me; they think I’m different. I laugh at them, ‘coz they are all same!!
CrazySankerFirst, allow me to clear this, I used "intermediate" as an english word, not as technical word. :) Theoretically, it should be like that connect with server1 select if it was a success connect with server2 insert if it was a success disconnect with server2 update if not success rollback disconnect with server1 Surely, you will need to have 2 connection objects, given this is not a live server with few hundereds transactions goin on. Hope that helps! Adeel
-
First, allow me to clear this, I used "intermediate" as an english word, not as technical word. :) Theoretically, it should be like that connect with server1 select if it was a success connect with server2 insert if it was a success disconnect with server2 update if not success rollback disconnect with server1 Surely, you will need to have 2 connection objects, given this is not a live server with few hundereds transactions goin on. Hope that helps! Adeel
-
Dear friend, my rquiremnt is like 1. SELECT some data from servr1.db1.Tbl1 2. INSERT that to server2.db2.Tbl1. 3. UPDATE the status filed of selected data in servr1.db1.Tbl1 If either step 2 or 3 fail I need to Roll back that transaction. any advice please..
They laugh at me; they think I’m different. I laugh at them, ‘coz they are all same!!
CrazySankerThe ConnectionString property can be set only when the connection is closed. (from MSDN) and when you close a query, the pending transactions are rolledback. So...my suggestion is to use at least 2 connections
using (SqlConnection conn = new SqlConnection())
{
//set the first cs
string cs1 = "connection string 1";
conn.ConnectionString = cs1;conn.Open();
SqlTransaction trans = conn.BeginTransaction();
//do the the select query
if (InsertSecondDB())
{
//update first DB
trans.Commit();
}
else
{
trans.Rollback();
}conn.Close();
}The InsertSecondDB should create a new connection and a new transaction to the second DataBase and return true if the query succeeded or false otherwise. Hope it helps.
I will use Google before asking dumb questions
-
The ConnectionString property can be set only when the connection is closed. (from MSDN) and when you close a query, the pending transactions are rolledback. So...my suggestion is to use at least 2 connections
using (SqlConnection conn = new SqlConnection())
{
//set the first cs
string cs1 = "connection string 1";
conn.ConnectionString = cs1;conn.Open();
SqlTransaction trans = conn.BeginTransaction();
//do the the select query
if (InsertSecondDB())
{
//update first DB
trans.Commit();
}
else
{
trans.Rollback();
}conn.Close();
}The InsertSecondDB should create a new connection and a new transaction to the second DataBase and return true if the query succeeded or false otherwise. Hope it helps.
I will use Google before asking dumb questions
-
hi, Is it posible to use two diferent connection string in a SqlTransaction object?? Be very grateful for any advice.
They laugh at me; they think I’m different. I laugh at them, ‘coz they are all same!!
CrazySankerDear friends, During my g0ole search i got this Distributed Transaction ^]
They laugh at me; they think I’m different. I laugh at them, ‘coz they are all same!!
CrazySanker