Using SQLDataReader and SQLDataAdapter

• Download source files - 36 Kb

Introduction

    The useability and the interaction for the end users with your web form will reflect the term of the success and the completeness for your work,and especially if most of your web forms get and show data from database. we talk to database via a connection ,so your connection will be in the lightweight and have a short time and this dedicated in using SqlDataReader class and your connection will be more efficient in using SQLDataAdapter class.

Background (optional)

DataReader and DataAdapter are both objects of ADO.Net,which enable you to access your database.DataReader is a storage object which dedicated for read only,it has a collection of methods that support SqlDataReader like ExecuteReader which move the command text to the connection instead of using SqlDataAdapter.
Besides that we can use SqlDataAdapter class for read and write data from and to database,i represent SqlDataAdapter like the taxi which reach people from one place to another and come back.SqlDataAdapter do the same but it hold Query statement(select statement)and it will come back to fill data in DataSet or non Query statements (insert,delete and update)and it will come back with the number of the affected rows.

Using the code

     i had created a computer database called library using SQL Server and a table called Books and i have three fields :
1.ISBN
2.Book Title
3.Book Category

    i have a dropdownlist and set AutoPostBack property to True,for display more details about the selected ISBN i have three TextBoxes the first is to display the selected ISBN ,the second TextBox for display the Book Title and the third to display the Book Category. then i retrieve the data and put it in TextBoxes ,i had choosen TextBoxes better than Labels to display data ,i made a right click on each TextBox then set the ReadOnly property to True and this nice thing to have.
i call System.Data.SqlClient Namespace to define what i need in this example for my connection requirements.

    Imports System.Data.SqlClient
Public Class RetrieveMoreData
    Inherits System.Web.UI.Page
//...    
    Dim channel As New SqlConnection
    Dim dataquery As New SqlCommand
    Dim dataholder As New SqlDataAdapter
    Dim ds As New DataSet
    Dim datareader As SqlDataReader
    Dim dv As New DataView   

on the page load i called GetChannel function which satisfy the connection Requirements and FillDdl function which fill the dropdownlist.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'Put user code to initialize the page here
    Try
        If Not IsPostBack Then
            GetChannel()
            FillDdl()
        End If
    Catch ex As Exception
        Response.Write("an error occured")
    End Try
End Sub

in GetChannel function you can satisfy your ConnectionString.

Private Sub GetChannel()
     Try
         channel.ConnectionString = "workstation id=COMPUTER NAME;packet size=4096;user id=SQL SERVER USER ID;_
         integrated security=SSPI;data source=COMPUTER NAME;persist security info=False;initial catalog=DATABASE NAME"
     Catch ex As Exception
         Response.Write("an error occured")
     End Try
 End Sub

In FillDdl function i opened the connection and moved the CommandText to the connection by datareader instead of SqlDataAdapter,IsDBNull is a function returns True if no available data and returns False if data is available,in case IsDBNull is False datareader get the value,after that i add it to the dropdownlist

<PRE> Private Sub FillDdl()
        Try
            channel.Open()
            dataquery.CommandText = "select * from Books"
            dataquery.Connection = channel
            datareader = dataquery.ExecuteReader   //datareader Read One Row at a time
            ddlGetISBN.Items.Add("Select ISBN")     //add a default item in dropdownlist
            While datareader.Read            //datareader enter in a loop while the data is available 
                If Not datareader.IsDBNull(0) Then
                    ddlGetISBN.Items.Add(datareader.GetValue(0).ToString)
                End If
            End While
            channel.Close()
        Catch ex As Exception
            Response.Write("an error occured")
        End Try
    End Sub

i used SqlDataAdapter here to move the command text to the connection,after that i had filled the retrieving data in the dataset,i tooke a custom view for the DataSet then filter it by dv with isbn variable which selected from the dropdownlist after that i convert it to ToInt64 (64-bit signed integer) because ISBN consist of 10 digits,then i filled the TextBoxes whith the particular data,i call ShowDetails function on the SelectedIndexChanged event for the dropdownlist.

//..
    Private Sub ShowDetails()
        Try
            GetChannel()
            channel.Open()
            dataquery.CommandText = "select * from Books"
            dataquery.Connection = channel
            dataholder.SelectCommand = dataquery
            dataholder.Fill(ds, ("Books"))
            dv.Table = ds.Tables("Books")     //take a custom view for Books Table from the DataSet
            dv.RowFilter = "isbn=" & System.Convert.ToInt64(ddlGetISBN.SelectedItem.Text)
            txtISBN.Text = dv(0)("ISBN")
            txtBookTitle.Text = dv(0)("Book Title")
            txtBookCategory.Text = dv(0)("Book Category")
            channel.close()           
        Catch ex As Exception 
            Response.Write("an error occured")       
        End Try       
    End Sub

here i call ShowDetails function and after the process finished i clear the selection by ClearSelection method in the dropdownlist and it doesn't useful in this case but if you have a user control and you want to manipulate it from dropdownlist selection,too you want to repeat the same selection twice or more you can use ClearSelection method as the best solution for the mentioned cases.

Private Sub ddlGetISBN_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)Handles ddlGetISBN.SelectedIndexChanged
     Try
         ShowDetails()
         ddlGetISBN.ClearSelection()
     Catch ex As Exception
         Response.Write("an error occured")
     End Try
 End Sub