DLINQ: Submitting Your Changes

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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 FilesLinq PreviewBinSqlMetal"
   /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.

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.