Learning SQL Server 2008 Reporting Services — Save 50%
A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
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.
Drag and drop a LinqDataSource control from Toolbox|Data shown in the next figure on to the Default.aspx
This creates an instance of the control LinqDataSource1 as shown. The figure also shows the smart tasks of this control as shown.
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.
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.
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 .
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.
eBook Price: $29.99
Book Price: $49.99
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.
Now drag and drop the Customers tables into the first pane, the Object Relational Designer pane as shown.
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.
You can also see that a data context has been created as shown. The connection string is stored in the web.config file.
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.
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.
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.
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.
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.
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.
Now you may browse the Default.aspx page. The page gets displayed as shown.
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 :
eBook Price: $29.99
Book Price: $49.99
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 firstname.lastname@example.org
Books From Packt