ADO.NET 101: SqlDataReader

This tutorial deals only with the SQLDataReader object. As the SQLDataReader is inherently bound to the SQLCommand object, SQLCommand object is also discussed. SQLCommand object by itself will be discussed in a future tutorial.

Code for all of the examples is initiated by a button click event, and data display is mostly accomplished by being written to the browser. Sometimes a list box or a text box may be used for parameter entry, or display data; otherwise, there are no other GUIs. The databases are example databases that are commonly found on a SQL Server. The only changes needed for the connection information are the database server name, the database name, and authentication. In the case of SQL authentication, the User ID and Password must be included.

The Stored Procedures can be created by pasting the code in the Query Analyzer when the proper database is chosen. Since stored procedures are executed, additonal permissions have to be set up for the stored procedures after they are created. This can be carried out easily in the Enterprise Manager.

SQLConnection

Before one can get to the SQLCommand, one needs to connect to the SQL Server. This is accomplished by using the SQLConnection. SQLConnection can be made using built-in GU interface or code, and in either case the correct connection string must be specified. This has been dealt with in considerable detail elsewhere(http://www.codeproject.com/aspnet/SQLConnect.asp).

SQLDataReader Class

The SQLDataReader object (as well as the other analogous ODBCDataReader, OleDbDataReader and OracleDataReader objects) does not have a GUI, but is accessed via SQLCommand. SqlDataReader is used to retrieve read-only, forward-only, row after row of streaming data from the SQL Server. Since the DataReader is connected to the server, it should be closed after retrieving the necessary data. While the data is streaming into the SQLDataReader, the connection is busy and cannot be used for other purposes. DataReader is especially useful if fast access is needed. As it retrieves, there will be only one row in the memory, reducing system ovehead.

SQLDataReader is a non-inheritable class, and this next screen shot shows the Class Viewer showing the details of the SQLDataReader class. It has a few properties and a large number of methods as shown in this class view. For best performance use the various Get.... methods of DataReader. If the column datatype is known before hand, using these typed accessor methods will improve performance because no data conversion is involved in retrieval. As will be seen presently, column or field access can be both by name and ordinal reference.

The properties of the SQLDataReader can be utilized to find the number of columns of data retrieved, whether or not any rows are returned by a command, and whether the reader is closed, or open, as shown in this tutorial. Very simply stated, in order to work with SQLDataReader, follow this recipe:

• Establish a SqlConnection and open the connection
  
  
• Create a SQLCommand
  
  
• Use one of the execute methods of the SqlCommand to enable the reader
  
  
• Optionally monitor SQLDataReader's Properties
  
  
• Read the contents of the SQLDataReader
  
  
• Close the SqlDataReader
  
  
• Close the SqlConnection

SQLCommand Execution

SQLCommand's Execute Methods

The SQLCommand, prepared and ready, must be executed against the database.

However, there are several ways of executing the command,

• ExecuteReader() method returns forward only, read only, SQLDataReader.
  
  
• ExecuteScalar() retrieves a single value (first row, first column).
  
  
• ExecuteNonQuery() does not return results. For example, a stored procedure may not return values, if it is making changes to database only.
  
  
• ExecuteXMLReader() returns an XML version of the DataReader. The command has to return XML type.

In this tutorial, only the ExecuteReader() and the ExecuteXMLReader() methods of execution will be considered.

SQLCommand's ExecuteReader() Method

SQLCommand Type

In order to use the SQLDataReader, a SQLCommand is necessary. There are three SQL Command Types as seen below.

Of these three, the TableDirect type is not supported by the .NET provider for SQL. However this is supported by the OleDB providers. This will be discussed towards the end of this article. The CommandType text is usually an SQL Statement (which also happens to be the default type) and the type Stored Procedure is for a named Stored Procedure on the SQL server.

Visual Studio's Data Controls from the ToolBox can be dragged and dropped on the Design Pane and can be configured in-situ. This will be dealt with in another article. In this article, mostly code based data access procedures will be discussed. However, for rapid prototyping, and to understand the syntax without resorting to heavy reading (online or off line), the GUI-based approach is invaluable.

• CommandType:SQL Statement
• CommandType:Stored Procedure
  • Simple Stored procedure:no parameters
  • Stored Procedure with input parameter
  • Stored Procedure with input & output parameters

SQLCommand for CommandType: SQL Statement

The following code makes a SQLConnection using the machine/database specific connection string, and prepares a SQLCommand to run a SQL Select Statement against the database. Observe that the code is along the lines of the recipe mentioned above. The SQLDataReader gets assigned when the SQLCommand's ExecuteReader() method is carried out.

In the click event of a button placed on a Web page, run the following code after building it. The result is written to the browser screen. SQLDataReader's HasRows property can be put to good use to determine if any rows are returned; the FieldCount Property can be used to find the number of columns returned, and so forth. In any case, the speed of retrieval can be improved if the data types returned are known beforehand.

 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 connction string of 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.Text
        'this property is the query that is run against
        'the(database)
        SQLCommand1.CommandText = "SELECT emp_id, " & _
        "fname, lname, hire_date FROM employee " & _
        " WHERE (hire_date > '1/1/1993')"
        'declare a SQLDataReader
        Dim dr As SqlClient.SqlDataReader
        'carry out the ExecuteReader method of the
        'command object
        dr = SQLCommand1.ExecuteReader
        'prints the number of columns in the retrieved data
        Response.Write("<b>No of Columns Retrieved: </b>" & _
        dr.FieldCount & "<br/>")
        'prints a Boolean if rows are returned
        Response.Write("<b>Are rows returned from query?: </b>" & _
        dr.HasRows & "<br/>")
        'verifies with a Boolean whether the reader is
        'closed or open
        'here, SQLDataReader is open
        Response.Write("<b>Is the SqlDataReader closed ?</b>" & _
        dr.IsClosed & "<br/>")
        While dr.Read
            'here you retrieve the row pertaining to the first
            'column()
            Response.Write(dr.Item(1) & "<br/>")
        End While
        'close the SQLDataReader
        dr.Close()
        'Here it is closed
        Response.Write("<b>Is the SqlDataReader closed ?</b>" & _
        dr.IsClosed & "<br/>")
        'close the connection
        SqlConnection1.close()
    End Sub

The SQLDataReader retrieves the data from the SQL Server and, using the Response.Write() method, writes it to the output HTML as shown in the next picture.

SQLCommand for CommandType: Stored Procedure

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:

Stored Procedure with one input and one output parameter

This Stored Procedure YtdSales requires an input variable @title and the result of the query is returned in another variable @ytd_sales. Notice that the OUTPUT variable is clearly marked in the Stored Procedure. Also note the ParameterDirection attribute.

Create Procedure YtdSales
@title varchar(50), @ytd_sales int OUTPUT
as
Select @ytd_sales=ytd_sales
from titles
where title=@title
return

1. Establish a connection as before

2. Place a textbox to receive the input value, a TextBox to display the output and a button to initiate retrieval.

3. In the click event of the button insert the code shown in step 4.

4. In this case there is no need to loop the returned rows as there will be only one result returned.

   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 and define the SQLCommand to process a
        'stored procedure
        Dim cmd As New SqlClient.SqlCommand
        With cmd
            .Connection = SqlConnection1
            .CommandType = CommandType.StoredProcedure
            .CommandText = "YtdSales"
        End With
        'declare a new SQLParamter
        Dim param1 As New SqlClient.SqlParameter
        'add this parameter to the collection
        param1 = cmd.Parameters.Add _
        ("@title", SqlDbType.VarChar)
        'this parameter is the input
        param1.Direction = ParameterDirection.Input
        'declare another paramter
        Dim param2 As New SqlClient.SqlParameter
        'add this second parameter to the collection
        param2 = cmd.Parameters.Add _
        ("@ytd_sales", SqlDbType.Int)
        'this one is the output
        param2.Direction = ParameterDirection.Output
        'assign the input parameter
        cmd.Parameters("@title").Value = "Is Anger the Enemy?"

        Dim dr As SqlClient.SqlDataReader
        'Perform ExecuteReader()
        dr = cmd.ExecuteReader()
        '
        TextBox1.Text = param1.Value
          TextBox2.Text = param2.Value
        Response.Write(cmd.Parameters.Count())
    End Sub

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

SQLCommand's ExecuteXMLReader() Method

XMLReader and XMLWriter belong to the Abstract base classes. The Class View of XMLReader is shown in the next screen shot. While XMLReader reads though XMLTextReader and XMLNodeReader, the XMLWriter only writes using the XMLTextWriter.

• System.XML
  • XMLReader
    • XMLTextReader
    • XMLNodeReader
  • XMLWriter
    • XMLTextWriter

When the SQLCommand executes, the XMLReader will read a stream of data coming in the form of XML. This XML stream is produced by the SQL Query sent to the server to retrieve data in the form of an XML document using the For XML(Raw|auto|explicit) clause. The SQLStatement as shown here is for the For XML auto case. Please refer to SQL Server book on line [BOL] for the other clauses. The data, or rows returned by such a query are read by the SQLDataReader:

SELECT CustomerID, CompanyName,
       City, PostalCode, Phone
FROM  Customers FOR xml auto

The code shown here connects to an SQL Server through an SQLConnection. An SQLCommand is issued against the database using an SQLQuery fashioned to retrieve the XML document stream.

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

        Dim SQLConnection1 = New SqlClient.SqlConnection
        Dim strCon As String
        strCon = "workstation id=XPHTEK;packet size=4096;" & _
        "integrated security=SSPI;data source=XPHTEK;" & _
        "persist security info=False;initial catalog=Northwind"
        SQLConnection1.connectionstring = strCon
        SQLConnection1.Open()
        Dim SQLCmd As New SqlClient.SqlCommand
        SQLCmd.Connection = SQLConnection1
        SQLCmd.CommandType = CommandType.Text
        SQLCmd.CommandTimeout = 20
        SQLCmd.CommandText = "SELECT CustomerID, " & _
        "CompanyName, City, PostalCode, Phone " & _
        "FROM Customers FOR xml auto"
        Dim myXmlReader As System.Xml.XmlReader = _
        SQLCmd.ExecuteXmlReader()
        Response.Write("<p> </p><br/>")
        Response.Write("<table border='1'>")

        While myXmlReader.Read
     Response.Write("<tr><td>" & _
           myXmlReader.Item("CustomerID") & " " & _
           myXmlReader.Item("CompanyName") & _
           myXmlReader.Item("City") & "" & _
           myXmlReader.Item("PostalCode") & "</td></tr>")
        End While
        Response.Write("</table>")
        myXmlReader.Close()
        SQLConnection1.Close()
    End Sub

The following shows a sample of the records retrieved by the XMLReader. This is read into a single cell of an HTML table using the Response object's write() method.

Conclusions

For situations requiring the read only type, the fastest data access is by using the SQL native provider, using an SQLDataReader while retrieving data from an SQL Server (version 7.0 and above). It is also possible to connect to SQL Server using ODBC or OleDB. The analogous types of "DataReaders" can also be used, but there is an overhead involved due to the intermediary, be it ODBC or OleDb.

For some cases, as in connecting to OLAP cubes for data retrieval, or connecting to an earlier version of SQL Server, it may be necessary to use the OleDB provider. Also, for situations which require commands against the table requiring "TableDirect" commands, OleDb has to be used. Although all examples shown here are for the SQL native, similar processing can be carried out using the appropriate drivers for the other types. Moreover, the Ado.net objects are used the same way, whether it is a Windows application or a Web application.

Finally, this article is self-contained and complete. The main focus of this article is to understand the functionality of the SQLDataReader, and other necessary coding like exception handling is not intentionally implemented. The author may be reached via DevShed or direct email, mysorian