Dapper通用虚拟类
public abstract class virtual_ope<T> where T : tbl_general
{//增加用户权限管理
public virtual string thisuser { get { return "admin"; } }//通用sql
public const string sql_general = "version,revision,created_by,created_time,updated_by,update_time";
public const string sql_atgeneral = "@version,@revision,@created_by,@created_time,@updated_by,@update_time";
public const string sql_upgeneral = "version=@version,revision=@revision,created_by=@created_by,created_time=@created_time,updated_by=@updated_by,update_time=@update_time";
//选择定义//必须定义
/// <summary>
/// 数据库名字
/// </summary>
public abstract string thisName { get; }
/// <summary>
/// 数据库实体类拼接
/// </summary>
public abstract string entity { get; }
/// <summary>
/// 数据库带@实体类拼接
/// </summary>
public abstract string atentity { get; }
/// <summary>
/// 数据更新值设置,可以设置全部和部分
/// </summary>
public abstract string upentity { get; }//sql语句定义,语句不允许外部注入使用@代替
public virtual string sqladd
{
get
{
return $"INSERT INTO {thisName}({entity}) VALUES({atentity}); ";
}
}
public virtual string sqldelete
{
get
{
return $"delete from {thisName} where code = @code";
}
}
//条数查询
public virtual string sqltotalcout
{
get
{
return $"select count(*) from {thisName}";
}
}
//批量删除
public virtual string sqldeletes
{
get
{
return $"delete from {thisName} where pcode = @pcode";
}
}
public virtual string sqlupdate
{
get
{
return $"update {thisName} set {upentity} where code = @code";
}
}
public virtual string sqlqueryall
{
get
{
return $"select {entity} from {thisName}";
}
}
public virtual string sqlquerybycode
{
get
{
return $"select {entity} from {thisName} where code = @code order by created_time asc";
}
}
public virtual string sqlquerybypcode
{
get
{
return $"select {entity} from {thisName} where pcode = @pcode order by created_time desc";//asc
}
}
//操作
/// <summary>
/// 获取所有
/// </summary>
/// <returns></returns>
public virtual List<T> getList()
{
return DapperHelper<T>.Ins().Query(sqlqueryall);
}
/// <summary>
/// 获取第一个
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public virtual T getByCode(string code)
{
return DapperHelper<T>.Ins().QueryFirst(sqlquerybycode, new { code = code });
}
/// <summary>
/// 通过code查询
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public virtual List<T> getListByCode(string code)
{
return DapperHelper<T>.Ins().Query(sqlquerybycode, new { code = code });
}
/// <summary>
/// 增加
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public virtual int add(T t)
{
return DapperHelper<T>.Ins().Execute(sqladd, t);
}
/// <summary>
/// 批量增加
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public virtual int adds(List<T> t)
{
return DapperHelper<T>.Ins().Execute(sqladd, t);
}
/// <summary>
/// 更新
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public virtual int update(T t)
{
//可以更新部分也可以全部更新
return DapperHelper<T>.Ins().Execute(sqlupdate, t);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
public virtual int deletebycode(string code)
{
try
{
// 存在注入吗?
return DapperHelper<T>.Ins().Execute(sqldelete, new { code = code });
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// 删除批量
/// </summary>
/// <param name="pcode"></param>
/// <returns></returns>
public virtual int deletebypcode(string pcode)
{
try
{
return DapperHelper<T>.Ins().Execute(sqldeletes, new { pcode = pcode });
}
catch (Exception)
{
return 0;
}
}/// <summary>
/// 通过父级code查询
/// </summary>
/// <returns></returns>
public virtual List<T> getListByPcode(string pcode)
{
try
{
return DapperHelper<T>.Ins().Query(sqlquerybypcode, new { pcode = pcode });
}
catch (Exception)
{
return new List<T>();
}}
/// <summary>
/// 通过父code获取一条数据
/// </summary>
/// <param name="pcode"></param>
/// <returns></returns>
public virtual T getByPcode(string pcode)
{
try
{
return DapperHelper<T>.Ins().QueryFirst(sqlquerybypcode, new { pcode = pcode });
}
catch (Exception)
{
return default(T);
}
}/// <summary>
/// 获取总条数
/// </summary>
/// <param name="exsql"></param>
/// <param name="bleak"></param>
/// <returns></returns>
public int QueryTotalCount(string extendsql, object param = null)
{
try
{
string sql = sqltotalcout + extendsql;
int val = (int)DapperHelper<T>.Ins().ExecuteScalar(sql, param);
return val;
}
catch (Exception ex)
{
//throw ReqException.Ins().Resp(RespExpType.ExecuteScalar, ex.Message);
return 0;
}}
// 查询是否存在同名
public int QueryIsExistName(string _name)
{
return QueryTotalCount(" where _name = @_name", new { _name = _name });
}
public int QueryIsExistSubName(string _name, string pcode)
{
return QueryTotalCount(" where _name = @_name and pcode=@pcode", new { _name = _name, pcode = pcode });
}
public int QueryIsExistupName(string _name, string code)
{
return QueryTotalCount(" where _name = @_name and code !=@code", new { _name = _name, code = code });
}
}
这一块可以在网上找找其他,很多
public class DapperHelper<T>
{
private volatile static DapperHelper<T> _instance = null;
private static readonly object lockHelper = new object();
private DapperHelper() { }
public static DapperHelper<T> Ins()
{
//
if (_instance == null)
{
lock (lockHelper)
{
if (_instance == null)
{
_instance = new DapperHelper<T>();
}
}
}
return _instance;
}
/// <summary>
/// 数据库连接字符串,访问其实数据库,由外部传入,方便切换库
/// </summary>
readonly string connectionString = ConfigurationManager.ConnectionStrings["myself"].ConnectionString;
/// <summary>
/// 查询列表
/// </summary>
/// <param name="sql">查询的sql</param>
/// <param name="param">替换参数</param>
/// <returns></returns>
public List<T> Query(string sql, object param = null)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.Query<T>(sql, param).ToList();
}
}/// <summary>
/// 查询第一个数据
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public T QueryFirst(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.QueryFirst<T>(sql, param);
}
}/// <summary>
/// 查询第一个数据没有返回默认值
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public T QueryFirstOrDefault(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.QueryFirstOrDefault<T>(sql, param);
}
}/// <summary>
/// 查询单条数据
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public T QuerySingle(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.QuerySingle<T>(sql, param);
}
}/// <summary>
/// 查询单条数据没有返回默认值
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public T QuerySingleOrDefault(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.QuerySingleOrDefault<T>(sql, param);
}
}/// <summary>
/// 增删改
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public int Execute(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.Execute(sql, param);
}
}/// <summary>
/// Reader获取数据
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public IDataReader ExecuteReader(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.ExecuteReader(sql, param);
}
}/// <summary>
/// Scalar获取数据
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.ExecuteScalar(sql, param);
}
}/// <summary>
/// Scalar获取数据
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public T ExecuteScalarForT(string sql, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.ExecuteScalar<T>(sql, param);
}
}/// <summary>
/// 带参数的存储过程
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <returns></returns>
public List<T> ExecutePro(string proc, object param)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
List<T> list = con.Query<T>(proc,
param,
null,
true,
null,
CommandType.StoredProcedure).ToList();
return list;
}
}
/// <summary>
/// 事务1 - 全SQL
/// </summary>
/// <param name="sqlarr">多条SQL</param>
/// <param name="param">param</param>
/// <returns></returns>
public int ExecuteTransaction(string[] sqlarr)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
using (var transaction = con.BeginTransaction())
{
try
{
int result = 0;
foreach (var sql in sqlarr)
{
result += con.Execute(sql, null, transaction);
}transaction.Commit();
return result;
}
catch (Exception)
{
transaction.Rollback();
return 0;
}
}
}
}/// <summary>
/// 事务2 - 声明参数
///demo:
///dic.Add("Insert into Users values (@UserName, @Email, @Address)",
/// new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" });
/// </summary>
/// <param name="Key">多条SQL</param>
/// <param name="Value">param</param>
/// <returns></returns>
public int ExecuteTransaction(Dictionary<string, object> dic)
{
using (SqlConnection con = new SqlConnection(connectionString))
{
using (var transaction = con.BeginTransaction())
{
try
{
int result = 0;
foreach (var sql in dic)
{
result += con.Execute(sql.Key, sql.Value, transaction);
}transaction.Commit();
return result;
}
catch (Exception)
{
transaction.Rollback();
return 0;
}
}
}
}/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">主sql 不带 order by</param>
/// <param name="sort">排序内容 id desc,add_time asc</param>
/// <param name="pageIndex">第几页</param>
/// <param name="pageSize">每页多少条</param>
/// <param name="useWriteConn">是否主库</param>
/// <returns></returns>
public List<T> ExecutePageList(string sql, string sort, int pageIndex, int pageSize, bool useWriteConn = false, object param = null)
{
string pageSql = @"SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) _row_number_,* FROM
({2})temp )temp1 WHERE temp1._row_number_>{3} ORDER BY _row_number_";
string execSql = string.Format(pageSql, pageSize, sort, sql, pageSize * (pageIndex - 1));
using (SqlConnection con = new SqlConnection(connectionString))
{
return con.Query<T>(sql, param).ToList();
}
}
}
sql通用类可以自行定义
public class tbl_general
{
public int version { get; set; }
public int revision { get; set; }
public string created_by { get; set; }
public DateTime? created_time { get; set; }
public string updated_by { get; set; }
public DateTime? update_time { get; set; }
}
使用方式:
public class tbl_user_info: tbl_general
{
public string code { get; set; }
public string pcode { get; set; }
public string nickname { get; set; }
public DateTime? date_of_birth { get; set; }
public string pwd { get; set; }
public string _email { get; set; }
public string real_name { get; set; }
public string _position { get; set; }
public string _gender { get; set; }
public string number_of_visits { get; set; }
public DateTime? last_login_time { get; set; }
public bool user_validity { get; set; }public const string entity = "code,pcode,nickname,date_of_birth,pwd,_email,real_name,_position,_gender,number_of_visits,last_login_time,user_validity,";
public const string atentity = "@code,@pcode,@nickname,@date_of_birth,@pwd,@_email,@real_name,@_position,@_gender,@number_of_visits,@last_login_time,@user_validity,";
public const string upentity = "code=@code,pcode=@pcode,nickname=@nickname,date_of_birth=@date_of_birth,pwd=@pwd,_email=@_email,real_name=@real_name,_position=@_position,_gender=@_gender,number_of_visits=@number_of_visits,last_login_time=@last_login_time,user_validity=@user_validity,";}
public class dao_user_info : virtual_ope<tbl_user_info>
{
public override string atentity
{
get
{
return tbl_user_info.atentity + sql_atgeneral;
}
}public override string entity
{
get
{
return tbl_user_info.entity + sql_general;
}
}
public override string thisName
{
get
{
return "tbl_user_info";
}
}public override string upentity
{
get
{
return tbl_user_info.upentity + sql_upgeneral;
}
}
}
可以根据具体情况,创建多数据库,分页等功能。