As an application developer, most of the time you'll be executing stored procedure programmatically. You can execute a stored procedure programmatically using the command object. Instead of passing a SQL statement, you pass the stored procedure name as the SQL statement to execute a stored procedure. Each data
provider provides a command object to execute SQL statements. The command class for the OleDb, Odbc, and Sql data provides are Oledbcommand, Odbccommand, and Sqlcommand, respectively. In listing 10-1, I'll use sqlcommand to execute a procedure programmatically against aSQL serverdatabase.
There are two steps involved in executing a stored procedure from your program. First, you set the command object property CommandText as the stored procedure name; second, you set the CommandType property as CommandType.StoredProcedure. Listing 10-1 executes the mySP stored procedure you created in the previous section. To test listing 10-1, I created a console application and typed listing 10-1 on the Main method. Don't forget to add a reference to the System.Data.dll assembly and add the following two namespaces to the project before using the Sql data provider classes:
Imports System.Data
Imports System.Data.SqlClient
Listing 10-1: Executing mySP stored procedure using Sql data provider
Imports System.Data
Imports System.Data.SqlClient
Namespace Executing_a_Stored_Procedure
Class Program
Private Shared Sub Main(ByVal args As String())
' Create a Connection Object
Dim ConnectionString As String = "Integrated Security=SSPI;" & "Initial Catalog=Northwind;" & "Data Source = localhost;"
Dim conn As New SqlConnection(ConnectionString)
conn.Open()
Dim cmd As New SqlCommand("mySP", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Console.Write(reader(0).ToString())
Console.Write(reader(1).ToString())
Console.WriteLine(reader(2).ToString())
End While
Console.Read()
'Close reader and connection
reader.Close()
conn.Close()
End Sub
End Class
End Namespace
As you can see from figure 10-1, I created SqlCommand object by passing the stored procedure as the first parameter of the SqlCommand constructor and then set the CommandType property CommandType.StoredProcedure. The result of listing 10-1 looks like Figure 10-14.
Figure 10-14. Output of stored procedure mySP
A stored procedure can also accept input, output, and both types of programmers. Now I'll modify the mySP stored procedure a little bit. This time I'll give the user an option to select the customers based on their country. Figure 10-15 shows the modified stored procedure.
\
Figure 10-15. Stored procedure with parameters
As you can see from figure 10-15, I selected customers based on the country entered by the user. You can use the SqlParameter class to create a parameter. The SqlParameter class has properties such as Direction and Value. The Direction property defines the direction if the stored procedure is an input or output (or both) or has a return value. The ParameterDirection enumeration defines values of Direction (see Table 10-1).
Table 10-1: The ParameterDirection Members
|
MEMBER
|
DESCRIPTION
|
|
Input
|
Input parameter.
|
|
InputOutput
|
Both input and output parameter.
|
|
Output
|
Output only.
|
|
ReturnValue
|
The parameter returns a value returned by the stored procedure.
|
The Value property sets the value of the parameter. The following code adds a parameter with the value UK. After you execute the mySP stored procedure. It'll return customers from the United Kingdom only:
Dim param As New SqlParameter()
param = StoredProcedureCommand.Parameters.Add("@country", SqlDbType.VarChar, 50)
param.Direction = ParameterDirection.Input
param.Value = "UK"
The updated source code looks like listing 10-2, and the output of listing 10-2 looks like figure 10-16. In listing 10-2, I created SqlParameter as the country and set its value to UK. ExecuteReader only returns rows where Country = "UK".
Listing 10-2: Using parameters in a stored Procedure
' Create a Connection Object
Dim ConnectionStringAs String ="Integrated Security=SSPI;" &"Initial Catalog=Northwind;" &"Data Source=localhost;"
Dim connAs New SqlConnection(ConnectionString)
Dim StoredProcedureCommandAs New SqlCommand("mySP", conn)
StoredProcedureCommand.CommandType = CommandType.StoredProcedure
Dim paramAs New SqlParameter()
param = StoredProcedureCommand.Parameters.Add("@country", SqlDbType.VarChar, 50)
param.Direction = ParameterDirection.Input
param.Value = "UK"
conn.Open()
Dim readerAs SqlDataReader = StoredProcedureCommand.ExecuteReader()
While reader.Read()
Console.Write(reader(0).ToString())
Console.Write(reader(1).ToString())
Console.WriteLine(reader(2).ToString())
End While
Console.Read()
' Close reader and connection
reader.Close()
conn.Close()
Figure 10-16: Output of listing 10-2
To return a value from a stored procedure, the only thing you need to do is change the stored procedure, which will store and return a value as a parameter, and set the parameter's Direction property as follows:
Dim param As New SqlParameter()
param.Direction = ParameterDirection.ReturnValue
Also, store the command execute results in a number variable like this:
param = StoredProcedureCommand.Parameters.Add("@counter", SqlDbType.Int)
Note: See the following example for the complete source code.
Now I'll show you an example of using ParameterDirection.OutPut. To test this source code, create a console application and the following
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Now create a stored procedure called AddCat1 that adds a row to the Categories table and returns the row count (see listing 10-3).
Listing 10-3: AddCat1 stored procedure
ALTER PROCEDUREdbo.AddCat1
@CategoryName nchar(15),
@Description char(16),
@Identity int OUT
AS
INSERT INTOCategories (CategoryName, Description)
VALUES(@CategoryName, @Description)
SET@Identity = @@Identity
RETURN@@ROWCOUNT
/* SET NOCOUNT ON */
RETURN
Listing 10-4 shows how to use output parameters. Everything is similar to the previous samples except that I used the parameter direction
ParameterDirection.Output.
Listing 10-4: Executing a stored procedure with output parameter
Dim connString As String = "Data Source=localhost;Integrated Security=SSPI;" &"Initial Catalog=northwind"
Dim sqlAs String ="SELECT CategoryID, CategoryName, Description FROM Categories"
Dim connAs New SqlConnection(connString)
Dim da As New SqlDataAdapter(sql, conn)
da.InsertCommand = New SqlCommand("AddCat1", conn)
da.InsertCommand.CommandType = CommandType.StoredProcedure
Dim myParmAs SqlParameter = da.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int)
myParm.Direction = ParameterDirection.ReturnValue
da.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName")
da.InsertCommand.Parameters.Add("@Description", SqlDbType.[Char], 16, "Description")
myParm = da.InsertCommand.Parameters.Add("@Identify", SqlDbType.Int, 0, "CotegoryID")
myParm.Direction = ParameterDirection.Output
Dim ds As New DataSet()
da.Fill(ds, "Categories")
Dim row As DataRow = ds.Tables("Categories").NewRow()
row("CategoryName") ="Beverages"
row("Description") ="Chai"
ds.Tables("Categories").Rows.Add(row)
da.Update(ds, "Categories")
Console.WriteLine(da.InsertCommand.Parameters("@RowCount").Value.ToString())
Conclusion
Hope this article would have helped you in understanding executing a Stored Procedure Programmatically. See other articles on the website also for further reference.