INCLUDE in classic ADO.NET

• Download SQL scripts - 4.9 KB
• Download source code - 242.1 KB

Introduction

The INCLUDE method in EF is a very good tool present in the ObjectSet and ObjectQuery classes. It helps to select all the related Entities that you need to use and it is performed in only one call having a great performance.

If for some reason you cannot use EF in your project, you are not able to use the INCLUDE method because in classic ADO.NET (no EF), it doesn’t exist.

In classic ADO.NET, most of the time, you need to get data for an entity and its related entities, you need to call the database more than once to select all the information needed, and that is very time consuming.

For that reason, I created the INCLUDE approach for classic ADO.NET to increase the performance when getting data from related entities.

Using the Code

To use this approach, you only need to follow the rules given below:

• Each table in the DB has its Entity class.
• Be consistent using the same name for each field in the table and in its Entity class.
• Create appropriate Stored Procedure to implement the INCLUDE method.
• Inherit from the EntityBase class to create your Entities.
• Use the SqlHelper class to interact with your DB.

EntityBase Class

This class has only one method to obtain the fields marked as OUTPUT in the child classes.

public abstract class EntityBase
{
    protected EntityBase()
    {
    }

    public string[] GetOutputFieldNames()
    {
        List<string> result = new List<string>();

        PropertyInfo[] properties = this.GetType().GetProperties();
        foreach (PropertyInfo property in properties)
        {
            FieldDirectionAttribute[] fieldDirectionsAtt = 
              (FieldDirectionAttribute[])property.GetCustomAttributes(
               typeof(FieldDirectionAttribute), true);
            if (fieldDirectionsAtt != null && fieldDirectionsAtt.Length > 0 &&
                (fieldDirectionsAtt[0].Direction == ParameterDirection.InputOutput ||
                fieldDirectionsAtt[0].Direction == ParameterDirection.Output))
                result.Add(property.Name);
        }

        return result.ToArray();
    }
}

SqlHelper Class

The ExecuteQueryStoredProcedure method is a basic method to fill a dataset object from a DB using ADO.NET and Stored Procedures.

public sealed class SqlHelper
{
    public static DataSet ExecuteQueryStoredProcedure(SqlConnection sqlConnection, 
           string storedProcedureName, List<SqlParameter> parameters)
    {
        DataSet result = new DataSet();

        using (SqlCommand sqlCommand = new SqlCommand())
        {
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = storedProcedureName;

            if (parameters != null && parameters.Count > 0)
                sqlCommand.Parameters.AddRange(parameters.ToArray());

            if (sqlCommand.Connection.State != ConnectionState.Open)
                sqlCommand.Connection.Open();

            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            sqlDataAdapter.Fill(result);
        }

        return result;
    }

This ExecuteQueryStoredProcedure method is in charge of filling a dataset using the INCLUDE query based on the type of the Entity to populate. CreateDynamicQuery is the method to create the INCLUDE query based on the type of the Entity. After creating the INCLUDE query, this method calls the previous method to fill out the dataset using the INCLUDE script and finally puts the primary keys to the tables coming in the dataset.

private static DataSet ExecuteQueryStoredProcedure<T>(SqlConnection sqlConnection, 
        string storedProcedureName, List<SqlParameter> parameters, 
        params string[] includePaths) where T : EntityBase
{
    DataSet result = null;

    List<string> includePathList;
    string includeQuery = CreateDynamicQuery<T>(out includePathList, includePaths);

    if (parameters == null)
        parameters = new List<SqlParameter>();

    parameters.Add(new SqlParameter("@Include", includeQuery));

    result = ExecuteQueryStoredProcedure(sqlConnection, storedProcedureName, parameters);

    if (result != null && result.Tables.Count > 0)
    {
        Type mainType = typeof(T);
        result.Tables[0].TableName = mainType.Name;
        SetPrimaryKey(result.Tables[0], mainType);

        if (result.Tables.Count == (includePathList.Count + 1))
        {
            for (int i = includePathList.Count; i > 0; i--)
            {
                result.Tables[i].TableName = includePathList[i - 1];
                Type entityType = GetType(mainType, includePathList[i - 1]);
                
                SetPrimaryKey(result.Tables[i], entityType);
            }
        }
    }

    return result;
}

The GetEntity method is the core of this class; it obtains an entity from a DataRow (getting the related entities from the dataset).

private static EntityBase GetEntity(DataRow dataRow, Type type, 
DataSet dataSet, EntityBase dontIncludeEntity)
{
    EntityBase result = null;

    if (dataRow != null && dataRow.Table.Columns.Count > 0)
    {
        result = (EntityBase)Activator.CreateInstance(type);
        DataColumnCollection dataColumns = dataRow.Table.Columns;

        foreach (PropertyInfo property in type.GetProperties())
        {
            if (dataColumns.Contains(property.Name))
            {   //If the property is a column in the table.
                object propertyValue = null;

                if (dataRow != null && dataRow[property.Name] != null)
                {
                    propertyValue = dataRow[property.Name];

                    if (propertyValue != null)
                    {
                        if (propertyValue is DBNull)
                            propertyValue = null;
                        else if (property.PropertyType.IsEnum)
                        {
                            //if (Enum.IsDefined(property.PropertyType, propertyValue)
                            propertyValue = Enum.Parse
                                            (property.PropertyType, propertyValue.ToString());
                        }
                    }
                }

                property.SetValue(result, propertyValue, null);
            }
            else if (dataSet != null && dataSet.Tables.Count > 0 && 
                                  dataSet.Tables.Contains(property.Name))
            {
                //If the property is not a column in the table (It's an Entity property)
                //Fill Entity property out using data from a related table (include).

                Type entityType = property.PropertyType;
                Type dontIncludeType = 
                   (dontIncludeEntity != null) ? dontIncludeEntity.GetType() : null;

                DataTable includedDataTable = dataSet.Tables[property.Name];

                if (!entityType.IsArray && (dontIncludeType == null ||
                    dontIncludeType != null && dontIncludeType != entityType))
                {
                    object[] foreignKeyValues = GetForeignKeyValues(dataRow, property);
                    DataRow includedRow = includedDataTable.Rows.Find(foreignKeyValues);

                    object entityValue = GetEntity(includedRow, entityType, dataSet, result);
                    result.GetType().GetProperty(property.Name).SetValue(result, entityValue, null);
                }
                else if (!entityType.IsArray)
                {
                    result.GetType().GetProperty(property.Name).SetValue
                                        (result, dontIncludeEntity, null);
                }
            }
        }
    }

    return result;
}

Entity Class

This is an example of an Entity (Customer entity).

[EntityAttributeBase("Customers", "Id")]
public class Customer : EntityBase
{
    [EnumStringType]
    public enum CustomerSize
    {
        None,
        Small,
        Medium,
        Large
    }

    [FieldDirection(ParameterDirection.InputOutput)]
    public long Id { get; set; }

    public string Name { get; set; }

    public string AddressLine1 { get; set; }

    public string AddressLine2 { get; set; }

    public string City { get; set; }
    
    public string State { get; set; }

    public string ZipCode { get; set; }

    public string CountryCode { get; set; }

    public string ContactName { get; set; }

    public string ContactEmail { get; set; }

    public string ContactPhone { get; set; }

    public CustomerSize Size { get; set; }

    [NavigationProperty]
    public Merchant[] Merchants { get; set; }

    [FieldDirection(ParameterDirection.InputOutput)]
    public Byte[] RowVersion { get; set; }
}

To use this code, you only need to create your entities inheriting from the EntityBase class and use the SqlHelper class.

Example

Here is a concrete example to better understand this approach.

This is a hypothetic case, where a company (a payment gateway company) has its database structure as shown in the following picture:

In this case, the gateway company has Customers, the Customers have Merchants, and those Merchants have Terminals.

Once you are getting a Terminal, you are able to get its Merchant and Customer using the INCLUDE functionality ("Merchant.Customer").

Here is an example of how you can use this approach from a data access class:

public Terminal GetTerminalByCode(string code)
{
    List<SqlParameter> parameters = new List<SqlParameter>();
        parameters.Add(new SqlParameter("@Code", code));

    return SqlHelper.GetEntity<Terminal>(_sqlConnection, 
           "Terminals_GetByCode", parameters, "Merchant.Customer");
}

Calling the GetEntity method of the SqlHelper class, you are able to get the desired entity and its dependencies.

This method needs a connection object, the name of the Stored Procedure in charge of getting the entity from the DB, the parameters for the Stored Procedure, and the include path needed.

And here is the structure of the Stored Procedure called in the previous example:

CREATE PROCEDURE [dbo].[Terminals_GetByCode] 
        @Code nvarchar(10),
        @Include nvarchar(MAX) = null
AS
BEGIN
    SET NOCOUNT ON;

    SELECT t.* 
    INTO #tmpTable
    FROM Terminals t
    WHERE t.Code = @Code;
    
    SELECT tmp.* FROM #tmpTable tmp;    
    
    IF (@Include is not null) BEGIN
        exec (@Include);
    END
    
    drop table #tmpTable;

END

One more thing: #tmpTable is needed in the Stored Procedure because it is used by the @Include variable.