BuildQuery - A Simple SQL Query Tool
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BuildQuery
{
/// <summary>
/// 使用提供的数据建立一个SQL查询
/// </summary>
public class BuildQuery
{
#region 类的变量
int numFieldsCount, dispos; //字段数量，记录逗号的数量
//
//将所有的字段和值放入到Dictionary集合里，提高操作效率
//
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>();//操作符集合
string strTable; //表名
Boolean IdentityCheck; //是否需要返回一个新的实体类
/// <summary>
/// 封装一个是否需要新的实体类
/// </summary>
public Boolean GetIdentity
{
get { return IdentityCheck; }
set { IdentityCheck = value; }
}
/// <summary>
/// 封装返回一个查询的结果
/// </summary>
public string Table
{
get { return strTable; }
set { strTable = value; }
}
#endregion
/// <summary>
/// 类的实例化构造方法
/// </summary>
public BuildQuery()
{
}
/// <summary>
/// 建立一个查询前清除DICTIONARY集合的所有内容
/// </summary>
public void FlushAllDada()
{
numFieldsCount = 0;
dispos = 0;
dicFields.Clear();
dicData.Clear();
dicQuotes.Clear();
listIDFields.Clear();
listIDValues.Clear();
listIDQuotes.Clear();
listIDOperators.Clear();
}
/// <summary>
/// 获取查询的字段及处理放入的值
/// </summary>
/// <param name="InputField">数据库字段</param>
/// <param name="InputData">将要处理的值</param>
/// <param name="InputQuotes">判断是否需要引号</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>
/// 使用操作符（默认是“=”）设置一部分查询语句
/// </summary>
/// <param name="IDField">将要使用的字段</param>
/// <param name="IDValue">对应字段的值</param>
/// <param name="IDQuotes">值是否需要引号</param>
public void IDSetting(string IDField, string IDValue, bool IDQuotes)
{
listIDFields.Add(IDField);
listIDValues.Add(IDValue);
listIDQuotes.Add(IDQuotes);
listIDOperators.Add("=");
}
/// <summary>
/// 使用操作符（默认是“=”）设置一部分查询语句
/// </summary>
/// <param name="IDField">将要使用的字段</param>
/// <param name="IDValue">对应字段的值</param>
/// <param name="IDQuotes">值是否需要引号</param>
/// <param name="IDOperator">实际使用的操作符</param>
public void IDSetting(string IDField, string IDValue, bool IDQuotes, string IDOperator)
{
listIDFields.Add(IDField);
listIDValues.Add(IDValue);
listIDQuotes.Add(IDQuotes);
listIDOperators.Add(IDOperator);
}
/// <summary>
/// 返回输入查询使用所提供的数据
/// </summary>
/// <returns></returns>
public string Insert()
{
StringBuilder InsertString = new StringBuilder();
dispos = 0;
numFieldsCount = dicData.Count;
InsertString.AppendFormat("insert into {0}(", strTable);
//加入字段，拼接SQL语句
foreach (KeyValuePair<string,string> i in dicFields)
{
InsertString.Append(i.Value);
dispos++;
if (dispos + 1 <= numFieldsCount) //有i个字段，则加入i-1个“，”
InsertString.Append(",");
}
dispos = 0;
InsertString.Append(") values(");
//将这些值赋给对应的字段，再次拼接SQL语句
foreach (KeyValuePair<string,string> k in dicData)
{
if (dicQuotes[k.Key])
InsertString.Append("'");
InsertString.Append(dicData[k.Key]);
if (dicQuotes[k.Key])
InsertString.Append("'");
dispos++;
if (dispos + 1 <= numFieldsCount)
InsertString.Append(","); //有i个字段值，则加入i-1个“，”
}
InsertString.Append(")");
if (IdentityCheck)
InsertString.AppendFormat("SET NOCOUNT ON;{0};SELECT @@IDENTITY as LastID", InsertString.ToString());
return InsertString.ToString();
}
/// <summary>
/// 返回修改查询使用所提供的数据
/// </summary>
/// <returns></returns>
public string Update()
{
StringBuilder UpdateString = new StringBuilder();
dispos = 0;
numFieldsCount = dicData.Count; //取出字段值的长度
UpdateString.AppendFormat("UPDATE {0} SET ", strTable);
//加入字段，拼接SQL语句
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("'");
dispos++;
if (dispos + 1 <= numFieldsCount)
UpdateString.Append(",");
}
//加入条件，拼接SQL语句
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>
/// 返回删除查询使用所提供的数据
/// </summary>
/// <returns></returns>
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();
}
}
}
翻译至THE CODEPROJECT
