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);
} }
}
}