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
Private Sub FillDdl()
Try
channel.Open()
dataquery.CommandText = "select * from Books"
dataquery.Connection = channel
datareader = dataquery.ExecuteReader
ddlGetISBN.Items.Add("Select ISBN")
While datareader.Read
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")
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