S3 Tech Blog: Submitting Your Changes

Share |

The article mentioned DLINQ's basic support for submitting your changes, but it didn't delve into the details. Submitting changes, as it turns out, is a lot more complex than querying for data. When it comes to submitting changes, you have to face various issues, such as concurrency, transactions, stored procedure support, graph consistency, and so forth. This article focuses on the various practical issues involved with submitting your changes as update, insert, and delete queries that are at the heart of any data-driven application. It then examines the various support options in DLINQ, such as change tracking, concurrency updates, transaction basics, stored procedure support, and so on.

Set Up a Strongly Typed DataContext

The support for submitting changes is the corollary of DataAdapter.Update. In fact, I'm glad Microsoft decided to change the name from "Update" to "SubmitChanges." This causes less confusion, because "SubmitChanges" in DLINQ and "Update" on DataAdapters could both cause update, insert, and delete queries to run on the database, not just "Update" (queries).

My previous article demonstrated how to set up a strongly typed DataContext manually. That may work for simpler databases, but it would simply be too tedious for larger databases. Thus, this article walks through using a utility called SqlMetal to set up a strongly typed DataContext called "NorthWind" for the NorthWind database running on a local server. Use the following steps to do this:

Run the Visual Studio 2005 command prompt by going to Start -> Programs -> Microsoft Visual Studio 2005 -> Visual Studio Tools -> Visual Studio 2005 Command Prompt.
Go to the directory where you want to place the NorthWind.cs.

Run the following command:

"C:\Program Files\Linq Preview\Bin\SqlMetal"
/server:. /database:Northwind/pluralize /code:NorthWind.cs

Now, you have set up a strongly typed DataContext called NorthWind in a file called NorthWind.cs. You now can add this .cs file to your other projects and use it like any regular class. With the strongly typed DataContext, you can now put it to some real use.

Modifying Data and Submitting Changes Basics

With the strongly typed DataContext set up, you now can easily write a ConsoleApplication that uses the NorthWind DataContext. Assuming that you have already added references to System.Data.Dlinq, System.Data.Extensions, and System.Query, and that you have added Northwind.cs to the project, you can query for the first customer in a strongly typed manner using the following LINQ query:

Northwind db = new Northwind(connStr);

Customer alfkiCustomer =
   (from c in db.Customers
    where c.CustomerID == "ALFKI"
    select c).First<Customer>();

This would give you a strongly typed aflkiCustomer representation whose ID is "ALFKI". Now, you can modify this customer in a way that is logical to you. Rather than writing DataTable code as shown below...

customerTable[0]["City"] = "London" ;

... you can write code that looks like this:

alfkiCustomer.City = "London" ;

It's just like a DataTable, only your disconnected data cache is modified at this time. Just as the database is unaffected until you execute DataAdapter.Update, you must execute the following statement in order to persist your changes:

db.SubmitChanges() ;

After executing the above statement, your underlying database should be updated. Alternatively, if you wish to execute a command directly on the database, you simply can use the DataContext.ExecuteCommand, ExecuteQuery, or ExecuteStoredProcedure methods.

If, instead of updating the customer, you wished to delete the customer from the database, you could simply replace the alfkiCustomer.City = "London" statement with the following:

db.Customers.Remove(alfkiCustomer) ;

Northwind db = new Northwind(connStr) ;
Customer newCustomer = new Customer() ;
// Set values for various required fields.
db.Customers.Add(newCustomer) ;
db.SubmitChanges() ;

In fact, you could have a number of changes done to your disconnected business objects, and all of those are translated into TSQL queries for you as you call "SubmitChanges". This brings up three questions:

What if my update logic was in stored procedures?
What about transactional support?
What about concurrency checks?

The following sections address each of these one by one.

Stored Procedure support

The DataContext.ExecuteStoredProcedure method executes a stored procedure immediately, so it is reasonable to suggest that it doesn't fit into the SubmitChanges paradigm. In other words, what if you wanted a stored procedure to be called at SubmitChanges, instead of an auto-generated TSQL query? DLINQ provides the necessary overrides to enable your stored procedures to be invoked automatically by the change processor instead of auto-generated TSQL queries.

You can add support for those stored procedures by using UpdateMethodAttribute, InsertMethodAttribute, or DeleteMethodAttribute. You can add a partial class to the NorthWind class, and add the necessary methods that will be called when a particular entity (say, Customer) is, say, inserted. Thus, for a customer being inserted, you can add a method that looks like this:

public partial class Northwind : DataContext
{
   ...

   [InsertMethod]
   public void OnCustomerInsert(Customer cust) { ... }
   ...
}

Alternatively, you simply can use SqlMetal and use the /sprocs switch to extract stored procedures as well. However, it is logical to expect that DLINQ won't support stored procedures that use dynamic SQL or use temporary tables directly, because the code generation is based on stored procedures that return statically determined resultsets. If SqlMetal cannot accurately determine the metadata that describes the resultsets of your stored procedures, chances are it is not going to work.

Transaction support in DLINQ

By default, DataContext.SubmitChanges automatically starts a transaction for you if there is no transaction in scope. Alternatively, you may want explicit control on the transaction (for example, you have multiple SubmitChanges on different data stores, non-database entities involved in an operation, or you simply wish to tweak the isolation level during both querying for data and submitting changes). The good news is you can do this really easily by using System.Transactions, much like ADO.NET 2.0, as shown here:

using(TransactionScope ts = new TransactionScope())
{
   db.ExecuteCommand("exec sp_DoSomethingCool");
   db.SubmitChanges();
   ts.Complete();
}

The sp_DoSomethingCool stored procedure and SubmitChanges both will execute together transactionally; if one fails, the other fails.

Alternatively, you can use the equivalent of SqlTransactions (in other words, Local transactions limited to one database) by using the following code:

db.LocalTransaction = db.Connection.BeginTransaction();
{
   db.SubmitChanges();
   db.LocalTransaction.Commit();
   db.AcceptChanges();
}
catch {
   db.LocalTransaction.Abort();
   throw;
}
finally {
   db.LocalTransaction = null;
}

Admittedly, the above code is both more complex and more limiting. Thus, I would strongly recommend that you use TransactionScope or System.Transactions over local transactions whenever you can. The obvious downside of using TransactionScope over BeginTransaction is that your transaction is more likely to be promoted to an external transaction co-coordinator, such as MSDTC. The promotion, although necessary to coordinate various resource managers (RMs) that have no idea about each other, comes with an additional price of higher isolation levels, lower performance, and the possibility of being blocked by firewalls.

Luckily, you simply can use the UseLocalTransactionsOnly property to deny promotion of your transactions to MSDTC as shown here:

using(TransactionScope ts = new TransactionScope())
{
   db.UseLocalTransactionsOnly = true;
   db.SubmitChanges();
   ts.Complete();
}

Future versions of SQL Server, ADO.NET, and everything else in between probably will contain significant enhancements. So, the number of scenarios where you will have to explicitly deny transaction promotion likely will reduce with time.

Concurrency checks in DLINQ

Of course, you simply could avoid the need for concurrency checks by expanding your TransactionScope block to a form of pessimistic concurrency. That approach, however, cannot scale because it would degrade the concurrent system performance as a whole. Without turning this article into a discussion on optimistic concurrency versus pessimistic concurrency, I am simply going to suggest that optimistic concurrency is essential in most of the practical cases you will come across. (You can read a further treatise on that in Chapters 10 and 11 of my book Pro ADO.NET with VB.NET 1.1 by Apress).

One of my pet peeves with ADO.NET was that you, as an application developer, had to go out of your way in many instances to build good optimistic concurrency support. The DLINQ architecture puts it right in front of you. DataContext.SubmitChanges has an overload that accepts a ConflictMode.

In DLINQ, objects that fail to update because of optimistic concurrency conflicts cause an (OptimisticConcurrencyException) exception to be thrown. You can specify whether the exception should be thrown at the first failure or whether all updates should be attempted, with any failures being accumulated and reported in the exception, by specifying the suitable ConflictMode to SubmitChanges as shown here:

db.SubmitChanges(ConflictMode.FailOnFirstConflict);

db.SubmitChanges(ConflictMode.ContinueOnConflict);

But if there indeed has been a conflict (an accident), you need to handle that conflict (call the ambulance).

Suppose you have original data and modified data as in Table 1.

AnimalName
AnimalWeight
AnimalType

Original Data
Pinky
10
cat

User1
Stinky
10 (unchanged)
skunk

User2
Pinky (unchanged)
20
Dog

Table 1. Original Data and Modified Data

If User2 has already saved his data, and then User1 comes by to submit his changes, a conflict will occur.

At this point, you have three options that are specified by the "RefreshMode" enumeration, the idea being that right before the actual update query is executed, the data on the client is "refreshed" with the data in the database at the time of the update. The data is examined and a decision is made. The decision could be one of the following:

Keep changes
Keep current values
Overwrite current values

KeepChanges

You can "keep changes" in scenarios where a conflict has occurred by overwriting the database values with your changed values. Thus, the data in Table 2 would be persisted in the database.

AnimalName
AnimalWeight
AnimalType

Final Data
Stinky
20
skunk

Table 2. KeepChanges Scenario Data

This 20-pound skunk looks like this in code:

try {
   context.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (OptimisticConcurrencyException e) {
   e.Resolve(RefreshMode.KeepChanges);
}
//submit succeeds on second try
context.SubmitChanges(ConflictMode.FailOnFirstConflict);

KeepCurrentValues

In this scenario, User1 would choose to "keep his current values" and overwrite whatever is in the database. But, what are the current values User1 has? The current values would be Stinky, the 10-pound skunk. Yes, the skunk is still 10 pounds in weight, because that is what the original data queried by User1 was and User1 intends to keep his current values. This looks like the following in code:

try {
   context.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (OptimisticConcurrencyException e) {
   foreach (OptimisticConcurrencyConflict c in e.Conflicts) {
      cc.Resolve(RefreshMode.KeepCurrentValues);
   }
}

This would result in a database record like Table 3.

AnimalName
AnimalWeight
AnimalType

Final Data
Stinky
10
Skunk

Table 3. KeepCurrentValues Scenario Data

OverwriteCurrentValues

OverwriteCurrentValues is the converse of KeepCurrentValues. Here, User1 overwrites his own values with the database values in case a conflict has occurred. Thus, after conflict resolution, the database may look like Table 4.

AnimalName
AnimalWeight
AnimalType

Final Data
Pinky
20
Dog

Table 4. OverwriteCurrentValues Scenario Data

This would look like the following in code:

try {
   context.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (OptimisticConcurrencyException e) {
   foreach (OptimisticConcurrencyConflict c in e.Conflicts) {
      cc.Resolve(RefreshMode.OverwriteCurrentValues);
   }
}

Frequently, you also may wish to override the inbuilt conflict detection and resolution logic and take things into your own hands. In those scenarios, you simply can access the conflicting entity using OptimisticConcurrencyConflict.Object. You also can access the individual members that are conflicting by using the OptimisticConcurrencyConflict.GetMemberConflicts method, which returns you an IEnumerable of OptimisticConcurrencyMemberConflict. Each OptimisticConcurrencyMemberConflict has three properties: CurrentValue, OriginalValue, and DatabaseValue. Given all this information, you now cantake a custom informed decision (per your custom logic) and persist the change that you want to persist.