SQL stands for Structured Query Language, a language for querying databases. SQL is discussed in detail in Chapter “Basic Concepts of Relational Databases”, and as you will see, SQL resembles LINQ. If you are not familiar with databases and SQL, you should read Chapter “Basic Concepts of Relational Databases” and then return to this section. SQL is a simple language, and I will explain the SQL statements used in this section’s examples; readers who are somewhat familiar with databases should be able to follow the examples of this section.
In this section, we’re going to build an application for displaying customers, orders, and order details, just as we did in the preceding section. The difference is that this time we won’t get our data from an XML document; we’ll retrieve them directly from the database. As you will see, the same LINQ queries will be used to process the rows returned by the queries. The code you’ll see in this section comes from the frmDB form of the VBLINQ sample project. This form is identical to the frmXMLFiles form shown in Figure 13.2; it just uses a different data source. The code isn’t identical to the code presented in the preceding section, but the differences are minor. The same principles will be applied to a very different data source.
We need a mechanism to connect to the database so we can retrieve data, and this mechanism is the DataContext class. The DataContext class talks to the database, retrieves data, and submits changes back to the database. To create a DataContext object, pass a string with the information about the database server, the specific database, and your credentials to the DataContext class’s constructor, as shown here:
Dim db As New DataContext("Data Source=localhost;
initial catalog=northwind;
Integrated Security=True")
Code language: PHP (php)
To use the DataContext class in your code, you must add a reference to the System.Data.Linq namespace and then import it into your code with this statement:
Imports System.Data.Linq
Code language: CSS (css)
You will find more information on connecting to databases in Chapter “Programming with ADO.NET”. For the purposes of this chapter, the preceding connection string will connect your application to the Northwind database on the local database server, assuming that you have installed SQL Server or SQL Server Express on the same machine as Visual Studio.
After you have initialized the DataContext object, you’re ready to read data from tables into variables. To do so, call the GetTable method of the db object to retrieve the rows of a table. Note that the name of the table is not specified as an argument. Instead, the table is inferred from the type passed to the GetTable method as an argument. The GetTable(Of Customer) method will retrieve the rows of the Customers table, because the name of the table is specified in the definition of the class, as you will see shortly.
customers = From cust In db.GetTable(Of Customer)() _
Select New Customer With _
{.CustomerID = cust.CustomerID, _
.CompanyName = cust.CompanyName, _
.ContactName = cust.ContactName, _
.ContactTitle = cust.ContactTitle}
orders = From ord In db.GetTable(Of Order)() _
Select New Order With _
{.OrderID = ord.OrderID, _
.OrderDate = ord.OrderDate, _
.CustomerID = ord.CustomerID, _
.Freight = ord.Freight, _
.ShipName = ord.ShipName}
details = From det In db.GetTable(Of Detail)() _
Select New Detail With _
{.OrderID = det.OrderID, _
.ProductID = det.ProductID, _
.Quantity = det.Quantity, _
.UnitPrice = det.UnitPrice, _
.Discount = det.Discount}
products = From prod In db.GetTable(Of NWProduct)() _
Select New NWProduct With _
{.ProductID = prod.ProductID, _
.ProductName = prod.ProductName}
Code language: PHP (php)
The type of the customers, orders, details, and products variables is IQueryable(of entity), where entity is the appropriate type for the information you’re reading from the database. The four variables that will store the rows of the corresponding tables must be declared at the form’s level with the following statements:
Dim customers As System.Linq.IQueryable(Of Customer)
Dim orders As System.Linq.IQueryable(Of Order)
Dim details As System.Linq.IQueryable(Of Detail)
Dim products As System.Linq.IQueryable(Of NWProduct)
Code language: CSS (css)
The variables must be declared explicitly at the form’s level, because they will be accessed from within multiple event handlers.
To make the most of LINQ to SQL, you must first design a separate class for each table that you want to load from the database. You can also specify the mapping between your classes and the tables from which their instances will be loaded, by prefixing them with the appropriate attributes. The Customer class, for example, will be loaded with data from the Customers table. To specify the relationship between the class and the table, use the Table attribute, as shown here:
<Table(Name:="Customers")>Public Class Customer
End Class
Code language: PHP (php)
Each property of the Customer class will be mapped to a column of the Customers table.
In a similar manner, decorate each property with the name of the column that will populate the property:
<Column(Name:="CompanyName")>Public Property Name
End Property
Code language: PHP (php)
If the name of the property matches the name of the relevant column, you can omit the column’s name:
<Column()>Public Property Name
End Property
Code language: HTML, XML (xml)
Listing 13.4 shows the definition of the four classes we’ll use to store the four tables (Customers, Orders, Order Details and Products).
Listing 13.4: The Classes for Storing Customers and Orders
<Table(Name:="Customers")> Public Class Customer
Private _CustomerID As String
Private _CompanyName As String
Private _ContactName As String
Private _ContactTitle As String
<Column()> Public Property CustomerID() As String
Get
Return _customerID
End Get
Set(ByVal value As String)
_customerID = value
End Set
End Property
<Column()> Public Property CompanyName() As String
Get
Return _CompanyName
End Get
Set(ByVal value As String)
_CompanyName = value
End Set
End Property
<Column()> Public Property ContactName() As String
Get
Return _ContactName
End Get
Set(ByVal value As String)
_ContactName = value
End Set
End Property
<Column()> Public Property ContactTitle() As String
Get
Return _ContactTitle
End Get
Set(ByVal value As String)
_ContactTitle = value
End Set
End Property
End Class
<Table(Name:="Orders")> Public Class Order
Private _OrderID As Integer
Private _CustomerID As String
Private _OrderDate As Date
Private _Freight As Decimal
Private _ShipName As String
<Column()> Public Property OrderID() As Integer
....
End Property
<Column()> Public Property CustomerID() As String
....
End Property
<Column()> Public Property OrderDate() As Date
....
End Property
<Column()> Public Property Freight() As Decimal
....
End Property
<Column()> Public Property ShipName() As String
....
End Property
End Class
<Table(Name:="Order Details")> Public Class Detail
Private OrderID As Integer
Private ProductID As Integer
Private Quantity As Integer
Private UnitPrice As Decimal
Private Discount As Decimal
<Column()> Public Property OrderID() As Integer
....
End Property
<Column()> Public Property ProductID() As Integer
....
End Property
<Column()> Public Property Quantity() As Short
....
End Property
<Column()> Public Property UnitPrice() As Decimal
....
End Property
<Column()> Public Property Discount() As Double
....
End Property
End Class
<Table(Name:="roducts")> Public Class NWProduct
Private _ProductID As Integer
Private _ProductName As String
<Column()> Public Property ProductID() As Integer
....
End Property
<Column()> Public Property ProductName() As String
....
End Property
End Class
Code language: JavaScript (javascript)
I’m not showing the implementation of most properties, because it’s trivial. What’s interesting in this listing are the Table and Column attributes that determine how the instances of the classes will be populated from the database, as you saw earlier.
The code that displays the selected customer’s orders and the selected order’s details is similar to the code you saw in the previous section that displays the data from the XML files. It selects the matching rows in the relevant table and shows them in the corresponding ListView control.
Retrieving Data with the ExecuteQuery Method
You can also retrieve a subset of the table by executing an SQL query against the database. The ExecuteQuery method, which accepts as arguments the SELECT statement to be executed and an array with parameter values, returns a collection with the selected rows as objects. To call the ExecuteQuery method, you must specify the class that will be used to store the results with the Of keyword in parentheses following the method’s name. Then you specify the SELECT statement that will retrieve the desired rows. If this query contains any parameters, you must also supply an array of objects with the parameter values. Parameters are identified by their order in the query, and not a name. The first parameters is 0, the second parameter is 1, and so on. The following statement will retrieve all customers from Germany and store them in instances of the Customer class:
Dim params() = {"Germany"}
Dim GermanCustomers = _
db.ExecuteQuery(Of Customer)( _
"SELECT CustomerID, CompanyName, " & _
"ContactName, ContactTitle " & _
"FROM Customers WHERE Country={0}", params)"
Code language: JavaScript (javascript)
After the GermanCustomers collection has been populated, you can iterate through its items as usual, with a loop like the following:
For Each cust In GermanCustomers
Debug.WriteLine(cust.CompanyName & " " & _
cust.ContactName)
Next
Code language: CSS (css)
You can also execute LINQ queries against it. To find out the number of customers from Germany, use the following expression:
Dim custCount = GermanCustomers.Count
To apply a filtering expression and then retrieve the count, use the following LINQ expression:
Dim g = GermanCustomers.Where(Function(c As Customer) _
c.CompanyName.ToUpper Like "*DELIKATESSEN*").Count
Code language: JavaScript (javascript)
To appreciate the role of the DataContext class in LINQ to SQL, you should examine the ToString property of a LINQ query that’s executed against the database. Insert a statement to display the expression GermanCustomers.ToString in your code and you will see that the DataContext class has generated and executed the following statement against the database. If you’re familiar with SQL Server, you can run the SQL Server Profiler and trace all commands executed against SQL Server. Start SQL Server Profiler (or ask the database administrator to create a log of all statements executed by your workstation against a specific database) and then execute a few LINQ to SQL queries. Here’s the statement for selecting the German customers as reported by the profiler:
exec sp executesql N'SELECT Customers.CompanyName,
Orders.OrderID, SUM(UnitPrice*Quantity) AS
OrderTotal FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
INNER JOIN [Order Details] ON
[Order Details].OrderID = Orders.OrderID
WHERE Customers.Country=@p0
GROUP BY Customers.CompanyName,
Orders.OrderID',N'@p0 nvarchar(7)',@p0=N'Germany'
Code language: PHP (php)