Wednesday, 21 September 2011

Retrieving Data Using the DataReader

You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Using the
DataReader can increase application performance both by retrieving data as soon as it is available, rather than waiting for the entire results of the query to be returned, and (by default) storing only one row at a time in memory, reducing system overhead.

The DataReader class in .NET provides similar functions to SQL Cursors, which are actually not supported in the .NET Framework. DataReades are used to efficiently retrieve a forward-only stream of data from a database. DataReaders are appropriate when the need is to simply display the result set, as only one record at a time is ever present in memory. The DataReader is mainly used in scenarios wherein data need not be updateable nor should persist across multiple requests.

Sample Code (with MsSql)
You must import SqlClient .

Imports System.Data.SqlClient

Dim sConnection As String = "server=(local);uid=sa;pwd=PassWord;database=DatabaseName"

Dim objCommand As New SqlCommand
objCommand.CommandText = "Select * From tablename"
objCommand.Connection = New SqlConnection(sConnection)

Dim objDataReader As SqlDataReader = objCommand.ExecuteReader()

If objDataReader.HasRows Then
Do While objDataReader.Read()
Console.WriteLine(" Your name is: " & Convert.ToString(objDataReader(0)))
Console.WriteLine("No rows returned.")
End If



Dim connString As String = "server=s; database=techarena;" + _
                                   "uid=sa; pwd="
        Dim conn As New SqlConnection(connString)
        Dim cmdString As String = "select * from author"
        Dim cmd As New SqlCommand(cmdString, conn)
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        While reader.Read()
            txtData.Text += reader("authorId").ToString()
            txtData.Text += ", "
            txtData.Text += reader("name").ToString()
            txtData.Text += vbCrLf
        End While