Dapper 简单封装
2024-09-04 10:50:49
using System;
using System.Collections.Generic;
using System.Text;
using Dapper;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using common.core.config;
using Npoi.Core.SS.Formula.Functions; namespace common.core.sqlserver
{
public class BaseService<TService, TEntity> where TService : BaseService<TService, TEntity>, new()
{
/// <summary>
/// 默认实例
/// </summary>
/// <returns>服务实例</returns>
public static TService Instance() => new TService(); /// <summary>
/// 插入多个
/// </summary>
/// <param name="listModel"></param>
public virtual int InsertMany(List<TEntity> listModel)
{
if (listModel == null || listModel.Count <= )
{
throw new Exception("插入数据不可为空");
}
TEntity model = listModel.FirstOrDefault();
var ps = model.GetType().GetProperties();
List<string> @colms = new List<string>();
List<string> @params = new List<string>(); foreach (var p in ps)
{
if (p.CustomAttributes.All(x => x.AttributeType != typeof(PrimaryKeyAttribute)) && p.CustomAttributes.All(x => x.AttributeType != typeof(DBIgnoreAttribute)))
{
@colms.Add(string.Format("[{0}]", p.Name));
@params.Add(string.Format("@{0}", p.Name));
}
}
var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2})", typeof(TEntity).Name, string.Join(", ", @colms), string.Join(", ", @params));
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
IDbTransaction transaction = _conn.BeginTransaction();
return _conn.Execute(sql, listModel, transaction, null, null);
} } /// <summary>
/// 插入一个
/// </summary>
/// <param name="model"></param>
public virtual int InsertOne(TEntity model)
{
if (model == null)
{
throw new Exception("插入数据不可为空");
}
var ps = model.GetType().GetProperties();
List<string> @colms = new List<string>();
List<string> @params = new List<string>(); foreach (var p in ps)
{
if (p.CustomAttributes.All(x => x.AttributeType != typeof(PrimaryKeyAttribute)) && p.CustomAttributes.All(x => x.AttributeType != typeof(DBIgnoreAttribute)))
{
@colms.Add(string.Format("[{0}]", p.Name));
@params.Add(string.Format("@{0}", p.Name));
}
}
var sql = string.Format("INSERT INTO [{0}] ({1}) VALUES({2})", typeof(TEntity).Name, string.Join(", ", @colms), string.Join(", ", @params));
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.Execute(sql, model, null, null, null);
}
} /// <summary>
/// 查询一个
/// </summary>
/// <param name="whereProperties"></param>
/// <returns></returns>
public virtual TEntity GetOne(object whereProperties)
{
string where = "";
var listPropert = whereProperties.GetType().GetProperties();
if (listPropert.Length > )
{
where += " where ";
listPropert.ToList().ForEach(e =>
{
where += $" {e.Name} = @{e.Name} and";
});
}
where = where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
//返回单条信息
string query = $"SELECT * FROM { typeof(TEntity).Name}{where}";
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.QuerySingleOrDefault<TEntity>(query, whereProperties);
}
} /// <summary>
/// 查询一个
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public virtual TEntity GetOne(string where)
{
if (!string.IsNullOrEmpty(where))
{
where = $" where 1=1 and {where}";
}
//返回单条信息
string query = $"SELECT * FROM { typeof(TEntity).Name} {where}";
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.QuerySingleOrDefault<TEntity>(query);
}
} /// <summary>
/// 查询多个
/// </summary>
/// <param name="whereProperties"></param>
/// <returns></returns>
public virtual List<TEntity> GetMany(object whereProperties)
{
string where = "";
var listPropert = whereProperties.GetType().GetProperties();
if (listPropert.Length > )
{
where += " where ";
listPropert.ToList().ForEach(e =>
{
where += $" {e.Name} = @{e.Name} and";
});
}
where = where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
string query = $"SELECT * FROM { typeof(TEntity).Name}{where}";
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.Query<TEntity>(query, whereProperties)?.ToList();
}
} /// <summary>
/// 查询多个
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public virtual List<TEntity> GetMany(string where)
{
if (!string.IsNullOrEmpty(where))
{
where = $" where 1=1 and {where}";
}
string query = $"SELECT * FROM { typeof(TEntity).Name} {where}";
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.Query<TEntity>(query)?.ToList();
}
} /// <summary>
/// 是否存在
/// </summary>
/// <param name="whereProperties"></param>
/// <returns></returns>
public virtual bool Exists(object whereProperties)
{
return GetMany(whereProperties).Count > ;
} /// <summary>
/// 是否存在
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public virtual bool Exists(string where)
{
return GetMany(where).Count > ;
} /// <summary>
/// 删除
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public virtual int DeleteById(TEntity entity)
{
if (entity == null)
{
throw new Exception("删除内容不可为空");
}
string where = "";
var listPropert = entity.GetType().GetProperties();
if (listPropert.Length > )
{
listPropert.ToList().ForEach(p =>
{
var primaryKey = p.CustomAttributes.FirstOrDefault(x => x.AttributeType == typeof(PrimaryKeyAttribute));
if (primaryKey != null)
{
where += $" {p.Name} = @{p.Name} and";
}
});
} where = where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
if (string.IsNullOrEmpty(where))
{
throw new Exception("未找到Id");
}
string query = $"DELETE FROM { typeof(TEntity).Name} where {where}";
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.Execute(query, entity);
}
} /// <summary>
/// 删除
/// </summary>
/// <param name="whereProperties"></param>
/// <returns></returns>
public virtual int Delete(object whereProperties)
{
string where = "";
var listPropert = whereProperties.GetType().GetProperties();
if (listPropert.Length > )
{
listPropert.ToList().ForEach(e =>
{
where += $"{e.Name} = @{e.Name} and";
});
}
where = where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
if (string.IsNullOrEmpty(where))
{
throw new Exception("条件不可为空");
}
string query = $"DELETE FROM { typeof(TEntity).Name} where {where}";
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.Execute(query, whereProperties);
}
} /// <summary>
/// 删除
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public virtual int Delete(string where)
{
if (string.IsNullOrEmpty(where))
{
throw new Exception("条件不可为空");
}
string query = $"DELETE FROM { typeof(TEntity).Name} where {where}";
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.Execute(query);
}
} /// <summary>
/// 根据Id更新
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public virtual int UpdateById(TEntity entity)
{
if (entity == null)
{
throw new Exception("更新内容不可为空");
}
string where = "";
var listPropert = entity.GetType().GetProperties();
if (listPropert.Length > )
{
listPropert.ToList().ForEach(p =>
{
var primaryKey = p.CustomAttributes.FirstOrDefault(x => x.AttributeType == typeof(PrimaryKeyAttribute));
if (primaryKey!=null)
{
where += $" {p.Name} = @{p.Name} and";
}
});
} where=where.TrimEnd('d').TrimEnd('n').TrimEnd('a');
if (string.IsNullOrEmpty(where))
{
throw new Exception("未找到Id");
} string update = "";
var listPropertUpdate = entity.GetType().GetProperties();
if (listPropertUpdate.Length > )
{
update += "";
listPropertUpdate.ToList().ForEach(e =>
{
if (e.CustomAttributes.All(x => x.AttributeType != typeof(PrimaryKeyAttribute)) && e.CustomAttributes.All(x => x.AttributeType != typeof(DBIgnoreAttribute)))
{
update += $"{e.Name} = @{e.Name} ,";
}
});
}
update = update.TrimEnd(',');
if (string.IsNullOrEmpty(update))
{
throw new Exception("无更新内容");
}
string query = $"update { typeof(TEntity).Name} set {update} where {where}";
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.Execute(query, entity);
} } /// <summary>
/// 根据条件更新
/// </summary>
/// <param name="updateProperty"></param>
/// <param name="where"></param>
/// <returns></returns>
public virtual int Update(object updateProperty, string where)
{
if (string.IsNullOrEmpty(where))
{
throw new Exception("需输入条件");
}
string update = "";
var listPropertUpdate = updateProperty.GetType().GetProperties();
if (listPropertUpdate.Length > )
{
update += "";
listPropertUpdate.ToList().ForEach(e =>
{
update += $"{e.Name} = @{e.Name} ,";
});
}
update = update.TrimEnd(',');
if (string.IsNullOrEmpty(update))
{
throw new Exception("无更新内容");
}
string query = $"update { typeof(TEntity).Name} set {update} where {where}";
using (var _conn = new SqlConnection(CommonConfigUtil.GlobalConfigExtend.SqlServer.Url))
{
return _conn.Execute(query, updateProperty);
} }
}
}
最新文章
- Oracle 表空间联机（online）与脱机（offline）
- JavaWeb学习总结(三)——Tomcat服务器学习和使用(二) 包含https 非对称秘钥 NB
- uva 10976 fractions again（水题）——yhx
- hdu1231最大连续子序列
- JS中的间歇(周期)调用setInterval()与超时(延迟)调用setTimeout()相关总结
- Codeforces Round #238 (Div. 1)
- LINUX 内核月报 taobao
- js 中对象--对象结构（原型链基础解析）
- each,map,grep的区别
- php ajax提交数据 在本地可以执行，而在服务器不能执行
- DEMO阶段已完成，今天，要深入钻
- Redis基本数据类型以及String（一）
- Maven-02: 依赖
- linux打包压缩常用命令
- vim编辑器常见命令归纳大全
- Windows上使用telnet测试端口号
- ES启动报错最大进程数太少
- Prometheus+grafana搭建
- Qt532,鼠标键盘事件_ZC
- HDU 1715 （大数相加，斐波拉契数列）