首先声明两点：
1.我一般DAL层返回的集合都是IList不是DataTabel,因为我认为既然用了.net那就应该返回强类型，要不然要类，要对象，要属性干嘛。
2.我读取数据喜欢用IDataReader,个人认为不占内存比较快。
3.我不喜欢用框架
4.请支持我的网站: 好游戏网
以上两点都是个人观点，如果您认为以上两点是错误的，那么也就没有看下去的必要了。
正因为以上两点我们经常反复的写诸如一下代码：
代码 /// < 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 就不贴了，一抓一大堆，我的也是自己写的也不一定好，如果需要全部代码请给我留言，我没找到怎么上传源码。
以上都是个人观点仅供参考。
支持我请先支持我的网站： 好游戏网
注：代码是给需要的人看的，本人水平有限也不是作家请不要妄加评论，谢谢。