LINQ To SQL

Now that we have generated our data layer. We will work on ASP.NET web application where we will use our data layer. To keep things simple we will create a web forms to search for customers and display search results. We will also create a web form to insert new customers. Let’s start by creating our web form for customer search. For this we will use the Default.aspx page. We will place few controls on the web form. These controls will give us search parameters and a button which will do the search and display results when clicked. This is what the form will look like after placing our controls.

We will also place a GridView control on our form to display search results. We will now put in some code in our button’s click event handler to do the search and display results in GridView. Make sure that we have a reference to Data Layer project, System.Data.Linq and appropriate using statement. Here is what our button click event handler will contain.

protected void buttonSearch_Click(object sender, EventArgs e)

{

  using (NorthwindDataContext context = new NorthwindDataContext())

  {

    var customers =

      from c in context.Customers

      select c;

    gridViewCustomers.DataSource = customers;

    gridViewCustomers.DataBind();

  }

}

This code will query the customers table in northwind database and will return all customers. We will now modify it slightly to accept customer name and company name as parameters for our query. After modification our event handler looks like this.

protected void buttonSearch_Click(object sender, EventArgs e)

{

  using (NorthwindDataContext context = new NorthwindDataContext())

  {

    var customers =

      from c in context.Customers

      where (

        c.ContactName.Contains(textBoxCustomerName.Text.Trim())

        &&

        c.CompanyName.Contains(textBoxCompanyName.Text.Trim()))

      select c;

      gridViewCustomers.DataSource = customers;

      gridViewCustomers.DataBind();

  }

}

Our search results will now be filtered.

Let us now created a data entry form for customers.  We will insert a new web form in our ASP.NET project and call it CustomerEntry. To start with we will make sure that our form contains fields required to insert a customer. Our form after completion will look like this.

We expect a new row to be inserted into customers table when Save Customer button is clicked. This code achieves data insertion into customers table for us.

protected void buttonSave_Click(object sender, EventArgs e)

{

  using (NorthwindDataContext context = new NorthwindDataContext())

  {

    Customer customer = new Customer

    {

      CustomerID = textBoxCustomerID.Text,

      CompanyName = textBoxCompanyName.Text,

      ContactName = textBoxCustomerName.Text,

      ContactTitle = textBoxTitle.Text,

      Address = textBoxAddress.Text,

      City = textBoxCity.Text,

      Region = textBoxRegion.Text,

      PostalCode = textBoxPostalCode.Text,

      Country = textBoxCountry.Text,

      Phone = textBoxPhone.Text,

      Fax = textBoxFax.Text

    };

    context.Customers.InsertOnSubmit(customer);

    context.SubmitChanges();

  }

}

Similarly an existing row in database can be updated by first retrieving the data and then submitting it via DataContext.

Conclusion

In this tutorial we have not written a single SQL statement to retrieve or insert data into a database. This is the beauty of LINQ To SQL. Further our retrieval code while in C# looks a lot like a query. We can already appreciate the benefits of such a streamlined and unified approach in dealing with data.