Developing a simple application using LINQ [Part 2]

As you can see, we have: 

1. A grid view to show our output  – dataGridView_Products

2. A text box to input out search query – txtSearch

3. Three buttons- one for showing all products, one for showing reorder products and the last for initiating the search query – btnShowAllProducts, btnReorderProducts and btnSearch

When we had created our LINQ_Inventory file, a datacontext was created called “LINQ_InventoryDataContext” which is a class that provides access to the database tables and functions that can be performed on them. Let’s create an instance of this class which will enable us to access the functionality of the datacontext. We will be using it throughout the code to access the Products table data.

 

LINQ_InventoryDataContext InventoryDataContext = new LINQ_InventoryDataContext();

 

Now we will see how easy it is to retrieve data using LINQ and even easier to bind it to a datagrid control.

 

As you can see, we have extracted data from the products table and bound it to the data grid control in just two short lines of code within the “Click” event of the “All Products” button. The “var” keyword is used because we are retrieving variable data instead of a fixed type (in this case we are recovering multiple rows with multiple and not just a single datatype). As you can see we are accessing the “Products” table using the data context “InventoryDataContext” (we can access any table in a database this way depending on the tables we drag into the LINQ-to-SQL Class Designer from the database). The LINQ statement above is the same as “Select * from Products” in SQL.

Now that we have retrieved all the records in the Products table, we simply bind the “products” variable to DataSource property of the datagrid and we are done. It’s that simple.

Let’s move on to the “Products for Reorder” button where the only difference is that the LINQ statement is conditional. First a little about Reorder Quantity. This refers to the stock of a product in the inventory below. If a product stock declines it must be reordered to maintain continuous availability.

Here, in the LINQ statement we are comparing the current quantity of a product to its reorder level to determine whether it needs to be reordered. In SQL, this statement would translate to “Select * from Products where Products_Quantity<=Products_ReorderQuantity”. We bind the result of the LINQ statement to the data grid view in the same way as before (by binding the “products” variable to the DataSource property of the data grid. 

Next we have the “Search” button which retrieves records based on the search parameters. Here, we have deliberately kept the search a simple affair since an advanced search is beyond the scope of this article. We write the search code within the “Click” even of the “Search” button which will take the search parameters from the search textbox (txtSearch).

In this code we are simply looking whether the search string or any part of it occurs within the Product Name, Product Category or Product Price columns of the Products table. The “a.Product_Name.Contains(txtSearch.Text)” method simply checks whether the current record in the Column “Product_name” contains any part of the string passed in “txtSearch.Text” and returns a (boolean)true/false value. The “||” in the LINQ query is represents an “OR” in SQL.

In the next article we will move on to the controls present on the “Add/Delete” tab of the tab control.

Tell us what you think about LINQ, and send us your feedback by commenting below. You can  also join us on Facebook at dvwx.in/dvwx-fb, and follow us on Linkedin and Twitter (@devworx)