public void AddProductToProducts(Product productToAdd)
{
// connect to the database
SqlConnection dbCon = new SqlConnection(dbConnectionEXPRESS);
// Open the database
dbCon.Open();
string sqlQuery = "INSERT INTO Products(ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) " +
"VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)";
using (dbCon)
{
SqlCommand insertProduct = new SqlCommand(sqlQuery, dbCon);
// set the parameters' values
insertProduct.Parameters.AddWithValue("@ProductName", productToAdd.productName);
insertProduct.Parameters.AddWithValue("@SupplierID", productToAdd.supplierID);
insertProduct.Parameters.AddWithValue("@CategoryID", productToAdd.categoryID);
insertProduct.Parameters.AddWithValue("@QuantityPerUnit", productToAdd.quantityPerUnit);
insertProduct.Parameters.AddWithValue("@UnitPrice", productToAdd.unitPrice);
insertProduct.Parameters.AddWithValue("@UnitsInStock", productToAdd.unitsInStock);
insertProduct.Parameters.AddWithValue("@UnitsOnOrder", productToAdd.unitsOnOrder);
insertProduct.Parameters.AddWithValue("@ReorderLevel", productToAdd.reorderLevel);
insertProduct.Parameters.AddWithValue("@Discontinued", productToAdd.Discontinued);
// execute the command
insertProduct.ExecuteNonQuery();
}
}
static void Main()
{
// Make an object to use the class for the first 5 tasks
NorthWindTasks taskExecutorNW = new NorthWindTasks();
// task 01 - retrieve the number of rows in the categories table Northwind DB
int rowsInCategories = taskExecutorNW.GetNumberOfRowsInCategories();
Console.WriteLine("The number of rows in the categories table is: {0}", rowsInCategories);
Console.WriteLine("--------------------------------------------------------------------------------");
// task 02 - get the name and description of all categories
var nameAndDescription = new Dictionary<string, string>();
taskExecutorNW.GetNameAndDescriptionOfCategories(nameAndDescription);
Console.WriteLine("\t\tName - Description\n");
Print(nameAndDescription);
Console.WriteLine("--------------------------------------------------------------------------------");
// task 03 - get the categories and the products in them
taskExecutorNW.GetNamesOfProductsAndProductsCategories();
Console.WriteLine("--------------------------------------------------------------------------------");
// task 04 - add a new product to the products table
Product milkaChocolate = new Product("Chocolate", 8, 2, "100g in package", 2.00M, 25, 10, 5, false);
taskExecutorNW.AddProductToProducts(milkaChocolate);
Console.WriteLine("The product is added!");
Console.WriteLine("--------------------------------------------------------------------------------");
// task 05 - retrieve the images for all categories and store them as jpg files on the file system
taskExecutorNW.GetImages();
Console.WriteLine("Images Are Saved!");
Console.WriteLine("--------------------------------------------------------------------------------");
// task 06 - read the excel file row by row through OLE DB
ExcelTasks taskExecutorExcel = new ExcelTasks();
taskExecutorExcel.ReadFileAndPrintItRowByRow();
Console.WriteLine("--------------------------------------------------------------------------------");
//task 07 - insert data to the excel file
taskExecutorExcel.InsertNewRowsToFile();
Console.WriteLine("The new data is inserted!");
Console.WriteLine("--------------------------------------------------------------------------------");
// task 08 - search product by pattern
taskExecutorNW.SearchProductsByPattern();
Console.WriteLine("--------------------------------------------------------------------------------");
}