Nikolaos Kantzelis ASP.Net Blog

This blog will focus on ASP.NET Framework

Sponsors

About Me

Great Blogs

Track updated/inserted entities in LINQ to SQL applications

In this post I would like to discuss in further detail the issue of track changing of entities in LINQ to SQL applications. I would like to show you how the DataContext object keeps track of all the items that are updated,deleted or inserted in the underlying data store.

If you want to have a look at my other post about LINQ to SQL and transactions click here.

I am going to demonstrate this with a hands on example. 

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 Employees 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) Drag a Gridview web server control on the Default.aspx page.

6) In the Page_Load event handling routine of the Default.aspx page type

        NorthwindDataContext ctx = new NorthwindDataContext();

                   var emps = from myemp in ctx.Employees
                                     select myemp;
                        

        GridView1.DataSource = emps;
        GridView1.DataBind();


7) Run your application and see the employees data printed in your screen.

8) Now let's modify our application so we can insert data into the underlying table.

9) Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Insert".

10) In the Button1_Click() event handling routine type

 NorthwindDataContext ctx = new NorthwindDataContext();

        Employee myemp = new Employee();
        myemp.LastName = "Kantzelis";
        myemp.FirstName = "Nikos";
        myemp.BirthDate = DateTime.Now.AddDays(-10000);
        myemp.HireDate = DateTime.Now.AddDays(-2000);
        myemp.HomePhone = "55555555";
        myemp.City = "Athens";
        myemp.Country = "Greece";

        ctx.Employees.InsertOnSubmit(myemp);

        ChangeSet mychanges =  ctx.GetChangeSet();

        foreach (var change in mychanges.Inserts)
        {
            Response.Write("Type changed: " + change.GetType().ToString());
            Response.Write("<br/>");
       

        if (change is Employee)
        {
            var changedEmployee = (Employee)change;

            Response.Write("Employee LastName: " + changedEmployee.LastName);
            Response.Write("<br/>");
            Response.Write("Employee FirstName: " + changedEmployee.FirstName);
            Response.Write("<br/>");
        }
        }
       
        try
        {
            ctx.SubmitChanges();
            Response.Write("Inserted");
        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }

        Load_Data();
    }

    private void Load_Data()
    {
        NorthwindDataContext ctx = new NorthwindDataContext();

        var emps = from myemp in ctx.Employees
                   select myemp;


        GridView1.DataSource = emps;
        GridView1.DataBind();
    }

 

11) The code above is similar to what we have seen so far.

After the line ctx.Employees.InsertOnSubmit(myemp);

I am using a ChangeSet object and then in order to view the changes tracked by the DataContext we use the GetChangeSet method.

Then I am trying to find out what type is changed. In this case is Employee and then with a simple If statament I check if that particulat type was changed and then I print the information for the Employee type on the screen.Then I save the record in the table by calling the SubmitChanges() method.

Run your application and see the newly added record in the table.

12) We can do something similar when updating a record.Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Update".

13) In the Button2_Click() event handling routine type

NorthwindDataContext ctx = new NorthwindDataContext();
        var myemp = (from emp in ctx.Employees
                     where emp.EmployeeID == 16
                     select emp).Single();

        myemp.FirstName = "nick";
        myemp.LastName = "Kezerlis";

        ChangeSet mychanges = ctx.GetChangeSet();

        foreach (var change in mychanges.Updates)
        {
            Response.Write("Type changed: " + change.GetType().ToString());
            Response.Write("<br/>");


            if (change is Employee)
            {
                var changedEmployee = (Employee)change;

                Response.Write("Employee LastName: " + changedEmployee.LastName);
                Response.Write("<br/>");
                Response.Write("Employee FirstName: " + changedEmployee.FirstName);
                Response.Write("<br/>");
            }
        }

        try
        {
            ctx.SubmitChanges();
            Response.Write("Updated");
        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }

        Load_Data();

14) Run your application and see the results printed in the screen and the record updated in the database.Wherever you think it is useful add a breakpoint and have a closer look at how the code works.

We do something similar like before.

I am using a ChangeSet object and then in order to view the changes tracked by the DataContext we use the GetChangeSet method.

Then I am trying to find out what type is changed. In this case is Employee and then with a simple If statament I check if that particular type was changed and then I print the information for the Employee type on the screen.Then I save the record in the table by calling the SubmitChanges() method.In this example we look for changes that are relevant to "Update" while in the previous example we look for changes that are relevant to "Insert"

Note that the changes will not be persisted to the database until the SubmitChanges() method executes.

That is all for now. Stay tuned for many posts on EF and LINQ.

Email me if you need the source code.

Hope it helps.

Comments

Mike said:

Brilliant, thanks for this! I've been trying to work how to keep a full audit of user changes via Linq to SQL and this explains it in easy language even I can understand. ;-)

# January 21, 2013 6:41 PM