源代码网整理以下在进行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(){}
软件开发网 www.mscto.com
源代码网整理以下 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();
软件开发网 www.mscto.com
outStr.Append("’"+tmp.Replace("’","’’")+"’");
源代码网整理以下 }
count++;
}
源代码网整理以下 if(arr1.Length>0)
{
for(int i=0;i<arr1.Length;i++)
{
if(arr2.toSqlTempletString()=="")
continue;
count++;
if((name!=null&&val!=null)||count>1)
{
outStr.Append(" ");
outStr.Append(arr1);
outStr.Append(" ");
}
outStr.Append(arr2.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++;
} 软件开发网 www.mscto.com
源代码网整理以下 if(arr1.Length>0)
{
for(int i=0;i<arr1.Length;i++)
{
if(arr2.toSqlTempletString()=="")
continue;
count++;
if((name!=null&&val!=null)||count>1)
{
outStr.Append(" ");
outStr.Append(arr1);
outStr.Append(" ");
}
outStr.Append(arr2.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.getSqlParameters();
for(int j=0;j<sps.Length;j++)
{
tmp.Add(sps[j]);
}
}
return (SqlParameter [])tmp.ToArray(new SqlParameter("","").GetType());
软件开发网 www.mscto.com
}
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);
软件开发网 www.mscto.com
源代码网整理以下 condition6.addCondition(LogicOper.or,condition4);
condition6.addCondition(LogicOper.or,condition5);
软件开发网 www.mscto.com
源代码网整理以下 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))
