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 [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
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
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
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
This action will add a Customer.cs file to your project as shown in figure 5 and will look like figure 6
Figure 5
Figure 6
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
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
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
Click on Next button as shown in figure 10
Figure 10
Click on where button on Configure Data Source Screen as shown in figure 11
Figure 11
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
On next screen as shown in figure 13, click on finish button to complete this process
Figure 13
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
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
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!
