How To Set Elements Of An Array Of A Private Type Using Visual Studio Shadows

Visual Studio uses Publicize to create accessors public for private members and types of a type.

But when you try to set elements of a private array of elements of a private type, things get complicated.

Imagine this hypothetic class to test:

public static class MyClass

{

    private static readonly MyInnerClass[] myArray = new MyInnerClass[10];

    public static bool IsEmpty()

    {

        foreach (var item in myArray)

        {

            if ((item != null) && (!string.IsNullOrEmpty(item.Field)))

            {

                return false;

            }

        }

        return true;

    }

    private class MyInnerClass

    {

        public string Field;

    }

}

If I want to write a test for the case when the array has “non empty” entries, I need to setup the array first.

Using the accessors generated by Visual Studio, I would write something like this:

[TestClass()]

public class MyClassTest

{

    [TestMethod()]

    public void IsEmpty_NotEmpty_ReturnsFalse()

    {

        for (int i = 0; i <>
        {
            MyClass_Accessor.myArray[i] = new MyClass_Accessor.MyInnerClass 

{ Field = i.ToString() };
        }
 
        bool expected = false;
        bool actual;
 
        actual = MyClass.IsEmpty();
 
        Assert.AreEqual(expected, actual);
    }
}
  
  
But the test will fail because, although the elements of

the private array myArray can be read as MyClass_Accessor.MyInnerClass instances,

they can’t be written as such.
  
To do so, the test would have to be written like this:
  
[TestClass()]
public class MyClassTest
{
    [TestMethod()]
    public void IsEmpty_NotEmpty_ReturnsFalse()
    {
        for (int i = 0; i <>
        {
            MyClass_Accessor.ShadowedType.SetStaticArrayElement

("myArray", new MyClass_Accessor.MyInnerClass { Field = i.ToString() }.Target, i);
        }
 
        bool expected = false;
        bool actual;
 
        actual = MyClass.IsEmpty();
 
        Assert.AreEqual(expected, actual);
    }
}
  
But, this way, we loose all the strong typing of the accessors because we need to

write the name of the array field.
  
Because the accessor for the field is a property, we could write a set of

extension methods that take care of getting the field name for us. Something

like this:
  
  
public static class PrivateypeExtensions
{
    public static void SetStaticArrayElement(this PrivateType self,

Expression<Func> expression, T value, params int[] indices)
    {
        object elementValue = (value is BaseShadow) ? (value as BaseShadow).Target : value;
 
        self.SetStaticArrayElement(
            ((PropertyInfo)((MemberExpression)(expression.Body)).Member).Name,
            elementValue,
            indices);
    }
 
    public static void SetStaticArrayElement(this PrivateType self, Expression<Func>

 expression, BindingFlags invokeAttr, T value, params int[] indices)
    {
        object elementValue = (value is BaseShadow) ? (value as BaseShadow).Target : value;
 
        self.SetStaticArrayElement(
            ((PropertyInfo)((MemberExpression)(expression.Body)).Member).Name,
            invokeAttr,
            elementValue,
            indices);
    }
}
  
  
Now, we can write the test like this:
  
[TestClass()]
public class MyClassTest
{
    [TestMethod()]
    public void IsEmpty_NotEmpty_ReturnsFalse()
    {
        for (int i = 0; i <>
        {
            MyClass_Accessor.ShadowedType.SetStaticArrayElement(() => 

MyClass_Accessor.myArray, new MyClass_Accessor.MyInnerClass { Field = i.ToString() }, i);
        }
 
        bool expected = false;
        bool actual;
 
        actual = MyClass.IsEmpty();
 
        Assert.AreEqual(expected, actual);
    }
}
  
  
It’s not the same as the first form, but it’s strongly typed and we’ll get a compiler

error instead of a test run error if we change the name of the myArray field.
  
 
  

How to Create a Web Server Farm Cache

Introduction

Dynamic database-driven web sites are required if your site has large quantities of data to upload, manage, and serve back to your customers. However, IT departments typically say this creates too big a load for the database -- hence many people keep the files on the hard drive along with their script pages. Not only does this not scale well when you expand from one web server to a web farm, it is hard to back-up, and the file system makes for a poor database -- lacking the transactional qualities of a database.

You can keep your files and images in the database if you take advantage of client-side caching and implement a web server file cache. The benefits include:

• All your content in one place: textual, images, and relationships are in the database.
• The ability to deploy the files to multiple web servers, without additional replication work.
• The ability to backup all the content in a single database backup.
• Minimum database reads for files, on low-write / high-read systems.

This article will show a simple way to deploy caching so that you can keep all your content in the database efficiently.

Associated Article

This article is provided in conjunction with another article about streaming uploaded files. The caching article's portion of this web site is in the Cache.aspx file. The code for the upload portion of that web site and the associated file is called UploadBlob.aspx.

Requirements

You need an IIS web server and a SQL server. The SQL tables, and stored procedures are provided in the Upload.sql file. You will also need files to upload. The /images directory of the associated web site has three images that you can use. A small view of each of the three images is below.

The SQL statement for creating the table is:

CREATE TABLE [dbo].[UploadBytesOfBlob](
[FileGuid] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Content] [varbinary](max) NULL,
[ContentType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Size] [numeric](18, 0) NULL,
[NumberOfChunks] [numeric](18, 0) NULL,
[FirstModifiedDate] [datetime] NULL,
[LastModifiedDate] [datetime] NULL,
CONSTRAINT [PK_UploadBytesOfBlob] PRIMARY KEY CLUSTERED
(
[FileGuid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

The primary key for the table in the sql database is called FileGuid and is of type UniqueIdentifier. This is a Guid in terms of asp.net.

Compile and Run the Site

Download, compile, and run the site. Make sure to create the SQL table and stored procedures using the Upload.sql file. You will be presented with the Default.aspx file which has a link to the UploadBlob.aspx page. Upload the ascent.jpg file found in the /images directory. After the upload, you will be redirected to the default.aspx and a GridView will show the image information. Do not click on either link just yet. Open a windows explorer and check that the file guid listed in the default.aspx is not found in the /CacheFolder of the web site. The folder should be empty. When the image is requested, the folder will have a file name guid.dat where the actual database guid is the prefix of the filename.

How the Cache System Works

For the rest of this article we will use the following web site idea. Assume we have an editor who uploads images as part of his work. The image is large and must eventually be loaded onto 12 web servers. The image may change from time to time so that the relevance of the image is current. When the image does change, it is important that the latest version of the image is used and not the image from the client-side cache or the web-server cache.

A customer of the web site then requests the page which includes the image. What happens? For the purposes of explaining the system, there will be several requests.

The First Request

On the very first request, the image is in the database but not in any cache. This system checks the client-side cache for the image including the relevant date of the image using the If-Modified-Since request header. After the file is not found there, since it is the first request, the system checks the web server file cache including the relevant date of the image. The file is not found there, so the last step is to grab the image from the database. Since the web server is grabbing the file, the file is placed in the web's cache folder (our own system, not the IIS cache), and returned to the client. As part of the brower's work to handle the image coming down, it is placed in the client-side cache.

The Second Request

The customer comes back a second time to request the file while the file on the client-side cache is still the most current version. The cache system responds with a 304 and the client's system fetches the image locally. This has saved a trip to the database and it has also saved a trip to the web server's file system.

The Content has Changed

Imagine the editor has now uploaded a new version of the image into the database. All twelve web servers need to be updated with this new version. Instead of uploading the file to all twelve servers at the time of the change, the change is made to each web server as the image is requested. This means that the work of moving the file from the database to the web server is only done when necessary and is based on a client-request. This saves the IT department from having to deal with replication of the content across the web servers.

The Third Request

On the next request, the file in the client's local cache is no longer current. The cache system checks the web server cache to see if that version is the most current. If the web server cache folder does have the most current, then that version is returned to the client. If that web server cache folder version is not the most current, the current image is fetched from the database, reloaded into the web server cache by writing the database content to a file, then returned to the client and placed in the client cache.

The URL for the system looks like cache.aspx?FileGuid=XYZ... The FileGuid is used instead of a name or incremental number for the content so that the database can grow to a large size while still managing the content.

The image URL on the page then looks like .

The Sample Web Site

The sample web site has three main files: default.aspx, cache.aspx, and uploadblob.aspx. Download, compile and run the web site. Make sure to create the SQL table and stored procedures using the Upload.sql file. Put a break point at the top of the Page_Load method of the cache.aspx.cs file. This is the area of code this article focuses on.

The first time you load the file, nothing will appear except a link to the upload page on the default.aspx. As you upload images, this page will show a GridView listing those images.

Click on the Upload File link, and you will go to the upload page. Once you upload the ascent.jpg image, you will be redirected to the default.aspx file which will now show a GridView with a row for the ascent.jpg file:

Set a break point in the cache.aspx at the top of the Page_Load function. Now click on the link in the FileGuid column. The breakpoint should activate in Visual Studio.

How the Code Works

The FileGuid of each row is used in the URL to the cache.aspx file. The URL looks like cache.aspx?FileGuid=91d... The FileGuid is not only the primary key for the database but is also the name of the file in the cache folder.

The code from cache.aspx for reading the FileGuid from the URL is:

// DFB: fileGuid is required
if (Request["FileGuid"] == String.Empty)
throw (new Exception("FileGuid missing"));

// DFB: Capture fileGuid
Guid fileguid = new Guid(Request["FileGuid"]);

// DFB: fileGuid is required
if (fileguid == Guid.Empty)
throw (new Exception("FileGuid missing"));

Notice that the FileGuid value from the Request is cast to a Guid datatype in aspx. This allows you to deal with the Guid object more easily and convert to the appropriate SQL data type when you need to.

Fetch From the Client Cache

The client's browser cache is checked by comparing the If-Modified-Since request header against the LastModifiedDate from the database. If the client's browser cache has the most recent copy, a respond code of 304 is returned.

The code from cache.aspx for checking the client's cache If-Modified-Since request header is:

// DFB: connect to database
using (SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
{
sqlConnection.Open();

// DFB: Get LastModifiedDate value only. We only fetch more from this row as needed.
using (sqlCommand = new SqlCommand("select LastModifiedDate from SingleDataStore where FileGuid='" + fileguid.ToString() + "'", sqlConnection))
{
lastModifiedDate = DateTime.Parse(sqlCommand.ExecuteScalar().ToString());
}

// DFB: Get Client-side Browser Cache's If-Modified-Since Date
if(!String.IsNullOrEmpty(Request.Headers["If-Modified-Since"]))
{
ifModifiedSince = DateTime.Parse(Request.Headers["If-Modified-Since"], System.Globalization.CultureInfo.InvariantCulture);

// DFB: If the client-side browser cache has the most current version,
// then return 304 and let the client fetch from its own cache
if (lastModifiedDate <= ifModifiedSince)
{
Response.StatusCode = 304;
Response.StatusDescription = "Not Modified";
Response.End();
return;
}
}

From this point on in the code, the content will either come from the local web server's cache or the database.

The Web Server's File System Cache

In order to reduce reads from the database and propagate the content to all web servers in the farm, the cache.aspx file will store a copy on the local file system of each web server in the farm as the content is requested.

The first time a request for the data is made, the data is served from the database and a file is created on the local web server. Each subsequent request is served from the local web server instead of making the unnecessary request for the content from the database if the LastModifiedDate value in the database hasn't changed.

The location of the cache folder is in the web.config. You will want to put the folder at the root of the website. Make sure the IUSR_servername, IWAM_servername, and the ASPNET local accounts have full permission to the cache folder.

// DFB: Build up cache path with file name where file name is guid.dat
// and the guid is the primary key of the table
cachePath = String.Format("{0}{1}.dat", ConfigurationManager.AppSettings["CacheFolder"], fileguid.ToString());

The file name will be FileGuid.dat. The file isn't given an extension associated with the type of content because we do not need to associate it to a type of content until it is requested. It is faster to read the database content type (mime type) than to look up the corresponding file extension in the registry and then determine its content type.

When we set the file's last modified date, the date will be the LastModifiedDate found in the database. This allows the code to determine if the file in the cache is out of sync with the database in future requests for the file.

If the directory is not found, it will be created. If the file is found in the directory, the date of the file is compared to the known LastModifiedDate returned from the database in the above code used for checking the client's cache. If the file is not found in the cache folder, the dirty flag is set to true.

// DFB: if the file system cache folder doesn't exist, create it
if (!Directory.Exists(ConfigurationManager.AppSettings["CacheFolder"]))
{
Directory.CreateDirectory(ConfigurationManager.AppSettings["CacheFolder"]);
dirty = true;
}
else
{
// DFB: Check to see if the file is in the cache
if (File.Exists(cachePath))
{
// DFB: Check the last write time of the file
FileInfo fileInfo = new FileInfo(cachePath);

// DFB: If times are different, mark the flag
// DFB: marking dirty mean to go get new data out of database
lastWriteTime = fileInfo.LastWriteTime;
dirty = (lastWriteTime != lastModifiedDate);
}
else
{
// DFB: file is not found in cache
dirty = true;
}
}

The dirty flag indicates that the content does need to be fetched from the database and written to the cache folder. It is important to understand that the content is read back from the database one chunk at a time inside a WHILE look. This allows for only the chunk size of the entire file to be in memory at a time.

if (dirty)
{
// DFB: Cached File Doesn't Exist or has old data so get data.

// DFB:Write Database Content to file
using (FileStream fileStream = File.Create(cachePath))
{
using (BinaryWriter binaryWriter = new BinaryWriter(fileStream))
{
Int64 sqlRead = 0;
Int64 index = 0;
Byte[] buffer = new Byte[1024];

using (sqlCommand = new SqlCommand("select Content from UploadBytesOfBlob where FileGuid='" + fileguid.ToString() + "'", sqlConnection))
{
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess))
{
if (!sqlDataReader.HasRows)
throw (new Exception("Invalid Id"));

sqlDataReader.Read();

while ((sqlRead = sqlDataReader.GetBytes(0, index, buffer, 0, buffer.Length)) != 0)
{
// WWB: Cast Is Fine Size Buffer < int.MaxValue
binaryWriter.Write(buffer, 0, (int)sqlRead);
index += sqlRead;
}
}
}
}
}

// DFB: Time Stamp The Last Modified Time With
// The Last Modified Time of the FileStore Object
// So that we can track changes
FileInfo fileInfo = new FileInfo(cachePath);
fileInfo.LastWriteTime = lastModifiedDate;
}

Once the file is in the cache folder, you need to construct the response to the client. This includes cache values, the content type, and the content.

// DFB: Setup To Successfully Cache The Page On the Client
Response.Cache.SetLastModified((lastModifiedDate).ToUniversalTime());
Response.Cache.SetCacheability(HttpCacheability.Public);

// DFB: ETag determines if data has changed on server
// DFB: ETag can be server/programmer specific be should
// be generated at the server to indicate data uniqueness
// DFB: For this program, I'll has the row's id with the row's
// last modified date
Response.Cache.SetETag(Hash(Request["FileGuid"].ToString() + lastModifiedDate));

// DFB: Set The Content-Type So the Browser Knows How To Render
// The Response
using (sqlCommand = new SqlCommand("select ContentType from SingleDataStore where FileGuid='" + fileguid.ToString() + "'", sqlConnection))
{
Response.ContentType = sqlCommand.ExecuteScalar().ToString();
}

// DFB: Writing The Cached File Allows IIS To Stream The Output
Response.WriteFile(cachePath);
}
}

Subsequent Requests for the Content

After the content is in a file in the cache folder, all subsequent requests will either come from the client's browser cache or the web server's cache folder until the content LastModifiedDate changes on the server. Since both situations check the LastModifiedDate in the database before returning the content, you can be sure that only the most current version is displayed on the client.

When the data is updated in the database, the client cache and the web server cache versions will be checked for date freshness and the dirty flag will be set to true. At this point, the entire content will be re-fetched from the database and the file recreated on the file system.

Clearing the Web Server Cache

Now that the file is in the web server cache with a file name that corresponds to the FileGuid, you can clear out the cache on a schedule of your choosing. You can delete all the contents, all contents before a certain date, or all contents of a certain content type.

Summary

This caching system illustrates how to build a database-driven web site using the single storage of a database while still using the cache features of the client and web server. The caches are checked using the If-Modified-Since of the cache content and the LastModifiedDate of the database file in order to deliver the most current data. Once the database content is modified, the cached file is no longer valid.

Creating a Data Access Layer for Paging in SQL Server 2005

Introduction In my previous article, Paging in SQL Server 2005, I demonstrated how to support paging the results of a SQL query inside of a stored procedure. In this two-part article, I will tie that logic to an ASP.NET page with a GridView and DetailsView to demonstrate an efficient way to incorporate paging and updating through an ObjectDataSource. Along the way we will use the Class Diagram tool and the Web Application Project template (added to Visual Studio 2005 by Service Pack 1), although you are free to use methods you are more comfortable with. Background Visual Studio 2005 introduced a number of tools that allow web developers to create dynamic web pages more easily than ever. The GridView encapsulates most of the functionality needed for displaying and editing tabular data efficiently, and can bind to several types of data sources. The DetailsView is a sibling to the GridView, supporting most of the same functionality but displaying a single record at a time. While these controls can bind to databases through simple DataSets and SqlDataSources, the ObjectDataSource control provides a more clearly defined, more sophisticated -- but potentially more complicated -- connection. For developers looking to model their SQL data within the application through custom classes, the ObjectDataSource allows them to bind their strongly-typed classes to user controls and add a discrete Business Logic Layer to ensure user interactions follow defined rules of behavior. The Database Layer The first order of business is to create a database to work with; the examples below were created in a database called DALdemo, but you can change the connection string to whatever name you choose. Once you have done this, the following SQL statement will create a simple table to work with: CREATE TABLE [dbo].[Person] ( [PersonID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, [MiddleName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NOT NULL, [Height] [decimal](5, 2) NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [PersonID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] To populate the table with some preliminary test data, execute the following SQL statements: INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Frank', 'Edward', 'Holiday', 62.38) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Nancy', 'Estelle', 'Walkafeller', 52.81) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Stewart', 'R.', 'Garvey', 61) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Jeff', 'A.', 'Walker', 66.43) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Walter', 'Albert', 'Johnson', 65.66) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Barbara', 'Connie', 'Jones', 65.38) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('William', '', 'Smith', 59.68) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Gillian', 'Kay', 'Krissinger', 62.43) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Sam', '', 'Stuart', 65.67) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Richard', 'G.', 'Harding', 63.98) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Laura', 'Edward', 'Kinsley', 70.56) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Frank', 'Quincy', 'George', 67.31) INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES ('Frank', 'P.', 'Williams', 62.99) We will use the following stored procedure to query this table, which incorporates SQL paging logic and uses a ReturnValue parameter to pass back the total number of records. CREATE PROCEDURE [dbo].[utilPAGE] @datasrc nvarchar(200) ,@orderBy nvarchar(200) ,@startPage int = 1 ,@pageSize int = NULL ,@fieldlist nvarchar(200) = '*' ,@filter nvarchar(200) = '' AS SET NOCOUNT ON DECLARE @STMT nvarchar(max) -- SQL to execute ,@recct int -- total # of records SET @recct = -1 IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1' IF @pageSize IS NULL BEGIN SET @STMT = ' SELECT ' + @fieldlist + ' FROM ' + @datasrc + ' WHERE ' + @filter + ' ORDER BY ' + @orderBy EXEC (@STMT) -- return requested records END ELSE BEGIN SET @STMT = ' SELECT @recct = COUNT(*) FROM ' + @datasrc + ' WHERE ' + @filter EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT DECLARE @lbound int, @ubound int IF @startPage < 1 SET @startPage = 1 IF @pageSize < 0 SET @pageSize = @pageSize * -1 IF @pageSize < 1 SET @pageSize = 1 SET @lbound = ((@startPage - 1) * @pageSize) SET @ubound = @lbound + @pageSize + 1 IF @lbound >= @recct BEGIN SET @ubound = @recct + 1 SET @lbound = @ubound - (@pageSize + 1) -- return last page of records END SET @STMT = ' SELECT ' + @fieldlist + ' FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, * FROM ' + @datasrc + ' WHERE ' + @filter + ' ) AS tbl WHERE row > ' + CONVERT(varchar(9), @lbound) + ' AND row < ' + CONVERT(varchar(9), @ubound) + ' ' EXEC (@STMT) -- return requested records END RETURN @recct We will use the PersonSET stored procedure to handle updates to the Person table. PersonSET handles both inserts and updates, with items being inserted if the @ID parameter is less than one: CREATE PROCEDURE [dbo].[PersonSET] ( @ID int = -1 ,@FirstName nvarchar(50) ,@MiddleName nvarchar(50) ,@LastName nvarchar(50) ,@Height decimal(5, 2) ) AS SET NOCOUNT ON BEGIN TRY BEGIN TRANSACTION IF @ID > 0 BEGIN UPDATE Person SET FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName, Height = @Height WHERE PersonID = @ID; END ELSE BEGIN INSERT INTO Person (FirstName, MiddleName, LastName, Height) VALUES (@FirstName, @MiddleName, @LastName, @Height); SET @ID = SCOPE_IDENTITY() END COMMIT TRANSACTION RETURN @ID END TRY BEGIN CATCH DECLARE @ERNUM int SELECT @ERNUM = ERROR_NUMBER() ROLLBACK TRANSACTION IF @ERNUM > 0 SET @ERNUM = -@ERNUM RETURN @ERNUM END CATCH PersonDELETE removes one record at a time from Person, based on the ID field: CREATE PROCEDURE PersonDELETE ( @ID int = 5 ) AS SET NOCOUNT ON DELETE FROM Person WHERE PersonID = @ID The Data Object Layer The next step is to create a new C# ASP.NET Web Application called WebApp1 in Visual Studio. As shown in Illustration 1, enable the option Create directory for solution and set the Solution Name to 15Seconds. This will allow us to work with multiple projects simultaneously. Illustration 1: Creating the Web Project and Solution To create the second project, select File | New | Project and create a new C# Class library called Library1. Be sure to change the default setting from Create new Solution to Add to Solution, as shown in Illustration 2. Illustration 2: Creating the class project within the solution Feel free to close and delete the Class1.cs file created automatically by Visual Studio, as we will not be using it. To create the class we do need, right-click on the Library1 project in the Solution Explorer, select Add | Class, and create a class named PersonEntity.cs. The quickest way to fill in the class is with the Class Editor; in the Solution Explorer, right-click on PersonEntity.cs and select View Class Diagram. The class needs internal data members for holding values, and we will follow the common practice of prefixing an underscore to their names to avoid confusion later. To create these, click on the text, type _ID, and press tab. Set Type to be int, and set modifier to private. Add the next three fields, _FirstName, _MiddleName, and _LastName, all with Type set to string. Lastly, add field _Height with Type set to double. The class needs accessor (set/get) methods so other classes can modify these internal values. To create these, click on the text, type ID, and press tab. Set Type to be int, and set modifier to public. Add the next three properties, called FirstName, MiddleName, and LastName, all with Type set to string. Lastly, add property Height with Type set to double. At this point, your class diagram should resemble Illustration 3. Illustration 3: Creating the PersonEntity skeleton with the Class Diagram tool Right-click on ClassDiagram1.cd and select Rename; change the name to diaPersonEntity.cd. Click on the PersonEntity.cs* tab to see what has been happening in the background. The fields we added will show up as private data members: private int _ID; private string _FirstName; private string _MiddleName; private string _LastName; private double _Height; The properties we added show up as public get/set methods. The ID property is: public int ID { get { throw new System.NotImplementedException(); } set { } } We need to wire the fields and properties together, so that when someone refers to a class property, the relevant field is used. Change the ID property to: public int ID { get { return _ID; } set { _ID = value; } } Making private data members with public get/set methods probably seems like a lot of extra work, but it is an important part of good design that prevents subtle bugs and supports additional functionality. If a class' internal data members are exposed, other classes may inadvertently modify the internal values without trying. Also, set/get methods allow us to ensure the values used meet any requirements we might have. If, for example, we want to ensure that _ID can only be set to values greater than zero, we can add code in the set method to ensure this: public int ID { get { return _ID; } set { if(value > 0) { _ID = value; } else { throw new Exception("ID must be greater than zero"); } } } Alternatively, if we want the internal values to return an empty string when they are set to null, we can change each of the get methods for string properties to use C#'s ?? operator. This is similar to SQL Server's ISNULL() function: if the first value specified is NULL, the second value specified is used instead. This can be used as shown below: public string FirstName { get { return _FirstName ?? ""; } set { _FirstName = value; } } Note that the above code isn't strictly necessary for this project; it is simply an example of things you can do if you want strict validation or other behavior within your custom class (which is the primary benefit of making custom classes). If you still aren't sold on the idea, see http://msdn2.microsoft.com/en-us/library/65zdfbdt(VS.71).aspx for a more complete explanation of the benefits. Since we want to access this class from another project, be sure to change the class declaration to: namespace Library1 { public class PersonEntity { At its simplest, the PersonEntity.cs class should resemble: using System; namespace Library1 { public class PersonEntity { private int _ID; private string _FirstName; private string _MiddleName; private string _LastName; public double _Height; public int ID { get { return _ID; } set { _ID = value; } } public string FirstName { get { return _FirstName; } set { _FirstName = value; } } public string MiddleName { get { return _MiddleName ?? ""; } set { _MiddleName = value; } } public string LastName { get { return _LastName; } set { _LastName = value; } } public double Height { get { return _Height; } set { _Height = value; } } } } The Data Access Layer Continuing to build from the back-end data store towards the user interface, we next need to create the Data Access Layer. The Data Access Layer is a class where you define the connection between an ObjectDataSource, a custom class (in our case, PersonEntity) and the data store for instances of this class (in our case, the Person table in SQL). After you define the relevant methods in the DAL class, you will configure the ObjectDataSource object to use the correct method for each necessary function, and can then set the ASP.NET elements that will use them. Again, this can seem like a lot of overhead, but the extra work helps to make future maintenance easier. Instead of having specific SQL information embedded in each web page, you will define it once and be able to use it from any page that needs it. When you inevitably need to change your SQL table, you can update the class and DAL instead of editing multiple copies of the same queries throughout your web site. To prepare the web application, right-click on WebApp1 in Solution Explorer and select Set As StartUp Project. Next, right-click on References within the WebApp1 project and select Add Reference. Click on the Projects tab and double-click Library1 as a new reference. Rather than hard-coding the database connection string into our DAL class, we will store it in the web.config, where it can be modified as needed. Note that in a production environment, you should encrypt the database connection string for better security. To add your connection string, double-click the web.config file in the WebApp1 project. Modify it to have the following lines, replacing the user id and password with valid credentials and setting the data source and initial catalog with the host and database name you are using, respectively: >connectionStrings> >add name="15seconds" connectionString="user id=demo; pwd=demopass; data source=localhost; initial catalog=15SecondsDAL;" providerName="System.Data.SqlClient" /> >/connectionStrings> To create the Data Access Layer class, right-click in the WebApp1 class and select Add | Add ASP.NET Folder | App_Data (this is the standard location for DAL classes). Right-click on the App_Data folder and select Add | Class and set the name to PersonDAL.cs. The DAL class must support four standard SQL functions: Select, Insert, Update, and Delete. However, the PersonSET stored procedure actually handles inserts and updates automatically, so we can use the same DAL method for both of these functions. In addition to these functions, the DAL class must return the total number of instances available (in our case, the number of records in the Person table). We will store the record count value returned from the utilPAGE stored procedure, so that when a bound data control needs the total number of records for its paging interface, the DAL will return that value instead of querying the database again. At run-time, the ObjectDataSource settings will determine which methods to call from your DAL class for select, insert, update, delete, and count, and what information is passed into those methods. We will need to ensure that the ObjectDataSource configuration matches the DAL methods. In fact, enabling and disabling features such as sorting and paging support within the ObjectDataSource will affect which methods are called, so you may need to create overloaded versions of your DAL methods depending on the combinations of features you intend to support. In this example, however, we will include support for sorting and paging and will create methods which meet these needs. A straightforward implementation of the PersonDAL class is shown below. The System.ComponentModel library lets us add declarative attributes to the source code that will help the .NET compiler catch any mistakes we might make with the DAL methods, and will help when the ObjectDataSource is deciding which DAL method to use. using System; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.Configuration; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using Library1; using WebApp1; namespace WebApp1.App_Data { [DataObject] public class PersonDAL { private int _count = 0; [DataObjectMethod(DataObjectMethodType.Select)] public List Select(int startRecIdx, int maxRows, String sortedBy) { List records = new List(); SqlConnection conn = null; SqlDataReader dr = null; SqlParameter ret = null; try { conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["15seconds"].ConnectionString); conn.Open(); SqlCommand cmd = new SqlCommand("utilPAGE", conn); cmd.CommandType = CommandType.StoredProcedure; if(String.IsNullOrEmpty(sortedBy)) { sortedBy = "LastName"; } int startpg = (startRecIdx / maxRows) + 1; cmd.Parameters.AddWithValue("@datasrc", "Person"); cmd.Parameters.AddWithValue("@orderBy", sortedBy); cmd.Parameters.AddWithValue("@startPage", startpg); cmd.Parameters.AddWithValue("@pageSize", maxRows); ret = new SqlParameter("ReturnValue", SqlDbType.Int); ret.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(ret); dr = cmd.ExecuteReader(); while(dr.Read()) { PersonEntity item = new PersonEntity(); item.ID = (int)dr["PersonID"]; item.FirstName = dr["FirstName"].ToString(); item.MiddleName = dr["MiddleName"].ToString(); item.LastName = dr["LastName"].ToString(); //item.Height = (double)(dr["Height"] ?? 0.0); item.Height = Convert.ToDouble(dr["Height"].ToString()); records.Add(item); } } catch(Exception ex) { throw ex; } finally { if(dr != null) { dr.Close(); } if(conn != null) { conn.Close(); } } _count = Convert.ToInt32(ret.Value); return records; } [DataObjectMethod(DataObjectMethodType.Update)] public static bool Update(PersonEntity item) { bool retval = false; SqlConnection conn = null; try { conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["15seconds"].ConnectionString); conn.Open(); SqlCommand cmd = new SqlCommand("PersonSET", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", item.ID); cmd.Parameters.AddWithValue("@FirstName", item.FirstName); cmd.Parameters.AddWithValue("@MiddleName", item.MiddleName); cmd.Parameters.AddWithValue("@LastName", item.LastName); cmd.Parameters.AddWithValue("@Height", item.Height); SqlParameter ret = new SqlParameter("ReturnValue", SqlDbType.Int); ret.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(ret); cmd.ExecuteNonQuery(); int result = Convert.ToInt32(ret.Value); retval = result > 0; } catch(Exception ex) { throw ex; } finally { if(conn != null) { conn.Close(); } } return retval; } [DataObjectMethod(DataObjectMethodType.Delete)] public static bool Delete(PersonEntity item) { bool retval = false; SqlConnection conn = null; try { conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["15seconds"].ConnectionString); conn.Open(); SqlCommand cmd = new SqlCommand("PersonDELETE", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@ID", item.ID); SqlParameter ret = new SqlParameter("ReturnValue", SqlDbType.Int); ret.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(ret); cmd.ExecuteNonQuery(); int result = Convert.ToInt32(ret.Value); retval = result > 0; } catch(Exception ex) { throw ex; } finally { if(conn != null) { conn.Close(); } } return retval; } public int GetRecordCount(int startRecIdx, int maxRows, string sortedBy) { return _count; } public int GetRecordCount() { return _count; } } } The Select method begins by creating an empty List collection that can hold PersonEntity objects. After declaring some ADO.NET objects, the code enters a try block. This is important, as any SQL objects used in this method need to be closed properly whether the method succeeds or fails. A connection is created based on the web.config connection string entry named 15seconds, and preparations are made for running the utilPAGE stored procedure. Because utilPAGE needs to have a non-blank orderBy argument, the LastName field is used as a default if nothing else is supplied. The parameters for table name, sort field list, starting page number, and page size are added, as is a ReturnValue parameter that will receive anything which the stored procedure explicitly sends with a return statement. The query is executed, the results are converted into new PersonEntity objects, and the PersonEntity objects are added to the List collection. The SQL objects are properly closed in the finally clause, and (because the return value can only be accessed after the SqlDataReader object is closed) the return value is stored in _count. The Update method receives a PersonEntity object when it is called. Because the only difference between an Insert and an Update will be the value of the ID field in this object, we will treat both the same and let the stored procedure handle the necessary logic. The Update method will largely imitate the Select method, except that the stored procedure and arguments are different and no result set will be processed. The return value will be checked to see if the update succeeded, with numbers less than one indicating an error. The Delete method receives a PersonEntity object when it is called, however only the ID field will be populated. (What fields are populated for this is controlled by the DataKeyNames property, which we will see later.) The Delete method is nearly identical to the Update method, except for the stored procedure and arguments. The return value will be checked to see if the delete succeeded, with numbers less than one indicating an error. Conclusion The work so far has focused on laying a foundation, and just as with building an actual foundation, there has been a lot of critical activity but little in the way of visible results. In the second part of this article, we will create an interactive web page with very little code that uses everything created so far.