/// <summary>
/// Getting all products
/// </summary>
/// <returns></returns>
public List <ProductDAL> GetAllProducts()
{
var products = new List <ProductDAL>();
ProductDAL product;
using (var connection = new SqlConnection())
{
connection.ConnectionString = this.connectionString;
connection.Open();
var cmd = new SqlCommand("select * from Product ", connection);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
product = new ProductDAL
{
ID = (int)reader["ID"],
Name = (string)reader["Name"],
Price = (double)reader["Price"],
};
products.Add(product);
}
}
}
return(products);
}
/// <summary>
/// Inserting product
/// </summary>
/// <param name="product">Some product instance </param>
public void InsertProduct(ProductDAL product)
{
using (var connection = new SqlConnection())
{
connection.ConnectionString = this.connectionString;
connection.Open();
// prepare command string
var insertString = @"
insert into Product
(Name,Price)
values (@Name,@Price)";
// 1. Instantiate a new command with a query and connection
var cmd = new SqlCommand(insertString, connection);
cmd.Parameters.AddWithValue("@Name", product.Name);
cmd.Parameters.AddWithValue("@Price", product.Price);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Getting product by ID
/// </summary>
/// <param name="ID"></param>
/// <returns></returns>
public ProductDAL GetProductByID(int ID)
{
var product = new ProductDAL();
using (var connection = new SqlConnection())
{
connection.ConnectionString = this.connectionString;
connection.Open();
var cmd = new SqlCommand("select * from Product where ID=@ID", connection);
cmd.Parameters.AddWithValue("@ID", ID);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
product.ID = (int)reader["ID"];
product.Name = (string)reader["Name"];
product.Price = (double)reader["Price"];
}
}
return(product);
}
}
