BuildQuery

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

[assembly: CLSCompliant(true)]
namespace NoInkSoftware
{
 /// <summary>
 /// Generates an SQL query using the provided data.
 /// </summary>
 public class BuildQuery
 {
  #region Class Variables
  int numFieldsCount, dicpos;
  Dictionary<string, string> dicFields = new Dictionary<string, string>();
  Dictionary<string, string> dicData = new Dictionary<string, string>();
  Dictionary<string, bool> dicQuotes = new Dictionary<string, bool>();
  List<string> listIDFields = new List<string>();
  List<string> listIDValues = new List<string>();
  List<bool> listIDQuotes = new List<bool>();
  List<string> listIDOperators = new List<string>();
  Boolean IdentityCheck;
  string strTable;
  #endregion

  #region Constructor
  /// <summary>
  /// Creates an instance of this class.
  /// </summary>
  public BuildQuery()
  {
  }
  #endregion

  #region Properties
  /// <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;
   }
  }
  #endregion

  #region Methods
  /// <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++;
    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++;
    if (dicpos + 1 <= numFieldsCount)
    {
     InsertString.Append(", ");
    }
   }
   InsertString.Append(")");
   if (IdentityCheck)
   {
    InsertString.AppendFormat("SET NOCOUNT ON;{0};SELECT @@Identity As LastID", InsertString.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++;
    if (dicpos + 1 <= numFieldsCount)
    {
     UpdateString.Append(", ");
    }
   }
   UpdateString.Append(" WHERE ");
   int Conditions = 0;
   for (int IDCount = 0; IDCount < listIDFields.Count; 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++;
   }
   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; 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++;
   }
   return DeleteString.ToString();
  }
  #endregion
 }
}