The SqlClient Namespace and Stored Procedures

Now that you've seen how to call stored procedures using the OleDb namespace, it's interesting to compare the same tasks using the SqlClient namespace. Remember that when you use the SqlClient namespace, you're using code that's written to interact specifically with SQL Server, and this code can take advantage of specific SQL Server features. The code you'll see, EmpAddSqlClient, works almost identically to the EmpAddOleDb procedure you saw earlier, but there are some subtle differences.

Listing 15.6 shows the entire procedure.

Listing 15.6. Using the SqlClient Namespace to Call a Stored Procedure
Private Sub EmpAddSqlClient()
  Dim cmd As SqlClient.SqlCommand
  Dim strSQL As String
  Dim strConn As String
  Dim intRows As Integer

  ' Set up the connection and SQL strings.
  strConn = Session("ConnectStringSQL").ToString
  strSQL = "EmployeeAdd"

  Try
    cmd = New SqlClient.SqlCommand()
    With cmd
      ' Create a Connection object
      .Connection = _
        New SqlClient.SqlConnection(strConn)

      ' Set the SQL
      .CommandText = strSQL
      .CommandType = CommandType.StoredProcedure

      ' Create OUTPUT Parameter for
      ' EmployeeID which will be
      ' fetched using @@IDENTITY
      With .Parameters.Add("@EmpID", _
       SqlDbType.Int)
        .Direction = ParameterDirection.Output
      End With

      ' Create input parameters
      With .Parameters.Add("@FirstName", _
       SqlDbType.Char, 10)
        .Value = txtFirst.Text
      End With

      ' Create input parameter for LastName
      With .Parameters.Add("@LastName", _
       SqlDbType.Char, 20)
        .Value = txtLast.Text
      End With

      ' Create Return Value parameter
      ' Doesn't need to be the first parameter added!
      With .Parameters.Add("ReturnValue", _
       SqlDbType.Int)
        .Direction = ParameterDirection.ReturnValue
      End With

      ' Open the connection
      .Connection.Open()

      ' Execute the stored procedure
      intRows = .ExecuteNonQuery()

      ' Get the output parameter
      lblEmpID.Text = _
       CStr(.Parameters("@EmpID").Value)

      ' Get the return value parameter
      lblError.Text = "Return Value = " & _
       CStr(.Parameters("ReturnValue").Value) & _
       "<BR>Rows Affected=" & intRows.ToString()
    End With

    ' Reload DropDownList control
    EmpLoad()

  Catch exp As Exception
    lblError.Text = exp.Message

  Finally
    ' Close the Connection
    With cmd.Connection
      If .State = ConnectionState.Open Then
        .Close()
      End If
    End With
  End Try
End Sub

If you look carefully, you'll note some subtle differences:

  • The parameter types use a different enumeration (SqlDbType as opposed to OleDbType).

  • The parameter names must match the exact names used in the stored procedure. That is, the parameters you create in your code must include “@,” which is part of the stored procedure names:

    With .Parameters.Add("@EmpID", SqlDbType.Int)
  .Direction = ParameterDirection.Output
End With

' Create input parameters
With .Parameters.Add("@FirstName", SqlDbType.Char, 10)
  .Value = txtFirst.Text
End With

' Create input parameter for LastName
With .Parameters.Add("@LastName", SqlDbType.Char, 20)
  .Value = txtLast.Text
End With

  • The order of the parameters doesn't matter—given the names you supply, SQL Server can figure out which parameters go where. This is distinctly different from using the OleDb namespace, in which parameter order is significant.

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

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