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:
```
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

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:

obj.FlushAllData()

Example:

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:
```
obj.IDSettings(IDField, IDValue, IDQuotes);
```
- or -
```
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

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:
```
obj.Insert();
```
Example:
```
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:
```
obj.Update();
```
Example:
```
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:
```
obj.Delete();
```
Example:
```
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.
```
obj.Table = value;
```
Example:
```
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.
```
obj.GetIdentity = true;
```
Example:
```
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)

About the Author

mbielski

Web Developer

United States

Member

Mr. Bielski has been developing websites for over 8 years and writing code for much 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).

Article Top