BuildQuery - A Simple SQL Query Tool

• Download source code - 10.72 KB

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.