public class Db
{
private static SqlConnection connection;
public static void Init()
{
string connectionString =
"Initial Catalog=QuickyMart;" +
"Data Source=marvin;" +
"user id=sa;password=abc;";
connection = new SqlConnection(connectionString);
connection.Open();
}
public static void Store(ProductData pd)
{
SqlCommand command = BuildInsertionCommand(pd);
command.ExecuteNonQuery();
}
private static SqlCommand
BuildInsertionCommand(ProductData pd)
{
string sql =
"INSERT INTO Products VALUES (@sku, @name, @price)";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@sku", pd.sku);
command.Parameters.Add("@name", pd.name);
command.Parameters.Add("@price", pd.price);
return command;
}
public static ProductData GetProductData(string sku)
{
SqlCommand command = BuildProductQueryCommand(sku);
IDataReader reader = ExecuteQueryStatement(command);
ProductData pd = ExtractProductDataFromReader(reader);
reader.Close();
return pd;
}
private static
SqlCommand BuildProductQueryCommand(string sku)
{
string sql = "SELECT * FROM Products WHERE sku = @sku";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@sku", sku);
return command;
}
private static ProductData
ExtractProductDataFromReader(IDataReader reader)
{
ProductData pd = new ProductData();
pd.Sku = reader["sku"].ToString();
pd.Name = reader["name"].ToString();
pd.Price = Convert.ToInt32(reader["price"]);
return pd;
}
public static void DeleteProductData(string sku)
{
BuildProductDeleteStatement(sku).ExecuteNonQuery();
}
private static SqlCommand
BuildProductDeleteStatement(string sku)
{
string sql = "DELETE from Products WHERE sku = @sku";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@sku", sku);
return command;
}
private static IDataReader
ExecuteQueryStatement(SqlCommand command)
{
IDataReader reader = command.ExecuteReader();
reader.Read();
return reader;
}
public static void Close()
{
connection.Close();
}
}

public class DbOrderGateway : OrderGateway
{
private readonly ProductGateway productGateway;
private readonly SqlConnection connection;
public DbOrderGateway(SqlConnection connection,
ProductGateway productGateway)
{
this.connection = connection;
this.productGateway = productGateway;
}
public void Insert(Order order)
{
string sql = "insert into Orders (cusId) values (@cusId)" +
"; select scope_identity()";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@cusId", order.CustomerId);
int id = Convert.ToInt32(command.ExecuteScalar());
order.Id = id;
InsertItems(order);
}
public Order Find(int id)
{
string sql = "select * from Orders where orderId = @id";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@id", id);
IDataReader reader = command.ExecuteReader();
Order order = null;
if(reader.Read())
{
string customerId = reader["cusId"].ToString();
order = new Order(customerId);
order.Id = id;
}
reader.Close();
if(order != null)
LoadItems(order);
return order;
}
private void LoadItems(Order order)
{
string sql =
"select * from Items where orderId = @orderId";
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@orderId", order.Id);
IDataReader reader = command.ExecuteReader();
while(reader.Read())
{
string sku = reader["sku"].ToString();
int quantity = Convert.ToInt32(reader["quantity"]);
Product product = productGateway.Find(sku);
order.AddItem(product, quantity);
}
}
private void InsertItems(Order order)
{
string sql = "insert into Items (orderId, quantity, sku)" +
"values (@orderId, @quantity, @sku)";
foreach(Item item in order.Items)
{
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.Add("@orderId", order.Id);
command.Parameters.Add("@quantity", item.Quantity);
command.Parameters.Add("@sku", item.Product.Sku);
command.ExecuteNonQuery();
}
}
}