A lot of Web coders that have used other platforms like PHP or Cold Fusion are a little bit surprised at the number of steps required to get data in and out of a database in .NET. Just to get a few values out of the database, you need to create a connection object, a command object and then at the very least, a DataReader. (In all fairness, you get back much of your time when data binding!) Sounds like the perfect place to build a useful class!

If we put all of our database logic into one class, we can write the SQL statements once and manipulate the data with far less code throughout our application. You’ll also benefit from having just one place to change code if you decide to use a different database (like Oracle). Best of all, implementing a data caching scheme is that much easier when all of your data code is in one place.

To help you see the benefit of this write-once, use-everywhere class, let’s see a code sample that creates a row in our database, reads the row, then deletes it.

C#

// Instantiate the Customer class using the default constructor
Customer objCustomer = new Customer();
// Assign some of its properties
objCustomer.LastName = "Jones";
objCustomer.FirstName = "Jeff";
// Call its Create() method to save the values in the database,
// and get its new primary key (CustomerID) value
int intCustomerID = objCustomer.Create();

// Instantiate the Customer class using the constructor that takes
// the CustomerID as a parameter
Customer objCustomer2 = new Customer(intCustomerID);
Trace.Write("LastName: " + objCustomer2.LastName);
Trace.Write("FirstName: " + objCustomer2.FirstName);

// Change the value of the first name then save the changes
// to the database
objCustomer2.FirstName = "Stephanie";
objCustomer2.Update();

// On second thought, let's just delete the record entirely
objCustomer2.Delete();

VB .NET

' Instantiate the Customer class using the default constructor
Dim objCustomer As New Customer()
' Assign some of its properties
objCustomer.LastName = "Jones"
objCustomer.FirstName = "Jeff"
' Call its Create() method to save the values in the database, 
' and get its new primary key (CustomerID) value
Dim intCustomerID As Integer = objCustomer.Create()
         
' Instantiate the Customer class using the constructor that takes
' the CustomerID as a parameter
Dim objCustomer2 As New Customer(intCustomerID)
Trace.Write(("LastName: " + objCustomer2.LastName))
Trace.Write(("FirstName: " + objCustomer2.FirstName))
         
' Change the value of the first name then save the changes
' to the database
objCustomer2.FirstName = "Stephanie"
objCustomer2.Update()
         
' On second thought, let's just delete the record entirely
objCustomer2.Delete()

You can see by these few lines of code that we didn’t go through the entire process of creating connection, command and other data objects. A few simple method calls are all we need to manipulate the data. Imagine how much time you’d save if you had to manipulate the data in dozens of places around your application! Know this block of code... we'll refer back to it throughout the article.

Analyzing design requirements

The first step in designing any class is to identify your needs in human terms before writing code. In our case, we want to make it easy to get, update and delete data from a table called Customers in SQL Server. None of the columns in our table allow null values.

We know that the Customers table has 9 columns that we can manipulate. We also know that we want to create, update and delete records in this table. The most obvious need we’ll have is to get data from the table. Once we have the basics of our class nailed down, we’ll revise the class to cache data and explore ways to get a number of records at one time.

Choosing our properties

Let's start writing our class by declaring it and creating the necessary references that we'll need.

C#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;

namespace UberAspNet
{
	public class Customer
	{
		private int _CustomerID;
		public int CustomerID
		{
			get {return _CustomerID;}
		}

		private string _LastName;
		public string LastName
		{
			get {return _LastName;}
			set {_LastName = value;}
		}

		private string _FirstName;
		public string FirstName
		{
			get {return _FirstName;}
			set {_FirstName = value;}
		}

		private string _Address;
		public string Address
		{
			get {return _Address;}
			set {_Address = value;}
		}

		private string _City;
		public string City
		{
			get {return _City;}
			set {_City = value;}
		}

		private string _State;
		public string State
		{
			get {return State;}
			set {_State = value;}
		}

		private string _Zip;
		public string Zip
		{
			get {return _Zip;}
			set {_Zip = value;}
		}

		private string _Phone;
		public string Phone
		{
			get {return _Phone;}
			set {_Phone = value;}
		}

		private DateTime _SignUpDate;
		public DateTime SignUpDate
		{
			get {return _SignUpDate;}
			set {_SignUpDate = value;}
		}
	}
}

VB .NET

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web

Namespace UberAspNet

   Public Class Customer
      
      Private _CustomerID As Integer
      Public ReadOnly Property CustomerID() As Integer
         Get
            Return _CustomerID
         End Get
      End Property 

      Private _LastName As String
      Public Property LastName() As String
         Get
            Return _LastName
         End Get
         Set
            _LastName = value
         End Set
      End Property 

      Private _FirstName As String
      Public Property FirstName() As String
         Get
            Return _FirstName
         End Get
         Set
            _FirstName = value
         End Set
      End Property 

      Private _Address As String
      Public Property Address() As String
         Get
            Return _Address
         End Get
         Set
            _Address = value
         End Set
      End Property 

      Private _City As String
      Public Property City() As String
         Get
            Return _City
         End Get
         Set
            _City = value
         End Set
      End Property 

      Private _State As String
      Public Property State() As String
         Get
            Return State
         End Get
         Set
            _State = value
         End Set
      End Property 

      Private _Zip As String
      Public Property Zip() As String
         Get
            Return _Zip
         End Get
         Set
            _Zip = value
         End Set
      End Property 

      Private _Phone As String
      Public Property Phone() As String
         Get
            Return _Phone
         End Get
         Set
            _Phone = value
         End Set
      End Property 

      Private _SignUpDate As DateTime
      Public Property SignUpDate() As DateTime
         Get
            Return _SignUpDate
         End Get
         Set
            _SignUpDate = value
         End Set
      End Property

   End Class

End Namespace

The code is fairly straight forward. We’ve created a property to correspond to each of the columns in our database table, matching the data types, using the typical get and set for each one. We’ve also created a private variable for each column for use internally in our class. The only thing out of the ordinary here is that we’ve made the CustomerID property read-only (in C# this is achieved by simply not indicating the set part). That’s because our database table is designed to make this a primary key and an identity field, meaning that SQL Server will number the column automatically when we add new rows. For that reason, we don’t want the class to have the ability to alter this property.

The constructors

Now that we know what pieces of data our class should contain, it’s time to setup that data when we instantiate the class. There are two scenarios: We’ll populate the properties with default values or we’ll populate them with values from our database. To do this, we’ll create two constructors by way of overloading. Constructors are the pieces of code that execute when you create an instance of a class.

MyHouse.SetTemperature(69);

MyHouse.SetTemperature("Heat");

In writing our constructors, we’ll have to decide up front how we’ll distinguish between default data and data we’ve entered or retrieved from the database. We could simply allow each property to return a null value, but that wouldn’t correspond well to the fact that our database table doesn’t allow null values. Instead, we’ll have all of our string values equal empty strings, our SignUpDate value equal January 1, 2000, and our CustomerID will be set to 0.

The CustomerID is perhaps the stickiest point in our class design. While the chances are that we’ll always know exactly what we’re doing in our code (I know, you can stop laughing), we need to know and document how we’re going to know if the instantiated Customer object actually corresponds to an existing database record or not. Again, we could just keep CustomerID null until we’ve created a record, but for our case we’ll decide right now that a value of 0 means that the either no record exists or we’ve created a new object with default values. This leaves the potential for populating our object with default values from both constructors, so we’ll create a method just for this purpose.

C#

private void PopulateDefault()
{
	_CustomerID = 0;
	_LastName = "";
	_FirstName = "";
	_Address = "";
	_City = "";
	_State = "";
	_Zip = "";
	_Phone = "";
	_SignUpDate = new DateTime(2000,1,1);
}

VB .NET

Private Sub PopulateDefault()
	_CustomerID = 0
	_LastName = ""
	_FirstName = ""
	_Address = ""
	_City = ""
	_State = ""
	_Zip = ""
	_Phone = ""
	_SignUpDate = New DateTime(2000, 1, 1)
End Sub

Now that we have that out of the way, our default constructor is a piece of cake.

C#

public Customer()
{
	PopulateDefault();
}

VB .NET

Public Sub New()
	PopulateDefault()
End Sub

Within our calling code, creating the object and assigning values to its properties is as easy as the first fragment of code we showed you in this article.

Our second overload for the constructor has the familiar database code you’ve been waiting for. It takes a single parameter, the CustomerID, and populates our object’s properties based on a match in the database.

C#

private string _ConnectionString = "server=(local);database=test;Integrated Security=SSPI";

public Customer(int CustomerID)
{
	SqlConnection objConnection = new SqlConnection(_ConnectionString);
	objConnection.Open();
	SqlCommand objCommand = new SqlCommand("SELECT CustomerID, LastName, FirstName, Address, City, State, Zip, Phone, SignUpDate WHERE CustomerID = @CustomerID", objConnection);
	objCommand.Parameters.Add("@CustomerID", CustomerID);
	SqlDataReader objReader = objCommand.ExecuteReader();
	if (objReader.Read())
	{
		_CustomerID = objReader.GetInt32(0);
		_LastName = objReader.GetString(1);
		_FirstName = objReader.GetString(2);
		_Address = objReader.GetString(3);
		_City = objReader.GetString(4);
		_State = objReader.GetString(5);
		_Zip = objReader.GetString(6);
		_Phone = objReader.GetString(7);
		_SignUpDate = objReader.GetDateTime(8);
	}
	else PopulateDefault();
	objReader.Close();
	objConnection.Close();
}

VB .NET

Private _ConnectionString As String = "server=(local);database=test;Integrated Security=SSPI"

Public Sub New(CustomerID As Integer)
	Dim objConnection As New SqlConnection(_ConnectionString)
	objConnection.Open()
	Dim objCommand As New SqlCommand("SELECT CustomerID, LastName, FirstName, Address, City, State, Zip, Phone, SignUpDate WHERE CustomerID = @CustomerID", objConnection)
	objCommand.Parameters.Add("@CustomerID", CustomerID)
	Dim objReader As SqlDataReader = objCommand.ExecuteReader()
	If objReader.Read() Then
		_CustomerID = objReader.GetInt32(0)
		_LastName = objReader.GetString(1)
		_FirstName = objReader.GetString(2)
		_Address = objReader.GetString(3)
		_City = objReader.GetString(4)
		_State = objReader.GetString(5)
		_Zip = objReader.GetString(6)
		_Phone = objReader.GetString(7)
		_SignUpDate = objReader.GetDateTime(8)
	Else
		PopulateDefault()
	End If
	objReader.Close()
	objConnection.Close()
End Sub

If you already know how to get data and the above code looks a little weird to you, fear not. We use several techniques here that make data extraction far more efficient, and since you only have to write this code once in your application, it's worth the work. By using the various "Get" methods of the DataReader, we save a little time because the framework doesn't have to look up the values by their common name, it just goes right to the zero-based index we give it.

First we create our connection object using a connection string we’ve added to the class. (In real life, you would probably store your connection string in web.config, but we include it here for simplicity.) Once open, we create a command object that has our SQL query, including the parameter in the WHERE clause that we’ll use to choose the record. The very next line, we add a parameter to our command object, setting its value from the parameter of the constructor. We create a DataReader, and if it can read, we populate our properties with the values from the database. If no record is found, we populate our object with the default values, using the standalone PopulateDefault() method we created earlier.

So far, we’ve got all of our properties and a means to create a Customer object with default values or values from an existing database record. Next we need ways to create, update and delete that data.

Create, Update and Delete methods

In the example we gave at the beginning of the article, we created a Customer object using the default constructor, assigned some properties, then called a Create() method. This Create() method takes all of the current property values and inserts them into their corresponding columns in a new row in the Customers table. Again, the code should be very familiar to you.

C#

public int Create()
{
	SqlConnection objConnection = new SqlConnection(_ConnectionString);
	objConnection.Open();
	SqlCommand objCommand = new SqlCommand("INSERT INTO Customers (LastName, FirstName, Address, City, State, Zip, Phone, SignUpDate) VALUES (@LastName, @FirstName, @Address, @City, @State, @Zip, @Phone, @SignUpDate)", objConnection);
	objCommand.Parameters.Add("@LastName", _LastName);
	objCommand.Parameters.Add("@FirstName", _FirstName);
	objCommand.Parameters.Add("@Address", _Address);
objCommand.Parameters.Add("@City", _City);
	objCommand.Parameters.Add("@State", _State);
	objCommand.Parameters.Add("@Zip", _Zip);
	objCommand.Parameters.Add("@Phone", _Phone);
	objCommand.Parameters.Add("@SignUpDate", _SignUpDate);
	objCommand.ExecuteNonQuery();
	objCommand.Parameters.Clear();
	objCommand.CommandText = "SELECT @@IDENTITY";
	int intNewCustomerID = Convert.ToInt32(objCommand.ExecuteScalar());
	objConnection.Close();
	_CustomerID = intNewCustomerID;
	return intNewCustomerID;
}

VB .NET

Public Function Create() As Integer
	Dim objConnection As New SqlConnection(_ConnectionString)
	objConnection.Open()
	Dim objCommand As New SqlCommand("INSERT INTO Customers (LastName, FirstName, Address, City, State, Zip, Phone, SignUpDate) VALUES (@LastName, @FirstName, @Address, @City, @State, @Zip, @Phone, @SignUpDate)", objConnection)
	objCommand.Parameters.Add("@LastName", _LastName)
	objCommand.Parameters.Add("@FirstName", _FirstName)
	objCommand.Parameters.Add("@Address", _Address)
	objCommand.Parameters.Add("@City", _City)
	objCommand.Parameters.Add("@State", _State)
	objCommand.Parameters.Add("@Zip", _Zip)
	objCommand.Parameters.Add("@Phone", _Phone)
	objCommand.Parameters.Add("@SignUpDate", _SignUpDate)
	objCommand.ExecuteNonQuery()
	objCommand.Parameters.Clear()
	objCommand.CommandText = "SELECT @@IDENTITY"
	Dim intNewCustomerID As Integer = Convert.ToInt32(objCommand.ExecuteScalar())
	objConnection.Close()
	_CustomerID = intNewCustomerID
	Return intNewCustomerID
End Function

Generally when we create a record in the database, we’re done with it and moving on to other things. However, just in case, we’ve added an extra step to our Create() method. We’re going back to the database to see what the value is in the CustomerID column of the new record we just created, using the SQL statement “SELECT @@IDENTITY.” We’re assigning that value to the CustomerID property of our class and sending it back as the return value of our method. Given the design parameter decision we made earlier, changing the CustomerID value to anything other than 0 means that it corresponds to an actual record in the database.

Going back again to the first code sample, we’ll use a method called Update() to change the data in a specific row of our database table.

C#

public bool Update()
{
	if (_CustomerID == 0) throw new Exception("Record does not exist in Customers table.");
	SqlConnection objConnection = new SqlConnection(_ConnectionString);
	objConnection.Open();
	SqlCommand objCommand = new SqlCommand("UPDATE Customers SET LastName = @LastName, FirstName = @FirstName, Address = @Address, City = @City, State = @State, Zip = @Zip, Phone = @Phone, SignUpDate = @SignUpDate WHERE CustomerID = @CustomerID", objConnection);
	objCommand.Parameters.Add("@LastName", _LastName);
objCommand.Parameters.Add("@FirstName", _FirstName);
	objCommand.Parameters.Add("@Address", _Address);
	objCommand.Parameters.Add("@City", _City);
	objCommand.Parameters.Add("@State", _State);
	objCommand.Parameters.Add("@Zip", _Zip);
	objCommand.Parameters.Add("@Phone", _Phone);
	objCommand.Parameters.Add("@SignUpDate", _SignUpDate);
	objCommand.Parameters.Add("@CustomerID", _CustomerID);
	bool boolResult = false;
	if (objCommand.ExecuteNonQuery() > 0) boolResult = true;
	objConnection.Close();
	return boolResult;
}

VB .NET

Public Function Update() As Boolean
	If _CustomerID = 0 Then Throw New Exception("Record does not exist in Customers table.")
	Dim objConnection As New SqlConnection(_ConnectionString)
	objConnection.Open()
	Dim objCommand As New SqlCommand("UPDATE Customers SET LastName = @LastName, FirstName = @FirstName, Address = @Address, City = @City, State = @State, Zip = @Zip, Phone = @Phone, SignUpDate = @SignUpDate WHERE CustomerID = @CustomerID", objConnection)
	objCommand.Parameters.Add("@LastName", _LastName)
	objCommand.Parameters.Add("@FirstName", _FirstName)
	objCommand.Parameters.Add("@Address", _Address)
	objCommand.Parameters.Add("@City", _City)
	objCommand.Parameters.Add("@State", _State)
	objCommand.Parameters.Add("@Zip", _Zip)
	objCommand.Parameters.Add("@Phone", _Phone)
	objCommand.Parameters.Add("@SignUpDate", _SignUpDate)
	objCommand.Parameters.Add("@CustomerID", _CustomerID)
	Dim boolResult As Boolean = False
	If objCommand.ExecuteNonQuery() > 0 Then boolResult = True
	objConnection.Close()
	Return boolResult
End Function

We start our Update() method with a check of the CustomerID value. If it’s 0, we know that the object does not correspond to an existing record in the database, so we throw an exception. If the code is allowed to continue, the rest includes the familiar connection and command objects, as well as parameters that take the current values of our properties and use them to update our database record.
 

The last few lines are used to check for a successful update of the database. The ExecuteNonQuery() method of the command object returns an integer indicating the number of rows affected by our command. Since our WHERE clause is matching the CustomerID column, a column that we know must have a unique value, the only thing we’re interested in knowing is that at least one row was affected. If a value greater than 0 is returned from ExecuteNonQuery(), then we return a Boolean true value back to the calling code. This allows us to confirm that the data was indeed updated.

Where we can create and update data, we can also delete it. Enter our Delete() method, the most simple of the lot.

C#

public void Delete()
{
	SqlConnection objConnection = new SqlConnection(_ConnectionString);
	objConnection.Open();
	SqlCommand objCommand = new SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", objConnection);
	objCommand.Parameters.Add("@CustomerID", _CustomerID);
	objCommand.ExecuteNonQuery();
	objConnection.Close();
	_CustomerID = 0;
}

VB .NET

Public Sub Delete()
	Dim objConnection As New SqlConnection(_ConnectionString)
	objConnection.Open()
	Dim objCommand As New SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", objConnection)
	objCommand.Parameters.Add("@CustomerID", _CustomerID)
	objCommand.ExecuteNonQuery()
	objConnection.Close()
	_CustomerID = 0
End Sub

There isn’t anything complex about this code. We create our connection object and command objects, use the current value of the CustomerID property to make our match in our SQL statement, and execute the command. Just in case the calling code decides it wants to do something with the data, like call the object’s Update() method against a record that now no longer exists, we set the CustomerID property back to 0.
 

In part two, we'll look at caching the data for better performance and retrieving multiple records. Read part two here.

©2013, POP World Media, LLC. All rights reserved
Legal, privacy, terms of service