public class BaseDAL<T>
{
/// <summary>
/// 数据分页
/// </summary>
/// <param name="minSize">最小数</param>
/// <param name="pageSize">查询的数量</param>
/// <param name="orderby">排序字段</param>
/// <param name="desc">排序方式:true 正序,false 倒序</param>
/// <param name="sqlwhere">条件(要and开头)</param>
/// <param name="totalCount">总数</param>
/// <param name="sqlParameterDic">sqlParameter参数</param>
/// <returns></returns>
public DataTable GerDatatable(int minSize, int pageSize, string orderby, bool desc, string sqlwhere, ref int totalCount, Dictionary<string, object> sqlParameterDic)
{
System.Text.StringBuilder sql = new System.Text.StringBuilder();
System.Text.StringBuilder where = new System.Text.StringBuilder(" where 1=1 ");
where.Append(sqlwhere);
DataSet dataSet = new DataSet();
sql.Append(string.Format(@"
select count(0) totalCount from {3} {0} ;
select top {1} * from
(
select row_number() over(order by {4} {5}) counts,
*
from {3}
{0}
)
tas where counts>{2} order by {4} desc;
", where, pageSize, minSize, typeof(T).Name, orderby, desc ? string.Empty : "desc"));
if (sqlParameterDic.Count > 0)
{
dataSet = DB.ExecuteDataset(CommandType.Text, sql.ToString(), GetSqlParameters(sqlParameterDic).ToArray());
}
else
{
dataSet = DB.ExecuteDataset(CommandType.Text, sql.ToString());
}
DataRowCollection dr = dataSet.Tables[0].Rows;
if (dr.Count > 0)
{
totalCount = Convert.ToInt32(dr[0]["totalCount"]);
}
return dataSet.Tables[1];
}
/// <summary>
/// 删除元素
/// </summary>
/// <param name="sq1where">条件(要and开头)</param>
/// <param name="sqlParameterDic">sqlParameter参数</param>
/// <returns></returns>
public int Delete(string sq1where, Dictionary<string, object> sqlParameterDic)
{
if (string.IsNullOrEmpty(sq1where)) { return 0; }
string sql = string.Format(@"delete {1} where 1=1 {0} ", sq1where, typeof(T).Name);
return DB.ExecuteNonQuery(CommandType.Text, sql, GetSqlParameters(sqlParameterDic).ToArray());
}
/// <summary>
/// 获取数据集合
/// </summary>
/// <param name="sqlwhere">条件(要and开头)</param>
/// <param name="orderby">排序字段</param>
/// <param name="isdes">排序方式:true 正序,false 倒序</param>
/// <param name="sqlParameterDic">sqlParameter参数</param>
/// <returns></returns>
public DataTable GerDatatable(string sqlwhere, string orderby, bool isdes, Dictionary<string, object> sqlParameterDic)
{
string sql = string.Format(@" select * from (select row_number() over(order by {2} {3}) rowid,* from {0} where 1=1 {1}) dt", typeof(T).Name, sqlwhere, orderby, isdes ? string.Empty : "desc");
NLog.LogManager.GetCurrentClassLogger().Error(sql);
DataTable dt = new DataTable();
if (sqlParameterDic.Count > 0)
{
dt = DB.ExecuteDataTable(CommandType.Text, sql, GetSqlParameters(sqlParameterDic).ToArray());
}
else dt = DB.ExecuteDataTable(CommandType.Text, sql);
return dt;
}
/// <summary>
/// 构建sqlparameter参数
/// </summary>
/// <param name="sqlParameterDic"></param>
/// <returns></returns>
private List<SqlParameter> GetSqlParameters(Dictionary<string, object> sqlParameterDic)
{
List<SqlParameter> sqlParameters = new List<SqlParameter>();
foreach (var item in sqlParameterDic)
{
sqlParameters.Add(new SqlParameter(item.Key, item.Value));
}
return sqlParameters;
}
/// <summary>
/// 增加记录
/// </summary>
/// <param name="obj">增加的对象</param>
/// <returns></returns>
public int Add(T obj)
{
System.Text.StringBuilder sql = new StringBuilder();
Type t = obj.GetType();
List<PropertyInfo> propertyInfos = t.GetProperties().ToList();
foreach (var item in propertyInfos)
{
object[] attrs = item.GetCustomAttributes(typeof(EntityAttribute), true);
if (attrs.Length > 0)
{
EntityAttribute attr = attrs[0] as EntityAttribute;
if (attr.name.Contains("Primary key"))
{
propertyInfos.Remove(item);
break;
}
}
}
sql.Append(string.Format("insert into {0}(", typeof(T).Name));
List<string> propertylist = propertyInfos.Select(p => p.Name).ToList();
sql.Append(string.Join(",", propertylist));
sql.Append(")Values(");
List<string> pamlist = propertylist.Select(s => "@" + s).ToList();
sql.Append(string.Join(",", pamlist));
sql.Append(")");
List<SqlParameter> sqlParameterlist = new List<SqlParameter>();
foreach (PropertyInfo item in propertyInfos)
{
if (item.GetValue(obj, null) != null)
sqlParameterlist.Add(new SqlParameter("@" + item.Name, item.GetValue(obj, null)));
}
return DB.ExecuteNonQuery(CommandType.Text, sql.ToString(), sqlParameterlist.ToArray());
}
/// <summary>
/// 修改记录
/// </summary>
/// <param name="obj">条件(要and开头)</param>
/// <param name="sqlwhere"></param>
public int Edit(T obj, string sqlwhere)
{
System.Text.StringBuilder sql = new StringBuilder();
List<SqlParameter> sqlParameterlist = new List<SqlParameter>();
Type t = obj.GetType();
List<PropertyInfo> propertyInfos = t.GetProperties().ToList();
foreach (var item in propertyInfos)
{
object[] attrs = item.GetCustomAttributes(typeof(EntityAttribute), true);
if (attrs.Length > 0)
{
EntityAttribute attr = attrs[0] as EntityAttribute;
if (attr.name.Contains("Primary key"))
{
if (item.GetValue(obj, null) != null)
sqlParameterlist.Add(new SqlParameter("@" + item.Name, item.GetValue(obj, null)));
propertyInfos.Remove(item);
break;
}
}
}
sql.Append(string.Format("update {0} set ", typeof(T).Name));
List<string> propertylist = propertyInfos.Select(p => p.Name).ToList();
foreach (PropertyInfo item in propertyInfos)
{
if (item.GetValue(obj, null) != null)
{
sql.Append(string.Format("{0}=@{0},", item.Name));
sqlParameterlist.Add(new SqlParameter("@" + item.Name, item.GetValue(obj, null)));
}
}
string updatesql = string.Format("{0} where 1=1 {1}", sql.ToString().TrimEnd(','), sqlwhere);
return DB.ExecuteNonQuery(CommandType.Text, updatesql, sqlParameterlist.ToArray());
}
}
C# 通过反射生成sql
于 2022-02-09 15:41:08 首次发布