private string GenerateUpdateSql()
{
List <string> updates = new List <string>();
foreach (Control control in EditControls)
{
DescriptionControl dc = control.Tag as DescriptionControl;
if (dc.ChampEdite)
{
if (dc.TypeOf == typeof(string))
{
updates.Add($"{dc.AliasDuChamp}='{GetControlDbValue(control)}', ");
}
else if (dc.TypeOf == typeof(int))
{
updates.Add($"{dc.AliasDuChamp}={GetControlDbValue(control)}, ");
}
}
}
if (updates.Count < 1)
{
return(string.Empty);
}
return($"UPDATE Membership SET {Utils.ConcatanateListOfString(updates)} WHERE ID={ID}");
}
private void ClearFieldNeedsUpdate()
{
textBoxAdresseLigne2Ville.ForeColor = Color.Black;
comboBoxProvinces.ForeColor = Color.Black;
textBoxAdresseLigne2CodePostal.ForeColor = Color.Black;
errorProvider.Clear();
buttonSave.Enabled = true;
foreach (Control c in EditControls)
{
DescriptionControl dc = c.Tag as DescriptionControl;
dc.ChampEdite = false;
c.ForeColor = Color.Black;
}
}
private void dataGridViewSelectionOccured(object sender, DataGridViewCellEventArgs e)
{
DataGridViewRow rows = dataGridView1.Rows[e.RowIndex];
ID = (int)rows.Cells["ID"].Value;
foreach (Control control in EditControls)
{
DescriptionControl dc = control.Tag as DescriptionControl;
SetControlDbValue(control, rows.Cells[dc.NomDuChamps].Value.ToString());
}
NewData = false;
ClearFieldNeedsUpdate();
ShowHideFamiliale();
buttonAjout1An.Enabled = true;
}
private string GenerateInsertSql()
{
List <string> fields, values;
fields = new List <string>();
values = new List <string>();
foreach (Control control in EditControls)
{
DescriptionControl dc = control.Tag as DescriptionControl;
if (string.Compare(dc.AliasDuChamp, "ID", StringComparison.InvariantCultureIgnoreCase) != 0)
{
fields.Add($"{dc.AliasDuChamp}, ");
if (dc.TypeOf == typeof(string))
{
values.Add($"'{GetControlDbValue(control)}', ");
}
else if (dc.TypeOf == typeof(int))
{
values.Add(string.IsNullOrWhiteSpace(control.Text) ? "NULL, " : $"{control.Text}, ");
}
}
}
return($"INSERT INTO Membership ({Utils.ConcatanateListOfString(fields)}) VALUES ({Utils.ConcatanateListOfString(values)})");
}
private void LoadData()
{
List <string> updates = new List <string>();
string sqlCommand = "SELECT ";
foreach (Control control in EditControls)
{
DescriptionControl dc = control.Tag as DescriptionControl;
sqlCommand += $"{dc.AliasDuChamp} as '{dc.NomDuChamps}', ";
}
sqlCommand = sqlCommand.Substring(0, sqlCommand.Length - 2);
sqlCommand += $" FROM Membership ";
sqlCommand += $"WHERE NomFamille LIKE '{textBoxWhereClause.Text}%'";
DateTime refDate = DateTime.Now;
if (checkBoxActives.Checked || checkBoxEchue.Checked)
{
if (!(checkBoxActives.Checked && checkBoxEchue.Checked))
{
if (checkBoxEchue.Checked)
{
sqlCommand += " AND ";
sqlCommand += $"DateRenouvellement < '{refDate.ToShortDateString()}'";
}
if (checkBoxActives.Checked)
{
sqlCommand += " AND ";
sqlCommand += $"DateRenouvellement >= '{refDate.ToShortDateString()}'";
}
}
}
else
{
sqlCommand += " AND ";
sqlCommand += $"DateRenouvellement = '{new DateTime(2000, 1, 1).ToShortDateString()}'";
}
if (checkBoxAvecNoMembres.Checked || checkBoxSansNoMembre.Checked)
{
if (!(checkBoxAvecNoMembres.Checked && checkBoxSansNoMembre.Checked))
{
if (checkBoxSansNoMembre.Checked)
{
sqlCommand += " AND ";
sqlCommand += "NoMembre IS NULL";
}
if (checkBoxAvecNoMembres.Checked)
{
sqlCommand += " AND ";
sqlCommand += "NoMembre IS NOT NULL";
}
}
}
else
{
sqlCommand += " AND ";
sqlCommand += $"DateRenouvellement = '{new DateTime(2000, 1, 1).ToShortDateString()}'";
}
sqlCommand += " ORDER BY NomFamille";
if (checkBoxActives.Checked || checkBoxEchue.Checked)
{
}
try {
if (Connection.State == ConnectionState.Closed)
{
Connection.Open();
}
using (SqlDataAdapter da = new SqlDataAdapter(sqlCommand, Connection)) {
using (DataTable dt = new DataTable()) {
da.Fill(dt);
dataGridView1.DataSource = dt;
}
}
dataGridView1.Rows[0].Selected = true;
} finally {
//Connection.Close();
}
}
