Google Search

Friday

DataReader and Afterwards

Which one is Faster DATABINDER? DataAdapter-CombinedWith-Dataset or Data Reader?

DataReader is much faster than DataAdapter-Dataset. By the way, DataReader is Readonly as well as forward only.

To further improve the performance, once the DataReader has read the data, off load the data to .... and close the DataReader as well as DataConnection.

If you dont close the DatabaseConnection, then, Mr.DataReader will hold that connection, which leads to scalability problems when the number of users are more. For details about this scalabitity is available here.http://msdn.microsoft.com/en-us/library/ms978388.aspx

The Next Question is : Where this DataReader will offload its data?

There are two places. ARRAYLIST,DATATABLE-CUM-DATAVIEW

I am using the following code for ArrayList: Further details about this code is available here:http://www.developerfusion.com/article/5292/datagridgridview-paging-and-sorting-using-a-datareader/3/

<%@ Page Language="VB" Debug="False" Strict="True" Explicit="True" Buffer="True" Trace="False" %>
<%@ Import Namespace="System" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %>
<html><head></head><body bgcolor="#FFFFFF" topmargin="0" marginheight="0"><script language="VB" runat="server">
'Set up the variables for our ArrayLists
Dim dgCache, bkResults As ArrayList

Sub Page_Load (Source As Object, E As EventArgs)
If Not Page.IsPostBack Then
'Reset DataGrid Page to the top
MyDataGrid.CurrentPageIndex = 0
'Check if ViewSate is already populated
If IsNothing(ViewState("SortOrder")) then
'Assign default column sort order
GetDataReader ("SupplierID asc")
Else
'Else retrieve sort order from ViewState
GetDataReader (ViewState("SortOrder").ToString())
End If
End If
End Sub


Public Structure DBInfo 'Lightweight Class to hold our data results

Private _SupplierID As Object
Private _CompanyName As String
Private _ContactName As String
Private _Country As String

'Constructor
Public Sub New (ByVal SupplierID As Object, ByVal CompanyName As String, _
ByVal ContactName As String, ByVal Country As String)

_SupplierID = SupplierID
_CompanyName = CompanyName
_ContactName = ContactName
_Country = Country

End Sub


'All relevant public properties based on database columns
Public Property SupplierID() As Object
Get
Return _SupplierID
End Get
Set (ByVal Value As Object)
_SupplierID = Value
End Set
End Property

Public Property CompanyName() As String
Get
Return _CompanyName
End Get
Set (ByVal Value As String)
_CompanyName = Value
End Set
End Property

Public Property ContactName() As String
Get
Return _ContactName
End Get
Set (ByVal Value As String)
_ContactName = Value
End Set
End Property

Public Property Country() As String
Get
Return _Country
End Get
Set (ByVal Value As String)
_Country = Value
End Set
End Property

End Structure 'DBinfo Structure


Function SortOrder (Field As String) As String

If Field = ViewState("SortOrder").ToString() Then
SortOrder = Replace (Field,"asc","desc")
Else
SortOrder = Replace (Field,"desc","asc")
End If

End Function


Sub GetDataReader(ColumnOrder As String)

'Assign ColumnOrder to ViewState
ViewState("SortOrder") = ColumnOrder

'Set up Cache Object and determine if it exists
dgCache = CType(Cache.Get("dgCache" & ColumnOrder), ArrayList)

If (dgCache Is Nothing) Then

Dim sqlStr As String = "SELECT SupplierID, CompanyName, "_
& "ContactName, Country FROM Suppliers Order by " & ColumnOrder
Dim strConn As String = "server=(local);uid=sa;pwd=;database=Northwind;"
Dim MyConnection As New SQLConnection (strConn)

MyConnection.Open()

Dim MyCommand As New SQLCommand(sqlStr, MyConnection)
Dim objDataReader As SQLDataReader = MyCommand.ExecuteReader (CommandBehavior.CloseConnection)
'Create instances of the class,
Dim bkResults as New ArrayList()

'Loop through DataReader
While objDataReader.Read()

With bkResults

'and then add the instances to the ArrayList
.Add (New DBInfo (objDataReader.GetInt32(0), _
objDataReader.GetString(1), _
objDataReader.GetString(2), _
objDataReader.GetString(3)))
End With
End While

'Insert ArrayList into Cache Object with unique identifier
Cache.Insert ("dgCache" & ColumnOrder, bkResults)
'Close DataReader Connection
objDataReader.Close()
'Bind DataGrid from ArrayList
MyDataGrid.DataSource = bkResults
Else
'Bind DataGrid from Cached ArrayList
MyDataGrid.DataSource = dgCache
End If

MyDataGrid.DataBind()

'Clear ArrayList
bkResults = Nothing
End Sub

Sub MyDataGrid_Page(sender As Object, e As DataGridPageChangedEventArgs)
MyDataGrid.CurrentPageIndex = e.NewPageIndex
GetDataReader (ViewState("SortOrder").ToString())
End Sub

Sub MyDataGrid_Sort(sender As Object, e As DataGridSortCommandEventArgs)
MyDataGrid.CurrentPageIndex = 0
GetDataReader(SortOrder(e.SortExpression.ToString()))
End Sub

</script>
<br><br>
<b>Dynamic DataGrid Paging and Sorting Using A DataReader Bound ArrayList</b>
<br>
<form runat="server">

<%= "Page: " & MyDataGrid.CurrentPageIndex+1 & " of " & (MyDataGrid.PageCount) %>
<br><br>

<ASP:DataGrid id="MyDataGrid" runat="server"

AutoGenerateColumns="False"
AllowPaging="True"
AllowCustomPaging="False"
AllowSorting="True"
PageSize="10"
PagerStyle-Mode="NumericPages"
PagerStyle-HorizontalAlign="Right"
PagerStyle-NextPageText="Next"
PagerStyle-PrevPageText="Prev"
BorderColor="black"
BorderWidth="1"
GridLines="Both"
CellPadding="3"
CellSpacing="0"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor="#aaaadd"
AlternatingItemStyle-BackColor="#eeeeee"
Width="700px"
OnPageIndexChanged="MyDataGrid_Page"
OnSortCommand="MyDataGrid_Sort" >
<Columns>
<asp:BoundColumn DataField="SupplierID" SortExpression="SupplierID asc" HeaderText="SupplierID"/>
<asp:BoundColumn DataField="CompanyName" SortExpression="CompanyName asc"
HeaderText="CompanyName"/>
<asp:BoundColumn DataField="ContactName" HeaderText="ContactName"
SortExpression="ContactName asc"/>
<asp:BoundColumn DataField="Country" HeaderText="Country"
SortExpression="Country asc"/>
</Columns>
</asp:DataGrid>
</form>
</body>
</html>

 

 

 

 
Google Search