using PT.Model;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace PT.DAL
{
public class BaseDal<T, V> where T : class, new() where V : class, new()
{
#region 通用公共方法
#region 查询
/// <summary>
/// 查询表所有数据
/// </summary>
/// <returns></returns>
public virtual List<T> Queryable()
{
return SqlSugarHelper.Db.Queryable<T>().ToList();
}
/// <summary>
/// 查询表未删除数据
/// </summary>
/// <returns></returns>
public virtual List<T> QueryableNotDeleted()
{
return SqlSugarHelper.Db.Queryable<T>().Where("IsDelete=@IsDelete", new { IsDelete = 0 }).OrderBy("Id desc").ToList();
}
/// <summary>
/// 以id数组集查询
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public virtual List<T> QueryableById(int[] ids)
{
return SqlSugarHelper.Db.Queryable<T>().Where("Id in(@ids)", new { ids = ids }).OrderBy("Id desc").ToList();
}
/// <summary>
/// 查询表未删除数据 按条件查询
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public virtual List<T> QueryableNotDeleted(T t)
{
return SqlSugarHelper.Db.Queryable<T>().Where("IsDelete=@IsDelete", new { IsDelete = 0 }).OrderBy("Id desc").ToList();
}
/// <summary>
/// 按条件查询一行
/// </summary>
/// <param name="whereString"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public virtual T QueryableWhereFirst(string whereString, object parameters)
{
//string whereString = "name=@name";
//object parameters = new { name = "xx" };
return SqlSugarHelper.Db.Queryable<T>().Where(whereString, parameters).First();
}
/// <summary>
/// 单主键查询
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public virtual T QueryableInSingle(string Id)
{
//单主键查询
return SqlSugarHelper.Db.Queryable<T>().InSingle(Id);
}
/// <summary>
/// 查询单条 没有返回Null，如果结果大于1条会抛出错误 （根据业务重写）
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public virtual UserList QueryableSingle(UserList t)
{
////没有返回Null，如果结果大于1条会抛出错误
return SqlSugarHelper.Db.Queryable<UserList>().Single(p => p.LoginID == t.LoginID && p.LoginPwd == t.LoginPwd && p.IsDelete == false);
}
/// <summary>
/// 是否存在记录
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public virtual bool IsExist(T t)
{
string whereString = "name=@name";
object parameters = new { name = "xx" };
return SqlSugarHelper.Db.Queryable<T>().Where(whereString, parameters).Any();
}
#endregion
#region 插入
/// <summary>
/// 插入 忽略null列
/// </summary>
/// <param name="t"></param>
/// <returns>返回自增id</returns>
public virtual int InsertableIgnoreColumns(T t)
{
return SqlSugarHelper.Db.Insertable(t).IgnoreColumns(ignoreNullColumn: true).ExecuteReturnIdentity();
}
/// <summary>
/// 插入 忽略null列
/// </summary>
/// <param name="ts"></param>
/// <returns>返回影响行数</returns>
public virtual int InsertableIgnoreColumns(List<T> ts)
{
int n = 0;
int Index = 0;
//ignoreNullColumn 不支持批量操作 需要打包提交
foreach (var item in ts)
{
SqlSugarHelper.Db.Insertable(item).IgnoreColumns(ignoreNullColumn: true).AddQueue();
if (Index % 200 == 0 && Index > 1)
{
n += SqlSugarHelper.Db.SaveQueues();
}
Index += 1;
}
n += SqlSugarHelper.Db.SaveQueues();
return n;
}
#endregion
#region 更新
/// <summary>
/// 更新 根据主键
/// </summary>
/// <param name="ts"></param>
/// <returns></returns>
public virtual int Updateable(List<T> ts)
{
return SqlSugarHelper.Db.Updateable(ts).ExecuteCommand();
}
/// <summary>
/// 更新 根据主键 忽略null列
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public virtual int UpdateableIgnoreColumns(T t)
{
return SqlSugarHelper.Db.Updateable(t).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
}
/// <summary>
/// 批量更新 根据主键 忽略null列
/// </summary>
/// <param name="ts"></param>
/// <returns></returns>
public virtual int UpdateableIgnoreColumns(List<T> ts)
{
int n = 0;
int Index = 0;
//ignoreNullColumn 不支持批量操作 需要打包提交
foreach (var item in ts)
{
SqlSugarHelper.Db.Updateable(item).IgnoreColumns(ignoreAllNullColumns: true).AddQueue();
if (Index % 200 == 0 && Index > 1)
{
n += SqlSugarHelper.Db.SaveQueues();
}
Index += 1;
}
n += SqlSugarHelper.Db.SaveQueues();
return n;
}
/// <summary>
/// 更新 根据自定义条件 忽略null列 （根据业务重写）
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public virtual int UpdateableIgnoreColumnsWhere(T t)
{
//return SqlSugarHelper.Db.Updateable(t).Where(p => p.xx == t.xx && p.IsDelete == false).IgnoreColumns(ignoreAllNullColumns: true).ExecuteCommand();
return 0;
}
/// <summary>
/// 主键等于默认值插入否则更新 id=0插入 id不等于0更新
/// </summary>
/// <param name="list"></param>
/// <returns>返回影响行数</returns>
public virtual int InsertableDefaultAddElseUpdate(List<T> list)
{
//会更新所有字段 有的字段不需要更新的设置不了
return SqlSugarHelper.Db.Storageable(list).DefaultAddElseUpdate().ExecuteCommand();
}
#endregion
#region 删除
/// <summary>
/// 删除 根据主键
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public virtual int Deleteable(T t)
{
return SqlSugarHelper.Db.Deleteable<T>().Where(t).ExecuteCommand();
}
/// <summary>
/// 逻辑删除 根据主键
/// </summary>
/// <param name="ModifyBy">修改人</param>
/// <param name="Ids">删除id集合</param>
/// <returns></returns>
public virtual int DeleteableLogic(string ModifyBy, List<int> Ids)
{
return SqlSugarHelper.Db.Deleteable<T>().In(Ids)
.IsLogic()
.ExecuteCommand("IsDelete", 1, "ModifyDate", "ModifyBy", ModifyBy);
}
#endregion
#endregion
#region 业务查询,根据业务需要重写方法
/// <summary>
/// 视图 多表联查 查询表未删除数据 （根据业务重写）
/// </summary>
/// <returns></returns>
public virtual List<UserListV> QueryableNotDeletedV()
{
//Type t = typeof(T);
var list = SqlSugarHelper.Db.Queryable<UserList>()
.LeftJoin<GroupList>((a, b) => (a.GroupId == b.Id && b.IsDelete == false))
.Where((a, b) => a.IsDelete == false)
.OrderBy(a => a.Id, OrderByType.Desc)
.Select((a, b) => new UserListV()
{
Id = a.Id,
},
true)//true表示 其余字段自动映射，根据字段名字
.ToList();
return list;
}
/// <summary>
/// 视图 多表联查 查询表未删除数据 按条件查询 （根据业务重写）
/// </summary>
/// <param name="v"></param>
/// <returns></returns>
public virtual List<IPListV> QueryableNotDeletedV(IPListV v)
{
//Type t = typeof(T);
var exp = Expressionable.Create<IPList, PurposeList>()
.And((a, b) => a.IsDelete == false)
.AndIF(!string.IsNullOrWhiteSpace(v.Floor), (a, b) => a.Floor == v.Floor)
.AndIF(!string.IsNullOrWhiteSpace(v.UserName), (a, b) => a.UserName.Contains(v.UserName))
.AndIF(!string.IsNullOrWhiteSpace(v.AssgnName), (a, b) => a.Assgn.Contains(v.AssgnName))
.AndIF(!string.IsNullOrWhiteSpace(v.IP), (a, b) => a.IP.Contains(v.IP))
.ToExpression();//注意 这一句 不能少
var list = SqlSugarHelper.Db.Queryable<IPList>()
.LeftJoin<PurposeList>((a, b) => (a.PurposeId == b.PID && b.IsDelete == false))
.Where(exp)
.OrderBy(a => a.Id, OrderByType.Desc)
.Select((a, b) => new IPListV()
{
Id = a.Id,
UserName = a.UserName,
PurposeName = b.Name,
},
true)//true表示 其余字段自动映射，根据字段名字
.ToList();
return list;
}
/// <summary>
/// 视图 多表联查 分页查询 查询表未删除数据 按条件查询 （根据业务重写）
/// </summary>
/// <param name="where"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public virtual List<IPListV> QueryablePageList(IPListV where, int pageIndex, int pageSize, out int totalCount)
{
//Type t = typeof(T);
totalCount = 0;
var exp = Expressionable.Create<IPList, PurposeList>()
.And((a, b) => a.IsDelete == false)
.AndIF(!string.IsNullOrWhiteSpace(where.Floor), (a, b) => a.Floor == where.Floor)
.AndIF(!string.IsNullOrWhiteSpace(where.IP), (a, b) => a.IP.Contains(where.IP))
.AndIF(where.SearchType == "UserName" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.UserName.Contains(where.SearchVal))
.AndIF(where.SearchType == "AssgnName" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.Assgn.Contains(where.SearchVal))
.AndIF(where.SearchType == "IPAddress" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.IP == where.SearchVal.Trim())
.AndIF(where.SearchType == "MacAddress" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.MacAddress.Contains(where.SearchVal))
.AndIF(where.SearchType == "NodeName" && !string.IsNullOrWhiteSpace(where.SearchVal), (a, b) => a.NodeName.Contains(where.SearchVal))
.ToExpression();//注意 这一句 不能少
var list = SqlSugarHelper.Db.Queryable<IPList>()
.LeftJoin<PurposeList>((a, b) => (a.PurposeId == b.PID && b.IsDelete == false))
.Where(exp)
//.OrderBy(a => a.Id, OrderByType.Desc)
.Select((a, b) => new IPListV()
{
Id = a.Id,
UserName = a.UserName,
PurposeName = b.Name,
},
true)//true表示 其余字段自动映射，根据字段名字
.ToPageList(pageIndex, pageSize, ref totalCount);
return list;
}
#endregion
}
}
