Sunday, April 22, 2012

Helper Library: MSSQL QUERY Builder


      Many times we need to build a sql query using concating strings according to situation, quite often than not we go with the flow and create function each time to suit our current need. Then why not create a class that will be useful in most of this situation can further augmented to as new requirement pops in that can’t be fulfilled by our current class. Bellow is class that I build keeping in this mind I will like to share with you.

Code snipet


DBManager dbManager = new DBManager();
            dbManager.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
            try
            {
                dbManager.Open();
                AffectedRows = dbManager.ExecuteNonQuery(System.Data.CommandType.Text, Query.ToString());
                return (AffectedRows > 0) ? true : false;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Close();
            }
            return false;
 }
 in each of this method uses my data access layer that can be modified to suit your requirement.  

This class basically deals with SELECT; INSERT and UPDATE SQL statements modus operandi is to build a valid sql statement using input parameters
is either string or string array.


Here is My Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Conflux.DLL;
using System.Configuration;
using System.Data;

namespace Conflux.Helper
{
    public class MssqlQueryBuilder
    {
     public static bool Update(string TableName, string[] ColoumNames, string[] ColoumValues, 
                                              string[] WhereKey, string[] WhereValue)
        {
            string ExcludeQuotes=string.Empty;
            StringBuilder Query = new StringBuilder("update ");
            int AffectedRows = int.MinValue;
            if (ColoumNames.Length != ColoumValues.Length)
            {
                return false;
            }
            if (WhereKey.Length != WhereValue.Length)
            {
                return false;
            }
            Query.Append(TableName);
            Query.Append(" set ");
            for (int i = 0; i < ColoumNames.Length; i++)
            {
                Query.Append(ColoumNames[i]);
                Query.Append(" = ");
                Query.Append("'");
               
                ExcludeQuotes = ColoumValues[i];
                if (ColoumValues[i] != "")
                {
                    if ((ColoumValues[i].Substring(0, 1) == "'") && 
                      (ColoumValues[i].Substring(ColoumValues[i].Length - 1, 1) == "'"))
                    {
                        ExcludeQuotes = ColoumValues[i].Substring(1, ColoumValues[i].Length - 2);
                    }
                }
                Query.Append(ExcludeQuotes);
                Query.Append("'");
                if (i != ColoumNames.Length - 1)
                {
                    Query.Append(" , ");
                }

            }
            Query.Append(" where ");
           
             ExcludeQuotes = string.Empty;
            for (int i = 0; i < WhereKey.Length; i++)
            {
                Query.Append(WhereKey[i]);
                Query.Append(" = ");
                Query.Append("'");
               
                ExcludeQuotes = WhereValue[i];
                if (WhereValue[i] != "")
                {
                    if ((WhereValue[i].Substring(0, 1) == "'") && 
                          (WhereValue[i].Substring(WhereValue[i].Length - 1,1) == "'"))
                    {
                        ExcludeQuotes = WhereValue[i].Substring(1, WhereValue[i].Length - 2);
                    }
                }
                Query.Append(ExcludeQuotes);
                Query.Append("'");
                if (i != WhereKey.Length - 1)
                {
                    Query.Append(" and ");
                }
            }

            DBManager dbManager = new DBManager();
            dbManager.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
            try
            {
                dbManager.Open();
                AffectedRows = dbManager.ExecuteNonQuery(System.Data.CommandType.Text, Query.ToString());
                return (AffectedRows > 0) ? true : false;
            }
            catch (Exception ex)
            {

            }
            finally
            {
                dbManager.Close();
            }
            return false;
        }
     public static bool Update(string TableName, string[] ColumnNames, string[] ColumnValues, 
                                                  string[] WhereKey, string[] WhereValue, string[] SkipColumns)
     {
         int NewArrayLength = ColumnNames.Length - SkipColumns.Length;
         string[] NewColumnNamesArray = new string[NewArrayLength];
         string[] NewColumnValuesArray = new string[NewArrayLength];
         int l = 0;
         for (int i = 0; i < SkipColumns.Length; i++)
         {
             for (int j = 0; j < ColumnNames.Length; j++)
             {
                 if (SkipColumns[i] != ColumnNames[j])
                 {
                     try
                     {
                         NewColumnNamesArray[l] = ColumnNames[j];
                         NewColumnValuesArray[l] = ColumnValues[j];
                         l++;
                     }
                     catch (Exception ex)
                     {

                     }
                 }
             }
         }
         return Update(TableName, NewColumnNamesArray, NewColumnValuesArray, WhereKey, WhereValue);
     }
    
     public static bool Insert(string TableName, string[] ColumnNames, string[] ColumnValues, string[] SkipColumns)
     {
         int NewArrayLength = ColumnNames.Length - SkipColumns.Length;
         string[] NewColumnNamesArray = new string[NewArrayLength];
         string[] NewColumnValuesArray = new string[NewArrayLength];
         int l=0;
         for (int i = 0; i < SkipColumns.Length; i++)
         {
             for (int j = 0; j < ColumnNames.Length; j++)
             {
                 if (SkipColumns[i] != ColumnNames[j])
                 {
                     NewColumnNamesArray[l] = ColumnNames[j];
                     NewColumnValuesArray[l] = ColumnValues[j];
                     l++;
                 }
             }
         }
         return Insert(TableName, NewColumnNamesArray, NewColumnValuesArray);
     }
     public static bool Insert(string TableName, string[] ColoumNames, string[] ColoumValues)
     {
         int AffectedRows = int.MinValue;
         StringBuilder Query = new StringBuilder("insert into ");
         if (ColoumNames.Length != ColoumValues.Length)
         {
             return false;
         }
         Query.Append(TableName + "(");
         for (int i = 0; i < ColoumNames.Length; i++)
         {
             Query.Append(ColoumNames[i]);
             if (i != ColoumNames.Length - 1)
             {
                 Query.Append(" , ");
             }
         }
         Query.Append(")values(");
         for (int i = 0; i < ColoumValues.Length; i++)
         {
             Query.Append("'");
             Query.Append(ColoumValues[i]);
             Query.Append("'");
             if (i != ColoumNames.Length - 1)
             {
                 Query.Append(" , ");
             }
         }
         Query.Append(")");

         DBManager dbManager = new DBManager();
         dbManager.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
         try
         {
             dbManager.Open();
             AffectedRows = dbManager.ExecuteNonQuery(System.Data.CommandType.Text, Query.ToString());
             return (AffectedRows > 0) ? true : false;
         }
         catch (Exception ex)
         {

         }
         finally
         {
             dbManager.Close();
         }
         return false;

     }
    
     public static DataSet Select(string TableName)
     {
         StringBuilder Query = new StringBuilder("Select * from ");
         Query.Append(TableName);
       
         DBManager dbManager = new DBManager();
         dbManager.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
         try
         {
             dbManager.Open();
             return dbManager.ExecuteDataSet(System.Data.CommandType.Text, Query.ToString());
         }
         catch (Exception ex)
         {
             throw new ArgumentException("Dynamically Generated Query Has Some Error");
         }
         finally
         {
             dbManager.Close();
         }
     }
     public static DataSet Select(string TableName, string[] ColoumNames, string[] WhereKey, string[] WhereValue)
     {
         StringBuilder Query = new StringBuilder("Select ");
         if (WhereKey.Length != WhereValue.Length)
         {
             throw new ArgumentException("WhereKey & WhereValue Array Size Differ");
         }

         for (int i = 0; i < ColoumNames.Length; i++)
         {
             Query.Append(ColoumNames[i]);
             if (i != ColoumNames.Length - 1)
             {
                 Query.Append(" , ");
             }

         }
         Query.Append(" from ");
         Query.Append(TableName);
         Query.Append(" where ");
         for (int i = 0; i < WhereKey.Length; i++)
         {
             Query.Append(WhereKey[i]);
             Query.Append(" = '");
             Query.Append(WhereValue[i]);
             Query.Append("'");
             if (i != WhereKey.Length - 1)
             {
                 Query.Append(" and ");
             }
         }

         DBManager dbManager = new DBManager();
         dbManager.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQL"].ToString();
         try
         {
             dbManager.Open();
             return dbManager.ExecuteDataSet(System.Data.CommandType.Text, Query.ToString());
         }
         catch (Exception ex)
         {
             throw new ArgumentException("Dynamically Generated Query Has Some Error");
         }
         finally
         {
             dbManager.Close();
         }
     }
     public static DataSet Select(string TableName, string ColoumNames, string WhereKey, string WhereValue)
     {
         string[] whereselect = WhereKey.Split(',');
         string[] WhereselValue = WhereValue.Split(',');
         string[] wheresel = ColoumNames.Split(',');
         return MssqlQueryBuilder.Select(TableName, wheresel, whereselect, WhereselValue);
     }
    }
}


If anybody find it useful feel free to use it,it is nice of you if you provide me with any further addition into it.

No comments:

Post a Comment