C#中的一个条件生成器类
来源:互联网 发布:最好的直销软件 编辑:程序博客网 时间:2024/06/05 07:17
在进行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))
- C#中的一个条件生成器类。
- C#中的一个条件生成器类
- 分享一个简单高效的C#随机函数生成器类
- C#中的条件方法
- C#类生成器
- c#类图生成器
- C#实体类生成器
- SQL条件生成器类(C#)
- C#写的一个代码生成器
- C#中的#define条件编译
- 自制C#实体类生成器
- SQL查询条件生成器
- 条件Lamada树生成器
- 一个简单的C#双色球号码随机生成器
- 为自己的C# ORM 写一个代码自动生成器
- C#中的#if 条件注释的用法
- C#中的条件判断(?,??等等)
- C# 窗体生成器
- Linux学习(2)_安装
- (置顶)“易语言.飞扬”一览,了解EF从这里开始
- 英文版本的cms大全
- VB 给你的程序提供暂停功能
- 一篇关于在COMBOBOX中使用SENDMESSAGE的实例
- C#中的一个条件生成器类
- JAVA学习入门 对初次接触者 相当有用
- VB常用文件操作类
- 整理的几个常用的数据库维护的脚本
- 一个动态调用DLL的源码
- 一次失误的操作,造成IIS不能启动、IWAM、IUSR账户出错!
- C#文件搜索实例
- DOS时代的游戏:刺杀希特勒、DOOM
- 将自定义.NET控件嵌入IE浏览器