public DataTable SearchItemById(Item item)
{
string commandString = @"SELECT * FROM Items WHERE ID = '" + item.ID + "'";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
DataTable dataTable = connectionClass.ExcecuteDataAdapter(commandString);
sqlConnection.Close();
return(dataTable);
}
public DataTable Display()
{
string commandString = @"SELECT * FROM Items";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
DataTable dataTable = connectionClass.ExcecuteDataAdapter(commandString);
sqlConnection.Close();
return(dataTable);
}
public bool UpdateItem(Item item)
{
string commandString = @"UPDATE Items SET Name = '" + item.Name + "', Price = " + item.Price + " WHERE ID = " + item.ID + "";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
bool isUpdated = connectionClass.ExecuteQueries(commandString);
sqlConnection.Close();
return(isUpdated);
}
public bool AddItem(Item item)
{
string commandString = @"INSERT INTO Items (Name, Price) VALUES('" + item.Name + "'," + item.Price + ")";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
bool isAdded = connectionClass.ExecuteQueries(commandString);
sqlConnection.Close();
return(isAdded);
}
public DataTable SearchOrderById(Order order)
{
string commandString = @"SELECT o.ID, c.Name AS Customer, i.Name AS Item, Quantity, TotalPrice FROM Orders AS o LEFT JOIN Customers AS c ON c.ID = o.CustomerID LEFT JOIN Items AS i ON i.ID = o.ItemID WHERE CustomerId = '" + order.CustomerId + "'";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
DataTable dataTable = connectionClass.ExcecuteDataAdapter(commandString);
sqlConnection.Close();
return(dataTable);
}
public bool DeleteItem(Item item)
{
string commandString = @"DELETE FROM Items WHERE ID = " + item.ID + "";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
bool isDeleted = connectionClass.ExecuteQueries(commandString);
sqlConnection.Close();
return(isDeleted);
}
public bool AddOrder(Order order)
{
string commandString = @"INSERT INTO Orders (CustomerId, ItemId, Quantity, TotalPrice) VALUES(" + order.CustomerId + "," + order.ItemId + "," + order.Quantity + ", " + order.TotalPrice + ")";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
bool isAdded = connectionClass.ExecuteQueries(commandString);
sqlConnection.Close();
return(isAdded);
}
public DataTable SearchOrderByName(Order order)
{
string commandString = @"SELECT * FROM Orders WHERE Name = '" + order.CustomerId + "'";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
DataTable dataTable = connectionClass.ExcecuteDataAdapter(commandString);
sqlConnection.Close();
return(dataTable);
}
public DataTable CustomerCombo()
{
string commandString = @"SELECT ID, Name FROM Customers";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
DataTable dataTable = connectionClass.ExcecuteDataAdapter(commandString);
sqlConnection.Close();
return(dataTable);
}
public bool UpdateOrder(Order order)
{
string commandString = @"UPDATE Orders SET CustomerID = " + order.CustomerId + ", ItemID = " + order.ItemId + ", Quantity = " + order.Quantity + ", TotalPrice = " + order.TotalPrice + " WHERE ID = " + order.ID + "";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
bool isUpdated = connectionClass.ExecuteQueries(commandString);
sqlConnection.Close();
return(isUpdated);
}
public DataTable SearchCustomerById(Customer customer)
{
string commandString = @"SELECT * FROM Customers WHERE ID = '" + customer.ID + "'";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
DataTable dataTable = connectionClass.ExcecuteDataAdapter(commandString);
sqlConnection.Close();
return(dataTable);
}
public bool AddCustomer(Customer customer)
{
string commandString = @"INSERT INTO Customers (Name, Contact, Address) VALUES('" + customer.Name + "','" + customer.Contact + "','" + customer.Address + "')";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
bool isAdded = connectionClass.ExecuteQueries(commandString);
sqlConnection.Close();
return(isAdded);
}
public bool UpdateCustomer(int id, string name, string contact, string address)
{
string commandString = @"UPDATE Customers SET Name = '" + name + "', Contact = '" + contact + "', address = '" + address + "' WHERE ID = " + id + "";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
bool isUpdated = connectionClass.ExecuteQueries(commandString);
sqlConnection.Close();
return(isUpdated);
}
public bool DeleteCustomer(int id)
{
string commandString = @"DELETE FROM Customers WHERE ID = " + id + "";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
bool isDeleted = connectionClass.ExecuteQueries(commandString);
sqlConnection.Close();
return(isDeleted);
}
public bool IsItemExist(Item item)
{
string commandString = @"SELECT * FROM Items WHERE Name='" + item.Name + "'";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
DataTable result = connectionClass.ExcecuteDataAdapter(commandString);
sqlConnection.Close();
if (result.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
public bool IsOrderExist(Order order)
{
string commandString = @"SELECT * FROM Orders WHERE ID='" + order.ID + "'";
SqlConnection sqlConnection = null;
ConnectionClass connectionClass = new ConnectionClass();
sqlConnection = connectionClass.CreateConnection();
sqlConnection.Open();
DataTable result = connectionClass.ExcecuteDataAdapter(commandString);
sqlConnection.Close();
if (result.Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}