/// <summary>
/// Fetches all Products
/// </summary>
public List <Product> GetProducts()
{
List <Product> result = new List <Product>();
try
{
using (SqlConnection conn = new SqlConnection(_builder.ConnectionString))
{
conn.Open();
string query = "Select * from [dbo].TProduct";
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var product = (Product)ModelFactory.Build("product");
product.cname = reader["cName"].ToString();
product.cdescription = reader["cdescription"].ToString();
product.nStock = int.Parse(reader["nStock"].ToString());
product.nProductId = int.Parse(reader["nProductId"].ToString());
product.nUnitPrice = decimal.Parse(reader["nUnitPrice"].ToString());
result.Add(product);
}
}
}
catch (SqlException e)
{
System.Console.WriteLine(e);
}
return(result);
}
/// <summary>
/// Fetches a list of Creditcards
/// based on a UserId
/// </summary>
public List <CreditCard> GetCreditCardsForUser(int UserId)
{
List <CreditCard> cards = new List <CreditCard>();
try
{
using (SqlConnection conn = new SqlConnection(_builder.ConnectionString))
{
conn.Open();
string query = "Select * from [dbo].TCreditCard Where nUserId = @UserId";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@UserId", UserId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var card = (CreditCard)ModelFactory.Build("creditcard");
card.nCreditCardId = int.Parse(reader["nCreditCardId"].ToString());
card.nIBANCode = Int64.Parse(reader["nIBANCode"].ToString());
card.dExpDate = reader["dExpDate"].ToString();
cards.Add(card);
}
}
}
catch (SqlException e)
{
System.Console.WriteLine(e);
}
return(cards);
}
/// <summary>
/// Fetches Products based on an search string
/// </summary>
public List <Product> SearchProducts(string in_request)
{
List <Product> result = new List <Product>();
try
{
using (SqlConnection conn = new SqlConnection(_builder.ConnectionString))
{
conn.Open();
string query = "Select * from [dbo].TProduct WHERE TProduct.cName LIKE @request Or TProduct.cDescription LIKE @request";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@request", "%" + in_request + "%");
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Product product = (Product)ModelFactory.Build("product") as Product;
product.cname = reader["cName"].ToString();
product.cdescription = reader["cdescription"].ToString();
product.nStock = int.Parse(reader["nStock"].ToString());
product.nProductId = int.Parse(reader["nProductId"].ToString());
product.nUnitPrice = decimal.Parse(reader["nUnitPrice"].ToString());
result.Add(product);
}
}
}
catch (SqlException e)
{
System.Console.WriteLine(e);
}
return(result);
}
/// <summary>
/// Fetches a list of Products
/// based on the Min Price and Max Price
/// </summary>
public List <Product> SearchProductsOnPrice(int in_min, int in_max)
{
if (in_max == 0)
{
in_max = 1000;
}
List <Product> result = new List <Product>();
try
{
using (SqlConnection conn = new SqlConnection(_builder.ConnectionString))
{
conn.Open();
string query = "Select * from [dbo].TProduct WHERE TProduct.nUnitPrice >= @min AND TProduct.nUnitPrice <= @max Order by nUnitPrice";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.Parameters.AddWithValue("@min", in_min);
cmd.Parameters.AddWithValue("@max", in_max);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var product = (Product)ModelFactory.Build("product");
product.cname = reader["cName"].ToString();
product.cdescription = reader["cdescription"].ToString();
product.nStock = int.Parse(reader["nStock"].ToString());
product.nProductId = int.Parse(reader["nProductId"].ToString());
product.nUnitPrice = decimal.Parse(reader["nUnitPrice"].ToString());
result.Add(product);
}
}
}
catch (SqlException e)
{
System.Console.WriteLine(e);
}
return(result);
}
