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. |
