ADO.NET通用DBHelper类，自己这两天琢磨的，和大家分享下，主要是希望有高手能指点下，好让我改进！
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Data.OracleClient;using System.Data.OleDb;using System.Data.Odbc;using System.Reflection;namespace WebDAL{ public class DBHelper { #region 属性 //项目中添加如下引用 //System.Configuration //System.Data.OracleClient //web.config中增加如下节点 //<appSettings> //<add key="DBType" value="SQLServer"/>配置数据库类型SQLServer||OleDb||ODBC||Oracle //<add key="SQLServer" value="连库字符串"/>配置该数据库类型对应的连库字符串 //<add key="assemblyName" value="Entity"/>指定实体类的命名空间 //</appSettings> //获取连接字符串和数据库类型 //从Web.config文件中动态获取 private static string conType=ConfigurationManager.AppSettings["DBType"].ToString(); private static string constr = ConfigurationManager.AppSettings[conType].ToString(); //动态获取程序集名称，以备反射时所用 private static string assemblyName = ConfigurationManager.AppSettings["assemblyName"].ToString(); #endregion #region 私有方法 /// <summary> /// 根据数据库类型，获取对应数据库的连接 /// </summary> /// <returns>连接接口</returns> private static IDbConnection GetConnection() { IDbConnection con = null; if (conType == DBType.SQLServer.ToString()) { con = new SqlConnection(constr); } else if (conType == DBType.Oracle.ToString()) { con = new OracleConnection(constr); } else if (conType == DBType.OleDb.ToString()) { con = new OleDbConnection(constr); } else if (conType == DBType.ODBC.ToString()) { con = new OdbcConnection(constr); } else { con = new SqlConnection(constr); } return con; } /// <summary> /// 根据数据库类型，获取对应Command对象 /// </summary> /// <param name="cmdText">SQL语句或存储过程名</param> /// <param name="cmdType">SQL命令类型</param> /// <param name="con">连接对象</param> /// <param name="param">SQL命令参数数组</param> /// <returns>Command接口对象</returns> private static IDbCommand GetCommand(string commandText,CommandType commandType,IDbConnection con, params IDbDataParameter[] param) { IDbCommand cmd = null; if (conType == DBType.SQLServer.ToString()) { cmd = new SqlCommand(commandText,con as SqlConnection); } else if (conType == DBType.Oracle.ToString()) { cmd = new OracleCommand(commandText, con as OracleConnection); } else if (conType == DBType.OleDb.ToString()) { cmd = new OleDbCommand(commandText,con as OleDbConnection); } else if (conType == DBType.ODBC.ToString()) { cmd = new OdbcCommand(commandText,con as OdbcConnection); } else { cmd = new SqlCommand(commandText, con as SqlConnection); } cmd.CommandType = commandType; if (param != null) { cmd.Parameters.Add(param); } return cmd; } /// <summary> /// 执行返回一条记录的泛型集合对象 /// </summary> /// <typeparam name="T">泛型类型</typeparam> /// <param name="reader">Reader对象，用于读取数据结果集</param> /// <returns>泛型对象</returns> private static T ExexuteDataReader<T>(IDataReader reader) { T obj = default(T); try { Type type = typeof(T); obj = (T)Assembly.Load(DBHelper.assemblyName).CreateInstance(assemblyName + "." + type.Name); PropertyInfo[] propertyInfos = type.GetProperties(); foreach (PropertyInfo propertyinfo in propertyInfos) { for (int i = 0; i < reader.FieldCount; i++) { string filedName = reader.GetName(i); if (filedName.ToLower() == propertyinfo.Name.ToLower()) { Object value = reader[propertyinfo.Name]; if (value != null && value != DBNull.Value) { propertyinfo.SetValue(obj, value, null); break; } } } } } catch (Exception ex) { throw ex; } return obj; } #endregion
using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.Common;using System.Reflection;namespace DBAnalyzer{ /// <summary> /// 数据库服务类 /// </summary> public class DbProvider : IDisposable { #region 属性 /// <summary> /// 数据库连接 /// </summary> protected DbConnection Connection { get; set; } /// <summary> /// 数据库命令对象 /// </summary> protected DbCommand Command { get; set; } /// <summary> /// 数据库适配器对象 /// </summary> protected DbDataAdapter Adapter { get; set; } #endregion /// <summary> /// 错误信息 /// </summary> public string ErrorMessage { get; set; } #region 方法 /// <summary> /// 创建类对象（System.Data.dll） /// </summary> /// <param name="dbType">SqlClient.Sql;OleDb.OleDb;Odbc.Odbc</param> /// <param name="classType">Connection;DataAdapter</param> /// <returns></returns> private object CreateInstance(string dbType, string classType) { return Assembly.LoadFile(string.Format("{0}\\System.Data.dll", Global.PhysicalPath)) .CreateInstance(string.Format("System.Data.{0}{1}", dbType, classType)); } public bool Connect(string dbType, string connectionString) { this.ErrorMessage = string.Empty; try { //利用反射创建实例 this.Connection = (DbConnection)this.CreateInstance(dbType, "Connection"); this.Connection.ConnectionString = connectionString; //打开 this.Connection.Open(); #region 生成一系列对象 this.Command = this.Connection.CreateCommand(); this.Adapter = (DbDataAdapter)this.CreateInstance(dbType, "DataAdapter"); this.Adapter.SelectCommand = this.Command; #endregion return true; } catch (Exception error) { this.ErrorMessage = error.Message; return false; } } #region 查询 /// <summary> /// 查询 /// </summary> /// <param name="type"></param> /// <param name="timeout">超时（秒）</param> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> private DataTable Query(CommandType type, int timeout, string sql, params DbParameter[] parameters) { this.ErrorMessage = string.Empty; try { //命令 this.Command.CommandText = sql; this.Command.CommandType = type; this.Command.CommandTimeout = timeout; this.Command.Parameters.Clear(); if (parameters.Length > 0) { this.Command.Parameters.AddRange(parameters); } //填充数据集 DataTable result = new DataTable(); this.Adapter.Fill(result); //返回 return result; } catch (Exception error) { this.ErrorMessage = error.Message; return null; } } /// <summary> /// SQL查询 /// </summary> /// <param name="sql"></param> /// <param name="timeout">超时（秒）</param> /// <param name="parameters"></param> /// <returns></returns> public DataTable Query(string sql, int timeout, params DbParameter[] parameters) { return this.Query(CommandType.Text, timeout, sql, parameters); } public DataTable Query(string sql, params DbParameter[] parameters) { return this.Query(CommandType.Text, 30, sql, parameters); } #endregion #endregion }}
[解决办法]
4楼的不错,更简洁
[解决办法]
不错，收藏了
[解决办法]
学习了，顶一下！
[解决办法]
我早上回复了一个帖子，参考：http://topic.csdn.net/u/20110925/09/8f715b25-adde-46a4-97f9-4cfc42d832f9.html