/// <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); } }