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.
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.
- 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.
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;
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; }
}
}
}
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.
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.
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.