Creating data classes for ASP.NET data controls
Part six of a seven-part series of blogs

Yes, ASP.NET provides DataSource controls, but wizards only get you so far. Share the secret of how Wise Owl create all our ASP.NET websites, using a single, simple data class.

  1. Data Classes in ASP.NET
  2. Our Example for this Data Classes Tutorial
  3. Storing and Retrieving Connection Strings
  4. ADO.NET: DataSets, DataTables and Data Adapters
  5. Consuming our Data Classes
  6. A Class to Run a Stored Procedure and Return its Rows (this blog)
  7. Consuming our Data Class on the Web Page

This blog is part of a larger online ASP.NET online tutorial.  For ASP.NET training or courses in VB programming or programming in C#, see the separate pages.

Posted by Andy Brown on 07 August 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

A Class to Run a Stored Procedure and Return its Rows

After all of the preceding theory (and I've tried to keep it to the bare minimum), it's time to create our class - but first we need somewhere to put it.

Creating the App_Code Folder

Microsoft recommend that you put all classes that you create in a special folder called App_Code.  To create this, right-click on your project and choose to create a special ASP.NET folder:

Creating App_Code folder

Right-click on your project to create the App_Code folder.

Creating the Class

Now that you've got the right sort of folder, you can create a class:

Creating a class - add new item

Right-click on the App_Code folder and choose to add a new item.

 

Choose to create a class, in VB or C#:

Creating a class

Follow the numbered steps below to create your class.

The steps to follow are as follows:

  1. Choose your language.
  2. Choose to create a class.
  3. Give the class a name.

It's a .NET convention that your class names should begin with cls.  I haven't followed this here, however!

The Code Listing for C#

Here's the class code for C# (I've tried to explain this at the bottom of this blog, after the VB code listing):

using System;

// allow use of DataTables, DataRows, etc.

using System.Data;

// allow use of SQL Server objects

using System.Data.SqlClient;

// other standard references for .NET

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

// the class starts!

public class aaaQuery

{

// will hold the SQL command object used to retrieve data

SqlCommand DcmdSelect;

// constructor (ie code to run when you create a new

// instance of the class, specifying the stored prcoedure

// name and the connection string name)

public aaaQuery(string spName, string ConnectionString)

{

// find out what the connection string is

// (see previous part of blog)

string pConnString =

System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString;

// create a new SQL command, and say that

// it's a stored procedure type

// using the requested connection string

DcmdSelect = new SqlCommand(spName);

DcmdSelect.CommandType = CommandType.StoredProcedure;

DcmdSelect.Connection = new SqlConnection(pConnString);

}

// lots of methods to add a parameter to the command object: the

// method names are identical, but the signature is

// different in each case

public void AddParameter(string ParameterName, string ParameterValue)

{

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue);

}

public void AddParameter(string ParameterName, int ParameterValue)

{

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue);

}

public void AddParameter(string ParameterName, decimal ParameterValue)

{

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue);

}

public void AddParameter(string ParameterName, DateTime ParameterValue)

{

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue);

}

// create and initialise a variable to hold table of values to return so

// that we avoid running the same code again if we try to access the

// DtSselectedRecords property a second time

private DataTable dt = null;

// the property must be publicly accessible, and is read-only (ie

// it only has a get clause)

public DataTable DtSelectedRecords {

get {

// if this is the first time we've called the property, the

// local variable dt will be null

if (dt == null) {

// create a data adapter based on our command ...

SqlDataAdapter da = new SqlDataAdapter(DcmdSelect);

// ... and use it to create and fill a new table

// called NewTable in a new dataset

DataSet ds = new DataSet();

da.Fill(ds, "NewTable");

// make sure we close the connection to SQL Server as soon as

// possible (the data adapter will have opened it)

DcmdSelect.Connection.Close();

// return the table called NewTable in the dataset (as it

// happens, it's the only table in the dataset!)

dt = ds.Tables["NewTable"];

}

return dt;

}

}

}

The Code Listing for VB

Here's the class for VB (it's explained below, although I've used the C# code as reference):

Imports System

'allow use of DataTables, DataRows, etc.

imports System.Data

'allow use of SQL Server objects

imports System.Data.SqlClient

'the class starts!

Public Class aaaQuery

'will hold the SQL command object used to retrieve data

Private DcmdSelect As SqlCommand

'constructor (ie code to run when you create a new

'instance of the class, specifying the stored prcoedure

'name and the connection string name)

Public Sub New(ByVal spName As String, ConnectionString As String)

'find out what the connection string is

'(see previous part of blog)

Dim pConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings(ConnectionString).ConnectionString

'create a new SQL command, and say that

'it's a stored procedure type

'using the requested connection string

DcmdSelect = New SqlCommand(spName)

DcmdSelect.CommandType = CommandType.StoredProcedure

DcmdSelect.Connection = New SqlConnection(pConnString)

End Sub

'lots of methods to add a parameter to the command object: the

'method names are identical, but the signature is

'different in each case

Sub AddParameter(ByVal ParameterName As String, ByVal ParameterValue As String)

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue)

End Sub

Sub AddParameter(ByVal ParameterName As String, ByVal ParameterValue As Integer)

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue)

End Sub

Sub AddParameter(ByVal ParameterName As String, ByVal ParameterValue As Decimal)

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue)

End Sub

Sub AddParameter(ByVal ParameterName As String, ByVal ParameterValue As DateTime)

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue)

End Sub

'create and initialise a variable to hold table of values to return so

'that we avoid running the same code again if we try to access the

'DtSselectedRecords property a second time

Private dt As DataTable = Nothing

'the property must be publicly accessible, and is read-only (ie

'it only has a get clause)

ReadOnly Property DtSelectedRecords() As DataTable

Get

'if this is the first time we've called the property, the

'local variable dt will be set to nothing

If dt Is Nothing Then

'create a data adapter based on our command ...

Dim da As New SqlDataAdapter(DcmdSelect)

'... and use it to create and fill a new table

'called NewTable in a new dataset

Dim ds As New DataSet

da.Fill(ds, "NewTable")

'make sure we close the connection to SQL Server as soon as

'possible (the data adapter will have opened it)

DcmdSelect.Connection.Close()

'return the table called NewTable in the dataset (as it

'happens, it's the only table in the dataset!)

dt = ds.Tables("NewTable")

End If

Return dt

End Get

End Property

End Class

An Explanation of the Code

What follows is an explanation of what the class is doing, line by line.  I've used the C# version of the code, although VB programmers should find it trival to convert the comments to VB (and will probably be used to doing so).

This doesn't mean that I think C# is better than VB - far from it!

Let's start with a quick reminder of how the class is called:

// on first loading the page, create a new "query"

aaaQuery sp = new aaaQuery("spListMovies", "csMovies");

// add a parameter so that we only show films winning

// at least 1 Oscar

sp.AddParameter("MinOscars", 1);

// get at the data table returned from this "query"

DataTable dt = sp.DtSelectedRecords;

At the time the class is instantiated, then, we pass it two things:

  1. The name of the stored procedure to run; and
  2. The connection string to use.

The first thing the class does is to create a SQL command object, and tell it which database it should get its data from:

// will hold the SQL command object used to retrieve data

SqlCommand DcmdSelect;

// constructor (ie code to run when you create a new

// instance of the class, specifying the stored prcoedure

// name and the connection string name

public aaaQuery(string spName, string ConnectionString)

{

// find out what the connection string is

// (see previous part of blog)

string pConnString =

System.Configuration.ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString;

// create a new SQL command, and say that

// it's a stored procedure type

// using the requested connection string

DcmdSelect = new SqlCommand(spName);

DcmdSelect.CommandType = CommandType.StoredProcedure;

DcmdSelect.Connection = new SqlConnection(pConnString);

}

The DcmdSelect variable is public for the class, so that other methods can subsequently refer to it. 

The next thing we do when consuming the class is to add a parameter to it.  Parameters can be text, integers, decimal numbers or dates, so we've written 4 overloaded methods (ie methods with the same name but different argument signatures) to accommodate all of the possibilities:

// lots of methods to add a parameter to the command object: the

// method names are identical, but the signature is

// different in each case

public void AddParameter(string ParameterName, string ParameterValue)

{

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue);

}

public void AddParameter(string ParameterName, int ParameterValue)

{

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue);

}

public void AddParameter(string ParameterName, decimal ParameterValue)

{

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue);

}

public void AddParameter(string ParameterName, DateTime ParameterValue)

{

DcmdSelect.Parameters.AddWithValue(ParameterName, ParameterValue);

}

As it happens, for our example it's the second of the four which will be used, since the minimum number of Oscars won is an integer.  Don't forget that this will be passed to the spListMovies stored procedure:

CREATE PROC [dbo].[spListMovies](

@MinOscars int=0

)

AS

SELECT

MovieId,

MovieName

FROM

tblMovie

WHERE

Oscars>=@MinOscars

ORDER BY

MovieName

We now want to create the public property called DtSelectedRecords, which will return a set of rows from the class.  We start by creating a private variable called dt, which initially will be null:

// create and initialise a variable to hold table of values to return so

// that we avoid running the same code again if we try to access the

// DtSselectedRecords property a second time

private DataTable dt = null;

When the DtSelectedRecords property is called, we can check to see if this is still null, and if it is we have work to do!

// the property must be publicly accessible, and is read-only (ie

// it only has a get clause)

public DataTable DtSelectedRecords {

get {

// if this is the first time we've called the property, the

// local variable dt will be null

if (dt == null) {

In this case, we have to take the records returned by the stored procedure, and use them to fill a data table in a newly created dataset:

// create a data adapter based on our command ...

SqlDataAdapter da = new SqlDataAdapter(DcmdSelect);

// ... and use it to create and fill a new table

// called NewTable in a new dataset

DataSet ds = new DataSet();

da.Fill(ds, "NewTable");

This leaves the connection to the database open, so we close it:

// make sure we close the connection to SQL Server as soon as

// possible (the data adapter will have opened it)

DcmdSelect.Connection.Close();

Now that we've filled a data table, we can set our local variable dt to refer to it:

// return the table called NewTable in the dataset (as it

// happens, it's the only table in the dataset!)

dt = ds.Tables["NewTable"];

}

We now have a table of data in dt (whether it was already there from a previous call, or whether newly created), and can return this as the value for the property:

return dt;

}

When you've got the hang of this class as it stands, try extending it (for example, by adding a read-only NumberRows property).

This blog has 0 threads Add post