public void GetProductsTest()
{
IProductsDataDataAccess dataAccess = new ProductsDataDataAccess();
ProductDataQuery productsDataQueryInput = new ProductDataQuery();
productsDataQueryInput.PageNumber = 1;
productsDataQueryInput.PageSize = 8;
productsDataQueryInput.Filters.Add(new Filter {
ColumnName = "Description", FilterType = FilterType.Contains, Value = "Prod"
});
//productsDataQueryInput.Filters.Add(new Filter { ColumnName = "LastSold", FilterType = FilterType.GreatherEqualThan, Value = "1/3/2018" });
productsDataQueryInput.Filters.Add(new Filter {
ColumnName = "LastSold", FilterType = FilterType.GreatherEqualThan, Value = "1/3/2018"
});
//productsDataQueryInput.Filters.Add(new Filter { ColumnName = "ShelLife", FilterType = FilterType.Equal, Value = "1" });
//productsDataQueryInput.Filters.Add(new Filter { ColumnName = "DepartmentId", FilterType = FilterType.GreatherThan, Value = "1" });
//productsDataQueryInput.Filters.Add(new Filter { ColumnName = "Price", FilterType = FilterType.Between, Value = "5", Value2 ="7" });
//productsDataQueryInput.Filters.Add(new Filter { ColumnName = "UnitId", FilterType = FilterType.GreatherThan, Value = "1" });
//productsDataQueryInput.Filters.Add(new Filter { ColumnName = "xFor", FilterType = FilterType.GreatherThan, Value = "1" });
//productsDataQueryInput.Filters.Add(new Filter { ColumnName = "Cost", FilterType = FilterType.GreatherThan, Value = "1" });
ProductDataQuery productsDataQueryResult = dataAccess.GetProducts(productsDataQueryInput);
}
public ProductDataQuery GetProducts(ProductDataQuery dataQuery)
{
try
{
}
catch (System.Exception)
{
//TODO: Log to file for make easy debbuging
throw;
}
return(new ProductDataQuery());
}
public ProductDataQuery GetProducts(ProductDataQuery dataQuery)
{
try
{
//TODO: Add bussines rules return an error message
if (dataQuery != null)
{
dataQuery = _productDataAccess.GetProducts(dataQuery);
}
}
catch (System.Exception Ex)
{
//TODO: Log to file for make easy debbuging
throw;
}
return(dataQuery);
}
public ProductDataQuery GetProducts(ProductDataQuery dataQuery)
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
string filterString = dataQuery.GetFiltersAsString();
string queryTotalProducts = SqlConstants.PRODUCTS_SELECT_QUERY_COUNT;
if (!string.IsNullOrEmpty(filterString))
{
queryTotalProducts += SqlConstants.WHERE + filterString;
}
SqlCommand cmdTotalProducts = new SqlCommand(queryTotalProducts, conn);
cmdTotalProducts.Parameters.AddRange(dataQuery.GetFilterParameters());
conn.Open();
Int32 count = Convert.ToInt32(cmdTotalProducts.ExecuteScalar());
cmdTotalProducts.Dispose();
dataQuery.Total = count;
string queryProducts = SqlConstants.PRODUCTS_SELECT_QUERY_OPEN;
if (!string.IsNullOrEmpty(filterString))
{
queryProducts += SqlConstants.WHERE + filterString;
}
queryProducts += SqlConstants.PRODUCTS_SELECT_QUERY_CLOSE;
SqlCommand cmdProducts = new SqlCommand(queryProducts, conn);
cmdProducts.Parameters.Add(new SqlParameter(SqlConstants.ROW_START_PARAMETER_NAME, SqlDbType.Int));
cmdProducts.Parameters[SqlConstants.ROW_START_PARAMETER_NAME].Value = dataQuery.StartRow;
cmdProducts.Parameters.Add(new SqlParameter(SqlConstants.ROW_END_PARAMETER_NAME, SqlDbType.Int));
cmdProducts.Parameters[SqlConstants.ROW_END_PARAMETER_NAME].Value = dataQuery.EndRow;
cmdProducts.Parameters.AddRange(dataQuery.GetFilterParameters());
List <Product> productList = new List <Product>();
using (SqlDataReader reader = cmdProducts.ExecuteReader())
{
int idOrdinal = reader.GetOrdinal("Id");
int descriptionOrdinal = reader.GetOrdinal("Description");
int lastSoldOrdinal = reader.GetOrdinal("LastSold");
int shelfLifeOrdinal = reader.GetOrdinal("ShelfLife");
int departmentOrdinal = reader.GetOrdinal("Department");
int priceOrdinal = reader.GetOrdinal("Price");
int unitOrdinal = reader.GetOrdinal("Unit");
int xForOrdinal = reader.GetOrdinal("xFor");
int costOrdinal = reader.GetOrdinal("Cost");
while (reader.Read())
{
productList.Add(new Product()
{
Id = (int)reader.GetInt32(idOrdinal),
Description = !reader.IsDBNull(descriptionOrdinal) ? reader.GetString(descriptionOrdinal) : string.Empty,
LastSold = reader.GetDateTime(lastSoldOrdinal),
ShelfLife = !reader.IsDBNull(shelfLifeOrdinal) ? reader.GetInt64(shelfLifeOrdinal) : 0,
Department = !reader.IsDBNull(departmentOrdinal) ? reader.GetString(departmentOrdinal) : string.Empty,
Price = !reader.IsDBNull(priceOrdinal) ? reader.GetDouble(priceOrdinal) : 0,
Unit = !reader.IsDBNull(unitOrdinal) ? reader.GetString(unitOrdinal) : string.Empty,
XFor = !reader.IsDBNull(xForOrdinal) ? reader.GetInt32(xForOrdinal) : 0,
Cost = !reader.IsDBNull(costOrdinal) ? reader.GetDouble(costOrdinal) : 0
});
}
}
dataQuery.Result = productList;
}
}
catch (Exception Ex)
{
//TODO: Log to file for make easy debbuging
throw;
}
return(dataQuery);
}
// GET api/products
public ProductDataQuery Get([ModelBinder(typeof(ProductDataQueryModelBinder))] ProductDataQuery dataQuery)
{
return(_productManager.GetProducts(dataQuery));
}
