private int InsertProduct(Product product)
{
SqlCommand command = new SqlCommand("INSERT INTO Products " +
"(ProductName, SupplierId, CategoryId, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued) " +
"VALUES (@ProductName, @SupplierId, @CategoryId, @QuantityPerUnit, @UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)", connection);
command.Parameters.AddWithValue("@ProductName", product.ProductName);
command.Parameters.AddWithValue("@QuantityPerUnit", product.QuantityPerUnit);
command.Parameters.AddWithValue("@Discontinued", product.Discontinued);
SqlParameter supplierId = new SqlParameter("@SupplierId", product.SupplierId);
if (product.SupplierId == null)
{
supplierId.Value = DBNull.Value;
}
command.Parameters.Add(supplierId);
SqlParameter categoryId = new SqlParameter("@CategoryId", product.CategoryId);
if (product.CategoryId == null)
{
categoryId.Value = DBNull.Value;
}
command.Parameters.Add(categoryId);
SqlParameter unitPrice = new SqlParameter("@UnitPrice", product.UnitPrice);
if (product.UnitPrice == null)
{
unitPrice.Value = DBNull.Value;
}
command.Parameters.Add(unitPrice);
SqlParameter unitsInStock = new SqlParameter("@UnitsInStock", product.UnitsInStock);
if (product.UnitsInStock == null)
{
unitsInStock.Value = DBNull.Value;
}
command.Parameters.Add(unitsInStock);
SqlParameter unitsOnOrder = new SqlParameter("@UnitsOnOrder", product.UnitsOnOrder);
if (product.UnitsOnOrder == null)
{
unitsOnOrder.Value = DBNull.Value;
}
command.Parameters.Add(unitsOnOrder);
SqlParameter reorderLevel = new SqlParameter("@ReorderLevel", product.ReorderLevel);
if (product.ReorderLevel == null)
{
reorderLevel.Value = DBNull.Value;
}
command.Parameters.Add(reorderLevel);
command.ExecuteNonQuery();
SqlCommand selectIdentity = new SqlCommand("SELECT @@Identity", connection);
int insertedProductId = (int)(decimal)selectIdentity.ExecuteScalar();
return insertedProductId;
}
static void Main(string[] args)
{
SqlQuery query = new SqlQuery();
try
{
query.ConnectToDatabase(Settings.Default.DBConnectionString);
Product product = new Product();
product.ProductName = "PeshoFood";
product.QuantityPerUnit = "12 - 12 oz jars";
product.Discontinued = true;
product.SupplierId = 2;
product.CategoryId = 1;
int insertedProductId = query.InsertProduct(product);
Console.WriteLine("Inserted new Product with id: {0}", insertedProductId);
}
finally
{
query.DisconnectFromDatabase();
}
}