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 you to delete records from the database.
In addition to creating our own Data Access Component to read data from our SQL database, we can also add a method to delete records from the database. In this tutorial, you will learn how to create a Data Access Component that will both read and display data from a SQL database and that also has the ability to delete records as well.
In this example, we will be using a SQL database with one table that has three columns - id, name and age. We will write a Data Access Component to access this data and use a GridView to display it. We will also add the functionality to delete records from the database. Finally, we will be adding AJAX to increase the usability of the system.
Once we have our database set up, we will begin to write the class. To read data from the database, we will need something like this:
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 |
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.
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 deleting data from the database, we will need to add another method, which will look something like this:
Public Sub Delete(ByVal id As Integer)
Dim con As New SqlConnection(_connectionString) Dim cmd As New SqlCommand("DELETE tblPeople WHERE Id=@Id", con) cmd.Parameters.AddWithValue("@Id", id) Using con
con.Open()
cmd.ExecuteNonQuery() End Using |
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 method uses SQL statements to delete database records with the id variable passed 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 Delete(ByVal id As Integer)
Dim con As New SqlConnection(_connectionString) Dim cmd As New SqlCommand("DELETE tblPeople WHERE Id=@Id", con) cmd.Parameters.AddWithValue("@Id", id) 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 |
This concludes our writing of the class. Now we just need to implement it into the ASPX page.
To do this, all we will do is include two controls: GridView and ObjectDataSource. By specifying a few attributes of these controls, we will unlock the functionality of the class we just created. The ASPX page will look something like this:
<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
DataKeyNames="id" AutoGenerateDeleteButton="true" Width="370px" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="People"
SelectMethod="GetAll" DeleteMethod="Delete" /> |
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.
As a final touch, we will implement AJAX to create a desktop feel to the application. By adding a ScriptManager and an UpdatePanel control, we will hijack the postback and reload just the GridView and DataSource. The ASPX page will now look something like this:
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" />
<asp:UpdatePanel ID="UpdateManager1" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
DataKeyNames="id" AutoGenerateDeleteButton="true" Width="370px" /> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="People"
SelectMethod="GetAll" DeleteMethod="Delete" />
</ContentTemplate>
</asp:UpdatePanel>
</form> |
Looking for the Visual Studio.NET 2008 C#.NET version? Click here!
Looking for more .NET Tutorials? Click Here!