Constructing a Simple LINQ Database Application

• Download AddressBook source - 25 KB
• Application demo (online)

Table of Contents

The Database
The Application
Creating the LINQ Class
The Wrapper Class
The Application
SQL Queries
Demo Application

Updates

• Dec, 02 2007
  • Updated to work with the release of .NET Framework 3.5.
  • Web.config was updated to reflect the newly released assemblies.
  • default.aspx.cs line 103 uses DeleteOnSubmit instead of Remove.
  • default.aspx.cs line 153 uses InsertOnSubmit instead of Add.
  • _Addressbook.dbml has been regenerated to bring compliance with the new format.
• Oct, 01 2007
  • Added paging and sorting to the packaged application, as well as the online demo, by utilizing dynamic LINQ.

The Database

In this sample application I am using a SQL 2005 database, but you could just as easily use an XML file as a data source. We will start off by creating the database AddressBook.

I built a table called Addresses inside that database.

This table is pretty standard; the only thing you must do is make the ID field a primary key with an auto-increment flag. If you don't make the ID a primary key, your application will be READONLY.

The Application

Creating the LINQ Class

• First, we simply use the nice little generator in Visual Studio 2008 to create our LINQ to SQL class. I called the class _AddressBook.dbml.
  
• When the design surface comes up, drag the Addresses table onto the design pane. Now rename the table to Addresses. Do this by clicking the name in the top of the blue box in the design pane.
  
• Go over to the Properties window and edit the properties for _AddressBookDataContext. Make sure they are set with the proper namespace and that the connection string is pulled from web.config.
  
• Next, edit the properties of the Address data class. The default properties should be ok.
  
• Now save and close the designer.

The Wrapper Class

Create a class called AddressBook. This is basically a wrapper class to wrap around _AddressBook. This class is so simple; all it does is initialize the object and provide easy access to your table. The only gotcha(s) are to make sure the namespace is the same one you told _AddressBook to use and to make sure AddressBookConnectionString is in web.config.

using System.Configuration;

/// <summary>
/// Summary description for AddressBook
/// </summary>
namespace Clarity.Database
{
    public class AddressBook
    {
        private _AddressBookDataContext _AddressBook;

        public AddressBook()
        {
            string conn = ConfigurationManager.ConnectionStrings[
                "AddressBookConnectionString"].ToString();
            _AddressBook = new _AddressBookDataContext(conn);
        }

        public System.Data.LINQ.Table<addresses> Addresses
        {
            get { return _AddressBook.Addresses; }
        }
    }
}

The Application

I'm not going to go into how to build an application; that's not the point here. I will, however, show you how to easily access your table via LINQ. If you want the full application, it's at the end of the article. Now for the tedious step of initializing and accessing the database. Hold on; this gets a little messy.

    private AddressBook thisAddressBook = new AddressBook();

That's it! You now have complete access to your database by using the thisAddressBook object. With this object you can query, insert, update and delete items from the database table by manipulating a simple generic class.

To Load the Data into the Grid

    private void LoadGrid()
    {
        GridView1.DataSource = thisAddressBook.Addresses.OrderBy(c =>

The data output by this query is ordered by LastName. In the lambda expression (c => c.LastName), c represents your object and could just as easily have been obj, as in (obj => obj.LastName). Whatever object name you choose will work; all you have to do is be consistent throughout the expression.

To Filter Data in the Grid

    private void LoadGrid(string filter)
    {
        GridView1.DataSource = thisAddressBook.Addresses.Where(
            c =>

This loads a filtered subset of data from your table into your data source, where the LastName field must begin with what is specified by the filter variable.

Deleting a record

    protected void GridView1_RowDeleting(object sender, 
        System.Web.UI.WebControls.GridViewDeleteEventArgs e)
    {
        int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value);
        Addresses thisAddress = thisAddressBook.Addresses.First(x =>

The assignment on the fourth line (Address thisAddress ...) queries the database for the first occurrence of the specified ID and assigns it to thisAddress. Then the thisAddressBook object (our table) is told to remove the entry from the table and save the changes.

Inserting/Updating a Record

    protected void AddressUpdated(object sender, UserData.UpdateEvent e)
    {
        Addresses thisAddress;
        int id = e.Address.id;

        // If this value is 0 then add a record
        if (id != 0)
            thisAddress = thisAddressBook.Addresses.First(x =>

This function takes an altered or new Addresses object and updates or inserts it into the table.

SQL Queries

I was interested in what the queries were actually commanding the database to do. Was it loading in all the records and then filtering them afterwards? Was it tailoring the queries to only get the info that was requested? Let's just say that I was really happy with the results...

Results from LoadQuery

SELECT [t0].[id], [t0].[FirstName],
[t0].[LastName], [t0].[Address1], [t0].[City], [t0].[State], [t0].[Zip],
[t0].[Email] FROM [dbo].[Addresses] AS [t0] ORDER BY [t0].[LastName]

Ok, there's nothing out of the ordinary here.

Results from the Filtered Query

exec sp_executesql N'SELECT
[t0].[id], [t0].[FirstName], [t0].[LastName], [t0].[Address1], [t0].[City],
[t0].[State], [t0].[Zip], [t0].[Email] FROM [dbo].[Addresses] AS [t0] WHERE
[t0].[LastName] LIKE @p0 ORDER BY [t0].[LastName]',N'@p0 nvarchar(2)',@p0=N'H%'

Ahh, it's using an SP as a security measure to ward off SQL injection attacks.

Results from the Delete Query

exec sp_executesql N'DELETE FROM
[dbo].[Addresses] WHERE ([id] = @p0) AND ([FirstName] = @p1) AND ([LastName] =
@p2) AND ([Address1] = @p3) AND  ([City] = @p4) AND ([State] = @p5) AND ([Zip]
= @p6) AND ([Email] = @p7)',N'@p0 int,@p1 nvarchar(5),@p2 nvarchar(5),@p3
nvarchar(15),@p4  nvarchar(10),@p5 nvarchar(2),@p6 nvarchar(5),
@p7 nvarchar(15)',@p0=4,@p1=N'Kelly',@p2=N'Smith',@p3=N'123
Fake  Street',@p4=N'Manchester',@p5=N'NH',@p6=N'03102',@p7=N'kelly@gmail.com'

This is the same as above.

Results from the Insert Query

exec sp_executesql N'INSERT INTO
[dbo].[Addresses]([FirstName], [LastName], [Address1], [City], [State], [Zip],
[Email]) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6)

SELECT [t0].[id] FROM
[dbo].[Addresses] AS [t0] WHERE [t0].[id] = (SCOPE_IDENTITY()) ',N'@p0
varchar(7),@p1 varchar(6),@p2 varchar(15),@p3 varchar(9),@p4 varchar(2),@p5
varchar(5),@p6  varchar(18)',@p0='John',@p1='Smith',@p2='123 Fake
Street',@p3='Somewhere',@p4='CT',@p5='03102',@p6='nobody@nowhere.com'

It seems that with an insert, they re-query the data to retrieve the newly created primary key's value.

Demo Application

The demo application was built in Visual Studio 2008 and written in C#. By no means is this a finished product; it is just a quick demonstration of what you can do with LINQ.

History

• 30 September, 2007 -- Original version posted
• 8 November, 2007 -- Article content updated
• 14 December, 2007 -- Download updated; article edited and moved to the main CodePoroject.com article base
  • Updated to work with the release of .NET Framework 3.5
  • Web.config was updated to reflect the new released assemblies.
  • default.aspx.cs line 103 uses DeleteOnSubmit instead of Remove.
  • default.aspx.cs line 153 uses nsertOnSubmit instead of Add.
  • _Addressbook.dbml regenerated to bring into compliance with the new format.