/// <summary>
/// 得到List集合 只支持有序的字段(max,min函数支持的字段)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlWhere"></param>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <param name="orderBy">默认是主键(不是代码写的默认值"")</param>
/// <param name="desc"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public static List <T> GetListByPage <T>(ref int totalCount, string sqlWhere = "", int page = 1, int pageSize = 20,
string orderBy = "",
bool desc = true) where T : new()
{
DealSqlWhere(ref sqlWhere);
var type = typeof(T);
TblStruct st = type.GetTblStruct();
if (string.IsNullOrEmpty(orderBy))
{
orderBy = st.PrimaryKey;
}
var skipCount = (pageSize * (page - 1));
var maxMin = string.Empty;
if (skipCount > 0)
{
//string sqlServerIsNull = "ISNULL(" + (desc ? "MIN" : "MAX") + "(" + orderBy + "),0)";
string oracleIsNull = "iif(IsNull(" + (desc ? "MIN" : "MAX") + "(" + orderBy + ")), 0, " +
(desc ? "MIN" : "MAX") + "(" + orderBy + "))";
maxMin = " and " + orderBy + (desc ? "<" : ">") +
"(select " + oracleIsNull + " from (select top " +
skipCount + " " + orderBy + " from " + st.TblName + " order by " + orderBy + (desc ? " desc" : " asc") + "))";
}
string sql = "select top " + pageSize + " " + st.SqlSearchCol + " from " + st.TblName +
" where " + sqlWhere + maxMin + " order by " + orderBy + (desc ? " desc" : " asc");
if (totalCount == -1)
{
totalCount = 0;
}
else
{
string sqlTotalCount = "select COUNT(1) from " + st.TblName + " where " + sqlWhere;
var obj = DbHelperAccess.GetScalar(sqlTotalCount);
if (obj == null)
{
totalCount = 0;
}
else
{
totalCount = Convert.ToInt32(obj);
}
}
return(GetListBySql <T>(sql));
}
/// <summary>
/// 主键是自增,则返回主键ID
/// 不是,则返回影响行数
/// 默认一般是>0就是成功
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public static int Add <T>(T t)
{
lock (_sycn)
{
var type = typeof(T);
var st = type.GetTblStruct();
string insertCol = string.Empty;
string values = string.Empty;
foreach (var item in st.ListCol)
{
if (st.Identity) //自增的主键,主键就不赋值
{
if (item.PrimaryKey)
{
continue;
}
}
if (item.NotAdd)
{
continue;
}
insertCol += item.ColName + ",";
var p = type.GetProperty(item.ClassColName);
var obj = p.GetValue(t, null);
if (obj == null)
{
values += "NULL,";
}
else
{
values += "'" + Convert.ToString(obj).SqlClear() + "',";
}
}
if (insertCol.Length > 0)
{
insertCol = insertCol.Remove(insertCol.Length - 1);
}
if (values.Length > 0)
{
values = values.Remove(values.Length - 1);
}
string sql = "insert into " + st.TblName + "(" + insertCol + ") values(" + values + ");";
if (st.Identity)
{
var count2 = DbHelperAccess.ExecuteCommand(sql);
if (count2 > 0)
{
sql = "select @@identity;";
object obj = DbHelperAccess.GetScalar(sql);
if (obj == null)
{
return(0);
}
var id = Convert.ToInt32(obj);
var p = type.GetProperty(st.ClassPrimaryKey);
p.SetValue(t, id, null);
return(id);
}
return(0);
}
var count = DbHelperAccess.ExecuteCommand(sql);
return(count);
}
}
