Click here to Skip to main content
14,976,005 members
Articles / Programming Languages / C#
Article
Posted 21 Jul 2019

Tagged as

Stats

19.5K views
650 downloads
21 bookmarked

Generic DAL using ADO

Rate me:
Please Sign up or sign in to vote.
4.62/5 (19 votes)
22 Jul 2019CPOL9 min read
Avoid ORMs with this reasonably generic approach to ADO

Introduction

Let's get this out of the way right up front. I don't use ORMs. I abhor the idea of them, because they try to be (and need to be) a one-size fits all solution, and assume that you should never stray from whatever "one true path" they tend to advocate. Furthermore, I don't know who decided it was a bad idea to use stored procedures, but ORMs appear to adhere to this paradigm sh*t, and I much prefer stored procedures over in-code queries due to the much higher inherent security stored procedures afford. Be it known that I'm not the slightest bit interested in arguing the point, nor even rationally discussing it, so don't waste your time (or mine) filling the comments section with such drivel.

Furthermore, this code uses SQL Server (2014 if you're interested), and should be compatible with everything back to 2008R2, and everything up to whatever version is currently available. Once again, I'm not interested in the typical fanboy claptrap regarding MySql, YourSql, or WhateverSQL you might be using, and I won't be willing to help you modify the code to do so. You're all programmers, so work the problem, and don't bother me about it.

I understand that the girls out there that use .NET Core might also be able to use this code, but since I don't, I won't be providing code that supports it, or even suggestions about how to convert it to do so. If you're even semi-self-aware, you'll be able to easily identify the parts of the code that require modification to support your own swirling vortex of insanity.

Now that we have the pleasantries out of the way...

General

This article reflects a technique I implemented in an environment that supports two networks (classified and unclassified), each having five environments (on different virtual machines), supporting 20 applications, and accessing over 30 databases. In the interest of brevity, I removed everything not directly related to this code (network, environment, and applications) because a) it's proprietary, b) it just cluttered up the code, and c) it didn't apply to the desired aim of the article. If anyone is doing the math, this scheme supported the possibility of having 6000 possible connection strings (although realistically, each of our apps would only require 4-5 to be available).

This article demonstrates a reasonably generic approach to using ADO.NET and support of a high number of connection strings. Not only that, but all connection strings are built on-demand, and absolves the programmer of maintaining config files.

The Code

The following are nice-to-know facts regarding this code:

  • Used Visual Studio 2017 (should be able to use 2013 or higher without significant gymnastics on your part)
  • .NET 4.62 (should be able to use 4.5 or greater)
  • SQL Server 2014 (should be able to use 2008R2 or higher)
  • Windows 7 VM (yes, I'm a barbarian, but the code should work in whatever bug-ridden abomination you might be using)
  • Reflection is used to achieve the generic nature of the code. I realize reflection is generally frowned upon because it's slow, but reflection performance can't be a factor due to the nature of using generic types.

The sample solution architecture is implemented in such a way as to minimize the code/assembly footprint. Feel free to release your inner programmer to make it in your own image. I retained the comments in an effort to reduce the typed article narrative.

Connection Strings

As stated above, the ConnectionString class presented here was developed due to the extreme breadth of operating environments we have to support. In our code, we have a ConnectionStringList class that implements proprietary code to support our requirements, but it wasn't necessary to illustrate the actual reason we're here. However, it might be useful if you want to obfuscate your own connection strings, or ease your eventual quagmire of web.config files in a multi-environment situation such as ours.

First, we have the expected connection string components. These properties support the most commonly used attributes in a connection string, and are unremarkable.

C#
/// <summary>
/// Get/set the server instance name
/// </summary>
protected string Server { get; set; }

/// <summary>
/// Get/set the name of the default catalog
/// </summary>
protected string Database { get; set; }

/// <summary>
/// Get/set the user id (if needed by the server)
/// </summary>
protected string UserID { get; set; }

/// <summary>
/// Get/set the password (if needed by the server)
/// </summary>
protected string Password { get; set; }

Next, we have a simple sanity check property. It's pupose is to perform rudimentary validation to make sure a minimally valid connection string is possible:

C#
/// <summary>
/// Get a flag indicating whether the parameters for the connection string are valid
/// </summary>
private bool IsValid
{
    get
    {
        // eases debugging
        bool hasServer = !string.IsNullOrEmpty(this.Server);
        bool hasDB     = !string.IsNullOrEmpty(this.Database);
        bool hasUid    = !string.IsNullOrEmpty(this.UserID);
        bool hasPwd    = !string.IsNullOrEmpty(this.Password);

        bool isValid   = (hasServer && hasDB);
        isValid       &= ((!hasUid && !hasPwd) || (hasUid && hasPwd));

        return isValid;
    }
}

And then come the more interesting properties, which are used to actually build the connection string when requested by the calling method. The connection strings created by this code determine how it's constructed based on the previously discussed properties. As you can see, credentials and encrypted traffic (take note of the comment for the encrypted traffic property) are supported. To add more security, the returned connection string is base64 encoded. While this code always encodes the connection string, you can easily refactor this class to either not do that at all, or to react to an accompanying property that can dictate the desired functionality.

C#
/// <summary>
/// Get the credentials needed for the server.
/// </summary>
private string Credentials
{
    get
    {
        string value = "Integrated Security=";
        // If the userid OR password are not specified, we assume that we use the windows 
        // login
        if (string.IsNullOrEmpty(this.Password) && string.IsNullOrEmpty(this.UserID))
        {
            value = string.Format("{0}true;", value);
        }
        else
        {
            value = string.Format("{0}false; user id={1}; password={2};", 
                    value, this.UserID, this.Password);
        }
        return value;
    }
}

/// <summary>
/// Get the part of the connection string that indicates that we encrypt the traffic between 
/// the database and the app.
/// </summary>
private string WithEncryptedTraffic
{
    get
    {
        // TrustServerCertificate: When set to true, SSL / TLS is used to encrypt the channel
        // when bypassing walking the certificate chain to validate trust. If it is set to 
        // true and Encrypt is set to false, the channel is not encrypted. Beginning in .NET 
        // Framework 4.5, when TrustServerCertificate is false and Encrypt is true, the 
        // server name(or IP address) in a SQL Server SSL certificate must exactly match the 
        // server name(or IP address) specified in the connection string. Otherwise, the 
        // connection attempt will fail.
        string value = string.Empty;
        if (this.EncryptTraffic)
        {
            value = "Encrypt=true; TrustServerCertificate=true;";
        }
        return value;
    }
}

/// <summary>
/// Get the connection string. 
/// </summary>
public string ConnectionString
{
    get
    {
        string value = string.Empty;
        if (this.IsValid)
        {
            value = string.Format("data source={0}; initial catalog={1}; {2} {3}", 
                    this.Server, this.Database, this.Credentials, this.WithEncryptedTraffic);
        }
        else
        {
            throw new InvalidOperationException
            ("One or more required connection string parameters were not specified.");
        }

        // LATE-BREAKING CODE CORRECTION #1
        // base64 encode the connection string - this prevents the connection string from
        // floating around in memory in un-obfuscated form. A small security concern, but 
        // you know - security first!
        return value.Base64Encode();
    }
}

Finally, we come to the constructor and the obligatory ToString() override. There's really nothing to discuss here because it's just standard C# stuff.

C#
/// <summary>
/// Constructor
/// </summary>
/// <paramname="name">The connection string name 
/// (I've been using the name of the database)</param>
/// <paramname="server">The server instance</param>
/// <paramname="database">The initial database to connect to</param>
/// <paramname="uid">The user id credential</param>
/// <paramname="pwd">The password credential</param>
/// <paramname="encryptTraffic">Whether or not you want traffic encrypted 
/// between server and this app (default=false)</param>
public AppConnectionString(string name, string server, 
        string database, string uid, string pwd, bool encryptTraffic=false)
{
    this.Name           = name;
    this.Server         = server;
    this.Database       = database;
    this.UserID         = uid;
    this.Password       = pwd;
    this.EncryptTraffic = encryptTraffic;
}

/// <summary>
/// Override that returns the (decorated) connection string
/// </summary>
/// <returns></returns>
public override string ToString()
{
    return this.ConnectionString;
}

Entities and Attributes

I decided to use the word "entity" to ease the calm for everyone that's been seduced by the promises of their favorite ORM.

EntityBase

The EntityBase class provides code that returns an inherited object's properties as a SqlParameter array. This means all of your entity-specific code can stay in the entity object without cluttering up the business logic. To add a modicum of control over what properties are returned a SQL parameters, you can decorate those properties with the CanDbInsert attribute. This means you can mix properties into your class that don't necessarily need to be (or qualify for) insertion into the database.

C#
/// <summary>
/// Represents the base class for your entities. 
/// This is handy if you need to do anything like 
/// change notification, or other would-be common functionality.
/// </summary>
public partial class EntityBase 
{
    private bool onlyDecorated = true;

    /// <summary>
    /// Get/set flag indicating whether the GetEntityProperties 
    /// restricts array to only properties 
    /// that are appropriately decorated.
    /// </summary>
    public bool OnlyDecorated 
    {
        get { return this.onlyDecorated; }
        set { this.onlyDecorated = value; }
    }

The following property simplifies the generation of an appropriate array of SqlParameter objects. It simply calls the GetEntityProperties method to retrieve all decorated properties to be converted to SQLParameter objects.

C#
/// <summary>
/// Get this object's properties as a SqlParameter array.
/// </summary>
public virtual SqlParameter[] AsSqlParams
{
    get
    {
        PropertyInfo[]  properties = this.GetEntityProperties();
        SqlParameter[] parameters = this.MakeSqlParameters(properties);
        return parameters;
    }
}

The following methods are used by the AsSqlParameters property, and use reflection to retrieve - and optionally filter - the properties that will be returned as SqlParameter objects.

C#
    /// <summary>
    /// Retrieve all properties for an insert command.
    /// </summary>
    /// <returns>
    /// If this.OnlyDecorated is true, returns properties decorated with CanDbInsertAttribute. 
    /// Otherwise, all properties are returned.
    /// </returns>
    public virtual PropertyInfo[] GetEntityProperties()
    {
        return GetEntityProperties(this.OnlyDecorated);
    }

    /// <summary>
    /// Gets the entity's properties, ostensibly for updates, where we might need non-insertable 
    /// properties to determine what to update.
    /// </summary>
    /// <paramname="onlyDecorated"></param>
    /// <returns>
    /// If onlyDecorated is true, returns properties decorated with CanDbInsertAttribute. 
    /// Otherwise, all properties are returned.
    /// </returns>
    public virtual PropertyInfo[] GetEntityProperties(bool onlyDecorated)
    {
        PropertyInfo[] properties = (onlyDecorated)
                                    ? this.GetType().GetProperties().Where
                                    (x => x.IsDefined(typeof(CanDbInsertAttribute))).ToArray()
                                    : this.GetType().GetProperties();
        return properties;
    }

Finally, we have a method that creates the SqlParameter array from the provided properties.

C#
    protected virtual SqlParameter[] MakeSqlParameters(PropertyInfo[] properties)
    {
        List<sqlparameter> parameters = new List<sqlparameter>();

        foreach(PropertyInfo property in properties)
        {
            parameters.Add(new SqlParameter(string.Format("@{0}",property.Name), 
                           property.GetValue(this)));
        }

        return ((parameters.Count > 0) ? parameters.ToArray() : null);
    }
}

The CanDbInsertAttribute Object

This object represents a nothing-fancy decorator attribute used by the EntityBase class to weed out non-insertable properties in your EntityBase-derived entities. If you need more attributes, you can use this one as an example.

C#
/// <summary>
/// Allows you to mark a property as insertable (ostensibly for building a bulk insert
/// datatable), but can be used elsewhere.
/// </summary>
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class CanDbInsertAttribute : Attribute
{
    public string Name { get; set; }
    public string Argument { get; set; }
}

The Sample Entity

The sample entity used in the sample application exercises the EntityBase class, and the CanDbInsertAttribute attribute.

C#
namespace BLLSample.Entities
{
    public class EntityUserInfo : EntityBase
    {
        //can't insert this because the db generates it automagically
        public int    UserID      { get; set; }

        [CanDbInsert]
        public string UserName    { get; set; }
        [CanDbInsert]
        public string Description { get; set; }
        [CanDbInsert]
        public string Gender      { get; set; }

        public SqlParameter[] AsSqlParametersForUpdate
        {
            get
            {
                PropertyInfo[] properties = this.GetEntityProperties(false);
                SqlParameter[] parameters = this.MakeSqlParameters(properties);
                return parameters;
            }
        }
    }
}

The DAL (Data Access Layer)

Finally, the reason we're all actually here. The DAL object is what makes this whole thing work in a generic way. The need to manually identify properties for inserting/updating into the database has already been adequately mitigated, but what about retrieving the data? The DAL object uses reflection rather heavily to achieve this functionality. However, it does not absolve us of creating the requisite ADO code.

First, we establish some necessary properties, and initialize them in the constructor. Take note of the FailOnMismatch property. This property allows you to tell the DAL whether or not an exception should be thrown in the event that the returned dataset has more/fewer columns that the receiving entity has matching properties. The default value for this property is false.

C#
/// <summary>
/// Get/set flag indicating whether the List<T> ExecuteXXX<T>() methods should 
/// throw an exception if the DataTable retrieved by the query does not match the model 
/// being created (it compares the number of datatable columns with the number of assigned 
/// values in the model). The default value is false.
/// </summary>
public bool   FailOnMismatch          { get; set; }

/// <summary>
/// Get/set value indicating the timeout value (in seconds)
/// </summary>
public int    TimeoutSecs             { get; set; }

/// <summary>
/// Get/(protected)set the connection string.
/// </summary>
public string ConnectionString        { get; protected set; }

/// <summary>
/// Get/set a flag indicating whether the return value parameter is added to the SQL 
/// parameter list if it's missing. This only applies to the SetData method 
/// (insert/update/delete functionality). In order for this to work, you MUST return 
/// @@ROWCOUNT from your stored proc. For text queries you don't have to do this (I 
/// don't think).
/// </summary>
public bool   AddReturnParamIfMissing { get; set; }

/// <summary>
/// Get/set the bulk insert batch size
/// </summary>
public int BulkInsertBatchSize        { get; set; }

/// <summary>
/// Get/set the number of seconds before the bulk copy times out
/// </summary>
public int BulkCopyTimeout            { get; set; }

/// <summary>
/// Create instance of DBObject, and set default values for properties.
/// </summary>
/// <paramname="connStr"></param>
public DAL(string connStr)
{
    if (string.IsNullOrEmpty(connStr))
    {
        throw new ArgumentNullException("connection string");
    }
    this.ConnectionString        = connStr;
    // five minutes should be enough, right?
    this.TimeoutSecs             = 300;
    this.FailOnMismatch          = false;
    this.AddReturnParamIfMissing = true;
    this.BulkInsertBatchSize     = 100;
    this.BulkCopyTimeout         = 600;
}

Next, we establish two methods for getting and setting data in the database. Notice that these two methods request the afore-described connection strings, and base64 decode them for use. If you choose NOT to base64-encode your connection strings, this method will still return the correct (un-decoded) string value.

C#
/// <summary>
/// Calls SqlCommand.ExecuteDataReader() to retrieve a dataset from the database.
/// </summary>
/// <paramname="cmdText">The stored proc or query to execute</param>
/// <paramname="parameters">The parameters to use in the storedproc/query</param>
/// <returns></returns>
protected DataTable GetData(string cmdText, SqlParameter[] parameters=null, 
                            CommandType cmdType = CommandType.StoredProcedure)
{
    // by defining these variables OUTSIDE the using statements, we can evaluate them in 
    // the debugger even when the using's go out of scope.
    SqlConnection conn   = null;
    SqlCommand    cmd    = null;
    SqlDataReader reader = null;
    DataTable     data   = null;

    // create the connection
    using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
    {
        // open it
        conn.Open();
        // create the SqlCommand object
        using (cmd = new SqlCommand(cmdText, conn) 
              { CommandTimeout = this.TimeoutSecs, CommandType = cmdType } )
        {
            // give the SqlCommand object the parameters required for the stored proc/query
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
            }
            //create the SqlDataReader
            using (reader = cmd.ExecuteReader())
            {
                // move the data to a DataTable
                data = new DataTable();
                data.Load(reader);
            }
        }
    }
    // return the DataTable object to the calling method
    return data;
}

/// <summary>
/// Calls SqlCommand.ExecuteNonQuery to save data to the database.
/// </summary>
/// <paramname="connStr"></param>
/// <paramname="cmdText"></param>
/// <paramname="parameters"></param>
/// <returns></returns>
protected int SetData(string cmdText, SqlParameter[] parameters, 
                      CommandType cmdType = CommandType.StoredProcedure)
{
    int result = 0;
    SqlConnection conn   = null;
    SqlCommand    cmd    =  null;
    using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
    {
        conn.Open();
        using (cmd = new SqlCommand(cmdText, conn) 
              { CommandTimeout = this.TimeoutSecs, CommandType = cmdType } )
        {
            SqlParameter rowsAffected = null;
            if (parameters != null)
            {
                cmd.Parameters.AddRange(parameters);
                // if this is a stored proc and we want to add a return param
                if (cmdType == CommandType.StoredProcedure && this.AddReturnParamIfMissing)
                {
                    // see if we already have a return parameter
                    rowsAffected = parameters.FirstOrDefault
                                   (x=>x.Direction == ParameterDirection.ReturnValue);
                    // if we don't, add one.
                    if (rowsAffected == null)
                    {
                        rowsAffected = cmd.Parameters.Add(new SqlParameter
                                       ("@rowsAffected", SqlDbType.Int) 
                                       { Direction = ParameterDirection.ReturnValue } );
                    }
                }
            }
            result = cmd.ExecuteNonQuery();
            result = (rowsAffected != null) ? (int)rowsAffected.Value : result;
        }
    }
    return result;
}

Next, we see the helper methods that actually use the magic of reflection to determine how to move data from the provided DataTable into the specified entity object. In order for this to work, the property names MUST be identical to the column names returned by the query (and yes, the comparison is case-sensitive).

C#
/// <summary>
/// Converts a value from its database value to something we can use (we need this because 
/// we're using reflection to populate our entities)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <paramname="obj"></param>
/// <paramname="defaultValue"></param>
/// <returns></returns>
protected static T ConvertFromDBValue<T>(object obj, T defaultValue)
{
    T result = (obj == null || obj == DBNull.Value) ? default(T) : (T)obj;
    return result;
}

/// <summary>
/// Creates the list of entities from the specified DataTable object. We do this because we 
/// have two methods that both need to do the same thing.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <paramname="data"></param>
/// <returns></returns>
protected List<T> MakeEntityFromDataTable<T>(DataTable data)
{
    Type objType = typeof(T);
    List<T> collection = new List<T>();
    // if we got back data
    if (data != null && data.Rows.Count > 0)
    {
        // we're going to count how many properties in the model were assigned from the 
        // datatable.
        int matched = 0;

        foreach(DataRow row in data.Rows)
        {
            // create an instance of our object
            T item = (T)Activator.CreateInstance(objType);

            // get our object type's properties
            PropertyInfo[] properties = objType.GetProperties();

            // set the object's properties as they are found.
            foreach (PropertyInfo property in properties)
            {
                if (data.Columns.Contains(property.Name))
                {
                    Type pType = property.PropertyType;
                    var defaultValue = pType.GetDefaultValue();
                    var value = row[property.Name];
                    value = ConvertFromDBValue(value, defaultValue );
                    property.SetValue(item, value);
                    matched++;
                }
            }
            if (matched != data.Columns.Count && this.FailOnMismatch)
            {
                throw new Exception("Data retrieved does not match specified model.");
            }
            collection.Add(item);
        }
    }
    return collection;
}

Next, we see the actual methods that are called by the business layer object. There are two pairs of overloaded methods. Each pair contains what amounts to a "getter" and a "setter" overload. The code from which this article was taken throws an exception when the developer tries to call ExecuteQuery because all of our database interaction is performed via stored procedures (it's just more reliable that way).

C#
/// <summary>
/// Executes the named stored proc (using ExecuteReader) that gets data from the database. 
/// It uses reflection to set property values in the specified type. If nothing was returned 
/// from the stored proc, the returned list will be empty.
/// </summary>
/// <typeparam name="T">The type of the list item</typeparam>
/// <paramname="storedProc"></param>
/// <paramname="parameters"></param>
/// <returns>A list of the specified type.</returns>
/// <remarks>Useage: List<MyObject> list = this.ExecuteStoredProc<MyObject>(...)</remarks>
public List<T> ExecuteStoredProc<T>(string storedProc, params SqlParameter[] parameters)
{
    if (string.IsNullOrEmpty(storedProc))
    {
        throw new ArgumentNullException("stored procedure");
    }
    // get the data from the database
    DataTable data = this.GetData(storedProc, parameters, CommandType.StoredProcedure);
    List<T> collection = this.MakeEntityFromDataTable<T>(data);
    return collection;
}

/// <summary>
/// Executes the named stored proc (using ExecuteNonQuery) that stores data in the database. 
/// </summary>
/// <paramname="storedProc"></param>
/// <paramname="parameters"></param>
/// <returns>The number of records affected</returns>
public int ExecuteStoredProc(string storedProc, params SqlParameter[] parameters)
{
    if (string.IsNullOrEmpty(storedProc))
    {
        throw new ArgumentNullException("stored procedure");
    }

    // Save the data to the database. If you don't explicitly return @@ROWCOUNT from your 
    // stored proc, the return value will always be -1, regardless of how many rows are 
    // actually affected.
    int result = this.SetData(storedProc, parameters, CommandType.StoredProcedure);

    return result;
}

/// <summary>
/// Executes the specifid query (using ExecuteReader) that gets data from the database. 
/// It uses reflection to set property values in the specified type. If nothing was returned 
/// from the stored proc, the returned list will be empty.
/// </summary>
/// <typeparam name="T">The type of the list item</typeparam>
/// <paramname="storedProc"></param>
/// <paramname="parameters"></param>
/// <returns>A list of the specified type.</returns>
/// <remarks>Useage: List<MyObject> list = this.ExecuteStoredProc<MyObject>(...)</remarks>
public List<T> ExecuteQuery<T>(string query, params SqlParameter[] parameters)
{
    if (string.IsNullOrEmpty(query))
    {
        throw new ArgumentNullException("query");
    }
    DataTable data = this.GetData(query, parameters, CommandType.Text);
    List<T> collection = this.MakeEntityFromDataTable<T>(data);
    return collection;
}

/// <summary>
/// Executes the specified query text (using ExecuteNonQuery) that stores data in the 
/// database. 
/// </summary>
/// <paramname="storedProc"></param>
/// <paramname="parameters"></param>
/// <returns>The number of records affected (if you didn't use SET NOCOUNT ON in 
/// your batch)</returns>
public int ExecuteQuery(string query, params SqlParameter[] parameters)
{
    if (string.IsNullOrEmpty(query))
    {
        throw new ArgumentNullException("query");
    }

    // Save the data to the database. If you use SET NOCOUNT ON in your query, the return 
    // value will always be -1, regardless of how many rows are actually affected.
    int result = this.SetData(query, parameters, CommandType.Text);
    return result;
}

And finally, we have a set of methods that allow bulk inserting into the database via the ADO SqlBulkInsert object.

C#
/// <summary>
/// Performs a simply bulk insert into a table in the database. The schema MUST be part of 
/// the table name.
/// </summary>
/// <paramname="dataTable"></param>
/// <returns></returns>
public int DoBulkInsert(DataTable dataTable)
{
    // If you have an auto-incrementing identity column, make sure you decorate the column 
    // with DbCanInsert attribute. If you don't, it will try to put the first available 
    // property into that db table column, and will throw an exception if the types don't 
    // match.
    int recordsAffected = 0;
    SqlConnection conn = null;
    SqlBulkCopy bulk = null;
    using (conn = new SqlConnection(this.ConnectionString))
    {
        conn.Open();
        using (bulk = new SqlBulkCopy(conn)
        {
            BatchSize             = this.BulkInsertBatchSize
            ,BulkCopyTimeout      = this.BulkCopyTimeout
            ,DestinationTableName = dataTable.TableName
        })
        {
            bulk.WriteToServer(dataTable);
        }
    }
    return recordsAffected;
}

/// <summary>
/// Performs a simple bulk insert into a table in the database. The schema MUST be part of 
/// the table name if the target schema isn't "dbo".
/// </summary>
/// <paramname="dataTable"></param>
/// <returns></returns>
public int DoBulkInsert<T>(IEnumerable<T> data, 
                           string tableName, bool byDBInsertAttribute=false)
{
    int result = 0;
    DataTable dataTable = null;

    if (data.Count() > 0)
    {
        using (dataTable = new DataTable(){TableName = tableName })
        {
            Type type = typeof(T);

            MethodInfo method = type.GetMethod("GetEntityProperties");

            // get the properties regardless of whether or not the object is using EntityBase
            PropertyInfo[] properties = (method == null) ? 
                           type.GetProperties().Where(prop => 
                           Attribute.IsDefined(prop, typeof(CanDbInsertAttribute))).ToArray()
                           : (PropertyInfo[])method.Invoke(this, null);

            foreach (PropertyInfo property in properties)
            {
                dataTable.Columns.Add(new DataColumn(property.Name, property.PropertyType));
            }
            foreach (T entity in data)
            {
                DataRow row = dataTable.NewRow();
                foreach (PropertyInfo property in properties)
                {
                    row[property.Name] = property.GetValue(entity);
                }
                dataTable.Rows.Add(row);
            }
        }
        result = this.DoBulkInsert(dataTable);
    }
    return result;
}

The BLL (Business Logic Layer)

Because of all the work done behind the scenes, implementing a BLL is laughably simple, and the methods implemented can be as simple or as complex as necessary. Essentially, the entities and the business layer are the only things you really have to code yourself. Your BLL object must inherit the DAL object, and at that point, Bob's your uncle. As you can see, your BLL methods are usually very simple (part of my belief that the further out you get from the core code, the simpler it should be on the programmer.

C#
namespace BLLSample
{
    public partial class BLL : DAL
    {
        public BLL(string connectionString) : base (connectionString)
        {
        }

        /// <summary>
        /// Get all users
        /// </summary>
        /// <returns>All users in the table</returns>
        public List<EntityUserInfo> GetUsers()
        {
            List<EntityUserInfo> users = this.ExecuteStoredProc<EntityUserInfo>
                                         ("dbo.spGetUsers", null);
            return users;
        }

        /// <summary>
        /// Get the specified user
        /// </summary>
        /// <param name="id"></param>
        /// <returns>A single EntityUserInfo object</returns>
        public EntityUserInfo GetUserInfo(int id)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@userID", id),
            };
            EntityUserInfo user = this.ExecuteStoredProc<EntityUserInfo>
                                  ("dbo.spGetUser", parameters).FirstOrDefault();
            return user;
        }

        /// <summary>
        /// Save the specified user to the database
        /// </summary>
        /// <param name="model"></param>
        /// <returns>Number of affected records</returns>
        public int SaveUser(EntityUserInfo model)
        {
            int result = this.ExecuteStoredProc("dbo.spAddUser", model.AsSqlParams);
            return result;
        }
    }
}

Usage

Before you compile and run the app, open the CreateDALSampleDB.sql file (in the DALSample project), and follow the instructions at the top of the file.

C#
using DataModels.DAL;
using BLLSample;
using BLLSample.Entities;

namespace DALSample
{
	class Program
	{
		static void Main(string[] args)
		{
            // Create a connectionstring object. The parts of the connection string 
            // are stored in separate properties, and the connection string itself 
            // is assembled and base64 encoded 
            // on demand. This makes the connectionstring itself more secure 
            // while the program is in memory.
            AppConnectionString connectionString = 
              new AppConnectionString("Sample", "localhost", "DALSample", "", "", false);
            BLL bll = new BLL(connectionString.ConnectionString);
            List<EntityUserInfo> data = bll.GetUsers();

		}
	}
}

Late Breaking Code Correction

1) I noticed that the ConnectionString property in the AppConnectionString class did not call the Base64Encode() method before returning the constructed connection string. I fixed this in the article code snippet, but NOT in the associated source code ZIP file (my work environment prevents me from downloading anything, so I can't fix it from work). The absence of the Base64Encode call won't hurt/break anything, but it may be important to you so, act on this in any way you see fit.

In Closing

I make no guarantees that this code follows anybody's idea of "best practice", and I will vehemently dissuade everyone from using an ORM of any kind. I also cannot guarantee compatibility with any past, current, or future version of any of the tools cited. In other words, if the code is somehow borked beyond all recognition, be a programmer, and fix it yourself. If you find problems, and let me know, I'll try to fix the article ASAP.

Spelling mistakes may be present in copious quantities - I typed this article on my phone - in the bathroom.

No liquid nitrogen was used or harmed, intentionally or otherwise, during the creation of this article.

Bacon - because 'Murrica.

History

  • 2019.07.23 - Added some narrative, and fixed some code in one of the connection string code blocks (refer to the section labelled Late Breaking Code Correction.
  • 2019.07.22 - Fixed PRE blocks
  • 2019.07.21 - Initial publication

License

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

Share

About the Author

#realJSOP
Software Developer (Senior) Paddedwall Software
United States United States
I've been paid as a programmer since 1982 with experience in Pascal, and C++ (both self-taught), and began writing Windows programs in 1991 using Visual C++ and MFC. In the 2nd half of 2007, I started writing C# Windows Forms and ASP.Net applications, and have since done WPF, Silverlight, WCF, web services, and Windows services.

My weakest point is that my moments of clarity are too brief to hold a meaningful conversation that requires more than 30 seconds to complete. Thankfully, grunts of agreement are all that is required to conduct most discussions without committing to any particular belief system.

Comments and Discussions

 
PraiseInsert and Update on Entity Pin
Nick Williams14-Jan-20 2:11
MemberNick Williams14-Jan-20 2:11 
GeneralRe: Insert and Update on Entity Pin
#realJSOP29-Mar-20 8:50
mva#realJSOP29-Mar-20 8:50 
GeneralRe: Insert and Update on Entity Pin
Nick Williams2-Apr-20 11:16
MemberNick Williams2-Apr-20 11:16 
Sorry haven't added this much code into a message block before. Is there an easy way to format it. I just copied and pasted from vs2019?? That was how it came out??
GeneralRe: Insert and Update on Entity Pin
#realJSOP2-Apr-20 23:14
mva#realJSOP2-Apr-20 23:14 
GeneralMy vote of 5 Pin
Member 1142401730-Oct-19 0:10
MemberMember 1142401730-Oct-19 0:10 
QuestionFunctionality Pin
bcostell0200017-Aug-19 4:43
Memberbcostell0200017-Aug-19 4:43 
AnswerRe: Functionality Pin
#realJSOP17-Aug-19 5:40
mva#realJSOP17-Aug-19 5:40 
GeneralRe: Functionality Pin
bcostell0200017-Aug-19 6:33
Memberbcostell0200017-Aug-19 6:33 
QuestionYou sir are my spirit animal! Pin
Member 1002774431-Jul-19 9:29
MemberMember 1002774431-Jul-19 9:29 
AnswerRe: You sir are my spirit animal! Pin
#realJSOP12-Aug-19 0:59
mva#realJSOP12-Aug-19 0:59 
QuestionDAL dead? Pin
cplas30-Jul-19 7:38
Membercplas30-Jul-19 7:38 
AnswerRe: DAL dead? Pin
#realJSOP7-Aug-19 7:34
mva#realJSOP7-Aug-19 7:34 
AnswerRe: DAL dead? Pin
Eddy Vluggen27-Nov-19 1:54
professionalEddy Vluggen27-Nov-19 1:54 
Suggestionwaste of time. Pin
vahyes26-Jul-19 8:04
Membervahyes26-Jul-19 8:04 
GeneralRe: waste of time. Pin
#realJSOP26-Jul-19 13:38
mva#realJSOP26-Jul-19 13:38 
GeneralRe: waste of time. Pin
Frans Vander Meiren26-Jul-19 22:54
MemberFrans Vander Meiren26-Jul-19 22:54 
Questionmy vote of 5 Pin
Frans Vander Meiren25-Jul-19 6:31
MemberFrans Vander Meiren25-Jul-19 6:31 
GeneralMy vote of 4 Pin
Klaus Luedenscheidt24-Jul-19 18:17
MemberKlaus Luedenscheidt24-Jul-19 18:17 
GeneralRe: My vote of 4 Pin
#realJSOP26-Jul-19 0:56
mva#realJSOP26-Jul-19 0:56 
QuestionSimple coding style question Pin
r_alf22-Jul-19 6:56
Memberr_alf22-Jul-19 6:56 
AnswerRe: Simple coding style question Pin
#realJSOP22-Jul-19 8:15
mva#realJSOP22-Jul-19 8:15 
QuestionCode Snippets Fixed Pin
#realJSOP22-Jul-19 6:42
mva#realJSOP22-Jul-19 6:42 
QuestionYou missed a bit Pin
Chris Maunder22-Jul-19 3:33
cofounderChris Maunder22-Jul-19 3:33 
AnswerRe: You missed a bit Pin
#realJSOP22-Jul-19 4:46
mva#realJSOP22-Jul-19 4:46 
GeneralRe: You missed a bit Pin
Chris Maunder22-Jul-19 6:30
cofounderChris Maunder22-Jul-19 6:30 

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

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