浏览 4127 次
|
锁定老帖子 主题：简单且超级实用的动态生成SQL语句的类文件
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
|---|---|
| 作者 | 正文 |
|
发表时间：2011-11-21
最后修改：2011-11-22
using System;
using System.Collections;
namespace DAL
{
#region 字符串类型枚举
/// <summary>
/// SQL语句的查询类型 - 学云网-www.ixueyun.com
/// </summary>
public enum SqlType
{
/// <summary>
/// 检索数据
/// </summary>
Select,
/// <summary>
/// 插入数据
/// </summary>
Insert,
/// <summary>
/// 更新数据
/// </summary>
Update,
/// <summary>
/// 删除数据
/// </summary>
Delete
}
public enum GetCorV
{
/// <summary>
/// 列字符串
/// </summary>
Colunm,
/// <summary>
/// 值字符串
/// </summary>
ValueList,
/// <summary>
/// 列 = 值格式
/// </summary>
CandV
}
#endregion
/// <summary>
/// 构建SQL查询字符串 学云网-www.ixueyun.com
/// </summary>
public class MakeQuery
{
#region 根据条件组合SQL语句
/// <summary>
/// 根据传进来的参数构造对应的SQL语句 学云网-www.ixueyun.com
/// </summary>
/// <param name="table">要查询的表名</param>
/// <param name="queryItems">字段/值组成的哈希表</param>
/// <param name="sidu">S=select,I=insert,U=update,D=delete</param>
/// <param name="where">查询条件例如 id=1 或者 name='thc' and pwd='123'</param>
/// <returns></returns>
public static string MakeQueryString(string table, Hashtable queryItems, SqlType sidu, string where)
{
string query = "";
switch (sidu)
{
case SqlType.Select:
query = "select " + SubHas(queryItems, GetCorV.Colunm) + " from " + table + " where " + where;
break;
case SqlType.Insert:
query = "insert into " + table + "(" + SubHas(queryItems, GetCorV.Colunm) + ") values(" + SubHas(queryItems, GetCorV.ValueList) + ")";
break;
case SqlType.Update:
query = "update " + table + " set " + SubHas(queryItems, GetCorV.CandV) + " where " + where;
break;
case SqlType.Delete:
query = "delete from " + table + " where " + where;
break;
}
return query;
}
/// <summary>
/// 构造对应的SQL语句（用于无条件查询） 学云网-www.ixueyun.com
/// </summary>
/// <param name="table">要查询的表名</param>
/// <param name="queryItems">字段/值组成的哈希表</param>
/// <param name="sidu">查询的类型</param>
/// <returns></returns>
public static string MakeQueryString(string table, Hashtable queryItems, SqlType sidu)
{
string query = "";
switch (sidu)
{
case SqlType.Select:
query = "select " + SubHas(queryItems, GetCorV.Colunm) + " from " + table;
break;
case SqlType.Insert:
query = "insert into " + table + "(" + SubHas(queryItems, GetCorV.Colunm) + ") values(" + SubHas(queryItems, GetCorV.ValueList) + ")";
break;
case SqlType.Update:
query = "update " + table + " set " + SubHas(queryItems, GetCorV.CandV);
break;
case SqlType.Delete:
query = "delete from " + table;
break;
}
return query;
}
/// <summary>
/// 构造对应的SQL语句(用于无值列的查询) 学 云 网-www.ixueyun.com
/// </summary>
/// <param name="table">要查询的表名</param>
/// <param name="type">查询的类型</param>
/// <param name="where">查询条件例如 id=1 或者 name='thc' and pwd='123'</param>
/// <returns></returns>
public static string MakeQueryString(string table, SqlType type, string where)
{
string query = "";
switch (type)
{
case SqlType.Select:
query = "select * from " + table + " where " + where;
break;
case SqlType.Delete:
query = "delete from " + table + " where " + where;
break;
}
return query;
}
/// <summary>
/// 构造对应的SQL语句(用于无值列的查询) 学 云 网 -www.ixueyun.com
/// </summary>
/// <param name="table">要查询的表名</param>
/// <param name="type">查询的类型</param>
/// <returns></returns>
public static string MakeQueryString(string table, SqlType type)
{
string query = "";
switch (type)
{
case SqlType.Select:
query = "select * from " + table;
break;
case SqlType.Delete:
query = "delete from " + table;
break;
}
return query;
}
#endregion
#region 根据要求返回需要的列或值字符串
/// <summary>
/// 根据查询方式，将哈希表中的键值对组合为字符串
/// </summary>
/// <param name="queryItems">装有列、值对的哈希表</param>
/// <param name="corvorcv">列，值字符串</param>
/// <returns></returns>
private static string SubHas(Hashtable queryItems, GetCorV corvorcv)
{
int count = 0;
string qstr = "";
if (corvorcv == GetCorV.Colunm)
{
foreach (DictionaryEntry de in queryItems)
{
qstr += de.Key + ",";
count++;
}
qstr = qstr.Remove(qstr.Length - 1);//删除最后的那个逗号
}
else if (corvorcv == GetCorV.ValueList)
{
foreach (DictionaryEntry de in queryItems)
{
qstr +="'"+ de.Value + "',";
count++;
}
qstr = qstr.Remove(qstr.Length - 1);//删除最后的那个逗号
}
else
{
foreach (DictionaryEntry de in queryItems)
{
qstr += de.Key + "='" + de.Value + "',";
count++;
}
qstr = qstr.Remove(qstr.Length - 1);//删除最后的那个逗号
}
return qstr;
}
#endregion
}
}
声明：ITeye文章版权属于作者，受法律保护。没有作者书面许可不得转载。
推荐链接
|
| 返回顶楼 | |
|
发表时间：2011-11-22
楼主东西很好，以后常来往哦
|
| 返回顶楼 | |
|
发表时间：2011-12-05
这个东西好，学习一下
|
| 返回顶楼 | |
|
发表时间：2011-12-28
还是封装得不够
|
| 返回顶楼 | |
|
发表时间：2012-03-21
这个东西好，学习一下
|
| 返回顶楼 | |
|
发表时间：2012-05-24
这个针对单表操作是可行的，但是如果涉及到多表查询或者嵌套表查询就有点吃力了。
|
| 返回顶楼 | |
