This tutorial will show you how to use VB and AJAX to create a Data Access Component that will display data from a SQL database and also allow edits of the data.
Using Visual Studio.NET to manage and manipulate data can save us a lot of time. VS.NET ships with built-in tools making it extremely easy for us to work with data sources. However, it also allows us to create our own Data Access Components, allowing us more control.
In this tutorial you will learn how to create a Data Access Component (DAC) and how we can write a method that will allow us to update the records within the database using a FormView control. Instead of using the built-in Update function, we will be writing our own to demonstrate the control we have.
Before we do anything else, we need a database. In this example, we will be working with a SQL database with one table, which has three columns - id, name and age. Once we have set up our database, we will add some sample records to work with. If you already have a database you wish to work with, then great.
The first thing we want to do is to create our class. This will handle all the interaction with our database - reading and writing. We will need to write a method for reading the data, and then a method for updating records. We will start off with reading the database:
Private Shared ReadOnly _connectionString As String
Private _id As Integer
Private _name As String
Private _age As String
Public Property Id() As Integer
Get
Return _id End Get Set(ByVal value As Integer)
_id = value End Set
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() 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 id,name,age FROM tblPeople", con) Using con
con.Open() Dim dr As SqlDataReader = cmd.ExecuteReader() Do While dr.Read()
Dim newPerson As New People()
newPerson.Id = CInt(Fix(dr("Id")))
newPerson.Name = CStr(dr("Name"))
newPerson.Age = CStr(dr("Age"))
results.Add(newPerson) Loop End Using 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.
At present, the above code is just for retrieving the data from the database. We use a List collection to gather all records from the database, and then return to the object that calls the method. In this case, we will use the ObjectDataSource to call the method, but that's a little later in the ASPX code. Because we want to add the functionality of adding data to the database, we will need to add another method, which will look something like this:
Public Sub Update(ByVal id As Integer, ByVal name As String, ByVal age As String)
Dim con As New SqlConnection(_connectionString) Dim cmd As New SqlCommand("UPDATE tblPeople SET name=@name,age=@age WHERE id=@id", con) cmd.Parameters.AddWithValue("@id", id) cmd.Parameters.AddWithValue("@name", name) cmd.Parameters.AddWithValue("@age", age) Using con
con.Open()
cmd.ExecuteNonQuery() End Using |
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
This method uses SQL statements to update the database record with variables to it from whatever calls it. In this example, the ObjectDataSource will be calling it, which we will get to a little later.
So the entire code-behind for the class looks something like this:
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Web.Configuration
Public Class People
Private Shared ReadOnly _connectionString As String Private _id As Integer Private _name As String Private _age As String Public Property Id() As Integer
Get
Return _id End Get Set(ByVal value As Integer)
_id = value End Set End Property 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 Sub Update(ByVal id As Integer, ByVal name As String, ByVal age As String)
Dim con As New SqlConnection(_connectionString) Dim cmd As New SqlCommand("UPDATE tblPeople SET name=@name,age=@age WHERE id=@id", con) cmd.Parameters.AddWithValue("@id", id) cmd.Parameters.AddWithValue("@name", name) cmd.Parameters.AddWithValue("@age", age) Using con
con.Open()
cmd.ExecuteNonQuery() End Using End Sub Public Function GetAll() 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 id,name,age FROM tblPeople", con) Using con
con.Open() Dim dr As SqlDataReader = cmd.ExecuteReader() Do While dr.Read()
Dim newPerson As New People()
newPerson.Id = CInt(Fix(dr("Id")))
newPerson.Name = CStr(dr("Name"))
newPerson.Age = CStr(dr("Age"))
results.Add(newPerson) Loop End Using Return results End Function Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings("connectionString").ConnectionString End Sub |
Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!
We are now done writing the class. Next up is to make use of it in the ASPX page.
To achieve this, all we need to do is include two controls: GridView and ObjectDataSource. Notice we specify the attributes of the class name (TypeName) and the method names we just created (SelectMethod: GetAll; and UpdateMetohd: Update). The ASPX page will look something like this:
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
DataKeyNames="id" AutoGenerateEditButton="true" Width="370px" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="People"
SelectMethod="GetAll" UpdateMethod="Update" /> |
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
We can improve the code further by simply adding a ScriptManager and an UpdatePanel to the page. The UpdatePanel will hijack the postback request and only refresh what is within the ContentTemplate, thus only reloading the data and not the entire page. This makes for a much more user-friendly interface.
The ASPX page will look something like this:
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" /> <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate>
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
DataKeyNames="id" AutoGenerateEditButton="true" Width="370px" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="People"
SelectMethod="GetAll" UpdateMethod="Update" /> </ContentTemplate> </asp:UpdatePanel> |
Looking for the Visual Studio.NET 2008 C#.NET version? Click here!
Looking for more .NET Tutorials? Click Here!