BuildQuery类是能快速,容易地构建一个复杂的INSERT或者UPDATE 的SQL查询语句。这个类将接收的一些参数,输出有效的SQL语句。它有一个方法能刷新内部数据,因此这个类可以无数次使用,而无需反复创建和销毁实例对象。Buid,IDSettings,和FlushAllData 方法用于设置数据。 Insert, Update, 和Delete 方法用于处理数据。只有 Build 和IDSettings带参数。
{
#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是用来接收查询字段及其值,以及对数据进行处理。这个方法有三个参数,可以如下调用:
InputField - 字符串值,包含显示的列。InputData
- 字符串值包含被使用的数据。InputQuotes
- 布尔值,表示数据是否应该用引号。
MyField = " Name " ;
MyData = " John Smith " ;
MyQuotes = true ;
MyQuery.Build(MyField, MyData, MyQuotes);
BuildQuery的FlushAllData 方法是用来清除通过Build存储的所有数据。FlushAllData 将能如下调用:
例子:
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方法有三个参数,还有一个四个参数的重载。它能被如下调用:
或者:
IDField - 包含了使用列的字符串。IDValue
- 包含值的字符串。IDQuotes
- 布尔值,值是否带引号。IDOperator
- 字符串包含逻辑操作符它将取代默认的'='。
MyField = " Name " ;
MyData = " John Smith " ;
MyQuotes = true ;
MyOperator = " >= " ;
MyQuery.IDSettings(MyField, MyData, MyQuotes, MyOperator);
BuildQuery 的Insert方法将产生一条准备在数据库中执行的有效地INSERT查询语句。这个方法需要设置Table属性。并至少有一个实例生成方法已被成功调用。Insert能如下使用:
例子:
MyQuery.Table = " MyTable " ;
MyQuery.Build(MyField, MyData, MyQuotes);
String strQuery = MyQuery.Insert();
BuildQuery的Update方法用来产生一条准备在数据库中执行的有效地 UPDATE查询语句。这个方法需要设置Table属性。对象实例至少成功调用一次IDSettings方法和Build方法各一次。Update方法能如下使用:
例子:
MyQuery.Table = " MyTable " ;
MyQuery.IDSettings( " MyID " , MyValue, false );
objBuildQuery.Build(MyField, MyData, true );
String strQuery = MyQuery.Update();
BuildQuery的Delete方法用于永久删除从在数据库中指定的表的一个或多个行。这个方法需要设置Table属性。对象实例至少成功调用一次IDSettings方法。Delete方法能如下使用:
这个类有两个属性:Table 和GetIdentity,两个属性都是可读可写的
Table 属性用来设置BuildQuery类使用的数据表。直到这个属性有值,否者如果产生查询的方法都会返回一个SQl错误
例子:
MyQuery.Table = " MyTable " ;
GetIdentity 属性用来得到和设置产生INSERT 查询语句插入数据之后是否返回一个行的标识种子,这个属性默认是false。
例子:
MyQuery.GetIdentity = true ;
参考原文:http://www.codeproject.com/KB/database/buildquery.aspx
(全文完)
以下为广告部分
您部署的HTTPS网站安全吗?
如果您想看下您的网站HTTPS部署的是否安全,花1分钟时间来 myssl.com 检测以下吧。让您的HTTPS网站变得更安全!
快速了解HTTPS网站安全情况。
安全评级(A+、A、A-...)、行业合规检测、证书信息查看、证书链信息以及补完、服务器套件信息、证书兼容性检测等。
安装部署SSL证书变得更方便。
SSL证书内容查看、SSL证书格式转换、CSR在线生成、SSL私钥加解密、CAA检测等。
让服务器远离SSL证书漏洞侵扰
TLS ROBOT漏洞检测、心血漏洞检测、FREAK Attack漏洞检测、SSL Poodle漏洞检测、CCS注入漏洞检测。