Displaying SQL Server Data using a Linq Data Source

Exclusive offer: get 80% off this eBook here
Learning SQL Server 2008 Reporting Services

Learning SQL Server 2008 Reporting Services — Save 80%

A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008

₨170.00    ₨34.00
by Dr. Jay Krishnaswamy | May 2009 | Microsoft

Linq to SQL is an object relational model that you can leverage in Visual Studio 2008. Your connection to SQL Server 2008 database is through a new control in Visual Studio 2008, the LinqDataSource control. This control needs a data context. The data context is provided by the Linq to SQL classes, a class generator that maps SQL server objects to the model. The class files generated support CRUD operations.

In this article by Dr. Jay Krishnaswamy, we will be adding a LinqDataSource control in Visual Studio 2008 to a ASP.NET website and configuring it by providing a data context. The data context will created using the Object Relational Designer(O/R Designer) in Visual Studio. The Linq data source is then bound to the Gridview in the Visual Studio designer.

Create web site project and add LinqDataSource control

Open Visual Studio 2008 from its shortcut on the desktop. Click File | New | Web Site...(or Shift+Alt+N) to open the New Web Site window. Change the default name of the site to a name of your choice (herein LinqDemo)on your local web server as shown. Make sure you are creating a .NET Framework 3.5 web site as shown here.

Learning SQL Server 2008 Reporting Services

Drag and drop a LinqDataSource control from Toolbox|Data shown in the next figure on to the Default.aspx

Learning SQL Server 2008 Reporting Services

This creates an instance of the control LinqDataSource1 as shown. The figure also shows the smart tasks of this control as shown.

Learning SQL Server 2008 Reporting Services

Create a data context for the LinqDataSource control

In order to use this control you also need to create a data context. Right click the localhost web site and choose Add New Item...to open the Add New Item - http://localhost/ LinqDemo window as shown.

Learning SQL Server 2008 Reporting Services

In the Visual Studio installed templates highlight Linq to SQL Classes. Change the default name from DataClasses.dbml to a name of your choosing. Herein MyDC.dbml. Click Add. This pops-up a Microsoft Visual Studio warning message as shown. The preferred location for this file is in the App_Code folder of your project as suggested here.

Learning SQL Server 2008 Reporting Services

Click on Yes. This adds a MyDC.dbml file to APP_Code folder as shown. MyDC.dbml consists of two components MyDC.dbml and MyDC.designer.vb to the App_Code folder as shown .

Learning SQL Server 2008 Reporting Services

Double click the MyDC.dbml node in the APP_Code folder. This opens the ObjectRelational Designer and the designer surface with two panes as shown. Read the instructions in the windows. In the left pane you can drag and drop items from the Server Explorer in Visual Studio to create the appropriate classes and in the right pane you can drag and drop stored procedures. In this article we will be looking at just creating classes from table objects.

Learning SQL Server 2008 Reporting Services

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: ₨170.00
Book Price: ₨425.00
See more
Select your format and quantity:

Configure the LinqDataSource control and bind it to data

We assume that you have created a connection to your SQL Server 2008 in Visual Studio 2008 (Add a new Data Connection by clicking on the icon in the Server Explorer and create a connection to SQL Server 2008's Northwind database). From View | Server Explorer you can access the objects on your SQL Server after expanding the nodes as shown.

Learning SQL Server 2008 Reporting Services

Now drag and drop the Customers tables into the first pane, the Object Relational Designer pane as shown.

Learning SQL Server 2008 Reporting Services

This activity creates code which you may review by opening the App_COde/MyDC.designer.vb. Highlight the Customer object in the ObjectRelational designer and right click to review its properties. You will see that it has created a Customer Data class using Customers table as shown.

Learning SQL Server 2008 Reporting Services

You can also see that a data context has been created as shown. The connection string is stored in the web.config file.

Learning SQL Server 2008 Reporting Services

Click on the Configure Data Source... in the LinqDataControl's smart tasks. This opens the "Choose a Context Object" of the Configure Data Source -LinqDataSource1 wizard. The MyDCDataContext should be visible as shown.

Learning SQL Server 2008 Reporting Services

Click Next. In the "Configure Data Selection" page of the wizard choose a couple of items. Herein CustomerID, CompanyName, Address, City, and Country are chosen. You can also filter and order your selection using the Where... and OrderBy..buttons. Here the defaults are used.

Learning SQL Server 2008 Reporting Services

Click Finish. This completes the LinqDataSource control. You can see these details in the source of Default.aspx as shown in the LinqDataSource control's declarative code.

Learning SQL Server 2008 Reporting Services

Adding a GridView and associating it with data

Drag and drop a GridView from the Toolbox | Data on to the design surface of the Default.aspx page below the LinqDataSource1. Click on the Smart Tasks to associate the LinqDataSource as a source for the GridView as shown.

Learning SQL Server 2008 Reporting Services

Click on LinqDataSource1. This adds few more tasks to the Smart Tasks and sets up the GridView with the meta data as shown.

For the Default.aspx to properly display the data, the Northwind database should have the Hodentek2ASPNET login.

Learning SQL Server 2008 Reporting Services

You may improve the GridView displayed data by performing the other Smart Tasks in the previous window. For example you can edit the column properties, enable sorting in the displayed page, enable paging if the data fills more than a page, etc. The databindings to the GridView is shown in the following declarative code.

Learning SQL Server 2008 Reporting Services

Now you may browse the Default.aspx page. The page gets displayed as shown.

Learning SQL Server 2008 Reporting Services

Summary

The article describes step-by-step how you may utilize the LinqDataSource control in Visual Studio 2008 and use it together with a GridView control to display server data. SQL Server 2008 provides the backend data in the form of Customers table.

 

If you have read this article you may be interested to view :

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: ₨170.00
Book Price: ₨425.00
See more
Select your format and quantity:

About the Author :


Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his website or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net

Books From Packt

 

VSTO 3.0 for Office 2007 Programming
VSTO 3.0 for Office 2007 Programming

SOA Patterns with BizTalk Server 2009
SOA Patterns with BizTalk Server 2009

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009

Joomla! E-Commerce with VirtueMart
Joomla! E-Commerce with VirtueMart

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

WCF Multi-tier Services Development with LINQ
WCF Multi-tier Services Development with LINQ

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

Small Business Server 2008 – Installation, Migration, and Configuration
Small Business Server 2008 Installation, Migration, and Configuration

 

Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software