public DataTable GetFilteredData(int startRowIndex, int maximumRows, string priority, string sortExpression)
{
if (maximumRows == 0)
{
maximumRows = int.MaxValue;
}
string sql = @"WITH OrderedOrders AS
(
SELECT ORDERS.*, ROW_NUMBER() OVER(ORDER BY {0}) AS ROW_NUMBER
FROM ORDERS
WHERE PRIORITY = ISNULL({3}, PRIORITY)
)
SELECT * FROM OrderedOrders
WHERE ROW_NUMBER > {1} AND ROW_NUMBER <= {2};";
if (string.IsNullOrEmpty(sortExpression))
{
sortExpression = "SORT_ID DESC";
}
sql = string.Format(sql, sortExpression, startRowIndex, startRowIndex + maximumRows,
string.IsNullOrEmpty(priority) ? "NULL" : TSqlBuilder.Instance.CheckQuotationMark(priority, true));
return(ObjData.RunSqlReturnDS(sql).Tables[0]);
}
public int GetFilteredDataCount(string priority)
{
string sql = "SELECT COUNT(*) FROM ORDERS";
if (string.IsNullOrEmpty(priority) == false)
{
sql += " WHERE PRIORITY = " + TSqlBuilder.Instance.CheckQuotationMark(priority, true);
}
return((int)ObjData.RunSqlReturnObject(sql));
}
public static DataView GetProductsOfOrders(string orderID)
{
DataTable table = new DataTable("ORDERS_PRODUCTS");
if (string.IsNullOrEmpty(orderID) == false)
{
string sql = string.Format("SELECT * FROM ORDERS_PRODUCTS WHERE ORDER_ID = '{0}'", orderID.Replace("\'", "\'\'"));
table = ObjData.RunSqlReturnDS(sql).Tables[0];
}
return(table.DefaultView);
}
public Order GetOrder(string orderID)
{
string sql = string.Format("SELECT * FROM ORDERS WHERE ORDER_ID = '{0}'", orderID.Replace("\'", "\'\'"));
DataSet ds = ObjData.RunSqlReturnDS(sql);
if (ds.Tables[0].Rows.Count == 0)
{
throw new ApplicationException(string.Format("不能找到OrderID为{0}的记录", orderID));
}
return(GetOrderFromDataRow(ds.Tables[0].Rows[0]));
}
public DataTable GetUnitDefine()
{
//using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
//{
// conn.Open();
// SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM UNIT_DEFINE ORDER BY UNIT_ID", conn);
// DataTable table = new DataTable("UNIT_DEFINE");
// adapter.Fill(table);
// return table;
//}
return(ObjData.RunSqlReturnDS("SELECT * FROM UNIT_DEFINE ORDER BY UNIT_ID").Tables[0]);
}
public DataView GetData(int startRowIndex, int maximumRows, string sortExpression)
{
string sql = @"WITH OrderedOrders AS
(
SELECT ORDERS.*, ROW_NUMBER() OVER(ORDER BY {0}) AS ROW_NUMBER
FROM ORDERS
)
SELECT * FROM OrderedOrders
WHERE ROW_NUMBER > {1} AND ROW_NUMBER <= {2};";
if (string.IsNullOrEmpty(sortExpression))
{
sortExpression = "SORT_ID DESC";
}
sql = string.Format(sql, sortExpression, startRowIndex, startRowIndex + maximumRows);
return(ObjData.RunSqlReturnDS(sql).Tables[0].DefaultView);
}
public DataView GetOrdersPruductsByOrderIDs(params string[] orderIDs)
{
if (orderIDs.Length == 0)
{
throw new ArgumentException("参数orderIDs的个数必须大于零", "orderIDs");
}
StringBuilder strB = new StringBuilder(256);
for (int i = 0; i < orderIDs.Length; i++)
{
if (strB.Length > 0)
{
strB.Append(", ");
}
strB.AppendFormat("\'{0}\'", orderIDs[i]);
}
string sql = string.Format("SELECT * FROM ORDERS_PRODUCTS WHERE ORDER_ID IN ({0})", strB.ToString());
return(ObjData.RunSqlReturnDS(sql).Tables[0].DefaultView);
}
public List <Order> GetOrdersList()
{
return(GetOrdersFromDataView(ObjData.RunSqlReturnDS("SELECT * FROM ORDERS ORDER BY SORT_ID DESC").Tables[0].DefaultView));
}
public DataTable GetCategories()
{
return(ObjData.RunSqlReturnDS("SELECT * FROM CATEGORY ORDER BY CATEGORY_ID").Tables[0]);
}
public int GetOrdersCount()
{
return((int)ObjData.RunSqlReturnObject("SELECT COUNT(*) FROM ORDERS "));
}
public DataView GetData()
{
return(ObjData.RunSqlReturnDS("SELECT * FROM ORDERS").Tables[0].DefaultView);
}
public static string GetCustomerFullNameByShortName(string shortName)
{
return(ObjData.RunSqlReturnObject("SELECT CUSTOMER_NAME FROM CUSTOMER_TEMPLATE WHERE SHORT_NAME = '" + shortName + "'").ToString());
}
public static DataView GetOrders(int count)
{
return(ObjData.RunSqlReturnDS(string.Format("SELECT TOP {0} * FROM ORDERS", count)).Tables[0].DefaultView);
}
