Click here to Skip to main content
15,889,867 members
Search within:


Articles / Programming Languages / SQL
 
Article

BuildQuery - A Simple SQL Query Tool

Rate me:
Please Sign up or sign in to vote.
2.60/5 (6 votes)
19 Feb 2010CPOL3 min read 43.1K   176   19   23
A custom class that creates SQL queries without long and potentially confusing string concatenation

Introduction

The BuildQuery class is used to make the creation of large or complex SQL INSERT or UPDATE queries faster and easier in ASP.NET pages using C#. The class will accept sets of data, and output valid queries. There is also a method to flush data from internal storage so that the class may be used for any number of queries without repeatedly creating and destroying an object. The download includes the source and a release version DLL file. If you want a debug version, you can compile the code yourself.

Using the Code

Methods

The Build, IDSettings, and FlushAllData methods are used to do things to the data, and the Insert, Update, and Delete methods are used to do things with the data. Of the six methods, only Build and IDSettings have arguments.

  • Build: The Build method of BuildQuery is how the query fields and their values get into the methods that do things with the data. The Build method has three arguments (all required), and is called as follows:
    C#
    obj.Build(InputField, InputData, InputQuotes);
    • InputField - String value containing the column in which the data will appear
    • InputData - String value containing the data that is to be used
    • InputQuotes - Boolean value indicating whether the data should be surrounded by quotes
    C#
    NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyField = "Name";
MyData = "John Smith";
MyQuotes = true;

MyQuery.Build(MyField, MyData, MyQuotes);
  • FlushAllData: The FlushAllData method of BuildQuery is used to clear out any values that have been stored with the Build method. The FlushAllData method is called as follows:
    C#
    obj.FlushAllData()

    Example:

    C#
    NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.Table = "MyTable";
MyQuery.Build(MyField, MyData, MyQuotes);
strQuery = MyQuery.Insert;
MyQuery.FlushAllData();
//The object is now ready to work on a different set of data
  • IDSettings: The IDSettings method of BuildQuery is used to set the conditions of the WHERE clause for UPDATE or DELETE queries. The base IDSettings method has three arguments (all required), with one overload that adds a fourth argument. The method is called as follows:
    C#
    obj.IDSettings(IDField, IDValue, IDQuotes);

    - or -

    C#
    obj.IDSettings(IDField, IDValue, IDQuotes, IDOperator);
    • IDField - String containing the column to be used
    • IDValue - String containing the value to be used
    • IDQuotes - Boolean indicating whether the value should have quotes around it
    • IDOperator - String containing the logic operator to be used in place of the default
    C#
    NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyField = "Name";
MyData = "John Smith";
MyQuotes = true;

MyOperator = ">=";
MyQuery.IDSettings(MyField, MyData, MyQuotes, MyOperator);
  • Insert: The Insert method of BuildQuery is used to generate a valid SQL INSERT query that is ready for execution in the database. This method requires that the Table property be set and that at least one instance of the Build method has been successfully called. The Insert method called as follows:
    C#
    obj.Insert();

    Example:

    C#
    NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.Table = "MyTable";
MyQuery.Build(MyField, MyData, MyQuotes);
String strQuery = MyQuery.Insert();
  • Update: The Update method of BuildQuery is used to generate a valid SQL UPDATE query that is ready for execution in the database. This method requires that the Table property has been set, at least one instance of the IDSettings method has been successfully called, and that at least one instance of the Build method has been successfully called. The Update method is called as follows:
    C#
    obj.Update();

    Example:

    C#
    NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.Table = "MyTable";
MyQuery.IDSettings("MyID", MyValue, false);
objBuildQuery.Build(MyField, MyData, true);
String strQuery = MyQuery.Update();
  • Delete: The Delete method of BuildQuery is used to permanently remove one or more rows from a specified table in the database. This method requires that the Table property be set and at least one instance of the IDSettings method has been called successfully. The Delete method is called as follows:
    C#
    obj.Delete();

    Example:

    C#
    NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.Table = "MyTable";
MyQuery.IDSettings("MyID", "7", false);
String strQuery = MyQuery.Delete();

Properties

This class has two properties: Table and GetIdentity. Both properties are read/write.

  • Table: The Table property sets the table to be used in the BuildQuery class. Until this property has a value, any queries generated by the class will return SQL errors.
    C#
    obj.Table = value;

    Example:

    C#
    NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.Table = "MyTable";
  • GetIdentity: The GetIdentity property gets or sets whether the INSERT query generated will return the identity value of the row that is inserted in the database. This property is set to false by default.
    C#
    obj.GetIdentity = true;

    Example:

    C#
    NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.GetIdentity = true;

Points of Interest

This originally started out as a Classic ASP class way back when I was learning how to handle ADO and custom classes. When I learned ASP.NET, I still found myself wanting to use it for those times when what I am doing doesn't directly work with something that isn't already built into, so it made the jump to .NET.

History

This is v1.0.0.1. A bug was found in the Insert() method and has been corrected. If you downloaded the original version, you should download this new version and replace the original with this.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
mbielski
Software Developer (Senior)
United States United States
Mr. Bielski has been developing websites since 2000 and writing code for a bit longer than that. When not plying is trade, he enjoys online and other games. He also contributes to the No Ink Software website (http://www.noinksoftware.com).

Comments and Discussions

 
First Prev Next
GeneralMy vote of 1 Pin
lobotomy4-Jan-10 22:57
professionallobotomy4-Jan-10 22:57 
GeneralMy vote of 1 Pin
jiragabo4-Jan-10 22:43
jiragabo4-Jan-10 22:43 
GeneralMy vote of 1 Pin
DanWalker29-Dec-09 9:21
DanWalker29-Dec-09 9:21 
GeneralRe: My vote of 1 Pin
mbielski30-Dec-09 5:29
mbielski30-Dec-09 5:29 
GeneralGreat concept... Pin
Terence Wallace29-Dec-09 8:30
Terence Wallace29-Dec-09 8:30 
GeneralRe: Great concept... Pin
mbielski29-Dec-09 8:41
mbielski29-Dec-09 8:41 
GeneralRe: Great concept... [modified] Pin
Terence Wallace29-Dec-09 9:09
Terence Wallace29-Dec-09 9:09 
Translated to VB.NET and C#. Thanks for your contribution. I think this class will work great into an ORM Framework.

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());
    }
}
}


To stay fairly consistent with Gof design Builder Pattern[^] the class was renamed to QueryBuilder.

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();
    }

  }
}


"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." - Red Adair

modified on Tuesday, December 29, 2009 3:15 PM
GeneralRe: Great concept... Pin
mbielski29-Dec-09 9:37
mbielski29-Dec-09 9:37 
GeneralRe: Great concept... Pin
Not Active29-Dec-09 10:43
mentorNot Active29-Dec-09 10:43 
GeneralMy vote of 1 Pin
CARPETBURNER28-Dec-09 23:15
CARPETBURNER28-Dec-09 23:15 
GeneralQuestionable use case Pin
Not Active28-Dec-09 8:46
mentorNot Active28-Dec-09 8:46 
GeneralRe: Questionable use case Pin
mbielski28-Dec-09 9:03
mbielski28-Dec-09 9:03 
GeneralRe: Questionable use case Pin
Not Active28-Dec-09 9:38
mentorNot Active28-Dec-09 9:38 
GeneralRe: Questionable use case Pin
mbielski28-Dec-09 10:11
mbielski28-Dec-09 10:11 
GeneralRe: Questionable use case Pin
Not Active28-Dec-09 10:37
mentorNot Active28-Dec-09 10:37 
GeneralRe: Questionable use case Pin
mbielski28-Dec-09 10:51
mbielski28-Dec-09 10:51 
GeneralRe: Questionable use case Pin
Robert Rohde28-Dec-09 22:54
Robert Rohde28-Dec-09 22:54 
GeneralRe: Questionable use case Pin
Not Active29-Dec-09 1:57
mentorNot Active29-Dec-09 1:57 
GeneralRe: Questionable use case Pin
Robert Rohde29-Dec-09 10:11
Robert Rohde29-Dec-09 10:11 
GeneralRe: Questionable use case Pin
Not Active29-Dec-09 10:40
mentorNot Active29-Dec-09 10:40 
GeneralRe: Questionable use case Pin
Robert Rohde30-Dec-09 3:50
Robert Rohde30-Dec-09 3:50 
GeneralRe: Questionable use case Pin
Not Active30-Dec-09 4:20
mentorNot Active30-Dec-09 4:20 
GeneralRe: Questionable use case Pin
Robert Rohde30-Dec-09 5:06
Robert Rohde30-Dec-09 5:06 
Last Visit: 31-Dec-99 18:00     Last Update: 1-May-24 15:46Refresh1

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.

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 28 Dec 2009

Article Copyright 2009 by mbielski
Everything else Copyright © CodeProject, 1999-2024

Web04 2.8:2024-04-02:1