private void btnReportClient_Click(object sender, EventArgs e)
{
ClientClass reportClient = new ClientClass();
if (lstClient.SelectedItems.Count > 0)
{
ListViewItem item = lstClient.SelectedItems[0];
reportClient.firstNameClient = item.SubItems[0].Text;
reportClient.lastNameClient = item.SubItems[1].Text;
reportClient.addressClient = item.SubItems[2].Text;
reportClient.cityClient = item.SubItems[3].Text;
reportClient.countryClient = item.SubItems[4].Text;
reportClient.postalCodeClient = item.SubItems[5].Text;
reportClient.companyClient = item.SubItems[6].Text;
reportClient.phoneClient = item.SubItems[7].Text;
reportClient.paymentClient = item.SubItems[8].Text;
reportClient.emailClient = item.SubItems[9].Text;
reportClient.createdClient = Convert.ToDateTime(item.SubItems[10].Text);
reportClient.updatedClient = Convert.ToDateTime(item.SubItems[11].Text);
reportClient.idClient = Convert.ToInt32(item.SubItems[12].Text);
ReportClient report = new ReportClient(reportClient);
report.Show();
}
else
{
MessageBox.Show("Please select a client ");
}
}
public static bool AddClient(ClientClass client)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string insertStatement =
"INSERT INTO clients " +
"( first_name, last_name, address, city, country, postal_code, " +
"company, phone, payment, email, created, updated ) " +
"VALUES ( @first_name, @last_name, @address, @city, @country, @postal_code, " +
"@company, @phone, @payment, @email, @created, @updated )";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@first_name", client.firstNameClient);
insertCommand.Parameters.AddWithValue(
"@last_name", client.lastNameClient);
insertCommand.Parameters.AddWithValue(
"@address", client.addressClient);
insertCommand.Parameters.AddWithValue(
"@city", client.cityClient);
insertCommand.Parameters.AddWithValue(
"@country", client.countryClient);
insertCommand.Parameters.AddWithValue(
"@postal_code", client.postalCodeClient);
insertCommand.Parameters.AddWithValue(
"@company", client.companyClient);
insertCommand.Parameters.AddWithValue(
"@phone", client.phoneClient);
insertCommand.Parameters.AddWithValue(
"@payment", client.paymentClient);
insertCommand.Parameters.AddWithValue(
"@email", client.emailClient);
insertCommand.Parameters.AddWithValue(
"@created", client.createdClient);
insertCommand.Parameters.AddWithValue(
"@updated", client.updatedClient);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <ClientClass> SearchClient(string search)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, first_name, last_name, address, city, country, postal_code, " +
"company, phone, payment, email, created, updated "
+ "FROM clients "
+ "WHERE last_name = @last_name" +
" OR first_name = @first_name" +
" OR email = @email";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
selectcommand.Parameters.AddWithValue("@last_name", search);
selectcommand.Parameters.AddWithValue("@first_name", search);
selectcommand.Parameters.AddWithValue("@email", search);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader();
List <ClientClass> clients = new List <ClientClass>();
while (custReader.Read())
{
ClientClass client = new ClientClass();
client.idClient = (int)custReader["id"];
client.firstNameClient = custReader["first_name"].ToString();
client.lastNameClient = custReader["last_name"].ToString();
client.addressClient = custReader["address"].ToString();
client.cityClient = custReader["city"].ToString();
client.countryClient = custReader["country"].ToString();
client.postalCodeClient = custReader["postal_code"].ToString();
client.companyClient = custReader["company"].ToString();
client.phoneClient = custReader["phone"].ToString();
client.paymentClient = custReader["payment"].ToString();
client.emailClient = custReader["email"].ToString();
client.updatedClient = Convert.ToDateTime(custReader["updated"]);
client.createdClient = Convert.ToDateTime(custReader["created"]);
clients.Add(client);
}
custReader.Close();
return(clients);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
private void btnModifyClient_Click(object sender, EventArgs e)
{
ClientClass modifyClient = new ClientClass();
if (lstClient.SelectedItems.Count > 0)
{
ListViewItem item = lstClient.SelectedItems[0];
modifyClient.firstNameClient = item.SubItems[0].Text;
modifyClient.lastNameClient = item.SubItems[1].Text;
modifyClient.addressClient = item.SubItems[2].Text;
modifyClient.cityClient = item.SubItems[3].Text;
modifyClient.countryClient = item.SubItems[4].Text;
modifyClient.postalCodeClient = item.SubItems[5].Text;
modifyClient.companyClient = item.SubItems[6].Text;
modifyClient.phoneClient = item.SubItems[7].Text;
modifyClient.paymentClient = item.SubItems[8].Text;
modifyClient.emailClient = item.SubItems[9].Text;
modifyClient.createdClient = Convert.ToDateTime(item.SubItems[10].Text);
modifyClient.updatedClient = Convert.ToDateTime(item.SubItems[11].Text);
modifyClient.idClient = Convert.ToInt32(item.SubItems[12].Text);
ModifyCient updateClient = new ModifyCient(modifyClient);
var result = updateClient.ShowDialog();
if (result == DialogResult.OK)
{
ClientClass changedUser = updateClient.modified;
bool returnedResult = ClientDB_Class.ModifyClient(changedUser);
if (returnedResult)
{
FillListViewClient();
MessageBox.Show("Client changed");
}
else
{
MessageBox.Show("Error");
}
}
}
else
{
MessageBox.Show("Please select a client ");
}
}
private void btnAddClient_Click(object sender, EventArgs e)
{
AddClient addClientClick = new AddClient();
var result = addClientClick.ShowDialog();
if (result == DialogResult.OK)
{
ClientClass AddClient = addClientClick.added;
bool returnedResult = ClientDB_Class.AddClient(AddClient);
if (returnedResult)
{
FillListViewClient();
MessageBox.Show("Client added");
}
else
{
MessageBox.Show("Error");
}
}
}
private void Modify_Click(object sender, EventArgs e)
{
if (IsValidData())
{
addclient.firstNameClient = txtFirstNameClient.Text;
addclient.lastNameClient = txtLastNameClient.Text;
addclient.addressClient = txtAddressClient.Text;
addclient.companyClient = txtCompanyClient.Text;
addclient.phoneClient = txtPhoneClient.Text;
addclient.postalCodeClient = txtPostalCodeClient.Text;
addclient.paymentClient = comboBoxPaymentModify.Text;
addclient.emailClient = txtEmailClient.Text;
addclient.updatedClient = DateTime.Today;
addclient.cityClient = txtCityClient.Text;
addclient.countryClient = txtCountryClient.Text;
this.modified = addclient;
this.DialogResult = DialogResult.OK;
this.Close();
}
}
public static bool ModifyClient(ClientClass client)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string updateStatement =
"UPDATE clients SET " +
"first_name = @first_name, " +
"last_name = @last_name, " +
"address = @address, " +
"city = @city, " +
"country = @country, " +
"postal_code = @postal_code, " +
"company = @company, " +
"phone = @phone, " +
"payment = @payment, " +
"email = @email, " +
"created = @created, " +
"updated = @updated " +
" WHERE id = @id ";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@id", client.idClient);
updateCommand.Parameters.AddWithValue(
"@first_name", client.firstNameClient);
updateCommand.Parameters.AddWithValue(
"@last_name", client.lastNameClient);
updateCommand.Parameters.AddWithValue(
"@address", client.addressClient);
updateCommand.Parameters.AddWithValue(
"@city", client.cityClient);
updateCommand.Parameters.AddWithValue(
"@country", client.countryClient);
updateCommand.Parameters.AddWithValue(
"@postal_code", client.postalCodeClient);
updateCommand.Parameters.AddWithValue(
"@company", client.companyClient);
updateCommand.Parameters.AddWithValue(
"@phone", client.phoneClient);
updateCommand.Parameters.AddWithValue(
"@payment", client.paymentClient);
updateCommand.Parameters.AddWithValue(
"@email", client.emailClient);
updateCommand.Parameters.AddWithValue(
"@created", client.createdClient);
updateCommand.Parameters.AddWithValue(
"@updated", client.updatedClient);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public ReportClient(ClientClass client)
{
addclient = client;
InitializeComponent();
}
public ModifyCient(ClientClass client)
{
addclient = client;
InitializeComponent();
}