Executing Stored Procedures

Stored procedures are a performance-optimized, robust way to encapsulate and compile queries on a database server. In this section, you see how to execute stored procedures using the SQL Server .NET Data Provider and the classes in the System.Data.SqlClient namespace.

Supplying Input Parameters

If we were to revisit our SQL code responsible for inserting employee records and place that code into a stored procedure, we would end up with something like the SQL code shown in Listing 15.4.

Listing 15.4. A Record Insert with Parameters
USE dbHumanRes
GO
CREATE PROCEDURE insert_employee
          (@id                       int,
           @first_name               varchar(25),
           @middle_initial    char(1),
           @last_name         varchar(25),
           @extension         char(4),
           @job_code          char(4),
           @division_id              int)

AS INSERT INTO [dbHumanRes].[dbo].[employee]
           (id,
           first_name,
           middle_initial,
           last_name,
           extension,
           job_code,
           division_id)

VALUES
         ( @id,
          @first_name,
          @middle_initial,
          @last_name,
          @extension,
          @job_code,
          @division_id)

This SQL code can be directly compiled as a stored procedure in SQL Server. Notice that the insert_employee stored procedure accepts seven input parameters. Because parameters are parameters in ADO.NET, regardless of whether we are talking about plain SQL commands or stored procedures, our design pattern established with Listing 15.2 doesn't need to change much. Listing 15.5 shows how we can rework our previous parameterized query code to work with a stored procedure.

To help illustrate the differences and similarities between the OLE DB Data Provider and the SQL Server Data Provider, we have written the code this time around using the components from the System.Data.SqlClient namespace and not the System.Data.OleDb namespace.

Listing 15.5. A Record Insert Using a Stored Procedure
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

Module Module1

    Sub Main()
        Dim dbConn As SqlConnection
        Dim connStr As String

        Dim firstName As String = "John"
        Dim middleInitial As String = "M"
        Dim lastName As String = "Burton"
        Dim extension As String = "5001"
        Dim jobCode As String = "DEV"
        Dim divID As Integer = 3

        Try

            connStr = "Provider=SQLOLEDB;Data Source=HR-DB01;" & _
                      "Integrated Security=SSPI;Initial Catalog=dbHumanRes"

            dbConn = New SqlConnection(connStr)

            Console.WriteLine("Trying db connect with -> { 0} ", connStr)
            dbConn.Open()

            Dim query As StringBuilder = New StringBuilder("insert_employee")

            'Create a new command object using the command text and
            'the previously created connection object
            Dim insertQry As New SqlCommand(query.ToString, dbConn)

            insertQry.Parameters.Add("@last_name", SqlDbType.VarChar, 25)
            insertQry.Parameters.Add("@first_name", SqlDbType.VarChar, 25)
            insertQry.Parameters.Add("@middle_initial", SqlDbType.Char, 1)
            insertQry.Parameters.Add("@extension", SqlDbType.Char, 4)
            insertQry.Parameters.Add("@job_code", SqlDbType.Char, 4)
            insertQry.Parameters.Add("@division_id", SqlDbType.Int, 4)

            'Flag the nature of the command text...
            insertQry.CommandType = CommandType.StoredProcedure

            Console.WriteLine("Trying command -> { 0} ", query.ToString)
            Dim rowsAffected As Integer = insertQry.ExecuteNonQuery()

            Console.WriteLine("{ 0}  rows inserted.", rowsAffected)

        Catch appErr As Exception
            Console.WriteLine("An error occurred: { 0} ", appErr.Message

        Finally
            dbConn.Close()

        End Try

    End Sub

End Module

The parameter statements look a bit different from our previous example, as well. Notice that we are free to use named parameters when working with the SqlClient namespace. That is, we can actually identify a parameter by its name (as defined and declared inside the target stored procedure). We don't have to pass parameters in along a fixed order. We are also specifying the data type and size. These should match the parameter declarations in the stored procedures as well. Table 15.12 shows the possible SqlDbType enumeration values.

Table 15.12. The SqlDbTypes Enumeration
Member Description
BigInt A 64-bit signed integer (equivalent to System.Int64)
Binary A fixed-length stream of binary data ranging between 1 and 8,000 bytes
Bit A bit value that can be 0, 1, or null (equivalent to System.Boolean)
Char A fixed-length stream with a minimum of 1 and a maximum of 8,000 characters (equivalent to System.String)
DateTime A date and time value (equivalent to System.DateTime)
Decimal A numeric value with fixed precision and scale (equivalent to System.Decimal)
Float A floating-point value (equivalent to System.Double)
Image A stream of binary data, variable in length
Int A 32-bit signed integer (equivalent to System.Int32)
Money A floating-point currency value; has an accuracy to the ten- thousandth of a currency unit (equivalent to System.Decimal)
NChar A fixed-length stream of 1 to 4,000 Unicode characters (data type equivalent: System.String)
NText A variable-length stream of 1 to 1,073,741,823 characters (data type equivalent: System.String)
NVarChar A variable-length stream of 1 to 4,000 Unicode characters (data type equivalent: System.String)
Real A floating-point value (data type equivalent: System.Single)
SmallDateTime A date and time data value (data type equivalent: System.DateTime)
SmallInt A 16-bit signed integer (data type equivalent: System.Int16)
SmallMoney A currency value with an accuracy to the ten-thousandth of a currency unit (data type equivalent: System.Decimal)
Text A variable-length stream of 1 to 2,147,483,647 non-Unicode characters (data type equivalent: System.String)
Timestamp A date and time value in the format of yyyymmddhhmmss (data type equivalent: System.DateTime)
TinyInt An 8-bit unsigned integer (data type equivalent: System.Byte)
UniqueIdentifier A globally unique identifier (data type equivalent: System.GUID)
VarBinary A variable-length stream of binary data of 1 to 8,000 bytes
VarChar A variable-length stream of 1 to 8,000 non-Unicode characters (data type equivalent: System.String)
Variant A data type that can accommodate varying data formats, including numeric, string, and binary data (data type equivalent: System.Object)

Stored procedures also have the capability to place values in output parameters.

Capturing Output Parameters

Listing 15.6 shows a new version of the stored procedure from Listing 15.5. In this version, we have decided to return the ID of the recently inserted row as an output parameter. To do this, we have added one more parameter, @new_id, which is identified in the parameters list as an OUTPUT parameter. We have also added a SELECT statement at the end of the stored procedure, which assigns the current value of @@IDENTITY (a SQL Server system function) into the @new_id parameter. The @@IDENTITY function will return the last assigned identity value used by SQL Server. Because the primary key ID column for the employee table is defined as an identity column, the value we see returned here should be the assigned ID value for the employee record that we have just inserted.

Listing 15.6. Insert Stored Procedure with an Output Parameter
USE dbHumanRes
GO
CREATE PROCEDURE insert_employee
          (@id                       int,
           @first_name               varchar(25),
           @middle_initial    char(1),
           @last_name         varchar(25),
           @extension         char(4),
           @job_code          char(4),
           @division_id              int,
@new_id        int OUTPUT)

AS INSERT INTO [dbHumanRes].[dbo].[employee]
           (id,
           first_name,
           middle_initial,
           last_name,
           extension,
           job_code,
           division_id)

VALUES
          ( @id,
           @first_name,
           @middle_initial,
           @last_name,
           @extension,
           @job_code,
           @division_id)

SET @new_id = @@IDENTITY

With this stored procedure in mind, let's consider the changes that we would have to make to our applet. First, we would need another addition to our parameters collection to account for the new @new_id parameter:

insertQry.Parameters.Add("@new_id", SqlDbType.Int, 4)

There is a problem here: The Add method doesn't allow you to specify a “direction” to the parameter. In other words, there is no way for you to indicate that the @new_id parameter is not an input parameter but is instead an output parameter. However, a property is available on the SqlParameter and OleDbParameter classes that enables you to specify a direction. The Direction property returns or accepts a ParameterDirection enumeration value. All the possible ParameterDirection values are listed in Table 15.13.

Table 15.13. The ParameterDirection Enumeration
Member Description
Input The parameter is an input parameter.
InputOutput The parameter can function both as an input parameter and an output parameter.
Output The parameter is an output parameter.
ReturnValue The parameter is a return value.

The Parameters.Add method actually returns a parameter instance (in this case, SqlParameter). With all our previous parameter additions, we have not worried about “catching” this returned parameter object. Here, because we need to set the Parameter.Direction property, we do need to worry about it. The code change is simple:

Dim parm As SqlParameter
parm = insertQry.Parameters.Add("@new_id", SqlDbType.Int, 4)
parm.Direction = ParameterDirection.Output

To examine the contents of the output parameter after the stored procedure has been executed, you would just reference the Value property of the appropriate SqlParameter object like this:

Console.WriteLine("Assigned ID: { 0} ", _
   insertQry.Parameters("@new_id").Value)

Stored procedures also have the capability to return values outside the parameters interface with a Return statement.

Capturing Return Values

To see return values in action, let's take one more pass at our stored procedure. This time, we will be adding code that checks for a special value of 9999 for the employee's extension (perhaps an extension of 9999 is an indication to an operations group that the employee has a phone but still needs his extension configured). Put aside the question of whether or not the stored procedure is the correct place to place logic like this—this example will at least let us verify the syntax for capturing the return value.

Listing 15.7 shows our insert procedure, revised yet again.

Listing 15.7. Insert Stored Procedure with a Return Statement
CREATE PROCEDURE insert_employee
          (@id                       int,
           @first_name               varchar(25),
           @middle_initial    char(1),
           @last_name         varchar(25),
           @extension         char(4),
           @job_code          char(4),
           @division_id              int,
           @new_id            int OUTPUT)

AS INSERT INTO [dbHumanRes].[dbo].[employee]
           (id,
           first_name,
           middle_initial,
           last_name,
           extension,
           job_code,
           division_id)

VALUES
          ( @id,
           @first_name,
           @middle_initial,
           @last_name,
           @extension,
           @job_code,
           @division_id)

SET @new_id = @@IDENTITY

IF (SELECT (@extension)) = '9999'
          Return 1
Else
       Return 0

Even though return values aren't technically treated as part of the parameters collection by SQL Server, they are lumped into that category by the SqlClient namespace. In fact, they are treated the same way as all other parameters with the exception of their Direction property, which should be set to ParameterDirection.ReturnValue:

parm = insertQry.Parameters.Add("@ext_flag", SqlDbType.Int, 4)
parm.Direction = ParameterDirection.ReturnValue

Note

The OLE DB .NET Data Provider requires that any return parameters be added to the parameters collection first. The SQL Server provider does not suffer from this limitation.


You can examine the return value in an identical fashion to the way that we examined the contents of the output parameter:

If insertQry.Parameters("ext_flag").Value <> 0 Then
      Console.WriteLine("Phone needs configuration")
End If

Listing 15.8 pulls all these concepts together.

Listing 15.8. Working with Output from a Stored Procedure
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text

Module Module1

    Sub Main()
        Dim dbConn As SqlConnection
        Dim connStr As String

        Dim firstName As String = "John"
        Dim middleInitial As String = "M"
        Dim lastName As String = "Burton"
        Dim extension As String = "5001"
        Dim jobCode As String = "DEV"
        Dim divID As Integer = 3

        Try

            connStr = "Provider=SQLOLEDB;Data Source=HR-DB01;" & _
                      "Integrated Security=SSPI;Initial Catalog=dbHumanRes"

            dbConn = New SqlConnection(connStr)

            Console.WriteLine("Trying db connect with -> { 0} ", connStr)
            dbConn.Open()

            Dim query As StringBuilder = New StringBuilder("insert_employee")
            'Create a new command object using the command text and
            'the previously created connection object
            Dim insertQry As New SqlCommand(query.ToString, dbConn)

            insertQry.Parameters.Add("@last_name", SqlDbType.VarChar, 25)
            insertQry.Parameters.Add("@first_name", SqlDbType.VarChar, 25)
            insertQry.Parameters.Add("@middle_initial", SqlDbType.Char, 1)
            insertQry.Parameters.Add("@extension", SqlDbType.Char, 4)
            insertQry.Parameters.Add("@job_code", SqlDbType.Char, 4)
            insertQry.Parameters.Add("@division_id", SqlDbType.Int, 4)

            Dim parm As SqlParameter
            parm = insertQry.Parameters.Add("@new_id", SqlDbType.Int, 4)
            parm.Direction = ParameterDirection.Output

            parm = insertQry.Parameters.Add("@ext_flag", SqlDbType.Int, 4)
            parm.Direction = ParameterDirection.ReturnValue

            insertQry.CommandType = CommandType.StoredProcedure

            Console.WriteLine("Trying command -> { 0} ", query.ToString)

            Dim rowsAffected As Integer = insertQry.ExecuteNonQuery()

            Console.WriteLine("{ 0}  rows inserted.", rowsAffected)
            Console.WriteLine("Assigned ID: { 0} ", _
               insertQry.Parameters("@new_id").Value)

            If insertQry.Parameters("ext_flag").Value <> 0 Then
                Console.WriteLine("Phone needs configuration")
            End If

        Catch appErr As Exception
            Console.WriteLine("An error occurred: { 0} ", appErr.Message)

        Finally
            dbConn.Close()

        End Try

    End Sub

End Module

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.223.119.155