LinqDataSource with ASP.NET data controls

Note: Please vote and give your feedback on this series so that future efforts can be improved / aligned with your expectation. Thanks!

Using LinqDataSource with ASP.NET data controls

On one of my projects I used LINQ. Since this was my first LINQ project, I decided to use Linqdatasource to fetch data from database. Another reason for using LinqDatasource is because of its in-built support for grid view paging and sorting functionality.

One can complete this task in 15/ 20 minutes. Important point covered in this part includes LINQ to SQL class file, LINQ DataSource and .dbml file structure Part II of this article is available here. Part III is available here

If you use Linqdatasource with Gridview and enable the paging, LINQ will write a decent paging query. I remember writing my own paging logic in stored procedure and was impressed with T-SQL generated by LINQ. LINQ is doing the same thing I did inside my stored procedure. [How LINQ queries work is byond the scope of this article. But one good explanation can be found on Mike Taulty’s blog]

So this article is a gist of LinqDataSource control. We will first create a web application project. Our aim is to fetch the data from customer table and display it using a gridview control. In part II, we will add a dropdown box to filter this gridview.

If you are new to LINQ, please visit ScottGu’s blogpost]. Note: you will need Visual Studio 2008 to work with this sample project.

Now on to the LinqDatasource… Create a new web application project in VS 2008.

Please enable Server Explorer from view menu if it’s not visible. From Server explorer, right click on Data Connection and click on Add Connection. Follow the wizard to add a database connection. Once you are done with database setup, your Server explorer will display the database objects. This view is just like an object browser in SQL Server.

We will be using a customer table in this project. But before we start working with database, we will need LINQ to SQL classes. Right click on your project in solution explorer and from add new item selection, choose LINQ to SQL classes file as shown below

Once you are done with adding LINQ to SQL file to your project, you can start working with the database. Note that Customer.dbml file is added to our project as shown below

Open the customer.dbml file and drag-n-drop the customer table from server explorer on the surface of Customer.dbml file as shown below

That’s it! We are done with setting up a database connection – now we can work with customer table in our project. Let’s browse customer table in VSS

Save the project and try building it. It should build without errors or warnings. After you are done with building your project add a grid view and LinqDatasource to the default.aspx page as shown below

Configure data source as shown below. Please don’t change anything on configure data source page. Defaults are just fine and will work like a charm – just hit the next button and select customer table from the dropdown list as shown in the following sequence

Click the finish button to complete this task. We will get back to where option once we complete the step 1.

Now set this datasource as a source for our grid view. Build and run the project.

That’s it – we are done with fetching data from database and displaying it on the webpage using LINQ.

Please open SQL Server query profiler and watch the TSQL generated by LINQ. It is not much different than the one you will write to accomplish such task. In part II we will filter the result set based on the dropdown list selection. We will use customer_type column for this filter operation. For example, display only corporate customers. We will also enable paging on grid view and analyze underlying TSQL.

Part III of this article is available here

Best luck and happy programming!

Note: Please vote and give your feedback on this series so that future efforts can be improved / aligned with your expectation. Thanks!