public static T FindByIdentity(object ideneity)
{
Type type = typeof(T);
FieldItem identityField = FieldItem.GetIdentityField(type);
if (identityField == null)
{
return(default(T));
}
string sql = string.Format("Select * From [{0}] WHERE {1}=@{1}", FieldItem.GetTableName(type), identityField.ColumnName);
SqlCommand cmd = new SqlCommand(sql);
cmd.Parameters.AddWithValue("@" + identityField.ColumnName, ideneity);
DataSet ds = Database.ExecuteDataSet(cmd);
List <T> list = new List <T>();
LoadData(list, ds);
if (list == null || list.Count == 0)
{
return(default(T));
}
else
{
return(list[0]);
}
}
public static List <T> FindAllByPage(out int total, int pageSize, int pageIndex, out int pageCount, string where, PageOrder order)
{
if (string.IsNullOrEmpty(where))
{
where = " 1 = 1";
}
string orderStr = string.Empty;
if (order != null && order.Name != null && !string.IsNullOrEmpty(order.Name))
{
orderStr = string.Format(@" ORDER BY {0} {1}", order.Name, order.Direction.ToString());
}
FieldItem identityField = FieldItem.GetIdentityField(typeof(T));
if (identityField == null || string.IsNullOrEmpty(identityField.ColumnName))
{
throw new Exception("分页查询需要设置一个Identity列");
}
string sql = string.Empty;
pageIndex = pageIndex - 1;
if (pageIndex < 0)
{
pageIndex = 0;
}
if (pageIndex == 0)
{
sql = string.Format(@"SELECT TOP({1})* FROM {0} WHERE {2} {3}",
FieldItem.GetTableName(typeof(T)), pageSize, where, orderStr);
}
else
{
sql = string.Format(@"SELECT TOP({3})* FROM {0} WHERE ({1} > (SELECT MAX({1}) FROM (SELECT TOP {4}({1}) FROM {0} ORDER BY {1})AS T)) AND {2} {5}",
FieldItem.GetTableName(typeof(T)), identityField.ColumnName, where, pageSize, pageSize * pageIndex, orderStr);
}
total = QueryCount(where);
pageCount = total / pageSize + (total % pageSize == 0 ? 0 : 1);
List <T> list = new List <T>();
DataSet ds = Query(sql);
LoadData(list, ds);
return(list);
}
public static List <T> FindAllByPage(Pager pager, string where)
{
if (string.IsNullOrEmpty(where))
{
where = " 1 = 1";
}
FieldItem identityField = FieldItem.GetIdentityField(typeof(T));
if (identityField == null || string.IsNullOrEmpty(identityField.ColumnName))
{
throw new Exception("分页查询需要设置一个Identity列");
}
if (pager.Order == null || string.IsNullOrEmpty(pager.Order.Name))
{
pager.Order = new PageOrder();
pager.Order.Name = identityField.ColumnName;
pager.Order.Direction = OrderDirection.Desc;
}
pager.TotalCount = QueryCount(where);
int pageCount = pager.TotalCount / pager.PageSize + (pager.TotalCount % pager.PageSize == 0 ? 0 : 1);
if (pageCount != 0 && pager.PageIndex > pageCount)
{
pager.PageIndex = pageCount;
}
string sql = string.Format(@"SELECT A.* FROM [{0}] AS A INNER JOIN(SELECT ROWNUM,[{1}] FROM(SELECT ROW_NUMBER() OVER(ORDER BY [{2}] {3}) AS ROWNUM,[{1}] FROM [{0}] WHERE {6}) AS T
WHERE ROWNUM BETWEEN {4} AND {5})AS B ON A.[{1}] = B.[{1}] ORDER BY B.ROWNUM",
FieldItem.GetTableName(typeof(T)), identityField.ColumnName, pager.Order.Name, pager.Order.Direction.ToString(), pager.PageSize * (pager.PageIndex - 1) + 1, pager.PageSize * pager.PageIndex, where);
List <T> list = new List <T>();
DataSet ds = Query(sql);
LoadData(list, ds);
return(list);
}
public virtual int Update()
{
Type type = base.GetType();
FieldItem identityField = FieldItem.GetIdentityField(type);
if (identityField == null)
{
throw new Exception("使用该方法需要配置Identity字段");
}
IList <FieldItem> dataObjectFields = FieldItem.GetDataObjectFields(type);
SqlCommand cmd = new SqlCommand();
StringBuilder sb = new StringBuilder();
foreach (FieldItem item in dataObjectFields)
{
if ((item.DataObjectField != null) && !item.DataObjectField.IsIdentity)
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.AppendFormat("[{0}]=@{0}", item.ColumnName);
cmd.Parameters.AddWithValue("@" + item.ColumnName, type.GetProperty(item.ColumnName).GetValue(this, null));
}
}
string sql = string.Format(@"UPDATE {0} SET {1} WHERE [{2}]=@{2}", FieldItem.GetTableName(type), sb.ToString(), identityField.ColumnName);
cmd.Parameters.AddWithValue("@" + identityField.ColumnName, type.GetProperty(identityField.ColumnName).GetValue(this, null));
cmd.CommandText = sql;
return(Database.ExecuteNonQuery(cmd));
}
