BuildQuery - A Simple SQL Query Tool

BooGhost wrote:

New and/or junior developers would have to be brought up to speed on the library before they could maintain anything that used it.

BooGhost wrote:

How are we to praise or critique the code if you don't provide us anything in the article?

Imports System
Imports System.Collections.Generic
Imports System.Text

Friend Class Program1
    Shared Sub Main(ByVal args() As String)

        Dim builder As New Common.QueryBuilder
        builder.Table = "Horses"
        builder.IDSettings("HorseID", "3", False)
        builder.Build("Name", "John Doe", True)
        builder.Build("Age", "15", False)
        builder.GetIdentity = True

        Console.WriteLine(builder.GetAll)
        Console.WriteLine(builder.Insert)
        Console.WriteLine(builder.Update)
        Console.WriteLine(builder.Delete)
        Console.WriteLine(builder.GetByID)
    End Sub
End Class

using System;
using System.Collections.Generic;
using System.Text;

namespace Common
{
public class Program1
{

    public static void Main()
    {
        Common.QueryBuilder builder = new QueryBuilder();
        builder.Table = "Horses";
        builder.IDSettings("HorseID", "3", false);
        builder.Build("Name", "John Doe", true);
        builder.Build("Age", "15", false);
        builder.GetIdentity = true;

        Console.WriteLine(builder.GetAll() );
        Console.WriteLine(builder.Insert());
        Console.WriteLine(builder.Update());
        Console.WriteLine(builder.Delete());
        Console.WriteLine(builder.GetByID());
    }
}
}

using System;
using System.Text;
using System.Collections.Generic;

[assembly: CLSCompliant(true)]
namespace Common  
{
  //'' <summary>
  //'' Generates an SQL query using the provided data.
  //'' </summary>
  public class QueryBuilder
  {

    private int numFieldsCount;
    private int dicpos;
    private Dictionary<string, string> dicFields = new Dictionary<string, string>();
    private Dictionary<string, string> dicData = new Dictionary<string, string>();
    private Dictionary<string, bool> dicQuotes = new Dictionary<string, bool>();
    private List<string> listIDFields = new List<string>();
    private List<string> listIDValues = new List<string>();
    private List<bool> listIDQuotes = new List<bool>();
    private List<string> listIDOperators = new List<string>();
    private bool IdentityCheck;
    private string strTable;

    //'' <summary>
    //'' Gets or sets the table that the resulting query will work with.
    //'' </summary>
    public string Table {
      get { return strTable; }
      set { strTable = value; }
    }

    //'' <summary>
    //'' Gets or sets whether the query is set to return a new identity value.
    //'' </summary>
    public bool GetIdentity {
      get { return IdentityCheck; }
      set { IdentityCheck = value; }
    }

    //'' <summary>
    //'' Creates an instance of this class.
    //'' </summary>
		public QueryBuilder()
		{
		}


    //'' <summary>
    //'' Clears all of the internal dictionaries so that a new query can be created.
    //'' </summary>
    public void FlushAllData()
    {
      numFieldsCount = 0;
      dicpos = 0;
      dicFields.Clear();
      dicData.Clear();
      dicQuotes.Clear();
      listIDFields.Clear();
      listIDValues.Clear();
      listIDQuotes.Clear();
      listIDOperators.Clear();
    }

    //'' <summary>
    //'' Adds data to the query.
    //'' </summary>
    //'' <param name="InputField">String value containing the column in which the data will appear.</param>
    //'' <param name="InputData">String value containing the data that is to be used.</param>
    //'' <param name="InputQuotes">Boolean value indicating whether the data should be surrounded by quotes.</param>
    public void Build(string InputField, string InputData, bool InputQuotes)
    {
      if (!(dicFields.ContainsKey(InputField))) {
        dicFields.Add(InputField, InputField);
        dicData.Add(InputField, InputData);
        dicQuotes.Add(InputField, InputQuotes);
      } else {
        dicData[InputField] = InputData;
        dicQuotes[InputField] = InputQuotes;
      }
    }

    //'' <summary>
    //'' Sets the WHERE portion of the query (if applicable) using the specified operator (default is "=".)
    //'' </summary>
    //'' <param name="IDField">String containing the column to be used.</param>
    //'' <param name="IDValue">String containing the value to be used.</param>
    //'' <param name="IDQuotes">Boolean indicating whether the value should have quotes around it.</param>
    public void IDSettings(string IDField, string IDValue, bool IDQuotes)
    {
      listIDFields.Add(IDField);
      listIDValues.Add(IDValue);
      listIDQuotes.Add(IDQuotes);
      listIDOperators.Add("=");
    }

    //'' <summary>
    //'' Sets the WHERE portion of the query (if applicable) using the specified operator (default is "=".)
    //'' </summary>
    //'' <param name="IDField">String containing the column to be used.</param>
    //'' <param name="IDValue">String containing the value to be used.</param>
    //'' <param name="IDQuotes">Boolean indicating whether the value should have quotes around it.</param>
    //'' <param name="IDOperator">String containing the logic operator to be used in place of the default.</param>
    public void IDSettings(string IDField, string IDValue, bool IDQuotes, string IDOperator)
    {
      listIDFields.Add(IDField);
      listIDValues.Add(IDValue);
      listIDQuotes.Add(IDQuotes);
      listIDOperators.Add(IDOperator);
    }

    //'' <summary>
    //'' Returns an Input query using the data provided.
    //'' </summary>
    public string Insert()
    {
      StringBuilder InsertString = new StringBuilder();
      dicpos = 0;
      numFieldsCount = dicData.Count;
      InsertString.AppendFormat("INSERT INTO {0} (", strTable);
      //Put all of the fields into the query
      foreach (KeyValuePair<string, string> I in dicFields) {
        InsertString.Append(I.Value);
        dicpos += 1;
        if (dicpos + 1 <= numFieldsCount) {
          InsertString.Append(", ");
        }
      }
      dicpos = 0;
      InsertString.Append(") VALUES (");
      //Put all of the data into the query
      foreach (KeyValuePair<string, string> K in dicData) {
        if (dicQuotes[K.Key]) {
          InsertString.Append("'");
        }
        InsertString.Append(dicData[K.Key]);
        if (dicQuotes[K.Key]) {
          InsertString.Append("'");
        }
        dicpos += 1;
        if (dicpos + 1 <= numFieldsCount) {
          InsertString.Append(", ");
        }
      }
      InsertString.Append(")");
      if (IdentityCheck) {
        string InsString = InsertString.ToString();
        InsertString = new StringBuilder();
        InsertString.Append(string.Format("SET NOCOUNT ON;{0};SELECT @@Identity As LastID", InsString.ToString()));
      }
      return InsertString.ToString();
    }

    //'' <summary>
    //'' Returns an Update query using the data provided.
    //'' </summary>
    public string Update()
    {
      StringBuilder UpdateString = new StringBuilder();
      dicpos = 0;
      numFieldsCount = dicData.Count;
      UpdateString.AppendFormat("UPDATE {0} SET ", strTable);
      //Match up fields and data
      foreach (KeyValuePair<string, string> I in dicFields) {
        UpdateString.AppendFormat("{0} = ", I.Value);
        if (dicQuotes[I.Key]) {
          UpdateString.Append("'");
        }
        UpdateString.Append(dicData[I.Key]);
        if (dicQuotes[I.Key]) {
          UpdateString.Append("'");
        }
        dicpos += 1;
        if (dicpos + 1 <= numFieldsCount) {
          UpdateString.Append(", ");
        }
      }
      UpdateString.Append(" WHERE ");
      int Conditions = 0;
      for (int IDCount = 0; IDCount <= listIDFields.Count - 1; IDCount++) {
        if (Conditions > 0) {
          UpdateString.Append(" AND ");
        }
        UpdateString.AppendFormat("{0} {1}", listIDFields[IDCount], listIDOperators[IDCount]);
        if (listIDQuotes[IDCount]) {
          UpdateString.Append("'");
        }
        UpdateString.Append(listIDValues[IDCount]);
        if (listIDQuotes[IDCount]) {
          UpdateString.Append("'");
        }
        Conditions += 1;
      }
      return UpdateString.ToString();
    }

    //'' <summary>
    //'' Returns a Delete query using the data provided.
    //'' </summary>
    public string Delete()
    {
      StringBuilder DeleteString = new StringBuilder();
      DeleteString.AppendFormat("DELETE FROM {0} WHERE ", strTable);
      int Conditions = 0;
      for (int IDCount = 0; IDCount <= listIDFields.Count - 1; IDCount++) {
        if (Conditions > 0) {
          DeleteString.Append(" AND ");
        }
        DeleteString.AppendFormat("{0} {1}", listIDFields[IDCount], listIDOperators[IDCount]);
        if (listIDQuotes[IDCount]) {
          DeleteString.Append("'");
        }
        DeleteString.Append(listIDValues[IDCount]);
        if (listIDQuotes[IDCount]) {
          DeleteString.Append("'");
        }
        Conditions += 1;
      }
      return DeleteString.ToString();
    }

    //'' <summary>
    //'' Returns a Get all query using the data provided.
    //'' </summary>
    public string GetAll()
    {
      StringBuilder GetAllString = new StringBuilder();
      GetAllString.AppendFormat("SELECT * FROM {0} ", strTable);
      return GetAllString.ToString();
    }

    //'' <summary>
    //'' Returns a Get query using the data provided.
    //'' </summary>
    public string GetByID()
    {
      StringBuilder SpecificString = new StringBuilder();
      SpecificString.AppendFormat("SELECT * FROM {0} WHERE ", strTable);
      int Conditions = 0;
      for (int IDCount = 0; IDCount <= listIDFields.Count - 1; IDCount++) {
        if (Conditions > 0) {
          SpecificString.Append(" AND ");
        }
        SpecificString.AppendFormat("{0} {1}", listIDFields[IDCount], listIDOperators[IDCount]);
        if (listIDQuotes[IDCount]) {
          SpecificString.Append("'");
        }
        SpecificString.Append(listIDValues[IDCount]);
        if (listIDQuotes[IDCount]) {
          SpecificString.Append("'");
        }
        Conditions += 1;
      }
      return SpecificString.ToString();
    }

  }
}

TL Wallace wrote:

I think this class will work great into an ORM Framework.

mbielski wrote:

They were designed for running complex tasks quickly on the database server...

mbielski wrote:

... and reducing the load on your web server thereby reducing the amount of time it takes to serve up the page

Mark Nischalke wrote:

You are ignoring other usages for stored procs, such as maintainability, reuse, security and access control.

Mark Nischalke wrote:

A poorly written stored proc can slow down an application and increase the load on the DB and slow other applications as well.

mbielski wrote:

I just don't think this is one of them.

mbielski wrote:

Yes, but that's not the point of this tool.

Mark Nischalke wrote:

I can ask to remove it if you'd like to continue to argue the case for it.

Robert Rohde wrote:

"Don't disagree with me or your article will be deleted."

Robert Rohde wrote:

I haven't one voted your posts, but I think about doing so

Mark Nischalke wrote:

I can ask to remove it if you'd like to continue to argue the case for it.

Robert Rohde wrote:

instead start a flamewar