public void AddCustomerToDb(Customer customer)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string countCommand = "SELECT COUNT(*) FROM Customer WHERE id=" + customer.Id;
SqlCommand sqlCountCommand = new SqlCommand(countCommand, connection);
var count = (int)sqlCountCommand.ExecuteScalar();
if (count >= 1)
{
string updateCommand = "UDAPTE Customer SET Name=@name,Email=@email,Adress=@adress,Discount=@discount WHERE id=" + customer.Id;
AddOrEditCustomer(updateCommand, customer, connection);
}
else
{
string addCommand = "INSERT INTO Customer (Name,Email,Adress,Discount) VALUES (@name,@email,@adress,@discount)";
AddOrEditCustomer(addCommand, customer, connection);
SqlCommand getLastIdCmd = new SqlCommand("SELECT @@IDENTITY", connection);
int insertedRecordId = (int)(decimal)getLastIdCmd.ExecuteScalar();
customer.Id = insertedRecordId;
}
}
}
public List<Customer> GetAllCustomers()
{
var customersList = new List<Customer>();
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string command = "SELECT * FROM Customer";
SqlCommand sqlCommand = new SqlCommand(command, connection);
var reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var customer = new Customer();
customer.Id = (int)reader["Id"];
customer.Name = (string)reader["Name"];
customer.Email = (string)reader["Email"];
customer.Address = (string)reader["Adress"];
if (reader["Discount"] == DBNull.Value)
{
customer.Discount = null;
}
else
{
customer.Discount = (int)reader["Discount"];
}
customersList.Add(customer);
}
return customersList.ToList();
}
}
private void AddOrEditCustomer(string command, Customer customer, SqlConnection connection)
{
SqlCommand sqlCommand = new SqlCommand(command, connection);
sqlCommand.Parameters.AddWithValue("@name", customer.Name);
SqlParameter emailParameter = new SqlParameter("@email", customer.Email);
if (customer.Email == null)
{
emailParameter.Value = DBNull.Value;
}
sqlCommand.Parameters.Add(emailParameter);
sqlCommand.Parameters.AddWithValue("@adress", customer.Address);
SqlParameter discountParameter = new SqlParameter("@discout", customer.Discount);
if (customer.Discount == null)
{
discountParameter.Value = DBNull.Value;
}
sqlCommand.Parameters.Add(discountParameter);
var rows = sqlCommand.ExecuteNonQuery();
Console.WriteLine("Rows Affected: {0}", rows);
}