C#中一套生成sql条件的类（转）

在进行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() [Page]
{
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);
} [Page]
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("’","’’")+"’"); [Page]
}
count++;
}
if(arr1.Length>0)
{
for(int i=0;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)
{ [Page]
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 {
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() [Page]
{
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 {
SqlParameter [] sps = arr[i].getSqlParameters();
for(int j=0;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); [Page]
}

}

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(); [Page]
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))