public JsonFlexiGridData QueryAllCategories()
{
PageView view = new PageView();
view.PageSize = 10000;
view.PageIndex = 0;
return(QueryDataForFlexGridByPager("CategoryID,CategoryName", "Categories", "", "CategoryID", "", view));
}
/// <summary>
/// Queries all product.
/// </summary>
/// <param name="view">The view.</param>
/// <param name="categoryid">The categoryid.</param>
/// <returns></returns>
public JsonFlexiGridData QueryAllProduct(PageView view, int categoryid)
{
string where = "";
if (categoryid > 0)
{
where = " and CategoryId=" + categoryid;
}
return(QueryDataForFlexGridByPager("ProductID,ProductName,QuantityPerUnit,UnitPrice,UnitsInStock,UnitsOnOrder", "Products", "", "ProductID", where, view));
}
public JsonFlexiGridData QueryDataForFlexGridByPager(string columns, string table, string order, string pk, string strparams,
PageView view)
{
if (string.IsNullOrEmpty(order) && view.OrderBy != null && view.OrderBy.Count > 0)
{
order = view.OrderBy.ToString();
}
if (string.IsNullOrEmpty(strparams) && view.QuickLuanch != null)
{
strparams = view.QuickLuanch.ToString();
}
var Para = new SqlParameter[8];
Para[0] = new SqlParameter("@SQLPARAMS", SqlDbType.NVarChar, 2000)
{
Value = strparams
};
Para[1] = new SqlParameter("@PAGESIZE", SqlDbType.Int, 4)
{
Value = view.PageSize
};
Para[2] = new SqlParameter("@PAGEINDEX", SqlDbType.Int, 4)
{
Value = view.PageIndex
};
Para[3] = new SqlParameter("@SQLTABLE", SqlDbType.VarChar, 1000)
{
Value = table
};
Para[4] = new SqlParameter("@SQLCOLUMNS", SqlDbType.VarChar, 4000)
{
Value = columns
};
Para[5] = new SqlParameter("@SQLPK", SqlDbType.VarChar, 50)
{
Value = pk
};
Para[6] = new SqlParameter("@SQLORDER", SqlDbType.VarChar, 200)
{
Value = order
};
Para[7] = new SqlParameter("@Count", SqlDbType.Int, 4)
{
Direction = ParameterDirection.Output
};
JsonFlexiGridData data = new JsonFlexiGridData {
page = view.PageIndex + 1, total = view.RecordCount
};
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.dbConstr, System.Data.CommandType.StoredProcedure, "PAGESELECT", false, Para))
{
if (reader.HasRows)
{
if (data.rows == null)
{
data.rows = new List <FlexiGridRow>();
}
while (reader.Read())
{
FlexiGridRow row = new FlexiGridRow {
cell = new List <string>()
};
for (int i = 0, l = reader.FieldCount; i < l; i++)
{
string v = reader.IsDBNull(i)?"":reader.GetValue(i).ToString();
if (pk == reader.GetName(i))
{
row.id = v;
}
row.cell.Add(v);
}
data.rows.Add(row);
}
}
}
data.total = view.RecordCount = Para[7].Value != null && Para[7].Value != DBNull.Value ? Convert.ToInt32(Para[7].Value) : -1;
return(data);
}