This tutorial will show you how to retrieve a Customer Object from a SQL Database using only the ID reference. C#
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.cs and click Ok. You should be presented with the default class. What we want to do is wrap it in a namespace like so:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
/// <summary>
/// Summary description for Car
/// </summary>
namespace DBTutorials.Car
{
/// <summary>
/// Summary description for Car
/// </summary>
public class Car
{
public Car()
{
}
} |
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.
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, as is using the Summary tags, which are used by the IntelliSense.
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
/// <summary>
/// Gets or sets Car ID. [Default value is 0].
/// </summary>
public Int32 CarID
{
get
{
return _CarID;
}
set
{
_CarID = value;
}
private Int32 _CarID = 0;
/// <summary>
/// Gets or sets the Make of the Car. [Default value is ''].
/// </summary>
public String Make
{
get
{
return _Make;
}
set
{
_Make = value;
}
private String _Make = "";
/// <summary>
/// Gets or sets the Model of the Car. [Default value is ''].
/// </summary>
public String Model
{
get
{
return _Model;
}
set
{
_Model = value;
}
private String _Model = "";
#endregion
|
If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!
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
/// <summary>
/// Sets the Car object using the SqlDataReader object passed to it.
/// </summary>
/// <param name="theObjReader">SqlDataReader object that contains DB info to set the Car object.</param>
private void SetObjectData(SqlDataReader theObjReader)
{
try
{
this._CarID = Convert.ToInt32(theObjReader["ID"]);
this._Make = theObjReader["Make"].ToString();
this._Model = theObjReader["Model"].ToString();
}
catch
{ }
#endregion
|
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.
/// <summary>
/// Contructor for Car object, sets the Car object from the SqlDataReader passed to it.
/// </summary>
/// <param name="theObjReader">SqlDataReader value that contains the DB information to set </param>
public Car(SqlDataReader theObjReader)
{
SetObjectData(theObjReader);
}
/// <summary>
/// Contructor for Car object, set the Car object from ID passed to it.
/// </summary>
/// <param name="theCarID">Int32 value of the ID of the Car to get.</param>
public Car(int theCarID)
{
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd;
cmd = new SqlCommand("sp_GetCarByID", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CarID", theCarID);
connection.Open();
SqlDataReader objReader = cmd.ExecuteReader();
while (objReader.Read())
{
SetObjectData(objReader);
}
objReader.Close();
connection.Close();
}
catch
{
connection.Close();
} |
We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
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" OnClick="btn_Submit_OnClick" />
<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 void btn_Submit_OnClick(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(fld_CarID.Text))
{
Car getCar = new Car(Convert.ToInt16(fld_CarID.Text));
if (getCar.CarID > 0)
{
lit_ID.Text = "ID: " + getCar.CarID.ToString();
lit_Make.Text = "Make: " + getCar.Make;
lit_Model.Text = "Model: " + getCar.Model;
}
} |
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.
|