private static string GetUpdateString <T>(T t, string whereKey)
{
var type = typeof(T);
TblStruct st = type.GetTblStruct();
string setString = string.Empty;
foreach (var item in st.ListCol)
{
if (item.NotUpdate)
{
continue;
}
if (item.ColName == whereKey)
{
continue;
}
var p = type.GetProperty(item.ClassColName);
var obj = p.GetValue(t, null);
if (obj == null)
{
setString += item.ColName + "=NULL,";
}
else
{
setString += item.ColName + "='" + Convert.ToString(obj).Replace("'", "''") + "',";
}
}
if (setString.Length > 0)
{
setString = setString.Remove(setString.Length - 1);
}
string sql = "update " + st.TblName + " set " + setString;
return(sql);
}
/// <summary>
/// 得到List集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sqlWhere">sqlWhere统一以and开头</param>
/// <returns></returns>
public static List <T> GetList <T>(string sqlWhere = null) where T : new()
{
DealSqlWhere(ref sqlWhere);
var type = typeof(T);
TblStruct st = type.GetTblStruct();
string sql = "select " + st.SqlSearchCol + " from " + st.TblName + " where " + sqlWhere;
return(GetListBySql <T>(sql));
}
/// <summary>
/// 得到单对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="only">唯一键</param>
/// <returns></returns>
public static T GetModelByOnly <T>(object only) where T : new()
{
var type = typeof(T);
TblStruct st = type.GetTblStruct();
string sql = "select top 1 " + st.SqlSearchCol + " from " + st.TblName + " where " + st.OnlyPrimary + "=" +
(st.TypeOnlyPrimary ? only : ("'" + only + "'"));
var list = GetListBySql <T>(sql);
if (list.Count > 0)
{
return(list[0]);
}
return(default(T));
}
/// <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>
/// 得到实体对应数据库关系
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
internal static TblStruct GetTblStruct(this Type type)
{
lock (_sycn)
{
var key = type.FullName;
if (_dic.ContainsKey(key))
{
return(_dic[key]);
}
//完善TblStruct
TblStruct st = new TblStruct();
var objs = type.GetCustomAttributes(typeof(TblNameAttribute), false);
string tblName = objs.Length == 0 ? type.Name : ((TblNameAttribute)objs[0]).TblName;
st.TblName = tblName;
st.ListCol = new List <ColStruct>();
var ps = type.GetProperties();
bool hasPrimaryKey = false;
foreach (var item in ps)
{
ColStruct col = new ColStruct();
col.ClassColName = item.Name;
objs = item.GetCustomAttributes(typeof(ColNameAttribute), false);
col.ColName = objs.Length == 0 ? item.Name : ((ColNameAttribute)objs[0]).ColName;
col.ColType = item.PropertyType;
objs = item.GetCustomAttributes(typeof(NotDataBaseAttribute), false);
bool notDataBase = objs.Length > 0;
if (notDataBase)
{
col.NotSearch = col.NotUpdate = col.NotSearch = true;
}
else
{
objs = item.GetCustomAttributes(typeof(NotSearchAttribute), false);
col.NotSearch = objs.Length > 0;
objs = item.GetCustomAttributes(typeof(NotUpdateAttribute), false);
col.NotUpdate = objs.Length > 0;
objs = item.GetCustomAttributes(typeof(NotAddAttribute), false);
col.NotAdd = objs.Length > 0;
}
objs = item.GetCustomAttributes(typeof(OnlyPrimaryAttribute), false);
col.OnlyPrimary = objs.Length > 0;
objs = item.GetCustomAttributes(typeof(PrimaryKeyAttribute), false);
col.PrimaryKey = objs.Length > 0;
if (col.PrimaryKey)
{
col.Identity = ((PrimaryKeyAttribute)(objs[0])).IsIdentity;
hasPrimaryKey = true;
}
st.ListCol.Add(col);
}
if (hasPrimaryKey == false)
{
var col = st.ListCol.FirstOrDefault(m => m.ColName.ToLower() == "id" || m.ColName.ToLower() == (m.ColName + "id").ToLower());
if (col != null)
{
col.PrimaryKey = true;
col.Identity = true;
}
else
{
throw new Exception(st.TblName + " 没有主键");
}
}
_dic.Add(key, st);
return(st);
}
}
