Walkthrough: Using Only Stored Procedures (C#)

Article
09/15/2021

This walkthrough provides a basic end-to-end LINQ to SQL scenario for accessing data by executing stored procedures only. This approach is often used by database administrators to limit how the datastore is accessed.

Note

You can also use stored procedures in LINQ to SQL applications to override default behavior, especially for Create, Update, and Delete processes. For more information, see Customizing Insert, Update, and Delete Operations.

For purposes of this walkthrough, you will use two methods that have been mapped to stored procedures in the Northwind sample database: CustOrdersDetail and CustOrderHist. The mapping occurs when you run the SqlMetal command-line tool to generate a C# file. For more information, see the Prerequisites section later in this walkthrough.

This walkthrough does not rely on the Object Relational Designer. Developers using Visual Studio can also use the O/R Designer to implement stored procedure functionality. See LINQ to SQL Tools in Visual Studio.

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.

This walkthrough was written by using Visual C# Development Settings.

Prerequisites

This walkthrough requires the following:

This walkthrough uses a dedicated folder ("c:\linqtest7") to hold files. Create this folder before you begin the walkthrough.
The Northwind sample database.

If you do not have this database on your development computer, you can download it from the Microsoft download site. For instructions, see Downloading Sample Databases. After you have downloaded the database, copy the northwnd.mdf file to the c:\linqtest7 folder.
A C# code file generated from the Northwind database.

This walkthrough was written by using the SqlMetal tool with the following command line:

sqlmetal /code:"c:\linqtest7\northwind.cs" /language:csharp "c:\linqtest7\northwnd.mdf" /sprocs /functions /pluralize

For more information, see SqlMetal.exe (Code Generation Tool).

Overview

This walkthrough consists of six main tasks:

Setting up the LINQ to SQL solution in Visual Studio.
Adding the System.Data.Linq assembly to the project.
Adding the database code file to the project.
Creating a connection with the database.
Setting up the user interface.
Running and testing the application.

Creating a LINQ to SQL Solution

In this first task, you create a Visual Studio solution that contains the necessary references to build and run a LINQ to SQL project.

To create a LINQ to SQL solution

On the Visual Studio File menu, point to New, and then click Project.
In the Project types pane in the New Project dialog box, click Visual C#.
In the Templates pane, click Windows Forms Application.
In the Name box, type SprocOnlyApp.
In the Location box, verify where you want to store your project files.
Click OK.

The Windows Forms Designer opens.

Adding the LINQ to SQL Assembly Reference

The LINQ to SQL assembly is not included in the standard Windows Forms Application template. You will have to add the assembly yourself, as explained in the following steps:

To add System.Data.Linq.dll

In Solution Explorer, right-click References, and then click Add Reference.
In the Add Reference dialog box, click .NET, click the System.Data.Linq assembly, and then click OK.

The assembly is added to the project.

Adding the Northwind Code File to the Project

This step assumes that you have used the SqlMetal tool to generate a code file from the Northwind sample database. For more information, see the Prerequisites section earlier in this walkthrough.

To add the northwind code file to the project

On the Project menu, click Add Existing Item.
In the Add Existing Item dialog box, move to c:\linqtest7\northwind.cs, and then click Add.

The northwind.cs file is added to the project.

Creating a Database Connection

In this step, you define the connection to the Northwind sample database. This walkthrough uses "c:\linqtest7\northwnd.mdf" as the path.

To create the database connection

In Solution Explorer, right-click Form1.cs, and then click View Code.

Type the following code into the Form1 class:

Northwnd db = new Northwnd(@"c:\linqtest7\northwnd.mdf");

Setting up the User Interface

In this task you set up an interface so that users can execute stored procedures to access data in the database. In the applications that you are developing with this walkthrough, users can access data in the database only by using the stored procedures embedded in the application.

To set up the user interface

Return to the Windows Forms Designer (Form1.cs[Design]).
On the View menu, click Toolbox.

The toolbox opens.

Note

Click the AutoHide pushpin to keep the toolbox open while you perform the remaining steps in this section.
Drag two buttons, two text boxes, and two labels from the toolbox onto Form1.

Arrange the controls as in the accompanying illustration. Expand Form1 so that the controls fit easily.
Right-click label1, and then click Properties.
Change the Text property from label1 to Enter OrderID:.
In the same way for label2, change the Text property from label2 to Enter CustomerID:.
In the same way, change the Text property for button1 to Order Details.
Change the Text property for button2 to Order History.

Widen the button controls so that all the text is visible.

To handle button clicks

Double-click Order Details on Form1 to open the button1 event handler in the code editor.

Type the following code into the button1 handler:

// Declare a variable to hold the contents of
// textBox1 as an argument for the stored
// procedure.
string param = textBox1.Text;

// Declare a variable to hold the results
// returned by the stored procedure.
var custquery = db.CustOrdersDetail(Convert.ToInt32(param));

// Execute the stored procedure and display the results.
string msg = "";
foreach (CustOrdersDetailResult custOrdersDetail in custquery)
{
    msg = msg + custOrdersDetail.ProductName + "\n";
}
if (msg == "")
    msg = "No results.";
MessageBox.Show(msg);

// Clear the variables before continuing.
param = "";
textBox1.Text = "";

Now double-click button2 on Form1 to open the button2 handler

Type the following code into the button2 handler:

// Comments in the code for button2 are the same
// as for button1.
string param = textBox2.Text;

var custquery = db.CustOrderHist(param);

string msg = "";
foreach (CustOrderHistResult custOrdHist in custquery)
{
    msg = msg + custOrdHist.ProductName + "\n";
}
MessageBox.Show(msg);

param = "";
textBox2.Text = "";

Testing the Application

Now it is time to test your application. Note that your contact with the datastore is limited to whatever actions the two stored procedures can take. Those actions are to return the products included for any orderID you enter, or to return a history of products ordered for any CustomerID you enter.

To test the application

Press F5 to start debugging.

Form1 appears.
In the Enter OrderID box, type 10249, and then click Order Details.

A message box lists the products included in order 10249.

Click OK to close the message box.
In the Enter CustomerID box, type ALFKI, and then click Order History.

A message box appears that lists the order history for customer ALFKI.

Click OK to close the message box.
In the Enter OrderID box, type 123, and then click Order Details.

A message box appears that displays "No results."

Click OK to close the message box.
On the Debug menu, click Stop debugging.

The debug session closes.
If you have finished experimenting, you can click Close Project on the File menu, and save your project when you are prompted.

Next Steps

You can enhance this project by making some changes. For example, you could list available stored procedures in a list box and have the user select which procedures to execute. You could also stream the output of the reports to a text file.

Share via