Visual Studio 2008 Does ORM with LINQ

• Download demo project - 102.52 KB

Table of Contents

1. Introduction
2. The Northwind LINQ Web Application
3. Creating the LINQ Data Model Project
4. Creating the ASP.NET Web Application
5. Conclusion

Introduction

Microsoft enters the ORM (Object Relational Mapping) world with the introduction of LINQ (Language Integrated Query). This is provided as a built-in feature in Visual Studio 2008 and the .NET Framework 3.5 which was released to the public on November 19, 2007.

Like most ORM data models, LINQ offers the ability to work with databases and other form of data stores (such as XML) as pure objects. For example, if your data store is a SQL Server relational database then LINQ will convert your tables into objects called Entities and treat the table fields as Properties. LINQ also provides full support for your stored procedures by using them as Methods (providing functionalities for your objects).

So why would anybody want to use LINQ? Well, in most cases today, if you want to provide data to your objects, you would do the following steps:

1. Use a connection string to authenticate and connect to a database using ADO.NET commands
2. Either call a stored procedure or run a SELECT SQL statement to get the data
3. Store it in a DataSet or DataReader object , and
4. Finally bind it to some data control (i.e., GridView, DropdownListBox, and so on…)

Assumed you have an ADO.NET wrapper called ADOSqlHelper, your code might look similar to this:

string conStr = "";
DataSet ds = ADOSqlHelper.GetDataSource(conStr,"SELECT * FROM Customers")
GridView1.DataSoure = ds.Tables[0];
GridView1.DataBind();

Not that there is anything wrong with doing it that way, but using LINQ offers an alternative. Once you have defined a LINQ object model using the designer palette in Visual Studio 2008, populating the GridView control with data is reduced to a couple of statements using new C# syntax:

GridView1.DataSource = from cust in Customers select cust;
GridView1.DataBind();

In addition to that, LINQ takes care of the behind the scene plumbing for you. It even took care of writing code for Inserting, Updating, and Deleting the data.

In this article you will learn how to use LINQ through a step by step example of creating a basic website that accesses data using LINQ. I have provided the sample demonstration web application as a zip file. Please read the Application Requirements section below for instructions on how to install and use it.

I am by no means a LINQ expert and there is so much to learn about LINQ. This article serves only as an introduction. For more information please visit http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx.

Requirements

1. A computer with .NET Framework 3.5 and Visual Studio 2008 installed.
2. Access to either a local or network instance of SQL Server.
3. A copy of the Northwind database attached to SQL Server (you can get this here).
4. The developer is assumed to have at least basic experience developing .NET windows and web applications.

Installing and Compiling the Sample Code

1. Download the NothwindLingWebApplication.zip file accompanying this article.
2. Unzip the file to a folder on your local drive.
3. Open the project solution and make the following configuration changes to the files below:
   • Project Name: NorthWindLINQSampleAppDataService
   • Files: App.Config, Setting.settings, and Setting.designer.cs
   • Change: the connection string properties Data Source, User ID, and Password values to that of your database.
4. Compile by building the solution.
5. Run the application.

The Northwind LINQ Web Application

The Northwind LINQ Web Application is a demo ASP.NET web application written in Visual Studio 2008 (C#) on top of the .NET Framework 3.5. It consists of two major functionalities as displayed in the main screen below:

When you select the link View and Manage Northwind Customers you are presented with a page that displays the list of customers from the Northwind Microsoft SQL Server sample database. From that page you have functionalities to Add, Edit, and Delete customers. See screen shot below.

When you select the link View Northwind Suppliers and their Products from the main screen you are presented with a page that displays the list of suppliers and their products.

The goal is to implement these functionalities for the demo application using LING as an ORM. To do this a Visual Studio 2008 solution with two projects will be created. The first project is a class library to hold our data model. The second project is an ASP.NET web application that contains the pages that will consume the object data model created in the class library project.

Creating the LINQ Data Model Project

To start developing the LINQ data model we create a new project that is a class library project type. See screen shot below.

Next we add a new LINQ to SQL item called Northwind to the project. It has an extension of DBML (short for database model).

This file will serve as the container for the newly created Northwind object database model. It actually contains two panes: one for holding the Entities (tables) with their Properties (table fields), and the other to hold the Methods (optional stored procedures). See screen shot below.

LINQ needs to know which server and database to use. To do this a database connection is required. To connect to a database, In the Visual Studio 2008 designer, click on the Tools menu and select the Connect to Database… item. It will then prompt to choose a data source as displayed in the screen shot below.

Then it will give the opportunity to select the SQL Server instance and database to model against. See below.

If successfully connected to a database then the Visual Studio designer palette now includes the Server Explorer column with the selected database and its tables and stored procedures as displayed in the screen shot below.

Now it is just a matter of opening the Tables and Stored procedures folders and dragging and dropping the tables (data classes) and stored procedures (methods) to be included with the model. For this project the tables Customer, Order, Order_Detail, Supplier, and Product are dropped in the data classes pane. Also the stored procedure CustOrderHist is dropped in the methods pane. The Visual Studio window looks like the following screen.

As the screen shows the model is presented nicely as an object relational model. The classes and their properties are displayed as boxes. Also, notice the one-to-many relationships existence between the classes as indicated by the arrows. Don’t forget the stored procedure also shown on the right side. That’s it! The model is done and we didn’t even write one line of code. Everything required to provide functionality for our web application is done (including Adding, Selecting, Updating, and deleting data). Actually, there’s a lot that took place behind the scene. But this is beyond this article. Please visit MSDN. For now let’s continue by implementing the ASP.NET web application. It’s time to start coding.

Creating the ASP.NET Web Application

Now that we have a data model that the web application can use to consume data we can start looking at the application’s functionalities. The web application must provide the following functions:

1. Displaying the customer list
2. Adding a new customer
3. Editing an existing customer
4. Deleting an existing customer
5. Displaying the Supplier list along with the product list for each Supplier.

Add a new ASP.NET Web Application project to the Visual Studio 2008 solution. Add a Reference to the data model project you created above. See screen shot below.

To save time please download and install the sample demo application zip file in order to view the ASPX and C# code files as I will now refer to them to continue the demonstration. See requirement and installation instructions in the Introduction section above.

Displaying the Customer List

The CustomerListing.aspx file contains a GridView control in which is used to bind to the Customers object we created in the data model. The code-behind located in the CustomerListing.aspx.cs file contains the following code snippet:

NorthwindDataContext db = new NorthwindDataContext();
var customers = from c in db.Customers select c;

grdCust.DataSource = customers;
grdCust.DataBind();

After referencing the data model class library into your ASP.NET web project, in order to start using its objects, you must create a new instance of the DataContext class. This class is the heart of LINQ. It is the main object through which you retrieve data from the database and submit changes back. It serves as the translator for the application request into the SQL queries.

Notice the new C# syntax of the .NET Framework 3.5. With a new DataContext instance, all of the objects and methods are available for you to query. Intellisense makes it very easy to use. See the screen shot below:

To get the list, simply declare a variable (using var) and set it to the query statement “from c in db.Customers select c;”. Then set the GridView’s DataSource property to the variable and call the its DataBind method.

Adding a New Customer

Adding a new customer to the Northwind database is just as simple. The web form file CustomerAdd.aspx contains three TextBox controls that are used to hold the three input values: Customer ID, Company Name, and Contact Name. The C# code-behind file CustomerAdd.aspx.cs provides the LINQ syntax required to create a new record in the Customers table.

NorthwindDataContext db = new NorthwindDataContext();
Customer customer = new Customer();

customer.CustomerID = txtCustomerID.Text.Trim();
customer.CompanyName = txtCompanyName.Text.Trim();
customer.ContactName = txtContactName.Text.Trim();

db.Customers.InsertOnSubmit(customer);
db.SubmitChanges();

First create a new instance of the NorthwindDataContext object. Then create a new customer using the Customer object. Set the customer’s properties (CustomerID, CustomerName, and ContactName) to the TextBox controls. Then call the InsertOnSubmit method passing it the customer object as the parameter holding the data to be inserted into the database. In order for the changes to take effect, the SubmitChanges method must be called. That’s it! You don’t have to worry about database connection, writing queries, and so on.

Updating a Customer

Updating a customer record is very similar to adding a new record. Please refer to the CustomerEdit.aspx and CustomerEdit.aspx.cs to view the layout of the web page.

//get the customer id that we want to edit from query string;
string customerID = Request.QueryString["customerID"].ToString();

//Use LINQ syntax to get customerID's data to be edited
NorthwindDataContext db = new NorthwindDataContext();
Customer customer = db.Customers.Single(c => c.CustomerID == customerID);

//set the customer properties to the text box controls
customer.CompanyName = txtCompanyName.Text;
customer.ContactName = txtContactName.Text;

//submit changes to take effect
db.SubmitChanges();

First get the customer ID from the Http request QueryString variable. Next use a DataContext instance to return a Single record from the Customers object for that customer. Then set the values of the customer object to the TextBox control’s Text property. Finally, call the SubmitChanges method so the changes can take effect.

Deleting a Customer

Deleting a customer is very similar to updating a customer. Refer to the CustomerDelete.aspx and CustomerDelete.aspx.cs files to view the layout of the web page.

string custID = "";
if (Request["CustomerID"] != null)
    custID = Request["CustomerID"].ToString();

if (custID.Length != 0)
{
    NorthwindDataContext db = new NorthwindDataContext();
    Customer customer = db.Customers.Single(c => c.CustomerID == custID);
    db.Customers.DeleteOnSubmit (customer);
    db.SubmitChanges ();
 }

First get the customer ID form the Http request QueryString variable. Next use a DataContext instance to return a Single record from the Customers object for the customer. Then call the DeleteOnSubmit method of the Customers object. Finally, call the SubmitChanges method so the command can take effect.

Displaying the Suppliers list and their products

Displaying the supplier listing is a bit more complicated because it is going to involve displaying a GridView control as a column of another GridView control. Refer to the SupplierListing.aspx and SupplierListing.aspx.cs files to see layout of the page. A partial showing of the ASPX file is displayed below.

<asp:GridView ID="grdSuppliers" runat="server" AutoGenerateColumns="false">
   <Columns>
     <asp:BoundField DataField="SupplierID" HeaderText="Supplier ID" />
     <asp:BoundField DataField="CompanyName" HeaderText="Company Name" />
      <asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
         <asp:TemplateField HeaderText="Products">
           <ItemTemplate>
             <asp:GridView ID="GridView1" AutoGenerateColumns ="false" runat="server" 
               DataSource ='<%# GetSupplierProducts (
(int)DataBinder.Eval(Container.DataItem, "SupplierID") ) %>'>
               <Columns>
                  <asp:BoundField DataField="ProductID" HeaderText="Product ID" />
                  <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
              </Columns> 
             </asp:GridView> 
           </ItemTemplate>
         </asp:TemplateField>
   </Columns>
</asp:GridView>

As you can see the GridView control with ID grdSuppliers contains three BoundField columns and one TemplateField column. The TemplateFiled column is a GridView control with two columns representing the product ID and the product name. The DataSource for this GridView is set to a method called GetSupplierProducts which returns an IQueryable<Product> (a collection in the object model). The implementation method is presented below.

public IQueryable<Product>  GetSupplierProducts(int supplierID)
{
    NorthwindDataContext db = new NorthwindDataContext();
    var products = from p in db.Products where p.SupplierID == supplierID select p;
    return (products);
}

This method takes an integer parameter which is the expected supplier ID. This is done using DataBinder.Eval which is an ASP.NET static method that is used to evaluate a data-binding expression at runtime. Next, the GridView control with ID grdSuppliers is populated using the following syntax:

NorthwindDataContext db = new NorthwindDataContext();
var supplier = from s in db.Suppliers select s;

//set and bind LINQ source to datagrid control
grdSuppliers.DataSource = supplier;
grdSuppliers.DataBind();

Conclusion

In this article we introduced LINQ (Language Integrated Query) as an ORM (Object Relational Model). We defined it as a data access method presented as an object data model – converted from a SQL Server database. It is built-in Visual Studio 2008 and the new .NET Framework 3.5. We explained how it is used through a sample Visual Studio 2008 project solution by creating 2 projects: the data model and an ASP.NET web application. The data model provided all the functionalities that were required for the web application including Retrieving data, Adding data, Updating data, and Deleting data. And we didn’t have to write not one line of ADO.NET data access code.

We also looked at some of the new C# integrated query language syntax. There is a bit of learning curve. To start using the data model we saw that it must first be referenced into a project. After that a new instance of the DataContext can be created in order to have access to all of the objects and methods available to query.