一、前言
现在市场上的数据比较多，日常开发中也可能是需要使用多中类型的数据，开发中也许碰到换另外一种数据库，开发完了，还是会碰到客户说要换个数据库。这都是我们程序员经常碰到的坑爹的事情。虽然坑，但是客户是老大啊。我们还是需要改的。所以嘛，这个数据库访问上要考虑的严密些。
二、思路
1、这个不管是Oracle、mssql、access啊，这都是有数据库连接、关闭、查询、修改啥的，这是都是公共的嘛，就都放在DBBase里了。
2、但是还有啊，你想啊，Oracle的参数是“:fieldName”mssql是“@fieldName”,Oracle我要用OracleConnection，mssql我要用SqlConnection等等啊，这样我就具体的数据库里把这些重写下啦。
3、外面调用的时候，我们不能直接把Oracle，或mssql就这样给别人啦，那样是不行的嘛，就用IDB吧。面向接口编程吧。
4、看是视乎都好啦。但是我们怎么使用啊。民以食为天嘛！不能看这画饼啊。所以嘛，就Dbhelp提供一个小小的工厂吧！
下面不说了，大家自己看吧
三、类结构关系
四、代码
1、DbHelp.cs
/*
* 作者：吴桂林
* 时间：2012-07-07
* 说明：希望大家文明使用，使用中遇到的问题可以通过QQ：20372268联系我，申请的是时候需要说明“Common交流”。谢谢
*
* 瑕疵：
* 1、在使用中最后决定数据库状态由用户自己控制，使用的时候调用Open()，使用完了Close()。
* 2、数据库事务建议使用TransactionScope，大家有什么好的建议一起交流。
* 3、联合查询，返回实体时怎么处理
*/
using System;
using System.Configuration;
namespace Yike.Common
{
public class DbHelp
{
public string DatabaseName
{
get;
set;
}
public DbHelp(string databaseName)
{
DatabaseName = databaseName;
}
public DbHelp()
{
DatabaseName = ConfigurationManager.AppSettings["defaultDb"].ToString();
}
public IDb Generate()
{
ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings[DatabaseName];
switch (connectionStringSettings.ProviderName)
{
case "System.Data.OracleClient": return new Oracle(connectionStringSettings);
case "System.Data.SqlClient": return new Mssql(connectionStringSettings);
case "System.Data.OleDb": return new Access(connectionStringSettings);
default: throw new Exception("Not support Database");
}
}
}
}
2、DbBase.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text.RegularExpressions;
namespace Yike.Common
{
public class DbBase : IDbConnection, IDb
{
public static DbConnection conn;
public ConnectionStringSettings ConnectionStringSetting
{
get;
set;
}
#region IDbConnection 成员
public IDbTransaction BeginTransaction(IsolationLevel il)
{
return conn.BeginTransaction(il);
}
public IDbTransaction BeginTransaction()
{
return conn.BeginTransaction();
}
public virtual void ChangeDatabase(string databaseName)
{
}
public void Close()
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
public string ConnectionString
{
get
{
return conn.ConnectionString;
}
set
{
conn.ConnectionString = value;
}
}
public int ConnectionTimeout
{
get { return conn.ConnectionTimeout; }
}
public IDbCommand CreateCommand()
{
return conn.CreateCommand();
}
public virtual IDbDataAdapter CreateDataAdapter()
{
return null;
}
string IDbConnection.Database
{
get { return conn.Database; }
}
public void Open()
{
if (conn.State != ConnectionState.Open)
conn.Open();
}
public ConnectionState State
{
get { return conn.State; }
}
#endregion
#region IDisposable 成员
public void Dispose()
{
conn.Close();
conn.Dispose();
}
#endregion
#region Command
/// <summary>
/// 执行查询，返回影响的行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
using (IDbCommand com = CreateCommand())
{
com.CommandText = sql;
return com.ExecuteNonQuery();
}
}
/// <summary>
/// 执行查询，返回影响的行数
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, List<DbParameter> parameters)
{
using (IDbCommand com = CreateCommand())
{
com.CommandText = sql;
if (parameters == null || parameters.Count == 0)
return 0;
foreach (DbParameter parameter in parameters)
com.Parameters.Add(parameter);
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
return com.ExecuteNonQuery();
}
}
/// <summary>
/// 执行查询，返回影响的行数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="entity"></param>
/// <returns></returns>
public int ExecuteNonQuery<T>(string sql, T entity) where T : class, new()
{
using (IDbCommand com = CreateCommand())
{
GetParameters(com, sql, entity);
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
return com.ExecuteNonQuery();
}
}
/// <summary>
/// 执行查询返回第一行第一列的数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql)
{
using (IDbCommand com = CreateCommand())
{
com.CommandText = sql;
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
return com.ExecuteScalar();
}
}
/// <summary>
/// 执行查询返回第一行第一列的数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, List<DbParameter> parameters)
{
using (IDbCommand com = CreateCommand())
{
com.CommandText = sql;
if (parameters == null || parameters.Count == 0)
return null;
foreach (DbParameter parameter in parameters)
com.Parameters.Add(parameter);
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
return com.ExecuteScalar();
}
}
/// <summary>
/// 执行查询返回第一行第一列的数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="entity"></param>
/// <returns></returns>
public object ExecuteScalar<T>(string sql, T entity) where T : class, new()
{
using (IDbCommand com = CreateCommand())
{
GetParameters(com, sql, entity);
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
return com.ExecuteScalar();
}
}
#endregion
#region Tools
/// <summary>
/// 根据sql和实体获取参数
/// </summary>
/// <param name="sql"></param>
/// <param name="entity"></param>
/// <returns></returns>
public virtual List<DbParameter> GetParameters<T>(IDbCommand com, string sql, T entity)
{
return null;
}
public IEnumerator GetParameterNames(string sql, Regex regex)
{
sql = Regex.Replace(sql, @"['][\S]*[']", "");
MatchCollection matchs = regex.Matches(sql);
return matchs.GetEnumerator();
}
/// <summary>
/// 根据数据库获取参数名称
/// </summary>
/// <param name="aFieldName">字段名称</param>
/// <returns></returns>
public virtual string GetParameterName(string fieldName)
{
return null;
}
public virtual DbParameter GetParameter(string name, object value)
{
return null;
}
#endregion
#region Query
/// <summary>
/// 将表转换为实体
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="dt">数据</param>
/// <returns></returns>
public List<T> Query<T>(string sql) where T : class, new()
{
return Query<T>(sql, new T());
}
/// <summary>
/// 将表转换为实体
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="dt">数据</param>
/// <returns></returns>
public DataSet QueryDS(string sql)
{
using (IDbCommand com = CreateCommand())
{
DataSet ds = new DataSet();
com.CommandText = sql;
IDbDataAdapter da = CreateDataAdapter();
da.SelectCommand = com;
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
da.Fill(ds);
return ds;
}
}
/// <summary>
/// 将表转换为实体
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="dt">数据</param>
/// <returns></returns>
public DataSet QueryDS(string sql, List<DbParameter> parameters)
{
using (IDbCommand com = CreateCommand())
{
com.CommandText = sql;
if (parameters == null || parameters.Count == 0)
return null;
foreach (DbParameter parameter in parameters)
com.Parameters.Add(parameter);
DataSet ds = new DataSet();
IDbDataAdapter da = CreateDataAdapter();
da.SelectCommand = com;
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
da.Fill(ds);
return ds;
}
}
/// <summary>
/// 将表转换为实体
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="dt">数据</param>
/// <returns></returns>
public List<T> Query<T>(string sql, T entity) where T : class, new()
{
using (IDbCommand com = CreateCommand())
{
GetParameters(com, sql, entity);
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
IDataReader dataReader = com.ExecuteReader();
return FillModels<T>(dataReader);
}
}
/// <summary>
/// 将表转换为实体
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="dt">数据</param>
/// <returns></returns>
public List<T> Query<T>(string sql, List<DbParameter> parameters) where T : class, new()
{
using (IDbCommand com = CreateCommand())
{
if (parameters == null || parameters.Count == 0)
return null;
foreach (DbParameter parameter in parameters)
com.Parameters.Add(parameter);
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
IDataReader dataReader = com.ExecuteReader();
return FillModels<T>(dataReader);
}
}
/// <summary>
/// 将表转换为实体
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="dt">数据</param>
/// <returns></returns>
public DataSet QueryDS<T>(string sql, T entity) where T : class, new()
{
using (IDbCommand com = CreateCommand())
{
GetParameters(com, sql, entity);
DataSet ds = new DataSet();
IDbDataAdapter da = CreateDataAdapter();
da.SelectCommand = com;
if (State != ConnectionState.Open)
throw new Exception("Database not opened");
da.Fill(ds);
return ds;
}
}
private List<T> FillModels<T>(IDataReader dr)
{
using (dr)
{
List<string> field = new List<string>(dr.FieldCount);
for (int i = 0; i < dr.FieldCount; i++)
{
field.Add(dr.GetName(i).ToLower());
}
List<T> list = new List<T>();
while (dr.Read())
{
T model = Activator.CreateInstance<T>();
foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
{
if (field.Contains(property.Name.ToLower()))
{
if (dr[property.Name] != DBNull.Value)
property.SetValue(model, Convert.ChangeType(dr[property.Name], property.PropertyType), null);
}
}
list.Add(model);
}
return list;
}
}
#endregion
}
}
3、IDb.cs
namespace Yike.Common
{
public interface IDb
{
System.Data.IDbTransaction BeginTransaction();
System.Data.IDbTransaction BeginTransaction(System.Data.IsolationLevel il);
void ChangeDatabase(string databaseName);
void Close();
string ConnectionString { get; set; }
int ConnectionTimeout { get; }
System.Data.IDbCommand CreateCommand();
System.Data.IDbDataAdapter CreateDataAdapter();
void Dispose();
int ExecuteNonQuery<T>(string sql, T entity) where T : class, new();
int ExecuteNonQuery(string sql, System.Collections.Generic.List<System.Data.Common.DbParameter> parameters);
int ExecuteNonQuery(string sql);
object ExecuteScalar(string sql);
object ExecuteScalar(string sql, System.Collections.Generic.List<System.Data.Common.DbParameter> parameters);
object ExecuteScalar<T>(string sql, T entity) where T : class, new();
System.Data.Common.DbParameter GetParameter(string name, object value);
string GetParameterName(string fieldName);
System.Collections.Generic.List<System.Data.Common.DbParameter> GetParameters<T>(System.Data.IDbCommand com, string sql, T entity);
void Open();
System.Collections.Generic.List<T> Query<T>(string sql, T entity) where T : class, new();
System.Collections.Generic.List<T> Query<T>(string sql) where T : class, new();
System.Collections.Generic.List<T> Query<T>(string sql, System.Collections.Generic.List<System.Data.Common.DbParameter> parameters) where T : class, new();
System.Data.DataSet QueryDS(string sql, System.Collections.Generic.List<System.Data.Common.DbParameter> parameters);
System.Data.DataSet QueryDS<T>(string sql, T entity) where T : class, new();
System.Data.DataSet QueryDS(string sql);
System.Data.ConnectionState State { get; }
}
}
4、Mssql.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
namespace Yike.Common
{
public class Mssql : DbBase, IDb
{
public Mssql(ConnectionStringSettings connectionStringSettings)
{
ConnectionStringSetting = connectionStringSettings;
ChangeDatabase(ConnectionStringSetting.ConnectionString);
}
public override void ChangeDatabase(string databaseName)
{
conn = new SqlConnection(ConnectionStringSetting.ConnectionString);
}
public override System.Data.IDbDataAdapter CreateDataAdapter()
{
return new SqlDataAdapter();
}
public override System.Data.Common.DbParameter GetParameter(string name, object value)
{
return new SqlParameter(name, value);
}
public override string GetParameterName(string fieldName)
{
return string.Format("@{0}", fieldName);
}
public override List<System.Data.Common.DbParameter> GetParameters<T>(System.Data.IDbCommand com, string sql, T entity)
{
Type t = entity.GetType();
PropertyInfo[] fields = t.GetProperties();
IEnumerator ParameterNames;
string fieldName;
List<DbParameter> parameterList = new List<DbParameter>();
ParameterNames = GetParameterNames(sql, new Regex(@"@[^,\s)]*"));
while (ParameterNames.MoveNext())
{
fieldName = ParameterNames.Current.ToString().Replace("@", string.Empty);
PropertyInfo field = fields.Where(d => d.Name == fieldName).First();
com.Parameters.Add(new SqlParameter(fieldName, field.GetValue(entity, null)));
}
return parameterList;
}
}
}
5、Oracle.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using System.Data.OracleClient;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
namespace Yike.Common
{
public class Oracle : DbBase, IDb
{
public Oracle(ConnectionStringSettings connectionStringSettings)
{
ConnectionStringSetting = connectionStringSettings;
ChangeDatabase(ConnectionStringSetting.ConnectionString);
}
public override void ChangeDatabase(string databaseName)
{
conn = new OracleConnection(ConnectionStringSetting.ConnectionString);
}
public override System.Data.IDbDataAdapter CreateDataAdapter()
{
return new OracleDataAdapter();
}
public override System.Data.Common.DbParameter GetParameter(string name, object value)
{
return new OracleParameter(name, value);
}
public override string GetParameterName(string fieldName)
{
return string.Format(":{0}", fieldName);
}
public override List<System.Data.Common.DbParameter> GetParameters<T>(System.Data.IDbCommand com, string sql, T entity)
{
Type t = entity.GetType();
PropertyInfo[] fields = t.GetProperties();
IEnumerator ParameterNames;
string fieldName;
List<DbParameter> parameterList = new List<DbParameter>();
ParameterNames = GetParameterNames(sql, new Regex(@":[^,\s)]*"));
while (ParameterNames.MoveNext())
{
fieldName = ParameterNames.Current.ToString().Replace(":", string.Empty);
PropertyInfo field = fields.Where(d => d.Name == fieldName).First();
com.Parameters.Add(new OracleParameter(fieldName, field.GetValue(entity, null)));
}
return parameterList;
}
}
}
6、Access.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Common;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
namespace Yike.Common
{
public class Access : DbBase, IDb
{
public Access(ConnectionStringSettings connectionStringSettings)
{
ConnectionStringSetting = connectionStringSettings;
ChangeDatabase(ConnectionStringSetting.ConnectionString);
}
public override void ChangeDatabase(string databaseName)
{
conn = new OleDbConnection(ConnectionStringSetting.ConnectionString);
}
public override System.Data.IDbDataAdapter CreateDataAdapter()
{
return new OleDbDataAdapter();
}
public override System.Data.Common.DbParameter GetParameter(string name, object value)
{
return new OleDbParameter(name, value);
}
public override string GetParameterName(string fieldName)
{
return string.Format("@{0}", fieldName);
}
public override List<System.Data.Common.DbParameter> GetParameters<T>(System.Data.IDbCommand com, string sql, T entity)
{
Type t = entity.GetType();
PropertyInfo[] fields = t.GetProperties();
IEnumerator ParameterNames;
string fieldName;
List<DbParameter> parameterList = new List<DbParameter>();
ParameterNames = GetParameterNames(sql, new Regex(@"@[^,\s)]*"));
while (ParameterNames.MoveNext())
{
fieldName = ParameterNames.Current.ToString().Replace("@", string.Empty);
PropertyInfo field = fields.Where(d => d.Name == fieldName).First();
com.Parameters.Add(new OleDbParameter(fieldName, field.GetValue(entity, null)));
}
return parameterList;
}
}
}
作者：wuguilin 出处：http://www.cnblogs.com/wuguilin/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
