S q l D a t a R e a d e r    

By Michael McIntyre

mikemc@getdotnetcode.com

The SqlDataReader provides a means of reading a forward-only stream of rows from a SQL Server database.

What if you need to know what’s in the “forward-only stream of rows” before you process them with your program? For this you can use the SqlDataReader’s FieldCount property, GetTypeName method, and GetFieldType methods.

FieldCount provides the number of columns in the stream of rows. GetTypeName provides the Sql data type of a column.  GetFieldType provides the .NET Framework data type.

Example Code

' Declare and instantiate a SqlConnection.

Dim aSqlConnection As New SqlClient.SqlConnection()

' Assign a connection string to aSqlConnection.

aSqlConnection.ConnectionString = _

        "Server=(local)\NetSDK;" & _

        "DataBase=northwind;" & _

        "Integrated Security=SSPI"

' Open the SqlConnection.

aSqlConnection.Open()

' Declare and instantiate a SqlCommand.

Dim aSqlCommand As New SqlCommand()

' Assign a SqlConnection to the SqlCommand object.

aSqlCommand.Connection = aSqlConnection

' Assign a SQL command string to the SqlCommand object.

aSqlCommand.CommandText = "SELECT * FROM Products"

' Declare a SqlDataReader.

' Call the SqlCommand's ExecuteReader method and

' assign the resulting stream Sql rows to the aSqlDataReader variable.

Dim aSqlDataReader As SqlDataReader = aSqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

' Declare a variable named numberOfFields of type integer.

' Assign the value of the SqlDataReader's FieldCount property value to it.

Dim numberOfFields As Integer = aSqlDataReader.FieldCount

' Report the name, Sql data type and .NET type of

' each column in row stream.

Dim i As Integer

For i = 0 To numberOfFields - 1

     ' Write Name of field, Sql Data Type, and .NET DataType.

     Console.WriteLine(aSqlDataReader.GetName(i) & " : " & _

     aSqlDataReader.GetDataTypeName(i).ToString & " : " & _

     aSqlDataReader.GetFieldType(i).ToString)

Next

' Close the connection.

aSqlConnection.Close()

Result:

ProductID : int : System.Int32

ProductName : nvarchar : System.String

SupplierID : int : System.Int32

CategoryID : int : System.Int32

QuantityPerUnit : nvarchar : System.String

UnitPrice : money : System.Decimal

UnitsInStock : smallint : System.Int16

UnitsOnOrder : smallint : System.Int16

ReorderLevel : smallint : System.Int16

Discontinued : bit : System.Boolean

Learn more about the SqlDataReader by clicking here.

Copyright © 2001-2004 aZ Software Developers. All rights reserved.