Calling the stored Procedure
Calling the stored procedure is just a matter of creating a SqlConnection object to connect to the database, and a SqlCommand object to run the stored procedure.
Now you have to decide what you want to return by calling the stored procedure. In this case, you return an instance of the SqlDataReader object. The TestForm.vb file contains a method called GetAuthorSqlReader that takes an author ID and returns an instance of a SqlDataReader. Here is the code for the method:
Private Function GetAuthorSqlReader(ByVal authorld As String) As SqlDataReader ' Build a SqlCommand
Dim command As SqlCommand = New SqlCommand("usp_authors_Get_By_ID",
GetPubsConnection()) ' Tell the command we are calling a stored procedure command.CommandType = CommandType.StoredProcedure ' Add the @au_id parameter information to the command command.Parameters.Add(New SqlParameter("@au_id", authorld)) ' The reader requires an open connection command.Connection.Open() ' Execute the sql and return the reader
Return command.ExecuteReader(CommandBehavior.CloseConnection) End Function
Code snippet from AdoNetFeaturesTest project
Notice that in the SqlCommand's constructor call, you have factored out creating a connection to the pubs database into a separate helper method. This is used later in other code examples in your form.
* J l Dato Connections m J^ tirilirlh\vr|tri|iirtv.fiiilit.<lh<] fu Database Diagrams
* CJ Tables niifhor-f I» [3 authors_bulfc_topy I- 33 discounts fr [3 employee t» ¡3 jubt ^ pubjrtfo
^ 03 stores t> SI titleauthor U [¡3 lili» P [j Views
* CJ Stored Protedures I» CD iiyi^iiiy l> □«Ptíji
;__J uip_dulhois_Detttc fiZl usp_authors_<jet_AH
> O UJP authors Get By ID tf h^J uipjii[illmr\_[mrrl t» []jj] usp_authors_Upoate
í» S ujp_Long_Runnino_Protedure
> [J Synonyms fr C3 Types
> Servers
FiGURE 10-2
Here is the code for the GetPubsConnection helper method:
Private Function GetPubsConnection() As SqlConnection ' Build a SqlConnection based on the config value. Return New
SqlConnection(ConfigurationSettings. _
ConnectionStrings("db").ConnectionString) End Function
Code snippet from AdoNetFeaturesTest project
The most significant thing this code does is grab a connection string to the database from the application's configuration file, app.config. Here is what the entry in the app.config file looks like (update to match the location of pubs on your computer):
<connectionStrings>
<add name="db" value="server=(local)\sqlexpress; database=pubs;trusted_connection=true;" /> </connectionStrings>
Although the helper method doesn't do much, it is nice to place this code in a separate method. This way, if the code to get a connection to the databases needs to be changed, the code only has to be changed in one place.
Accessing a stored procedure is more verbose (but not more difficult) than accessing a normal SQL statement through the methods discussed thus far. The approach is as follows:
1. Create a SqlCommand object.
2. Configure it to access a stored procedure by setting the CommandType property.
3. Add parameters that exactly match those in the stored procedure itself.
4. Execute the stored procedure using one of the SqlCommand object's ExecuteX methods.
There is no real need to build an impressive UI for this application, as we're about to add a button named getAuthorByldButton that calls the GetAuthorSqlRecord helper method and displays the selected author's name. Here is the button's Click event handler:
Private Sub _getAuthorByIdButton_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles _getAuthorByIdButton.Click Dim reader As SqlDataReader = Me. GetAuthorSqlReader ("409-56-7008") If reader.Read()
MessageBox.Show(reader("au_fname").ToString() & " " & reader("au_lname").ToString())
End If reader.Close() End Sub
Code snippet from AdoNetFeaturesTest project
This has hard-coded an author ID of 409-56-7008. Run the code now and you should see the result shown in Figure 10-3.
Post a comment