Part 1 of “Programming with Stored Procedures” demonstrated how you can edit and test stored procedures without leaving Visual Studio .NET. In this part I will demonstrate how you can codify the analogous steps to incorporate stored procedures into your Visual Basic .NET applications.
In this article I will demonstrate how to:
- Externalize connection string information in an XML configuration file
- Connect to a database
- Create an instance of a command object
- Create an adapter and fill a DataSet with data returned from a stored procedure
- And, use both Input and Output parameters with stored procedures
The information contained herein will provide you with the most common skills you will need to professionally incorporate stored procedures into your applications. The techniques demonstrated work with most of the databases you are likely to encounter, including MS SQL Server, DB2 Universal Database, MS Access, Oracle, Informix, and any other database that works with ODBC.
Connecting to a Database
ADO.NET supports using both traditional databases as data providers and non-traditional repositories for data. The examples in this section demonstrate how to connection to a typical database. To do so you will need a couple of bits of information. You will need a database to connect to, a connection string, and Visual Basic .NET.
In order to connect to a database you need to know which category the database falls into. If you are connecting to MS SQL Server 7.0 or greater then you will need to use the classes in System.Data.SqlClient namespace. For everything else you will need to use the classes in System.Data.OleDb namespace. For our demonstration we will be using MS SQL Server 2000, so will use the SqlClient ADO.NET classes.
Externalizing the Connection String
A technique I prefer to use is to externalize the connection string information in a .config file. If you are writing a Windows application then you can add an Application Configuration file to your project. If you are writing a Web application then you can place the connection string in the Web.config file that is created with Web applications.
Conveniently we can use the System.Configuration.ConfigurationSettings class’ shared collection AppSettings to read from an application configuration file. If we place the connection string information in an <appSettings> tag in the .config file then .NET already provides with a means of reading this information. Our demo application is a Windows application. We can add an App.config file to our project manually or by selecting File|Add New Item and picking the Application Configuration applet from the Add New Item dialog (see figure 1). Listing 1 contains the App.config file for our example program, including an initialized connection string.
Figure 1: The Application Configuration File template will add an App.config file to your project.
Listing 1: Defining the connection string in the
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ConnectionString"
value="data source=SCI\Erewhon;initial
catalog=Northwind;integrated security=SSPI;persist
security info=True;workstation id=PTK800;packet size=4096" />
</appSettings>
</configuration>
Caution: The value parameter is a contiquous string on a single line in the .config file. It is shown wrapped due to margin limits set in my word processor.
<appSettings> elements are added as key and value pairs. The key is the name we use to access the value with, and the value defines the value we want externalize. In the example the key is named ConnectionString and the value is an actual connection string for an MS SQL Server instance of the Northwind database. Your connection string will vary, but an excellent way to build a connection string is to use the Data Links Properties (see figure 2) dialog used to edit .udl files or simply copy and paste the connection string from the Properties window in Visual Studio .NET.
Figure 2: The Data Link Properties editor can be used to edit .udl text files and is a good way to build a connection string for OleDb providers.
To copy the connection string from the Properties window:
- Make sure the computer with the database is registered in the Server Explorer
- Select the database in the Server Explorer
- Open the Properties window with F4
- And, copy the ConnectionString from the Properties window (see figure 3)
Figure 3: Copy the connection string for a specific database from the Properties window in Visual Studio .NET.
After we have defined the connection string and externalized it in the App.config file we can read the ConnectionString key’s value using the ConfigurationSettings class. An example of reading the connection string and initializing the connection object is shown in listing 2.
Opening the Connection
ADO.NET uses a disconnected model. This doesn’t mean we don’t use a connection; what it does mean is that we don’t hold the connection. We still need to declare and initialize a connection object to talk to our database. Listing 2 demonstrates how to read an external connection string from an App.config file and initialize a connection object.
Listing 2: Declaring and initializing a connection object.
Dim Connection As SqlConnection = _
New SqlConnection( _
ConfigurationSettings.AppSettings( _
"ConnectionString"))
(Again the text was wrapped based on available digital real estate. You can still use the underscore character (_) to wrap code in Visual Basic .NET. (I’d prefer it if they got rid of this requirement and simply allowed text to wrap.) The single statement both declares and initializes a SqlConnection object. Declared in a more verbose form the code could be re-written as shown in listing 3.
Listing 3: A verbose form of the code in listing 2.
Dim ConnectionString As String
ConnectionString = _
System.Configuration.ConfigurationSettings.AppSettings( _
"ConnectionString")
Dim Connection As SqlConnection
Connection = New SqlConnection(ConnectionString)
Happily there is no longer any reason to write verbose code. By using the brief version in listing 1 we can avoid many temporary variables and a lot of unnecessary lines of code.
Creating the Command Object
Command objects are created implicitly or you can create them explicitly. Essentially the command object is the object-oriented representation of SQL text and stored procedure information. To invoke a stored procedure we need to create a command object and initialize it with information about the database and the way we want to interact with the database. Listing 4 is combined with listing 2, demonstrating how to initialize a command object and its relationship to a connection.
Listing 4: Initializing a command object.
1: Dim Connection As SqlConnection = _ 2: New SqlConnection( _ 3: ConfigurationSettings.AppSettings( _ 4: "ConnectionString")) 5: 6: Dim Command As SqlCommand = _ 7: New SqlCommand() 8: Command.Connection = Connection 9: Command.CommandText = "Ten Most Expensive Products" 10: Command.CommandType = CommandType.StoredProcedure
(Line numbers were added for reference only.) Lines 1 through 5 initialize the connection object. Lines 6 and 7 initialize a new SqlCommand object. Line 8 assigns the connection instance to the command’s Connection property. Line 9 names the command to execute, and line 10 indicates that the text represents a stored procedure. When we run this code we will be invoking the “Ten Most Expensive Products” stored procedure.
Creating an Adapter
If we elect to use a DataSet—we could use a DataReader—we need to create an adapter to bridge the gap between the connection and the DataSet. A reasonable person might wonder why there are so many classes to read data from a database. The answer is that common features have been factored out to eliminate or reduce redundancy. For example, if the code in the adapter were stuffed into the DataSet then client applications would have to carry it around and the code would need to be repeated in the DataTable. This separation of responsibilities is just good object-oriented design.
We can create an adapter and implicitly create the command object by passing the SQL text and the connection to the adapter, or we can create a command—as we have done—and use the command to initialize the adapter. (Think of the adapter as being responsible for moving data between the database and the DataSet.) Listing 5 demonstrates how to initialize an adapter, DataSet, and display the data in a DataGrid. I have provided the entire listing in listing 5 to include the Imports statements.
Listing 5: Invoking a stored procedure and displaying the results.
1: Imports System.Data 2: Imports System.Data.SqlClient 3: Imports System.Configuration 4: 5: Public Class Form1 6: Inherits System.Windows.Forms.Form 7: 8: [ Windows Form Designer generated code ] 9: 10: Private Sub Form1_Load(ByVal sender As Object, _ 11: ByVal e As System.EventArgs) Handles MyBase.Load 12: 13: NoParameter() 14: 15: End Sub 16: 17: Private Sub NoParameter() 18: 19: Dim Connection As SqlConnection = _ 20: New SqlConnection( _ 21: ConfigurationSettings.AppSettings( _ 22: "ConnectionString")) 23: 24: Dim Command As SqlCommand = _ 25: New SqlCommand() 26: Command.Connection = Connection 27: Command.CommandText = "Ten Most Expensive Products" 28: Command.CommandType = CommandType.StoredProcedure 29: 30: Dim Adapter As SqlDataAdapter = _ 31: New SqlDataAdapter(Command) 32: 33: Dim DataSet As DataSet = _ 34: New DataSet(Command.CommandText) 35: 36: Adapter.Fill(DataSet) 37: DataGrid1.DataSource = DataSet.Tables(0) 38: 39: End Sub 40: 41: End Class
Again, the line numbers were added for reference and the Windows Forms designer generated code was collapsed. The application contains a single form with a single DataGrid. The three namespaces needed are declared on lines 1 through 3, and the additional adapter and DataSet code is shown on lines 30 through 37. When the form loads the result set returned by the stored procedure is displayed in the DataGrid.
Invoking a Stored Procedure with Parameters
Stored procedures are essentially functions that reside on the database. We need to be able to send arguments to stored procedure just like we send arguments to functions and subroutines in Visual Basic .NET. We also need a means of indicating how these parameters are used. In Visual Basic .NET code we use the ByVal and ByRef modifiers. When defining stored procedure parameters we need to perform analogous tasks in a way that is meaningful to database engines. This is accomplished by using parameter objects.
Passing Input Parameters
Without further ado here is an example (listing 6) that demonstrates sending a parameter to a stored procedure. I have included the complete listing for clarity but will not elaborate on the code other then to suggest that everything except the command and parameter objects are identical to the code provided and described in listing 5.
Listing 6: Sending input parameters to a stored procedure.
1: Imports System.Data
2: Imports System.Data.SqlClient
3: Imports System.Configuration
4:
5: Public Class Form1
6: Inherits System.Windows.Forms.Form
7:
8: [ Windows Form Designer generated code ]
9:
10: Private Sub Form1_Load(ByVal sender As Object, _
11: ByVal e As System.EventArgs) Handles MyBase.Load
12:
13: InputParameter()
14:
15: End Sub
16:
17: Public Sub InputParameter()
18:
19: Dim Connection As SqlConnection = _
20: New SqlConnection( _
21: ConfigurationSettings.AppSettings( _
22: "ConnectionString"))
23:
24: Dim Command As SqlCommand = _
25: New SqlCommand()
26: Command.Connection = Connection
27: Command.CommandText = "CustOrderHist"
28: Command.CommandType = CommandType.StoredProcedure
29:
30: Dim Parameter As SqlParameter = _
31: New SqlParameter("@CustomerID", "ALFKI")
32: Parameter.Direction = ParameterDirection.Input
33: Parameter.DbType = DbType.String
34:
35: Command.Parameters.Add(Parameter)
36:
37: Dim Adapter As SqlDataAdapter = _
38: New SqlDataAdapter(Command)
39:
40: Dim DataSet As DataSet = _
41: New DataSet("Order History")
42:
43: Adapter.Fill(DataSet)
44: DataGrid1.DataSource = DataSet.Tables(0)
45:
46: End Sub
47: End Class
I used a bold case font to highlight the code that was modified in order to send the input parameter. On line 27 I indicated that we are invoking the CustOrderHist stored procedure. The parameter object is created on line 30 and added to the Command’s Parameters collection on line 35. In between I specified the parameter name and value as arguments to the SqlParameter constructor (line 31) and indicated the direction and type on lines 32 and 33. These are all analogous operations to calling VB.NET methods.
If you need to send more then one parameter then you need to repeat the code on lines 30 through 35 for each parameter, varying the name, value, direction, and type, as appropriate, for each parameter.
Passing and Retrieving Output Parameters
Output parameters are used less frequently than input parameters. For one thing, you always have the option of returning a cursor if you need a lot of data, but there are instances when you just need one piece of data. For example, if you insert a new row and the primary key is generated then you may need to get this data back from the database. An output parameter works fine here.
Output parameters are analogous to ByRef arguments. You have two ways of reading the value of an output parameter: you can declare the parameter object as a separate variable and read the value after the stored procedure has run, or you can read the value via the command’s Parameter collection. Listing 7 is an excerpt from the IBUYSPY portal code which demonstrates how to invoke a stored procedure with an output parameter and how to retrieve the data after the procedure is called.
Listing 7: Invoking a stored procedure with an output parameter.
1: public int AddAnnouncement(int moduleId, int itemId, _
2: String userName, String title, DateTime expireDate, _
3: String description, String moreLink, String mobileMoreLink)
4: {
5:
6: if (userName.Length < 1) {
7: userName = "unknown";
8: }
9:
10: // Create Instance of Connection and Command Object
11: SqlConnection myConnection = _
12: new SqlConnection(ConfigurationSettings.AppSettings[ _
"connectionString"]);
13:
14: SqlCommand myCommand = _
15: new SqlCommand("AddAnnouncement", myConnection);
16:
17: // Mark the Command as a SPROC
18: myCommand.CommandType = CommandType.StoredProcedure;
19:
20: // Add Parameters to SPROC
21: SqlParameter parameterItemID = _
22: new SqlParameter("@ItemID", SqlDbType.Int, 4);
23: parameterItemID.Direction = ParameterDirection.Output;
24: myCommand.Parameters.Add(parameterItemID);
25:
26: SqlParameter parameterModuleID = _
27: new SqlParameter("@ModuleID", SqlDbType.Int, 4);
28: parameterModuleID.Value = moduleId;
29: myCommand.Parameters.Add(parameterModuleID);
30:
31: SqlParameter parameterUserName = _
32: new SqlParameter("@UserName", SqlDbType.NVarChar, 100);
33: parameterUserName.Value = userName;
34: myCommand.Parameters.Add(parameterUserName);
35:
36: SqlParameter parameterTitle = _
37: new SqlParameter("@Title", SqlDbType.NVarChar, 150);
38: parameterTitle.Value = title;
39: myCommand.Parameters.Add(parameterTitle);
40:
41: SqlParameter parameterMoreLink = _
42: new SqlParameter("@MoreLink", SqlDbType.NVarChar, 150);
43: parameterMoreLink.Value = moreLink;
44: myCommand.Parameters.Add(parameterMoreLink);
45:
46: SqlParameter parameterMobileMoreLink = _
47: new SqlParameter("@MobileMoreLink", _
SqlDbType.NVarChar, 150);
48: parameterMobileMoreLink.Value = mobileMoreLink;
49: myCommand.Parameters.Add(parameterMobileMoreLink);
50:
51: SqlParameter parameterExpireDate = _
52: new SqlParameter("@ExpireDate", SqlDbType.DateTime, 8);
53: parameterExpireDate.Value = expireDate;
54: myCommand.Parameters.Add(parameterExpireDate);
55:
56: SqlParameter parameterDescription = _
57: new SqlParameter("@Description", _
SqlDbType.NVarChar, 2000);
58: parameterDescription.Value = description;
59: myCommand.Parameters.Add(parameterDescription);
60:
61: myConnection.Open();
62: myCommand.ExecuteNonQuery();
63: myConnection.Close();
64:
65: return (int)parameterItemID.Value;
66: }
AddAnnouncement inserts an announcement into the IBUYSPY Portal database. Most of the code should be familiar to you by now. The code accepts all of the input parameters as arguments to AddAnnouncement and uses these as parameters to pass to the stored procedure. The elements for invoking the stored procedure are still the same; the listing is longer because we are creating and adding several parameters. What we are interested in is the code on lines 21 through 24 and line 65.
Lines 21 through 24 create an output stored procedure parameter by specifying the ParameterDirection.Output argument to the SqlParameter's constructor. (Keep in mind that method and constructor overloading is supported in Visual Basic .NET. The SqlParameter constructor call is an example of an overloaded constructor.) After the stored procedure is invoked we can read the value returned by the stored procedure, which is done on line 65.
To experiment with some great ASP.NET and Visual Basic .NET code I encourage you download and install the IBUYSPY portal.
Summary
Stored procedures are a powerful aspect of database programming for all platforms. A great benefit of using stored procedures is that you can offload database intensive code to the server, and separate responsibilities by requiring your DBAs to manage the stored procedure code for you.
If your experiences are like mine then you may have to write many of your own stored procedures and code that invokes them. The examples in this article should help you along the way.
About the Author
Paul Kimmel is a freelance writer for Developer.com and CodeGuru.com. Look for his recent book "Advanced C# Programming" from McGraw-Hill/Osborne on Amazon.com. Paul Kimmel is available to help design and build your .NET solutions and can be contacted at pkimmel@softconcepts.com.
# # #
