public static DataTable ExecuteDataTable(string sprocName, params object[] paramList)
{
DbCommand command = null;
if (paramList.GetUpperBound(0) == -1)
{
command = _database.GetStoredProcCommand(sprocName);
}
else
{
command = _database.GetStoredProcCommand(sprocName, paramList);
}
return(DatabaseUtility.ExecuteDataSet(_database, command).Tables[0]);
}
public static List <Product> GetProducts()
{
var list = new List <Product>();
String commandText = @"SELECT * FROM test_Products ORDER BY ProductId DESC";
var parameterList = new List <SqlParameter>();
string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
var commandType = CommandType.Text;
DataSet dataSet = DatabaseUtility.ExecuteDataSet(new SqlConnection(connectionString), commandText, commandType, parameterList.ToArray());
if (dataSet.Tables.Count > 0)
{
using (DataTable dt = dataSet.Tables[0])
{
foreach (DataRow dr in dt.Rows)
{
var e = GetProductFromDataRow(dr);
list.Add(e);
}
}
}
return(list);
}
public static Product GetProduct(int productId)
{
string connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
String commandText = @"SELECT * FROM test_Products WHERE productId=@productId";
var parameterList = new List <SqlParameter>();
var commandType = CommandType.Text;
parameterList.Add(DatabaseUtility.GetSqlParameter("productId", productId, SqlDbType.Int));
DataSet dataSet = DatabaseUtility.ExecuteDataSet(new SqlConnection(connectionString), commandText, commandType, parameterList.ToArray());
if (dataSet.Tables.Count > 0)
{
using (DataTable dt = dataSet.Tables[0])
{
foreach (DataRow dr in dt.Rows)
{
var e = GetProductFromDataRow(dr);
return(e);
}
}
}
return(null);
}
public static DataTable ExecuteDataTable(TransactionManager transaction, DbCommand command)
{
return(DatabaseUtility.ExecuteDataSet(transaction, command).Tables[0]);
}
public static DataTable ExecuteDataTable(DbCommand command)
{
return(DatabaseUtility.ExecuteDataSet(_database, command).Tables[0]);
}
private ProductsSearchResult GetProductsSearchResult(
int storeId,
string search,
List <Filter> filters,
int top,
int skip,
Boolean isAdmin = false, string categoryApiId = "women")
{
var searchResult = new ProductsSearchResult();
String commandText = @"SearchProducts";
var commandType = CommandType.StoredProcedure;
var parameterList = new List <SqlParameter>();
var dtFilters = new DataTable("med_tpt_Filter");
dtFilters.Columns.Add("FieldName");
dtFilters.Columns.Add("ValueFirst");
dtFilters.Columns.Add("ValueLast");
if (filters != null && filters.Any())
{
foreach (var filter in filters)
{
DataRow dr = dtFilters.NewRow();
dr["FieldName"] = filter.FieldName;
dr["ValueFirst"] = filter.ValueFirst;
dr["ValueLast"] = filter.ValueLast;
dtFilters.Rows.Add(dr);
}
}
parameterList.Add(DatabaseUtility.GetSqlParameter("IsAdmin", isAdmin, SqlDbType.Bit));
parameterList.Add(DatabaseUtility.GetSqlParameter("storeId", storeId, SqlDbType.Int));
parameterList.Add(DatabaseUtility.GetSqlParameter("search", search.ToStr(), SqlDbType.NVarChar));
parameterList.Add(DatabaseUtility.GetSqlParameter("ApiCategoryId", categoryApiId.ToStr(), SqlDbType.NVarChar));
parameterList.Add(DatabaseUtility.GetSqlParameter("filter", dtFilters, SqlDbType.Structured));
parameterList.Add(DatabaseUtility.GetSqlParameter("top", top, SqlDbType.Int));
parameterList.Add(DatabaseUtility.GetSqlParameter("skip", skip, SqlDbType.Int));
DatabaseUtility.SqlCommandTimeout = StoreConstants.StoreProcedureCommandTimeOut;
DataSet dataSet = DatabaseUtility.ExecuteDataSet((SqlConnection)StoreDbContext.Database.Connection, commandText, commandType, parameterList.ToArray());
if (dataSet.Tables.Count > 0)
{
var productCategories = new List <ProductCategory>();
using (DataTable dt = dataSet.Tables[0])
{
foreach (DataRow dr in dt.Rows)
{
var item = GetProductCategoriesFromDataRow(dr);
productCategories.Add(item);
}
;
}
searchResult.ProductCategories = productCategories;
var fileManagerList = new List <FileManager>();
using (DataTable dt = dataSet.Tables[3])
{
foreach (DataRow dr in dt.Rows)
{
var item = GetFileManagerFromDataRow(dr);
fileManagerList.Add(item);
}
;
}
var productFiles = new List <ProductFile>();
using (DataTable dt = dataSet.Tables[2])
{
foreach (DataRow dr in dt.Rows)
{
var item = GetProductFilesFromDataRow(dr);
productFiles.Add(item);
item.FileManager = fileManagerList.FirstOrDefault(r => r.Id == item.FileManagerId);
}
;
}
var products = new List <Product>();
using (DataTable dt = dataSet.Tables[1])
{
foreach (DataRow dr in dt.Rows)
{
var item = GetProductsFromDataRow(dr);
products.Add(item);
item.ProductFiles = productFiles.Where(r => r.ProductId == item.Id).ToList();
}
;
}
searchResult.Products = products;
using (DataTable dt = dataSet.Tables[4])
{
var m = new List <Filter>();
foreach (DataRow dr in dt.Rows)
{
var item = GetFilterFromDataRow(dr);
item.OwnerType = ProductsItem;
m.Add(item);
}
searchResult.Filters = m;
}
using (DataTable dt = dataSet.Tables[5])
{
var stats = new RecordsStats();
foreach (DataRow dr in dt.Rows)
{
stats = GetRecordsStatsFromDataRow(dr);
stats.OwnerType = ProductsItem;
}
searchResult.Stats = stats;
}
}
searchResult.PageSize = top;
return(searchResult);
}