Handling disconnected Crud operations using Linq to SQL

By: John Charles Olamendy Turruellas

Introduction

I’ve been searching in Internet for articles about Linq to SQL and most of the articles are written for connected environments where we have a data context created and the database operations are executed in the data context, but there are common business scenario where we gather data from a context and we move to another context to update the changes to the database such as ASP.NET Web applications and data transfer using Web services as well as desktop applications when we separate the data access code from the business logic code. In this example, I will show how to execute Crud operations using Linq to SQL in a disconnected environment where we use different data contexts to execute different operations. Our application is a classic multi-layer application where we separate the objects in layers according to the functions in the application. In order to simplify the logic, we are going to build a desktop application with only two layers: one for the presentation of the data and the other one for implementing the data access logic.

Getting started with the solution

Let’s open the Visual Studio.NET 2008 IDE and create a Windows Forms Application project.  We’re going to create a desktop application which connects to a SQL Server 2005 database system and manages the data on the dbo.Department table on a TestDB database. The schema of the dbo.Department table is show in the Listing 1.

CREATE TABLE [dbo].[Department]

(

[DeptID] [int] NOT NULL,

[DeptName] [varchar](50) NOT NULL,

[Location] [varchar](50) NOT NULL,

CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED

(

[DeptID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

Listing 1

Then add a new form for the management of the Department entities in the database and add three label controls, three textbox controls, and four button controls in order to get a GUI as shown in the Figure 1.

Figure 1

Next step is to add a new “Linq to SQL Classes” artifact to implement the data access logic through “Linq to SQL” mechanism (see Figure 2).

Figure 2

Then go to the “Server Explorer” windows and create a connection to the TestDB database and then drag and drop the Department table from the connection onto the “Linq to SQL” designer (see Figure 3).

Figure 3

Next step is to bind the text fields to the properties of the Department entity. Go to the “Data Source” windows and add a new data source (see Figure 4 and Figure 5).

Figure 4

Figure 5

Then drag and drop a BindingSource artifact from the Data tab on the Toolbox windows on to the form and set the DataSource property to the Department data source (see Figure 6).

Figure 6

Now it’s time to start writing code. We need to add a Department private attribute to the form, create an instance and assign this instance to the bindingsource artifact (see Listing 2).

private Department _objDepartment;

private void prvInitDepartmentEntity()

{

this._objDepartment = new Department();

this._bsDepartment.DataSource = this._objDepartment;

}

private void DepartmentManagementForm_Load(object sender, EventArgs e)

{

this.prvInitDepartmentEntity();

}

Listing 2

Now let’s create a Data Access Object class to manage the data access logic. This class is semantically an entity manager so we call this class DepartmentEntityManager.

The first operation to implement is to get a department by its identifier (see Listing 3). This operation uses a Linq statement to get a single Department. It’s important to set the DeferredLoadingEnable property of the DataContext instance to false in order to work with entities from a DataContext instance, then execute some business logic on the entities, and finally update the changes to the database using another DataContext instance.

public Department GetDepartmentById(int nId)

{

Department objDepartment = null;

using (HumanResourcesDataContext dcHumanResources = new HumanResourcesDataContext())

{

dcHumanResources.DeferredLoadingEnabled = false;

objDepartment = (from Department tmpDept in dcHumanResources.Departments

where tmpDept.DeptID == nId

select tmpDept).Single();

}

return objDepartment;

}

Listing 3

Next method to implement is the CreateDepartment. We use the standard way to do it (see Listing 4). See the line code, dcHumanResources.DeferredLoadingEnable=false, which is very important for disconnected environments.

public void CreateDepartment(Department deptValue)

{

using (HumanResourcesDataContext dcHumanResources = new HumanResourcesDataContext())

{

try

{

dcHumanResources.DeferredLoadingEnabled = false;

dcHumanResources.Departments.InsertOnSubmit(deptValue);

dcHumanResources.SubmitChanges();

}

catch (Exception)

{

dcHumanResources.Transaction.Rollback();

throw;

}

}

}

Listing 4

Next step is to implement the update method. Linq to SQL supports this scenario although we need to know some advanced principles and techniques of database systems because Linq to SQL uses optimistic concurrency managed by the Data Context instances in order to leave the data in a consistent state. In this case, the data integrity is at risk once two transactions work with the same records and updates the changes back to the database where lost updates are possible. Optimistic concurrency control solves this problem by validating that the changes from one transaction don’t affect the changes of another transaction. In this type of concurrency control there is a pre-commit phase, where we check that the state of entities are the same as when we gather from the database system, so we are sure that no other transaction has altered the entities. We can implement this by checking every field in the entity or by creating row version field to track the changes to a particular entity (extending the UPDATE statement with a WHERE clause). Once the verification succeeds, then all changes including the new row version value can be committed back to the database system. This kind of concurrency control is mainly used for update and deletes operations on entities.

For instance, Linq to SQL must generate the UPDATE statement (see Listing 5) to update the changes of the Department entities back to the database system.

UPDATE dbo.Department

SET DeptName=@p3, Location=@p4

WHERE (DeptID=@p0) AND (DeptName=@p1) AND (Location=@p2)

Listing 5

In the case, that we add a row version timestamp column, then we only check the columns representing the primary key and row version (see Listing 6).

UPDATE dbo.Department

SET DeptName=@p2, Location=@p3

WHERE (DeptID=@p0) AND (RowVersioTimeStamp=@p1)

Listing 6

Now let’s see how we implement this feature using Linq to SQL. We need to decorate the properties of the Entity class with the UpdateCheck property of the Column attribute to indicate which of them must participate in the optimistic concurrency checking. By default, Linq to SQL checks every fields of the entity. We can change this very easy using the Property editor. Open your mapping in the Linq to SQL designer and select a property of a business entity. Then the UpdateCheck property enables changing this setting (see Figure 7).

Figure 7

In order to track the original changes, we need to first go to the database system and see the actual state of the entity because DataContext instance does not know these original values. In the Listing 7, you can see that we achieve this by calling the GetDepartmentById method. Then a call to the Attach method on the DataContext instance is necessary to include the changed entity along with the original entity in this context. This is required for the generation of the UPDATE statement described in the Listing 5. And finally, we need to invoke the SubmitChanges method on the data context. When invoking the SubmitChanges method, we can specify a ConflictMode enum value to change the way optimistic concurrency’s conflicts are detected and resolved.

public void SaveDepartment(Department deptValue)

{

using (HumanResourcesDataContext dcHumanResources = new HumanResourcesDataContext())

{

try

{

dcHumanResources.DeferredLoadingEnabled = false;

Department objOriginal = this.GetDepartmentById(deptValue.DeptID);

dcHumanResources.Departments.Attach(deptValue, objOriginal);

dcHumanResources.SubmitChanges();

}

catch (Exception ex)

{

dcHumanResources.Transaction.Rollback();

throw;

}

}

}

Listing 7

And finally the Delete method is implemented similar to the Update method, the only difference is to include the invocation to the DeleteOnSubmit method in the data context (see Listing 8).

public void DeleteDepartment(Department deptValue)

{

using (HumanResourcesDataContext dcHumanResources = new HumanResourcesDataContext())

{

try

{

dcHumanResources.DeferredLoadingEnabled = false;

Department objOriginal = this.GetDepartmentById(deptValue.DeptID);

dcHumanResources.Departments.Attach(deptValue, objOriginal);

dcHumanResources.Departments.DeleteOnSubmit(deptValue);

dcHumanResources.SubmitChanges();

}

catch (Exception ex)

{

dcHumanResources.Transaction.Rollback();

throw;

}

}

}

Listing 8

Conclusion

In this article, I’ve shown to handle CRUD operations in a disconnected environments using Linq to SQL as the ORM tool. I’ve also discussed the optimistic concurrency control mechanism to check and resolve concurrency conflicts in this kind of environment. We can see the examples and apply to your own business situation.

Advertisements

4 thoughts on “Handling disconnected Crud operations using Linq to SQL

  1. Pingback: MVP Factor

  2. Pingback: Fernando Garcia Lorea

  3. Pingback: Handling disconnected Crud operations using Linq to SQL « DbRunas – Noticias y Recursos sobre Bases de Datos

  4. One of the Best Article (from the productivity perspective) I have read. Thanks a Million for such a thoughtful / fruitful and well explain article. Waiting for more 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s