This tutorial will show you how to build your own
Data Access Component and how to retrieve the time taken to execute. VB
version.
Building DAC with Execution Time in ASP.NET 3.5 and VB
This tutorial will show you how to build your own
Data Access Component and how to retrieve the time taken to execute. VB
version.
Instead of using ASP.NET's built-in controls to retrieve our
data, we can build our own Data Access Components. In this tutorial, we
will look at how we can do this, and how we can also retrieve the
duration of data retrieval. This can be useful when dealing with large
amounts of data. For this example, we will be working with a SQL
database, and create our own class to retrieve the data. We will be
using the Object Data Source to interact with our class, and a GridView
to display the data.
The database we will be working with will have just one table, and
three columns - id, name and age. Once created, we will add some sample
data to use.
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
When our database is ready to go, we can start building our class
that will retrieve data from our database. The class will have a method
to retrieve all records from the database and input into a List. Our
class will look something like this:
Private Shared ReadOnly _connectionString As String
Private _name As String
Private _age As String
Public Property Name() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
Public Property Age() As String
Get
Return _age
End Get
Set(ByVal value As String)
_age = value
End Set
Public Function GetAll(<System.Runtime.InteropServices.Out()> ByRef execTime As Long) As List(Of People)
Dim results As List(Of People) = New List(Of People)()
Dim con As New SqlConnection(_connectionString)
Dim cmd As New SqlCommand("SELECT Name,Age FROM tblPeople", con)
con.StatisticsEnabled = True
Using con
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
Do While dr.Read()
Dim newPerson As New People()
newPerson.Name = CStr(dr("Name"))
newPerson.Age = CStr(dr("Age"))
results.Add(newPerson)
Loop
End Using
Dim stats As IDictionary = con.RetrieveStatistics()
execTime = CLng(Fix(stats("ExecutionTime")))
Return results
Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
End Sub |
The GetAll method uses a List to collect all data from the database
and then using a loop, we add each record from the database into the
results List we created. This is the List that is then returned. We
will use this method to select data, using the ObjectDataSource. We can
now build our ASPX page like so:
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
Width="377px" />
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
TypeName="People" SelectMethod="GetAll" OnSelected="ObjectDataSource1_Selected">
<SelectParameters>
<asp:Parameter Name="execTime" Type="Int64" Direction="Output" />
</SelectParameters>
</asp:ObjectDataSource>
<br />
<br />
Time to retrieve data was: <asp:Label ID="lblStatus" runat="server" />.
|
Notice that we have assigned the GetAll method to our SelectMethod
attribute of our ObjectDataSource, and the TypeName is the name of the
Class. We also have a method that fires on the OnSelected event. This
means that when the ObjectDataSource selects data (through the class),
the following code is processed:
| Protected Sub ObjectDataSource1_Selected(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
lblStatus.Text = e.OutputParameters("execTime").ToString() & "ms"
End Sub |
We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers
and add-ons were setup swiftly, in less than 24 hours. We were able to
confirm our order over the phone. They respond to our inquiries within
an hour. Server Intellect's customer support and assistance are the best we've ever experienced.
This is where we output the time it took to retrieve the data.
The entire code-behind will look something like this:
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub ObjectDataSource1_Selected(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
lblStatus.Text = e.OutputParameters("execTime").ToString() & "ms"
End Sub
|
The entire code of the class is as follows:
Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Web.Configuration
Public Class People
Private Shared ReadOnly _connectionString As String
Private _name As String
Private _age As String
Public Property Name() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property
Public Property Age() As String
Get
Return _age
End Get
Set(ByVal value As String)
_age = value
End Set
End Property
Public Function GetAll(<System.Runtime.InteropServices.Out()> ByRef execTime As Long) As List(Of People)
Dim results As List(Of People) = New List(Of People)()
Dim con As New SqlConnection(_connectionString)
Dim cmd As New SqlCommand("SELECT Name,Age FROM tblPeople", con)
con.StatisticsEnabled = True
Using con
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
Do While dr.Read()
Dim newPerson As New People()
newPerson.Name = CStr(dr("Name"))
newPerson.Age = CStr(dr("Age"))
results.Add(newPerson)
Loop
End Using
Dim stats As IDictionary = con.RetrieveStatistics()
execTime = CLng(Fix(stats("ExecutionTime")))
Return results
End Function
Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
End Sub
|
|
