BuildQuery類是能快速，容易地構建一個復雜的INSERT或者UPDATE 的SQL查詢語句。這個類將接收的一些參數，輸出有效的SQL語句。它有一個方法能刷新內部數據，因此這個類可以無數次使用，而無需反復創建和銷毀實例對象。Buid，IDSettings，和FlushAllData 方法用於設置數據。 Insert, Update, 和Delete 方法用於處理數據。只有 Build 和IDSettings帶參數。
代碼
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
}
BuildQuery類的Build是用來接收查詢字段及其值，以及對數據進行處理。這個方法有三個參數，可以如下調用：
obj.Build(InputFIEld, InputData, InputQuotes);
InputFIEld - 字符串值，包含顯示的列。
InputData - 字符串值包含被使用的數據。
InputQuotes - 布爾值，表示數據是否應該用引號。
NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyFIEld = "Name";
MyData = "John Smith";
MyQuotes = true;
MyQuery.Build(MyFIEld, MyData, MyQuotes);
BuildQuery的FlushAllData 方法是用來清除通過Build存儲的所有數據。FlushAllData 將能如下調用：
obj.FlushAllData()
例子：
代碼
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
BuildQuery 的IDSettings方法將用來設置 UPDATE 或者DELETE 得where條件。基本的IDSettings方法有三個參數，還有一個四個參數的重載。它能被如下調用：
obj.IDSettings(IDFIEld, IDValue, IDQuotes);
或者：
obj.IDSettings(IDFIEld, IDValue, IDQuotes, IDOperator);
IDFIEld - 包含了使用列的字符串。
IDValue - 包含值的字符串。
IDQuotes - 布爾值，值是否帶引號。
IDOperator - 字符串包含邏輯操作符它將取代默認的'='。
代碼
NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyFIEld = "Name";
MyData = "John Smith";
MyQuotes = true;
MyOperator = ">=";
MyQuery.IDSettings(MyFIEld, MyData, MyQuotes, MyOperator);
BuildQuery 的Insert方法將產生一條准備在數據庫中執行的有效地INSERT查詢語句。這個方法需要設置Table屬性。並至少有一個實例生成方法已被成功調用。Insert能如下使用:
obj.Insert();
例子：
NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.Table = "MyTable";
MyQuery.Build(MyFIEld, MyData, MyQuotes);
String strQuery = MyQuery.Insert();
BuildQuery的Update方法用來產生一條准備在數據庫中執行的有效地 UPDATE查詢語句。這個方法需要設置Table屬性。對象實例至少成功調用一次IDSettings方法和Build方法各一次。Update方法能如下使用:
obj.Update();
例子：
代碼
NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.Table = "MyTable";
MyQuery.IDSettings("MyID", MyValue, false);
objBuildQuery.Build(MyFIEld, MyData, true);
String strQuery = MyQuery.Update();
BuildQuery的Delete方法用於永久刪除從在數據庫中指定的表的一個或多個行。這個方法需要設置Table屬性。對象實例至少成功調用一次IDSettings方法。Delete方法能如下使用:
obj.Delete();
這個類有兩個屬性：Table 和GetIdentity，兩個屬性都是可讀可寫的
Table 屬性用來設置BuildQuery類使用的數據表。直到這個屬性有值，否者如果產生查詢的方法都會返回一個SQl錯誤
obj.Table = value;
例子：
NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.Table = "MyTable";
GetIdentity 屬性用來得到和設置產生INSERT 查詢語句插入數據之後是否返回一個行的標識種子，這個屬性默認是false。
obj.GetIdentity = true;
例子：
NoInkSoftware.BuildQuery MyQuery = new NoInkSoftware.BuildQuery();
MyQuery.GetIdentity = true;
