Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

Using Custom Validation with LINQ to SQL in an ASP.Net application

A friend of mine is working in an ASP.Net application and using SQL Server as the backend. He also uses LINQ to SQL as his data access layer technology.

I know that Entity framework is Microsoft's main data access technology. All the money and resources are available for the evolution of Entity Framework.

If you want to read some interesting links regarding LINQ to SQL roadmap and future have a look at the following links.

http://blogs.msdn.com/b/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx

http://blogs.msdn.com/b/adonet/archive/2008/10/31/clarifying-the-message-on-l2s-futures.aspx

LINQ to SQL is not dead though. It is used widely by many developers around the globe and it is perfect for RAD development.

There are updates regarding LINQ to SQL in .Net 4.0. The best blog post that describes all those changes can be found here.

My friend wants to add some custom logic in his LINQ to SQL project. I will try to demonstrate how I helped him to do so with a hands on example. I will not use his actual database or data.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose c# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

3) From the Server Explorer / Data Explorer window drag the Orders table and drop it on the Northwind.dbml.

4) Have a look at the generated code from the Linq engine in Northwind.designer.cs

5) We need to apply a business rule in our application. The first one and the only one I am going to demonstrate is this one

We cannot have the ShippedDate field to get a value that is prior to the value that the OrderDate field has. In plain english, it is impossible ( and we should not allow it to happen in our application ) to have an order shipped before it is ordered.In this example we will not insert a new record. Instead we will try to update an existing record.

6) Let's see how we can update a record from the Orders table.We will use an existing record with the OrderID =12  and we will update only the fields ShippedDate and OrderDate with incompatible values.

7) Add a label control in the Default.aspx page. In the Page_Load event handling routine add the following lines

       NorthwindDataContext ctx = new NorthwindDataContext();

        var myorderdate = (from myorder in ctx.Orders
                       where myorder.OrderID == 12
                       select myorder).Single();


        myorderdate.OrderDate = DateTime.Parse("10/10/2007");
        myorderdate.ShippedDate = DateTime.Parse("10/09/2006");

        try
        {
            ctx.SubmitChanges();
            Label1.Text = myorderdate.OrderID.ToString() + "-" + myorderdate.OrderDate.ToString() + "-" +
                          myorderdate.ShippedDate.ToString();
        }
        catch (Exception ex)
        {
          Response.Write(ex.Message);
        }

8) I am not doing something fancy here.I have written a very simple LINQ query that holds the value of the row with OrderID=12  in the myorderdate variable. Then I update the ShippedDate and OrderDate with the wrong values. Then I do save the values in the database.Run your application and you will see that you will not have a problem saving these values. That is a violation of the business rule. Now, let's see how to fix that.

9) Add a new class file to your site. Name it Order.cs. This is going to be a public partial class.I am going to write some code for the OnValidate partial method

 

public partial class Order
{

    partial void OnValidate(System.Data.Linq.ChangeAction action)
    {
        throw new NotImplementedException();
    }

}

10) Now we will make our checks inside this partial method.

partial void OnValidate(System.Data.Linq.ChangeAction action)
    {
        if (action == System.Data.Linq.ChangeAction.Update)
        if (OrderDate > ShippedDate)
        {
            throw new Exception("Order date cannot be greater than the shipped date");

        }
    }

11) The code above is easy to follow. I just check for the Update action then I make a simple check and finally I throw a simple message to the user.

12) Run your application ( make sure you have the wrong values in those two fields ) and notice that we get the exception information we have defined in the partial method OnValidate.

Partial classes are a fantastic concept and I use them a lot in my Linq to SQL applications.

You can take this example and extend it as you like.

Hope its helps. Email me, if you want the source code.

 

Comments

Crystal said:

Wow! I could not even guess about it)) Not bad.

# March 26, 2012 6:34 PM