Displaying SQL Server Data using a Linq Data Source

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

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

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 :

Books to Consider

comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free