我写的DBHelp

首先声明两点：

1.我一般DAL层返回的集合都是IList不是DataTabel,因为我认为既然用了.net那就应该返回强类型，要不然要类，要对象，要属性干嘛。

2.我读取数据喜欢用IDataReader,个人认为不占内存比较快。

3.我不喜欢用框架

4.请支持我的网站:http://www.haoy7.com 好游戏网

以上两点都是个人观点，如果您认为以上两点是错误的，那么也就没有看下去的必要了。

正因为以上两点我们经常反复的写诸如一下代码：

代码

        /// <summary>返回某类别n条最新信息(更新时间排序)
        /// </summary>
        /// <param name="top"></param>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public IList<BookInfo> GetTypeTopList(int top,int typeId)
        {
            string sql = string.Format("select top {0} info_id,type_id,type_name,info_name from book_info where type_id={1}",top,typeId);
            IList<BookInfo> modelList = new List<BookInfo>();
            BookInfo model = null;
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExcuteDataReaer(SqlHelper.connectionString,CommandType.Text,sql,null);
                while (dr.Read())
                {
                    model = new BookInfo();
                    model.Name=dr["info_name"].ToString();
                    model.Id=(int)dr["info_id"];
                    model.TypeId = (int)dr["type_id"];
                    model.TypeName=dr["type_name"].ToString();
                    modelList.Add(model);
                }
            }
            catch { }
            finally
            {
                SqlHelper.CloseDataRead(dr);
            }
            return modelList;
        }

注意：sql语句参数是int类型所以没用SqlParameter方式。

写时间长了，我就在想为什么我总是写同样的代码呢？于是我就想能不能有一个通用方法，让我不再写这些重复的东西，于是开始构思，它应该是这样的：

1.自动打开数据

2.根据客户输入的sql,自动填充客户指定的实体集合

3.自动关闭数据库

4.最好是可以跨数据库例如：sqlserver.Oracle

总结：调用的客户只需要传入sql语句和参数，并指定实体，就能根据sql语句返回实体结合，至于其它事客户不用关心

实现代码如下：

注释都很清楚不在解释了

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
/// <summary>数据库操作装饰类接口
/// 郝国微 2010-11-12 15:25
/// </summary>
namespace MES.Lib
{
    public interface IDecorator
    {
        /// <summary>获取数据集合
        /// </summary>
        /// <param name="connectionString">连接字符串（xml配置节点名）</param>
        /// <param name="CommandType">sql语句类型</param>
        /// <param name="sql">sql语句</param>
        /// <param name="DicCommandParameters">参数集合</param>
        /// <returns>结果集</returns>
        IList<T> ExcuteList<T>(string connectionString, CommandType CommandType, string sql, Dictionary<string, object> DicCommandParameters) where T:new();
        /// <summary>获取详细信息
        /// </summary>
        /// <param name="connectionString">连接字符串（xml配置节点名）</param>
        /// <param name="CommandType">sql语句类型</param>
        /// <param name="sql">sql语句</param>
        /// <param name="DicCommandParameters">参数集合</param>
        /// <returns>结果集</returns>
        T ExcuteModel<T>(string connectionString, CommandType CommandType, string sql, Dictionary<string, object> DicCommandParameters) where T : new();
        /// <summary>执行sql语句返回影响行数
        /// </summary>
        /// <param name="connectionString">连接字符串（xml配置节点名）</param>
        /// <param name="CommandType">sql语句类型</param>
        /// <param name="sql">sql语句</param>
        /// <param name="DicCommandParameters">参数集合</param>
        /// <returns>影响行数</returns>
        int ExecuteNonQuery(string connectionString, CommandType CommandType, string sql, Dictionary<string, object> DicCommandParameters);
        /// <summary>通用的sql执行方法,返回dataTable
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行sql的类型 StoredProcedure存贮过程，TableDirect表名，Text文本</param>
        /// <param name="cmdText">sql语句</param>
        /// <param name="commandParameters">存有字段的sql参数数组</param>
        /// <returns>DataTable</returns>
        DataTable ExcuteDataTable(string connectionString, CommandType cmdType, string cmdText, Dictionary<string, object> DicCommandParameters);
        /// <summary>通用的sql执行方法,返回结果集的第一行第一列
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行sql的类型 StoredProcedure存贮过程，TableDirect表名，Text文本</param>
        /// <param name="cmdText">sql语句</param>
        /// <param name="commandParameters">存有字段的sql参数数组</param>
        /// <returns>object</returns>
        object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, Dictionary<string, object> DicCommandParameters);
        /// <summary>参数集合
        /// </summary>
        /// <returns></returns>
        DbParameter[] GetParameters();
    }
}

sqlserver实现

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
/// <summary> SqlServer数据库操作装饰类
/// 郝国微 2010-11-12 15:25
/// </summary>
namespace MES.Lib
{
    public class SqlDecorator : IDecorator
    {
        /// <summary>获取数据集合
        /// </summary>
        /// <param name="connectionString">连接字符串（xml配置节点名）</param>
        /// <param name="CommandType">sql语句类型</param>
        /// <param name="sql">sql语句</param>
        /// <param name="DicCommandParameters">参数集合</param>
        /// <returns></returns>
        public IList<T> ExcuteList<T>(string connectionString, CommandType CommandType, string sql, Dictionary<string, object> DicCommandParameters) where T:new()
        {
            DBAdapter<T> adapter = new DBAdapter<T>();
            IList<T> list = new List<T>();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExcuteDataReaer(SqlHelper.GetConnectionString(connectionString), CommandType, sql, GetParameters(DicCommandParameters));
                list = adapter.GetList(dr);
            }
            catch (Exception ex) { }
            finally
            {
                SqlHelper.CloseDataRead(dr);
            }
            return list;
        }
        /// <summary>执行sql语句返回影响行数
        /// </summary>
        /// <param name="connectionString">连接字符串（xml配置节点名）</param>
        /// <param name="CommandType">sql语句类型</param>
        /// <param name="sql">sql语句</param>
        /// <param name="DicCommandParameters">参数集合</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string connectionString, CommandType CommandType, string sql, Dictionary<string, object> DicCommandParameters)
        {
            int row = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnectionString(connectionString), CommandType, sql, GetParameters(DicCommandParameters));
            return row;
        }
        /// <summary>通用的sql执行方法,返回dataTable
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行sql的类型 StoredProcedure存贮过程，TableDirect表名，Text文本</param>
        /// <param name="cmdText">sql语句</param>
        /// <param name="commandParameters">存有字段的sql参数数组</param>
        /// <returns>DataTable</returns>
        public DataTable ExcuteDataTable(string connectionString, CommandType cmdType, string cmdText, Dictionary<string, object> DicCommandParameters)
        {
            DataTable dt = SqlHelper.ExcuteDataTable(SqlHelper.GetConnectionString(connectionString), cmdType, cmdText, GetParameters(DicCommandParameters));
            return dt;
        }
        /// <summary>通用的sql执行方法,返回结果集的第一行第一列
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行sql的类型 StoredProcedure存贮过程，TableDirect表名，Text文本</param>
        /// <param name="cmdText">sql语句</param>
        /// <param name="commandParameters">存有字段的sql参数数组</param>
        /// <returns>DataTable</returns>
        public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, Dictionary<string, object> DicCommandParameters)
        {
            object obj = SqlHelper.ExecuteScalar(SqlHelper.GetConnectionString(connectionString), cmdType, cmdText, GetParameters(DicCommandParameters));
            return obj;
        }
        /// <summary>返回字典转换成的参数集合
        /// </summary>
        /// <param name="DicCommandParameters"></param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(Dictionary<string, object> DicCommandParameters)
        {
            Parameters = new SqlParameter[DicCommandParameters.Count];
            int i = 0;
            foreach (KeyValuePair<string, object> temp in DicCommandParameters)
            {
                Parameters[i] = new SqlParameter(temp.Key, temp.Value);
            }
            return Parameters;
        }
        private SqlParameter[] Parameters
        {
            set;
            get;
        }
        /// <summary>参数集合
        /// </summary>
        /// <returns></returns>
        public DbParameter[] GetParameters()
        {
            return (DbParameter[])Parameters;
        }
        /// <summary>获取详细信息
        /// </summary>
        /// <param name="connectionString">连接字符串（xml配置节点名）</param>
        /// <param name="CommandType">sql语句类型</param>
        /// <param name="sql">sql语句</param>
        /// <param name="DicCommandParameters">参数集合</param>
        /// <returns>结果集</returns>
        public T ExcuteModel<T>(string connectionString, CommandType CommandType, string sql, Dictionary<string, object> DicCommandParameters) where T : new()
        {
            DBAdapter<T> adapter = new DBAdapter<T>();
            T model = new T();
            SqlDataReader dr = null;
            try
            {
                dr = SqlHelper.ExcuteDataReaer(SqlHelper.GetConnectionString(connectionString), CommandType, sql, GetParameters(DicCommandParameters));
                model = adapter.GetModel(dr);
            }
            catch { }
            finally
            {
                SqlHelper.CloseDataRead(dr);
            }
            return model;
        }
    }
}

oracle实现

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Data.Common;
/// <summary>Oracle数据库操作装饰类
/// 郝国微 2010-11-12 15:25
/// </summary>
namespace MES.Lib
{
    public class OracleDecorator : IDecorator
    {
        /// <summary>获取数据集合
        /// </summary>
        /// <param name="connectionString">连接字符串（xml配置节点名）</param>
        /// <param name="CommandType">sql语句类型</param>
        /// <param name="sql">sql语句</param>
        /// <param name="DicCommandParameters">参数集合</param>
        /// <returns></returns>
        public IList<T> ExcuteList<T>(string connectionString, CommandType CommandType, string sql, Dictionary<string, object> DicCommandParameters) where T : new()
        {
            DBAdapter<T> adapter = new DBAdapter<T>();
            IList<T> list = new List<T>();
            OracleDataReader dr = null;
            try
            {
                dr = OracleHelper.ExcuteDataReaer(OracleHelper.GetConnectionString(connectionString), CommandType, sql, GetParameters(DicCommandParameters));
                list = adapter.GetList(dr);
            }
            catch { }
            finally
            {
                OracleHelper.CloseDataRead(dr);
            }
            return list;
        }
        /// <summary>执行sql语句返回影响行数
        /// </summary>
        /// <param name="connectionString">连接字符串（xml配置节点名）</param>
        /// <param name="CommandType">sql语句类型</param>
        /// <param name="sql">sql语句</param>
        /// <param name="DicCommandParameters">参数集合</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string connectionString, CommandType CommandType, string sql, Dictionary<string, object> DicCommandParameters)
        {
            int row = OracleHelper.ExecuteNonQuery(OracleHelper.GetConnectionString(connectionString), CommandType, sql, GetParameters(DicCommandParameters));
            return row;
        }
        /// <summary>通用的sql执行方法,返回dataTable
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行sql的类型 StoredProcedure存贮过程，TableDirect表名，Text文本</param>
        /// <param name="cmdText">sql语句</param>
        /// <param name="commandParameters">存有字段的sql参数数组</param>
        /// <returns>DataTable</returns>
        public DataTable ExcuteDataTable(string connectionString, CommandType cmdType, string cmdText, Dictionary<string, object> DicCommandParameters)
        {
            DataTable dt = OracleHelper.ExcuteDataTable(OracleHelper.GetConnectionString(connectionString), cmdType, cmdText, GetParameters(DicCommandParameters));
            return dt;
        }
        /// <summary>通用的sql执行方法,返回结果集的第一行第一列
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行sql的类型 StoredProcedure存贮过程，TableDirect表名，Text文本</param>
        /// <param name="cmdText">sql语句</param>
        /// <param name="commandParameters">存有字段的sql参数数组</param>
        /// <returns>DataTable</returns>
        public object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, Dictionary<string, object> DicCommandParameters)
        {
            object obj = OracleHelper.ExecuteScalar(OracleHelper.GetConnectionString(connectionString), cmdType, cmdText, GetParameters(DicCommandParameters));
            return obj;
        }
        /// <summary>返回字典转换成的参数集合
        /// </summary>
        /// <param name="DicCommandParameters"></param>
        /// <returns></returns>
        private OracleParameter[] GetParameters(Dictionary<string, object> DicCommandParameters)
        {
            Parameters = new OracleParameter[DicCommandParameters.Count];
            int i = 0;
            foreach (KeyValuePair<string, object> temp in DicCommandParameters)
            {
                Parameters[i] = new OracleParameter();
                Parameters[i].ParameterName = temp.Key;
                Parameters[i].Value = temp.Value;
                Parameters[i].Direction = ParameterDirection.InputOutput;
                if (temp.Value == null)
                {
                    Parameters[i].OracleType = OracleType.Cursor;
                    Parameters[i].Direction = ParameterDirection.Output;
                }
                i++;
            }
            return Parameters;
        }
        private OracleParameter[] Parameters
        {
            set;
            get;
        }
        /// <summary>参数集合
        /// </summary>
        /// <returns></returns>
        public DbParameter[] GetParameters()
        {
            return (DbParameter[])Parameters;
        }
        /// <summary>获取详细信息
        /// </summary>
        /// <param name="connectionString">连接字符串（xml配置节点名）</param>
        /// <param name="CommandType">sql语句类型</param>
        /// <param name="sql">sql语句</param>
        /// <param name="DicCommandParameters">参数集合</param>
        /// <returns>结果集</returns>
        public T ExcuteModel<T>(string connectionString, CommandType CommandType, string sql, Dictionary<string, object> DicCommandParameters) where T : new()
        {
            DBAdapter<T> adapter = new DBAdapter<T>();
            T model=new T();
            OracleDataReader dr = null;
            try
            {
                dr = OracleHelper.ExcuteDataReaer(OracleHelper.GetConnectionString(connectionString), CommandType, sql, GetParameters(DicCommandParameters));
                model = adapter.GetModel(dr);
            }
            catch { }
            finally
            {
                OracleHelper.CloseDataRead(dr);
            }
            return model;
        }
    }
}

数据适配器

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Reflection;
using System.Data.Odbc;
using System.Data.Common;
/// <summary>数据适配器
///郝国微 2010-11-12 15:25
/// </summary>
namespace MES.Lib
{
    public class DBAdapter<T> where T : new()
    {
        /// <summary>获取集合
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public IList<T> GetList(DbDataReader dr)
        {
            IList<T> modelList = new List<T>();
            if (dr != null)
            {
                try
                {
                    T t;
                    while (dr.Read())
                    {
                        t = new T();
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            t.GetType().GetProperty(dr.GetName(i), BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase).SetValue(t, dr[i], null);
                        }
                        modelList.Add(t);
                    }
                }
                catch (Exception ex) { }
            }
            return modelList;
        }
        /// <summary>获取实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public T GetModel(DbDataReader dr)
        {
            T model = new T();
            if (dr != null)
            {
                try
                {
                    if (dr.Read())
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            model.GetType().GetProperty(dr.GetName(i), BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase).SetValue(model, dr[i], null);
                        }
                    }
                }
                catch (Exception ex) { }
            }
            return model;
        }
    }
}

客户端调用：

代码

        IDecorator dbHelp = new SqlDecorator();
        public IList<Model.Test> GetList()
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();
            dic.Add("id", 5);
            string sql = "select tabl_id as id,tabl_name as name,tabl_Time as time from test_table where tabl_id>:id";
            IList<Model.Test> list = dbHelp.ExcuteList<Model.Test>("test", CommandType.Text, sql, dic);
            return list;
        }

OracleHelper.cs和SqlHelper.cs 就不贴了，一抓一大堆，我的也是自己写的也不一定好，如果需要全部代码请给我留言，我没找到怎么上传源码。

以上都是个人观点仅供参考。

支持我请先支持我的网站：http://www.haoy7.com 好游戏网

注：代码是给需要的人看的，本人水平有限也不是作家请不要妄加评论，谢谢。

posted @ 2011-01-25 10:42 野百合也有春天324 阅读(857) 评论(5) 编辑收藏举报

野百合的春天

我写的DBHelp

公告