Tutorial RSS
 
Navigator: Home - Retrieve - Building Data Access Components in ASP.NET 3.5 and VB

Building Data Access Components in ASP.NET 3.5 and VB

This tutorial will show you how we can create our own class to retrieve data from a database, using the ObjectDataSource control, in VB.


I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.


Looking for the C#.NET Version? Click here!

Looking for more .NET Database Tutorials? Click Here!

ASP.NET is full of controls and classes to make interacting with our data easier. However, ASP.NET also gives us the ability to create our own classes to interact and manipulate our data.
In this tutorial, you will learn how to create your own Data Access Component. We will simply be displaying data from our database, but to do this, we will write our own class that will retrieve the data from a SQL database.

We will start by creating our SQL database - we will have one table with three columns - id, name and city. Once we have our database structure, we can add some sample data, and then we can start work on our web application.

To display the data, we will be using a DataGrid and an ObjectDataSource to manage our data source. We can always display the data on a button click to see how it performs the request. The following is what the ASPX page will look like:

<form id="form1" runat="server">
<asp:Button ID="butGetData" runat="server" Text="Get Data"
onclick="butGetData_Click" /><br /><br />

<asp:DataGrid ID="DataGrid1" runat="server" />

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="People"
SelectMethod="GetAll" />
</form>


Notice that we have TypeName and SelectMethod attributes on the ObjectDataSource. These refer to our Class, which we can now write. It will look something like this:

Private Shared ReadOnly _connectionString As String

Private _name As String
Private _city 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 City() As String
Get
Return _city
End Get
Set(ByVal value As String)
_city = value
End Set
End Property

Public Function GetAll() As List(Of People)
Dim results As List(Of People) = New List(Of People)()
Dim conn As New SqlConnection(_connectionString)
Dim cmd As New SqlCommand("SELECT name,city FROM tblPeople", conn)
Using conn
conn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
Do While dr.Read()
Dim newPeople As New People()
newPeople.Name = CStr(dr("Name"))
newPeople.City = CStr(dr("City"))
results.Add(newPeople)
Loop
End Using
Return results
End Function

Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
End Sub

Try Server Intellect for Windows Server Hosting. Quality and Quantity!


For each column we wish to interact with, we create a Get and a Set, which allows us to both retrieve data and also set data. Our GetAll method is simply retrieving all data that is in the database and storing in a List, which we then return to the ObjectDataSource.
The entire Class will look something like this:

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Web.Configuration

Public Class People
Private Shared ReadOnly _connectionString As String

Private _name As String
Private _city 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 City() As String
Get
Return _city
End Get
Set(ByVal value As String)
_city = value
End Set
End Property

Public Function GetAll() As List(Of People)
Dim results As List(Of People) = New List(Of People)()
Dim conn As New SqlConnection(_connectionString)
Dim cmd As New SqlCommand("SELECT name,city FROM tblPeople", conn)
Using conn
conn.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
Do While dr.Read()
Dim newPeople As New People()
newPeople.Name = CStr(dr("Name"))
newPeople.City = CStr(dr("City"))
results.Add(newPeople)
Loop
End Using
Return results
End Function

Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
End Sub
End Class


Finally, we can add logic to the button on the back-end of our ASPX page:

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub butGetData_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles butGetData.Click
DataGrid1.DataSourceID = "ObjectDataSource1"
DataGrid1.DataBind()
End Sub
End Class


If we now run this web application, we will be able to retrieve our data from the database with the click of a button.


Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!




Looking for the C#.NET Version? Click here!

Looking for more .NET Database Tutorials? Click Here!
Download Project Source - Enter your Email to be emailed a link to download the Full Source Project used in this Tutorial!



100% SPAM FREE! We will never sell or rent your email address!

411asp.net123aspxDotNetFreaksServer Intellect