private static string BuildTopQuery(int pageIndex, int pageSize, string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields) { string str = string.IsNullOrEmpty(sortBy) ? pk : sortBy; string str2 = string.IsNullOrEmpty(filter) ? "" : ("WHERE " + filter); string str3 = string.IsNullOrEmpty(filter) ? "" : ("AND " + filter); StringBuilder builder = new StringBuilder(); builder.AppendFormat("SELECT TOP {0} {1} FROM {2} ", pageSize.ToString(CultureInfo.InvariantCulture), selectFields, table); if (pageIndex == 1) { builder.AppendFormat("{0} ORDER BY {1} {2}", str2, str, sortOrder.ToString()); } else { int num = (pageIndex - 1) * pageSize; if (sortOrder == SortAction.Asc) { builder.AppendFormat("WHERE {0} > (SELECT MAX({0}) FROM (SELECT TOP {1} {0} FROM {2} {3} ORDER BY {0} ASC) AS TMP) {4} ORDER BY {0} ASC", new object[] { str, num, table, str2, str3 }); } else { builder.AppendFormat("WHERE {0} < (SELECT MIN({0}) FROM (SELECT TOP {1} {0} FROM {2} {3} ORDER BY {0} DESC) AS TMP) {4} ORDER BY {0} DESC", new object[] { str, num, table, str2, str3 }); } } if (isCount) { builder.AppendFormat(";SELECT COUNT({0}) FROM {1} {2}", str, table, str2); } return builder.ToString(); }
//private Database database = DatabaseFactory.CreateDatabase(); private static string BuildTopQuery2(int pageIndex, int pageSize, string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields) { DbCommand sqlStringCommand = DatabaseFactory.CreateDatabase().GetSqlStringCommand(@"select t0.OrderId,t0.Remark,t1.SKU,t2.ProductName,t0.BundlingNum,t3.CompanyName,t0.ShipOrderNumber,t0.OrderStatus,t0.Amount ,t1.ItemListPrice,t0.ShipTo,t0.TelPhone,t0.Address,t0.ShipToDate,t0.Remark,t0.OrderTotal,t0.UserId,t0.UserName, t0.Freight,t0.OrderDate,t0.PayDate,t0.ShippingDate,t0.FinishDate,t0.ShipToDate,t0.BreachStatus,t0.JSC_Code from ExhibFlat_Orders t0 join ExhibFlat_OrderItems t1 on t0.OrderId=t1.OrderId join ExhibFlat_Products t2 on t1.ProductId = t2.ProductId join ExhibFlat_Suppliers t3 on t2.SupplierId=t3.SupplierId" ); DataSet ds = DatabaseFactory.CreateDatabase().ExecuteDataSet(sqlStringCommand); DataTable dt = ds.Tables[0]; string str = string.IsNullOrEmpty(sortBy) ? pk : sortBy; string str2 = string.IsNullOrEmpty(filter) ? "" : (" WHERE t0." + filter); string str3 = string.IsNullOrEmpty(filter) ? "" : ("AND t0." + filter); string str4 = string.IsNullOrEmpty(filter) ? "" : ("where" + filter); StringBuilder builder = new StringBuilder(); builder.AppendFormat(@"select top {0} t0.OrderId,t0.Remark,t1.SKU,t2.ProductName,t0.BundlingNum,t3.CompanyName,t0.ShipOrderNumber,t0.OrderStatus,t0.Amount ,t1.ItemListPrice,t0.ShipTo,t0.TelPhone,t0.Address,t0.ShipToDate,t0.OrderTotal,t0.UserId,t0.UserName, t0.Freight,t0.OrderDate,t0.PayDate,t0.ShippingDate,t0.FinishDate,t0.BreachStatus,t0.JSC_Code from ExhibFlat_Orders t0 join ExhibFlat_OrderItems t1 on t0.OrderId=t1.OrderId join ExhibFlat_Products t2 on t1.ProductId = t2.ProductId join ExhibFlat_Suppliers t3 on t2.SupplierId=t3.SupplierId ", pageSize.ToString(CultureInfo.InvariantCulture)); //SELECT TOP {0} {1} FROM {2} ", pageSize.ToString(CultureInfo.InvariantCulture), selectFields, "()"); if (pageIndex == 1) { builder.AppendFormat("{0} ORDER BY {1} {2}", str2, str, sortOrder.ToString()); // builder.AppendFormat("ORDER BY {1} {2}", str2, str, sortOrder.ToString()); } else { int num = (pageIndex - 1) * pageSize; if (sortOrder == SortAction.Asc) { builder.AppendFormat("WHERE {0} > (SELECT MAX({0}) FROM (SELECT TOP {1} {0} FROM {2} {3} ORDER BY {0} ASC) AS TMP) {4} ORDER BY {0} ASC", new object[] { str, num, table, str2, str3 }); } else { builder.AppendFormat("WHERE {0} < (SELECT MIN({0}) FROM (SELECT TOP {1} {0} FROM {2} {3} ORDER BY {0} DESC) AS TMP) {4} ORDER BY {0} DESC", new object[] { str, num, table, str2, str3 }); } } if (isCount) { builder.AppendFormat(";SELECT COUNT({0}) FROM {1} {2}", str, table, str4); } return(builder.ToString()); }
public static string BuildNotinQuery(int pageIndex, int pageSize, string sortBy, SortAction sortOrder, bool isCount, string table, string key, string filter, string selectFields) { string str = string.IsNullOrEmpty(filter) ? "" : ("WHERE " + filter); string str2 = string.IsNullOrEmpty(filter) ? "" : ("AND " + filter); string str3 = string.IsNullOrEmpty(sortBy) ? "" : ("ORDER BY " + sortBy + " " + sortOrder.ToString()); StringBuilder builder = new StringBuilder(); builder.AppendFormat("SELECT TOP {0} {1} FROM {2} ", pageSize.ToString(CultureInfo.InvariantCulture), selectFields, table); if (pageIndex == 1) { builder.AppendFormat("{0} {1}", str, str3); } else { int num = (pageIndex - 1) * pageSize; builder.AppendFormat("WHERE {0} NOT IN (SELECT TOP {1} {0} FROM {2} {3} {4}) {5} {4}", new object[] { key, num, table, str, str3, str2 }); } if (isCount) { builder.AppendFormat(";SELECT COUNT({0}) FROM {1} {2}", key, table, str); } return builder.ToString(); }
private static string BuildRownumberQuery(string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields, int partitionSize) { StringBuilder builder = new StringBuilder(); string str = string.IsNullOrEmpty(filter) ? "" : ("WHERE " + filter); if (partitionSize > 0) { builder.AppendFormat("SELECT TOP {0} {1}, ROW_NUMBER() OVER (ORDER BY ", partitionSize.ToString(CultureInfo.InvariantCulture), selectFields); } else { builder.AppendFormat("SELECT {0} , ROW_NUMBER() OVER (ORDER BY ", selectFields); } builder.AppendFormat("{0} {1}", string.IsNullOrEmpty(sortBy) ? pk : sortBy, sortOrder.ToString()); builder.AppendFormat(") AS RowNumber FROM {0} {1}", table, str); builder.Insert(0, "SELECT * FROM (").Append(") T WHERE T.RowNumber BETWEEN @StartNumber AND @EndNumber"); if (isCount && (partitionSize == 0)) { builder.AppendFormat(";SELECT COUNT(*) FROM {0} {1}", table, str); } return builder.ToString(); }
private static string CteRownumberQuery(string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields, int partitionSize) { StringBuilder builder = new StringBuilder(); string str = string.IsNullOrEmpty(filter) ? "" : ("WHERE " + filter); if (partitionSize > 0) { builder.AppendFormat("SELECT TOP {0} {1}, ROW_NUMBER() OVER (ORDER BY ", partitionSize.ToString(CultureInfo.InvariantCulture), selectFields); } else { builder.AppendFormat("SELECT {0} , ROW_NUMBER() OVER (ORDER BY ", selectFields); } builder.AppendFormat("{0} {1}", string.IsNullOrEmpty(sortBy) ? pk : sortBy, sortOrder.ToString()); builder.AppendFormat(") AS RowNumber FROM {0} {1}", table, str); builder.Insert(0, "SELECT * FROM (").Append(") T WHERE T.RowNumber BETWEEN @StartNumber AND @EndNumber ORDER BY sortStr"); if (isCount && (partitionSize == 0)) { builder.AppendFormat(@";with cte as(select convert(nvarchar(50), UserID) as sortStr,* from vw_aspnet_Chanels where ParentID=0 or ParentID is null union all select convert(nvarchar(50),convert(nvarchar(6), isnull(a.ParentID,0))+'-'+convert(nvarchar(6),isnull(a.UserID,0))) as sortStr,a.* from vw_aspnet_Chanels a join cte b on a.ParentID=b.UserID) SELECT COUNT({0}) FROM cte {1}", string.IsNullOrEmpty(sortBy) ? pk : sortBy, str); } return(builder.ToString()); }
public static string BuildNotinQuery(int pageIndex, int pageSize, string sortBy, SortAction sortOrder, bool isCount, string table, string key, string filter, string selectFields) { string str = string.IsNullOrEmpty(filter) ? "" : ("WHERE " + filter); string str2 = string.IsNullOrEmpty(filter) ? "" : ("AND " + filter); string str3 = string.IsNullOrEmpty(sortBy) ? "" : ("ORDER BY " + sortBy + " " + sortOrder.ToString()); StringBuilder builder = new StringBuilder(); builder.AppendFormat("SELECT TOP {0} {1} FROM {2} ", pageSize.ToString(CultureInfo.InvariantCulture), selectFields, table); if (pageIndex == 1) { builder.AppendFormat("{0} {1}", str, str3); } else { int num = (pageIndex - 1) * pageSize; builder.AppendFormat("WHERE {0} NOT IN (SELECT TOP {1} {0} FROM {2} {3} {4}) {5} {4}", new object[] { key, num, table, str, str3, str2 }); } if (isCount) { builder.AppendFormat(";SELECT COUNT({0}) FROM {1} {2}", key, table, str); } return(builder.ToString()); }
public virtual IList <T> Gets <T>(string sortBy, SortAction sortOrder, int?maxNum = default(int?)) where T : new() { Type typeFromHandle = typeof(T); List <T> list = new List <T>(); TableNameAttribute tableNameAttribute = (TableNameAttribute)Attribute.GetCustomAttribute(typeFromHandle, typeof(TableNameAttribute)); string text = "SELECT"; if (maxNum.HasValue) { text = text + " TOP " + maxNum.Value; } text = text + " * FROM " + tableNameAttribute.TableName + " ORDER BY " + sortBy + " " + sortOrder.ToString(); DbCommand sqlStringCommand = this.database.GetSqlStringCommand(text); using (IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand)) { while (dataReader.Read()) { T val = new T(); int fieldCount = dataReader.FieldCount; for (int i = 0; i < fieldCount; i++) { if (((IDataRecord)dataReader)[i] != DBNull.Value) { PropertyInfo property = typeFromHandle.GetProperty(dataReader.GetName(i), BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public | BindingFlags.GetProperty); if (property != (PropertyInfo)null) { Type type = property.PropertyType; if (type.IsGenericType && type.GetGenericTypeDefinition().Equals(typeof(Nullable <>))) { NullableConverter nullableConverter = new NullableConverter(type); type = nullableConverter.UnderlyingType; } if (type.IsEnum) { object value = Enum.ToObject(type, ((IDataRecord)dataReader)[i]); property.SetValue(val, value, null); } else { object obj = Convert.ChangeType(((IDataRecord)dataReader)[i], type); if (type.Equals(typeof(string)) && obj == null) { obj = string.Empty; } property.SetValue(val, obj, null); } } } } list.Add(val); } } return(list); }
private static string BuildRownumberQuery(string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields, int partitionSize, bool isAdmin) { if (!isAdmin) { if (!String.IsNullOrEmpty(sortBy)) { if (sortBy.Equals("DisplaySequence") && table.Equals("vw_Ecshop_BrowseProductList p")) { sortBy = "Fraction"; } } } StringBuilder stringBuilder = new StringBuilder(); string text = string.IsNullOrEmpty(filter) ? "" : ("WHERE " + filter); if (partitionSize > 0) { stringBuilder.AppendFormat("SELECT TOP {0} {1}, ROW_NUMBER() OVER (ORDER BY ", partitionSize.ToString(CultureInfo.InvariantCulture), selectFields); } else { stringBuilder.AppendFormat("SELECT {0} , ROW_NUMBER() OVER (ORDER BY ", selectFields); } stringBuilder.AppendFormat("{0} {1}", string.IsNullOrEmpty(sortBy) ? pk : sortBy, sortOrder.ToString()); stringBuilder.AppendFormat(") AS RowNumber FROM {0} {1}", table, text); stringBuilder.Insert(0, "SELECT * FROM (").Append(") T WHERE T.RowNumber BETWEEN @StartNumber AND @EndNumber"); if (isCount && partitionSize == 0) { stringBuilder.AppendFormat(";SELECT COUNT({0}) FROM {1} {2}", string.IsNullOrEmpty(sortBy) ? pk : sortBy, table, text); } return(stringBuilder.ToString()); }
private static string BuildRownumberQuery(string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields, int partitionSize) { StringBuilder builder = new StringBuilder(); string str = string.IsNullOrEmpty(filter) ? "" : ("WHERE " + filter); if (partitionSize > 0) { builder.AppendFormat("SELECT TOP {0} {1}, ROW_NUMBER() OVER (ORDER BY ", partitionSize.ToString(CultureInfo.InvariantCulture), selectFields); } else { builder.AppendFormat("SELECT {0} , ROW_NUMBER() OVER (ORDER BY ", selectFields); } builder.AppendFormat("{0} {1}", string.IsNullOrEmpty(sortBy) ? pk : sortBy, sortOrder.ToString()); builder.AppendFormat(") AS RowNumber FROM {0} {1}", table, str); builder.Insert(0, "SELECT * FROM (").Append(") T WHERE T.RowNumber BETWEEN @StartNumber AND @EndNumber"); if (isCount && (partitionSize == 0)) { builder.AppendFormat(";SELECT COUNT({0}) FROM {1} {2}", string.IsNullOrEmpty(sortBy) ? pk : sortBy, table, str); } return builder.ToString(); }
public static string BuildRownumberQuery(string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields, int partitionSize) { StringBuilder stringBuilder = new StringBuilder(); string text = string.IsNullOrEmpty(filter) ? "" : ("WHERE " + filter); if (partitionSize > 0) { stringBuilder.AppendFormat("SELECT TOP {0} {1}, ROW_NUMBER() OVER (ORDER BY ", partitionSize.ToString(CultureInfo.InvariantCulture), selectFields); } else { stringBuilder.AppendFormat("SELECT {0} , ROW_NUMBER() OVER (ORDER BY ", selectFields); } stringBuilder.AppendFormat("{0} {1}", string.IsNullOrEmpty(sortBy) ? pk : sortBy, sortOrder.ToString()); stringBuilder.AppendFormat(") AS RowNumber FROM {0} {1}", table, text); stringBuilder.Insert(0, "SELECT * FROM (").Append(") T WHERE T.RowNumber BETWEEN @StartNumber AND @EndNumber"); string text2 = ""; if (!string.IsNullOrEmpty(sortBy)) { if (sortBy.IndexOf(",") > 0) { text2 = sortBy.Substring(0, sortBy.IndexOf(",")); } else { text2 = sortBy; } } if (isCount && partitionSize == 0) { stringBuilder.AppendFormat(";SELECT COUNT(0) FROM {1} {2}", string.IsNullOrEmpty(text2) ? pk : text2, table, text); } return(stringBuilder.ToString()); }