ADO.NET 101: SqlDataReader - SQLCommand for CommandType: Stored Procedure

(Page 5 of 7 )

In the case of Stored Procedures, there are basically two types, the ones that make changes to the database (similar to action queries) and those that do not make changes. SQLDataReader is of the Read only, Forward only type by nature and cannot be used for making changes to the database. In the case of those that do not make changes, three different types of procedures are discussed:

Simple Stored Procedure with no input or output parameters

1. Create connection to "pubs" database as before.

2. In "Server Explorer," expand the database to show all stored procedures.

3. "MySimple" stored procedure will be used in this example. The SQL statements used while creating the Stored procedure are shown here. This example could also have been used by just using an SQL query, but it is used here to show how a Stored Procedure is accessed by code. Also, for queries requiring considerable processing as in the case of multiple joins, it may be faster to use a stored procedure.

Create Procedure MySimple
as
Select stor_name, city, zip, state
from stores
where state='CA'or state='WA'
order by city

4. Use this code:

 Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click
        'declare a connection of type SQLConnection
        Dim SqlConnection1 = New SqlClient.SqlConnection
        'define the connection string for this connection
        SqlConnection1. _
        connectionString = "workstation id= XPHTEK;packet size=4096;" & _
        "integrated security=SSPI;data source=XPHTEK;" & _
        "persist security info=False;initial catalog=pubs"
        'open the connection
        SqlConnection1.Open()
        'declare a SQLCommand1 as the new instance
        Dim SQLCommand1 = New SqlClient.SqlCommand
        'set the next four properties of the SQLCommand1
        SQLCommand1.Connection = SqlConnection1
        SQLCommand1.CommandTimeout = 30
        'now the CommandType is 'Stored Procedure
        <b>SQLCommand1.CommandType = CommandType.StoredProcedure</B>
        'CommandText is the name of the stored procedrue
        <b>SQLCommand1.commandtext = "MySimple"</b>
        Dim dr As SqlClient.SqlDataReader
        dr = SQLCommand1.executeReader
        Response.Write("<b>Are rows returned?</b> " & _
        dr.HasRows & "<br>")
        While dr.Read
        'only one column is returned to reduce clutter
            Response.Write(dr.Item(0) & "<br>")
        End While
        dr.Close()
        SqlConnection1.close()
    End Sub

 
5. When the code is run the following result is obtained.

{mospagebreak title=Simple Stored Procedure with one input parameter)

This Stored procedure MySimple2 requires an input value[@State]and retrieves records from the database. The SQL statements used while creating the Stored Procedure are shown here:

Create procedure MySimple2
@State char(2)
as
Select stor_name, city, zip, state
from stores
where state=@State
order by city

1. Establish a connection as before.

2. Place a textbox to receive the input value, a ListBox to display retrieved values and a button to initiate retrieval.

3. In the click event of the button insert this code. Since an input parameter is going to be used, you need to specify the parameter and add it to the SQLParameters collection.

 Private Sub Button1_Click(ByVal sender As _
    System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'declare a connection of type SQLConnection
        Dim SqlConnection1 = New SqlClient.SqlConnection
        'define the connection string for this connection
        SqlConnection1. _
        connectionString = "workstation id= XPHTEK;packet size=4096;" & _
        "integrated security=SSPI;data source=XPHTEK;" & _
        "persist security info=False;initial catalog=pubs"
        'open the connection
        SqlConnection1.Open()
        'declare a SQLCommand1 as the new instance
        Dim SQLCommand1 = New SqlClient.SqlCommand
        'set the next four properties of the SQLCommand
        SQLCommand1.Connection = SqlConnection1
        SQLCommand1.CommandTimeout = 30
        SQLCommand1.CommandType = _
        CommandType.StoredProcedure
       
        SQLCommand1.commandtext = "MySimple2"
       
        SQLCommand1.parameters.add( _
        (New SqlClient.SqlParameter _
        ("@State", TextBox1.Text)))
        Dim dr As SqlClient.SqlDataReader
        dr = SQLCommand1.executeReader
        'Response.Write(dr.HasRows)

        While dr.Read
            ListBox1.Items.Add(dr.Item(1))
        End While
        dr.Close()
        SqlConnection1.close()
        TextBox1.Text = ""
    End Sub

4. When the code is run you should see the following:

Next: Stored Procedure with one input and one output parameter >>
 

More Database Articles
More By Jayaram Krishnaswamy

Comments On: ADO.NET 101: SqlDataReader

>>> Be the FIRST to comment on this article!