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;
        }
Example #2
0
 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;
 }
Example #3
0
 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();
         }
     }
 }
Example #4
0
 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);
 }