Return a Forward-Only Data Stream in ADO.NET

Category:

C#, VB.NET, ASP.NET

Type:

Snippets

Difficulty:

Beginning

Author:

Intelligent Solutions Inc.

Version Compatibility: Visual Basic.NET

More information:

This snippet illustrates use of ADO.NET's ADOConnection, ADOCommand, and ADODataReaders objects. The ADOConnection and ADOCommand objects are similar to the Connection and Command objects in ADO; the ADODataReader provides a way of reading a forward-only stream of data and is therefore similar to a forward-only recordset in ADO.

This snippet is similar in functionality to the one at http://www.freevbcode.com/ShowCode.Asp?ID=1257, which shows how to open a DSN-Less connection in ADO.

Originally developed for beta versions;verified to work in .NET version 1.0, 04/02/02.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
'Also Add reference to System Data Imports System.Data.OleDb

Code:

Dim objCommand As OleDbCommand
Dim objConnection As New OleDbConnection()

'A OLEDBDataReader is an object that provides a 
'forward only stream of data, like a
'forward-only recordset in ADO.

Dim objDR As OleDbDataReader
Dim objRow As DataRow

Dim sSQL As String
Dim intColumnCount As Integer
Dim intCtr As Integer
'Change to your table name
sSQL = "SELECT * FROM MYTABLE"
Try
    ' FOR SQL SERVER USE SOMETHING LIKE THIS
    'Data Source = Server Name
    'Initial Catalog = Database
    'Use your own user names and password

objConnection.ConnectionString = _
"Provider=SQLOLEDB.1;User ID=sa;password=mypassword;Initial Catalog=MyDatabase;Data Source = MySQLServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

'FOR ACCESS USE SOMETHING LIKE THIS:
    'Change Data Source to full path of database

objConnection.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDatabase.mdb"

objConnection.Open()
    objCommand = New OleDbCommand(sSQL, objConnection)
    objCommand.CommandType = CommandType.Text
    objDR = objCommand.ExecuteReader

intColumnCount = objDR.FieldCount

'Loop through each row
    Do While objDR.Read
For intCtr = 0 To intColumnCount - 1
    'Get the Value of each column
   If Not objDR.IsDBNull(intCtr) Then
    Debug.WriteLine(objDR.Item(intCtr).ToString)
   End If
Next
    Loop

objDR.Close()

Catch Ex As Exception
    'Excpetion Handler
    Debug.WriteLine(Ex.Message)
Finally

'Will execute whether or not an exception
    'occurs

If objConnection.State = ConnectionState.Open Then
          objConnection.Close()
    End If

End Try