dapper之连接数据库（Oracle，SQL Server，MySql）
2024-09-01 12:23:49
因为项目需求，需要项目同时可以访问三个数据库，因此本人经过一番研究，得出以下代码。
1.建立公共连接抽象类(DataBase)
public abstract class DataBase
{
/// <summary>
///
/// </summary>
public abstract string ConnectionString { get; } /// <summary>
///
/// </summary>
/// <param name="cmd"></param>
/// <param name="pName"></param>
/// <param name="value"></param>
/// <param name="type"></param>
/// <returns></returns> public DbParameter CreateParameter(DbCommand cmd, String pName, Object value, System.Data.DbType type)
{
var p = cmd.CreateParameter();
p.ParameterName = pName;
p.Value = (value == null ? DBNull.Value : value);
p.DbType = type;
return p;
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public abstract DbConnection CreateConnection();
/// <summary>
/// 返回List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
public List<T> Select<T>(string sql, Object paramObject = null)
{ try
{
using (DbConnection conn = CreateConnection())
{
conn.Open();
var list = Dapper.SqlMapper.Query<T>(conn, sql, paramObject);
return list.ToList<T>();
} }
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message,this.GetType());
return null;
}
}
/// <summary>
/// 返回List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tabName">表名</param>
/// <param name="paramObject"></param>
/// <returns></returns>
public List<T> Select<T>()
{
try
{
using (DbConnection conn = CreateConnection())
{
conn.Open();
var list = Dapper.SqlMapper.Query<T>(conn, "SELECT * FROM " + typeof(T).Name, null);
return list.ToList<T>();
}
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return null;
}
}
public int Insert<T>(T t)
{
try
{
using (DbConnection conn = CreateConnection())
{
conn.Open();
var id = conn.Insert(t);
return id ?? ;
}
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return -;
}
}
public int Delete<T>(T t)
{
try
{
using (DbConnection conn = CreateConnection())
{
conn.Open();
return conn.Delete(t);
}
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return -;
}
}
public int Update<T>(T t)
{
try
{
using (DbConnection conn = CreateConnection())
{
conn.Open();
return conn.Update(t);
}
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return -;
}
}
public string InsertByGuid<T>(T t)
{
try
{
using (DbConnection conn = CreateConnection())
{
conn.Open();
return conn.Insert<string,T>(t);
}
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return "";
}
}
public List<T> GetList<T>(string sql, Object paramObject = null)
{
try
{
using (DbConnection conn = CreateConnection())
{
conn.Open();
return conn.Query<T>(sql, paramObject).ToList();
}
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return null;
}
}
public IEnumerable<dynamic> GetList(string sql, Object paramObject = null)
{
try
{
using (DbConnection conn = CreateConnection())
{
conn.Open();
return conn.Query(sql, paramObject);
}
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return null;
}
}
/// <summary>
///
/// </summary>
/// <param name="sql"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
public List<dynamic> Select(string sql, Object paramObject = null)
{
DbConnection conn = null;
try
{
conn = CreateConnection();
conn.Open();
var list = Dapper.SqlMapper.Query(conn, sql, paramObject);
return list.ToList<dynamic>();
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return null;
}
finally
{
if (conn != null)
conn.Close();
}
} /// <summary>
/// 获取一条数据
/// </summary>
/// <param name="sql"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
public dynamic Single(string sql, Object paramObject = null)
{
DbConnection conn = null;
try
{
conn = CreateConnection();
conn.Open();
var list = Dapper.SqlMapper.QuerySingleOrDefault<dynamic>(conn, sql, paramObject);
return list;
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return null;
}
finally
{
if (conn != null)
conn.Close();
}
} /// <summary>
/// 获取一条数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
public T Single<T>(string sql, Object paramObject = null)
{ DbConnection conn = null;
try
{
conn = CreateConnection();
conn.Open();
var list = Dapper.SqlMapper.QuerySingleOrDefault<T>(conn, sql, paramObject);
return list;
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return default(T);
}
finally
{
if (conn != null)
conn.Close();
}
} /// <summary>
/// 获取一行一列
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
public T ExecuteScalar<T>(string sql, Object paramObject = null)
{ DbConnection conn = null;
try
{
conn = CreateConnection();
conn.Open();
T t = Dapper.SqlMapper.ExecuteScalar<T>(conn, sql, paramObject);
return t;
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return default(T);
}
finally
{
if (conn != null)
conn.Close();
}
} /// <summary>
/// 返回受影响行数
/// </summary>
/// <param name="sql"></param>
/// <param name="paramObject"></param>
/// <returns></returns>
public int Execute(string sql, Object paramObject = null)
{
DbConnection conn = null;
try
{
conn = CreateConnection();
conn.Open();
int count = Dapper.SqlMapper.Execute(conn, sql, paramObject);
return count;
}
catch (Exception ex)
{
Logs.Write(LogType.Error, ex.Message, this.GetType());
return ;
}
finally
{
if (conn != null)
conn.Close();
}
}
}
2.建立3个不同数据库连接类（OracleDataBase、SqlDataBase、MySqlDataBase）继承(DataBase)类
public class OracleDataBase : DataBase
{
public override string ConnectionString
{
get
{
return System.Configuration.ConfigurationManager.ConnectionStrings["OracleConnection"].ToString();
}
}
/// <summary>
/// 常用
/// </summary>
/// <returns></returns>
public override DbConnection CreateConnection()
{
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(ConnectionString);
conn.ConnectionString = ConnectionString;
return null;
}
}
public class SqlDataBase : DataBase
{
/// <summary>
///
/// </summary>
public override string ConnectionString
{
get
{
return System.Configuration.ConfigurationManager.ConnectionStrings["SqlConnection"].ToString();
}
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public override DbConnection CreateConnection()
{
SqlConnection conn = new SqlConnection(ConnectionString);
conn.ConnectionString = ConnectionString;
return conn;
} }
public class MySqlDataBase : DataBase
{
/// <summary>
///
/// </summary>
public override string ConnectionString
{
get
{
return System.Configuration.ConfigurationManager.ConnectionStrings["MySqlConnection"].ToString();
}
}
/// <summary>
/// 常用
/// </summary>
/// <returns></returns>
public override DbConnection CreateConnection()
{
MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);
return conn;
}
}
3.访问不同的连接
public List<T> GetList<T>()
{
var db = new SqlDataBase();
return db.Select<T>();
}
public List<T> GetList<T>()
{
var db = new OracleDataBase();
return db.Select<T>();
}
public List<T> GetList<T>()
{
var db = new MySqlDataBase();
return db.Select<T>();
}
以上代码就是访问不同的数据库的列表信息，其他增删改和上面的差不多写法，如果还有其他问题，请评论！
最新文章
- JavaScript闭包（Closure）学习笔记
- mysql的sql_mode合理设置
- Android入门第八篇之GridView(九宫图)
- 用mtrace检查内存泄漏
- JAVA通过C3P0连接数据库
- win7win8远程桌面提示凭证不工作问题
- （转+原）python中的浅拷贝和深拷贝
- MVC自定义配置
- logrotate 进行nginx日志分割
- 魔力Python--斐波那契数列(全)
- Debian系统下实现通过wpa_config连接WPA-PSK加密的Wifi连接
- 函数防抖 debounce
- java项目打成war包
- 方差variance, 协方差covariance, 协方差矩阵covariance matrix
- java.lang.Math
- 怎么使用JavaScript进行进制转换
- Nodejs this详解
- 【Amazon 必考】Amazon Leadership Principles 亚马逊领导力准则
- HDU 6040 stl
- 认识与设计Serverless（一）