Click here to Skip to main content
Click here to Skip to main content

Constructing a Simple LINQ Database Application

, 15 Dec 2007
Rate this:
Please Sign up or sign in to vote.
More about the technology, but the demo app is ASP.NET

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.

DatabaseStructure.gif

I built a table called Addresses inside that database.

TableStructure.gif

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.
    CreateLinqClass.gif
  • 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.
    DesignPane.gif
  • 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.
    DataContextProperties.gif
  • Next, edit the properties of the Address data class. The default properties should be ok.
    AdderssProperties.gif
  • 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;

/// <span class="code-SummaryComment"><summary></span>
/// Summary description for AddressBook
/// <span class="code-SummaryComment"></summary></span>
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Matthew Hazlett
Web Developer
United States United States
I started programming for fun when I was about 10 on an Franklin Ace 1000.
 
I still do it just for fun but it has gotten me a few jobs over the years. More then I can say for my Microsoft Certifications. Smile | :)
 
The way I learned was by example, now its time to give back to the next generation of coders.
 


Comments and Discussions

 
QuestionQuestion PinmemberMember 1015871916-Jul-13 12:30 
GeneralThankYou Pinmembersurbhi54217-Apr-13 1:18 
GeneralGood Article Pinmembermerlin9818-Nov-07 3:45 
QuestionHow to run it Pinmembermehrbat3-Oct-07 15:30 
AnswerRe: How to run it PinmemberMatthew Hazlett3-Oct-07 16:46 
GeneralRe: How to run it Pinmembermehrbat3-Oct-07 16:50 
GeneralRe: How to run it PinmemberMatthew Hazlett3-Oct-07 17:00 
Use the open web site option
file -> open -> web site
 

 
Matthew Hazlett
 
Sometimes I miss the simpler DOS days of Borland Turbo Pascal (but not very often).

GeneralRe: How to run it Pinmembermehrbat3-Oct-07 17:06 
GeneralThanks for the article Pinmembertonyvuolo3-Oct-07 3:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 15 Dec 2007
Article Copyright 2007 by Matthew Hazlett
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid