As you might have guessed I am continuing my LINQ to SQL posts. I am teaching a course right now on ADO.Net 3.5 (LINQ & EF) and I know a lot of people who have learned through my blog and my style of writing.

I am going to use a step by step example to demonstrate how to select,update,insert,delete data through LINQ to SQL into the database.

If you want to have a look on how to return data from a database with LINQ to SQL and stored procedures click here. If you want to have a look on how to insert,update and delete data with LINQ to SQL and stored procedures click here.

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 results printed in the screen.

 8) But what if wanted only a few columns of the Employees table back from the database?

we need to rewrite the code above, like that (comment out everything you have so far inside the )

 NorthwindDataContext ctx = new NorthwindDataContext();


        var emps = from myemp in ctx.Employees
                   select new
                              {
                              myemp.LastName,
                              myemp.FirstName,
                              myemp.BirthDate,
                              myemp.HireDate,
                              myemp.HomePhone,
                              myemp.City,
                              myemp.Country
                               

                              };
     


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

 9) Run your application and see only the selected columns printed out on the screen.Now I am going to show you how to update the data.

Before we do that let's change the code a bit. Create a new method called Load_Data. Inside this method place all the code above.

So the whole method looks like this

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


        var emps = from myemp in ctx.Employees
                   select new
                              {
                                  myemp.LastName,
                                  myemp.FirstName,
                                  myemp.BirthDate,
                                  myemp.HireDate,
                                  myemp.HomePhone,
                                  myemp.City,
                                  myemp.Country
                               

                              };
     


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

The Page_Load() event handling routine becomes

 protected void Page_Load(object sender, EventArgs e)
    {
        Load_Data();
    }

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

11) In the Button1_Click() event handling routine type

 NorthwindDataContext ctx = new NorthwindDataContext();

        var myEmployee = ctx.Employees.Single(myemp => myemp.EmployeeID == 4);

        myEmployee.FirstName = "Julia";

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

           Response.Write(ex.Message);
        }
        Load_Data();

    }

12) Run your application and you will see the LastName value for the particular record changing to the new value.Note that the change will not be persisted to the database until the SubmitChanges() method executes.

13) Now let's try and insert a new record in our Employees table. Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Insert".

 14) In the Button2_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);

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

            Response.Write(ex.Message);
        }

        Load_Data();

Run your application and see the new record inserted into the table.

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

15) Now let's try to delete the newly added record from our Employees table. Add a button in the Default.aspx page.Leave the default name.Change the Text property of the button control to "Delete".

16) In the Button3_Click() event handling routine type

        NorthwindDataContext ctx = new NorthwindDataContext();

        var myEmployee = ctx.Employees.Single(myemp => myemp.EmployeeID == 10);

        ctx.Employees.DeleteOnSubmit(myEmployee);
       
        try
        {
            ctx.SubmitChanges();
            Response.Write("Deleted");
        }
        catch (Exception ex)
        {

            Response.Write(ex.Message);
        }

        Load_Data();

We get an instance of the object  that I want to delete. Then we pass it as a parameter in the DeleteOnSubmit() method.

Run your application and see the newly added record deleted from the table.

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.