One of the more significant areas of improvement is in transaction processing. It's still early in beta so nothing is written in stone, but by and large things got a LOT easier. In the original versions of ADO.NET, you could implement transactions a few different ways. If your implementation context was a single database, you could instantiate an instance of one of the IDBTransaction objects, attach it to your connection, process what you wanted, and either commit or rollback depending on the results. By virtue of the fact that this was done client side, many people found that it wasn't all that they were hoping. A similar method would entail rolling your transaction processing into a stored procedure and simply invoking the procedure. On the whole I think this produced some more reliable results, but it had some problems too - namely that it was highly coupled with the specific database implementation you were using. So if you needed to move a file for instance, send a success message to a MSMQ Message Queue, and then update a SQL Server database, you were going to have to do a fair amount of work. This process has been simplified so much it's hard to believe it actually works. Anyway, I'll dive into an example in a second, but let me make sure that the distinction I'm about to draw is clear: Now, just as before, you have two choices with regard to transactions, Local and Distributed. Distributed transactions span multiple items whereas local transactions typically span just one. Either way you can take advantage of the TransactionScope object to simplify your life. Simple Transaction Under ADO.NET 2.0:
bool IsConsistent = false;
using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())
{
SqlConnection cn = newSqlConnection(CONNECTION_STRING );
string sql = "DELETE Categories";
SqlCommand cmd = newSqlCommand(sql,
cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
//Based on this property the
transaction will commit if
//successful. If it fails however, this property will
//not be set and the transaction will not
commit.
ts.Consistent = IsConsistent;
}
Basically, I created a query which whacked and entire table, wrapped it in a transaction and ensured that it wouldn't commit. In doing so, the table remains fully in tact just as it was before calling ExcecutNonQuery. Now, what's so different about this? Well, notice that the connection itself is confined within the scope so it automatically participates in the transaction. All that is required to commit or rollback the transaction is specifying True or False for consistent. A more realistic example can be illustrated by making a few minor changes:
A Slightly Improved Implementation:
bool IsConsistent = false;
using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())
{
SqlConnection cn = newSqlConnection(CONNECTION_STRING
);
string sql = "DELETE Categories";
SqlCommand cmd = newSqlCommand(sql,
cn);
cn.Open();
try
{
cmd.ExecuteNonQuery();
IsConsistent = true;
}
catch (SqlException ex)
{
//You can specify additional
error handling here
}
cn.Close();
//Again, since this was set to false originally it will
only
//commit if it worked.
ts.Consistent = IsConsitent;
}
This example is more in line with the earlier version of ADO.NET's transaction processing, namely, if everything works then commit, else rollback. This is hardly climactic in any practical sense because even though it's a lot more concise than previous versions, you're not really talking about any dramatic reduction in complexity of code. To see the elegance and power of this object you really need to examine a distributed scenario. Say that you have some really complex situation where you have a table in a Yukon database that you want to clear, and then you have a corresponding table in a separate database that needs to be cleared as well. Furthermore, assume that this is an all or nothing deal and there has to be complete success or complete failure.
bool IsConsistent = false;
using (TransactionScope ts = newTransactionScope())
{
using (SqlConnection cn = newSqlConnection(YUKON_CONNECTION_STRING))
{
string sql = "DELETE Products";
SqlCommand cmd = newSqlCommand(sql,
cn);
cn.Open();
try
{
cmd.ExecuteNonQuery();
using(SqlConnection cnn = newSqlConnection(CONNECTION_STRING))
{
string sql_2 = "DELETE Categories";
SqlCommand cmd2 = newSqlCommand(sql_2,
cnn);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
IsConsistent = true;
}
catch (SqlException ex)
{
//You can specify additional
error handling here
}
cn.Close();
}
ts.Consistent = IsConsistent;
}
Now, what I'm about to discuss is pretty amazing, and I can't in clear conscience take credit for it. Angel Saenz-Badillos was the first one to tip me off to how all of this works and worked with me through a few examples. It's laughable at the moment, but the first time I heard of this, my initial response was something like "Ok, that'll save me 3 lines of code - great" I couldn't believe that it could possibly live up to the hype, and it took working with it a few times before my little brain could process it.
So here's the deal stated simply. Wrap everything in a TransactionScope object, and it takes care of everything else for you. What does that mean? Well, it will determine if you need a local or a distributed transaction, and it will react accordingly. It will enlist where necessary and process locally otherwise. Notice that the first connection string points to a Yukon (SQL Server 2005) database. As such, you can take advantage of "Delegation". This is a fancy way of saying "We don't need no stinking distributed transaction, we're using Yukon" and thereafter not using it unless it becomes necessary. Now, if you cut out the inner statements where you fire the query pointing to ANOTHER database, everything would be done under the purview of a local transaction. However, as soon as we try to hit another database, we're back in distributed transaction mode. Now, the natural assumption is that they are run under two different contexts, right? After all, you need to promote to DT mode once you try to hit the second database, but prior to that you were running locally. Actually, the answer is NO, you don't need to do squat. That's what's so amazing about it. As soon as the code gets to a point where it won't be running locally, everything is promoted accordingly. And you don't just have support for SQL Server here - Oracle and MSMQ are both currently supported, and there's a REALLY strong probability that File System support will be included in the final release.
So, does the same principle apply here if you were connecting to Oracle or MSMQ instead of SQL Server 2000? Yes, and for all intents and purposes the transactional component here would behave identically. If you've used COM+ before, then you no doubt realize how much easier this is. If you haven't, just put in Distributed Transaction COM+ into Google or read up on it, and you'll quickly see how much more simple this makes things. Even if you aren't familiar with either of those scenarios, just look to the unstable nature of client side transaction processing with ADO.NET and you'll quickly see this is pretty darned impressive.
As cool as this is, there's no doubt some folks out there won't be impressed. Well, fine. You aren't precluded from doing anything you otherwise would by employing the TransactionScope; heck you don't even have to use it. If you like writing tons of code, and you get a sense of security by doing unnecessary tasks, knock yourself out. Or even if you're not that much of a hard-core ludite, but you want to do things manually, here's how you do it:
Client Side Transaction under 1.x Framework
privatebool OldSchool()
{
bool IsConsistent = false;
ICommittableTransaction oldSchoolTrans = Transaction.Create();
using (SqlConnection cn = newSqlConnection(CONNECTION_STRING))
{
string sql = "DELETE Categories";
SqlCommand cmd = newSqlCommand(sql,
cn);
cn.Open();
cn.EnlistTransaction((ITransaction)oldSchoolTrans);
try
{
cmd.ExecuteNonQuery();
IsConsistent = true;
returntrue;
}
catch (SqlException ex)
{
//You can specify
additional error handling here
//This is where you’d rollback your
transaction
return (ex.ToString().Length < 1);
}
cn.Close();
}
}
