RepeatHost Business Hosting

Data Access Layer using SqlDataReader and C# - Joins

This is a follow-up post to the 2 previous posts that describe how to make a fast and efficient data access layer using SqlDataReader and C#. Here are the 2 previous posts that this relates to:

Data Access Layer using SqlDataReader and C#

Data Access Layer using SqlDataReader and C# - Code Explanation

Here we are going to talk about how to do SQL JOIN's using this new data access layer model. The previous posts only talk about using a "per table" wrapper that allows you to quickly query a single table at a time. Here we will discuss how to query multiple tables at once using a SQL JOIN.

First, we need to create a new table to accompany our Category table from the previous posts. This table will have a foreign key relationship with the Category table. We will make a table called "ListItem". Here is the SQL to create this table:

-- create the ListItem table.
-- CategoryFk is a foreign to Category.Id
create table ListItem     
(        
    PrimaryKey int identity(1000,1) not null, 
    CategoryFk int not null,       
    ListName nvarchar(50) not null,            
    DateCreated datetime not null,                
    constraint PK_ListItem primary key (PrimaryKey),    
)

Now set up the foreign key relationship between the Category and ListItem table.

-- add foreign key constraint to Category table for ListItem    
alter table ListItem  with check add constraint FK_ListItem_Category foreign key(CategoryFk)    
references Category (PrimaryKey)     
 
-- set up a check constraint on this foreign key for this table    
alter table ListItem check constraint FK_ListItem_Category

Now if you created a diagram in SQL Server, you would see something like this for the relationship between the 2 tables:

sql management studio join diagram

Now wrap the ListItem table the same way you wrapped the Category table. Use this post for instructions on the step by step process to get this table wrapped:

Data Access Layer using SqlDataReader and C# - Joins

Once the table is wrapped, your project structure should look something like this:

project view

Now we can add wrapper classes for our join to these 2 tables. The goal here is to do as much code reuse as possible. One really cool thing that this method does is it "optimize out" any table joins that are not used. In other words, if a join query has 7 possible tables, but you only need fields in 2 of them, then it doesn't do the extra joins that aren't needed. It does all of this on the fly at run-time so it naturally makes your queries quicker and more efficient.

Schema\ListItemJoinSchema.cs


First, we need to add a new schema definition to the Schema directory called "JoinListItemSchema.cs". This will wrap the schemas for all possible tables that could be joined to this ListItem. In our case, we will have schema definitions for Category and ListItem. Here's the code:

// Schema definition for JOIN of ListItem and Category tables.
public class JoinListItemSchema
{
    // This enumeration contains "extra" fields that may be used in a join query
    // where a calculated value is returned that is not part of a specific column.
    // For instance, there may be a sum or count field calculated that can be 
    // returned here.
    [FlagsAttribute]
    public enum ColumnList : long
    {
        Undefined = 0,
        CalculatedField = 1
    }
 
    // Private members to hold the list of columns selected for 
    // this join.
    private Schema.ListItemSchema.ColumnList m_ListItemSchemaList;
    private Schema.CategorySchema.ColumnList m_CategorySchemaList;
    private Schema.JoinListItemSchema.ColumnList m_JoinListItemSchemaList;
 
    // Constructor.  Initialize column definitions to Undefined
    // by default.
    public JoinListItemSchema()
    {
        m_ListItemSchemaList = Schema.ListItemSchema.ColumnList.Undefined;
        m_CategorySchemaList = Schema.CategorySchema.ColumnList.Undefined;
    }
 
    // Public properties to get the list of columns for each 
    // join table.
    public Schema.ListItemSchema.ColumnList ListItemSchemaList
    {
        get { return m_ListItemSchemaList; }
        set { m_ListItemSchemaList = value; }
    }
 
    public Schema.CategorySchema.ColumnList CategorySchemaList
    {
        get { return m_CategorySchemaList; }
        set { m_CategorySchemaList = value; }
    }
 
    public Schema.JoinListItemSchema.ColumnList JoinListItemSchemaList
    {
        get { return m_JoinListItemSchemaList; }
        set { m_JoinListItemSchemaList = value; }
    }
}

QueryBuilder\JoinListItemQueryBuilder.cs


Now we can add the query builder object to store the fields and assist us in the building the columns for the SQL queries.

// Builder utility class to keep track of what fields / columns are selected
// for the JOIN'd list of columns.
public class JoinListItemBuilder
{
    // Private members hold the list of columns for each table
    // that is joined here.
    private List<QueryBuilder.ListItemColumn> m_ListItemColumnList;
    private Schema.ListItemSchema.ColumnList m_ListItemSchemaList;
    private List<QueryBuilder.CategoryColumn> m_CategoryColumnList;
    private Schema.CategorySchema.ColumnList m_CategorySchemaList;
    private List<QueryBuilder.JoinListItemColumn> m_JoinListItemColumnList;
    private Schema.JoinListItemSchema.ColumnList m_JoinListItemSchemaList;
 
    // Constructor.  Default all private members to Undefined and
    // list of columns initialized.
    public JoinListItemBuilder()
    {
        m_ListItemColumnList = new List<ListItemColumn>();
        m_CategoryColumnList = new List<CategoryColumn>();
        m_JoinListItemColumnList = new List<JoinListItemColumn>();
        m_ListItemSchemaList = Schema.ListItemSchema.ColumnList.Undefined;
        m_CategorySchemaList = Schema.CategorySchema.ColumnList.Undefined;
        m_JoinListItemSchemaList = Schema.JoinListItemSchema.ColumnList.Undefined;
    }
 
    public List<QueryBuilder.ListItemColumn> ListItemColumnList
    {
        get { return m_ListItemColumnList; }
        set { m_ListItemColumnList = value; }
    }
 
    public Schema.ListItemSchema.ColumnList ListItemSchemaList
    {
        get { return m_ListItemSchemaList; }
        set { m_ListItemSchemaList = value; }
    }
 
    public List<QueryBuilder.CategoryColumn> CategoryColumnList
    {
        get { return m_CategoryColumnList; }
        set { m_CategoryColumnList = value; }
    }
 
    public Schema.CategorySchema.ColumnList CategorySchemaList
    {
        get { return m_CategorySchemaList; }
        set { m_CategorySchemaList = value; }
    }
 
    public List<QueryBuilder.JoinListItemColumn> JoinListItemColumnList
    {
        get { return m_JoinListItemColumnList; }
        set { m_JoinListItemColumnList = value; }
    }
 
    public Schema.JoinListItemSchema.ColumnList JoinListItemSchemaList
    {
        get { return m_JoinListItemSchemaList; }
        set { m_JoinListItemSchemaList = value; }
    }
 
    // Convenient property to hold the SQL generated for the 
    // list of columns to select.
    public string ColumnListSql { get; set; }
}
 
public class JoinListItemColumn
{
    // stores the columsn that are specific to the join like the calculated field.
    public JoinListItemColumn(int iOrdinalPosition, Schema.JoinListItemSchema.ColumnList columnType)
    {
        this.ColumnListType = columnType;
        this.OrdinalPosition = iOrdinalPosition;
    }
    public Schema.JoinListItemSchema.ColumnList ColumnListType { get; set; }
    public int OrdinalPosition { get; set; }
}

DataObject\JoinListItem.cs

Next, add the wrapper object which is actually a collection of all possible tables that can be joined. This object encapsulates each object to make the in memory representation of a row returned from the join.

// Holds a "row" in the JOIN'd object.  Basically, each
// table's wrapper object is encapsulated here to build the 
// overall JOIN row object.
public class JoinListItem
{
    // Private members: List of all tables that are
    // possible to be joined in this table.
    private ListItem m_ListItem;
    private Category m_Category;
 
    // Holds an optional field value that can be added
    // to the join query such as a sum() or count()
    // column that was calculated.
    // NOTE: this is stored as an object so you can 
    // shove anything you want into it.
    private object m_CalculatedField;
 
    // Constructor: creates a new object
    // for each joined table.
    public JoinListItem()
    {
        m_ListItem = new ListItem();
        m_Category = new Category();
        m_CalculatedField = null;
    }
 
    // Property to hold the ListItem table
    // wrapper object.
    public ListItem ListItemTable
    {
        get { return m_ListItem; }
        set { m_ListItem = value; }
    }
 
    // Property to hold the Category table
    // wrapper object.
    public Category CategoryTable
    {
        get { return m_Category; }
        set { m_Category = value; }
    }
 
    // Property for calculated field.
    public object CalculatedField
    {
        get { return m_CalculatedField; }
        set { m_CalculatedField = value; }
    }
}

Base\JoinListItemDataAccessBase

This is the base class to actually perform the queries for the join. There is a lot of code here so we will break it up by method.

// Provides data access to JOIN of the ListItem table.  Only SQL SELECT 
// methods are here because we can't do inserts, updates, or deletes on 
// the joined row as a whole.  We need to do these using our wrapper 
// objects for each table.
public class JoinListItemDataAccessBase
{
    // Constructor
    public JoinListItemDataAccessBase()
    {
        // Set the connection string
        this.ConnectionString = Utility.DalUtility.GetConnectionString();
    }
 
    // Public properties
    public string ConnectionString { get; set; }
 
    // ... code continued below...

This method builds the list of columns and their SQL that should be used in the join query based on what was specified in the column list enumeration.

<!-- code formatted by http://manoli.net/csharpformat/ -->

// Builds the SQL for the column list that is to be queried based on the enumeration specified.
protected QueryBuilder.JoinListItemBuilder _BuildColumnList(QueryBuilder.JoinListItemBuilder columnList)
{
    // 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 character for SQL query
    string sDelim = ", ";
 
    // Create a new builder 
    QueryBuilder.JoinListItemBuilder builder = new QueryBuilder.JoinListItemBuilder();
    int iOrdinalPosition = 0;
 
    // Check if the optional calculated field is specified to be loaded.
    if ((columnList.JoinListItemSchemaList & Schema.JoinListItemSchema.ColumnList.CalculatedField) > 0)
    {
        // add a alias name for this calculated field
        sbColumns.Append(Schema.JoinListItemSchema.ColumnList.CalculatedField.ToString());
        sbColumns.Append(sDelim);
 
        builder.JoinListItemColumnList.Add(new QueryBuilder.JoinListItemColumn(iOrdinalPosition, Schema.JoinListItemSchema.ColumnList.CalculatedField));
        iOrdinalPosition++;
    }
 
    // Check each column in the ListItem table to see if it is to be loaded 
    // and add the SQL for each column that is loaded.
    if ((columnList.ListItemSchemaList & Schema.ListItemSchema.ColumnList.PrimaryKey) > 0)
    {
        // NOTE: because this is a JOIN of tables, we need to refer to columns with the 
        // following syntax [Table Name].[Column Name].  This prevents name collisions
        // in the case that 2 tables have the same column name.
        sbColumns.Append(Schema.ListItemSchema.TableName);
        sbColumns.Append(".");
        sbColumns.Append(Schema.ListItemSchema.PrimaryKey.ColumnName);
        sbColumns.Append(sDelim);
 
        // Add this column to the builder
        builder.ListItemColumnList.Add(new QueryBuilder.ListItemColumn(iOrdinalPosition, Schema.ListItemSchema.ColumnList.PrimaryKey));
        iOrdinalPosition++;
    }
 
    if ((columnList.ListItemSchemaList & Schema.ListItemSchema.ColumnList.CategoryFk) > 0)
    {
        sbColumns.Append(Schema.ListItemSchema.TableName);
        sbColumns.Append(".");
        sbColumns.Append(Schema.ListItemSchema.CategoryFk.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.ListItemColumnList.Add(new QueryBuilder.ListItemColumn(iOrdinalPosition, Schema.ListItemSchema.ColumnList.CategoryFk));
        iOrdinalPosition++;
    }
 
    if ((columnList.ListItemSchemaList & Schema.ListItemSchema.ColumnList.ListName) > 0)
    {
        sbColumns.Append(Schema.ListItemSchema.TableName);
        sbColumns.Append(".");
        sbColumns.Append(Schema.ListItemSchema.ListName.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.ListItemColumnList.Add(new QueryBuilder.ListItemColumn(iOrdinalPosition, Schema.ListItemSchema.ColumnList.ListName));
        iOrdinalPosition++;
    }
 
    if ((columnList.ListItemSchemaList & Schema.ListItemSchema.ColumnList.DateCreated) > 0)
    {
        sbColumns.Append(Schema.ListItemSchema.TableName);
        sbColumns.Append(".");
        sbColumns.Append(Schema.ListItemSchema.DateCreated.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.ListItemColumnList.Add(new QueryBuilder.ListItemColumn(iOrdinalPosition, Schema.ListItemSchema.ColumnList.DateCreated));
        iOrdinalPosition++;
    }
 
    // Now check each colum in the Category table to see which is loaded.
    if ((columnList.CategorySchemaList & Schema.CategorySchema.ColumnList.PrimaryKey) > 0)
    {
        sbColumns.Append(Schema.CategorySchema.TableName);
        sbColumns.Append(".");
        sbColumns.Append(Schema.CategorySchema.PrimaryKey.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.CategoryColumnList.Add(new QueryBuilder.CategoryColumn(iOrdinalPosition, Schema.CategorySchema.ColumnList.PrimaryKey));
        iOrdinalPosition++;
    }
 
    if ((columnList.CategorySchemaList & Schema.CategorySchema.ColumnList.UserAccountFk) > 0)
    {
        sbColumns.Append(Schema.CategorySchema.TableName);
        sbColumns.Append(".");
        sbColumns.Append(Schema.CategorySchema.UserAccountFk.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.CategoryColumnList.Add(new QueryBuilder.CategoryColumn(iOrdinalPosition, Schema.CategorySchema.ColumnList.UserAccountFk));
        iOrdinalPosition++;
    }
 
    if ((columnList.CategorySchemaList & Schema.CategorySchema.ColumnList.CategoryName) > 0)
    {
        sbColumns.Append(Schema.CategorySchema.TableName);
        sbColumns.Append(".");
        sbColumns.Append(Schema.CategorySchema.CategoryName.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.CategoryColumnList.Add(new QueryBuilder.CategoryColumn(iOrdinalPosition, Schema.CategorySchema.ColumnList.CategoryName));
        iOrdinalPosition++;
    }
 
    if ((columnList.CategorySchemaList & Schema.CategorySchema.ColumnList.DateCreated) > 0)
    {
        sbColumns.Append(Schema.CategorySchema.TableName);
        sbColumns.Append(".");
        sbColumns.Append(Schema.CategorySchema.DateCreated.ColumnName);
        sbColumns.Append(sDelim);
 
        builder.CategoryColumnList.Add(new QueryBuilder.CategoryColumn(iOrdinalPosition, Schema.CategorySchema.ColumnList.DateCreated));
        iOrdinalPosition++;
    }
 
    // Parse off the final "," if it is found so that the
    // SQL is well formed.
    string sColumnList = sbColumns.ToString();
    if (sColumnList.EndsWith(sDelim))
    {
        sColumnList = sColumnList.TrimEnd(sDelim.ToCharArray());
    }
 
    // Store the generated SQL for all of these columns in the builder for 
    // later retrieval.
    builder.ColumnListSql = sColumnList;
 
    // Return the builder with the SQL and column list definition.
    return builder;
}

This method reads a row in the DataReader and builds the join object based on the values returned.

<!-- code formatted by http://manoli.net/csharpformat/ -->

// 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.  This method builds each object that was loaded for each 
// possible table in the JOIN query.
protected DataObject.JoinListItem _BuildObject(QueryBuilder.JoinListItemBuilder builder, 
    System.Data.SqlClient.SqlDataReader reader)
{
    // This is a combination object that encapsulates each table that is a 
    // possible JOIN of ListItem.  In this case, it wraps ListItem and Category.
    DataObject.JoinListItem joinListItem = new DataObject.JoinListItem();
 
    // Loop list of columns loaded for the Join.
    for (int i = 0; i < builder.JoinListItemColumnList.Count; i++)
    {
        QueryBuilder.JoinListItemColumn column = builder.JoinListItemColumnList[i];
 
        if (column.ColumnListType == Schema.JoinListItemSchema.ColumnList.CalculatedField)
        {
            // get value.  Since it is an object we return it as a generic object
            // and then put it in our CalculatedField holder field.
            joinListItem.CalculatedField = reader.GetValue(column.OrdinalPosition);
        }
    }
 
    // Loop the list of columns loaded for the ListItem table.
    for (int i = 0; i < builder.ListItemColumnList.Count; i++)
    {
        // Temp object to speed loop tests
        QueryBuilder.ListItemColumn column = builder.ListItemColumnList[i];
 
        // check if column matches and if so, load the value.
        if (column.ColumnListType == Schema.ListItemSchema.ColumnList.PrimaryKey)
        {
            joinListItem.ListItemTable.PrimaryKey = reader.GetInt32(column.OrdinalPosition);
        }
        else if (column.ColumnListType == Schema.ListItemSchema.ColumnList.CategoryFk)
        {
            joinListItem.ListItemTable.CategoryFk = reader.GetInt32(column.OrdinalPosition);
        }
        else if (column.ColumnListType == Schema.ListItemSchema.ColumnList.ListName)
        {
            joinListItem.ListItemTable.ListName = reader.GetString(column.OrdinalPosition);
        }
        else if (column.ColumnListType == Schema.ListItemSchema.ColumnList.DateCreated)
        {
            joinListItem.ListItemTable.DateCreated = reader.GetDateTime(column.OrdinalPosition);
        }
    }
 
    // Loop the list of columns loaded for the Category table.
    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)
        {
            joinListItem.CategoryTable.PrimaryKey = reader.GetInt32(column.OrdinalPosition);
        }
        else if (column.ColumnListType == Schema.CategorySchema.ColumnList.UserAccountFk)
        {
            // Because this is a nullable field, we have to check if it is NULL before
            // getting it's value directly otherwise set it to NULL.
            if (reader[column.OrdinalPosition] != DBNull.Value)
            {
                joinListItem.CategoryTable.UserAccountFk = reader.GetInt32(column.OrdinalPosition);
            }
            else
            {
                joinListItem.CategoryTable.UserAccountFk = null;
            }
        }
        else if (column.ColumnListType == Schema.CategorySchema.ColumnList.CategoryName)
        {
            joinListItem.CategoryTable.CategoryName = reader.GetString(column.OrdinalPosition);
        }
        else if (column.ColumnListType == Schema.CategorySchema.ColumnList.DateCreated)
        {
            joinListItem.CategoryTable.DateCreated = reader.GetDateTime(column.OrdinalPosition);
        }
    }
 
    return joinListItem;
}

Finally, this is a nice utility method to actually perform the query.

<!-- code formatted by http://manoli.net/csharpformat/ -->

// Provides a convenient wrapper for standard select queries to build the object list.  
protected List<DataObject.JoinListItem> _ExecuteSelectQuery(System.Data.SqlClient.SqlCommand cmd, 
    QueryBuilder.JoinListItemBuilder builder)
{
    // The list of objects to return.
    List<DataObject.JoinListItem> joinListItemList = new List<DataObject.JoinListItem>();
 
    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.
                joinListItemList.Add(_BuildObject(builder, reader));
            }
        }
    }
 
    return joinListItemList;
}

JoinListItemDataAccess.cs

Finally, add this class which inherits from the Base class. This is the only object that should be used to access the database for these join queries.

// Provides data access to the ListItem and Category tables as JOIN's.  All custom queries should be 
// placed in this class and this should be the only class used to interact
// with the join of these tables.
public class JoinListItemDataAccess : Base.JoinListItemDataAccessBase
{
    public JoinListItemDataAccess() : base()
    {
        // Empty constructor
    }
 
    // Returns all rows with the specified iCategoryFk.
    public List<DataObject.JoinListItem> SelectBy_CategoryFk(int iCategoryFk, Schema.JoinListItemSchema columnLoadList)
    {
        if (columnLoadList.ListItemSchemaList == Schema.ListItemSchema.ColumnList.Undefined)
        {
            // make sure at least 1 field in the master table that is to be joined 
            // to is actually selected
            throw new Exception("You must load at least 1 field from the " + 
                Schema.ListItemSchema.TableName + " master join table.");
        }
 
        // Create the object to store the returned rows
        List<DataObject.JoinListItem> joinListItemList = new List<DataObject.JoinListItem>();
 
        // Create the join QueryBuilder to get the list of columns to load for this join
        QueryBuilder.JoinListItemBuilder builderState = new QueryBuilder.JoinListItemBuilder();
        // set the list of columns that are to be loaded for this query for each table.
        builderState.ListItemSchemaList = columnLoadList.ListItemSchemaList;
        builderState.CategorySchemaList = columnLoadList.CategorySchemaList;
 
        // build the sql from the list of columns to be loaded and set it to our builder
        QueryBuilder.JoinListItemBuilder builder = _BuildColumnList(builderState);
 
        // build the SQL for the rest of the query
        System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
        // do select on master table first
        sbSql.Append("select ");
        sbSql.Append(builder.ColumnListSql);
        sbSql.Append(" from ");
        sbSql.Append(Schema.ListItemSchema.TableName);
 
        // now join each sub-table if a column is specified to be returned for this table.
        if (columnLoadList.CategorySchemaList != Schema.CategorySchema.ColumnList.Undefined)
        {
            sbSql.Append(" left outer join ");
            sbSql.Append(Schema.CategorySchema.TableName);
            sbSql.Append(" on ");
            sbSql.Append(Schema.CategorySchema.TableName);
            sbSql.Append(".");
            sbSql.Append(Schema.CategorySchema.PrimaryKey.ColumnName);
            sbSql.Append(" = ");
            sbSql.Append(Schema.ListItemSchema.TableName);
            sbSql.Append(".");
            sbSql.Append(Schema.ListItemSchema.CategoryFk.ColumnName);
        }
 
        using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand())
        {
            // Get the SQL
            cmd.CommandText = sbSql.ToString();
 
            // Add parameter for CategoryFk
            cmd.Parameters.Add(Utility.DalUtility.BuildSqlParameter(Schema.ListItemSchema.CategoryFk.DataType, 
                Schema.ListItemSchema.CategoryFk.ColumnName, iCategoryFk));
 
            // Execute the query and build the join object.
            joinListItemList = _ExecuteSelectQuery(cmd, builder);
        }
 
        return joinListItemList;
    }
}

How to Use the Join Object

Now we can actually use this object. Here's a simple query to get all rows by CategoryFk that are joined against the ListItem table:

// set up the columns we want to load first.  Since this is a join, we have to specify what columns
// we want to load for each table in the join.
MyDal.Data.Schema.JoinListItemSchema schema = new MyDal.Data.Schema.JoinListItemSchema();
schema.CategorySchemaList = MyDal.Data.Schema.CategorySchema.ColumnList.All;
schema.ListItemSchemaList = MyDal.Data.Schema.ListItemSchema.ColumnList.All;
 
// Create new instance of the dal access object for this join.
MyDal.Data.JoinListItemDataAccess dalJoin = new MyDal.Data.JoinListItemDataAccess();
// Execute the query and return the rows
List<MyDal.Data.DataObject.JoinListItem> joinList = dalJoin.SelectBy_CategoryFk(1000, schema);
 
for (int i = 0; i < joinList.Count; i++)
{
    // for each row returned, access one of the fields in the join like this:
    string sCategoryName = joinList[i].CategoryTable.CategoryName;
}

This same object can be used to query for any type of join combination and the query is smart enough to optimize out any tables that are not included in the join fields specified.