c# 访问数据库 ,利用泛型,支持多种数据库,mysql、sqllite、oracle,直接上代码
using System;
using System.Collections.Generic;using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace YKDBCom
{
/// <summary>
/// 数据库接口
/// </summary>
public interface IYKDB
{
/// <summary>
/// 开始事务
/// </summary>
void Begin();
/// <summary>
/// 提交事务
/// </summary>
void Commit();
/// <summary>
/// 回滚事务
/// </summary>
void RollBack();
/// <summary>
/// 执行sql
/// </summary>
/// <param name="sql">sql</param>
/// <param name="pars">参数</param>
/// <returns>执行条数</returns>
int Exce(string sql, params object[] pars);
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="sql">sql</param>
/// <param name="objs">参数</param>
/// <returns>数据</returns>
List<T> Query<T>(string sql, params object[] objs);
}
public class YKDB : IYKDB
{
private static object _connLock = new object();//连接锁
private static Type _connType = null;//连接类型;
private static int _databaseType=0;//连接类型,0为Mysql
public static string ConfigName = "DB_DATA_NAME";
private string _connectionStr="";//连接字符串
private string _providerName = "";//驱动提供名
private IDbConnection _connection=null;//数据库连接
private IDbTransaction _transaction = null;//事务
public YKDB()
{
}
/// <summary>
/// 开始事务
/// </summary>
public void Begin()
{
try
{
Open();
if (_connection == null)
{
throw new Exception("创建数据连接失败!");
}
_transaction= _connection.BeginTransaction();
if (_transaction == null)
{
throw new Exception("创建事务失败!");
}
}
catch (Exception ex)
{
throw new Exception("开始事务失败!");
}
}
/// <summary>
/// 提交事务
/// </summary>
public void Commit()
{
try
{
_transaction.Commit();
}
catch (Exception e)
{
throw new Exception("提交事务失败!");
}
finally
{
Close();
}
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollBack()
{
try
{
if(_transaction!=null)
_transaction.Rollback();
}
catch (Exception ex)
{
throw new Exception("回滚事务失败!");
}
finally
{
Close();
}
}
/// <summary>
/// 打开数据库连接
/// </summary>
private void Open()
{
try
{
_connection= GetIDbConnection();
_connection.Open();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private void Close()
{
try
{
if (_connection != null && _connection.State == ConnectionState.Open)
{
_connection.Close();
}
}
catch (Exception ex)
{
}
finally
{
_connection = null;
_transaction = null;
}
}
/// <summary>
/// 初始化配置文件
/// </summary>
private void InitConnInf()
{
try
{
_connectionStr = ConfigurationManager.ConnectionStrings[ConfigName].ConnectionString;
_providerName = ConfigurationManager.ConnectionStrings[ConfigName].ProviderName;
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 获取数据库连接
/// </summary>
/// <returns></returns>
private IDbConnection GetIDbConnection()
{
InitConnInf();
IDbConnection conn=null;
if (_providerName.ToUpper().Contains("MYSQL"))
{
conn = (IDbConnection)GetMysqlType();
}
else
{
conn = DbProviderFactories.GetFactory(_providerName).CreateConnection();
}
conn.ConnectionString = _connectionStr;
return conn;
}
/// <summary>
/// 获取mysql Connection
/// </summary>
/// <returns>连接IDbConnetion</returns>
private static object GetMysqlType()
{
if(_connType==null)
{
Monitor.Enter(_connLock);
_connType = ObjHelper.CreateType("MySql.Data.dll", "MySql.Data.MySqlClient.MySqlConnection");
Monitor.Exit(_connLock);
}
return _connType.Assembly.CreateInstance(_connType.FullName);
}
/// <summary>
/// 执行查询
/// </summary>
/// <typeparam name="T">泛型</typeparam>
/// <param name="sql">sql</param>
/// <param name="objs">参数</param>
/// <returns>数据</returns>
public List<T> Query<T>(string sql, params object[] objs)
{
IDbCommand command= CreateCommand(sql, objs);
IDataReader dataReader = command.ExecuteReader(CommandBehavior.SingleResult);
List<T> list = null;
if(dataReader!=null)
{
list = new List<T>();
while(dataReader.Read())
{
T entity = ObjHelper.CreateObj<T>();
MappingEntity(dataReader,entity);
list.Add(entity);
}
}
dataReader.Close();
dataReader.Dispose();
command.Dispose();
return list;
}
/// <summary>
/// 映射实体
/// </summary>
/// <param name="dataReader"></param>
/// <param name="entity"></param>
private void MappingEntity(IDataReader dataReader, object entity)
{
int colSize = dataReader.FieldCount;
for (int i = 0; i < colSize; i++)
{
string propName = dataReader.GetName(i);
object propVal = dataReader.GetValue(i);
if(propVal!=DBNull.Value)
{
ObjHelper.SetProp(entity,propName,propVal);
}
}
}
/// <summary>
///创建Command
/// </summary>
/// <param name="sql"></param>
/// <param name="pars"></param>
/// <returns></returns>
private IDbCommand CreateCommand(string sql,params object[] pars)
{
IDbCommand command = _connection.CreateCommand();
command.Connection = _connection;
command.CommandTimeout = 30000;
command.Transaction = _transaction;
command.CommandType = CommandType.Text;
CorrSql(command,sql, pars);
return command;
}
/// <summary>
/// 更正sql
/// </summary>
/// <param name="command"></param>
/// <param name="sql"></param>
/// <param name="pars"></param>
private void CorrSql(IDbCommand command,string sql, params object[] pars)
{
if(_databaseType==0)
{//mysql
ReplaceSql(command,sql,'@','?',pars);
}
}
/// <summary>
/// 替换sql
/// </summary>
/// <param name="command"></param>
/// <param name="sql"></param>
/// <param name="s"></param>
/// <param name="r"></param>
/// <param name="pars"></param>
private void ReplaceSql(IDbCommand command, string sql,char s,char r, params object[] pars)
{
int count = sql.Length;
string rsql="";
string par = "";
bool isPar = false;
int parIndex=0;//参数索引
int parSize = pars.Length;
for(int i=0;i<count;i++)
{
if (s == sql[i])
{
rsql += r;
par += r;
isPar = true;
continue;
}
else if(isPar)
{
if (sql[i] == ' ' || sql[i]==',' || sql[i]==')' || count==i+1)
{
isPar = false;
if (count == i + 1&& sql[i]!=' '&& sql[i] != ','&& sql[i] != ')')
par += sql[i];
if (parIndex < parSize)
{
SetIDataParameter(command, par, pars[parIndex]);
parIndex++;
}
par = "";
}
else
par += sql[i];
}
rsql += sql[i];
}
command.CommandText = rsql;
}
/// <summary>
/// 设置参数
/// </summary>
/// <param name="command"></param>
/// <param name="parName"></param>
/// <param name="parVal"></param>
private void SetIDataParameter(IDbCommand command, string parName,object parVal)
{
IDataParameter dataPar = command.CreateParameter();
dataPar.ParameterName = parName;
dataPar.Value = parVal;
command.Parameters.Add(dataPar);
}
/// <summary>
/// 执行sql
/// </summary>
/// <param name="sql"></param>
/// <param name="pars"></param>
/// <returns></returns>
public int Exce(string sql,params object[] pars)
{
IDbCommand command = CreateCommand(sql,pars);
return command.ExecuteNonQuery();
}
}
class AttrHelper
{
/// <summary>
/// 应用程序目录
/// </summary>
private static string _appRoot="";
/// <summary>
/// dll根目录
/// </summary>
private static string _dllRoot = "";
static AttrHelper()
{
if(string.IsNullOrEmpty(_appRoot))
{
_appRoot = AppDomain.CurrentDomain.BaseDirectory;
if (!Directory.Exists(_appRoot + "dll"))
Directory.CreateDirectory(_appRoot + "dll");
_dllRoot = _appRoot + "dll/";
}
}
/// <summary>
/// 获取应用程序目录
/// </summary>
/// <returns></returns>
public static string GetAppDir()
{
return _appRoot;
}
public static string GetDllDir()
{
return _dllRoot;
}
}
static class ObjHelper
{
/// <summary>
/// 创建对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static T CreateObj<T>()
{
Type t = typeof(T);
return (T)t.Assembly.CreateInstance(t.FullName);
}
/// <summary>
/// 创建Type
/// </summary>
/// <param name="dllName">dll名称</param>
/// <param name="clsName">实例名</param>
/// <returns>返回当前实例类型</returns>
public static Type CreateType(string dllName,string clsName)
{
try
{
object obj = CreateCls(dllName, clsName);
return obj.GetType();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 创建当前指定类型实例
/// </summary>
/// <param name="dllName">dll名称</param>
/// <param name="clsName">实例名</param>
/// <returns></returns>
public static object CreateCls(string dllName, string clsName)
{
Assembly assembly = Assembly.LoadFrom(AttrHelper.GetDllDir()+dllName);
return assembly.CreateInstance(clsName);
}
public static bool SetProp(object obj,string propName,object val)
{
try
{
if (obj == null || string.IsNullOrEmpty(propName))
return false;
Type type = obj.GetType();
PropertyInfo info = type.GetProperty(propName);
if(info!=null)
{
object propObj = info.GetValue(obj,null);
if(propObj!=null)
{
info.SetValue(obj, cevObj(propObj.GetType(),val),null);
}
else
{
info.SetValue(obj,val,null);
}
return true;
}
return false;
}
catch (Exception)
{
throw;
}
}
private static object cevObj(Type type, object val)
{
if (val == null || val == DBNull.Value)
{
if (!type.IsGenericType && !type.Equals(typeof(string)))
{
return val;
}
return null;
}
else
{
Type valType = val.GetType();
if (valType.FullName == "System.Decimal")
{
if (type.FullName == "System.Decimal" || type == typeof(decimal?))
{
return val;
}
if (type.FullName == "System.UInt16" || type == typeof(ushort?))
{
return ushort.Parse(val.ToString());
}
if (type.FullName == "System.UInt32" || type == typeof(uint?))
{
return uint.Parse(val.ToString());
}
if (type.FullName == "System.UInt64" || type == typeof(ulong?))
{
return ulong.Parse(val.ToString());
}
if (type.FullName == "System.Int16" || type == typeof(short?))
{
return short.Parse(val.ToString());
}
if (type.FullName == "System.Int32" || type == typeof(int?))
{
return int.Parse(val.ToString());
}
if (type.FullName == "System.Int64" || type == typeof(long?))
{
return long.Parse(val.ToString());
}
if (type.FullName == "System.Single" || type == typeof(float?))
{
return float.Parse(val.ToString());
}
if (type.FullName == "System.Double" || type == typeof(double?))
{
return double.Parse(val.ToString());
}
return val;
}
else
{
if (type.FullName == "System.Decimal")
{
if (valType.FullName == "System.Decimal")
{
return valType;
}
if (valType.FullName == "System.Double")
{
return Convert.ToDecimal(val);
}
return val;
}
else
{
if (type.FullName != valType.FullName && !type.IsGenericType)
{
return Convert.ChangeType(val, type);
}
if (type.FullName != valType.FullName && type.GetGenericTypeDefinition() == typeof(Nullable))
{
return Convert.ChangeType(val, Nullable.GetUnderlyingType(type));
}
return val;
}
}
}
}
}static class ObjHelper
{
/// <summary>
/// 创建对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static T CreateObj<T>()
{
Type t = typeof(T);
return (T)t.Assembly.CreateInstance(t.FullName);
}
/// <summary>
/// 创建Type
/// </summary>
/// <param name="dllName">dll名称</param>
/// <param name="clsName">实例名</param>
/// <returns>返回当前实例类型</returns>
public static Type CreateType(string dllName,string clsName)
{
try
{
object obj = CreateCls(dllName, clsName);
return obj.GetType();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 创建当前指定类型实例
/// </summary>
/// <param name="dllName">dll名称</param>
/// <param name="clsName">实例名</param>
/// <returns></returns>
public static object CreateCls(string dllName, string clsName)
{
Assembly assembly = Assembly.LoadFrom(AttrHelper.GetDllDir()+dllName);
return assembly.CreateInstance(clsName);
}
public static bool SetProp(object obj,string propName,object val)
{
try
{
if (obj == null || string.IsNullOrEmpty(propName))
return false;
Type type = obj.GetType();
PropertyInfo info = type.GetProperty(propName);
if(info!=null)
{
object propObj = info.GetValue(obj,null);
if(propObj!=null)
{
info.SetValue(obj, cevObj(propObj.GetType(),val),null);
}
else
{
info.SetValue(obj,val,null);
}
return true;
}
return false;
}
catch (Exception)
{
throw;
}
}
private static object cevObj(Type type, object val)
{
if (val == null || val == DBNull.Value)
{
if (!type.IsGenericType && !type.Equals(typeof(string)))
{
return val;
}
return null;
}
else
{
Type valType = val.GetType();
if (valType.FullName == "System.Decimal")
{
if (type.FullName == "System.Decimal" || type == typeof(decimal?))
{
return val;
}
if (type.FullName == "System.UInt16" || type == typeof(ushort?))
{
return ushort.Parse(val.ToString());
}
if (type.FullName == "System.UInt32" || type == typeof(uint?))
{
return uint.Parse(val.ToString());
}
if (type.FullName == "System.UInt64" || type == typeof(ulong?))
{
return ulong.Parse(val.ToString());
}
if (type.FullName == "System.Int16" || type == typeof(short?))
{
return short.Parse(val.ToString());
}
if (type.FullName == "System.Int32" || type == typeof(int?))
{
return int.Parse(val.ToString());
}
if (type.FullName == "System.Int64" || type == typeof(long?))
{
return long.Parse(val.ToString());
}
if (type.FullName == "System.Single" || type == typeof(float?))
{
return float.Parse(val.ToString());
}
if (type.FullName == "System.Double" || type == typeof(double?))
{
return double.Parse(val.ToString());
}
return val;
}
else
{
if (type.FullName == "System.Decimal")
{
if (valType.FullName == "System.Decimal")
{
return valType;
}
if (valType.FullName == "System.Double")
{
return Convert.ToDecimal(val);
}
return val;
}
else
{
if (type.FullName != valType.FullName && !type.IsGenericType)
{
return Convert.ChangeType(val, type);
}
if (type.FullName != valType.FullName && type.GetGenericTypeDefinition() == typeof(Nullable))
{
return Convert.ChangeType(val, Nullable.GetUnderlyingType(type));
}
return val;
}
}
}
}
}
}