Edit

Share via

How to: Call a Stored Procedure by Using LINQ (Visual Basic)

  • Article

Language-Integrated Query (LINQ) makes it easy to access database information, including database objects such as stored procedures.

The following example shows how to create an application that calls a stored procedure in a SQL Server database. The sample shows how to call two different stored procedures in the database. Each procedure returns the results of a query. One procedure takes input parameters, and the other procedure does not take parameters.

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.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

To create a connection to a database

  1. In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

  2. Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.

  3. Specify a valid connection to the Northwind sample database.

To add a project that contains a LINQ to SQL file

  1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

  2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

  3. Name the file northwind.dbml. Click Add. The Object Relational Designer (O/R Designer) is opened for the northwind.dbml file.

To add stored procedures to the O/R Designer

  1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Stored Procedures folder.

    If you have closed the O/R Designer, you can reopen it by double-clicking the northwind.dbml file that you added earlier.

  2. Click the Sales by Year stored procedure and drag it to the right pane of the designer. Click the Ten Most Expensive Products stored procedure drag it to the right pane of the designer.

  3. Save your changes and close the designer.

  4. Save your project.

To add code to display the results of the stored procedures

  1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

  2. Double-click Form1 to add code to its Load event.

  3. When you added stored procedures to the O/R Designer, the designer added a DataContext object for your project. This object contains the code that you must have to access those procedures. The DataContext object for the project is named based on the name of the .dbml file. For this project, the DataContext object is named northwindDataContext.

    You can create an instance of the DataContext in your code and call the stored procedure methods specified by the O/R Designer. To bind to the DataGridView object, you may have to force the query to execute immediately by calling the ToList method on the results of the stored procedure.

    Add the following code to the Load event to call either of the stored procedures exposed as methods for your data context.

    Dim db As New northwindDataContext

' Display the results of the Sales_by_Year stored procedure.
DataGridView1.DataSource =
    db.Sales_by_Year(#1/1/1996#, #1/1/2007#).ToList()

    ' Display the results of the Ten_Most_Expensive_Products
' stored procedure.

DataGridView1.DataSource =
    db.Ten_Most_Expensive_Products.ToList()

  4. Press F5 to run your project and view the results.

See also