c#工具类
每当在编写程序的时候公司总是使用数据源，虽然数据源比较快，但是移植性差，都是要重复写很多的代码，近来在与公司的同事交流的时候，尽量要用ADO.net的方式，以下是一个工具类，以后再写程序的时候就可以直接调用，实现相关的功能，这样可以节省编写代码。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace gov_corp.db
{
public class DBHelper
{
//获取连接字符串
static string conStrs = "server=.;database=gov_corp;uid=sa;pwd=sa";
//连接对象
private static SqlConnection conn;
//连接对象的设置
public static SqlConnection Conn
{
get
{
if (conn == null)
{
conn = new SqlConnection(conStrs);
conn.Open();
}
if (conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return DBHelper.conn;
}
}
/// <summary>
/// 关闭的方法
/// </summary>
public static void CloseConn()
{
if (Conn != null && Conn.State != ConnectionState.Closed)
{
Conn.Close();
}
}
/// <summary>
/// 通用增删改的方法
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <returns></returns>
public static int ExcuteCommand(string sql)
{
//创建commond对象
SqlCommand cmd = new SqlCommand(sql, Conn);
//指定命令类型
cmd.CommandType = CommandType.Text;
//执行结果
int count = cmd.ExecuteNonQuery();
//调用关闭连接的方法
CloseConn();
return count;
}
/// <summary>
/// 查询单个值
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <returns></returns>
public static object GetScaler(string sql)
{
//创建command对象
SqlCommand cmd = new SqlCommand(sql, Conn);
//指定命令类型
cmd.CommandType = CommandType.Text;
//执行查询返回具体行数
Object count = cmd.ExecuteScalar();
CloseConn();
return count;
}
/// <summary>
/// 查询多条数据方法
/// </summary>
/// <param name="sql"></param>
/// <param name="type"></param>
/// <returns></returns>
public static DataTable GetTable(string sql)
{
//创建读取器
SqlDataAdapter sda = new SqlDataAdapter(sql, Conn);
//指定命令
sda.SelectCommand.CommandType = CommandType.Text;
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
}
还有一个：
using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Reflection;
using System.Data;
using System.Reflection;
namespace gov_corp.db
{
/// <summary>
/// 根据条件创建特定的SQL语句
/// </summary>
public class SqlFactory
{
/// <summary>
/// 根据条件获取数据库查询语句
/// </summary>
/// <param name="con">条件对象</param>
/// <returns>数据库操作对象</returns>
/// <action>注意了！！如果要分组查询的,con对象的FieldList里面，一定别加进ID字段，否则报错</action>
public string SelectSqlBuilder(Conditions con)
{
//声明数据库语句，先置空，以备返回
string sql = "";
//获取where条件字符串
string where = this.MakeWhere(con.WhereList);
//获取排序字符串
string order = this.MakeOrder(con.OrderList);
//获取字段或者聚合函数等查询条件字符串
string field = this.MakeField(con.FieldList);
//获取分组条件字符串
string group = this.MakeGroup(con.GroupList);
//封装数据库查询语句
sql = "select " + field + " from " + con.TableName + where + group + order + ";";
//返回数据库语句
return sql;
}
/// <summary>
/// 修改数据库语句
/// </summary>
/// <param name="con">条件对象</param>
/// <returns>修改数据库操作语句</returns>
public string UpdateSqlBuilder(Conditions con)
{
//声明数据库语句，先置空，以备返回
string sql = "";
//获取where条件字符串
string where = this.MakeWhere(con.WhereList);
string update = "";
if (con.Obj != null)
{
//获取更新的字符串
update = this.MakeUpdate(con.Obj);
}
else
{
update = this.MakeManyUpdate(con.UpdateList);
}
//拼装sql语句
sql = "update " + con.TableName + " set " + update + where + ";";
//返回数据库语句
return sql;
}
/// <summary>
/// 获取插入数据库记录的语句
/// </summary>
/// <param name="con">条件对象</param>
/// <returns>插入数据库语句</returns>
public string InsertSqlBuilder(Conditions con)
{
//声明数据库语句,先置空,以备返回
string sql = "";
//获取插入语句的值字符串
string valueString = this.MakeValue(con.ValueList);
string fieldString = this.MakeField(con.FieldList);
//封装数据库操作语句
sql = "insert into " + con.TableName +"("+ fieldString +")"+ valueString + ";";
//返回数据库语句
return sql;
}
/// <summary>
/// 获取删除数据库记录的语句
/// </summary>
/// <param name="con">条件对象</param>
/// <returns>删除数据库语句</returns>
public string DeleteSqlBuilder(Conditions con)
{
//声明数据库语句，先置空，以备返回
string sql = "";
//获取where条件字符串
string where = this.MakeWhere(con.WhereList);
//封装sql
sql = "delete from " + con.TableName + where + ";";
//返回数据库语句
return sql;
}
/// <summary>
/// 拆分where条件集合，拼装成一个where条件字符串
/// </summary>
/// <param name="whereList">条件集合</param>
/// <returns>where条件字符串</returns>
public string MakeWhere(ArrayList whereList)
{
if (whereList == null || whereList.Count == 0)
{
return "";
}
//初始化where语句
string where = " where ";
foreach (object wh in whereList)
{
where += (wh.ToString().ToLower() + " and ");
}
where = where.Substring(0, (where.Length - 4));
return where;
}
/// <summary>
/// 拆分排序集合，拼装成一个排序order by 字符串
/// </summary>
/// <param name="orderList">排序集合</param>
/// <returns>排序字符串</returns>
public string MakeOrder(ArrayList orderList)
{
if (orderList == null || orderList.Count == 0)
{
return "";
}
//初始化排序语句
string order = " order by ";
//遍历排序集合
foreach (object od in orderList)
{
order += (od.ToString().ToLower() + ",");
}
order = order.Substring(0, order.Length - 1);
return order;
}
/// <summary>
/// 获取更新数据库语句部分
/// </summary>
/// <param name="updateList">更新等式集合，如"name='tom'""age=20"</param>
/// <returns></returns>
public string MakeUpdate(object obj)
{
string uString = "";
//遍历对象的所有属性集合
foreach (System.Reflection.PropertyInfo objProperty in obj.GetType().GetProperties())
{
if (objProperty.GetValue(obj, null) == null)
{
uString += (objProperty.Name + "= null,");
}
else
{
//获取当前的属性名称的长度
int length = objProperty.Name.Length;
//根据参数对象的类型，这里需要所有的数据库建立时，实体类的名称+“_id”
string id = obj.GetType().ToString().ToLower() + "_id";
//截取和当前属性名称一样长度的
string ids = id.Substring(id.Length - length);
//获取当前属性名称
string s = objProperty.Name;
//这里做的是更新，但是主键XXX_id是不允许被修改的，所以要判断不是XXXX_id才可以，出去id那个字段
if(!(s==ids))
{
if (objProperty.GetValue(obj, null).GetType().ToString() == "System.String" || objProperty.GetValue(obj, null).GetType().ToString() == "System.DateTime")
{
uString += (objProperty.Name + "= '" + objProperty.GetValue(obj, null) + "',");
}
else
{
uString += (objProperty.Name + "= " + objProperty.GetValue(obj, null) + ",");
}
}
}
}
uString = uString.Substring(0, uString.Length - 1);
return uString;
}
/// <summary>
/// 生成批量修改的数据库语句
/// </summary>
/// <param name="updateList">条件集合</param>
/// <returns>返回修改数据库语句部分</returns>
public string MakeManyUpdate(ArrayList updateList)
{
if(updateList == null || updateList.Count == 0)
{
return "";
}
string update = "";
foreach (object ul in updateList)
{
update += (ul.ToString().ToLower() + ",");
}
update = update.Substring(0, update.Length - 1);
return update;
}
/// <summary>
/// 获取对象的所有属性，放入一个集合中
/// </summary>
/// <param name="o">对象</param>
/// <returns>对象的属性集合</returns>
public ArrayList GetObjectProperties(object o)
{
ArrayList pList = new ArrayList();
PropertyInfo[] pis = o.GetType().GetProperties();
foreach (PropertyInfo pi in pis)
{
pList.Add(pi.Name);
}
return pList;
}
/// <summary>
/// 查询部分字段信息时，字段的连接
/// </summary>
/// <param name="fieldList">字段集合</param>
/// <returns>字段字符串</returns>
public string MakeField(ArrayList fieldList)
{
if (fieldList == null || fieldList.Count == 0)
{
return " * ";
}
string field = "";
foreach (object fl in fieldList)
{
field += (fl.ToString().ToLower() + ",");
}
field = field.Substring(0, field.Length - 1) ;
return field;
}
/// <summary>
/// 将分组集合，组合成分组字符串
/// </summary>
/// <param name="groupList">分组集合</param>
/// <returns>分组字符串</returns>
public string MakeGroup(ArrayList groupList)
{
if (groupList == null || groupList.Count == 0)
{
return " ";
}
string group = " group by ";
foreach (object gl in groupList)
{
group += (gl.ToString().ToLower() + ",");
}
group = group.Substring(0, group.Length - 1);
return group;
}
/// <summary>
/// 将值的集合,组合成值的字符串
/// </summary>
/// <param name="valueList">进行数据库插入时值集合</param>
/// <returns>值字符串</returns>
public string MakeValue(ArrayList valueList)
{
if (valueList == null || valueList.Count == 0)
{
return "";
}
string valueString = "";
for (int i = 0; i < valueList.Count; i++)
{
if (valueList[i] == null)
{
valueString += "null,";
}
else
{
if (valueList[i].GetType().ToString() == "System.String" || valueList[i].GetType().ToString() == "System.DateTime")
{
valueString += "'" + valueList[i].ToString().ToLower() + "',";
}
else
{
valueString += (valueList[i].ToString().ToLower() + ",");
}
}
}
valueString = valueString.Substring(0, valueString.Length - 1);
valueString = " values (" + valueString + ")";
return valueString;
}
/// <summary>
/// 根据对象，和datatable获得集合
/// </summary>
/// <param name="obj">对象</param>
/// <param name="dt">虚拟表</param>
/// <returns>对象对应类型的集合</returns>
public ArrayList getListByObjectDatable(object obj,DataTable dt)
{
//创建集合以备返回
ArrayList objList = new ArrayList();
//得到对象的反射类型
System.Reflection.PropertyInfo[] p = obj.GetType().GetProperties();
//遍历dataview
foreach (DataRow dr in dt.Rows)
{
foreach (System.Reflection.PropertyInfo pInfo in p)
{
string s = (dr[pInfo.Name.ToLower()]).GetType().ToString();
if ((dr[pInfo.Name.ToLower()]).GetType().ToString()!="System.DBNull")
{
pInfo.SetValue(obj, dr[pInfo.Name.ToLower()], null);
}
}
objList.Add(obj);
}
//返回集合
return objList;
}
}
/// <summary>
/// 封装数据库操作条件的类
/// </summary>
public class Conditions
{
/// <summary>
/// 条件类的空参构造
/// </summary>
public Conditions(){}
/// <summary>
/// 条件类全参构造
/// </summary>
/// <param name="fieldList">字段查询目标集合</param>
/// <param name="whereList">条件集合</param>
/// <param name="groupList">分组集合</param>
/// <param name="orderList">条件集合</param>
/// <param name="updateList">修改时的集合，里面的内容如："name='mary'"或者"age=23"</param>
/// <param name="valueList">执行数据库插入操作的时候，值集合</param>
/// <param name="obj">用户传入的对象</param>
/// <author>成红雷</author>
/// <date>2010-04-27</date>
public Conditions(ArrayList fieldList, ArrayList whereList, ArrayList groupList, ArrayList orderList, ArrayList updateList, ArrayList valueList, object obj)
{
this.TableName = tableName;
this.FieldList = fieldList;
this.WhereList = whereList;
this.GroupList = groupList;
this.OrderList = orderList;
this.UpdateList = updateList;
this.ValueList = valueList;
this.Obj = obj;
}
//表名
private string tableName;
public string TableName
{
get { return tableName; }
set { tableName = value; }
}
//字段，数据库查询目标
private ArrayList fieldList = null;
public ArrayList FieldList
{
get { return fieldList; }
set { fieldList = value; }
}
//条件集合
private ArrayList whereList = null;
public ArrayList WhereList
{
get { return whereList; }
set { whereList = value; }
}
//分组集合
private ArrayList groupList = null;
public ArrayList GroupList
{
get { return groupList; }
set { groupList = value; }
}
//排序集合
private ArrayList orderList = null;
public ArrayList OrderList
{
get { return orderList; }
set { orderList = value; }
}
//执行数据库修改操作的，条件集合，形如："name='sssss'"
private ArrayList updateList = null;
public ArrayList UpdateList
{
get { return updateList; }
set { updateList = value; }
}
//执行数据库插入的时候，所提供的值集合
private ArrayList valueList = null;
public ArrayList ValueList
{
get { return valueList; }
set { valueList = value; }
}
private object obj = null;
public object Obj
{
get { return obj; }
set { obj = value; }
}
}
}
