You have almost certainly heard of LINQ by now: Language INtegrated Query. It's the new technology supported by Visual Studio 2008 and the C# and VB.NET languages, introduced in .NET 3.5 and C# 3.0 (it is supported in VB.NET, as well, but my examples all use C#). Perhaps you have even used it to retrieve and update data in your database tables.
But did you know that LINQ has support for calling Stored Procedures as well? This article describes the steps to take for calling a Stored Procedure from LINQ. The first part of the article sets up the project. If you have a project already and want to skip to the LINQ portion, click here.
In This Article
Setting Up The Project
Adding the Stored Procedure to the Design Surface
Populating the Combo Box
Calling the Stored Procedure
What's the Big Deal?
How LINQ Handles Stored Procedure Parameters
How Else Can I Use Stored Procedures?
Conclusion
Setting Up The Project
For this example I'll create a new WinForm project and add a Combo Box to select different managers and a Data Grid View to hold the results:
Before you can select data, you need a connection to your database. To do this, click on the Server Explorer, right-click "Data Connections" and select "Add Connection...". I'm using the AdventureWorks database which can be installed with SQL Server. The database is on the same machine that I'm developing on, so "localhost" works as the host name.
Once the data connection is created a DBML file must be added to the project for LINQ support. To do this, right-click on the project name and choose "Add", then "New Item...". In the "Add New Item" dialog box, select "LINQ to SQL Classes". Provide a file name and then click "Add" (I'm using the default name of "DataClasses1"). The LINQ to SQL design surface is displayed.
Adding the Stored Procedure to the Design Surface
From the Server Explorer, expand the data connection, then expand the "Stored Procedures" node to get a list of stored procedures. Drag "uspGetManagerEmployees" and drop it onto the design surface. If you don't see anything happen, right-click the design surface and select "Show Methods Pane". You should now see that the stored procedure has been added to the design surface (though it doesn't appear as a table; rather it looks like a function call). The following image shows the stored procedure in the Methods Pane, as well as the Contact and Employee tables in the main design pane, which I added in order to populate the combo box in the UI:
Populating the Combo Box
Before we can call the stored procedure, which is the focus of this article, we need to get some data onto the form to work with. For this example, we'll load manager names into the combo box. When a manager is selected in the combo box we'll then call the stored procedure to select the Employees who report to the manager.
The following code selects the manager names and loads them into the combo box:
1: protected void LoadManagers()
2: {3: DataClasses1DataContext dc = new DataClasses1DataContext();
4: 5: // Retrieve the managers in the system.
6: // A manager is defined here as having at least one
7: // Employee under him or her.
8: // The result is a collection of EmployeeData instances.
9: var managers = (from m in dc.Employees
10: where m.Employees.Count() > 0
11: select new EmployeeData
12: {13: Name = string.Format("{0}, {1}",
14: m.Contact.LastName, 15: m.Contact.FirstName), 16: ID = m.EmployeeID 17: }).Distinct(); 18: 19: comboBox1.Items.Clear(); 20: 21: // Set the datasource to the collection retrieved above
22: comboBox1.DataSource = managers; 23: 24: // The data-binding information comes from the projected type, above
25: comboBox1.DisplayMember = "Name";
26: comboBox1.ValueMember = "ID";
27: }Line 3 creates the data context, lines 9 through 17 retrieves data from the database, and lines 19 through 26 fill the combo box with the data.
Calling the Stored Procedure
When the selection in the combo box changes, the new manager's employees should be displayed in the data view grid. This code in the combo box's Selected Index Changed event handled that:
1: private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
2: {3: if (comboBox1.SelectedIndex != -1)
4: {5: // Selected item is an instance of EmployeeData
6: EmployeeData manager = comboBox1.SelectedItem as EmployeeData;
7: 8: if (manager != null)
9: {10: DataClasses1DataContext dc = new DataClasses1DataContext();
11: 12: // Call the stored procedure to get the Employees
13: var employees = from e in
14: dc.uspGetManagerEmployees(manager.ID)15: select new EmployeeData
16: {17: Name = string.Format("{0}, {1}",
18: e.LastName, 19: e.FirstName), 20: ID = e.EmployeeID ?? 0 21: }; 22: 23: // Populate the grid with the results
24: dataGridView1.AutoGenerateColumns = true;
25: BindingSource bindingSource1 = new BindingSource();
26: bindingSource1.DataSource = employees; 27: dataGridView1.DataSource = bindingSource1; 28: } 29: } 30: }Since a collection of EmployeeData instances made up the data source for the combo box, then the SelectedItem property will have an instance of EmployeeData we can cast to (line 6). We use it to get the manager ID, which is then fed into the stored procedure (line 14). We then send the results to the data view grid (lines 24 through 27). When the program runs you'll see something similar to this:
What's the Big Deal?
This is so simple that you may be asking yourself what the big deal is, and I don't blame you. But I didn't show you all of the magic, either.
Let's explore what happens when the stored procedure is dropped onto the design surface. A function is added to the DataContext class (found in DataClasses1.designer.cs), which looks like so (maximize your browser to see the lines without line wrapping):
1: [Function(Name="dbo.uspGetManagerEmployees")]
2: public ISingleResult<uspGetManagerEmployeesResult> uspGetManagerEmployees([Parameter(Name="ManagerID", DbType="Int")] System.Nullable<int> managerID)
3: {4: IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), managerID);
5: return ((ISingleResult<uspGetManagerEmployeesResult>)(result.ReturnValue));
6: }There are a couple of interesting things here. First, the name of the function is (by default) the name of the stored procedure. You can change that by clicking on the stored procedure in the Methods Pane and then changing the "Name" property in the Properties window.
The actual call to the stored procedure happens in the ExecuteMethodCall, a method from the DataContext class. Reflection is used to get the name of the calling method, and that name is used to figure out which stored procedure to call internally.
The return value is cast to something called an ISingleResult<uspGetManagerEmployeesResult>. When the stored procedure is added to the DBML design surface, the stored proc is called and the return value is gleaned from the results. In this case, a table is returned from the stored procedure, and so this gets mapped to a class called uspGetManagerEmployeesResult, which also lives in the DataClasses1.designer.cs file. This class has a property for each column returned from the stored procedure. In my code above, I glossed over this by assigning the results of the stored procedure call to a "var" variable. If you need to use the return value as the actual class, change the "var" (line 13) to ISingleResult<uspGetManagerEmployeesResult> and carry on from there.
How LINQ Handles Stored Procedure Parameters
If we compare the C# method to the SQL Server Stored Procedure signature, you'll notice that the stored procedure has one integer parameter with no default value. This makes the mapping from SQL to C# fairly straightforward. However, you may have a stored procedure that has output parameters or default values for parameters. Let's take a quick look at how these are handled in LINQ.
AdventureWorks has a stored procedure called uspLogError, which has the following signature:
1: CREATE PROCEDURE [dbo].[uspLogError]
2: @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
3: AS -- by uspLogError in the ErrorLog table
4: BEGIN
5: ...6: END
Notice that @ErrorLogID is an output parameter with a default value of 0. When this is added to the DBML design surface, the following C# code is generated:
1: [Function(Name="dbo.uspLogError")]
2: public int uspLogError([Parameter(Name="ErrorLogID", DbType="Int")] ref System.Nullable<int> errorLogID)
3: {4: IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), errorLogID);
5: errorLogID = ((System.Nullable<int>)(result.GetParameterValue(0)));
6: return ((int)(result.ReturnValue));
7: }Since @ErrorLogID is an output parameter, the generated C# code creates its counterpart parameter, errorLogID, as a ref parameter (line 2), and the body of the method contains a line to assign the results of the stored procedure call back to errorLogID before returning (line 5).
Default values for a stored procedure are handled by the fact that a Nullable<T> is passed in to the method. Since the default value lives in the stored procedure itself, there is no need to pass it explicitly from the C# code. If a null value is passed in to the C# method then the stored procedure itself assigns the default value to the incoming parameter.
How Else Can I Use Stored Procedures?
Stored procedures can also be used in place of the code generated to do basic CRUD (Create, Retrieve, Update, Delete). Let's say you drag a table onto the DBML design surface, but instead of using the generated code to delete the object from the database, you prefer to mark the object as deleted, a so-called "soft delete". One way to make this happen is by using a stored procedure in place of the generated delete code.
To do this, create the stored procedure in the database which performs the update to the record (we'll change the Employee table's "CurrentFlag" to 0 in this example):
1: CREATE PROCEDURE [dbo].[uspSoftDeleteEmployee]
2: @EmployeeID [int]
3: AS
4: BEGIN
5: SET NOCOUNT ON;
6: 7: UPDATE Employee SET CurrentFlag = 0 WHERE EmployeeID = @EmployeeID
8: END;
Now drag this stored procedure onto the DBML design surface (you may have to refresh the Stored Procedures list in the Server Explorer to see it). Next, click on the Employee table and then go to the Properties window. In it, you'll see that for the Delete property, "Use Runtime" is selected. This indicates that the generated code will be used to perform deletes (and that the row will be removed from the database if this delete is called!). Click the ellipsis next to "Use Runtime" to bring up the Configure Behavior dialog box:
"Class" should be set to "Employee" already, and "Behavior" should be set to "Delete". Click on the Customize option button and choose the uspSoftDeleteEmployee stored procedure. The Configure Behavior dialog does a good job of guessing which class property should map to the stored procedure's argument in this case, since they're named similarly. Note that you can choose either the Current or the Original value for each of the properties. Ideally, ID values should not get updated in code, but if you want to ensure that the original Employee ID is used for the delete then choose "EmployeeID (Original)". Follow this link to read more about LINQ to SQL change tracking.
Once you click the OK button, any deletes in your code will now call the uspSoftDeleteEmployee stored procedure instead of any generated code. You can verify this by looking once again at the DataClasses1.designer.cs file, and searching for the "DeleteEmployee" method:
1: private void DeleteEmployee(Employee obj)
2: {3: this.uspSoftDeleteEmployee(((System.Nullable<int>)(obj.EmployeeID)));
4: }No longer is this a partial method (meaning that it gets compiled out if there is no implementation for it), so it gets called each time an Employee object is deleted. Notice that this method simply wraps a call to the stored procedure we just dragged onto the design surface.
Conclusion
Calling stored procedures from LINQ is incredibly easy, just drag the stored procedure name from the Server Explorer window and drop it on the DBML design surface, and then call it from an instance of the DataContext in your project. All return types and in/out parameters work as you would expect them to work when calling the stored procedure directly from T-SQL. Additionally, you can override the way inserts, updates, and deletes are handled by providing a stored procedure to perform these actions.
No comments:
Post a Comment