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 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 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());
}
