Click here to Skip to main content
Click here to Skip to main content
Go to top

INCLUDE in classic ADO.NET

, 5 Jul 2012
Rate this:
Please Sign up or sign in to vote.
Implementing and using an INCLUDE method in classic ADO.NET.

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 to 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 following rules:

  • 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();
    }
}</string></string>

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:

DB Diagram

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 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Erick Cerra
Software Developer (Senior)
United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmvpKanasz Robert26-Sep-12 7:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140921.1 | Last Updated 5 Jul 2012
Article Copyright 2012 by Erick Cerra
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid