4 Jun 2008CPOL
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 WinForms project, how to use LINQ to SQL to select, insert, update, and delete data, and how to use LINQ to SQL to execute stored proced
See also Advanced Querying Techniques later in this book. LINQ Expressions. LINQ allows you to write queries of arbitrary complexity. LightSpeed handles only queries that can be translated to SQL on the database at hand. Consequently, if you write complex queries, you may encounter NotSupportedException at runtime. LINQ to SQL (Part 3 - Querying our Database). Creating and executing the appropriate SQL code for you when you perform a query or update against their object model. Hope this article will be helpful for you for writing simple linq to sql query thus help doing basic database operations. I will be writing on some more advance level linq to sql usage soon. To be updated, keep in touch. LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the.NET Framework 'Orcas' release, and which allows you to model a relational database using.NET classes. You can then query the database using LINQ, as well as update/insert/delete data from it. LINQ to SQL fully supports transactions, views, and stored procedures. Recently, I have discussed about getting started linq to sql in c#.net. That articles also covered for creating select query for retrieving both single and multiple results. Today, in this tutorial, I will try to give some more examples on other types of database operations, that writes/updates the database. I'm doing a project with LINQ to SQL. I'm mapping the database in code. I have 2 tables: Cargo(CodCargo PK, Cargo) Utilizador(CodUtilizador PK, Utilizador, CodCargo FK.
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 WinForms 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).
The demonstration project included with the article is a simple WinForms 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:
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: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: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 starting with the letter “A”; the results are then bound to a DataGridView
control.If you were to run the query, the results would appear as follows:
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: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
”):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 as the type behind the list is as follows:Example 6 – Searching an Existing List<T> Using LINQ to Objects
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 with “RICAR”:Example 7 – Searching an Existing List<T> Using LINQ to Objects and Returning a Single Result
In this example, a typed list is created (as in the previous example), populated, and then queried using LINQ to Objects. In this case, a single result of type “
Parts
” is returned:The results are displayed as:
Figure 8: Returning a Single Result
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 WinForms project called “LINQSQLCS”; this project contains a form - the main form used to display the results of the demonstration queries (
frmMain
), a LINQ to SQL database model (NorthWindDataClasses.dbml) along with the models designer code and the layout file, and a class, “Accessor
”, which contains the 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:
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 project 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, and 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.
Figure 12: Model of the Northwind Data Class (tables on left, stored procedures on right)
This project is intended for Visual Studio 2008 with the .NET framework version 3.5.
Code: Accessor.cs
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.The class begins with the normal and default imports:
The next section contains the namespace and class declarations.
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<T>
, 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 the 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:The next region is “Inserting, Updating, and Deleting Data”; 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:
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:
That concludes the description of the “
Accessor
” class.Code: Main Application Form (frmMain.cs)
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.Get Sql From Linq
The structure for the main form’s menu is as follows:
- Menu
- File
- Exit
- Read
- Tables
- Queries
- Stored Procedures
- Insert/Update/Delete
Linq In Sql Server
The class begins with the normal and default imports:
The next section contains the namespace and class declarations.
Next is the definition of a private variable used to maintain the position within the orders table; it is used in an example showing how to make use of the
Skip
and Take
functions.The next region of code in the class contains the constructor. The constructor sets the
OrderPosition
integer value to zero.The next code region is called ‘Full Table Requests’. Each of the functions operates in a similar manner in that a 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.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:Querying A Database With Linq To Sql Updates
The Insert or Update Customer menu item
Click
event handler calls the Accessor
class InsertOrUpdateCustomer
function, passing in some canned arguments to populate the list. 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 final region in this class is used to execute a couple of the stored procedures made available through the data context.
The last region contained in the class is the Housekeeping region; in this region, there is only one
Click
event handler which is used to exit the application.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.
-->Language-Integrated Query (LINQ) queries make it easy to access database information and modify values in the database.
The following example shows how to create a new application that retrieves and updates information in a SQL Server database.
The examples in this topic use the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases.
To create a connection to a database
- In Visual Studio, open Server Explorer/Database Explorer by clicking the View menu, and then select Server Explorer/Database Explorer.
- Right-click Data Connections in Server Explorer/Database Explorer, and click Add Connection.
- Specify a valid connection to the Northwind sample database.
To add a Project with a LINQ to SQL file
- In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.
- On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.
- Name the file
northwind.dbml
. Click Add. The Object Relational Designer (O/R Designer) is opened for thenorthwind.dbml
file.
C# Linq To Sql Query
To add tables to query and modify to the designer
- In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Tables folder.If you have closed the O/R Designer, you can reopen it by double-clicking the
northwind.dbml
file that you added earlier. - Click the Customers table and drag it to the left pane of the designer.The designer creates a new Customer object for your project.
- Save your changes and close the designer.
- Save your project.
To add code to modify the database and display the results
- From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.
- When you added tables to the O/R Designer, the designer added a DataContext object to your project. This object contains code that you can use to access the Customers table. It also contains code that defines a local Customer object and a Customers collection for the table. The DataContext object for your project is named based on the name of your .dbml file. For this project, the DataContext object is named
northwindDataContext
.You can create an instance of the DataContext object in your code and query and modify the Customers collection specified by the O/R Designer. Changes that you make to the Customers collection are not reflected in the database until you submit them by calling the SubmitChanges method of the DataContext object.Double-click the Windows Form, Form1, to add code to the Load event to query the Customers table that is exposed as a property of your DataContext. Add the following code: - From the Toolbox, drag three Button controls onto the form. Select the first
Button
control. In the Properties window, set theName
of theButton
control toAddButton
and theText
toAdd
. Select the second button and set theName
property toUpdateButton
and theText
property toUpdate
. Select the third button and set theName
property toDeleteButton
and theText
property toDelete
. - Double-click the Add button to add code to its
Click
event. Add the following code: - Double-click the Update button to add code to its
Click
event. Add the following code: - Double-click the Delete button to add code to its
Click
event. Add the following code: - Press F5 to run your project. Click Add to add a new record. Click Update to modify the new record. Click Delete to delete the new record.