一、数据库访问 概述
1. 数据库使用mysql，orm采用dapper框架。dapper框架应用简单，只是需要自己手写sql语句，但是对于像我这样写了多年sql语句的人来说，这应该不算问题，个人还是比较喜欢这种手写sql语句的框架。
Dapper 虽然已经非常简单，但是为了调用时方便还是进行了一下封装 ，这样在写DAL 层方法时，就不用每次都try catch了。
2.先放代码：
调用方法示例：
#region role 增删改查
/// <summary>
/// 增加一条
/// </summary>
public static void AddRole(Role role, IDbTransaction tran = null)
{
string sql = @"insert into role(Id,Name,Note)
values(@Id,@Name,@Note)";
role.EnsureIdNotNull();
DB.DBHelper.Execute(sql, role, tran);
} /// <summary>
/// 更新
/// </summary>
public static void UpdateRole(Role role, IDbTransaction tran = null)
{
string sql = @"update role set Name=@Name,Note=@Note where Id=@Id";
DB.DBHelper.Execute(sql, role, tran);
} /// <summary>
/// 删除 一条记录
/// </summary>
public static void DeleteRole(string Id, IDbTransaction tran = null)
{
string sql = "select count(1) from userrole where RoleId= @Id";
var c = DB.DBHelper.ExecuteScalar<int>(sql, new { Id = @Id });
if (c > )
{
throw new OperateException("该角色已经有用户在使用，不能删除");
} List<string> sqllist = new List<string>();
sqllist.Add("delete from role where Id=@Id");
sqllist.Add("delete from rolemenu where RoleId=@Id"); //同步删除为角色分配的权限
DB.DBHelper.Execute(sqllist, new { Id = Id }, tran);
} /// <summary>
/// 列表查询
/// </summary>
/// <param name="pl"></param>
/// <returns></returns>
public static ResultSet<Role> GetRoleList(ParamList pl)
{
string sql = @"select Id,Name,Note from role where 1=1";
pl.orderby = "Name";
//添加各种查询条件
if (pl.isnotnull("keywords"))
{
sql += " and instr(concat(Name,Note),@keywords)>0";
}
return DB.DBHelper.GetResultSet<Role>(sql, pl.orderby, pl);
}
#endregion
DBHelper 类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using Dapper;
using CommonModel; namespace DBTools
{
/// <summary>
/// 执行sql ，抛出异常
/// </summary>
public class DBHelper
{
#region 连接信息
public virtual string ConnectionString
{
get;
set;
}
public virtual DataBaseType DBType
{
get;
set;
}
#endregion #region 获取DBAdapter
public DBAdapter GetDBAdapter()
{
return DBTools.DBFactory.GetAdapter(DBType, ConnectionString);
} #endregion #region 查询
public List<dynamic> Query(string sql, object query = null, IDbTransaction tran = null)
{
DBAdapter adapter = null; ; try
{
if (tran == null)
{
adapter = GetDBAdapter();
}
var conn = tran != null ? tran.Connection : adapter.OpenDatabase();
var m = conn.Query(sql, query).ToList();
return m;
}
catch (Exception ex) { throw ex; }
finally
{
if (adapter != null)
{
adapter.CloseDatabase();
}
}
} public List<T> Query<T>(string sql, object query = null, IDbTransaction tran = null)
{
DBAdapter adapter = null; ; try
{
if (tran == null)
{
adapter = GetDBAdapter();
}
var conn = tran != null ? tran.Connection : adapter.OpenDatabase();
var m = conn.Query<T>(sql, query).ToList<T>();
return m;
}
catch (Exception ex) { throw ex; }
finally
{
if (adapter != null)
{
adapter.CloseDatabase();
}
}
} public T ExecuteScalar<T>(string sql, object query = null, IDbTransaction tran = null)
{
DBAdapter adapter = null; ; try
{
if (tran == null)
{
adapter = GetDBAdapter();
}
var conn = tran != null ? tran.Connection : adapter.OpenDatabase();
var m = conn.ExecuteScalar<T>(sql, query, tran);
return m;
}
catch (Exception ex) { throw ex; }
finally
{
if (adapter != null)
{
adapter.CloseDatabase();
}
}
} #endregion #region Result /// <summary>
/// 返回一个ResultSet 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <param name="pl"></param>
/// <param name="tran"></param>
/// <returns></returns>
public ResultSet<T> GetResultSet<T>(string sql, string OrderBy, ParamList pl, IDbTransaction tran = null)
{
DBAdapter adapter = GetDBAdapter(); try
{
var conn = adapter.OpenDatabase();
if (pl.ispage)
{
string pagesql = adapter.GetPageSql(sql, OrderBy);
string countsql = adapter.GetCountSql(sql); var m = conn.Query<T>(pagesql, pl.GetParameters(), tran).ToList<T>();
var c = conn.ExecuteScalar<int>(countsql, pl.GetParameters(), tran);
return new ResultSet<T>() { Value = m, Total = c };
}
else
{
var m = conn.Query<T>(sql + " order by " + OrderBy, pl.GetParameters(), tran).ToList<T>();
return new ResultSet<T>() { Value = m, Total = m.Count };
}
}
catch
{
throw;
}
finally
{
adapter.CloseDatabase();
}
} /// <summary>
/// 返回一个ResultSet 查询
/// </summary>
/// <param name="sql"></param>
/// <param name="OrderBy"></param>
/// <param name="pl"></param>
/// <param name="tran"></param>
/// <returns></returns>
public ResultSet GetResultSet(string sql, string OrderBy, ParamList pl, IDbTransaction tran = null)
{
DBAdapter adapter = GetDBAdapter(); try
{
var conn = adapter.OpenDatabase();
if (pl.ispage)
{
string pagesql = adapter.GetPageSql(sql, OrderBy);
string countsql = adapter.GetCountSql(sql); var m = conn.Query(pagesql, pl.GetParameters(), tran);
var c = conn.ExecuteScalar<int>(countsql, pl.GetParameters(), tran);
return ResultSet.GetResultSet(m, c);
}
else
{
var m = conn.Query(sql + " order by " + OrderBy, pl.GetParameters(), tran);
return ResultSet.GetResultSet(m);
}
}
catch
{
throw;
}
finally
{
adapter.CloseDatabase();
}
}
#endregion #region 执行sql
/// <summary>
/// 执行sql，返回受影响记录数
/// </summary>
/// <param name="sql"></param>
/// <param name="model"></param>
/// <param name="tran"></param>
public int Execute(string sql, object model = null, IDbTransaction tran = null)
{
DBAdapter adapter = null; ; try
{
if (tran == null)
{
adapter = GetDBAdapter();
}
var conn = tran != null ? tran.Connection : adapter.OpenDatabase();
return conn.Execute(sql, model, tran); }
catch (Exception ex) { throw ex; }
finally
{
if (adapter != null)
{
adapter.CloseDatabase();
}
}
}
/// <summary>
/// 执行sql，返回受影响条记录数
/// </summary>
/// <param name="sql"></param>
/// <param name="pl"></param>
/// <param name="tran"></param>
/// <returns></returns>
public int Execute(string sql, ParamList pl, IDbTransaction tran = null)
{
DBAdapter adapter = null; ; try
{
if (tran == null)
{
adapter = GetDBAdapter();
}
var conn = tran != null ? tran.Connection : adapter.OpenDatabase();
return conn.Execute(sql, pl.GetParameters(), tran); }
catch { throw; }
finally
{
if (adapter != null)
{
adapter.CloseDatabase();
}
}
} /// <summary>
/// 同一个sql语句，批量操作多个对象
/// </summary>
/// <param name="sql"></param>
/// <param name="models"></param>
/// <param name="tran"></param>
public int Execute(string sql, IEnumerable<object> models, IDbTransaction tran = null)
{
DBAdapter adapter = null; ; try
{
if (tran == null)
{
adapter = GetDBAdapter();
}
var conn = tran != null ? tran.Connection : adapter.OpenDatabase();
return conn.Execute(sql, models, tran);
}
catch (Exception ex) { throw ex; }
finally
{
if (adapter != null)
{
adapter.CloseDatabase();
}
}
} /// <summary>
/// 使用事务批量执行多个语句
/// </summary>
/// <param name="sqllist"></param>
/// <param name="pl"></param>
/// <returns></returns>
public void Execute(List<string> sqllist, object model = null, IDbTransaction tran = null)
{
DBAdapter adapter = null; ; try
{
if (tran == null)
{
adapter = GetDBAdapter();
tran = adapter.BeginTransaction();
}
var conn = tran.Connection; foreach (var sql in sqllist)
{
conn.Execute(sql, model, tran);
}
if (adapter != null)
{
adapter.Commit();
}
}
catch
{
if (adapter != null)
{
adapter.Rollback();
}
throw;
}
finally
{
if (adapter != null)
{
adapter.CloseDatabase();
}
} }
/// <summary>
/// 使用事务批量执行sql语句
/// </summary>
/// <param name="sqllist"></param>
/// <param name="pl"></param>
/// <param name="tran"></param>
public void Execute(List<string> sqllist, ParamList pl, IDbTransaction tran = null)
{
DBAdapter adapter = null; ; try
{
if (tran == null)
{
adapter = GetDBAdapter();
tran = adapter.BeginTransaction();
}
var conn = tran.Connection; foreach (var sql in sqllist)
{
conn.Execute(sql, pl.GetParameters(), tran);
}
if (adapter != null)
{
adapter.Commit();
}
}
catch
{
if (adapter != null)
{
adapter.Rollback();
}
throw;
}
finally
{
if (adapter != null)
{
adapter.CloseDatabase();
}
} }
#endregion }
}
3.说明：
Dapper 相关目录结构如下：
a.分成两个项目是为了避免数据库访问DBTools 被每层引用。除DAL层外，各层可以只引用CommonModel 就可以了.
b.使用DBAdapter 和DBFactory 对相关操作进行了封装，用于支持多数据库，目前我已经实现了MySqlDBAdapter,其他的数据库可以自行实现。
c. 使用时可以使用一个新的DB 类 对DBHelper 进行封装，达到方便调用，及同一个项目中使用多个数据库的目的，如在DAL 层中定义一个DB 类，类中包含一个静态DBHelper 对象，这样就可以向文中开始的那样调用DBHelper 了。
如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration; namespace MVC.DAL
{
public class DB
{
private static DBTools.DBHelper _instance;
private static object lockhelper = new object();
public static DBTools.DBHelper DBHelper
{
get
{
if (_instance == null)
{
lock (lockhelper)
{
if (_instance == null)
{
_instance = new DBTools.DBHelper();
_instance.DBType = DBTools.DataBaseType.MySql;
_instance.ConnectionString = ConfigurationManager.ConnectionStrings["mysql"].ConnectionString;
}
}
}
return _instance;
}
}
}
}