static void Main ( string[] args )
{
DataBaseController controller = new DataBaseController();
Orders o = new Orders(16 , new DateTime(2015 , 01 , 29) , 18 , 7);
controller.AddOrder(o);
Console.ReadKey();
}
public void AddOrder ( Orders o )
{
using(connection = new SqlConnection(ConfigurationManager.ConnectionStrings["HackCompany"].ConnectionString))
{
connection.Open();
string findIDQuery =
@"SELECT *
FROM [Orders]
WHERE Orders.OrderID = " + o.OrderID;
var commandFind = new SqlCommand(findIDQuery , connection);
string affectedRows = commandFind.ExecuteScalar().ToString();
string query = null;
if(affectedRows !=null)
{
query = string.Format(
@"UPDATE Orders
SET OrderID = {0},
DateOfOrder = {1},
TotalPrice = {2},
CustomerID = {3}
WHERE OrderID = {4}" , o.OrderID , o.DateOfOrder.ToString("yyyy-MM-dd") , o.TotalPrice , o.CustomerID , o.OrderID);
var commandUpdate = new SqlCommand(query , connection);
Console.WriteLine("Updated {0}" , commandUpdate.ExecuteNonQuery());
}
else
{
query = string.Format(
@"INSERT INTO Orders
VALUES ({0},{1},{2},{3})" , o.OrderID , o.DateOfOrder.ToString("yyyy-MM-dd") , o.TotalPrice , o.CustomerID , o.OrderID);
var commandAdd = new SqlCommand(query , connection);
Console.WriteLine("Added {0}" , commandAdd.ExecuteNonQuery());
}
}
}
public static List<Orders> GetOrders()
{
List<Orders> list = new List<Orders>();
string connectionString = ConfigurationManager.ConnectionStrings["Week08Day03"].ConnectionString;
string query = @"SELECT [ID],
[DateAndTimeOfOrder],
[TotalPrice],
[CustomerID]
FROM [Orders]";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Orders temp = new Orders();
temp.ID = GetIntOrZero(reader["ID"]);
temp.TotalPrice = GetIntOrZero(reader["Name"]);
temp.CustomerID = GetIntOrZero(reader["Email"]);
temp.DateAndTimeOfOrder = GetDateOrNull(reader["DateAndTimeOfOrder"]);
list.Add(temp);
}
}
}
return list;
}