Click here to Skip to main content
15,034,292 members
Articles / Programming Languages / C#
Article
Posted 5 Apr 2020

Stats

12.3K views
451 downloads
21 bookmarked

Generic DAL for ADO.Net - A Revisit

Rate me:
Please Sign up or sign in to vote.
4.88/5 (9 votes)
10 Apr 2020CPOL29 min read
A rewrite of a previous article with more liquid nitrogen and bacon
After due consideration (and some input from other developers at work, I found the old article somewhat lacking, This rewrite improves the old code, and explores the magic of the singleton pattern.

Preface

This article is a fairly significant rewrite of an code/article I posted last year, and reflects modifications to that code, as well as new ideas based on the way other programmers at work have used it. The code is significantly changed, so I thought it warranted a whole new article instead of just pointing back to my old code. Beyond that, this code is used in an app for which I'll be writing an article in the future, and I want to simply provide a link to this article instead of having to describe everything there (or make what will be a long article even longer. Those of you with the attention span of a two-slice toaster will rejoice in my sensitivity to your needs. See? I'm a sensitive guy. No - really.

You may notice that I have modified some of the CodeProject styles regarding headers, and <pre> block background colors (mostly to delineate between code blocks and text output). Note to CP staff/editors (please don't muck around with these styles unless absolutely necessary).

There are no screen shots in this article, because there is no GUI associated with the class library or the test application. Instead, I copy/pasted the console output into <pre> blocks because it's easier to read and doesn't run afoul of the CodeProject article image width restrictions.

Finally, fair warning - this is an admittedly long read. That's the way I roll, and in my oh-so-outlaw opinion, it's the way pretty much ALL articles should be. That's why they're called "articles", instead of "tips". New guys? Watch and learn.

Introduction

Why not just use an ORM? 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, and generally provide poor support for their use. I much prefer stored procedures over in-code queries due a) to the much higher inherent security stored procedures afford, and b) find it more convenient to modify the back-end instead of having to deploy a new version of an app. 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.

What databases does this code support? This code supports SQL Server (I use the 2014 Developer Edition, if you're interested), and should be compatible with all versions of SQL Server, from 2008R2 to the present. Other databases don't interest me, and besides, it would take a lot longer to write this code/article if I were to create alternatives for every "better idea" that's out there. It should be a simple matter for you - a programmer - to figure out how to make this code your own. Once again, I'm not interested in the typical fanboy claptrap regarding Oracle, MySql, YourSql, or WhateverSQL you might be using, and I won't be receptive or 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.

Does this code work in .Net Core? I understand that the girls out there that use .NET Core might also be able to use this code, but since I don't (yet), I won't be providing code that supports it, or even suggestions about how to convert it be compatible. If you're even semi-self-aware (and rightfully assuming you're a programmer), you'll be able to easily identify the parts of the code that require modification to support your own swirling vortex of insanity. In fact, if you want to convert this code to .Net Core, be my guest, but make sure you post an article about it. In fact, you can post it as an alternative to this article. Make sure you fully describe what it took to convert.

No .Net Core support? Really Simply out of curiosity, I tried to convert the project to .Net Core. The steps that were required are shown at the end of this article.

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

Originally, I put the onus on the programmer of enabling some of the ideas via base entity classes, in the form of various ways to implement SqlParameter[] creation. In this version of the article, I included more automatic method for it, without removing the ability of the programmer to do it the old way. This new idea is explained in the section that talks about how reflection is used in the library.

To make this code suitable for public distribution, I removed the connection string list class that manages the fore-mentioned connection string chaos that we have to deal with at work, but left in the security-minded code, such as encrypted databases and obfuscated connection strings.

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, unless he/she wants to do that.

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.72 (need to use 4.0 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 Microsoft 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.
     
  • Singletons are used for single-instance objects in the TestHarness application. I normally just create a static global class that instantiates globally needed objects, but I thought I'd stray outside that particular comfort zone and do something I'd never had real cause to use.
     
  • Beyond casual mention, SQL coding techniques and best practices are pretty much outside the scope of this article. Using this code (or even your favorite ORM) does NOT absolve you of the responsibility of being familiar with SQL server and how to write adequate stored procedures and queries. Your job is being a programmer, so BE A PROGRAMMER.

The sample solution architecture is implemented in such a way as to minimally exercise the code. Feel free to release your inner programmer to make it in your own image. I retained the comments in the code snippets in an effort to reduce the typed article narrative. You may notice html-style line-breaks in the intellisense comments. The reason is that VS2019 is supposed to support line-breaks here, but older versions do not. This lack of support in older versions makes my prolific use of comments kinda unwieldy because they end up being REALLY long and many times extend beyond the end of the screen. Blame Microsoft.

Connection Strings with [PWConnectionString]

As stated above, the PWConnectionString class presented here was developed due to the extreme breadth of operating environments we have to support at work. In our code, we have a ConnectionStringList class that implements proprietary code to support our infrastructure 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 mostly unremarkable.

Of most notable interest is the fact that the connection string is assembled from it's component parts, and automatically adapts to the use of Windows login or database credentials. Also, the ConnectionString property returns the assembled connection string as a base-64 encoded string. At work, we do this to keep the connection string obfuscated until it's actually needed by a SqlConnection object. This keeps the security nazis off our backs when they're checking the code with source code security scanning apps, such as Fortify.

C#
#region Properties

/// <summary>
/// Get/set a flag indicating whether our connections encrypt <br/> 
/// traffic between the db and the app. The default value is <br/>
/// true.
/// </summary>
public bool EncryptTraffic { get; set; }

/// <summary>
/// Get/set the name of this object (use this name to retrieve it later)
/// </summary>
public string Name { get; set; }

/// <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; }

/// <summary>
/// Get a flag indicating whether the parameters for the connection string are valid
/// </summary>
private bool IsValid
{
    get
    {
        string value = string.Concat( this.Server,   ","
                                        ,this.Database, ","
                                        ,this.UserID,   ","
                                        ,this.Password);
        string[] parts = value.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
        return (parts.Length >= 2);
    }
}

/// <summary>
/// Get the appropriate credentials needed for the server (if any). This property is used 
/// only when the connection string is being constructed for use.
/// </summary>
private string Credentials
{
    get
    {
        string value = "Integrated Security=";
        // If the userid OR passwrod 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);
        }
        // otherwise, we set the specified server database credentials
        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. By default, this indicator should be true, and thus will NOT 
/// return an empty/null string.
/// </summary>
private string WithEncryptedTraffic
{
    get
    {
        string value = string.Empty;
        if (this.EncryptTraffic)
        {
            value="Encrypt=true; TrustServerCertificate=true;";
        }
        return value;
    }
}

/// <summary>
/// Get the connection string, (constructed and returned as a base64-encoded string).
/// </summary>
/// <remarks>Use the string extension method Base64Decode() to decode the string into readable text.</remarks>
public string ConnectionString
{
    get
    {
        string value = string.Empty;
        if (this.IsValid)
        {
            // build the connection string with the specified server, database, and credentials
            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.");
        }
        return value.Base64Encode();
    }
}

#endregion Properties

Finally, we have an override for the ToString() method, which simply returns the value of the ConnectionString property. This is simply a "just-in-case" approach to getting the connection string, and puts the ToString() override to good use. (Normally, if you call ToString() for a class that doesn't have an overriden version of the method, you get the name of the class instead of anything meaningful.)

C#
/// <summary>
/// Override that returns the base64-encoded connection string.
/// </summary>
/// <returns>The base64-encoded connection string</returns>
public override string ToString()
{
    return this.ConnectionString;
}

The [DAL] (Data Access Layer) Class

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 write code to identify properties for inserting/updating into the database is, quite frankly, the main reason various corporate entities developed ORMs. ORMs can automatically identify what properties exist in a given entity, but where they go wrong is when they assume that a given entity is an exact representation of a table in the database. This is fine if you're not doing anything in the least bit complicated, but when you start throwing ORMs at real-world (and admittedly sometimes wonky) problems, they just get in the way.

NOTE: Pretty much all of the methods in the DAL are protected virtual. The reason is that it's entirely plausible that a given programmer may either want to use all of those methods from a derived class, want to override one or more, override one or more of the methods, or even both. I am of the personal opinion that making private non-virtual methods just interferes with the programmer's use of a given class.

My DAL object makes heavy use of reflection (there are many good discussions on the internet about what reflection is, and how it works in .Net) so that most of the dreariness of writing code by hand is reasonably mitigated. However, it does not absolve us of creating a certain amount of code.

First, we establish some necessary properties, and initialize them in the constructor. Descriptions of each property's purpose are in the following code snippet.

C#
#region properties

/// <summary>
/// Get/set flag indicating whether the List<T> ExecuteXXX<T>() methods should <br/>
/// throw an exception if the DataTable retrieved by the query does not match the model <br/>
/// being created (it compares the number of data table columns with the number of assigned <br/>
/// values in the model). The default value is false.
/// </summary>
public bool   FailOnMismatch                 { get; set; }
/// <summary>
/// Get/set value indicating the SqlCommand object's 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 a return value parameter is added to the sql <br/>
/// parameter list if it's missing. This only applies to the SetData method <br />
/// (insert/update/delete functionality). In order for this to work, you MUST return <br />
/// @@ROWCOUNT from your stored proc.
/// </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>
protected int BulkCopyTimeout                { get; set; }
/// <summary>
/// Get/set options flag for SqlBulkCopy operations
/// </summary>
protected SqlBulkCopyOptions BulkCopyOptions { get; set; }
/// <summary>
/// Get/set the external transaction that can be set for/used by SqlBlukCopy.
/// </summary>
protected SqlTransaction ExternalTransaction { get; set; }
/// <summary>
/// Get/set a flag indicating whether the database can be accessed from <br/>
/// the GetData or SetData methods. Allows debugging of BLL without <br/>
/// actually reading from or writing to the database. Sefault value <br/>
/// is true.
/// </summary>
public bool CanReadWriteDB                   { get; set; }

#endregion properties

There are two constructors, so you can pass in either a string, or a PWConnectionString object.

C#
#region constructors

/// <summary>
/// Create instance of the DAL, and set default values for properties.
/// </summary>
/// <param name="connStr"></param>
/// <exception cref="ArgumentNullException">The connStr parameter cannot be null/empty.</exception>
public DAL(string connStr)
{
    if (string.IsNullOrEmpty(connStr))
    {
        throw new ArgumentNullException("connStr");
    }
    // this connection string *should* be base64 encoded
    this.ConnectionString        = connStr;
    this.Init();
}

/// <summary>
/// Constructor (calls the other overload to set the connection string)
/// </summary>
/// <param name="conStr">The AefConnectionString object to use for this DAL instance.</param>
public DAL(PWConnectionString connStr) : this(connStr.ConnectionString)
{
}

#endregion constructors

The DAL has two basic functions - getting data, and setting data. Setting data is further broken down into setting data one record at a time, and doing a "bulk" insert. All of that processing utilizes reflection, which is what makes this code work with ANY entity you could throw at it.

How it Uses Reflection

Let's start by defining an entity (the sample entities I use here are in the test harness application included in the solution). Here's a simple entity that has six public properties. Notice that none of the properties are decorated, and all of them have a set method. They're also virtual so that I can overload them later in the article (for purposes of example).

C#
public class MyEntity
{
    public virtual string   StringValue   { get; set; }
    public virtual int      IntValue      { get; set; }
    public virtual double   FloatValue    { get; set; }
    public virtual bool     BoolValue     { get; set; }
    public virtual DateTime DateTimeValue { get; set; }
    public virtual string   AnotherString { get; set; }
}

Normally, when you use ADO, you're saddled with not only writing the code to access the database, but wrangling a SqlParameter array in order to save data, or manually deserializing the result set you get back when retrieving data. This DAL object eliminates both of these aspects of dealing with database access via ADO, by using reflection.

The Static [DAL] Methods

Let me start off by acknowledging that there are many ways to skin this particular cat, and my approach is just one of those. I could have used added a method to the existing ObjectExtensions.TypeExtensions class, but I wanted to keep all this stuff in one place. This is purely an artistic decision, so don't assume it's the "one true way". Maybe one day, when I'm bored, I'll re-organize the code. But then again, maybe not.

The static SqlParameters class contains a single static method that allows you to build a SqlParameter array from the specified entity object.

C#
/// <summary>
/// Creates a SqlParameter array from the specified entity, and based on the specified <br/>
/// bulk insert type
/// </summary>
/// <typeparam name="T">The entity type</typeparam>
/// <param name="entity">The entity object</param>
/// <param name="bulkType">The bulk insert type.</param>
/// <param name="precedence">How to treat the discovered SqlParameter[] property (if any)</param>
/// <param name="propertyName">Case-sensitive name of desired SqlParameter[] property</param>
/// <returns>An appropriate SqlParameter array </returns>
/// <exception cref="ArgumentNullException">The entity cannot be null</exception>
/// <exception cref="InvalidOperationException">The propertyName should never be null.</exception>
protected static SqlParameter[] MakeSqlParameters<T>(T entity, 
                                                        BulkInsertType bulkType, 
                                                        ParamPrecedence precedence = ParamPrecedence.None, 
                                                        string propertyName = "SqlParameters")
{
    if (entity == null)
    {
        throw new ArgumentNullException("entity");
    }
    if (string.IsNullOrEmpty(propertyName))
    {
        throw new InvalidOperationException("It makes no sense to specify a null propertyName. Ever.");
    }

    SqlParameter[] parameters = null;
    PropertyInfo[] properties = entity.GetType().GetProperties();

    // see if we can find the specified propertyName that returns a SqlParameter[] (with the right name)
    PropertyInfo sqlParams = sqlParams = properties.FirstOrDefault(x => x.PropertyType.Name == "SqlParameter[]" && 
                                                                        x.Name == propertyName);

    // if the entity has a property that returns a SqlParameter array AND the calling 
    // method did not specify to ignore it, set the parameters var to that property's 
    // value, and our job is done here
    if (sqlParams != null && precedence != ParamPrecedence.UseBulkType)
    {
        parameters = (SqlParameter[])sqlParams.GetValue(entity);
    }
    else
    // looks like we gotta finger it out on our own - NOBODY EXPECTED THE MANUAL DETECTION!!
    {
        List<SqlParameter> list = new List<SqlParameter>();

        properties = DAL.GetEntityProperties(properties, bulkType);

        // populate the list of SqlPrameters from the properties we gatherd in the switch statment.
        foreach(PropertyInfo property in properties)
        {
            list.Add(new SqlParameter(string.Format("@{0}", property.Name), property.GetValue(entity)));	
        }
        parameters = list.ToArray();
    }

#if DEBUG
    Global.WriteLine("-----------------------------"); 
    if (properties.Length == 0)
    {
        Global.WriteLine("No properties found.");
    }
    else
    {
        // satisfy my fanatical desire to line stuff up.
        int length = parameters.Max(x=>x.ParameterName.Length) + 1;
        string format = string.Concat("    {0,-", length.ToString(), "}{1}");

        //// i thought this was providing redundant info, but only commented it out so I can 
        //// easily get it back if needed.
        //Global.WriteLine("Discovered properties:");
        //foreach(PropertyInfo item in properties)
        //{
        //	string text = string.Format(format, item.Name, item.GetValue(entity).ToString());
        //	Global.WriteLine(text); 
        //}
        Global.WriteLine("Resulting parameters:");
        foreach(SqlParameter item in parameters)
        {
            string text = string.Format(format, item.ParameterName, item.SqlValue);
            Global.WriteLine(text);
        }
    }
#endif

    // and return them to the calling method
    return parameters;
}

Despite the comments being included, I feel like more explanation of the parameters is warranted.

  • T entity - this is the entity object that contains the properties we'll be returning in the SqlParameter array
     
  • BulkInsertType bulkType - Indicates how you want to populate the resulting SqlParameter array.
    • ALL - causes the MakeParameters method to return all properties, regardless of their status. It is only included for completeness, and you will most likely never use this value, especially for use in the DAL.
    • DBInsertAttribute - causes the MakeParameters metrhod to return any property decorated with the [CanDbInsert] attribute. This is probably the one you'll use the most (if you're not using one or more SqlParameter[] properties in your entity).
    • HasSetMethod - causes the MakeParameters method to return any property that has a set method.
  • ParamPrecedence precedence - Indicates whether or not you want to use or ignore the specified bulk insert type (the default value is ParamPrecedence.None).
    • None - causes the MakeParameters method to return the "standard" SqlParameter[] property, named "SqlParameters", if it exists. If it doesn't exist, it uses the specified BulkInsertType value to determine how to get the properties.
    • UseBulkType - causes the MakeParameters method to *ignore* any/all SqlParameter[] properties in the entity, and get properties based on the specified bulkType value.
  • string propertyName - the name of the SqlParameter[] property in the entity object that you want to use which returns a SqlParameter[] (the default value is "SqlParameters"). This parameter is only useful when you specify a precedence of None.

As you can see, the resulting array of SqlParameter items depends on the combination of parameter values you specify when you call the MakeParameters method. I tried to sufficiently comment the method so there wouldn't be any/many questions about what's going on.

Next up, we have some supporting static methods that are used by methods in the DAL, and these methods are three overloads to the GetEntityProperties method. In the interest of brevity, I will only show the prototype for each method.

C#
protected static PropertyInfo[] GetEntityProperties(Type type, BulkInsertType bulkType)...

This overload gets properties for the entity *type*, based on the value of the bulkInsert parameter, and is used to assist in the creation of columns in a DataTable object.

C#
protected static PropertyInfo[] GetEntityProperties<t>(T entity, BulkInsertType bulkType)
</t>

This overload returns property info (including values) for an entity (or class), based on the value of the bulkInsert parameter (it actually calls the next overloaded method), and is used by the DAL methods that actually do the work involving the database.

C#
protected static PropertyInfo[] GetEntityProperties(PropertyInfo[] properties, BulkInsertType bulkType)

This overload is where the actual work is done based on the value of the bulkInsert parameter, and can be used inf you've already got a SqlParameter[] from another source (probably not associated with this code).

Testing (Using the TestHarness Application)

NOTE: We interrupt our regularly scheduled class description to bring you this important (and presciant) announcement regarding testing, because it's the best way we can think of to allow you a moment to reflect on the effects of reflection. So without further deflection (or inappropriate erections), a discussion of type detection with reflection... in this section...

To assist in testing, I added a TestHarness application project (described later) to the solution. In that application, I create a BLL object, and added some testing methods to it. What follows is the console app output for each call that the BLL makes to the inherited DAL methods. I created a list of MyEntity objects, each with five items. In the interest of brevity, I only show the output for one item's output (because all five are the same).

The key points with regards to the MyEntity class - it contains six undecorated properties, and no SqlParameter[] property. This means that the two default parameters would have no effect on the result, so they're not specified.

==========================================================
The method call that was made:
ExecuteStoredProc(e.AsEnumerable(), "test", BulkInsertType.ALL);

The output that was generated:
Because there are no SqlParameter[] properties, it returned all of the parameters.
-----------------------------
Resulting parameters:
    @StringValue   101
    @IntValue      1
    @FloatValue    1
    @BoolValue     True
    @DateTimeValue 4/3/2021 8:54:54 AM
    @AnotherString 101B

==========================================================
The method call that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.DBInsertAttribute)

The output that was generated:
Because there are no SqlParameter[] properties, and because none of the properties were decorated 
with the [CanDbInsert] attribute, it returned no parameters.
-----------------------------
No properties found.

==========================================================
The method call that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.HaveSetMethod)

The output that was generated:
Because there are no SqlParameter[] properties, and because all of the properties have a "set" 
method, it returned six parameters.
-----------------------------
(should get six props/params for each entity)
-----------------------------
Resulting parameters:
    @StringValue   101
    @IntValue      1
    @FloatValue    1
    @BoolValue     True
    @DateTimeValue 4/3/2021 8:54:54 AM
    @AnotherString 101B

Now let's add a SqlParameter[] property. To make it easy on the examples, we'll simply inherit from the MyEntity class. Note that I'm using the "standard" name for this property to exercise the code a little more.

C#
public class MyEntity2 : MyEntity
{
	// use the standard name that the code knows to look for
    public virtual SqlParameter[] SqlParameters
    {
        get
        {
            SqlParameter[] value = new SqlParameter[]
            {
                new SqlParameter ("@StringValue",   this.StringValue  )
                ,new SqlParameter("@IntValue",      this.IntValue     )
                ,new SqlParameter("@FloatValue",    this.FloatValue   )
                ,new SqlParameter("@BoolValue",     this.BoolValue    )
                ,new SqlParameter("@DateTimeValue", this.DateTimeValue)
            };
            return value;
        }
    }
}

Because we added the SqlParameter[] property, we have twice as many ways we can use the method that makes sense.

==========================================================
The method call(s) that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.ALL, ParamPrecedence.None);
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.DBInsertAttribute, ParamPrecedence.None);
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.HaveSetMethod, ParamPrecedence.None);

The output that was generated:
All three of those method calls will retrieve 5 of the 6 available properties 
from the [SqlParameters] property , because and precedence is [None], thus 
ignoring the [bulkInsert] value.
-----------------------------
Resulting parameters:
    @StringValue   101
    @IntValue      1
    @FloatValue    1
    @BoolValue     True
    @DateTimeValue 4/3/2021 9:12:54 AM


==========================================================
The method call(s) that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.ALL, ParamPrecedence.UseBulkType)

The output that was generated:
The method call above will return all properties, regardless of any status 
they may have, because precedence is set to [UseBulkType], thus ignoring 
any SqlParameter[] properties that might be defined. Furthermore, note that 
the SqlParameter[] property is also returned, which is probably not the 
desired result (this is why using [ALL] is not recommended within the 
context of using the DAL).
-----------------------------
Resulting parameters:
    @SqlParameters
    @StringValue   101
    @IntValue      1
    @FloatValue    1
    @BoolValue     True
    @DateTimeValue 4/3/2021 9:12:54 AM
    @AnotherString 101B

==========================================================
The method call that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.HaveSetMethod, ParamPrecedence.UseBulkType)

The output that was generated:
This call will return any property that has a [set] method because the 
precedence is set to [UseBulkType], thus ignoring any SqlParameter[] 
properties that might be defined. Note that since the SqlParameter[] 
property doesn't have a [set] method, it is  not returned as a 
parameter.
-----------------------------
Resulting parameters:
    @StringValue   101
    @IntValue      1
    @FloatValue    1
    @BoolValue     True
    @DateTimeValue 4/3/2021 9:12:54 AM
    @AnotherString 101B

==========================================================
The method call that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.DBInsertAttribute, ParamPrecedence.UseBulkType)

The output that was generated:
This method call returns no parameters, because bulkType is set to 
[DBInsertAttribute] with no properties being decorated with the [CanDbInsert] 
attribute, and because precedence is set to [UseBulkType], which causes any 
SqlParameter[] properties to be ignored.
-----------------------------
No properties found.

Finally, lets derive a new class - MyEntity3 - from MyEntity2, decorate five of the six properties, and add a second SqlParameter[] property.

C#
public class MyEntity3 : MyEntity2
{
    [CanDbInsert]
    public override string   StringValue   { get; set; }
    [CanDbInsert]
    public override int      IntValue      { get; set; }
    [CanDbInsert]
    public override double   FloatValue    { get; set; }
    [CanDbInsert]
    public override bool     BoolValue     { get; set; }
    [CanDbInsert]
    public override DateTime DateTimeValue { get; set; }

    public SqlParameter[] SqlParametersEntity3
    {
        get
        {
            SqlParameter[] value = new SqlParameter[]
            {
                    new SqlParameter("@StringValue",   this.StringValue)
                ,new SqlParameter("@IntValue",      this.IntValue)
            };
            return value;
        }
    }
}

Once again, the MyEntity3 class gives us many more ways to use the MakeParameters method:

==========================================================
The method call(s) that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.DBInsertAttribute, ParamPrecedence.None)

The output that was generated:
This method call returns 5 of 6 parameters because precedence is None, and the 
entity contains a SqlParameters prop). The [bulkType] is ignored.
-----------------------------
Resulting parameters:
    @StringValue   101
    @IntValue      1
    @FloatValue    1
    @BoolValue     True
    @DateTimeValue 4/3/2021 12:11:39 PM

==========================================================
The method call(s) that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.DBInsertAttribute, ParamPrecedence.None, "SqlParametersXYZ")

The output that was generated:
This method call returns 5 of 6 parameters because  a) precedence is set to 
[None], b) the specified [propertyName] doesn't exist, but c) the class *does* 
have decorated properties.
-----------------------------
Resulting parameters:
    @StringValue   101
    @IntValue      1
    @FloatValue    1
    @BoolValue     True
    @DateTimeValue 4/3/2021 12:11:39 PM

==========================================================
The method call(s) that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.DBInsertAttribute, ParamPrecedence.None, "SqlParametersEntity3")

The output that was generated:
This method call gets the two properties because a) the precedence is set to 
[None], and b) the named SqlParameter property was found, thus ignoring the 
specified [bulkType].
-----------------------------
Resulting parameters:
    @StringValue 101
    @IntValue    1

==========================================================
The method call(s) that was made:
ExecuteStoredProc(e.AsEnumerable(),"test", BulkInsertType.DBInsertAttribute, ParamPrecedence.UseBulkType)

The output that was generated:
This method call returns 5 of 6 properties because the the [bulkType] is set 
to [DBInsertAttribute], the class has decorated properties, and the 
precedence is set to [UseBulkType], thus ignoring any SqlParameter[] 
properties that might be defined.
-----------------------------
Resulting parameters:
    @StringValue   101
    @IntValue      1
    @FloatValue    1
    @BoolValue     True
    @DateTimeValue 4/3/2021 12:11:39 PM

As you can see, the results of the MakeParameters method can vary, depending on how you call it.

Now that you have an understanding about the way reflection is used in support of the DAL, let's talk about getting and setting data.

Getting and Setting Data

In order to retrieve data, the DAL implements three overloaded protected virtual methods. These will be the methods that are typically used by your BLL object.

Method - [ExecuteStoredProc]

The ExecuteStoredProc overloads allow you to use a stored procedure to get or set data. The overload that is chosen by .Net depends on the return value and parameters you use. The last method in the block below is included to allow a given "set data" operation to use a persistent SqlConnection object for all objects in the specified collection, ostensibly to provide an efficient "upsert" functionality. One of our programmers expressed concern that opening up a SqlConnection object for each insert into the database. I can't imagine this not being more efficient, but honestly, I have not conducted any testing to be able to confirm or deny the idea with tangible results.

C#
/// <summary>
/// Executes the named stored proc (using ExecuteReader) that gets data from the database. <br/> 
/// </summary>
/// <typeparam name="T">The type of the list item</typeparam>
/// <param name="storedProc">The name of the stored procedure to execute</param>
/// <param name="parameters">The parameters to pass to the stored procedure</param>
/// <returns>A list of the specified object type (may be empty).</returns>
/// <remarks>Useage: List<MyObject> list = this.ExecuteStoredProc<MyObject>(...)</remarks>
/// <exception cref="ArgumentNullException">The storedProc parameter cannot be null/empty.</exception>
protected virtual List<T> ExecuteStoredProc<T>(string storedProc, SqlParameter[] parameters)
{
	if (string.IsNullOrEmpty(storedProc))
	{
		throw new ArgumentNullException("storedProc");
	}
	// 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 specified stored proc (using ExecuteNonQuery) that stores data (specified <br/>
/// in the [parameters] parameter) in the database. 
/// </summary>
/// <param name="storedProc">The stored proc to execute</param>
/// <param name="parameters">The parameters to pass to the stored procedure</param>
/// <returns>The number of records affected</returns>
/// <exception cref="ArgumentNullException">The [storedProc] parameter cannot be null/empty.</exception>
/// <exception cref="ArgumentNullException">The [parameters] array parameter cannot be null.</exception>
/// <exception cref="ArgumentNullException">The [parameters] array parameter cannot be empty.</exception>
protected virtual int ExecuteStoredProc(string storedProc, SqlParameter[] parameters)
{
	if (string.IsNullOrEmpty(storedProc))
	{
		throw new ArgumentNullException("storedProc");
	}
	if (parameters == null)
	{
		throw new ArgumentNullException("parameters");
	}
	if (parameters.Length == 0)
	{
		throw new InvalidOperationException("The [parameters] array must contain at least one item.");
	}
	// You must SET NOCOUNT OFF at the top of your stored proc in order to automatically 
	// return the @@ROWCOUNT value (created and set by sql server).
	int result = this.SetData(storedProc, parameters, CommandType.StoredProcedure);

	return result;
}

/// <summary>
/// Execute the specified stored proc to save the specified data item in the database.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">The data item to save</param>
/// <param name="storedProc">The stored proc to execute</param>
/// <param name="bulkType">How to build the SqlParameter array</param>
/// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
/// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
/// <returns>The number of records affected</returns>
protected virtual int ExecuteStoredProc<T>(T data, 
											string storedProc, 
											BulkInsertType bulkType, 
											ParamPrecedence precedence = ParamPrecedence.None, 
											string paramArrayPropName = "SqlParameters")
{
	int result = 0;
	SqlParameter[] parameters = DAL.MakeSqlParameters(data, bulkType, precedence, paramArrayPropName);
	result = this.ExecuteStoredProc(storedProc, parameters);
	return result;
}

/// <summary>
/// Executes the specified stored proc for an entity collection, using a persistent <br/>
/// sql connection. This is intended to be used for data that needs to be merged <br/>
/// (update or insert) into a table rather than simply inserted.
/// </summary>
/// <typeparam name="T">The type of entity represented by the specified collection</typeparam>
/// <param name="data">The collection of entities</param>
/// <param name="storedProc">The name of the stored proc (must be a stored proc)</param>
/// <param name="bulkType">Inidcates how properties should be retrieved from the entity items</param>
/// <param name="precedence">Whether to ignore the discovered SqlParameter[] property</param>
/// <param name="paramArrayPropName">Name of desired SqlParameter[] property</param>
/// <exception cref="ArgumentNullException">If the data parameter is null</exception>
/// <exception cref="ArgumentNullException">If the storedProc parameter is null/empty</exception>
/// <exception cref="InvalidOperationException">If the data collection is empty.</exception>
/// <returns>The number of records affected (inserted + updated).</returns>
/// <remarks>Usage:  int result = ExecuteStoredProc(data.AsEnumerable(), "dbo.MyStoredProc", BulkInsertType.DBInsertAttribute);</remarks>
protected virtual int ExecuteStoredProc<T>(IEnumerable<T> data, 
											string storedProc, 
											BulkInsertType bulkType,
											ParamPrecedence precedence = ParamPrecedence.None, 
											string paramArrayPropName = "SqlParameters")
{
	if (string.IsNullOrEmpty(storedProc))
	{
		throw new ArgumentNullException("storedProc");
	}
	if (data == null)
	{
		throw new ArgumentNullException("data");
	}
	if (data.Count() == 0)
	{
		throw new InvalidOperationException("Data collection must contain at least onme item");
	}

	int result = this.DoBulkMerge(data, storedProc, bulkType, CommandType.StoredProcedure, precedence, paramArrayPropName);
	return result;
}

Notice the last two overloads above. Some of you might be aware that normally, calling one of these overloads will cause .Net to execute the (T entity...) overload, even if you pass a collection of objects instead of just a single object. The reason is that when using generic types, .Net sees a collection of objects the same as a non-collection object, so it finds and uses the most appropriate overload. But all hope is not lost. All you have to do is be more specific about what you're passing to the method. See the sample code below.

C#
// If you're working with a single object, it works as expected, and 
// will execute the overload with the prototype "(T entity, ...)""
MyObject oneObject = new MyObject;
int result = this.ExecuteStoredProc(oneObject, ...);

// However, if you want to use the overload with the prototype 
// "(IEnumerable<t> entity, ...)", you simply have to call the 
// collection's AsEnumerable() method.
List<MyObject> manyObj = new List<MyOIbject>()
{ 
    new MyObject(), 
    new MyObject() 
};
int result = this.ExecuteStoredProc(oneObject.AsEnumerable(), ...);
</t>

Take note that the ExecuteQuery method overloads (discussed next) also require this manipulation of parameters to finesse .Net into selecting the desired overload.

Method - [ExecuteQuery]

Like the ExecuteStoredProc method, the ExecuteQuery method allows the programmer to get or set data, and several overloads are implemented to that end. All the same comments above regarding the ExecuteStoredProc, apply to this method as well, except this stored procedure is intended to execute a straight-up SQL query.

C#
/// <summary>
/// Executes the specified query (using ExecuteReader) that gets data from the database.
/// </summary>
/// <typeparam name="T">The type of the list item</typeparam>
/// <param name="query">The query text to execute</param>
/// <param name="parameters">The data to pass to the query text</param>
/// <returns>A list of the specified type.</returns>
/// <remarks>Useage: List<MyObject> list = this.ExecuteStoredProc<MyObject>(...)</remarks>
/// <exception cref="ArgumentNullException">If the query parameter is null/empty</exception>
protected virtual List<T> ExecuteQuery<T>(string query, params SqlParameter[] parameters)
{
    // If you have questions regarding the use of parameters in query text, google 
    // "c# parameterized queries". In short, parameterized queries prevent sql 
    // injection. This code does not (and cannot) validate the use of parameters 
    // because some queries simply don't need them. Therefore, it's completely 
    // on you - the developer - to make sure you're doing it right.

    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 <br/>
/// database. 
/// </summary>
/// <param name="query"></param>
/// <param name="parameters"></param>
/// <returns>The number of records affected (if you didn't use SET NOCOUNT ON in 
/// your batch)</returns>
/// <exception cref="ArgumentNullException">If the query parameter is null/empty</exception>
protected virtual int ExecuteQuery(string query, params SqlParameter[] parameters)
{
    // If you have questions regarding the use of parameters in query text, google 
    // "c# parameterized queries". In short, parameterized queries prevent sql 
    // injection. This code does not (and cannot) validate the use of parameters 
    // because some queries simply don't need them. Therefore, it's completely 
    // on you - the developer - to make sure you're doing it right.
    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;
}

/// <summary>
/// Execute the specified sql query to save the specified data item. This method creates the <br/>
/// parameters for you using the properties in the specified entity. While not required, you <br/>
/// can "grease the skids" by implementing a public property in your entities that returns a <br/>
/// SqlParameter[] array.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">The data item to save</param>
/// <param name="query">The sql query text to execute</param>
/// <param name="bulkType">The flag that indicates how to build the SqlParameter array</param>
/// <returns>The number of records affected.</returns>
protected virtual int ExecuteQuery<T>(T data, 
                                        string query, 
                                        BulkInsertType bulkType,
                                        ParamPrecedence precedence = ParamPrecedence.None, 
                                        string paramArrayPropName = "SqlParameters")
{
    int result = 0;
    SqlParameter[] parameters = DAL.MakeSqlParameters(data, bulkType);
    result = this.ExecuteQuery(query, parameters);
    return result;
}

/// <summary>
/// Performs an insert of an entity collection, using a persistent connection (reduces <br/>
/// processing time and memory consumption because we're not opening/closing a database <br/>
/// connection for every item). This method is intended to be used for data that needs <br/>
/// to be merged (update or insert) into a table rather than simply inserted.
/// </summary>
/// <typeparam name="T">The type of entity represented by the specified collection</typeparam>
/// <param name="data">The collection of entities</param>
/// <param name="storedProc">The name of the stored proc (must be a stored proc)</param>
/// <param name="bulkType">Inidcates how properties should be retrieved from the entity items</param>
/// <exception cref="ArgumentNullException">If the data parameter is null</exception>
/// <exception cref="ArgumentNullException">If the query parameter is null/empty</exception>
/// <exception cref="InvalidOperationException">If the data collection is empty.</exception>
/// <returns>The number of records affected.</returns>
protected virtual int ExecuteQuery<T>(IEnumerable<T> data, 
                                        string query, 
                                        BulkInsertType bulkType,
                                        ParamPrecedence precedence = ParamPrecedence.None, 
                                        string paramArrayPropName = "SqlParameters")
{
    if (string.IsNullOrEmpty(query))
    {
        throw new ArgumentNullException("query");
    }
    if (data == null)
    {
        throw new ArgumentNullException("data");
    }
    if (data.Count() == 0)
    {
        throw new InvalidOperationException("Data collection must contain at least onme item");
    }
    int result = this.DoBulkMerge(data, query, bulkType, CommandType.Text, precedence, paramArrayPropName);
    return result;
}

Method - [ExecuteBulkInsert]

An important aspect of inserting data into the database is the ability to just flog data into a table. So, this DAL object implements functionality to do just that. You have the option of hitting it with a DataTime object, or with your own entity collection.

C#
/// <summary>
/// Performs a simply bulk insert into a table in the database. The schema MUST be part of <br/>
/// the table name. Using a bulk insert is NOT suitable if you need to merge data into an <br/>
/// existing table. Use the BulkMerge stored proc instead.
/// </summary>
/// <param name="dataTable">The datatable to bulk copy</param>
/// <returns>The number of records affected.</returns>
/// <exception cref="InvalidOperationException">If the table name hasn't been specified in the datatable</exception>
/// <exception cref="InvalidOperationException">If the schema hasn't been specified as part of the tablename</exception>
/// <exception cref="InvalidOperationException">If the dataTable is empty</exception>
protected virtual int ExecuteBulkInsert(DataTable dataTable)
{
	if (string.IsNullOrEmpty(dataTable.TableName))
	{
		throw new InvalidOperationException("The table name MUST be specified in the datatable (including the schema).");
	}
	if (!dataTable.TableName.Contains('.') || dataTable.TableName.StartsWith("."))
	{
		throw new InvalidOperationException("The schema MUST be specified with the table name.");
	}
	if (dataTable.Rows.Count == 0)
	{
		throw new InvalidOperationException("The dataTable must contain at least one item");
	}

	int recsBefore = BulkInsertTargetCount(dataTable.TableName);

	int           recordsAffected = 0;
	SqlConnection conn            = null;
	SqlBulkCopy   bulk            = null;

	using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
	{
		conn.Open();
		using (bulk = new SqlBulkCopy(conn, this.BulkCopyOptions, this.ExternalTransaction)
		{
			BatchSize             = this.BulkInsertBatchSize
			,BulkCopyTimeout      = this.BulkCopyTimeout
			,DestinationTableName = dataTable.TableName
		})
		{
			Debug.WriteLine("DoBulkInsert - inserting {0} rows",dataTable.Rows.Count);
			bulk.WriteToServer(dataTable);
		}
	}

	int recsAfter = BulkInsertTargetCount(dataTable.TableName);
	recordsAffected = recsAfter - recsBefore;
	return recordsAffected;
}

/// <summary>
///	Performs a simple bulk insert into a table in the database. The schema MUST be part of <br/>
/// the table name.
/// </summary>
/// <typeparam name="T">The entity type being bulk inserted</typeparam>
/// <param name="data">The list of entities to be inserted</param>
/// <param name="tableName">The table name in which to insert the data</param>
/// <param name="byDBInsertAttribute">If true, only properties decorated with the CanDbInsert 
/// attribute will be stored in the database.</param>
/// <returns>Number of records affected.</returns>
/// <exception cref="ArgumentNullException">If the data parameter is null.</exception>
/// <exception cref="ArgumentNullException">If The tableName parameter cannot be null/empty.</exception>
/// <exception cref="InvalidOperationException">If the data collection is empty.</exception>
/// <exception cref="InvalidOperationException">If the table name doesn't include the schema.</exception>
protected virtual int ExecuteBulkInsert<T>(IEnumerable<T> data, 
										string tableName, 
										BulkInsertType bulkType, 
										ParamPrecedence precedence = ParamPrecedence.None, 
										string paramArrayPropName = "SqlParameters")
{
	// sanity checks
	if (data == null)
	{
		throw new ArgumentNullException("data");
	}
	if (data.Count() == 0)
	{
		throw new InvalidOperationException("The data collection must contain at least one item");
	}
	if (string.IsNullOrEmpty(tableName))
	{
		throw new ArgumentNullException("The tableName parameter cannot be null or empty.");
	}
	if (!tableName.Contains('.'))
	{
		throw new InvalidOperationException("The schema MUST be specified with the table name.");
	}

	int result = 0;
	DataTable dataTable = null;

	if (data.Count() > 0)
	{
		dataTable = this.MakeDataTable(data, tableName, bulkType, precedence, paramArrayPropName);
		result = this.DoBulkInsert(dataTable);
	}
	return result;
}

Possible [SqlParameter[]] Property Approach

If you change the static method MakeParameters to be public (it's protected as presented in this article), you can call it from your entity, like so:

C#
public SqlParameter[] SqlParameters4
{
    get
    {
        SqlParameter[] value = DAL.MakeSqlParameters(this, 
                                                     BulkInsertType.DBInsertAttribute, 
                                                     ParamPrecedence.UseBulkType);
        return value;
    }
}

To be honest, I see this as wasted effort since the DAL already does this for you (because it uses MakeParameters as well, and in the same way). However, I wanted to illustrate that it is indeed possible to do this. It's also worth mentioning that you can also make the DAL.GetProperties methods public and use those from within your entities as well. To make it easy on you, I included a compiler definition at the top of the DALStaticMethods.cs file. Uncomment the compiler definition, and all of the static methods in the file become public.

Like I said earlier, there are many ways to skin this cat.

The Helper Methods

The info presented in the section are not necessary to understand/use the DAL object, and is presented only in the name of completeness of documentation.

Method - [protected virtual void Init()]

I anticipated having to create overloaded constructors, so I decided to put the initialization code into a method that could be called from each constructor. It turns out that I haven't yet found a need to do it this way, but I kept it around, because you never know what's going to happen in the future. Because it's virtual, the programmer can easily override it to perform additional (BLL-specific) initialization tasks.

C#
/// <summary>
/// Set some reasonable defaults
/// </summary>
protected virtual void Init()
{
    // this method is called from the constructor(s), and exists so that we can overload 
    // the constructor without duplicating code. 
    this.TimeoutSecs             = 300;
    this.FailOnMismatch          = false;
    this.AddReturnParamIfMissing = true;
    this.ExternalTransaction     = null;
    this.BulkInsertBatchSize     = 250;
    this.BulkCopyTimeout         = 600;
    this.BulkCopyOptions         = SqlBulkCopyOptions.Default;
    this.CanReadWriteDB          = true;
}

Method - [protected virtual string TestConnection()]

Adding a method to test the connection seemed logical. Amusingly, the DAL itself doesn't use it, because I figured the programmer would want to call it just once, from his BLL object (that inherits the DAL object.

C#
/// <summary>
/// Allows the programmer to test the database connection before trying to use it.
/// </summary>
/// <returns>If valid and empty string, otherwise, the message text from the ensuing <br/>exception.</returns>
protected virtual string TestConnection()
{
    // assume success
    string        result = string.Empty;
    SqlConnection conn   = null;
    try
    {
        using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
        {
            conn.Open();
            conn.Close();
        }
    }
    catch (Exception ex)
    {
        result = ex.Message;
    }
    return result;
}

Method - [protected virtual string AddTryCatchTranToQuery(...)]

If you use query text (as opposed to stored procedures) to access your database, you can call this method to wrap your query with a transaction. It wraps your query in a try/catch block and creates/commits a transaction. If an exception is thrown, the transaction will be rolled back. You can also optionally specify query text that performs logging (or whatever else you want to do if your query fails.

For stored procedures, I assume that you already includes this kind of code, especially when you're doing any kind of CRUD operations. If you don't do this, you should.

C#
/// <summary>
/// Adds a try/catch block, as well as a transaction (with optional name) to the specified <br/>
/// plain sql query text. This code does not check to see if transaction code is already <br/>
/// part of the query.
/// </summary>
/// <param name="query">The query to encase</param>
/// <param name="logQuery">The query that implements your logging mechanism.</param>
/// <param name="transactionName">The desired transaction name (optional)</param>
/// <returns>The transaction-wrapped query</returns>
protected virtual string AddTryCatchTranToQuery(string query, string logQuery, string transactionName="")
{
    transactionName    = transactionName.Trim();
    logQuery           = logQuery.Trim();

    StringBuilder text = new StringBuilder();
    text.AppendLine("BEGIN TRY");
    text.AppendFormat("    BEGIN TRAN {0};", transactionName).AppendLine();
    text.AppendLine(query).AppendLine();
    text.AppendFormat("    COMMIT TRAN {0};", transactionName).AppendLine();
    text.AppendLine("END TRY");
    text.AppendLine("BEGIN CATCH");
    text.AppendFormat("    IF @@TRANCOUNT > 0 ROLLBACK TRAN {0};", transactionName).AppendLine();
    text.AppendLine(logQuery);
    text.AppendLine("END CATCH");
    return text.ToString();
}

Method - [protected virtual string NormalizeTableName(...)]

One of the ExecuteBulkInsert method overloads needs a table name to be specified. To prevent the possibility of sql injection, we need to normalize the specified table name by putting square brackets around the name (it also accounts for a schema being included in the name. The idea is that anything put into square brackets would be evaluated as a sql table name, and if the table name isn't valid, your stored procedure/query will throw a sql exception.

Beyond this kind of protection, you should really go read this article regarding "SQL inject". The author enumerates methods for mitigating the threat. This is REALLY important if you value your data - SQL Injection Attacks and Some Tips on How to Prevent Them [^ ]. Securing your SQL is entirely on you, not to mention being outside the scope of this article.

C#
/// <summary>
/// Normalizes the table name so there's no chance of sql injection. You can't be too careful.
/// </summary>
/// <param name="tableName">The table name (should have the schema included as well.</param>
/// <returns>The tablename with square brackets around it</returns>
protected virtual string NormalizeTableName(string tableName)
{
    string[] parts = tableName.Split('.');
    tableName = string.Empty;
    foreach(string part in parts)
    {
        tableName = (string.IsNullOrEmpty(tableName))
                    ? string.Format("[{0}]", part)
                    : string.Format(".[{0}]", part);
    }
    return tableName.Replace("[[", "[").Replace("]]","]");
}

Method - [protected virtual int BulkInsertTargetCount(...)]

The ADO SqlBulkCopy object will take an entire data table, and insert the rows into the database. Unfortunately, it does NOT tell you how many records were actually inserted. If you want to know this value, you have to handle it yourself. This method does a simple count of records in the specified table, and returns that value. The DAL calls this method before and after the use of the SqlBulkCopy object and returns the difference between the two counts.

C#
/// <summary>
/// Counts the number of records currently in the table targeted by the merge attempt.
/// </summary>
/// <param name="tableName">The table we're counting</param>
/// <returns>The number of records in the specified table</returns>
protected virtual int BulkInsertTargetCount(string tableName)
{
	if (string.IsNullOrEmpty(tableName))
	{
		throw new ArgumentNullException("tableName");
	}
	if (!tableName.Contains('.') || tableName.StartsWith("."))
	{
		throw new InvalidOperationException("The [tableName] must include a schema. Example: 'dbo.tableName'");
	}

	int result = 0;
	string query = string.Format("SELECT COUNT(1) FROM {0}", this.NormalizeTableName(tableName));
	List<EntityRowCount> rowCount = this.ExecuteQuery<EntityRowCount>(query);
	if (rowCount != null && rowCount.Count > 0) 
	{
		result = rowCount[0].Count;
	}
	return result;
}

Method - [protected virtual DataTable GetData(...)]

When you get right down to it, getting data is pretty simple. All you need is the sql query that you want to execute, parameters to pass to the query, and what kind of query it is (stored procedure or a sql query text). Where I work, we don't use any straight-up sql queries, and require that all database access be accomplished via stored procedures, but I suspect there are at least a few environments that don't enforce that paradigm (those places are probably like hell on earth, and doom be unto all those that dare to enter such a realm).

For this DAL, all "get data" requests eventually funnel into the GetData() method. There really isn't anything strange going on.

C#
/// <summary>
/// Calls SqlCommand.ExecuteDataReader() to retrieve a dataset from the database.
/// </summary>
/// <param name="cmdText">The storedproc or query to execute</param>
/// <param name="parameters">The parameters to use in the storedproc/query</param>
/// <returns>A DataTable object</returns>
/// <exception cref="ArgumentNullException">The cmdText parameter cannot be null/empty.</exception>
protected virtual DataTable GetData(string cmdText, 
                                    SqlParameter[] parameters=null, 
                                    CommandType cmdType = CommandType.StoredProcedure)
{
    if (string.IsNullOrEmpty(cmdText))
    {
        throw new ArgumentNullException("cmdText");
    }

    // 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
            if (this.CanReadWriteDB)
            {
                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;
}

Method - [protected virtual int SetData(...)]

The actual act of setting data is only a little mores exciting. Along with the same set of parameters necessary to make GetData do it's magic, SetData provides an optional parameter that permits the calling method to indicate that the code should include transaction support.

C#
/// <summary>
/// Calls SqlCommand.ExecuteNonQuery to save data to the database.
/// </summary>
/// <param name="cmdText">The query text to execute</param>
/// <param name="parameters">The parameters to use</param>
/// <param name="cmdType">The sql command type</param>
/// <param name="useAdoTransaction">Flag indicating to wrap query with ado transaction</param>
/// <returns>The number of records affected</returns>
/// <exception cref="ArgumentNullException">The cmdText parameter cannot be null/empty.</exception>
protected virtual int SetData(string cmdText, 
								SqlParameter[] parameters, 
								CommandType cmdType = CommandType.StoredProcedure, 
								bool useAdoTransaction=false)
{
	if (string.IsNullOrEmpty(cmdText))
	{
		throw new ArgumentNullException("cmdText");
	}

	int result = 0;
	SqlConnection conn   = null;
	SqlCommand    cmd    =  null;
	SqlTransaction transaction = null;
	using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
	{
		conn.Open();
		if (useAdoTransaction && cmdType != CommandType.StoredProcedure)
		{
			transaction = conn.BeginTransaction();
		}

		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 } );
					}
				}
			}
			try
			{
				if (this.CanReadWriteDB)
				{
					result = cmd.ExecuteNonQuery();
				}
			}
			catch (SqlException ex)
			{
				if (transaction != null && cmdType != CommandType.StoredProcedure)
				{
					transaction.Rollback();
				}
				throw (ex);
			}
			result = (rowsAffected != null) ? (int)rowsAffected.Value : result;
		}
	}
	return result;
}

Method - [protected virtual int DoBulkMerge(...)]

While the ADO SqlBulkCopy object is a nice way to just flog data into the database, it lacks a certain finesse that is sometimes needed, namely the ability to either update or insert records, depending on the target table's use. So, the DAL object provides an ExecuteStoredProc (and ExecuteQuery) method overload to implement this typical "upsert" functionality. We've already talked about the overloaded method, both of which call this method.

In it, a persistent SqlConnection object is created to provide a certain level of efficiency when inserting multiple rows of data. Beyond that, it is very similar to the SetData method (also discussed above)

It is HIGHLY advisable to use a stored procedure to upsert.

C#
/// <summary>
/// Base BulkMerge 
/// </summary>
/// <typeparam name="T">The entity type represented by the collection</typeparam>
/// <param name="data">The entity collection</param>
/// <param name="queryText">The query text to execute</param>
/// <param name="bulkType">How to extract propertiues from the entity</param>
/// <param name="cmdType">The sql command type</param>
/// <returns>Number of records affected.</returns>
protected virtual int DoBulkMerge<T>(IEnumerable<T> data, 
                                        string queryText, 
                                        BulkInsertType bulkType, 
                                        CommandType cmdType, 
                                        ParamPrecedence precedence = ParamPrecedence.None,
                                        string paramArrayPropName = "SqlParameters",
                                        bool useAdoTransaction=false)
{
    if (string.IsNullOrEmpty(queryText))
    {
        throw new ArgumentNullException("queryText");
    }

    int result = 0;
    SqlConnection  conn        = null;
    SqlCommand     cmd         = null;
    SqlTransaction transaction = null;
    using (conn = new SqlConnection(this.ConnectionString.Base64Decode()))
    {
        conn.Open();
        if (useAdoTransaction && cmdType != CommandType.StoredProcedure)
        {
            transaction = conn.BeginTransaction();
        }
        using (cmd = new SqlCommand(queryText, conn) { CommandTimeout = this.TimeoutSecs, CommandType = cmdType } )
        {
            try
            {
                foreach(T item in data)
                {
                    SqlParameter[] parameters = DAL.MakeSqlParameters(item, bulkType, precedence, paramArrayPropName);
                    if (parameters != null)
                    {
                        cmd.Parameters.AddRange(parameters);
                        if (this.CanReadWriteDB)
                        {
                            cmd.ExecuteNonQuery();
                        }
                        cmd.Parameters.Clear();
                        result++;
                    }
                }
            }
            catch (Exception ex)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw(ex);
            }
        }
    }
    return result;
}

Here's an example of an upsert-style stored procedure. Keep in mind that this code is NOT a panacea for preventing duplicates from being inserted into the table. You should implement constraints that enforce unique records (google is your friend), and additionally, you can use the SQL MERGE statement instead of the simple upsert code provided below.

C#
DECLARE @rowCount INT = -1;

BEGIN TRY

    BEGIN TRANSACTION MergeData_Transaction;

    UPDATE	dbo.MyTable
    SET		StringValue     = @StringValue   
            ,IntValue		= @IntValue     
    WHERE	IntValue		= @IntValue;
    SET @rowCount = @@ROWCOUNT;

    IF @rowCount = 0
    BEGIN
        INSERT INTO dbo.WebSvcTarget
        (
            StringValue
            ,IntValue
        )
        VALUES
        (
            @StringValue   
            ,@IntValue     
        )
    END
    SET @rowCount = @rowCount + @@ROWCOUNT;

    COMMIT TRANSACTION MergeData_Transaction;

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        SET @rowCount = 0;
        ROLLBACK TRANSACTION MergeData_Transaction;

        -- do something with the error if desired
    END
END CATCH

Method - [protected static T ConvertFromDBValue<T>(...)

This method supports the generic types we depend on so heavily in the DAL object. It's purpose is to convert a value retrieved from the database to a value that C# can handle when a DataTable is being populated.

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

Method - [protected virtual List<T> MakeEntityFromDataTable<T>(...)]

 

C#
/// <summary>
/// Creates the list of entities from the specified DataTable object. We do this because we <br/>
/// have two methods that both need to do the same thing.
/// </summary>
/// <typeparam name="T">The entity type represented by the collection</typeparam>
/// <param name="data">The entity collection</param>
/// <returns>The instantiated and populated list of entities.</returns>
/// <exception cref="ArgumentNullException">The data parameter cannot be null.</exception>
protected virtual List<T> MakeEntityFromDataTable<T>(DataTable data)
{
    if (data == null)
    {
        throw new ArgumentNullException("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            = DAL.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;
}

Method - [protected virtual DataTable MakeDataTable<T>(...)]

This method creates a DataTable object from the specified collection. Columns are automatically created, and rows are added based on the definition of the collection entity. This method is called from most of the previously discussed methods that are responsible for putting data INTO the database.

C#
/// <summary>
/// Creates a DataTable and populates it with the specified data collection.
/// </summary>
/// <typeparam name="T">The entity type represented by the collection</typeparam>
/// <param name="data">The collection of entties</param>
/// <param name="tableName">The name of the table to insert the data into</param>
/// <param name="bulkType">How to populate the columns from the entity</param>
/// <returns>The created/populated DataTable object.</returns>
protected virtual DataTable MakeDataTable<T>(IEnumerable<T> data, 
                                                string tableName, 
                                                BulkInsertType bulkType, 
                                                ParamPrecedence precedence = ParamPrecedence.None, 
                                                string paramArrayPropName = "SqlParameters")
{
    DataTable dataTable = null;

    Debug.WriteLine(string.Format("MakeDataTable - {0} data items specified.", data.Count()));

    using (dataTable = new DataTable(){TableName = tableName })
    {
        Type type = typeof(T);

        // Get the properties to send to the database. If byDBInsertAttribute is true, only object 
        // properties that are decorated with the CanDBInsert attribute will be retrieved. If 
        // byDBInsertAttribute is false, only properties that have a Set method will be retrieved
        PropertyInfo[] properties = DAL.GetEntityProperties(type, bulkType);

        Debug.WriteLine(string.Format("MakeDataTable - {0} item properties per item.", properties.Length));

        // create columns that match the retrieved properties
        foreach (PropertyInfo property in properties)
        {
            dataTable.Columns.Add(new DataColumn(property.Name, property.PropertyType));
        }

        Debug.WriteLine(string.Format("MakeDataTable - {0} dataTable columns created.", dataTable.Columns.Count));

        // add the rows to the datatable
        foreach (T entity in data)
        {
            DataRow row = dataTable.NewRow();
            foreach (PropertyInfo property in properties)
            {
                row[property.Name] = property.GetValue(entity);
            }
            dataTable.Rows.Add(row);
        }
    }

    Debug.WriteLine(string.Format("MakeDataTable - {0} rows created.", dataTable.Rows.Count));

    return dataTable;
}

The TestHarness Application

What would a library be without a test vehicle of some description? In this solution, the TestHarness is a console application that allows you to play with the DAL object without having to put it into your own code until you're comfortable with doing so. As presented, the app illustrates the following aspects of my insanity.

Use of Singletons

While using the original PWConnectionString class, one of the programmers hinted that it would be nice if we could create a globally available instance. I suggested that he create a static Globals class to do just that, but after thinking about it for a bit, I figured I'd provide a thread-safe singleton container class that does pretty much the same thing without having to create the previously mentioned static Global class.

This singleton container simply allows the programmer to instantiate the class once, and use that instance throughout the application. For those not familiar with the singleton pattern I found a decent explanation of singletons here - Implementing the Singleton Pattern in C# [^].

The [Singleton<T>] Base Class

This is my first real experience with the singleton pattern, so, much of the stuff I've done is completely unnecessary in order to "make it go", but I like to play around with new stuff that I'm learning. In the interest of experimentation, I decided to see if I could abstract away what little nastiness is involved in the implementation of a singleton into a base class that can then be inherited in the outward-facing code. In my search for an example of what I wanted to do, I found this CodeProject article - A Reusable Base Class for the Singleton Pattern in C# [^], by Boris Brock.

Singleton object classes should be sealed to prevent inheritance, but in this case, we WANT to inherit it, but at the same time, we don't want it to be instantiated itself, so instead of using sealed, we make the class abstract. Furthermore, a singleton class has a private constructor, but once again, we need to make the constructor it accessible so we can inherit it, so we change the constructor's accessor protected>. After we've done that, we can use generic types to make it digestible by any deriving class.

C#
/// <summary>
/// Represents the singleton base class.
/// </summary>
public abstract class Singleton<T> where T : class
{
    // The class is abstract so you can't instatiate it. Classes that derive 
    // from this class should be sealed.

    // .Net 4+ Lazy object
    private static readonly Lazy<T> lazy = new Lazy<T>(()=>Create());

    public static T Instance { get { return lazy.Value; } }

    // Protected constructor makes it possible to inherit this class
    protected Singleton()
    {
        Debug.WriteLine(string.Format("{0} instantiated.", typeof(T).GetType().Name));
    }

    private static T Create()
    {
        return Activator.CreateInstance(typeof(T), true) as T;
    }
}

The [PWConnectionStringleton] Class

Get it? "Stringleton"? Sounds like "Singleton"? I make myself laugh out loud!

I treat my singletons as containers for the actual instantiated objects. This segregates the singleton pattern itself from the instantiated object, thus making the code more flexible (to use, or NOT use singletons) with little/no code duplication.

As you can see, the derived singleton class looks just like a regular class. Since a singleton object's construct cannot accept parameters, we have to include a mechanism that allows us to initialize the contained object, namely, the PWConnectionString object.

C#
/// <summary>
/// Represents the singleton container the PWConnectString class.
/// </summary>
public sealed partial class PWConnectionStringleton:Singleton<PWConnectionStringleton>
{
    public PWConnectionString PWConnString { get; set; }

    public void Init(string name, string server, string database, bool encryptTraffic=false, string uid="", string pwd="")
    {
        this.PWConnString =  new PWConnectionString(name, server, database, encryptTraffic, uid, pwd);
    }
}

The [BLLSingleton] Class

The [BLL] Class

The DAL is intended to be inherited by your BLL object. If you have any experience at all with .Net and object-oriented development, you'll understand how all that works. Since most of the methods and properties are protected, they can only be used from the DAL itself, or from classes that inherit from it. The reason you want to simply inherit the DAL is so that you can extend it with your own methods, provide public methods for use by external code, and to avoid polluting the DAL with code that you add.

Inheriting the [DAL] Object

Below, you'll see how it's done. For the purposes of testing in this application, we prevent the DAL from actually reading from or writing to the database by setting DAL.CanReadWriteDB property to false. This will allow us to test various aspects of the DAL without executing stored procedures or queries.

C#
public partial class BLL : DAL
{
    public BLL(PWConnectionString connStr) : base(connStr)
    {
        // for the purposes of testing
        this.CanReadWriteDB = false;
    }

    //... more code that you write...
}

An example of a method you might write is the act of retrieving data and storing it into a collection of entities that you've defined.

C#
// get some data
public class BLL:DAL
{
    ...

   public List<MyObjects> GetSalesData()
   {
       List<MyObjects> list = this.ExecuteStoredProc("dbo.GetSalesData", null);
       return list;
   }

   ...
}

// calling it would look like this
List<MyObjects> list = BLL.GetSalesData();

...or a method to save data to the database (remember, there are several overloads to this method that make this possible):

C#
// save some data
public class BLL:DAL
{
    ...

    public int SaveSalesData(MyObject model)
    {
        int recordsAffected = this.ExecuteStoredProc(model);
        return recordsAffected;
    }
    public int SaveSalesDataCollection(List<MyObject> model)
    {
        int recordsAffected = this.ExecuteStoredProc(model.AsEnumerable());
        return recordsAffected;
    }

    ...
}

//---------------------------------------------------
// given the following (this code goes in the object that 
// is calling the BLL methods)
List<MyObject> myObjects = new List<MyObject>(){new MyObject(),new MyObject(),...};
// calling it would look like this
int result = BLL.SaveSalesData(myObject[0]);
// or
result = BLL.SaveSalesDataCollection(myObjects);

And let's not forget the ability to perform bulk inserts:

C#
public class BLL:DAL
{
    ...

    public int BulkInsertSalesData(List<MyObject> model)
    {
        int recordsAffected = this.ExecuteBulkInsert(model, 
                                                     "sales", 
                                                     BulkInsertType.DBInsertAttribute);
        return recordsAffected;
    }

    public int UpsertSalesData(List<MyObject> model)
    {
        int recordsAffected = this.ExecuteStoredProc(model.AsEnumerable(), 
                                                     "storedprocname", 
                                                     BulkInsertType.DBInsertAttribute);
        return recordsAffected;
    }

    ...
}

//---------------------------------------------------
// given the following (this code goes in the object that 
// is calling the BLL methods)
List<MyObject> myObjects = new List<MyObject>(){new MyObject(),new MyObject(),...};
// calling it would look like this
int result = BLL.BulkInsertSalesData(myObjects);
// or
result = BLL.UpsertSalesData(myObjects);

I personally believe that the closer you get to the outward-facing implementation, the simpler the interface should be. You can see how that works in the code samples above.

The [Program] Class

This is where the magic happens. We instantiate the singleton ConnectionString and BLL objects, checks the contents of the value of the string, and proceeds to exercise the test methods. Notice the compiler directive that turns the use of singletons on and off. Keep in mind that if you turn singles off, you have to do it in the MyClass object as well.

C#
//#undef __USE_SINGLETONS__
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using PaddedwallDAL;
using ObjectExtensions;

namespace TestHarness
{
    // This project is only intended to demonstrate the use of the connection string and DAL 
    // classes as either instantiated on-demand objects, or as globally accessible singleton 
    // objects. Do NOT add this project to your own projects.
    class Program
    {
        static PWConnectionString PWConnString 
        { 
#if __USE_SINGLETONS__ 
            get { return PWConnectionStringleton.Instance.PWConnString; } 
#else
            get; set;
#endif
        }

        static BLL BLL
        {
#if __USE_SINGLETONS__
            get { return BLLSingleton.Instance.BLL; }
#else
            get; set;
#endif
        }

        static void Main(string[] args)
        {
            try
            {
                // this lets us write to the console
                Global.IsConsoleApp = true;

#if __USE_SINGLETONS__
                // if you want to use singletons (desktop apps only?) for your connection string 
                // and/or BLL, do this. We use the "master" database so you don't have to create 
                // a database when first exploring the code.
                PWConnectionStringleton.Instance.Init("Test", "localhost", "master");
                BLLSingleton.Instance.Init(PWConnectionStringleton.Instance.PWConnString);

                // see what the connection string contains
                Global.WriteLine(string.Format("ConnectionString (base64) = {0}", PWConnString.ConnectionString));
                Global.WriteLine(string.Format("ConnectionString (ascii)  = {0}", PWConnString.ConnectionString.Base64Decode()));

                // assuming you set up properties to access the BLL object (as demonstrated in this 
                // app), you can use this throughout the application:
                BLL.MyMethod();

                // or sinmply do this (but I think this is uglier and prone to typing mistakes):
                BLLSingleton.Instance.BLL.MyMethod();

                // testing access to the BLL singleton from another class. Put a breakpoint in 
                // this class constructr, and inspect the BLL object.
                MyClass myClass = new MyClass();

#else
                // we use a guid for the name to prove it's a uniquely created instance 
                PWConnString = new PWConnectionString(Guid.NewGuid().ToString(), "localhost", "Test");
                BLL = new BLL(PWConnString);
#endif

                // testing the property/parameter code - generates a crapload of console output
                BLL.TestEntity1();
                BLL.TestEntity2();
                BLL.TestEntity3();
            }
            catch (Exception ex)
            {
                Global.WriteLine(string.Format("{0}{1}{2}", ex.Message, Environment.NewLine, ex.StackTrace));
            }
            // gives you a chance to see the console output before dismissing the console window.
            Console.WriteLine();
            Console.WriteLine("Press a key...");
            Console.ReadKey();
        }
    }
}

Other Notes

It wasn't necessary to instantiate the connection string or BLL objects as singletons, but I figured the code needed to be tested, so here we are. For those of you wanted to see the difference between using a singleton, and NOT using a singleton, I created a compiler definition called __USE_SINGLETONS__ in the project properties, which automatically tells the code to use singletons, but allows the programmer to switch between the two paradigms by simply undefining the compiler definition (see the top of the program.cs file).

Closing Comments

I've tried to make using ADO as generic as possible because I gotta backup my general dislike of ORM's. Like my dad always told me, "If you're gonna talk the talk, make sure you can walk the walk."

If this article violates your warped sense of "best practice", well, that's on you. However, don't feel like you can't offer up where you think I went wrong, and how society as a whole is much the less for it, or cetrain protected classes of individuals are likely to fall into a fiery pit.

Breaking Change - A .Net Core Conversion Version

Let me start off by saying this was done ONLY out of curiosity, and I am in no way advocating the use of .Net Core. In point of fact, after my brief exposure to VS2019 and the whole Core paradigm, I ain't impressed. That being said, here we go...

First Things First

NOTE: If you don't want to convert it yourself, I provided a new download for this version (at the top of this article).

This code uses .Net Core 3.1 (which is, as of this writing, in "pre-release" - make of that what you will), and was created in VS2019 (I don't know if Core 3.1 is available for VS2017). Keep in mind that beyond a few using statement changes, NONE of the actual class code had to change to support Core. So if you're ready, here's what I did:

  • Create a new .Core class library project, called PaddedwallDalCore, and delete the default Class1.cs file.
     
  • Add a new .Core console application to the project, called TestHarness, and set it as the "startup project".
     
  • Add a reference to the class library project in the console app project.
     
  • Use NuGet to add Microsoft.Data.SqlClient to the solution
     
  • Copy the following folders/files into the approriate solution projects from the original PaddedwallDAL solution. For those that didn't notice, VS2019 doesn't require you to show hidden files and "include in project" on the folders/files that you just copied. Once copied, they are automatically added to the project (and you can still choose to exclude them like you could in past versions of Visual Studio).
     
  • In the entire solution, "Replace in Files" all instances of using System.Data.SqlClient to Microsoft.Data.SqlClient.
     

Unless I forgot a step, all of the above takes about 15 minutes. When you compile/run it, it acts just like the .Net Framework version.

Comments about ADO in .Net Core

IMHO, it seems kinda half-assed. Instead of bringing it ALL into the Core Microsoft namespace, they require you to keep using System.Data. Not only that, Core's ADO support is STILL in the pre-release phase, and they're changing - or worse, deleting - stuff from Core (without telling anyone) . IMHO, this does NOT exactly promote its rapid adoption in enterprise-level code, nor advocating for its use.

Lastly, I often hear the term "cross-platform" associated with Core. On Linux/iOS, without Mono, your Core stuff WILL NOT RUN. That makes it just like .Net Framework, and thus, NOT cross-platform in the truest sense of the term.

So, if you wanna do Core, more power to you, I suppose, but I won't be until it's STABLE - and been stable for at least a year. Not a fan...

Points of Interest

If you want to see an example of how to extend this code, go to this article - Generic DAL Revisit - Making it Your Own, A Practical Example [^].

Why are they releasing dangerous felons because of the corona virus, but arresting people for violating the stay-at home orders? Things that make you go "hmmmm."

History

  • 2020.03.06 Added Core 3.1 version.
  • 2020.03.05 Initial release.
     

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

 
GeneralMy vote of 5 Pin
Anurag Gandhi8-May-20 16:45
professionalAnurag Gandhi8-May-20 16:45 
GeneralMy vote of 5 Pin
r_hyde6-Apr-20 15:39
Memberr_hyde6-Apr-20 15:39 
GeneralRe: My vote of 5 Pin
#realJSOP8-Apr-20 5:21
mva#realJSOP8-Apr-20 5:21 
GeneralRe: My vote of 5 Pin
pkfox8-Apr-20 11:44
professionalpkfox8-Apr-20 11:44 
GeneralRe: My vote of 5 Pin
#realJSOP8-Apr-20 12:08
mva#realJSOP8-Apr-20 12:08 
QuestionObviously Understands Real-World, Get-It-Done Programming Pin
Allen Goodwin6-Apr-20 18:58
MemberAllen Goodwin6-Apr-20 18:58 
QuestionpType.GetDefaultValue(); Pin
pkfox6-Apr-20 9:25
professionalpkfox6-Apr-20 9:25 
AnswerRe: pType.GetDefaultValue(); Pin
#realJSOP6-Apr-20 9:42
mva#realJSOP6-Apr-20 9:42 
GeneralRe: pType.GetDefaultValue(); Pin
pkfox6-Apr-20 11:28
professionalpkfox6-Apr-20 11:28 
GeneralRe: pType.GetDefaultValue(); Pin
#realJSOP6-Apr-20 11:45
mva#realJSOP6-Apr-20 11:45 
GeneralRe: pType.GetDefaultValue(); Pin
pkfox6-Apr-20 19:29
professionalpkfox6-Apr-20 19:29 
GeneralRe: pType.GetDefaultValue(); Pin
pkfox6-Apr-20 20:52
professionalpkfox6-Apr-20 20:52 
GeneralRe: pType.GetDefaultValue(); Pin
#realJSOP6-Apr-20 21:23
mva#realJSOP6-Apr-20 21:23 
GeneralRe: pType.GetDefaultValue(); Pin
pkfox6-Apr-20 23:45
professionalpkfox6-Apr-20 23:45 
GeneralRe: pType.GetDefaultValue(); Pin
#realJSOP7-Apr-20 1:56
mva#realJSOP7-Apr-20 1:56 
GeneralRe: pType.GetDefaultValue(); Pin
pkfox7-Apr-20 2:36
professionalpkfox7-Apr-20 2:36 
GeneralRe: pType.GetDefaultValue(); Pin
#realJSOP7-Apr-20 6:02
mva#realJSOP7-Apr-20 6:02 
GeneralRe: pType.GetDefaultValue(); Pin
#realJSOP8-Apr-20 3:43
mva#realJSOP8-Apr-20 3:43 
GeneralRe: pType.GetDefaultValue(); Pin
pkfox8-Apr-20 9:43
professionalpkfox8-Apr-20 9:43 
GeneralRe: pType.GetDefaultValue(); Pin
#realJSOP8-Apr-20 9:58
mva#realJSOP8-Apr-20 9:58 
GeneralRe: pType.GetDefaultValue(); Pin
pkfox8-Apr-20 10:41
professionalpkfox8-Apr-20 10:41 
GeneralRe: pType.GetDefaultValue(); Pin
#realJSOP8-Apr-20 10:52
mva#realJSOP8-Apr-20 10:52 
GeneralRe: pType.GetDefaultValue(); Pin
pkfox8-Apr-20 11:04
professionalpkfox8-Apr-20 11:04 
GeneralRe: pType.GetDefaultValue(); Pin
#realJSOP8-Apr-20 12:06
mva#realJSOP8-Apr-20 12:06 
GeneralRe: pType.GetDefaultValue(); Pin
pkfox8-Apr-20 21:10
professionalpkfox8-Apr-20 21:10 

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.