public void CreateOrderTest()
{
using (DbConnection connection = this.providerFactory.CreateConnection())
{
CreateOrderFields newOrder = new CreateOrderFields();
var rand = new Random();
newOrder.Discount = 0.1;
newOrder.Quantity = rand.Next(1, 25);
connection.ConnectionString = this.connectionString;
connection.Open();
var insertCommand = (SqlCommand)connection.CreateCommand();
insertCommand.CommandText = "SELECT Count(*) FROM [dbo].[Orders]";
var ordersCount = (int)insertCommand.ExecuteScalar();
insertCommand.CommandText = "SELECT Count(*) FROM [dbo].[Order Details]";
var ordersDetCount = (int)insertCommand.ExecuteScalar();
var insertData = (SqlCommand)connection.CreateCommand();
insertData.CommandText = "SELECT TOP 1 CustomerID FROM [dbo].[Orders]";
string custId = (string)insertData.ExecuteScalar();
insertData.CommandText = "SELECT TOP 1 ProductID FROM [dbo].[Products]";
int prodId = (int)insertData.ExecuteScalar();
newOrder.CustomerId = custId;
newOrder.ProductID = prodId;
this.repository.CrateOrder(newOrder);
insertData.CommandText = "SELECT Count(*) FROM [dbo].[Orders]";
var ordersCountRes = (int)insertData.ExecuteScalar();
insertData.CommandText = "SELECT Count(*) FROM [dbo].[Order Details]";
var ordersDetCountRes = (int)insertData.ExecuteScalar();
Assert.AreEqual(ordersCount + 1, ordersCountRes);
Assert.AreEqual(ordersDetCount + 1, ordersDetCountRes);
var id = (SqlCommand)connection.CreateCommand();
id.CommandText = "SELECT IDENT_CURRENT('dbo.Orders')";
var deleteCommand = (SqlCommand)connection.CreateCommand();
deleteCommand.CommandText = "DELETE FROM [Order Details] WHERE OrderID = @id " +
"DELETE FROM Orders WHERE OrderID = @id";
deleteCommand.Parameters.AddWithValue("@id", Convert.ToInt32(id.ExecuteScalar()));
deleteCommand.ExecuteNonQuery();
var ordDetRecordsCount = (int)insertData.ExecuteScalar();
insertData.CommandText = "SELECT Count(*) FROM [dbo].[Orders]";
var ordRecordsCount = (int)insertData.ExecuteScalar();
Assert.AreEqual(ordDetRecordsCount, ordersDetCount);
Assert.AreEqual(ordRecordsCount, ordersCount);
}
}
/// <summary>
/// Create new records in datatables Orders, Order Details for EmployeeID with the cheapest UnitPrice for ProductID
/// </summary>
/// <param name="newOrder"></param>
public bool CrateOrder(CreateOrderFields newOrder)
{
try
{
using (DbConnection connection = this.providerFactory.CreateConnection())
{
connection.ConnectionString = this.connectionString;
connection.Open();
var command = (SqlCommand)connection.CreateCommand();
command.Parameters.AddWithValue("@custId", newOrder.CustomerId);
command.Parameters.AddWithValue("@productId", newOrder.ProductID);
command.Parameters.AddWithValue("@quantity", newOrder.Quantity);
command.Parameters.AddWithValue("@discount", newOrder.Discount);
command.CommandText = "INSERT INTO [dbo].[Orders]([CustomerID], [EmployeeID], [ShipName], [ShipAddress],[ShipCity], [ShipRegion], [ShipPostalCode], [ShipCountry]) " +
"(SELECT @custId, (SELECT TOP 1 SupplierID FROM[dbo].[Products] " +
"WHERE ProductId = @productId ORDER BY UnitPrice), " +
"custs.CompanyName, custs.Address, custs.City, custs.Region, custs.PostalCode, custs.Country FROM[dbo].[Customers] AS custs " +
"WHERE custs.CustomerID = @custId) " +
"INSERT INTO dbo.[Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) " +
"VALUES(IDENT_CURRENT('dbo.Orders'), @productId, (SELECT UnitPrice FROM[dbo].[Products] " +
"WHERE ProductID = @productId), @quantity, @discount)";
var res = command.ExecuteNonQuery();
if (res == 1)
{
command.Parameters.Clear();
command.CommandText = "SELECT IDENT_CURRENT('dbo.Orders') FROM dbo.[Orders]";
var id = (int)command.ExecuteScalar();
this.SetOrderDate(id, DateTime.Now);
return(true);
}
else
{
return(false);
}
}
}
catch (SqlException exc)
{
// ...
return(false);
}
catch (InvalidCastException exc)
{
// ...
return(false);
}
catch (Exception exc)
{
this.UnresolvedExceptions.Add(exc);
return(false);
}
}
