C#生成sql条件的类

在进行sql查询的时候，有时候要进行很多条件限制，自己来拼写SQLwhere条件容易出错，而且判断条件复杂，后期维护困难，

基于这个原因我在一个小项目中写了一套生成sql条件的类。总共包括一个Condition类，与两个枚举型类型（LogicOper，CompareOper）

 using System;
 using System.Collections.Generic;
 using System.Text;
 using System.Data;
 using System.Data.SqlClient;
 using System.Collections;

 namespace ConsoleApplication1
{
 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
 }

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

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

 string compareOper = null;
 string name = null;
 string templateName = null;
 string valType = null;
 object val = null;

 public Condition()
 {

 }
 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 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() == "")
 {
 count++;
 continue;
 }
 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 < tmp.Count; i++)
 {
 SqlParameter[] sps = arr[i].getSqlParameters();
 for (int j = 0; j < 98; j++)
 {
 tmp.Add(sps[j]);
 }
 }
 return (SqlParameter[])tmp.ToArray(new SqlParameter("", "").GetType());
 }


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


调用测试
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();


 Console.WriteLine(condition5.toSqlString());



通过Condition类的addCondition方法可以实现任意复杂的条件组合。toSqlString()方法返回sql条件，可以用于sql拼接方式使用。

而toSqlTempletString()方式生成的是以参数形式的sql条件，配合getSqlParameters()方法可以实现 以参数传递的条件

（相当于java中的prepareStatement实现）。