This tutorial will show you how to create your own Data Access Component and also how to display the time it takes to retrieve data from a SQL database. VB version.
Looking for the C#.NET 2008 Version? Click Here!
Looking for more .NET Tutorials? Click Here!
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.
Using Visual Studio.NET 2008, we can build our own Data Access Components to work with and manipulate data. In this tutorial, we will look at how we can do this, and how we can also retrieve the duration of data retrieval, which can be useful when dealing with large amounts of data. For this example, we will be working with a SQL database, and we will be creating 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.
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 |
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
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 |
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 |
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
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 |
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
Looking for the C#.NET 2008 Version? Click Here!
Looking for more .NET Tutorials? Click Here!
|