RepeatHost Business Hosting

Data Access Layer using SqlDataReader and C# - Code Explanation

This is a follow-up to the previous post Data Access Layer using SqlDataReader and C# that describes the details on why and how to set up a Data Access Layer using SqlDataReader. The following is a description of the code involved in wrapping the Category table from the previous post.

First, set up the Utility namespace.

Utility.ColumnDefinition

// Contains a definition of a column in a database table.
public class ColumnDefinition
{
    // private members
    private string m_sColumnName;
    private int m_iLength;
    private System.Data.SqlDbType m_DataType;
    private bool m_bIsAllowNull;
 
    /// <summary>
    /// Defines the column in the database table and it's associated properties.
    /// </summary>
    /// <param name="sColumnName">The name of the column.</param>
    /// <param name="iLength">The length of the column (such as varchar(50) would be length of 50).  
    /// Default to "1" when length is not applicable such as Int or DateTime.</param>
    /// <param name="dataType">The data type of the column as an SQL type.</param>
    /// <param name="bIsAllowNull">Whether or not this column allows nulls.</param>
    public ColumnDefinition(string sColumnName, int iLength, System.Data.SqlDbType dataType, bool bIsAllowNull)
    {
        m_sColumnName = sColumnName;
        m_iLength = iLength;
        m_DataType = dataType;
        m_bIsAllowNull = bIsAllowNull;
    }
 
    // The name of the column.
    public string ColumnName { get { return m_sColumnName; } }
 
    // The length of the column in terms of number of characters.
    public int Length { get { return m_iLength; } }
 
    // The data type of the column as an SQL type.
    public System.Data.SqlDbType DataType { get { return m_DataType; } }
 
    // Whether or not this column allows nulls.
    public bool IsAllowNull { get { return m_bIsAllowNull; } }
}

Utility.DalUtility

// Utility methods for the data access layer.
public class DalUtility
{
    // This is a global method to get your connection string whatever way you want.
    // Here we read the connection string from the configuration file ConnectionStrings section.
    public static string GetConnectionString()
    {
        return System.Configuration.ConfigurationManager.ConnectionStrings[1].ConnectionString;
    }
 
    // Convenient wrapper function to create a SqlParameter object
    public static System.Data.SqlClient.SqlParameter BuildSqlParameter(System.Data.SqlDbType dataType, string sParameterName, object oValue)
    {
        System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter();
        param.SqlDbType = dataType;
 
        // If value is null, set to DBNull so it will be null in the database
        if (oValue == null)
        {
            param.Value = DBNull.Value;
        }
        else
        {
            param.Value = oValue;
        }
 
        // Take whatever the parameter name is and add "@" to the beginning to make it a unique name
        param.ParameterName = "@" + sParameterName;
        return param;
    }
}

Now set up the schema for the Category table in the Schema\CategorySchema.cs file.

Schema.CategorySchema

// Schema definition for the Category table
public class CategorySchema
{
    // Create an enumeration of the columns in the table.  This will be used to XOR
    // together the columns you want returned in a select query.
    // NOTE: You must have the FlagsAttribute so you can XOR the enumeration correctly.
    // ALSO NOTE: I set this to long so it will support tables with a large number of columns.
    [FlagsAttribute]
    public enum ColumnList : long
    {
        Undefined = 0,
        PrimaryKey = 1,
        UserAccountFk = 2,
        CategoryName = 4,
        DateCreated = 8,
 
        // Add some predefined XOR's so it is less typing to get all columns or a custom subset.
        All = PrimaryKey | UserAccountFk | CategoryName | DateCreated,
        // Insert is used to define the column list that is allowed to be inserted as a new record.
        Insert = UserAccountFk | CategoryName | DateCreated,
        CategoryDetails = PrimaryKey | CategoryName
    }
 
    // The name of the table
    public static string TableName { get { return "Category"; } }
 
    // The column definitions of the table.
    // See Utility.ColumnDefinition for details on the properties of a column.
    public static Utility.ColumnDefinition PrimaryKey { get { return new Utility.ColumnDefinition("PrimaryKey", 1, System.Data.SqlDbType.Int, false); } }
    public static Utility.ColumnDefinition UserAccountFk { get { return new Utility.ColumnDefinition("UserAccountFk", 1, System.Data.SqlDbType.Int, true); } }
    public static Utility.ColumnDefinition CategoryName { get { return new Utility.ColumnDefinition("CategoryName", 50, System.Data.SqlDbType.NVarChar, false); } }
    public static Utility.ColumnDefinition DateCreated { get { return new Utility.ColumnDefinition("DateCreated", 1, System.Data.SqlDbType.DateTime, false); } }
}

Now add the builder for the queries to the Category table. Here we've put 2 classes into the same file (not generally recommneded in .NET) but it was more of a convenience thing since they are not interacted with directly by the user and they are relatively simple classes anyway. Here is what should be in the QueryBuilder\CategoryBuilder.cs file.

QueryBuilder.CategoryBuilder

// This is a container class that stores each column in the query and what 
// ordinal position each column is at.
public class CategoryBuilder
{
    // Constructor - Stores a generic List of CategoryColumns
    // which define the column list and ordinal position for each
    // column referenced in the query.
    public CategoryBuilder()
    {
        this.CategoryColumnList = new List<QueryBuilder.CategoryColumn>();
    }
 
    // Public properties
    public List<QueryBuilder.CategoryColumn> CategoryColumnList { get; set; }
    // stores the SQL used in the query for easy access in the caller.
    public string ColumnListSql { get; set; }
}
 
// Object that holds a column and ordinal position in the query.  This is used to keep track of what 
// columns are in what order in the select query.
public class CategoryColumn
{
    // Constructor - Specify the ordinal position of the column and the enumerated value
    // from the schema that relates to this column.  For example, if the column was 
    // CategoryName and this was the 3rd field returned in the Select query order, then
    // the values would be OrdinalPosition = 2 (zero based array) and the columnType = 
    // Schema.CategorySchema.ColumnList.CategoryName
    public CategoryColumn(int iOrdinalPosition, Schema.CategorySchema.ColumnList columnType)
    {
        this.ColumnListType = columnType;
        this.OrdinalPosition = iOrdinalPosition;
    }
 
    // Public properties
    public Schema.CategorySchema.ColumnList ColumnListType { get; set; }
    public int OrdinalPosition { get; set; }
}

Now create the Category object in the DataObject namespace which is a direct strongly typed representation of a row in the Category table. Notice that there is a public property for each column in the table.

IMPORTANT: If a column in the table allows nulls, it must be added as a System.Nullable<> field. Notice that the UserAccountFk field in the Category table allows null so it is not specified as int here, instead it is System.Nullable. For details on nullable fields in C#, see this post:

Using Nullable Data Types with C#

DataObject.Category

// Data object for a row in the Category table.
public class Category
{
    // Currently loaded columns in this object.
    private Schema.CategorySchema.ColumnList m_ColumnListLoaded;
 
    // Private member list of all columns in this record.
    // Primary key of the table.  Does not allow nulls.
    private int m_PrimaryKey;
    // Foreign key to the UserAccount table.  Allows nulls so it is a nullable datatype.
    private System.Nullable<int> m_UserAccountFk;
    // The name of the category.  Does not allow nulls.
    private string m_CategoryName;
    // The date the record was created.
    private DateTime m_DateCreated;
 
    // Public properties
    public int PrimaryKey
    {
        get
        {
            // If column is loaded, return it.
            if ((m_ColumnListLoaded & Schema.CategorySchema.ColumnList.PrimaryKey) > 0)
            {
                return m_PrimaryKey;
            }
            else
            {
                // Column was not loaded in the query so there is no value so throw exception.
                throw new Exception("PrimaryKey Not Loaded!");
            }
        }
        set
        {
            // If column is loaded, set its value to the value passed to this property.
            if ((m_ColumnListLoaded & Schema.CategorySchema.ColumnList.PrimaryKey) > 0)
            {
                // object has changed so set HasChanges to true.
                m_PrimaryKey = value;
            }
            else
            {
                // Column is not loaded yet so add this column to the object's column load list
                // and set the value of the property.
                m_ColumnListLoaded = m_ColumnListLoaded | Schema.CategorySchema.ColumnList.PrimaryKey;
                m_PrimaryKey = value;
            }
        }
    }
 
    public System.Nullable<int> UserAccountFk
    {
        get
        {
            // If column is loaded, return it.
            if ((m_ColumnListLoaded & Schema.CategorySchema.ColumnList.UserAccountFk) > 0)
            {
                return m_UserAccountFk;
            }
            else
            {
                // Column was not loaded in the query so there is no value so throw exception.
                throw new Exception("UserAccountFk Not Loaded!");
            }
        }
        set
        {
            // If column is loaded, set its value to the value passed to this property.
            if ((m_ColumnListLoaded & Schema.CategorySchema.ColumnList.UserAccountFk) > 0)
            {
                // object has changed so set HasChanges to true.
                m_UserAccountFk = value;
            }
            else
            {
                // Column is not loaded yet so add this column to the object's column load list
                // and set the value of the property.
                m_ColumnListLoaded = m_ColumnListLoaded | Schema.CategorySchema.ColumnList.UserAccountFk;
                m_UserAccountFk = value;
            }
        }
    }
 
    public string CategoryName
    {
        get
        {
            // If column is loaded, return it.
            if ((m_ColumnListLoaded & Schema.CategorySchema.ColumnList.CategoryName) > 0)
            {
                return m_CategoryName;
            }
            else
            {
                // Column was not loaded in the query so there is no value so throw exception.
                throw new Exception("CategoryName Not Loaded!");
            }
        }
        set
        {
            // If column is loaded, set its value to the value passed to this property.
            if ((m_ColumnListLoaded & Schema.CategorySchema.ColumnList.CategoryName) > 0)
            {
                // object has changed so set HasChanges to true.
                m_CategoryName = value;
            }
            else
            {
                // Column is not loaded yet so add this column to the object's column load list
                // and set the value of the property.
                m_ColumnListLoaded = m_ColumnListLoaded | Schema.CategorySchema.ColumnList.CategoryName;
                m_CategoryName = value;
            }
        }
    }
 
    public DateTime DateCreated
    {
        get
        {
            // If column is loaded, return it.
            if ((m_ColumnListLoaded & Schema.CategorySchema.ColumnList.DateCreated) > 0)
            {
                return m_DateCreated;
            }
            else
            {
                // Column was not loaded in the query so there is no value so throw exception.
                throw new Exception("DateCreated Not Loaded!");
            }
        }
        set
        {
            // If column is loaded, set its value to the value passed to this property.
            if ((m_ColumnListLoaded & Schema.CategorySchema.ColumnList.DateCreated) > 0)
            {
                // object has changed so set HasChanges to true.
                m_DateCreated = value;
            }
            else
            {
                // Column is not loaded yet so add this column to the object's column load list
                // and set the value of the property.
                m_ColumnListLoaded = m_ColumnListLoaded | Schema.CategorySchema.ColumnList.DateCreated;
                m_DateCreated = value;
            }
        }
    }
}

Now we can start implementing the actual inserts, updates, deletes and selects in the base class CategoryDataAccessBase class.

CategoryDataAccessBase

// Provides data access to this table for inserts, updates, deletes, and selects.
// This is the basic data access methods for each table.
public class CategoryDataAccessBase
{
    // Constructor
    public CategoryDataAccessBase()
    {
        // Set the connection string
        this.ConnectionString = Utility.DalUtility.GetConnectionString();
    }
 
    // Public properties
    public string ConnectionString { get; set; }
 
// more code goes here...

There are 3 private methods that help build the queries based on the columns specified as well as creating the strongly typed object from the SqlDataReader row that is being read. The first builds the list of columns to use in the select query based on the XOR'd enumeration of columns passed.

// Builds the SQL for the column list that is to be queried based on the enumeration specified.
// bIsParameterized specifies whether or not the columns should be set up as parameterized names
// instead of just using the name directly.  (NOTE: this is useful for the Insert() method.
protected QueryBuilder.CategoryBuilder _BuildColumnList(Schema.CategorySchema.ColumnList columnList, bool bIsParameterized)
{
    // A subset of SQL that comma separates the columns to query for from the list of 
    // enumerated columns requested to load.
    System.Text.StringBuilder sbColumns = new System.Text.StringBuilder();
    // Separator section and the character to preface parameters with.
    string sDelim = ", ";
    string sParam = "@";
 
    // Create a new builder to store each column and it's ordinal position.
    QueryBuilder.CategoryBuilder builder = new QueryBuilder.CategoryBuilder();
    int iOrdinalPosition = 0;
 
    // If this column is loaded, add it to the SQL list and add it to the builder.
    if ((columnList & Schema.CategorySchema.ColumnList.PrimaryKey) > 0)
    {
        // Append to the column's SQL
        if (bIsParameterized)
        {
            sbColumns.Append(sParam);
        }
        sbColumns.Append(Schema.CategorySchema.PrimaryKey.ColumnName);
        sbColumns.Append(sDelim);
 
        // Add this column to the QueryBuilder object.
        builder.CategoryColumnList.Add(new QueryBuilder.CategoryColumn(iOrdinalPosition, Schema.CategorySchema.ColumnList.PrimaryKey));
        // Increment the ordinal position so it is set up for the next column to load.
        iOrdinalPosition++;
    }
 
    if ((columnList & Schema.CategorySchema.ColumnList.UserAccountFk) > 0)
    {
        if (bIsParameterized)
        {
            sbColumns.Append(sParam);
        }
        sbColumns.Append(Schema.CategorySchema.UserAccountFk.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.CategoryColumnList.Add(new QueryBuilder.CategoryColumn(iOrdinalPosition, Schema.CategorySchema.ColumnList.UserAccountFk));
        iOrdinalPosition++;
    }
 
    if ((columnList & Schema.CategorySchema.ColumnList.CategoryName) > 0)
    {
        if (bIsParameterized)
        {
            sbColumns.Append(sParam);
        }
        sbColumns.Append(Schema.CategorySchema.CategoryName.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.CategoryColumnList.Add(new QueryBuilder.CategoryColumn(iOrdinalPosition, Schema.CategorySchema.ColumnList.CategoryName));
        iOrdinalPosition++;
    }
 
    if ((columnList & Schema.CategorySchema.ColumnList.DateCreated) > 0)
    {
        if (bIsParameterized)
        {
            sbColumns.Append(sParam);
        }
        sbColumns.Append(Schema.CategorySchema.DateCreated.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.CategoryColumnList.Add(new QueryBuilder.CategoryColumn(iOrdinalPosition, Schema.CategorySchema.ColumnList.DateCreated));
        iOrdinalPosition++;
    }
 
    // Convert this column list SQL to a string and parse off the ending delimiter if there is one.
    string sColumnList = sbColumns.ToString();
    if (sColumnList.EndsWith(sDelim))
    {
        sColumnList = sColumnList.TrimEnd(sDelim.ToCharArray());
    }
 
    // set the column SQL to the builder so it can be used in queries.
    builder.ColumnListSql = sColumnList;
 
    return builder;
}

BuildObject() creates an object from the row in the SqlDataReader.

// Builds a row object based on the columns loaded and their ordinal position.  Reads each column from the 
// reader and loads the value into the object.
protected DataObject.Category _BuildObject(QueryBuilder.CategoryBuilder builder, System.Data.SqlClient.SqlDataReader reader)
{
    // Object to load the fields specified.
    DataObject.Category category = new DataObject.Category();
 
    // Loop the list of columns loaded.
    for (int i = 0; i < builder.CategoryColumnList.Count; i++)
    {
        // Temp object to speed loop tests.
        QueryBuilder.CategoryColumn column = builder.CategoryColumnList[i];
 
        // check if column matches and if so, load the value.
        if (column.ColumnListType == Schema.CategorySchema.ColumnList.PrimaryKey)
        {
            category.PrimaryKey = reader.GetInt32(column.OrdinalPosition);
        }
        else if (column.ColumnListType == Schema.CategorySchema.ColumnList.UserAccountFk)
        {
            // Because this column allows NULL in the database, we must check if the value
            // being returned is DBNull.  If it is, then set the value to null to match the
            // database.  If it is not DBNull, then set the property to the value.
            if (reader[column.OrdinalPosition] != DBNull.Value)
            {
                category.UserAccountFk = reader.GetInt32(column.OrdinalPosition);
            }
            else
            {
                category.UserAccountFk = null;
            }
        }
        else if (column.ColumnListType == Schema.CategorySchema.ColumnList.CategoryName)
        {
            category.CategoryName = reader.GetString(column.OrdinalPosition);
        }
        else if (column.ColumnListType == Schema.CategorySchema.ColumnList.DateCreated)
        {
            category.DateCreated = reader.GetDateTime(column.OrdinalPosition);
        }
        else
        {
            // The column is not in the list of known columns for this table to throw exception.
            throw new Exception("Unknown column '" + column.ColumnListType.ToString() + "' cannot be found.");
        }
    }
 
    return category;
}

The BuildUpdateColumns() method builds the SQL to specify the list of columns to update.

// For the Update() method, this builds the list of columns to update as specified in the 
// column list to update as a SQL string.  
protected string _BuildUpdateColumns(Schema.CategorySchema.ColumnList columnList)
{
    // Holds the concatenated SQL that is built.
    System.Text.StringBuilder sbColumns = new System.Text.StringBuilder();
    // Constants to hold the delimiter, parameter character to append to the 
    // beginning of the ColumnName, and the standard SQL syntax "=".
    string sDelim = ", ";
    string sParam = "@";
    string sEquals = " = ";
 
    // NOTE: PrimaryKey is not here because we can't allow it to be updated since
    // it is auto-assigned by SQL Server.
 
    // If column is to be updated, add the SQL for this column.  Do for each column.
    if ((columnList & Schema.CategorySchema.ColumnList.UserAccountFk) > 0)
    {
        sbColumns.Append(sParam);
        sbColumns.Append(Schema.CategorySchema.UserAccountFk.ColumnName);
        sbColumns.Append(sEquals);
        sbColumns.Append(Schema.CategorySchema.UserAccountFk.ColumnName);
        sbColumns.Append(sDelim);
    }
 
    if ((columnList & Schema.CategorySchema.ColumnList.CategoryName) > 0)
    {
        sbColumns.Append(sParam);
        sbColumns.Append(Schema.CategorySchema.CategoryName.ColumnName);
        sbColumns.Append(sEquals);
        sbColumns.Append(Schema.CategorySchema.CategoryName.ColumnName);
        sbColumns.Append(sDelim);
    }
 
    if ((columnList & Schema.CategorySchema.ColumnList.DateCreated) > 0)
    {
        sbColumns.Append(sParam);
        sbColumns.Append(Schema.CategorySchema.DateCreated.ColumnName);
        sbColumns.Append(sEquals);
        sbColumns.Append(Schema.CategorySchema.DateCreated.ColumnName);
        sbColumns.Append(sDelim);
    }
 
    // Convert SQL to string and parse off the ending delimiter if there is one.
    string sColumnList = sbColumns.ToString();
    if (sColumnList.EndsWith(sDelim))
    {
        sColumnList = sColumnList.TrimEnd(sDelim.ToCharArray());
    }
 
    return sColumnList;
}

There is one other method that is more of a utility method to make it easier to implement in a new query in the inherited class. This method provides a wrapper on the opening of a connection as well as the building of the strongly typed objects based on the rows returned.

// Provides a convenient wrapper for standard select queries to build the object list.  
protected List<DataObject.Category> _ExecuteSelectQuery(System.Data.SqlClient.SqlCommand cmd, QueryBuilder.CategoryBuilder builder)
{
    // The list of objects to return.
    List<DataObject.Category> categoryList = new List<DataObject.Category>();
 
    using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
    {
        // Open connection to database
        cmd.Connection = con;
        con.Open();
 
        // Loop the reader rows and build an object for each row.
        using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                // Add object to the generic List.
                categoryList.Add(_BuildObject(builder, reader));
            }
        }
    }
 
    return categoryList;
}

Now we can implement the base methods that apply to every table. First we set up a Select by Primary Key. By using the first overload, you can specify exactly what columns you want to load. The second method is a nice short hand that load all columns.

// Gets the category specified by the PrimaryKey
public List<DataObject.Category> SelectBy_PrimaryKey(int iPrimaryKey, Schema.CategorySchema.ColumnList columnLoadList)
{
    // List of row(s) to return
    List<DataObject.Category> categoryList = new List<DataObject.Category>();
 
    // Get list of columns to load in this select query.
    QueryBuilder.CategoryBuilder builder = _BuildColumnList(columnLoadList, false);
 
    // Build the SQL to retrieve the row by Id.
    // To avoid hard-coded strings, use the schema properties to build the SQL for this table
    System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
    sbSql.Append("select ");
    sbSql.Append(builder.ColumnListSql);
    sbSql.Append(" from ");
    sbSql.Append(Schema.CategorySchema.TableName);
    sbSql.Append(" where ");
    sbSql.Append(Schema.CategorySchema.PrimaryKey.ColumnName);
    sbSql.Append(" = @");
    sbSql.Append(Schema.CategorySchema.PrimaryKey.ColumnName);
 
    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
    {
        cmd.CommandText = sbSql.ToString();
 
        // Add parameters (always use a parameterized query)
        cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(Schema.CategorySchema.PrimaryKey.DataType, Schema.CategorySchema.PrimaryKey.ColumnName, iPrimaryKey));
 
        categoryList = _ExecuteSelectQuery(cmd, builder);
    }
 
    return categoryList;
}
 
// Overloaded method that specifies to load all columns.
public List<DataObject.Category> SelectBy_PrimaryKey(int iPrimaryKey)
{
    return SelectBy_PrimaryKey(iPrimaryKey, Schema.CategorySchema.ColumnList.All);
}

Here is the method to insert. For an insert, all fields that do not allow null MUST be filled into the category object being passed in.

// Add a new record to the category table with the values in the category object parameter.
// NOTE: No column load list is specified because this an insert so all non-null fields 
// are required.
public DataObject.Category Insert(DataObject.Category category)
{
    // Set DateCreated to now because we inserting.
    category.DateCreated = DateTime.Now;
 
    // Build insert SQL from column lists.
    // NOTE: scope_identity() is called to return the newly inserted primary key.
    System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
    sbSql.Append("insert into ");
    sbSql.Append(Schema.CategorySchema.TableName);
    sbSql.Append(" (");
    sbSql.Append(_BuildColumnList(Schema.CategorySchema.ColumnList.Insert, false).ColumnListSql);
    sbSql.Append(") values (");
    sbSql.Append(_BuildColumnList(Schema.CategorySchema.ColumnList.Insert, true).ColumnListSql);
    sbSql.Append("); select scope_identity()");
 
    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
    {
        cmd.CommandText = sbSql.ToString();
 
        // Add parameters for parameterized query.
        // NOTE: PrimaryKey is not here because that is auto-assigned by SQL Server.
        cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(
            Schema.CategorySchema.UserAccountFk.DataType, 
            Schema.CategorySchema.UserAccountFk.ColumnName, 
            category.UserAccountFk));
        cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(
            Schema.CategorySchema.CategoryName.DataType, 
            Schema.CategorySchema.CategoryName.ColumnName, 
            category.CategoryName));
        cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(
            Schema.CategorySchema.DateCreated.DataType, 
            Schema.CategorySchema.DateCreated.ColumnName, 
            category.DateCreated));
 
        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
        {
            // Open connection to database
            cmd.Connection = con;
            con.Open();
 
            // Execute query and get the newly assigned primary key from the inserted record
            // and add it to the category object to be returned.
            object oPrimaryKey = cmd.ExecuteScalar();
            int iPrimaryKey = Convert.ToInt32(oPrimaryKey);
            category.PrimaryKey = iPrimaryKey;
        }
    }
 
    return category;
}

The delete method removes the record at the row specified by the primary key.

// Delete the record specified by iPrimaryKey
public void Delete(int iPrimaryKey)
{
    // Build the SQL using the table schema to minimize hard-coded strings
    StringBuilder sbSql = new StringBuilder();
    sbSql.Append("delete from ");
    sbSql.Append(Schema.CategorySchema.TableName);
    sbSql.Append(" where ");
    sbSql.Append(Schema.CategorySchema.PrimaryKey.ColumnName);
    sbSql.Append(" = @");
    sbSql.Append(Schema.CategorySchema.PrimaryKey.ColumnName);
 
    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
    {
        cmd.CommandText = sbSql.ToString();
 
        // Add parameters for parameterized query
        cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(System.Data.SqlDbType.Int, Schema.CategorySchema.PrimaryKey.ColumnName, iPrimaryKey));
 
        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
        {
            // Open connection to database
            cmd.Connection = con;
            con.Open();
 
            int iRowsEffected = cmd.ExecuteNonQuery();
 
            // Make sure at least one row was effected or throw exception
            if (iRowsEffected < 1)
            {
                throw new Exception("Row could not be updated.");
            }
        }
    }
}

Finally, in this file, there is the Update() method. This allows you to specify a list of columns to be updated and it will dynamically build the SQL for this Update to only update the columns you want.

// Update the record by PrimaryKey for the columns specified to update.
public void Update(DataObject.Category category, Schema.CategorySchema.ColumnList columnsToUpdate)
{
    // Build the SQL using the table schema so we minimize hard-coded strings
    System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
    sbSql.Append("update ");
    sbSql.Append(Schema.CategorySchema.TableName);
    sbSql.Append(" set ");
    sbSql.Append(_BuildUpdateColumns(columnsToUpdate));
    sbSql.Append(" where ");
    sbSql.Append(Schema.CategorySchema.PrimaryKey.ColumnName);
    sbSql.Append(" = @");
    sbSql.Append(Schema.CategorySchema.PrimaryKey.ColumnName);
 
    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
    {
        cmd.CommandText = sbSql.ToString();
 
        // For each column to update, add a parameter for the parameterized query.
        // NOTE: PrimaryKey is not here because it cannot be updated.  It is auto-assigned.
        if ((columnsToUpdate & Schema.CategorySchema.ColumnList.UserAccountFk) > 0)
        {
            cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(Schema.CategorySchema.UserAccountFk.DataType, Schema.CategorySchema.UserAccountFk.ColumnName, category.UserAccountFk));
        }
 
        if ((columnsToUpdate & Schema.CategorySchema.ColumnList.CategoryName) > 0)
        {
            cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(Schema.CategorySchema.CategoryName.DataType, Schema.CategorySchema.CategoryName.ColumnName, category.CategoryName));
        }
 
        if ((columnsToUpdate & Schema.CategorySchema.ColumnList.DateCreated) > 0)
        {
            cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(Schema.CategorySchema.DateCreated.DataType, Schema.CategorySchema.DateCreated.ColumnName, category.DateCreated));
        }
 
        // Add PrimaryKey here for the "where" clause parameter (but not to update the primary key)
        cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(Schema.CategorySchema.PrimaryKey.DataType, Schema.CategorySchema.PrimaryKey.ColumnName, category.PrimaryKey));
 
        using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
        {
            // Open connection to database
            cmd.Connection = con;
            con.Open();
 
            int iRowsEffected = cmd.ExecuteNonQuery();
 
            // Make sure at least one row was effected or throw exception
            if (iRowsEffected < 1)
            {
                throw new Exception("Row could not be updated.");
            }
        }
    }
}

Finally, we add the CategoryDataAccess.cs class which inherits from Base.CategoryDataAccessBase.cs. This is the file where you can add other select methods (inserts, updates, and deletes are already handled in the base class) to do more specific selects for this table that are not generically applicable across other tables in your database. 

// Provides data access to the Category table.  All custom queries should be 
// placed in this class and this should be the only class used to interact
// with the Category table.
public class CategoryDataAccess : Base.CategoryDataAccessBase
{
    // Constructor calls base class constructor
    public CategoryDataAccess() : base()
    {
        // Empty constructor
    }
 
    // Gets the category specified by the CategoryName
    public List<DataObject.Category> SelectBy_CategoryName(string sCategoryName, Schema.CategorySchema.ColumnList columnLoadList)
    {
        // List of row(s) to return
        List<DataObject.Category> categoryList = new List<DataObject.Category>();
 
        // Get list of columns to load in this select query.
        QueryBuilder.CategoryBuilder builder = _BuildColumnList(columnLoadList, false);
 
        // Build the SQL to retrieve the row.
        System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
        sbSql.Append("select ");
        sbSql.Append(builder.ColumnListSql);
        sbSql.Append(" from ");
        sbSql.Append(Schema.CategorySchema.TableName);
        sbSql.Append(" where ");
        sbSql.Append(Schema.CategorySchema.CategoryName.ColumnName);
        sbSql.Append(" = @");
        sbSql.Append(Schema.CategorySchema.CategoryName.ColumnName);
 
        using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
        {
            cmd.CommandText = sbSql.ToString();
 
            // Add parameters (always use a parameterized query)
            cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(Schema.CategorySchema.CategoryName.DataType, Schema.CategorySchema.CategoryName.ColumnName, sCategoryName));
 
            categoryList = _ExecuteSelectQuery(cmd, builder);
        }
 
        return categoryList;
    }
 
    // Overloaded method that specifies to load all columns.
    public List<DataObject.Category> SelectBy_CategoryName(string sCategoryName)
    {
        return SelectBy_CategoryName(sCategoryName, Schema.CategorySchema.ColumnList.All);
    }
}

You can follow these same steps to wrap each table in your database.