|
download source code
In Part 1 of this series on LINQ, you learned all the basics of LINQ including type inference, Lamda expressions,
extension methods, and anonymous types. You also discussed the use of standard query operators for querying in-memory
objects, and collections. With that background, this installment will now focus on the use of LINQ in querying
relational data. Specifically you will learn DLINQ (also known as LINQ to SQL) framework and its role in querying
relational data. Along the way, you will also understand the utilities shipped with LINQ that make this
relational querying a breezy experience. Finally, you will also learn how to submit changes back to the
database after making changes to the in-memory data contained in objects.
Introduction to LINQ
Microsoft's stated goal for DLINQ sounds quite simple and straightforward, which is representing relational data as objects and providing a translation mechanism to convert from relational data to objects and vice versa. However this is a very ambitious goal when considering the fact that the previous attempts at creating an object relational mapping framework never really took off and did not result in production-worthy code. Now with DLINQ, it is about to change as this new framework provides excellent relational to object mapping capabilities in addition to building on top of the LINQ's strong query capabilities.
DLINQ provides this relational to object mapping solution by giving you the object representation of a strongly typed database. This means you could use treat the relational tabular representation of data in the database as objects that are not only strongly typed but also checked at compile time in your .NET code.
To run the code samples supplied with this article, you need Visual Studio 2005 Professional RTM as well as
LINQ May 2006 Community Technology Preview.
Simple DLINQ Example
At the heart of DLINQ is the DataContext class. The DataContext is the main object through which you retrieve objects from the database and submit changes back. You use it in the same way that you would use an ADO.NET Connection object. In fact, the DataContext is initialized with a connection or connection string you supply. The purpose of the DataContext is to translate your requests for relational data into SQL queries and then assemble objects out of the results.
Before you can access the database, you need to create the DLINQ layer that is required for communicating with the database. You can create this using any of the three different approaches.
- Manually hard code the data classes by deriving from the DataContext class
- Use the DLINQ Visual Designer to create the classes
- Use the command line utility called sqlmetal.exe to create the classes
For the purposes of this article, I will make use of the sqlmetal.exe utility to create the DLINQ layer for communicating with the AdventureWorks database. The next section discusses the steps to be followed for creating this object model.
Creating the Object Model
To create the object model that maps to the relational data, there are two ways you can do this.
- To the class, add the mapping attributes that indicate how and where they should be stored in the database. This is referred to as attribute based mapping.
- It is also possible for you to specify the mapping using an external XML file. The external XML file abstracts the underlying database schema from the code thereby enabling you to make changes to the runtime schema mapping on the fly. This is a very powerful feature that can go a long way in increasing the extensibility of the solution.
I will show examples of both of these. Based on your requirements, you can choose any of these approaches with no impact to performance.
First, let us focus on how to use the sqlmetal utility. If you install the May 2006 LINQ CTP edition, you can find the sqlmetal utility in the "< Drive_Name>\Program Files\LINQ Preview\Bin" folder.
sqlmetal /server:localhost /database:AdventureWorks /user:username /password:password /code:AdventureWorks.cs
By default, the auto-generated AdventureWorks class follows the attribute-based mapping approach meaning that all the classes are decorated with the required relational data mapping.
As an example, if you look at the declaration of the ProductSubcategory class, you will see the class being decorated with the name of the table, which is ProductSubCategory, in this case.
[Table(Name="[Production].[ProductSubcategory]")]
public partial class ProductSubcategory :
System.Data.DLINQ.INotifyPropertyChanging,
System.ComponentModel.INotifyPropertyChanged
{
private int _ProductSubcategoryID;
private int _ProductCategoryID;
private string _Name;
-----
-----
}
To use the external XML file for mapping the table with the class, you need to supply the /map switch along with the name of the XML file as shown below:
sqlmetal /server:localhost /database:AdventureWorks /user:username /password:password /map:AdventureWorksMapping.xml /code:AdventureWorks.cs
As an example, here is how the mapping for the ProductSubcategory table looks.
<?xml version="1.0" encoding="utf-8"?>
<Database xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="AdventureWorks">
<Table Name="[Production].[ProductSubcategory]">
<Type Name=".ProductSubcategory">
<Column Name="ProductSubcategoryID" Member="ProductSubcategoryID"
Storage="_ProductSubcategoryID" DbType="Int NOT NULL IDENTITY"
IsIdentity="True" IsAutoGen="True" />
<Column Name="ProductCategoryID" Member="ProductCategoryID"
Storage="_ProductCategoryID" DbType="Int NOT NULL" />
<Column Name="Name" Member="Name" Storage="_Name"
DbType="NVarChar(50) NOT NULL" />
<Column Name="rowguid" Member="Rowguid" Storage="_Rowguid"
DbType="UniqueIdentifier NOT NULL" />
<Column Name="ModifiedDate" Member="ModifiedDate" Storage="_ModifiedDate"
DbType="DateTime NOT NULL" />
<Association Name="FK_Product_ProductSubcategory_ProductSubcategoryID"
Member="Product" Storage="_Product" ThisKey="ProductSubcategoryID"
OtherTable="Product" OtherKey="ProductSubcategoryID" />
<Association
Name="FK_ProductSubcategory_ProductCategory_ProductCategoryID"
Member="ProductCategory" Storage="_ProductCategory"
ThisKey="ProductCategoryID" OtherTable="ProductCategory"
OtherKey="ProductCategoryID" IsParent="True" />
</Type>
</Table>
---
---
---
</Database>
Since the mappings are established in the external file, if you examine the generated code file (AdventureWorks.cs) you will find that the references to the table names and column names are stripped off from the classes and properties. This approach is a lot cleaner as it allows you to keep the code separate from the mappings information.
Once data mappings and relationships are established, you can then easily write DLINQ code to perform queries and updates against a database.
Executing Queries
This section will focus on the code required to query the database using the AdventureWorks class created in the previous section. For example, the below code uses the AdventureWorks class to retrieve all categories and displays them in a GridView.
To start with, create a new Visual C# LINQ ASP.NET Web Site Template project using Visual Studio 2005. When you use this template to create the project, Visual Studio automatically adds an entry to the Web.config file to use the C# 3.0 compiler.
Once the Web site is created, add a new ASP.NET page named SimpleDLinq.aspx and modify its code to look as follows:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.DLINQ" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Query" %>
<%@ Import Namespace="System.Web.Configuration" %>
<script runat="server">
void Page_Load(object sender, EventArgs e)
{
string connectionString = WebConfigurationManager.ConnectionStrings
["AdventureWorks"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
AdventureWorks db = new AdventureWorks(connection);
gridCategories.DataSource = from category in
db.Production.ProductSubcategory
orderby category.ProductSubcategoryID
select category;
gridCategories.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Simple Data retrieval using DLINQ</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView runat="server" ID="gridCategories"
AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="ProductSubcategoryID"
HeaderText="Category ID"/>
<asp:BoundField DataField="Name" HeaderText="Name"/>
<asp:BoundField DataField="ModifiedDate"
HeaderText="ModifiedDate"/>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
To start with, all the namespaces including the core LINQ namespaces such as System.Query and System.Data.DLINQ are imported.
In the Page_Load event, you retrieve the connection string from the Web.config file and then create an instance of the SqlConnection object passing in the connection string to the constructor of the SqlConnection object.
SqlConnection connection = new SqlConnection(connectionString);
Once the SqlConnection object is created, you supply that as an argument to the constructor of the AdventureWorks class.
AdventureWorks db = new AdventureWorks(connection);
Note that if you are using the mapping file, you need to pass that mapping information (in the form of XmlMappingSource object) to the constructor of the AdventureWorks in addition to the SqlConnection object.
With this plumbing in place, you can now query any table in the AdventureWorks database using the simple dot notation. As an example, you can retrieval all the categories from the ProdutSubcategory table just by using the format db.Production.ProductSubcategory.
gridCategories.DataSource = from category in
db.Production.ProductSubcategory
orderby category.ProductSubcategoryID
select category;
As discussed in Part 1 of this article series, the from…orderby…select query retrieves all the categories and orders them by the category id column. Here is the output produced by the page when requested from the browser.
Selecting Specific Columns from the Resultset
In the previous example, the DLINQ query returned all the columns from the categories table. However there are times where you might want to restrict the number of columns returned from the query. To do this, you can use the anonymous type feature discussed in the Part 1 of this series. As an example, if you want to return just the category id and name columns, you modify the query to look as follows:
gridCategories.DataSource = from category in
db.Production.ProductSubcategory
orderby category.ProductSubcategoryID
select new
{
CategoryID = category.ProductSubcategoryID,
Name = category.Name
};
Once you make the above changes to the query, you also need to make the corresponding changes in the GridView. Specifically you need to modify the DataField attribute of the <asp:BoundField> elements to reflect the property names of the anonymous type.
<asp:GridView runat="server" ID="gridCategories"
AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CategoryID"
HeaderText="Category ID"/>
<asp:BoundField DataField="Name" HeaderText="Name"/>
</Columns>
</asp:GridView>
In the above declaration, the category id field is modified to reflect the property name, which is CategoryID in this case.
Executing a Stored Procedure
So far, you have seen how to query the tables directly using DLINQ layer. If you have a well layered design, you would typically have most of the logic contained within the stored procedures for reasons of abstraction and maintainability. If you want to reuse those stored procedures (as you migrate your existing applications to use DLINQ), you can still do that since DLINQ provides you with a way to invoke those stored procedures as well.
This section will show you a simple example on invoking a stored procedure using DLINQ. First, let us create a stored procedure named GetAllProductsByCategoryID that returns all the products for a specific category in the AdventureWorks database.
CREATE PROCEDURE [dbo].[GetAllProductsByCategoryID]
@ProductSubcategoryID INT
AS
BEGIN
SELECT ProductID, ProductSubcategoryID, Name, ProductNumber, ListPrice
FROM Production.Product WHERE ProductSubcategoryID = @ProductSubcategoryID
END
Now that the stored procedure is created, the next step is to create the required stored procedure to object mappings. Using the Sqlmetal utility, you can easily accomplish this by just adding the switch "/sprocs".
sqlmetal /server:localhost /database:AdventureWorks /user:username /password:password /sprocs /code:AdventureWorks.cs
With the above code executed, you can now execute all the stored procedures in the AdventureWorks database in the same way you queried the AdventureWorks tables in the previous section. As an example, the following ASP.NET page shows how to do execute the GetAllProductsByCategoryID stored procedure.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.DLINQ" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Query" %>
<%@ Import Namespace="System.Web.Configuration" %>
<script runat="server">
void Page_Load(object sender, EventArgs e)
{
string connectionString = WebConfigurationManager.ConnectionStrings
["AdventureWorks"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
AdventureWorks db = new AdventureWorks(connection);
gridProducts.DataSource = from prod in db.GetAllProductsByCategoryID(1)
orderby prod.ProductID
select prod;
gridProducts.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Executing a Stored Procedure using DLINQ</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView runat="server" ID="gridProducts"
AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="ProductSubcategoryID"
HeaderText="Category ID" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="ListPrice" HeaderText="List Price" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
The key change in the above code is the use of stored procedure in the from..select query.
gridProducts.DataSource = from prod in db.GetAllProductsByCategoryID(1)
orderby prod.ProductID
select prod;
For reasons of brevity, the value for the category id parameter is hard coded in the above code snippet.
If you navigate to the page using the browser, you should see an output that is somewhat similar to the below screenshot.
Selecting Hierarchical Data using LINQ
So far you have seen DLINQ queries that were focused on retreival of simple flat data. However you can use DLINQ queries to retrieve complex hierachical data as well. For example, you can use a single query to retrieve all the categories as well as all the products for each of the categories.
In this case, the list of products for each category will be embedded as a collection within the category object. Here is the ASP.NET page code required to produce the hierarchical display.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.DLINQ" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Query" %>
<%@ Import Namespace="System.Web.Configuration" %>
<script runat="server">
void Page_Load(object sender, EventArgs e)
{
string connectionString = WebConfigurationManager.ConnectionStrings
["AdventureWorks"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
AdventureWorks db = new AdventureWorks(connection);
gridCategories.DataSource = from category in
db.Production.ProductSubcategory
orderby category.ProductSubcategoryID
select new
{
CategoryID = category.ProductSubcategoryID,
Name = category.Name,
Products = category.Product
};
gridCategories.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Selecting Hierarchical Data using DLINQ</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gridCategories" runat="server"
AutoGenerateColumns="false" HeaderStyle-BackColor="blue"
HeaderStyle-ForeColor="White">
<Columns>
<asp:TemplateField HeaderText="Category">
<ItemStyle VerticalAlign="Top" Width="20%"/>
<ItemTemplate>
<br/><b><%# Eval("CategoryID") %></b>
<br/><br/><%# Eval("Name") %><br/>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Products">
<ItemStyle VerticalAlign="Top"/>
<ItemTemplate>
<asp:GridView AutoGenerateColumns="false" ID="gridProducts"
DataSource='<%# Eval("Products") %>' runat="server">
<Columns>
<asp:BoundField DataField="Name"
HeaderText="Product Name"/>
<asp:BoundField DataField="ProductNumber"
HeaderText="Product Number"/>
<asp:BoundField DataField="ListPrice"
HeaderText="List Price"
DataFormatString="{0:C}"/>
</Columns>
</asp:GridView>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
As you can see from the code, you have a parent GridView (that displays categories information) and a child GridView (that displays products information for each category) , which is embedded in the parent GridView. In the Page_Load event, when you set the parent GridView's DataSource property, you also include a property called Products that has all the products for the current category id. You get these list of products for the current category id using the Product property exposed through the category object. By examining the foreign key relationships between the ProductSubcategory and Product tables, the Sqlmetal utility automatically generated the Product property within the ProductSubcategory class.
gridCategories.DataSource = from category in
db.Production.ProductSubcategory
orderby category.ProductSubcategoryID
select new
{
CategoryID = category.ProductSubcategoryID,
Name = category.Name,
Products = category.Product
};
Once you have retrieved the products for the current category id, you can then assign that to the child GridView by setting the DataSource property to <% # Eval ("Products") %>. As each row in the category result set is populated in the parent GridView, the child GridView is also automatically populated with the corresponding products.
The output produced by the page is as follows:
Inserting a New Record
In addition to exposing powerful query features, DLINQ also provides for a way to make changes to the in-memory data and submit those changes back to the database. To accomplish this, all you do is to modify the in-memory object and then call the SubmitChanges() method on the object that derives from the DataContext class. In our case, it is the AdventureWorks class that derives from the DataContext class. DLINQ maintains enough tracking information to enable it to submit a change and use optimistic concurrency to check for concurrency issues. It also provides various facilities for changing optimistic concurrency behaviors. The below code shows an example where you add a new category to the ProductCategory table through the DLINQ layer.
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.DLINQ" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Query" %>
<%@ Import Namespace="System.Web.Configuration" %>
<script runat="server">
void Page_Load(object sender, EventArgs e)
{
string connectionString = WebConfigurationManager.ConnectionStrings
["AdventureWorks"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
AdventureWorks db = new AdventureWorks(connection);
ProductCategory cate = new ProductCategory();
cate.ProductCategoryID = 5;
cate.Name = "New Category";
cate.Rowguid = System.Guid.NewGuid();
cate.ModifiedDate = DateTime.Now;
db.Production.ProductCategory.Add(cate);
db.SubmitChanges();
lblResult.Text = "Category added successfully";
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Inserting a new row</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="lblResult" runat="server" />
</div>
</form>
</body>
</html>
The above code uses the following steps to add a new category to the category table.
- Create a new ProductCategory object that represents a category instance
- Populate the properties of the ProductCategory object with appropriate values
- Add the ProductCategory object to the ProductCategory collection using the Add() method
- Finally, commit the changes back to the database using the SubmitChanges() method
Note that till you invoke the SubmitChanges() method, the changes are not committed to the actual data in the database. When you do call SubmitChanges(), the DataContext will attempt to translate all your changes into equivalent SQL commands, inserting, updating or deleting rows in corresponding tables.
If you don't like the default submission behavior, you can also customize it by overriding the auto-generated code with custom commands. Similar to inserting a new record, you can also update an in-memory row and save the changes back to the database by calling the SubmitChanges() method.
Conclusion
In this part, we looked at the basics of performing data access using DLINQ. Then we looked at how to perform queries ranging all the way from simple queries, to executing stored procedures, and all the way to retrieving hierarchical data. Finally you also understood the steps involved in committing changes back to the database through the DLINQ layer. Part-3 of this series will discuss the XML extensions of LINQ (known as XLINQ) in depth by providing examples.
|
