数据库操作通用方法类

public class DbMethods
    {
        /// <summary>
        /// 获取数据库
        /// </summary>zlf 2014-12-11
        /// <param name="dbCode">用户：u 项目：p 捐赠：d 公共：c 活动：a 日志：l</param>
        public static Database GetDb(string dbCode)
        {
            Database db = null;
            switch (dbCode)
            {
                case "a":
                    db = Db.ActivityDb;
                    break;
                case "c":
                    db = Db.CommonDb;
                    break;
                case "d":
                    db = Db.DonationDb;
                    break;
                case "p":
                    db = Db.ProjectsDb;
                    break;
                case "u":
                    db = Db.UsersDb;
                    break;
                case "l":
                    db = Db.LogDb;
                    break;
                default:
                    break;
            }
            return db;
        }
        /// <summary>
        /// 验证sql匹配条件是否正确(若以and开头则自动去除)
        /// </summary>zlf 2014-12-10
        /// <param name="where">sql匹配条件</param>
        public static string CheckWhere(string where)
        {
            string str = where.TrimStart();//去除前置空格
            if (str.ToLower().IndexOf("and ") == 0)//若以and开头则自动去除第一个and
            {
                where = str.Substring(4);//若要保留前面一个空格，可以改为3
            }
            return where;
        }
        /// <summary>
        /// 根据条件获取指定表中指定列的值
        /// </summary>zlf 2014-12-10
        /// <param name="useDb">数据库 用户：u 项目：p 捐赠：d 公共：c 活动：a</param>
        /// <param name="ColumnCode">列编码</param>
        /// <param name="tbName">数据表名</param>
        /// <param name="strWhere">匹配条件</param>
        /// <returns>满足条件的第一行的值</returns> 
        public static string GetColumnValue(string useDb, string ColumnCode, string tbName, string strWhere)
        {
            string value = String.Empty;
            if (String.IsNullOrEmpty(ColumnCode) || String.IsNullOrEmpty(tbName))
            {
                return value;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 " + ColumnCode + " from " + tbName);

            strWhere = CheckWhere(strWhere);
            if (!String.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            try
            {
                Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDb(useDb);
                if (db != null)
                {
                    //ExecuteScalar执行查询，并返回查询所返回的结果集中第一行的第一列或空引用（如果结果集为空).忽略其他列或行
                    object obj = db.ExecuteScalar(CommandType.Text, strSql.ToString());
                    if (obj != null)
                        value = obj.ToString();
                }
            }
            catch //(Exception ex)
            { }
            return value;
        }
        /// <summary>
        /// 根据条件获取数据集
        /// </summary>zlf 2014-12-11
        /// <param name="useDb">数据库 用户：u 项目：p 捐赠：d 公共：c 活动：a</param>
        /// <param name="fileds">列集合</param>
        /// <param name="tbName">数据表名</param>
        /// <param name="strWhere">匹配条件</param>
        /// <returns>满足条件的数据集</returns> 
        public static DataSet GetList(string useDb, string fileds, string tbName, string strWhere)
        {
            DataSet ds = null;
            if (String.IsNullOrEmpty(fileds) || String.IsNullOrEmpty(tbName))
            {
                return ds;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select " + fileds + " from " + tbName);

            strWhere = CheckWhere(strWhere);
            if (!String.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            try
            {
                Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDb(useDb);
                if (db != null)
                {
                    ds = db.ExecuteDataSet(CommandType.Text, strSql.ToString());
                }
            }
            catch //(Exception ex)
            { }
            return ds;
        }
        /// <summary>
        /// 根据条件获取数据集
        /// </summary>zlf 2014-12-12
        /// <param name="useDb">数据库 GetDb()</param>
        /// <param name="fileds">列集合</param>
        /// <param name="tbName">数据表名</param>
        /// <param name="strWhere">匹配条件</param>
        /// <returns>满足条件的数据集</returns> 
        /// <param name="orderby">排序字段 如：addtime desc</param>
        /// <param name="pageIndex">当前页号</param>
        /// <param name="pageSize">每页数据量</param>
        public static DataSet GetListByPage(string useDb, string fileds, string tbName, string strWhere, string orderby, int pageSize, int pageIndex)
        {
            DataSet ds = null;
            if (String.IsNullOrEmpty(fileds) || String.IsNullOrEmpty(tbName) || String.IsNullOrEmpty(orderby))
            {
                return ds;
            }

            if (pageSize < 1) pageSize = 10;
            if (pageIndex < 1) pageIndex = 1;
            int start = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (");
            strSql.Append("select ROW_NUMBER() OVER (ORDER BY " + orderby + ") as row," + fileds + " from " + tbName);

            strWhere = CheckWhere(strWhere);
            if (!String.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }

            strSql.Append(") as T where T.row between " + start + " and " + end);

            try
            {
                Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDb(useDb);
                if (db != null)
                {
                    ds = db.ExecuteDataSet(CommandType.Text, strSql.ToString());
                }
            }
            catch //(Exception ex)
            { }
            return ds;
        }
        /// <summary>
        /// 根据条件获取指定表中记录总数
        /// </summary>zlf 2014-12-11
        /// <param name="useDb">数据库 用户：u 项目：p 捐赠：d 公共：c 活动：a</param>
        /// <param name="tbName">数据表名</param>
        /// <param name="strWhere">匹配条件</param>
        /// <returns>满足条件的记录总数</returns> 
        public static int GetRecordCount(string useDb, string tbName, string strWhere)
        {
            int value = 0;
            if (String.IsNullOrEmpty(tbName))
            {
                return value;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  count(1) from " + tbName);

            strWhere = CheckWhere(strWhere);
            if (!String.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            try
            {
                Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDb(useDb);
                if (db != null)
                {
                    //ExecuteScalar执行查询，并返回查询所返回的结果集中第一行的第一列或空引用（如果结果集为空).忽略其他列或行
                    object obj = db.ExecuteScalar(CommandType.Text, strSql.ToString());
                    if (obj != null)
                        value = Convert.ToInt32(obj);
                }
            }
            catch //(Exception ex)
            { }
            return value;
        }
        /// <summary>
        /// 更新指定数据库指定表中信息
        /// </summary>zlf 2014-12-10
        /// <param name="useDb">数据库 用户：u 项目：p 捐赠：d 公共：c 活动：a</param>
        /// <param name="ColAndVal">列+值(col = ‘val‘,col2=‘val2‘)</param>
        /// <param name="tbName">数据表名</param>
        /// <param name="strWhere">匹配条件</param>
        /// <returns>是否更新成功</returns> 
        public static bool SetValue(string useDb, string ColAndVal, string tbName, string strWhere)
        {
            bool value = false;
            if (String.IsNullOrEmpty(ColAndVal) || String.IsNullOrEmpty(tbName))
            {
                return false;
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tbName + " set  ");
            strSql.Append(ColAndVal);

            strWhere = CheckWhere(strWhere);
            if (!String.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            try
            {
                Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDb(useDb);
                if (db != null)
                {
                    int rows = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
                    return rows > 0;
                }
            }
            catch //(Exception ex)
            { }
            return value;
        }
        /// <summary>
        /// 对指定数据表中批量插入记录(不支持html数据)
        /// </summary>zlf 2014-12-24
        /// <param name="useDb">数据库 用户：u 项目：p 捐赠：d 公共：c 活动：a</param>
        /// <param name="tbName">数据表名</param>
        /// <param name="fields">字段集合 如：col1,col2,...</param>
        /// <param name="values">值集合(值中不能包含",") 如：‘val1‘,‘val2‘,...</param>
        public static bool InsertRecord(string useDb, string tbName, string fields, List<string> values)
        {
            bool value = false;
            if (String.IsNullOrEmpty(tbName) || String.IsNullOrEmpty(fields) || values.Count < 1)
            {
                return false;
            }
            int colLength = fields.Split(‘,‘).Length;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into " + tbName + "(" + fields + ") ");

            bool equalLength = false;//字段长度是否与值长度是否相同
            for (int i = 0; i < values.Count; i++)
            {
                if (values[i].Split(‘,‘).Length == colLength)
                {
                    equalLength = true;
                    if (i == 0)
                    {
                        strSql.Append(" select " + values[i]);
                    }
                    else
                    {
                        strSql.Append(" union all ");
                        strSql.Append(" select " + values[i]);
                    }
                }
            }
            if (equalLength)
            {
                try
                {
                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDb(useDb);
                    if (db != null)
                    {
                        int rows = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
                        return rows > 0;
                    }
                }
                catch 
                { }
            }

            return value;
        }
        /// <summary>
        /// 添加一条记录(不支持html数据)
        /// </summary>zlf 2015-01-13
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="useDb">数据库 用户：u 项目：p 捐赠：d 公共：c 活动：a</param>
        /// <param name="tbName">数据表名</param>
        /// <param name="info">数据实体</param>
        public static bool AddRecord<T>(string useDb, string tbName, T info) where T : class
        {
            bool result = false;
            if (String.IsNullOrEmpty(tbName) || info == null)
            {
                return false;
            }

            var type = typeof(T);
            var fields = type.GetProperties();

            StringBuilder sb = new StringBuilder();
            sb.Append("insert into " + tbName + "({0}) values ({1})");
            var columns = String.Empty;
            var values = String.Empty;
            foreach (var p in fields)
            {
                var v = p.GetValue(info, null);
                if (v == null) continue;

                if (String.IsNullOrEmpty(columns))
                {
                    columns += p.Name;
                    values += "‘" + v.ToString().Replace("‘", "") + "‘";
                }
                else
                {
                    columns += "," + p.Name;
                    values += ",‘" + v.ToString().Replace("‘", "") + "‘";
                }
            }
            if (!String.IsNullOrEmpty(columns))
            {
                var sql = string.Format(sb.ToString(), columns, values);
                try
                {
                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDb(useDb);
                    if (db != null)
                    {
                        int rows = db.ExecuteNonQuery(CommandType.Text, sql);
                        return rows > 0;
                    }
                }
                catch
                { }
            }
            return result;
        }
        /// <summary>
        /// 获取对象实体
        /// </summary>zlf 2015-02-03
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="useDb">数据库</param>
        /// <param name="tbName">数据表名</param>
        /// <param name="strWhere">匹配条件</param>
        /// <returns>数据实体</returns>
        public static T GetModel<T>(string useDb, string tbName, string strWhere) where T : class
        {
            T val = null;

            if (!String.IsNullOrEmpty(tbName))
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select top 1 * from " + tbName);

                strWhere = CheckWhere(strWhere);
                if (!String.IsNullOrEmpty(strWhere))
                {
                    strSql.Append(" where " + strWhere);
                }
                try
                {
                    Microsoft.Practices.EnterpriseLibrary.Data.Database db = GetDb(useDb);
                    if (db != null)
                    {
                        using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
                        {
                            dataReader.Read();

                            var type = typeof(T);
                            var fields = type.GetProperties();//实体属性集合

                            var columns = new List<string>();//数据列集合
                            for (int i = 0; i < dataReader.FieldCount; i++)
                            {
                                columns.Add(dataReader.GetName(i));
                            }
                            foreach (var p in fields)
                            {
                                //为实体赋值
                                if (columns.Contains(p.Name))
                                {
                                    var ovalue = dataReader[p.Name];
                                    if (ovalue == null) continue;

                                    p.SetValue(val, ovalue, null);
                                    continue;
                                }
                            }
                        }
                    }
                }
                catch //(Exception ex)
                { }
            }
            return val;
        }
    }