http://www.developer.com/net/article.php/3817471/Language-Integrated-Query-LINQ-to-SQL.htm

Language Integrated Query (LINQ) to SQL

April 28, 2009

Welcome to this installment of the .NET Nuts & Boltscolumn. The prior two articles have been an introduction toLINQ and LINQ to XML. This article will focus on anothervariation of LINQ, which is LINQ to SQL. LINQ to SQL allowsyou to query and manipulate objects associated withrelational database tables and is another valuable part ofthe LINQ family.

Introducing LINQ to Relational Data

LINQ introduced the concept of a unified data accesstechnique to eliminate challenges of accessing data from adisparity of sources. LINQ has been extended to include theability to access relational data through LINQ to SQL andLINQ to Entities. LINQ to SQL offers a direct mapping tothe Microsoft SQL Server family of databases and allows youto query and manipulate objects associated with databasetables. LINQ to Entities offers a more flexible mapping torelational data stored in other databases beyond just SQLServer. You build against a conceptual schema that ismapped to an actual schema. The remainder of this articlewill concentrate on LINQ to SQL.

LINQ to SQL Object Model

LINQ to SQL allows you to query and manipulate objectsassociated with database tables. Creating an object modelinvolves creating a typed object that represents thedatabase connection along with classes that map to databaseentities. The following list of objects are involved:

DataContext - A base class that is a disposable type that gets extended to represent a strongly typed connection to the database.
Classes and Collections - Represent objects and collection of objects that map to database entities.
Methods - Are used to extend classes or to represent stored procedure calls.

There are a few different ways to create an object model.The two most straight forward approaches are to use theprovided designer within Visual Studio 2008 to auto-generatethe objects and mappings or adding attributes to existingobjects. For this example I downloaded the Northwind sample databaseand loaded it in my SQL Server so that I could retrieve datafrom it for testing. I created a new solution and added aconsole application. I then added a LINQ to SQL data classto the console application. I used the Server Explorer tosetup a new connection to the Northwind database my localSQL Server, and then dragged all of the tables from the ServerExplorer on to the design surface for the data class.Finally I right clicked on the design surface and chose"Layout Diagram" to auto arrange all of the newly addedtables and relationships. Refer to Figure 1 for an exampleof the completed design surface.

Click here for larger image
Figure 1 - LINQ to SQL Data Class Design Surface

Test Driving LINQ to SQL through Examples

Now that we've covered the background let's use a couple ofexamples of LINQ to SQL. Our examples will demonstrate theuse of the DataContext object along with an example ofquerying the Northwind sample database using expressions.

Querying Data

The following example code uses LINQ syntax to query datafrom the database and then loop through it to print it tothe console. You'll notice how the query expression syntaxis the same as what you would find whether it be LINQ to XMLor another form of LINQ. Also notice how theNorthwindDataClassesDataContext object is wrapped within ausing construct. TheNorthwindDataClassesDataContext is an object thatextends the DataContext class and represents a stronglytyped connection to our database. TheNorthwindDataClassesDataContext is a disposabletype, so wrapping it in a using block ensures it is properlydisposed after use.

  using (NorthwindDataClassesDataContext context =   new NorthwindDataClassesDataContext())  {  var results = from customers in context.Customers                     where customers.City == "London"                     orderby customers.CompanyName                     select customers;    foreach (var customer in results)  {    Console.WriteLine("Company is {0} and contact is {1}",   customer.CompanyName, customer.ContactName);  }    // Pause to see the output  Console.ReadLine();  }

Modifying Data Using the Created Object Model

The previous example focused on the retrieval of data.We'll now examine the basic data manipulation, create,update, and delete operations and how LINQ to SQL makes themsimple. The object model used to manipulate the data is themodel generated in one of the earlier sections. After eachoperation we'll use the SubmitChanges() method to save thechanges to the database. We'll execute some simple queryexpressions with the Count() method to verify themodifications were made as desired. As the example willshow, you can use instance of objects, make modifications tothem, and the changes that you make are tracked in a changeset and pushed back to the database.

  using (NorthwindDataClassesDataContext context =  new NorthwindDataClassesDataContext())  {    // Add a new record and verify it exists through Count    var customer = new Customer()    {  CompanyName = "Drama Cafe",  CustomerID = "DRACA",  ContactName = "Tom Smith",  City = "Beverly Hills",  Address = "123 Melrose Place",  PostalCode = "90210"    };    context.Customers.InsertOnSubmit(customer);    context.SubmitChanges();    Console.WriteLine("Number of DRACA records: {0}",  context.Customers.Where(c => c.CustomerID == "DRACA").Count());      // Modify the record and verify it is changed through Count    customer.ContactName = "Joe Smith";    context.SubmitChanges();    Console.WriteLine("Number of Joe Smith records: {0}",  context.Customers.Where(c => c.ContactName == "Joe Smith").Count());      // Delete a record and verify it is removed through Count    context.Customers.DeleteOnSubmit(customer);    context.SubmitChanges();    Console.WriteLine("Number of DRACA records: {0}",  context.Customers.Where(c => c.CustomerID == "DRACA").Count());      // Pause to see the output    Console.ReadLine();

Calling Stored Procedures

The previous retrieve, create, update, and delete operations involved dynamically generated SQL statements. It is also possible to call stored procedures in place of dynamically generated SQL. The sample code below demonstrates how to call stored procedures. The Northwind database has a stored procedure called "Ten Most Expensive Products" we will use for this example. Once again using the Server Explorer navigate to the Stored Procedures in the tree view and drag the stored procedure to the design surface. You'll want to right click on the design surface and select "Show Methods Pane" if it isn't already available. You should now see a method, Ten_Most_Expensive_Products(), that has been created and we'll use in our next code example to get the list of top products and display the prices to the console.

  using (NorthwindDataClassesDataContext context =   new NorthwindDataClassesDataContext())  {    // Use the ten most expensive products stored procedure    var results = from products in context.Ten_Most_Expensive_Products()      select products;      foreach (var product in results)    {  Console.WriteLine("Product price is {0}", product.UnitPrice);    }      // Pause to see the output    Console.ReadLine();

The stored procedure example above is pretty straight forward. You can create your own stored procedures, including those that have input parameters, and use them in a similar manner.

Summary

You have now seen LINQ to SQL and the simplicity that it can bring to working with SQL Server based databases. We have examined the object model involved, retrieving and altering data through dynamically generated SQL statements, and calling stored procedures.

Future Columns

The topic of the next column is likely to be LINQ to Entities, which is another form of LINQ for accessing relational data. If you have something else in particular that you would like to see explained here please e-mail me.

About the Author

Mark Strawmyer is a Senior Architect of .NET applications for large and mid-size organizations. Mark is a technology leader with Crowe Horwath LLP in Indianapolis, Indiana. He specializes in architecture, design and development of Microsoft-based solutions. Mark was again honored to be named a Microsoft MVP for application development with C#. You can reach Mark at mark.strawmyer@crowehorwath.com.

Sitemap | Contact Us