using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DBUnity;
using PMS.Common;
namespace PMS.DAL
{
public class BQuery<T>
{
public T GetModel(string strWhere, string strCols, params SqlParameter[] paras)
{
//生成查询语句
string selSql = CreateSql.CreateSelectSql<T>(strWhere, strCols);
//生成reader对象
SqlDataReader reader = SQLHelper.ExecuteReader(selSql, 1, paras);
//转为实体对象
T model = DbConvert.SqlDataReaderToModel<T>(reader, strCols);
reader.Close();
return model;
}
//根据ID获取实体信息
public T GetById(int id, string strCols)
{
Type type = typeof(T);
string strWhere = $"[{type.GetPrimary()}]=@Id";
SqlParameter[] paras = { new SqlParameter("@Id", id) };
return GetModel(strWhere, strCols, paras);
}
//根据条件判断存在
public bool Exists(string strWhere, params SqlParameter[] paras)
{
Type type = typeof(T);
string sql = $"SELECT COUNT(1) FROM {type.GetTName()} WHERE {strWhere}";
object val = SQLHelper.ExecuteScalar(sql, 1, paras);
if (val.GetInt() > 0)
return true;
else
return false;
}
public bool ExistsByName(string sName, string vName)
{
string strWhere = $"{sName}=@{sName}";
SqlParameter[] paras =
{
new SqlParameter($"@{sName}",vName)
};
return Exists(strWhere, paras);
}
//在同一级别下是否重名
public bool ExistsByName(string sName,string vName,string sParent,int parId)
{
string strWhere = $"{sName}=@{sName}";
if (parId > 0)
strWhere += $" and {sParent}=@{sParent}";
strWhere += " and isDelete = 0";
SqlParameter[] paras =
{
new SqlParameter($"{sName}",vName),
new SqlParameter($"{sParent}",parId)
};
return Exists(strWhere, paras);
}
public List<T> GetModelList(string strWhere,string strCols,params SqlParameter[] paras)
{
if (string.IsNullOrEmpty(strWhere))
{
strWhere = "1=1";
}
string selSql = CreateSql.CreateSelectSql<T>(strWhere, strCols);
SqlDataReader reader = SQLHelper.ExecuteReader(selSql, 1, paras);
List<T> list = DbConvert.SqlDataReaderToList<T>(reader, strCols);
reader.Close();
return list;
}
public List<T> GetModelList(string cols)
{
return GetModelList("IsDeleted=0", cols);
}
//返回带行号的列表
public List<T> getRowModelList(string strWhere,string strCols,params SqlParameter[] paras)
{
if (string.IsNullOrEmpty(strWhere))
strWhere = "1=1";
//生成SQL语句
string selSql = CreateSql.CreateSelectSql<T>(strWhere, strCols);
SqlDataReader reader = SQLHelper.ExecuteReader(selSql, 1, paras);
List<T> list = DbConvert.SqlDataReaderToList<T>(reader, strCols + ",Id");
reader.Close();
return list;
}
public DataTable GetList(string sql,int isProc,string strCols,params SqlParameter[] paras)
{
List<string> listCols = strCols.GetStrList(',', true);
DataTable dt = SQLHelper.GetDataTable(sql, isProc, paras);
return dt;
}
public DataSet GetDs(string sql,int isProc,params SqlParameter[] paras)
{
DataSet ds = SQLHelper.GetDataSet(sql, isProc, paras);
return ds;
}
//分页查询
public DataSet GetPageDs<S>(string strWhere,int typeId,string keywords,string strCols,string proName,int startIndex,int pageSize)
{
List<SqlParameter> listParas = new List<SqlParameter>();
listParas.Add(new SqlParameter("@typeId", typeId));
listParas.Add(new SqlParameter("@keywords", keywords));
string sql = CreateSql.CreateRowsSelectSql<S>(strWhere, strCols);
listParas.Add(new SqlParameter("@sql", sql));
listParas.Add(new SqlParameter("@startIndex", startIndex));
listParas.Add(new SqlParameter("@endIndex", startIndex + pageSize-1));
DataSet ds = GetDs(proName, 2, listParas.ToArray());
return ds;
}
}
}
BQuery
最新推荐文章于 2024-08-22 17:56:09 发布