Switching connection strings and performing shared connection transactions about as seamless as possible. It also extends support to Oracle.
To Change the Active Connection String (Not Necessarily with a Transaction)
using (SharedDbConnectionScope scope = new SharedDbConnectionScope("new connectionstring ")) { //do stuff using new connection string} // returns to default provider connection string
To Perform a Transaction With a Shared Connection
using (SharedDbConnectionScope sharedConnectionScope = new SharedDbConnectionScope()) using (TransactionScope ts = new TransactionScope()) { Product p = new Product(1); p.Title = "new title"; Product p2 = new Product(2); p.Title = "another new title"; // ... p.Save(); p2.Save(); ts.Close(); }
Using System.Transactions
You can optionally wrap any code you like in a System.Transaction.TransactionScope to take advantage of the DTC. This allows you to do all kinds of operations within the scope of a given transaction. You have to have the DTC service running on your server, however. Note that the DTC is not used in every case and System.Transaction will elevate the transaction to the DTC when it's necessary. If it can run this inside of SQL, it will.
There are times, though, when the performance tradeoff is needed:
//use the product object to test the transaction scope
using (TransactionScope scope = new TransactionScope()) {
try {
Product p1 = new Product(1);
p1.QuantityPerUnit = "10 boxes x 30 bags";
p1.Save("Unit Test");
Product p2 = new Product(2);
p2.QuantityPerUnit = "24 - 42 oz bottles";
p2.Save("Unit Test");
Product p3 = new Product(3);
p3.QuantityPerUnit = "12 - 5500 ml bottles";
p3.Save("Unit Test");
//other operations...
scope.Complete();
} catch (System.Data.SqlClient.SqlException x) {
//trap/trace/log as needed
throw x;
}
}
Simple Pass-through Transactions
You can also use our QueryCommandCollection and the DataService to handle some simple pass-through transactions where you don't need to handle any return data.
To do this, you create multiple QueryCommands (by using straight SQL or pulling them from instanced objects), add them to a QueryCommandCollection, and pass that to the DataService:
//create a new command collection
QueryCommandCollection coll = new QueryCommandCollection();
//change some data
Product p1 = new Product(1);
p1.ProductName = "New Name";
Product p2 = new Product(2);
p2.ProductName = "Another new name";
//add the commands to the collection
//by pulling them from the objects
//can also use GetInsertCommand() and GetDeleteCommand()
coll.Add(p1.GetUpdateCommand(Environment.UserName));
coll.Add(p2.GetUpdateCommand(Environment.UserName));
//Hand them to the DataService to execute in the scope
//of a transaction
DataService.ExecuteTransaction(coll);
