Introduction

This article provides an introduction to employing LINQ to SQL within a Windows Forms application; the article will address the incorporation of LINQ to SQL into a win forms project, how to use LINQ to SQL to select, insert, update, and delete data, and how to use LINQ to SQL to execute stored procedures. Select query examples will demonstrate ordering, filtering, aggregation, returning typed lists, returning single objects and values, and how to query across entity sets (which are essentially related tables associated by foreign keys).

Figure 1: Application Main Form

The demonstration project included with the article is a simple win forms application; this example contains a datagridview control and a menu; the menu is used to execute each example query contained in the demonstration. The application provides the following functionality:

Return Full Tables

Return Typed Lists

Return Single Typed Values

Insert Data

Update Data

Delete Data

Execute Stored Procedures

Select Filtered Lists

Select Ordered Lists

Perform Aggregate Functions

There is a great deal more that one can do with LINQ to SQL that is not contained in this demonstration however, the demonstration was geared towards the mechanics of performing the most typical types of queries that might be required within a data driven application.

LINQ to SQL Statements

This section will discuss some of the common techniques used in LINQ to SQL statement construction. In a nutshell, LINQ to SQL provides the developer with the means to conduct queries against a relational database through a LINQ to SQL database model and related data context.

Data Context

The data context provides the mapping of all entities (essentially tables) to the database. It is through the data context that the application can query the database and it is through the data context that changes to the database can be executed.

Anatomy of LINQ to SQL Statements

Example 1 – A Simple Select

This is an example of a very simple LINQ to SQL statement:

PublicSub SampleQ1()

Dim dc AsNew NwindDataClassesDataContext()

Dim q = _ From a In dc.GetTable(Of Order)() _Select a

DataGridView1.DataSource = q

EndSub

In the example, an instance of the data context is created and then a query is formed to get all of the values in the table; once the query runs, the result is used as the data source of a datagridview control and the results are displayed in the grid:

Dim q = _ From a In dc.GetTable(Of Order)() _Select a

Since the GetTable function in the data context returns the entire table, this query is pretty useless but it does work and it is representative of a simple select query. You could accomplish the same task using this code:

PublicSub SampleQ2()

Dim dc AsNew NwindDataClassesDataContext()

DataGridView1.DataSource = dc.GetTable(Of Order)()

EndSub

If you were to create a project, add either bit of code to a method and run it, the results would look like this:

Figure 2: Query Results

Example 2 – Select with a Where Clause

The next example shows a LINQ to SQL query that incorporates a where clause. In this example, we get a data context to work with first, and then query the Orders table to find a customer with the customer ID of starts with the letter “A”, the results are then bound to a datagridview control.

PublicSub SimpleQ3()

Dim dc AsNew NwindDataClassesDataContext()

Dim q = _ From a In dc.GetTable(Of Order)() _ Where a.CustomerID.StartsWith("A") _Select a

DataGridView1.DataSource = q

EndSub

If you were to run the query, the results would appear as follows:

Figure 3: Query Results

Example 3 – Select with a Where Clause

In a slight variation to the previous query, this example looks for an exact match in its where clause:

PublicSub SimpleQ4()

Dim dc AsNew NwindDataClassesDataContext()

Dim q = _ From a In dc.GetTable(Of Order)() _ Where a.CustomerID = "VINET" _Select a

DataGridView1.DataSource = q

EndSub

Running this code will display this result:

Figure 4: Query Results

Example 4 – Generating an Ordered List

In this query, the list of orders is ordered (using “orderby a.OrderDate ascending”):

PublicSub SimpleQ5()

Dim dc AsNew NwindDataClassesDataContext()

Dim q = _ From a In dc.GetTable(Of Order)() _ Where a.CustomerID.StartsWith("A") _ Order By a.OrderDate Ascending _Select a

DataGridView1.DataSource = q

EndSub

Figure 5: Query Results

Example 5 – Working with a Custom Type

In this example a query is built to return a list of a custom type (CustomerOrderResult).

The “select new” in the query defines the result type and then sets each of the properties in the type to a value returned by the query. At the end of the query, the output is converted to a List of the CustomerOrderResult type.

The displayed results of running the query are:

Figure 6: Query Results

The CustomerOrderResult class used in as the type behind the parts list is as follows:

In this example, a typed list is created (as in the previous example) using LINQ to SQL, populated, and then the returned typed list is queried using LINQ to Objects. In this case, the query includes a where clause that only returns matches were the customer ID begins is equal to “RICAR”:

The preceding examples were intended to provide a simple overview as to how to conduct some basic queries against collections using LINQ to SQL and LINQ to Objects; there are certainly a great number of more complex operations that can be executed using similar procedures (groups and aggregation, joins, etc.) however, the examples provided are representative of some of the more common types of queries.

Getting Started

There is a single solution included with this download, the solution contains a Win Forms project called “L2S_Northwind_VB”; this project contains one form (the main form used to display the results of the demonstration queries (frmMain) , and LINQ to SQL database model (NorthwindDataClasses.dbml) along with the models designer code and layout file, and a class entitled, “Accessor” which contains code used to perform the LINQ to SQL queries used in the demonstration.

If you open the attached project into Visual Studio 2008, you should see the following in the solution explorer:

Figure 9: Solution Explorer

The demonstration relies upon an instance of the Northwind database running in SQL Server 2005. The database can be downloaded from here; the database was created for SQL Server 2000 but you can install the database and attach to it using a local instance of SQL Server 2005.

Once the database is installed, you will want to update the connection string found in the “MyProject” settings. Open the settings and click on the button (showing an ellipsis) to set the connection string.

Figure 10: Settings and the Connection String

Figure 11: Adding LINQ to SQL Classes to a Project

When starting from scratch, in order to add LINQ to SQL to a project, open the “Add New Item” dialog and select the LINQ to SQL Classes item (Figure 11); name the data classes and then select the “Add” button to add the item to the project. Once set, set the connection string for the data classes, then open the server explorer to drag tables and stored procedures onto the designer (dragging the tables into the left hand side of the workspace and stored procedures into the right hand side of the workspace (Figure 12)). Once that is done, build the project to generate the LINQ to SQL code.

This project is intended for Visual Studio 2008 with the .NET framework version 3.5.

Code: Accessor.vb

The Accessor class is used to the store all of the functions used to execute LINQ to SQL queries against the database. The functions contained in this class are all static and include a mixed bag of selects, inserts, updates, deletes, and stored procedure evocations. You may wish to implement some business logic in the extensibility methods defined in the auto-generated code contained in the designer file but I chose not to in this example.

'''<summary>''' This class defines functions used to''' select, insert, update, and delete data''' using LINQ to SQL and the defined''' data context'''</summary>'''<remarks></remarks>PublicClass Accessor

Next up is a region containing all of the functions used to return full tables from the database through the data context. All of the functions work essentially the same way; the data context includes a function called GetTable (Of Type) which is used to return a table of the indicated type. Each example gets a data context and then evokes the GetTable function to return the full table of the indicated type.

That next region contained in the Accessor class is Queries region; this region contains examples of different types of select queries that may be performed using LINQ to SQL. Each query is described in the annotation:

Collapse

#Region"Queries"

' This region contains examples of some' of the sorts of queries that can be' executed using LINQ to SQL

'''<summary>''' Example: Where Clause''' Returns an employee where the''' employee ID matches the value''' passed in as empID'''</summary>'''<paramname="empId"></param>'''<returns></returns>'''<remarks></remarks>PublicSharedFunction GetEmployeeById(ByVal empId AsInteger) As Employee

' get the data contextDim dc AsNew NorthwindDataClassesDataContext()

' get the first Employee with and employee ID' matching the employee ID passed in as an' argument to this functionDim retVal = (From e In dc.GetTable(Of Employee)() _ Where (e.EmployeeID = empId) _Select e).FirstOrDefault()

Return retVal

EndFunction

'''<summary>''' Example: Select to a single returned object''' using a Where Clause'''''' Returns the first matching order'''</summary>'''<paramname="ordId"></param>'''<returns></returns>'''<remarks></remarks>PublicSharedFunction GetOrderById(ByVal ordId AsInteger) As Order

' get the data contextDim dc AsNew NorthwindDataClassesDataContext()

' return a single value from the orders table' where the order Id match the ordId argument' passed to this functionDim retVal = (From ord In dc.GetTable(Of Order)() _ Where (ord.OrderID = ordId) _Select ord).FirstOrDefault()

Return retVal

EndFunction

'''<summary>''' Example: Select to a typed List''' using a Where Clause'''</summary>'''<paramname="ordId"></param>'''<returns></returns>'''<remarks></remarks>PublicSharedFunction GetOrdersById(ByVal ordId AsInteger) As List(Of Order)

' get the contextDim dc AsNew NorthwindDataClassesDataContext()

' get a list of Orders where the Order ID matches' the ordId argument and return the collection as' a list of type OrderDim retVal = (From ord In dc.GetTable(Of Order)() _ Where (ord.OrderID = ordId) _Select ord).ToList()

Return retVal

EndFunction

'''<summary>''' Example: Return an ordered list'''''' Converts the returned value to a List''' of type Employee; the list is ordered''' by hire date'''</summary>'''<returns></returns>'''<remarks></remarks>PublicSharedFunction GetEmployeesByHireDate() As List(Of Employee)

' get the contextDim dc AsNew NorthwindDataClassesDataContext()

' get the Employee table, order it by HireDate' and return the result as a list of type EmployeeDim retVal = (From emp In dc.GetTable(Of Employee)() _ Order By emp.HireDate Ascending _Select emp).ToList()

Return retVal

EndFunction

'''<summary>''' This class is used to define the return type''' for the next function - OrdersAndDetails'''''' When results are extracted from multiple tables''' you can either return the results as anonymous''' or as a type; this class defines the return''' type used by OrdersAndDetails'''</summary>'''<remarks></remarks>PublicClass OrdersAndDetailsResult

'''<summary>''' Example: Joins''' Joining using the join keyword'''''' The values are set to each of the''' properties contained in the''' OrdersAndDetailsResult class'''''' The value returned is converted''' to a list of the specified type'''</summary>'''<returns></returns>'''<remarks></remarks>PublicSharedFunction OrdersAndDetails() As List(Of OrdersAndDetailsResult)

'''<summary>''' Defined to support following function:''' GetOrderAndPricingInformation - this class''' supplies the return type for that function'''</summary>'''<remarks></remarks>PublicClass OrderAndPricingResult

'''<summary>''' Example: Query across an entity ref''' This example collections information from the orders table''' and the order_details table through the orders table''' entity association to the orders_details table. '''''' An entity is a representation in the model of a table''' in the database, foreign key relationships are maintained''' as entity references to the related tables in the model.''' It is possible to query across tables through this''' relationship in LINQ to SQL'''</summary>'''<returns></returns>'''<remarks></remarks>PublicSharedFunction GetOrderAndPricingInformation() As List(Of OrderAndPricingResult)

'''<summary>''' Example: Query across entity ref with Where class''' Same as previous function with added where clause'''''' An entity is a representation in the model of a table''' in the database, foreign key relationships are maintained''' as entity references to the related tables in the model.''' It is possible to query across tables through this''' relationship in LINQ to SQL'''</summary>'''<paramname="ordId"></param>'''<returns></returns>'''<remarks></remarks>PublicSharedFunction GetOrderAndPricingInformationByOrderId(ByVal ordId AsInteger) As List(Of OrderAndPricingResult)

'''<summary>''' Example: Aggregation'''''' Returns the total sum of the order''' selected by order ID by selecting''' unit price multiplied by quantity''' ordered and then calling sum for''' the total'''</summary>'''<paramname="orderId"></param>'''<returns></returns>'''<remarks></remarks>PublicSharedFunction GetOrderValueByOrderId(ByVal orderId AsInteger)As Decimal?

' get the data contextDim dc AsNew NorthwindDataClassesDataContext()

' get the order with a matching order ID and then' multiply the unit price by the quantity, when' all matching order items have been calculated' individually into a collection, sum the total of' that collection and return the valueDim rtnVal = (From od In dc.GetTable(Of Order_Detail)() _ Where od.OrderID = orderId _Select (od.Product.UnitPrice * od.Quantity)).Sum()

Return rtnVal

EndFunction

'''<summary>''' Example: Using Take to get a limited''' number of returned values for display and''' using Skip to sequence to a different''' starting point within the returned values -''' can be used to navigate through a large''' list'''</summary>'''<paramname="SkipNumber"></param>'''<returns></returns>'''<remarks></remarks>PublicSharedFunction GetTopFiveOrdersById(ByVal SkipNumber AsInteger)

' get the data contextDim dc AsNew NorthwindDataClassesDataContext()

' order the table by Order ID' and then skip down the SkipNumber of records and' take the next file records, covert that to' a list and return itDim rtnVal = (From ord In dc.GetTable(Of Order)() _ Order By ord.OrderID Ascending _Select ord).Skip(SkipNumber).Take(5).ToList()

Return rtnVal

EndFunction

#End Region

The next region is “Insert Update Delete”; it contains examples of how to insert or update data, and an example showing how to delete data from the database. Each function is described in its annotation:

'''<summary>''' Insert a customer if the customer does not exist, or''' update the customer if it does exist'''</summary>'''<paramname="customerId"></param>'''<paramname="companyName"></param>'''<paramname="contactName"></param>'''<paramname="contactTitle"></param>'''<paramname="address"></param>'''<paramname="city"></param>'''<paramname="region"></param>'''<paramname="postalCode"></param>'''<paramname="country"></param>'''<paramname="phone"></param>'''<paramname="fax"></param>'''<remarks></remarks>PublicSharedSub InsertOrUpdateCustomer(ByVal customerId As String, _ByVal companyName As String, _ByVal contactName As String, _ByVal contactTitle As String, _ByVal address As String, _ByVal city As String, _ByVal region As String, _ByVal postalCode As String, _ByVal country As String, _ByVal phone As String, _ByVal fax AsString)

The last region of the class contains the code used to execute stored procedures. The stored procedures, once added to the project may be immediately accessed through the data context; to access a stored procedure just get an instance of the data context and call the stored procedure, passing along any required parameters as arguments to the function call:

Code: Main Application Form (frmMain.vb)

This is the main form of the application; this form is used to provide a test harness for testing each of the functions defined in the Accessor class; all functions defined in the Accessor class have a corresponding menu item and the click event handler for each menu item executes an Accessor class function; supplying any arguments necessary as canned values.

'''<summary>''' Demonstration Application - this form''' class is used to test each of the functions''' and subroutines defined in the Accessor''' class'''</summary>'''<remarks></remarks>PublicClass frmMain

Next is the definition of a private variable used to maintain the position within the orders table; it used in an example showing how to make use of the Skip and Take functions.

' used to support take/skip examplePrivate OrderPosition AsInteger

The next region of code in the class contains the constructor. The constructor sets the Order Position integer value to zero.

#Region"Constructor"

PublicSubNew()

' This call is required by the Windows Form Designer. InitializeComponent()

The next code region is called ‘Full Table Requests’. Each of the functions operates in a similar manner in that the function creates a list of the type returned by the Accessor class function called, evokes the Accessor function and then assigns the returned list to the data source property of the datagridview control contained in the main form.

The next region contains the menu item click event handlers used to execute each of the queries described in the queries region of the Accessor class. Each function is annotated to describe what it does and what it is intended to demonstrate.

EndSub'''<summary>''' Gets an Order by the order ID and''' displays information about the first''' single matching order.'''</summary>'''<paramname="sender"></param>'''<paramname="e"></param>'''<remarks></remarks>PrivateSub orderByIDToolStripMenuItem_Click( _ByVal sender As System.Object, _ByVal e As System.EventArgs) _Handles orderByIDToolStripMenuItem.Click

EndSub'''<summary>''' Retrieves values across an entity set to ''' display both order and pricing information ''' by filtering for an order ID'''</summary>'''<paramname="sender"></param>'''<paramname="e"></param>'''<remarks></remarks>PrivateSub ordersAndDetailsByOrderIDEntityRefToolStripMenuItem_Click( _ByVal sender As System.Object, _ByVal e As System.EventArgs) _Handles ordersAndDetailsByOrderIDEntityRefToolStripMenuItem.Click

EndSub'''<summary>''' Displays to total dollar value of the selected order''' by multiplying each order product's unit cost by''' the units ordered, and then summing the total of each''' individual cost.'''</summary>'''<paramname="sender"></param>'''<paramname="e"></param>'''<remarks></remarks>PrivateSub orderValueByOrderIDToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles orderValueByOrderIDToolStripMenuItem.Click

' get the dollar valueDim d As Decimal? = Accessor.GetOrderValueByOrderId(10248)

' display the dollar value MessageBox.Show("The total dollar value of order 10248 is " & _ dollarValue, "Order 10248 Value")EndSub'''<summary>''' Displays the top five orders in the order table''' on first selection and then increments up by''' five orders to show the list five orders''' at a time'''</summary>'''<paramname="sender"></param>'''<paramname="e"></param>'''<remarks></remarks>PrivateSub getTopFiveOrdersToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles getTopFiveOrdersToolStripMenuItem.Click

Try' get the top five orders starting at the current position dataGridView1.DataSource = Accessor.GetTopFiveOrdersById(OrderPosition)

' increment the formwide variable used to' keep track of the position within the ' list of orders OrderPosition += 5

' change the text in the menu strip item' to show that it will retrieve the next' five values after the current position ' of the last value shown in the grid getTopFiveOrdersToolStripMenuItem.Text = "Get Next Five Orders"

The next region contains methods used to insert, update, or delete data from the database; these click event handlers evoke the corresponding functions contained in the Accessor class:

#Region"Insert Update Delete"

The Insert or Update Customer menu item click event handler calls the Accessor class Insert or Update Customer function, passing in some canned arguments to populate the last. If you look at the customer table before and after executing this click event handler you will see the customer added to the table.

The Delete Customer menu item click event handler is used to delete the customer created by running the previous function; again, checking the table before and after running this click event handler will allow you to see the added customer deleted from the table.

The next region in this class is used to execute a couple of the stored procedures made available through the data context.

Collapse

#Region"Stored Procedures"

'''<summary>''' Execute the Sales by Year stored''' procedure and display the results''' in the datagrid'''</summary>'''<paramname="sender"></param>'''<paramname="e"></param>'''<remarks></remarks>PrivateSub salesByYearToolStripMenuItem_Click( _ByVal sender As System.Object, _ByVal e As System.EventArgs) _Handles salesByYearToolStripMenuItem.Click

Summary

The article shows some simple examples of LINQ to SQL; from it you can see how easy it is to query against single and related tables and to write filtered queries, execute stored procedures, perform aggregation, and how to insert, update, and delete records from the database.