Click here to Skip to main content
Click here to Skip to main content

Using LinqDataSource with ASP.NET data controls [Part II]

, 18 Jun 2008
Rate this:
Please Sign up or sign in to vote.
Part II of the Using LinqDataSource with ASP.NET data controls like gridview and dropdown list
Note: Please vote and give your feedback on this series so that future efforts can be improved / aligned with your expectation. Thanks!

Topics covered
Intro, LINQ to SQL class file, LINQ DataSource and .dbml file structure DataContext class, writing LINQ in C# 3.0 and parameter zing LINQ queries change underlying TSQL on the fly using LinqDataSource and part of CRUD operation [update and delete] Insert using LinqDataSource.Insert() and managing database connection via web.config
LinqDataSource Part I LinqDataSource Part II LinqDataSource Part III LinqDataSource Part IV

Using LinqDataSource with ASP.NET data controls [Part II]

This is part II of this article. Part I is available here. We will continue with the sample project we have started in Part I.[ Part IIIis available here]

Now we will add a Dropdown list to our project and will use it as a filter. One can complete this task in 15/ 20 minutes. Important point covered in this part includes DataContext class, writing LINQ in C# 3.0 and parameter zing LINQ queries

Let’s get started. From toolbox, add a Dropdownlist on default.aspx as shown in figure 1

Figure 1

image001.jpg

We will add a few lines of C# code. Notice that we are half way through and haven’t written a single line of plumbing code. We will populate the Dropdown list using LINQ query. Again, if you are new to LINQ, I will highly recommend you to visit ScottGu’s blogpost.

The code to populate the Dropdown list will be added to the Page_Load event as shown in figure 2

Figure 2

image002.jpg

As marked by step 1 and 2, it’s clear that this is a page_load event on the default.aspx.cs page. Important thing to note here really starts with step 3.

CustomerDataContext context = new CustomerDataContext()

What is CustomerDataContext? How to figure out its origin? Honestly, these are the points that will confuse any one new to LINQ. So lets focus on solving this mystery. Remember in part I, we added a LINQ to SQL file customer.dbml to our project as shown in figure 3

Figure 3

image003.jpg

The moment we dropped a customer table on customer.dbml, ASP.NET generated some code for us. Although we ignored it in part I, we can’t afford to overlook it anymore. Just double click on Customer.dbml in your solution explorer. This will open a Customer.dbml file in VS IDE. As shown in figure 4, select view code to find out more details

Figure 4

image004.jpg

This action will add a Customer.cs file to your project as shown in figure 5 and will look like figure 6

Figure 5

image005.jpg

Figure 6

image006.jpg

Notice partial class CustomerDataContext. This partial class is our key to the LINQ kingdom. If you go back to figure 2, step 3 – that’s what we are refering to. This is our connection to the database. So any table , stored procedure, function dropped on .dbml file will be available via this DataContect partial class. This partial class is very important for writing LINQ queries. Refer to this MSDN explanation for more details on DataContext class. Just try intellisence to explore the possibilities with DataContext as shown in figure 7.

Figure 7

image007.jpg

Enough on DataContext class for now. Back to figure no 2, step 4 – this is a real LINQ query using implicit type var. Implicit type is a new C# 3.0 feature. You will find this var all over the LINQ documentation and samples.

var query = from ct in context.customers

select ct.customer_type;

Now this query syntax is quite counter intuitive for anyone familiar with T-SQL. First from caluse then select syntax. I guess this will be the biggest hurdle for anyone walking into LINQ domain. But there is a reason for this. I will leave that discussion for some other time. Suffice to say that above code will work, will compile and is a valid LINQ syntax.

What we will get in return is a collection of all customer types from customer table. This is same as saying “select customer_type from customer” in T-SQL.

As you can see from this T-SQL, we will get all the customer types, albit all – with duplicate onces repeated. What we really need is “select distinct customer_type from customer” – correct. Now problem is to figure out how? I guess most of us will be able to write T-SQL fairly quickly, but will struggle with corrosponding LINQ syntax. So I will recommend this great source. [Unfortunately I don’t know any tool that will take T-SQL and convert it to LINQ. Let me know if you find one]

As explained in figure 2, step 5 – we will use query.Distinct() to achieve this result. And ToList() will insure that this query runs only one time as explained on Mike Taulty’s blog.

Figure 2, step 6 will bind this LINQ query with our DropDown List. At this point if you build your project – it will compile and will work. The outcome of writing these 12 -15 lines of hard C# code would result in figure 8

Figure 8

image008.jpg

Hurray! We are almost done. We wrote our LINQ query and it did work. Now, only thing remaining is to use this DropDown list to filter the outcome of GridView result set. As shown in figure 9, select LinqDataSource – Configure Data Source option by clicking on LinqDataSource1 – smarttag option

Figure 9

image009.jpg

Click on Next button as shown in figure 10

Figure 10

image010.jpg

Click on where button on Configure Data Source Screen as shown in figure 11

Figure 11

image011.jpg

We want to filter end result based on customer_type, so use that from column dropdown list. This value should come from control –so choose DropDownList from ControlID droprdown list. And default value should be “Normal”. Click on Add button and then on OK button as shown in figure 12

Figure 12

image012.jpg

On next screen as shown in figure 13, click on finish button to complete this process

Figure 13

image013.jpg

On last thing, and most important one is to enable auto postback on the dropdownlist as shown in figure 14. This is the thing I forget almost all the time.

Figure 14

image014.jpg

Now build the project and evaluate the end result in your favorite browser. We should see the output as shown in figure 15

Figure 15

image015.jpg

image016.jpg

image017.jpg

Don’t forget to evaluate the T-SQL coming across the pipe in query profiler. In fact, SQL server query profiler is an important tool in understanding LINQ queries. After all SQL server is the place where “rubber meets the road”.

We got the working LINQ project that is doing something useful in just under 15 lines of C# code. Now we can add paging on Gridview and check the underlying T-SQL. And one more thing – I just heard from some one –“Wouldn’t it be nice to have All option – I mean – I can select “All” from DropDown List and can see customers of all the types” . Why not? That’s the fun of developing software – you can start building from level 3 then to level 7 and then the foundation – I guess this requirement is not that bad. So we will add paging and this “All” option in our next iteration of this project, in Part III of this article. Part I of this article covers more basics in case you missed it

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

abhigad
Architect Wells Fargo
United States United States
MS Computer Science + Information Science
Architect at Wells Fargo [Wells Fargo Dealer Services -WFDS]
Blog: Profile

abhijit gadkari
abhijit dot gadkari at gmail dot com
Irvine-92618,CA

Comments and Discussions

 
QuestionHow to get distinct rows from a list using LINQ:A Pingroupelizas25-Jan-10 2:56 
Generalproblem in linq Pinmembervaidadi26-Oct-09 20:42 
AnswerRe: problem in linq Pinmemberabhigad27-Oct-09 5:13 
GeneralAdding fix value to column Pinmemberaliarjmandi21-Jul-09 10:55 
GeneralNice articles! and another source of Linq info Pinmembergholbrook16-Jun-08 19:47 
GeneralRe: Nice articles! and another source of Linq info Pinmemberabhigad17-Jun-08 14:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 19 Jun 2008
Article Copyright 2008 by abhigad
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid