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; }
        }

    }
}