public bool Insert(Customer customer)
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
string query = @"SELECT count(*) FROM Customer c WHERE c.Id='" + customer.Id + "'";
var command = new SqlCommand(query, connection);
int rows = (int)command.ExecuteScalar();
if (rows == 1)
{
string discount = customer.Discount?.ToString();
if (discount == null)
{
discount = "NULL";
}
string updateQuery =
string.Format(
"UPDATE Customer SET Name='{0}',Email='{1}', Address='{2}', Discount={3} WHERE Id={4}",
customer.Name,
customer.Email,
customer.Email,
discount,
customer.Id);
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.ExecuteNonQuery();
return true;
}
else if (rows == 0)
{
string discount = customer.Discount?.ToString();
if (discount == null)
{
discount = "NULL";
}
string insertQuery = string.Format(
"INSERT INTO Customer VALUES('{0}','{1}','{2}',{3})",
customer.Name,
customer.Email,
customer.Address,
discount);
var insertCommand = new SqlCommand(insertQuery, connection);
insertCommand.ExecuteNonQuery();
customer.Id = this.GetLastId("Customer");
}
}
return true;
}
public Customer GetCustomerByID(int id)
{
Customer cust = new Customer();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
string query = @"SELECT*
FORM CUSTOMER
WHERE CUSTOMERID=" + id;
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
cust.Name = (string)reader["NAME"];
cust.Email = (string)reader["EMAIL"];
cust.Address = (string)reader["ADRESS"];
cust.Discount = (double)reader["DICOUNT"];
cust.CustomerID = (int)reader["CUSTOMERID"];
}
}
return cust;
}
public void CreateOrUpdateCustomer(Customer customer)
{
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = @"SELECT COUNT(*)
FROM CUSTOMER
WHERE CUSTOMERID=" + customer.CustomerID;
var command = new SqlCommand(query, connection);
int counter = (int)command.ExecuteScalar();
if (counter == 1)
{
string updateQuery = @"UPDATE CUSTOMER
SET NAME=@customerName,EMAIL=@customerEmail,ADRESS=@customerAdress,DICOUNT=@customerDiscount,CUSTOMERID=customerCustomerID
WHERE CUSTOMERID=" + customer.CustomerID;
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.Parameters.AddWithValue("@customerName", customer.Name);
updateCommand.Parameters.AddWithValue("@customerEmail", customer.Email);
updateCommand.Parameters.AddWithValue("@customerAdress", customer.Address);
updateCommand.Parameters.AddWithValue("@customerDiscount", customer.Discount);
updateCommand.Parameters.AddWithValue("@customerCustomerID", customer.CustomerID);
updateCommand.ExecuteNonQuery();
}
else
{
string insertQuery = @"INSERT INTO CUSTOMER(NAME,EMAIL,ADRESS,DICOUNT,CUSTOMERID)
VALUES (@customerName,@customerEmail,@customerAdress,@customerDiscount,@customerCustomerID)";
var addCommand = new SqlCommand(insertQuery, connection);
addCommand.Parameters.AddWithValue("@customerName", customer.Name);
addCommand.Parameters.AddWithValue("@customerEmail", customer.Email);
addCommand.Parameters.AddWithValue("@customerAdress", customer.Address);
addCommand.Parameters.AddWithValue("@customerDiscount", customer.Discount);
addCommand.Parameters.AddWithValue("@customerCustomerID", customer.CustomerID);
addCommand.ExecuteNonQuery();
}
}
}
public List<Customer> ListCustomers()
{
List<Customer> customers = new List<Customer>();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = @"SELECT*
FROM CUSTOMER";
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
Customer temp = new Customer();
temp.Name = (string)reader["NAME"];
temp.Email = (string)reader["EMAIL"];
temp.Address = (string)reader["ADRESS"];
temp.Discount = (double)reader["DICOUNT"];
temp.CustomerID = (int)reader["CUSTOMERID"];
customers.Add(temp);
}
}
return customers;
}
public void UpdateOrInsert(Customer customer)
{
string query = $@"IF EXISTS (SELECT TOP 1 *
FROM Customers
WHERE CustomerID = '{customer.CustomerId}')
BEGIN
UPDATE Customers
SET Name = '{customer.Name}'
SET Email = '{customer.Email}'
SET Address = '{customer.Address}'
SET Discount = '{customer.Discount}'
WHERE CustomerID = '{customer.CustomerId}'
END
ELSE
BEGIN
SET IDENTITY_INSERT Customers ON
INSERT INTO Customers
VALUES ('{customer.CustomerId}', '{customer.Name}', '{customer.Email}', '{customer.Address}', '{customer.Discount}')
SET IDENTITY_INSERT Customers OFF
END";
ExecuteNonQuery(query);
}
