Click here to Skip to main content
16,017,100 members
Articles / Web Development / ASP.NET
Article

Using SQLDataReader and SQLDataAdapter

Rate me:
Please Sign up or sign in to vote.
3.25/5 (12 votes)
19 Mar 20063 min read 144.8K   769   24   7
retrieve

Sample Image - RetrieveMoreData.gif

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Technical Lead BEAT
Jordan Jordan
Iyad Abu Abdu is currently a technical team leader at Blue Energy for Advanced Technologies (Jordan),he works in architecting and planning for enterprise solutions using different entities such as MOSS 2007, K2 blackPearl, .Net and MS SQL Server.

To contact Iyad,email him at iyad.abuabdu@gmail.com

Comments and Discussions

 
GeneralADO Connection String Pin
VenkateshSaNaTa17-Apr-08 22:06
professionalVenkateshSaNaTa17-Apr-08 22:06 
GeneralSome Thoughts Pin
renzea22-Mar-06 10:03
renzea22-Mar-06 10:03 
GeneralRe: Some Thoughts Pin
AnasHashki22-Mar-06 21:02
AnasHashki22-Mar-06 21:02 
Thanks a lot it's very helpful
Wink | ;)

AMH
LIFE'S SHORT. If you don't look around once in a while you might miss it

GeneralRe: Some Thoughts Pin
Iyad Abu Abdu22-Mar-06 22:09
Iyad Abu Abdu22-Mar-06 22:09 
GeneralRe: Some Thoughts Pin
renzea23-Mar-06 9:09
renzea23-Mar-06 9:09 
GeneralRe: Some Thoughts Pin
Darth Jackass12-May-06 4:15
Darth Jackass12-May-06 4:15 
Generalgood Pin
yazan_nemer4-Mar-06 20:41
yazan_nemer4-Mar-06 20:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.