.net_2.0 : Building SQL Queries Automatically in C# using Generics

News

Some articles may have clipped text, as I just upgraded the Community Server product used to host this site. In addition, finally figured out how to change the blog skins which resulted in the afore-mentioned issue.

Navigation

2.0 Related

Archives

Building SQL Queries Automatically in C# using Generics

I have been looking for methods to get rid of redundant code throughout my Sites-Easy Project. The biggest issue I have is the redundancy of all of the DAL methods. For instance I have the ability to add a Section that contains 40 some parameters, but also create methods to only update subsets of the Section Data. While I can declare Parameters as static strings to reduce some of the overhead, it is still a tedious process to assign the value to the SQLParameter parameters I define, as each method still has to be defined in the DAL. On top of that - I have opted not to use SQL Stored Procedures as I'm at the stage where I am de-normalizing the tables to maximize manageability and introduce optional features.

Basic Considerations:

To build a Stored Procedure in C# we need to consider the following Basic requirements:

1. What type of action are we doing: UPDATE, INSERT, DELETE etc...

2. What SqlParameter types are we passing: Int, NTEXT, NVARCHAR etc...

3. What are the column names in the table that we have to associate the SqlParameter types to for the action.

4. Defining the where clause.

Advanced Considerations:

1. Requiring additional SQL Exec to look up values before or after doing an insert.

2. Multi-table SqlCommands.

3. Wrapping up all the queries in a transaction(s) to handle failures.

Before .NET 2.0 - there simply was no way to actually create a DAL that literally would just build a stored procedure in C# with little or no actual code without dealing with a bunch of arrays, casting values and the likes. Usually when using a SQLHelper class we define manually the SQLParameter types and reference them in individual DAL methods. .Net 2.0 introduces Generics which provides strongly typed Collections that actually works around the need to cast objects back and forth, and allows collections that actually can contain varying data types. Thus using Generics provides a great deal of flexibility in terms of assigning static SqlParameter types with various SqlDataTypes. In a general scope this allows us to concentrate more on HOW we construct the SqlComandText based on our Basic Considerations.

To take it a step further we need to develop a global method that can associate the SqlParameter types with the Column Names of the table we are targeting, and build dynamically the UPDATE and INSERT - SQL syntax. Sure if you just have a UPDATE MYTABLE SET ID = @SectionID" then its not really a issue but what if we revisit the 40 some values we have to update - we need a better way to invoke the action.

[Typically, most data access helper classes use complex code to pull table schemas from the database and generate a array list of parameters. While handy - that level of abstraction still makes coding to the helper a bit more complex. In a development process it is better to know exactly what you are modifying and why, rather than coding to a helper class. Then we have performance considerations to ponder and there really is no need to pull schemas if we do not need them.]

Enter into the picture - Generics - specifically the DICTIONARY<> and LIST<> features. The Dictionary<> is important because we can assign a key value for searching as well as an object that we want to return if the key match is made. The List<> function is equally as important as we can build a collection of objects that might have different type values.

For instance we may want to return:

public List<ParmInfo2> MATCH_SQL_PARM(string KEY, bool Value)

or

public List<ParmInfo2> MATCH_SQL_PARM(string KEY, string Value)

or

public List<ParmInfo2> MATCH_SQL_PARM(string KEY, int Value)

Blurring the line between the BLL and DAL.

Unfortunately, the steps we will need to take will blur the BLL and DAL distinction a smidgen - as we will need to pass the ILIST<> from the BLL to the DAL. However, we will try to reduce the cross-over as much as we possibly can as at the end of the day the goal is to have an single DAL servicing a very complex project. You know what I mean as well if you program web applications that consists of dozens of projects yet the projects themselves share 90% of the common variables of the core application where each project has its own DAL for independence and code separation (typical when writing modules for an application).

First we need to define a collection class that contains the parameters and dictionaries:

namespace Cavalia
{
    public abstract class PARMBUILDER
    {
        /// <summary>
        /// Match SectionID and CommunityID
        /// </summary>
        public static string WHERE_PARM_1 = " WHERE section_ID = @sectionID and @communityID = section_communityID";

        public static ParmInfo2 MATCH_SQL_PARM(string KEY, int Value)
        {

            Dictionary<string, PARMTEMP> SQLPARMS = new Dictionary<string, PARMTEMP>();

            //SQLPARMS.Add("sectionID","@sectionID", PARM_SECTIONID);
            SQLPARMS.Add("sectionID", new PARMTEMP("section_sectionID", new SqlParameter("@sectionID", SqlDbType.Int)));
            SQLPARMS.Add("communityID", new PARMTEMP("section_communityID", new SqlParameter("@communityID", SqlDbType.Int)));
            //SQLPARMS.Add("sectionID", ParmInfo2("sectionID", new SqlParameter("@sectionID", SqlDbType.Int)));
            SQLPARMS.Add("contentID", new PARMTEMP("section_contentID", new SqlParameter("@contentID", SqlDbType.Int)));
            SQLPARMS.Add("pageType", new PARMTEMP("section_pageType", new SqlParameter("@pageType", SqlDbType.Int)));
            SQLPARMS.Add("webBoxDisplayMode", new PARMTEMP("section_webBoxDisplayMode", new SqlParameter("@webBoxDisplayMode", SqlDbType.Int)));
            SQLPARMS.Add("webServiceBoxDisplayMode", new PARMTEMP("section_webServiceBoxDisplayMode", new SqlParameter("@webServiceBoxDisplayMode", SqlDbType.Int)));
            SQLPARMS.Add("parentSectionID", new PARMTEMP("section_parentSectionID", new SqlParameter("@parentSectionID", SqlDbType.Int)));
            SQLPARMS.Add("recordsPerPage", new PARMTEMP("section_recordsPerPage", new SqlParameter("@recordsPerPage", SqlDbType.Int)));

            if (SQLPARMS.ContainsKey(KEY))
            {
                PARMTEMP TEMP = (SQLPARMS[KEY]);
                ParmInfo2 RETVAL = new ParmInfo2(TEMP.SQLCOLUMN, TEMP.SQLPARM, Value);

                return RETVAL;
            }
            else return null;
            //Content Page Parameters

        }
        public static ParmInfo2 MATCH_SQL_PARM(string KEY, string Value)
        {

            Dictionary<string, PARMTEMP> SQLPARMS = new Dictionary<string, PARMTEMP>();
            SQLPARMS.Add("pageSkin", new PARMTEMP("section_pageSkin", new SqlParameter("@pageSkin", SqlDbType.NVarChar, 100)));
            SQLPARMS.Add("failOverPageSkin", new PARMTEMP("section_failOverPageSkin", new SqlParameter("@failOverPageSkin", SqlDbType.NVarChar, 100)));
            SQLPARMS.Add("SectionWebServicePassword", new PARMTEMP("section_SectionWebServicePassword", new SqlParameter("@SectionWebServicePassword", SqlDbType.NVarChar, 50)));
            SQLPARMS.Add("transformations", new PARMTEMP("section_transformations", new SqlParameter("@transformations", SqlDbType.NText)));
            SQLPARMS.Add("logo", new PARMTEMP("section_logo", new SqlParameter("@logo", SqlDbType.NVarChar, 50)));
            SQLPARMS.Add("pageHeader", new PARMTEMP("section_pageHeader", new SqlParameter("@pageHeader", SqlDbType.NText)));
            SQLPARMS.Add("pageFooter", new PARMTEMP("section_pageFooter", new SqlParameter("@pageFooter", SqlDbType.NText)));
            SQLPARMS.Add("footer", new PARMTEMP("section_footer", new SqlParameter("@footer", SqlDbType.NVarChar, 250)));
            SQLPARMS.Add("pageMetaKeys", new PARMTEMP("section_pageMetaKeys", new SqlParameter("@pageMetaKeys", SqlDbType.NVarChar, 250)));
            SQLPARMS.Add("pageMetaDesc", new PARMTEMP("section_pageMetaDesc", new SqlParameter("@pageMetaDesc", SqlDbType.NVarChar, 250)));
            SQLPARMS.Add("name", new PARMTEMP("section_name", new SqlParameter("@name", SqlDbType.NVarChar, 50)));
            SQLPARMS.Add("title", new PARMTEMP("section_title", new SqlParameter("@title", SqlDbType.NVarChar, 100)));
            SQLPARMS.Add("menuTitle", new PARMTEMP("section_menuTitle", new SqlParameter("@menuTitle", SqlDbType.NVarChar, 50)));
            SQLPARMS.Add("description", new PARMTEMP("section_description", new SqlParameter("@description", SqlDbType.NVarChar, 500)));
            SQLPARMS.Add("pageStyle", new PARMTEMP("pageStyle", new SqlParameter("@pageStyle", SqlDbType.NVarChar, 100)));


            if (SQLPARMS.ContainsKey(KEY))
            {
                PARMTEMP TEMP = (SQLPARMS[KEY]);
                ParmInfo2 RETVAL = new ParmInfo2(TEMP.SQLCOLUMN, TEMP.SQLPARM, Value);

                return RETVAL;
            }
            else return null;

        }
        public static ParmInfo2 MATCH_SQL_PARM(string KEY, bool Value)
        {
            Dictionary<string, PARMTEMP> SQLPARMS = new Dictionary<string, PARMTEMP>();
            SQLPARMS.Add("enableTopics", new PARMTEMP("section_enableTopics", new SqlParameter("@enableTopics", SqlDbType.Bit)));
            SQLPARMS.Add("enableComments", new PARMTEMP("section_enableComments", new SqlParameter("@enableComments", SqlDbType.Bit)));
            SQLPARMS.Add("enableModeration", new PARMTEMP("section_enableModeration", new SqlParameter("@enableModeration", SqlDbType.Bit)));
            SQLPARMS.Add("enableRatings", new PARMTEMP("section_enableRatings", new SqlParameter("@enableRatings", SqlDbType.Bit)));
            SQLPARMS.Add("enableCommentRatings", new PARMTEMP("section_enableCommentRatings", new SqlParameter("@enableCommentRatings", SqlDbType.Bit)));
            SQLPARMS.Add("enableNotifications", new PARMTEMP("section_enableNotifications", new SqlParameter("@enableNotifications", SqlDbType.Bit)));
            SQLPARMS.Add("inheritTransformations", new PARMTEMP("section_inheritTransformations", new SqlParameter("@inheritTransformations", SqlDbType.Bit)));
            SQLPARMS.Add("isWebBoxesInherited", new PARMTEMP("section_isWebBoxesInherited", new SqlParameter("@isWebBoxesInherited", SqlDbType.Bit)));
            SQLPARMS.Add("isSectionWebService", new PARMTEMP("section_isSectionWebService", new SqlParameter("@isSectionWebService", SqlDbType.Bit)));
            SQLPARMS.Add("allowHtmlInput", new PARMTEMP("section_allowHtmlInput", new SqlParameter("@allowHtmlInput", SqlDbType.Int)));
            SQLPARMS.Add("allowCommentHtmlInput", new PARMTEMP("section_allowCommentHtmlInput", new SqlParameter("@allowCommentHtmlInput", SqlDbType.Int)));
            SQLPARMS.Add("isSystem", new PARMTEMP("section_isSystem", new SqlParameter("@isSystem", SqlDbType.Bit)));
            SQLPARMS.Add("isWebServiceBoxesInherited", new PARMTEMP("section_isWebServiceBoxesInherited", new SqlParameter("@isWebServiceBoxesInherited", SqlDbType.Bit)));
            SQLPARMS.Add("isEnabled", new PARMTEMP("section_isEnabled", new SqlParameter("@isEnabled", SqlDbType.Bit)));

            if (SQLPARMS.ContainsKey(KEY))
            {
                PARMTEMP TEMP = (SQLPARMS[KEY]);
                ParmInfo2 RETVAL = new ParmInfo2(TEMP.SQLCOLUMN, TEMP.SQLPARM, Value);

                return RETVAL;
            }
            else return null;
        }

    }
    /// <summary>
    /// This is used to simply populate the correct SQLCOLUMN and SQLPARAMETER.
    /// It gets assigned on a Dictionary KEY Match which is then used to generate
    /// the actual list item that gets passed back.
    /// </summary>
    public class PARMTEMP
    {
        public string SQLCOLUMN;
        public SqlParameter SQLPARM;

        public PARMTEMP(string _SQLCOLUMN, SqlParameter _SQLPARM)
        {
            this.SQLCOLUMN = _SQLCOLUMN;
            this.SQLPARM = _SQLPARM;

        }
    }
    public class ParmInfo2
    {
        public string SQLCOLUMN;
        public SqlParameter SQLPARM;

        public ParmInfo2(string _SQLCOLUMN, SqlParameter _SQLPARM, string _VALUE)
        {

            this.SQLCOLUMN = _SQLCOLUMN;
            this.SQLPARM = _SQLPARM;
            this.SQLPARM.Value = _VALUE;

        }
        public ParmInfo2(string _SQLCOLUMN, SqlParameter _SQLPARM, int _VALUE)
        {

            this.SQLCOLUMN = _SQLCOLUMN;
            this.SQLPARM = _SQLPARM;
            this.SQLPARM.Value = _VALUE;

        }
        public ParmInfo2(string _SQLCOLUMN, SqlParameter _SQLPARM, bool _VALUE)
        {

            this.SQLCOLUMN = _SQLCOLUMN;
            this.SQLPARM = _SQLPARM;
            this.SQLPARM.Value = _VALUE;

        }
    }

}

What we accomplish above is allowing for searching for keys that match a specific data type such as bool, int, and string. Using the Dictionary feature - finding matches is seamless. The returned values is merely the name of the SQL column and the SqlParameter with the value already inserted. This could be modified in many ways - such as to allow custom NVARCHAR lengths etc... however, in my particular case - since I am building the stored procedures via code I wanted a list of available and known sql parameters statically defined. This helps reduce the amount of coding required and you'll see why later...

Why not use cached parameters? Because, we are not fetching this data and instead hard coding it instead, honestly - I doubt much would be gained from using a cache in this instance. Additionally, for large projects there maybe huge dictionaries which could cause issues with the cache always be flushed out and still would require to be be rebuilt. BUt - it could be done, I just tend to only throw things in the cache when a remote call to the database or some other system is required. The code is already in memory after all - and yes, some perf gain is probable and likely - I just do not think it would be greater than 2-5% at maximum load handling capacity.

Why not use XML documents? Could be done - but I am not a big fan of using XML unless it is specifically for a inter-op scenario. While XML can be cached, performance is fast - its still not faster than the method above.

Next we need a DAL method that builds the stored procedure:

/// <summary>
        /// The ExecuteSQL is a dynamic builder for Update / Inserts and Deletions
        /// It automatically generates the parameters and populates the values
        /// thus reducing code bloat significantly.
        /// </summary>
        /// <param name="CMDTEXT"></param>
        /// <param name="PARMS">Generic List<> that contains the ParmInfo</param>
        /// <param name="CMDTYPE">Allowed Values: Update, Insert, Delete</param>

        public void ExecuteSQL(string CMDTABLE, List<ParmInfo2> PARMS, string CMDTYPE, List<ParmInfo2> WHERE_PARMS,string CMDWHERE)
        {
            // init variables required
            CMDTYPE = CMDTYPE.ToUpper();
            //Needed to seperate the Columns vs Parameters
            string INSERT_VALUES_VAR = " VALUES ";
            //This is the actual SP that will be sent
            string SQL_TEXT = string.Empty;
            if (CMDTYPE == "INSERT")
            {
                SQL_TEXT = "INSERT INTO " + CMDTABLE;
            }
            if (CMDTYPE == "UPDATE")
            {
                SQL_TEXT = "UPDATE " + CMDTABLE + " SET ";
            }
            if (CMDTYPE == "DELETE")
            {
                SQL_TEXT = "DELETE FROM " + CMDTABLE;
            }

            //Start
            SqlConnection conn = new SqlConnection(CommunityGlobals.ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            //The Attempt here is to roll a handler for generating INSERT,UPDATE, SELECT and DELETE
            int i = 1;
            Trace.Write("Start - count i = " + i + " and ParmCount is " + PARMS.Count);

            foreach (ParmInfo2 item in PARMS)
            {
                Trace.Write("Top Loop - count i = " + i + " and ParmCount is " + PARMS.Count);
                if (item != null)
                {
                    if (CMDTYPE == "UPDATE")
                    {
                        //String Building

                        SQL_TEXT = SQL_TEXT + " " + item.SQLCOLUMN + " = " + item.SQLPARM.ParameterName;

                        if (i < PARMS.Count)
                        {
                            //Add commas
                            SQL_TEXT = (SQL_TEXT + ", ");
                        }

                    }
                    if (CMDTYPE == "INSERT")
                    {
                        SQL_TEXT = SQL_TEXT + " " + item.SQLCOLUMN;

                        INSERT_VALUES_VAR = INSERT_VALUES_VAR + " " + item.SQLPARM.ParameterName + " ";
                        if (i < PARMS.Count)
                        {
                            SQL_TEXT = SQL_TEXT + ", ";
                            INSERT_VALUES_VAR = INSERT_VALUES_VAR + ", ";

                        }
                        //Add the Parameter to the list...as it doesn't matter when we do it..
                        //so may as well do it while in the loop.

                    }
                    cmd.Parameters.Add(item.SQLPARM);
                }
                else
                {
                    Trace.Write("SQL Column was null - count i = " + i + " and ParmCount is " + PARMS.Count);
                }
                i++;
            }
            //If Insert we need to combine the INSERT_VALUES_VAR with SQLTEXT
            if (CMDTYPE == "INSERT")
            {
                SQL_TEXT = SQL_TEXT + INSERT_VALUES_VAR;
            }
            //Now we add the Where clause and orderby clauses if necessary.
            //Skip if Insert command as will cause errors
            if (CMDTYPE != "INSERT")
            {
                foreach (ParmInfo2 item in WHERE_PARMS)
                {
                    //Check to see if the Parm has been inialized previously
                    //as we do not want duplicate entries...
                    if (!PARMS.Contains(item))
                    {
                        cmd.Parameters.Add(item.SQLPARM);
                    };

                }
                SQL_TEXT = SQL_TEXT + CMDWHERE;
            }
            //now wrap the request into a transaction so we can rollback
            //the failure and notify calling function when failure...
            cmd.CommandText = SQL_TEXT;
            SqlTransaction tran = null;
            try
            {
                conn.Open();
                tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception ex)
            {

                tran.Rollback();
                bllLogging.RecordError("Could not save Basic Options: ", ex, Severity.High, "Dal", "none");
            }

            finally
            {
                conn.Close();
            }
            return;

}

As you can see from the code above - we do not statically define any parameters. Instead we require that the parameters be passed in as part of the method call. Additionally, we define variables for the where clause, command type we want to execute, and naturally the table to make the changes on.

Note: Two seperate ILIST<> are used. When we pass the where clause and associated parameters - we do a search on the primary ILIST<> to make sure we do not add duplicate SqlParameters (Which would result in a SQL Exception).

An example of the UPDATE function properly executing is this (From SQL Profiler):

exec sp_executesql N'UPDATE admin_Data SET section_name = @name, section_title = @title, section_menuTitle = @menuTitle, section_description =
@description, section_isEnabled = @isEnabled WHERE section_ID = @sectionID and @communityID = section_communityID',N'@name nvarchar(50),@title
nvarchar(100),@menuTitle nvarchar(50),@description nvarchar(500),@isEnabled bit,@sectionID int,@communityID int',@name=N'EditSections',@title=N'Edit
Sections',@menuTitle=N'Edit Sections',@description=N'Allows for editing Section Content',@isEnabled=1,@sectionID=7,@communityID=1

The result above is very performant when under load. SQL can actually cache this properly and the cached execution plan will be streamlined and fast. While slightly more 'bulky' in terms of what is passed across the network - typically most web servers would have a separate NIC attached to a separate subnet that the DB is on anyways. This is always the trade-off when rolling stored procedures in code versus using stored procedures. However, the difference in overall performance is negligible as evident from all the Benchmark studies which use code to generate the exec statements instead of using SQL stored procedures. Eventually - I will bring this into a lab and provide some actual stats to back that up.

This is the BLL calling method that passes in the variables into the DAL:

private void UpdateData()
        {
            //modified to work with the GlobalDAL Routine
            List<ParmInfo2> UPDATE_PARMS = new List<ParmInfo2>();
            UPDATE_PARMS.Add(PARMBUILDER.MATCH_SQL_PARM("name", txtName.Text));
            UPDATE_PARMS.Add(PARMBUILDER.MATCH_SQL_PARM("title", txtTitle.Text));
            UPDATE_PARMS.Add(PARMBUILDER.MATCH_SQL_PARM("menuTitle", txtMenuTitle.Text));
            UPDATE_PARMS.Add(PARMBUILDER.MATCH_SQL_PARM("description", txtDescription.Text));
            UPDATE_PARMS.Add(PARMBUILDER.MATCH_SQL_PARM("isEnabled", chkIsEnabled.Checked));
            List<ParmInfo2> WHERE_PARMS = new List<ParmInfo2>();
            WHERE_PARMS.Add(PARMBUILDER.MATCH_SQL_PARM("sectionID", currentID));
            WHERE_PARMS.Add(PARMBUILDER.MATCH_SQL_PARM("communityID", Section.CommunityID));
            dalGlobals.ExecuteSQL(modeID + "_Data", UPDATE_PARMS, "UPDATE", WHERE_PARMS, PARMBUILDER.WHERE_PARM_1);

While it appears that this is excessive in terms of code in the BLL layer, we save a ton coding of adding IDAL, and DAL method declarations and- we accomplish two things here.

1. Implementation of strongly typed collections which assists us in code developement (less errors before compiling).

2. The only code we have to touch once the dictionary is properly populated is in the BLL which we would have to do anyways honestly.

What truly makes this SQLHelper class different from most is that you no longer have to create a ton of parameters with the sql exec statements you want to execute. If you change a form and add new fields that need to be updated as well you only have to make sure they are included in the dictionary listings, and add the new entries to the BLL. Typically you would modify the BLL, the IDAL, and add a new method to the DAL in the conventional programming model.

This is a simple example on how to:

1. Use Generics overall.

2. Using the IDICTIONARY<>

3. Using the ILIST<>

4. Build dynamic statements.

Hope you get some worth from this example.

Posted: Saturday, November 25, 2006 5:52 PM by Jody

Filed under: c#, SQL, Generics

Comments

No Comments

New Comments to this post are disabled

.net_2.0

Recent Posts

Tags