Querying the Database C# Help

As you’ve seen, there are a number of ways in which you can query the database from the code of your application, In some of the simplest forms, your queries looked like the following:

Table<Product> query = dc.Products;

This command was pulling down the entire Products table to your query object instance.

Using Query Expressions

In addition to a pulling a table straight out of the database using dc . Products, you also can use a query expression directly in your code that is strongly typed. An example of this is shown in the following code:

using System;
using System.Linq;

namespace ConsoleApplicationl
class Classl
static void Main(string[] args)
NorthwindDataContext dc = new NorthwindDataContext();
var query = from p in dc.Products
select p;
foreach (Product’ item in query)
Console.WriteLine(item.ProductID + • • + item.ProductName);
Console.ReadLine() ;

In this case, a query object (again, a Table<Product> object) is populated with the query value of from in dc. Products select p;. This command, though shown on two lines for readability purposes, can also be presented on a single line if you wish.

Query Expressions In Detail

You will find that there are a number of query expressions that you can use from your code, The previous Example is a simple select statement that returns the entire table, The following list of items are some of the other query expressions that you have at your disposal.

Filtering Using Expressions

In addition to straight queries for the entire table, you can filter items using the where and distinct options The following provides an example of querying the products table for a specific type of record:
var query = from p in dc. Products
where ProductName.StartsWith(‘L’)
select p;

In this case, this query is selecting all the records from the Products table that start with the letter L, This is done via the where p. ProductName. Starts With (L) expression, You will find a large selection of methods available from the Product Name property that allows you to fine-tune the filtering you need. This operation produces the following results:

65 Louisiana Fiery Hot Pepper Sauce
6& Louisiana Hot Spiced Okra
67 Laughing Lumberjack Lager
74 Longlife Tofu
76 Lakkalikoori

You can also add as many of these expressions to the list as you need, For instance, here is an example of adding two where statements to your query:

var query = from p in dC.Products
where p.ProductName.StartsWith(“L”)
where p.ProductName.EndsWith(“i”)
select p;

In this case, there is a filter expression that looks for items with a product name starting with the letter L and then a second expression is done to make sure that the second criteria is also applied, which states that the items must also?, end with the letter i. This would give you the following results:

76 I Lakkalikoori

Performing Joins

In addition to working with one table, you can work with multiple tables and perform joins with your queries, If you drag and drop both the Customers table and the Orders table onto the Northwind

Figure 27-9

Figure 27-9

From this figure, you can see that after you drag and drop both of these elements onto the design surface, Visual Studio will know that there is a relationship between these items and will create this relationship for you in the code and represent it with the black arrow.
From here, you can use a join statement in your query to work with both of the tables as presented in the following example:

This example is pulling from the Customers table and joining on the Orders table where the CustomerID columns match. This is done through the join statement:

join 0 in dc.Orders on c.CustomerID equals o.CustomerID

From here, a new object is created with the select new statement and this new object comprises of the CustomerID, CompanyName,and Country columns from the Customer table as well as the OrderID and OrderDate columns from the Orders table, When it comes to iterating through the collection of this new object, the interesting part is that the foreach statement also uses the var keyword because the type is not known at this point in time:

foreach (var item in query)
Console.WriteLine(item.CustomerID + • I • + item.CompanyName
+ • I • + item.Country +’ I • + item.OrderID
.+ • I • + item.OrderDate);

Regardless, the item object here has access to all the properties that you specified, When you run this example, you will get results similar to what is presented in this partial result:

WILMK Wilrnan Kala Finland 10695 10/7/1997 12:00:00 AM
WILMK Wilman Kala Finland 10615 7/30/1997 12:00~00 AM
WILMK Wilman Kala Finland 10673 9/18/1997 12:00:00 AM
WILMK Wilman Kala Finland 11005 4/7/1998 12:00:00 AM
WILMK Wilman Kala Finland 10879 2/10/1998 12:00:00 AM
WILMK Wilman Kala Finland 10873 2/6/1998 12;00:00 AM
WILMK Wilrnan Kala Finland 10910 2/26/1998 12:00:00 AM

Grouping Items

You are also easily able to group items with your queries. In the Northwind, dbml example that you are working with, drag and drop the Categories table onto the design surface and you will see that there is a relation with this table and the Products table from earlier, The following example shows you how to group products by categories:

This example creates a new object, which is a group of categories, and packages the entire Product table into this new table called g.Before that, the categories are ordered by name using the order by statement because the order provided is an ascending order (the other option being descending), The output is the Category (passed in through the Key property) and the Product instance The iteration with the foreach statements is done once for the categories and another for each of the products that are found in the category.

A partial output of this program is presented here:
Guarana Fantastica
Sasquatch Ale
Steel eye Stout
Cote de Blaye
Chartreuse verte
Ipoh Coffee
Laughing Lumberjack Lager
Outback Lager
Rhonbrau Klosterbier
Aniseed Syrup .
Chef Anton’s Cajun Seasoning
Chef Anton’s GumboMix
Grandma’s Boysenberry Spread
Northwoods Cranberry_ Sauce
Genen Shouyu
Gula Malacca
Sirop d ‘ erable
Louisiana Fiery Hot Pepper Sauce
Louisiana hot Spiced Okra
Original Frankfurter grune Sobe

You will find that there a lot more commands and expressions available to you beyond what are presented in this short chapter.

Stored Procedures

So far, you have been querying the tables directly and leaving it up to to create the appropriate SQL statement for the operation, When working with pre-existing databases that make heavy use of stored procedures and for those that want to follow the best practice of using stored procedures within database, you will find that LINQ is still a viable option.

LINQ to SQL treats working with stored procedures as a method call. As you saw in Figure 27-4, there a design surface called the O/R Designer that allows you to drag and drop tables onto it so that you can then programmatically work with the table, On the right side of the O/R Designer, you will find a spot where you are able to drag and drop stored procedures.

Any stored procedures that you drag and drop onto this part of the O/R Designer will no available methods to you from DataConcext object, For this example, drag and drop the TenMostExpensiveProducts stored procedure onto this part of the O/R Designer.

The following example shows how you would call this stored procedure within the Northwind database:

From this example, you can see that the rows coming out of the stored procedure are collected into an ISingleResult <ten_Most_Expensive_ProductsResult> object, From here, iteration through this object is as simple as all the rest.

As you can see from this example, calling your stored procedures is a simple process.

Posted on October 31, 2015 in LINQ to SQL

Share the Story

Back to Top