This tutorial will show you how to retrieve a Customer Object from a SQL Database using only the ID reference. VB.NET
In ASP.NET, it is extremely easy for us to create our own classes and objects to organize and manipulate data. In this tutorial, we will be looking at true object-orientated programming, and how we can retrieve custom objects from the database. We will be creating a new class that represents an object we store in the database, and then use that class to get a record from the database. For this example, we will use a table of cars, with columns ID, Make, and Model. First, let's creat our SQL Database. Create a new ASP.NET Website in Visual Studio.NET 2008 and then right-click your App_Data folder in Solution Explorer and choose Add New Item.. SQL Server Database. You should then see the database open in the Server Explorer window. When you do, right-click the Tables folder and choose to Add New Table.
Create three columns: ID , int; Make, varchar(50); and Model, varchar(50).
We will also want to set the ID as Primary Key and Identity Specification (in Properties).
Once complete, save the Table and close it. Then go back into Server Explorer and right-click our new table, choose Show Table Data. Now we can add some sample data, like VW Jetta, Chevrolet Suburban, and Ford Mustang. The IDs will auto-populate when the record is inserted. Now we have some sample data to work with, you can close the table once again.
Before moving on, let's be done with database and add the Connection String to the Web.config. It should look something like this:
|
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Cars.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>
|
If you need help with the Connection String, see ConnectionString.com
Let's now move onto the Class. If you don't have an App_Code folder in your Solution Explorer, right-click the project name and choose Add ASP.NET Folder > App_Code. Then right-click the App_Code folder and choose Add New Item.. Class. Name it Car.vb and click Ok. You should be presented with the default class. What we want to do is wrap it in a namespace like so:
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Namespace DBTutorials.Car
Public Class Car
End Class
End Namespace
|
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
Doing this will make it easier to organize a lot of code, especially when grouping multiple Classes together. The namespace is not necessary; but is good practice.
Now to build our class, we are going to create Properties that mirror the columns of the Table we created earlier. To do this, we will add a Properties region below the constructor:
#Region "properties"
Public Property CarID() As Int32
Get
Return _CarID
End Get
Set(ByVal value As Int32)
_CarID = value
End Set
End Property
Private _CarID As Int32 = 0
Public Property Make() As String
Get
Return _Make
End Get
Set(ByVal value As String)
_Make = value
End Set
End Property
Private _Make As String = ""
Public Property Model() As String
Get
Return _Model
End Get
Set(ByVal value As String)
_Model = value
End Set
End Property
Private _Model As String = "" |
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
We make the Properties public so that they are accessible outside of the class, and we also set the default values within the class.
Below the Properties, we create another region for the methods. In this example, we will just have the one method. This method will be used to populate the object from a SqlDataReader passed to it:
#Region "methods"
Private Sub SetObjectData(ByVal theObjReader As SqlDataReader)
Try
Me._CarID = Convert.ToInt32(theObjReader("ID"))
Me._Make = theObjReader("Make").ToString()
Me._Model = theObjReader("Model").ToString()
Catch
End Try
End Sub |
The only thing left for us to do with our Class is to create the constructors, one that takes an integer parameter, which will build the object. The aim of this method is to get the ID and then connect to the database and retrieve the record which matches the ID, then use SetObjectData to build the Car object, which it will then return.
|
Public Sub New(ByVal theObjReader As SqlDataReader)
SetObjectData(theObjReader)
End Sub
Public Sub New(ByVal theCarID As Integer)
Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Try
Dim cmd As SqlCommand
cmd = New SqlCommand("sp_GetCarByID", connection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@CarID", theCarID)
connection.Open()
Dim objReader As SqlDataReader = cmd.ExecuteReader()
Do While objReader.Read()
SetObjectData(objReader)
Loop
objReader.Close()
connection.Close()
Catch
connection.Close()
End Try |
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!
We are using a Stored Procedure here, which can be added to the database by right-click the Stored Procedures folder in Server Explorer, and then choosing Add New Stored Procedure. The SP will look something like this
CREATE PROCEDURE dbo.sp_GetCarByID
@CarID int
AS
SELECT * FROM Cars WHERE ID = @CarID
|
Now we have completed our class, we can move onto our ASPX page to build the front-end. We will add a TextBox and a Button, as well as three Literal controls. The TextBox and the Button will be for the user to input an Car ID, and have the Car returned from the database and displayed on the page. The Literals will be used to display the data from the object retrieved. Our simple ASPX page will look something like this:
<form id="form1" runat="server">
Car ID: <asp:TextBox ID="fld_CarID" runat="server" Columns="4" />
<asp:Button ID="btn_Submit" runat="server" Text="Submit" />
<br />
<br />
<asp:Literal ID="lit_ID" runat="server" /><br />
<asp:Literal ID="lit_Make" runat="server" /><br />
<asp:Literal ID="lit_Model" runat="server" /> |
In our code-behind, we need to reference the custom class first, like so:
Our Event handler for the Button will look something like this:
Protected Sub btn_Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Submit.Click
If (Not String.IsNullOrEmpty(fld_CarID.Text)) Then
Dim getCar As New Car(Convert.ToInt16(fld_CarID.Text))
If getCar.CarID > 0 Then
lit_ID.Text = "ID: " & getCar.CarID.ToString()
lit_Make.Text = "Make: " & getCar.Make
lit_Model.Text = "Model: " & getCar.Model
End If
End If |
Notice here we are creating a new instance of the Car object using the ID specified by the user. If the ID exists in the database, the object will be populated and displayed in the browser. Run the application and enter an ID that exists in the database - the Literals will display the rest of the information. This is how we retrieve custom objects from the database.
|