Creating a StudentDB Class for ASP.NET 2.0

What you are going to learn is how you can separate your data access code from the ASP.NET pages so your code will look elegant and easier to debug, manage and modify as well. We will also discuss how you can use generics, one of the new great features of C# 2.0, to return a strongly typed collection that manipulates objects of type Student, and how nice that is for our website.

We will also see the different syntax involved in creating SqlParameter objects, using the Command.Behavior enumeration to close the connection and to return a single row result set. But today, we are going to create the database table and the stored procedures involved with creating the website and creating the Student class. Then we are going to talk about what we need to do after that.

Let's start by creating the database and the database table we need for this article and the next two; then we'll insert some data and start working on our website. Run the following code in Microsoft SQL Server Management Studio to create the necessary database, table and INSERT statements that insert a few rows into the Student table.

CREATE DATABASE School
GO
  USE School
GO
  CREATE TABLE Students
  (
    StudentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(20) NOT NULL,
    LastName NVARCHAR(20) NOT NULL,
    DateOfBirth DATETIME NOT NULL,
    AdmissionDate DATETIME NOT NULL,
    Major NVARCHAR(40) NOT NULL,
    Active BIT NOT NULL
  )
GO
  INSERT INTO Students
    VALUES
      ('Jack','Roberts','2/15/1984', '7/7/2006','Computer Science',1)
  INSERT INTO Students
    VALUES
      ('Mary','Paul','5/19/1984', '7/7/2006','Information Systems',1)
  INSERT INTO Students
    VALUES
      ('Mark','David','8/6/1984', '7/7/2006','Physics',0)
  INSERT INTO Students
    VALUES
      ('Julia','Anderson','3/27/1983', '7/10/2006','Computer Science',1)
GO

You will get the following displayed in the Messages panel:

We have simply created a database called School, and then created a table inside it called Students. The Students table has an identity and primary key column called StudentID, followed by the first name, last name, date of birth and admission date and finally a column of type BIT to determine whether the student has registered for this semester or not. The SQL Server BIT data type is an integer data type but with restricted values of 0, 1 or NULL.

Note that our table design doesn't make sense and the database should contain other tables to correctly store and manipulate the data. Still, for the sake of simplicity we are going to work on only one table to concentrate on learning the concepts and the technique discussed in this article and the two articles to follow. After creating the objects we have to insert a few records so we can test our website after writing the code. We have used the T-SQL INSERT statement to do that but you can use Management Studio visual tools to do it without writing any T-SQL code. Now let's move on to the ASP.NET website and see what we need to do.

Creating the Website and the App_Code Folder

Create a new ASP.NET website on the local file system and select Visual C# as the language. I will call the website TheSchool but you can call it whatever you like. You can accomplish this task from the menu option File ---> New ---> Web Site. Now we need to create the first class, which is the Student class. There is no data access code that will be written in that class. The Student class simply represents a row in the Students database table; the data access code will be written in the StudentDB class which has methods that accept instances of the Student class as a parameter and use this object to write a row to the database table. The best way to explain this is by example, so let's get to the code so you can understand what I mean. Don't worry though; we are going to talk more about this technique.

You can create a new C# Class Library Project for the Student class and reference it from your website, but there is an easier technique that you will prefer for small to medium websites. New in ASP.NET 2.0 is the "ASP.NET Folders" feature which provides special folders that are used in your website for special functionalities. For example the App_Code folder is used to store your C# utility classes, data access and business logic code files, like our C# Student.cs file that we are going to create. 

The classes you add to this folder will be compiled into an assembly and referenced from your web site code so you can access it without adding a reference to it. You can also organize your files, inside the App_Code folder, into subfolders and write as many classes as your application needs. So let's add this special folder to our website. Right click on the web site entry at Solution Explorer, select Add ASP.NET Folder, and then click on App_Code as shown in the next screenshot:

Note that the App_Code folder has been added to the website's directory. Now right click on the App_Code folder, click on Add New Item, and then select a Class. Name this class Student.cs as shown in the next screenshot:

Let's write that class.

Writing the Student class

As we have said before, the Student class represents a record of the Students database table. That means we need to simulate the table's fields with C# properties for that class. So we are going to create seven properties to represent the record. Please wait until we write the data access code and I'm sure that you will fully understand this technique, but for now let's see the code for the Student class. The following is the code you need to copy and paste, or write it by hand if you wish, in place of the auto-generated code of the Student.cs file:

using System;

public class Student
{
  private int studentId;

  public int StudentId
  {
    get { return studentId; }
  }
  private string firstName;

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

  public string LastName
  {
    get { return lastName; }
    set { lastName = value; }
  }
  private DateTime dateOfBirth;

  public DateTime DateOfBirth
  {
     get { return dateOfBirth; }
     set { dateOfBirth = value; }
  }
  private DateTime admissionDate;

  public DateTime AdmissionDate
  {
    get { return admissionDate; }
    set { admissionDate = value; }
  }
    private string major;

  public string Major
  {
    get { return major; }
    set { major = value; }
  }
  private bool active;

  public bool Active
  {
    get { return active; }
    set { active = value; }
  }

  public Student(int studentId, string firstName, string lastName, DateTime dateOfBirth, DateTime admissionDate, string major, bool active)
  {
    this.studentId = studentId;
    this.firstName = firstName;
    this.lastName = lastName;
    this.dateOfBirth = dateOfBirth;
    this.admissionDate = admissionDate;
    this.major = major;
    this.active = active;
  }
}

As you can see, there is nothing complicated about this class, but note the difference between the data types used here and the data types we used when we created the Students table on SQL Server, like NVARCHAR, INT, DATETIME and BIT. We will talk more about that in the next part of the series, but for now let's continue. 

We have also added a constructor to the class that accepts values for all the private instance variables as parameters. You will see how this constructor is useful soon. Now let's do one more thing. Before we move on to creating the StudentDB class, we need to create the connection string that will be used by the static methods of the StudentDB class to access the database. Let's do that now. 

Storing the Connection String in a Web.Config file

New in ASP.NET 2.0 is the <connectionStrings> element, or we could say section, in the web.config file. You can add connection strings as child elements to the <connectionStrings> element as in the following code:

<connectionStrings>
  <add name="SchoolConnectionString"
connectionString="Data Source=(local);Initial
Catalog=School;Integrated Security=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>

You need to put this <connectionStrings> element in the Web.Config file of the website. To do that, right click on the website in Solution Explorer; right click then select Add New Item, select Web Configuration File and leave its default name as shown in the following screenshot:

Click on add; now open the Web.Config file and locate the element <connectionStrings /> and replace it with the one above. Note that we have used the child element <add> in order to add a connection string to the Web.Config. The name attribute denotes the name that will be used in code to refer to that particular connection string. The connection string itself is added using the connectionString attribute.

Please, if you are not familiar with ADO.NET, you must read about it before you proceed. I have written many articles about ADO.NET objects covering SqlConnection, SqlCommand and SqlDataReader. These will give you an understanding of how to work with ADO.NET.

To retrieve the connection string from the C# code you need the following line of code:

WebConfigurationManager.ConnectionStrings
["SchoolConnectionString"].ConnectionString;

The WebConfigurationManager class has a property called ConnectionStrings that is used to retrieve the connection string from the configuration file. This property is of type ConnectionStringSettingsCollection so we use the indexer syntax to access its individual objects which are of type ConnectionStringSettings. We get the connection string itself through the use of the ConnectionStringSettings.ConnectionString property. This article is not about configuring ASP.NET applications so I will not spend the time to talk about the Web.Config file; please consult other articles on this topic. We will write the above line in the StudentDB C# class in the next section.

The StudentDB class

The studentDB class contains the methods InsertStudent() and UpdateStudent() which accept a parameter of type Student, and the method DeleteStudent() which accepts an integer value that represents the Student ID. Both InsertStudent() and UpdateStudent() methods do the insertion and the updating on the Students table by using the Student instance passed as a parameter to them.

In other words, the Student instance passed to those methods represents a record in the Students table, and by calling a method like UpdateStudent() and passing it that Student instance, the database record (that this Student instance represents) will be updated with the values of the properties of that Student instance. The code that we write in this small series will crystallize this concept for you -- and after this series I will teach you another technique to perform the same operations on the Students table using other classes.

The StudentDB class also contains the GetAllStudents() and GetStudent() methods which we will develop and discuss. Note that all these methods are static and we don't need to create instances to use them; it makes sense if you think about it. The only part that would be an instance is the object passed to the method as a parameter. You create an instance of the Student class and pass it to a method like InsertStudent() which will do its job and insert a record that represents that Student object into the database. Now let's look at this class:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

// additional namespaces needed for this class
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;

public class StudentDB
{
  private readonly static string connString;

  static StudentDB()
  {
    connString = WebConfigurationManager.ConnectionStrings
["SchoolConnectionString"].ConnectionString;
  }

  public static int InsertStudent(Student student)
  { }

  public static void UpdateStudent(Student student)
  { }

  public static void DeleteStudent(int studentId)
  { }

  public static Student GetStudent(int studentId)
  { }

  public static DataTable GetAllStudentsInDataTable()
  { }

  public static List<Student> GetAllStudentsInCollection()
  { }
}

These are the methods we are going to develop in the series. For now just keep in mind that all the members are static and the connString is a private readonly string member that is assigned its value using the static constructor with the syntax we have discussed in the previous section. Also note that we have added three additional namespace references. The first is a namespace reference to the ADO.NET SQL Server Data Provider and the second is needed by the constructor's code because the WebConfigurationManager class lives in the namespace System.Web.Configuration. The third namespace is needed because we are using C# Generics in the GetAllStudentsInCollection() method.

C# Generics is a great new feature that has been added in .NET Framework 2.0 which gives you the ability to create a strongly typed collection without writing any code. we simply use the .NET-provided generic collection types, like List<>, and tell it what type we need to use to create a strongly typed collection. In our case, we need a collection of Student objects, so we have used the syntax List<Student>. As you will see, you don't have to create a collection class and use an inner collection object, like the ArrayList, along with public methods, like Add(Student student), that accept the strongly typed object with which you want to work. All what you have to do is use generics.

In the next article, we are going develop the GetStudent() and GetAllStudentsInCollection() methods.