/// <summary>
/// 根据主键ID查询单一实体数据
/// </summary>
/// <param name="id">主键ID的值</param>
/// <param name="name">主键ID的数据库字段名称，默认是ID</param>
/// <returns></returns>
public T Single(int id, string name = "ID")
{
T entity = Clone() as T;
if (id > 0)
{
string tableName = (typeof(T).GetCustomAttributes(false).Where(attr => attr.GetType().Name == "TableAttribute").SingleOrDefault() as dynamic).Name;
string sql = string.Format("select * from {0} WITH(NOLOCK) where {1} = @ID", tableName, name);
try
{
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
if (!string.IsNullOrEmpty(name))
{
entity = connection.Query <T>(sql, new { id }).FirstOrDefault();
}
}
}
catch (Exception e)
{
log.Error($"查询单一数据失败:sql:{sql}", e);
}
}
return(entity);
}
/// <summary>
/// 根据主键ID删除数据
/// </summary>
/// <param name="id">主键ID的值</param>
/// <param name="name">主键ID的数据库字段名称，默认是ID</param>
/// <returns></returns>
public bool Delete(int id, string name = "ID")
{
bool result = false;
if (id > 0)
{
string tableName = (typeof(T).GetCustomAttributes(false).Where(attr => attr.GetType().Name == "TableAttribute").SingleOrDefault() as dynamic).Name;
string sql = string.Format("delete from {0} where {1} = @{1}", tableName, name);
try
{
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
if (!string.IsNullOrEmpty(name))
{
result = connection.Execute(sql, new { id }) > 0;
}
}
}
catch (Exception e)
{
log.Error($"删除数据失败:sql:{sql}", e);
}
}
return(result);
}
/// <summary>
/// 执行返回第一行第一列值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlContent"></param>
/// <returns></returns>
public static T ExecScalarSql <T>(string sqlContent)
{
try
{
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
var resutl = connection.ExecuteScalar <T>(sqlContent, null, null, null, CommandType.Text);
connection.Close();
return(resutl);
}
}
catch (Exception ex)
{
log.Error($"执行sql:{sqlContent}", ex);
}
return(default(T));
}
/// <summary>
/// 获取该实体的查询
/// </summary>
/// <param name="sql">SQL完整语句</param>
/// <param name="param">参数化对象</param>
/// <returns></returns>
public List <T> GetQueryTest(string sql, object param = null)
{
List <T> list = null;
if (!string.IsNullOrEmpty(sql))
{
try
{
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
list = connection.Query <T>(sql, param).ToList();
}
}
catch (Exception e)
{
log.Error($"获取列表数据失败sql:{sql}", e);
}
}
return(list);
}
/// <summary>
/// 根据SQL语句查询一个结果值
/// </summary>
/// <param name="sql">SQL完整语句</param>
/// <param name="param">参数化对象</param>
/// <returns></returns>
public object GetScalar(string sql, object param = null)
{
object totalCount = null;
if (!string.IsNullOrEmpty(sql))
{
try
{
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
totalCount = connection.ExecuteScalar <object>(sql, param, null, null, null);
}
}
catch (Exception e)
{
log.Error($"获取结果值失败:sql:{sql}", e);
}
}
return(totalCount);
}
/// <summary>
/// 获取该实体的查询
/// </summary>
/// <param name="sql">SQL完整语句</param>
/// <param name="param">参数化对象</param>
/// <returns></returns>
public int Count(string sql, object param = null)
{
int totalCount = 0;
if (!string.IsNullOrEmpty(sql))
{
try
{
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
totalCount = connection.ExecuteScalar <int>(sql, param, null, null, null);
}
}
catch (Exception e)
{
log.Error("获取数据条数失败", e);
}
}
return(totalCount);
}
/// <summary>
/// Delete，根据实体对象删除
/// </summary>
/// <returns>返回,成功:true,失败:false</returns>
public bool Delete(T entity)
{
bool result = false;
if (entity != null)
{
string tableName = (typeof(T).GetCustomAttributes(false).Where(attr => attr.GetType().Name == "TableAttribute").SingleOrDefault() as dynamic).Name;
try
{
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
result = connection.Delete <T>(entity);
}
}
catch (Exception e)
{
log.Error($"删除数据失败:Table:{tableName},Id:{JsonConvert.SerializeObject(entity)}", e);
}
}
return(result);
}
/// <summary>
/// 根据主键ID查询单一实体数据
/// </summary>
/// <param name="id">主键ID的值</param>
/// <param name="name">主键ID的数据库字段名称，默认是ID</param>
/// <returns></returns>
public T Single(string where, object param = null)
{
T entity = Clone() as T;
if (!string.IsNullOrEmpty(where))
{
try
{
string tableName = (typeof(T).GetCustomAttributes(false).Where(attr => attr.GetType().Name == "TableAttribute").SingleOrDefault() as dynamic).Name;
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
entity = connection.Query <T>(string.Format("select * from {0} where {1} ", tableName, where), param).FirstOrDefault();
}
}
catch (Exception e)
{
log.Error("查询单一数据失败", e);
}
}
return(entity);
}
public static List <T> ExecuteProcedure <T>(string ProcName, object param = null)
{
try
{
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
List <T> list = new List <T>();
list = connection.Query <T>(ProcName,
param,
null,
true,
null,
CommandType.StoredProcedure).ToList();
return(list);
}
}
catch (Exception e)
{
log.Error($"执行存储过程:ProcName={ProcName},param:{param.ToString()}", e);
}
return(new List <T>());
}
/// <summary>
/// 根据主键ID查询单一实体数据
/// </summary>
/// <param name="id">主键ID的值</param>
/// <param name="name">主键ID的数据库字段名称，默认是ID</param>
/// <returns></returns>
public int Single(string where, object param = null, string key = "")
{
//T entity = Clone() as T;
int result = 0;
if (!string.IsNullOrEmpty(where))
{
string tableName = (typeof(T).GetCustomAttributes(false).Where(attr => attr.GetType().Name == "TableAttribute").SingleOrDefault() as dynamic).Name;
string sql = string.Format("select " + key + " from {0} WITH(NOLOCK) where {1} ", tableName, where);
try
{
using (SqlConnection connection = RunConnection.GetOpenConnection())
{
result = connection.Query <int>(sql, param).FirstOrDefault();
}
}
catch (Exception e)
{
log.Error($"查询单一数据失败:sql:{sql}", e);
}
}
return(result);
}
