Public Function getBookCount(ByVal AuthorID As String) As Integer
Return 0
End Function 'getBookCount
In the body of the method, create an instance of the SqlConnection class and pass in connection information with the instantiation of the SqlConnection object.
Dim cnPubs As New SqlConnection("server=localhost;integrated security=true;" + "database=pubs")
Next, create a SqlCommand object and set the appropriate properties needed to execute the up_AuthorBookCount stored procedure created earlier.
Dim cmdAuthors As New SqlCommand("up_AuthorBookCount", cnPubs)cmdAuthors.CommandType = CommandType.StoredProcedure
Using the Add method of the SqlCommand's Parameter collection add an input parameter that takes the AuthorId value passed in by the caller. Also add an output parameter that will store the value passed back by the stored procedure. The names and data types of the parameters must mach those defined in the stored procedure.
cmdAuthors.Parameters.Add("@au_id", SqlDbType.NVarChar, 11)cmdAuthors.Parameters("@au_id").Value = AuthorID '
cmdAuthors.Parameters.Add("@Count", SqlDbType.Int)
cmdAuthors.Parameters("@Count").Direction = ParameterDirection.Output
'method of the command object
Once the stored procedure is executed, the value of the output parameter is held in a local variable, which is in turn passed back to the client. Don't forget to close the connection after executing the stored procedure.
cnPubs.Open()
Dim iCount As Integer
cmdAuthors.ExecuteNonQuery()
iCount = CInt(cmdAuthors.Parameters("@Count").Value)
cnPubs.Close()
Return iCount
end function
