ADO.NET 101: SqlDataReader - SQLCommand for CommandType: SQL Statement

(Page 4 of 7 )

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.

Next: SQLCommand for CommandType: Stored Procedure >>
 

More Database Articles
More By Jayaram Krishnaswamy

Comments On: ADO.NET 101: SqlDataReader

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