DLINQ: Submitting Your Changes

DLINQ gives you the ability to work with relational data as objects without giving up the ability to query. My previous article described the basics of DLINQ, showing how you can create a strongly typed DataContext and use that to query your data as objects.

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:

  1. Run the Visual Studio 2005 command prompt by going to Start -> Programs -> Microsoft Visual Studio 2005 -> Visual Studio Tools -> Visual Studio 2005 Command Prompt.
  2. Go to the directory where you want to place the NorthWind.cs.
  3. 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) ;

Similarly, you can add a new Customer by using the following code:

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:

  1. What if my update logic was in stored procedures?
  2. What about transactional support?
  3. 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.

DLINQ: Submitting Your Changes

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.

DLINQ Ushering in Change

Every WinForms or ASP.NET application used ADO.NET to work with its underlying data store. However, the DataSet implementation in ADO.NET left a lot to be desired. So, many of us simply wrote everything ourselves, from custom business objects to custom translation engines between business objects and relational data. DLINQ, on the other hand, performs this translation for you in a way that aligns with the LINQ strategy in .NET 3.0.

Although it is a good thing that such support is in the framework now, it is also overwhelming to fathom the possibilities and expanse of change DLINQ and other enhancements in ADO.NET 3.0 bring to the table. But, it is something that you cannot afford to ignore.

About the Author

Sahil Malik has worked for a number of top-notch clients in Microsoft technologies ranging from DOS to .NET. He is the author of Pro ADO.NET 2.0 and co-author of Pro ADO.NET with VB.NET 1.1. Sahil is currently also working on a multimedia series on ADO.NET 2.0 for Keystone Learning. For his community involvement, contributions, and speaking, he has also been awarded the Microsoft MVP award.



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

  • Live Event Date: September 17, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Another day, another end-of-support deadline. You've heard enough about the hazards of not migrating to Windows Server 2008 or 2012. What you may not know is that there's plenty in it for you and your business, like increased automation and performance, time-saving technical features, and a lower total cost of ownership. Check out this upcoming eSeminar and join Rich Holmes, Pomeroy's practice director of virtualization, as he discusses the …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds