This is an article continued from Developing a simple application using LINQ [Part 3]. In this part we will be looking at where we can use specific datatypes instead of using the "var" keyword, the Count(), Sum()and First() method. Continuing from the previous article, we create the third function.
PopulateProductStatistics() - In this function we will recover different statistics(products for reorder, types of products and total products) about the inventory. Here we will see two more functions in LINQ which are used to count the number of records retrieved and add all the values of a result set.
Notice that here instead of the "var" keyword we have used the datatype "int". This is because here we are sure of the datatype of the result which is a single integer value. In the "Products for reorder" part of the code above we will retrieve the result the LINQ statement into the integer variable ReorderProducts which will hold a single value.
The conditional LINQ statement retrieves those products whose quantity is below or equal to their reorder quantity. We encapsulate the LINQ statement into brackets and add ".Count()" after it. This simply counts the number of records or values retrieved. We simply assign the integer value ReorderProducts to the label lblReorderProducts after converting it to a string value. Simple isn’t it?
Similarly for the "Types of products" part of the code we count the total records in the table using ".Count()" and bind the retrieved integer value "TypesOfProducts" to the label lblTypesOfProducts after converting it to a string.
In the "Total products" part of the code, we calculate the total quantity of all the products combined. So here we encapsulate the LINQ statement into brackets and add ".Sum()" after it. This adds up all the values of the result set, which in this case it is the quantity of each and every product. We assign the retrieved integer value "TotalProducts" to the label "lblTotalProducts".
We also code the "SelectedIndexChanged" event of the listbox "listBox_Products" which shows the names of the products, so that when we click on a name, the values corresponding to the selected product show up in the textboxes related to deleting and modifying the product.
We retrieve specific records from the table by passing the "Product" name selected in the list box as a search parameter in the LINQ query. Notice, that here the LINQ statement has a ".First" extension in the end. What this does is that it selects the first record of a result set it there are more than one records matching the condition. Further process is as simple as assigning values to the appropriate form controls by calling properties of the variable "product". These are mapped to the columns of the "Products" table.
Now, that these functions are explained let us proceed further. We first call these functions in the form "Load" event. Our form is named "frmMain".
We call these here as the form displays the statistics as soon as it loads. In the next article we will move on to more important functionalities of LINQ like adding, deleting and altering a record.