public static List <Contact> GetContacts()
{
string sqlstring = "SELECT * FROM ct_contacts ORDER BY Nom";
using (MySqlCommand cmd = ContactsSQLHelper.GetCommand(sqlstring))
{
cmd.Connection.Open();
MySqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
List <Contact> ContactList = new List <Contact>();
while (dr.Read())
{
Contact contact = new Contact();
contact.Contact_ID = Convert.ToInt32(dr["ID"]);
contact.ActuellementInscrit = Convert.ToInt32(dr["ActuellementInscrit"]);
contact.Adresse1 = Convert.ToString(dr["Adresse1"]);
contact.Adresse2 = Convert.ToString(dr["Adresse2"]);
contact.Adresse3 = Convert.ToString(dr["Adresse3"]);
contact.CodePostal = Convert.ToString(dr["Code Postal"]);
contact.Comite = Convert.ToInt32(dr["Comite"]);
contact.Email = Convert.ToString(dr["Email"]);
contact.Fixe = Convert.ToString(dr["Fixe"]);
contact.NePasContacter = Convert.ToInt32(dr["NePasContacter"]);
contact.Nom = Convert.ToString(dr["Nom"]);
contact.Notes = Convert.ToString(dr["Notes"]);
contact.Portable = Convert.ToString(dr["Portable"]);
contact.Prenom = Convert.ToString(dr["Prenom"]);
contact.Ville = Convert.ToString(dr["Ville"]);
ContactList.Add(contact);
}
dr.Close();
return(ContactList);
}
}
public static void UpdateContact(Contact contact)
{
string sqlstring = "UPDATE ct_contacts SET `Nom`=?vNom, `Prenom`=?vPrenom, `Adresse1`=?vAdresse1, `Adresse2`=?vAdresse2, `Adresse3`=?vAdresse3, `Ville`=?vVille, `Code Postal`=?vCodePostal, `Fixe`=?vFixe, `Portable`=?vPortable, `Email`=?vEmail, `Notes`=?vNotes, `ActuellementInscrit`=?vActuellementInscrit, `Comite`=?vComite, `NePasContacter`=?vNePasContacter WHERE ID = ?key";
using (MySqlCommand cmd = ContactsSQLHelper.GetCommand(sqlstring))
{
cmd.Parameters.Add(new MySqlParameter("key", contact.Contact_ID));
cmd.Parameters.Add(new MySqlParameter("vNom", contact.Nom));
cmd.Parameters.Add(new MySqlParameter("vPrenom", contact.Prenom));
cmd.Parameters.Add(new MySqlParameter("vAdresse1", contact.Adresse1));
cmd.Parameters.Add(new MySqlParameter("vAdresse2", contact.Adresse2));
cmd.Parameters.Add(new MySqlParameter("vAdresse3", contact.Adresse3));
cmd.Parameters.Add(new MySqlParameter("vVille", contact.Ville));
cmd.Parameters.Add(new MySqlParameter("vCodePostal", contact.CodePostal));
cmd.Parameters.Add(new MySqlParameter("vFixe", contact.Fixe));
cmd.Parameters.Add(new MySqlParameter("vPortable", contact.Portable));
cmd.Parameters.Add(new MySqlParameter("vEmail", contact.Email));
cmd.Parameters.Add(new MySqlParameter("vNotes", contact.Notes));
cmd.Parameters.Add(new MySqlParameter("vActuellementInscrit", contact.ActuellementInscrit));
cmd.Parameters.Add(new MySqlParameter("vComite", contact.Comite));
cmd.Parameters.Add(new MySqlParameter("vNePasContacter", contact.NePasContacter));
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Checks that the username and password combination exist in the database.
/// </summary>
/// <param name="username">Username to check</param>
/// <param name="password">Password corresponding to the user</param>
/// <returns></returns>
public static bool ValidateLogin(string username, string password)
{
DataSet dataset = new DataSet();
ContactsSQLHelper conSql = new ContactsSQLHelper();
using (MySqlDataAdapter adapter = new MySqlDataAdapter())
{
adapter.SelectCommand = conSql.GetCommand("SELECT * FROM employes WHERE utilisateur = ?user AND motdepasse = ?pw");
adapter.SelectCommand.Parameters.Add("?user", username);
adapter.SelectCommand.Parameters.Add("?pw", ToMD5(password));
conSql.Connection.Open();
adapter.Fill(dataset);
if (dataset != null && dataset.Tables.Count > 0 && dataset.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
}
public static string GetColumnComment(string tablename, string columnname)
{
string comment = "";
ContactsSQLHelper conSql = new ContactsSQLHelper();
string selectSQL = "SELECT column_comment FROM information_schema.columns WHERE ((columns.table_name=?tablename) AND (columns.column_name=?columnname))";
using (MySqlCommand cmd = conSql.GetCommand(selectSQL))
{
cmd.Parameters.Add("?tablename", tablename);
cmd.Parameters.Add("?columnname", columnname);
conSql.Connection.Open();
using (MySqlDataReader dr = cmd.ExecuteReader())
{
dr.Read();
if (dr.HasRows)
{
return(dr.GetString(0));
}
else
{
return("");
}
}
}
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
{
string insertSQL = "DELETE FROM " + tablename + " WHERE ID='" + GridView1.DataKeys[e.RowIndex].Value.ToString() + "'";
ContactsSQLHelper conSql = new ContactsSQLHelper();
int executed = 0;
using (MySqlCommand cmd = conSql.GetCommand(insertSQL))
{
conSql.Connection.Open();
executed = cmd.ExecuteNonQuery();
}
if (executed == 1)
{
Label1.Text = "Fiche supprimée avec succès";
EmptyTextBoxes();
BindDataGrid();
}
else
{
String message = "Une erreur est survenue. ID = '" + GridView1.Rows[e.RowIndex].Cells[1].Text + "' et Nombre = '" + GridView1.Rows[e.RowIndex].Cells[2].Text;
Label1.Text = message;
}
}
}
public static List <Contact> GetContactsById(Int32 ContactID, Int32 Status)
{
string sqlstring = "SELECT * FROM ct_contacts";
if (ContactID == 0 && Status == -1)
{
sqlstring += " ORDER BY Nom";
}
else if (ContactID != 0)
{
sqlstring += " WHERE ID=?key ORDER BY Nom";
}
else if (ContactID == 0 && Status > -1)
{
sqlstring += " WHERE ActuellementInscrit=?inscrit ORDER BY Nom";
}
using (MySqlCommand cmd = ContactsSQLHelper.GetCommand(sqlstring))
{
if (ContactID > 0)
{
cmd.Parameters.Add(new MySqlParameter("key", ContactID));
}
if (Status >= 0)
{
cmd.Parameters.Add(new MySqlParameter("inscrit", Status));
}
cmd.Connection.Open();
MySqlDataReader dr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
List <Contact> ContactList = new List <Contact>();
while (dr.Read())
{
Contact contact = new Contact();
contact.Contact_ID = Convert.ToInt32(dr["ID"]);
contact.ActuellementInscrit = Convert.ToInt32(dr["ActuellementInscrit"]);
contact.Adresse1 = Convert.ToString(dr["Adresse1"]);
contact.Adresse2 = Convert.ToString(dr["Adresse2"]);
contact.Adresse3 = Convert.ToString(dr["Adresse3"]);
contact.CodePostal = Convert.ToString(dr["Code Postal"]);
contact.Comite = Convert.ToInt32(dr["Comite"]);
contact.Email = Convert.ToString(dr["Email"]);
contact.Fixe = Convert.ToString(dr["Fixe"]);
contact.NePasContacter = Convert.ToInt32(dr["NePasContacter"]);
contact.Nom = Convert.ToString(dr["Nom"]);
contact.Notes = Convert.ToString(dr["Notes"]);
contact.Portable = Convert.ToString(dr["Portable"]);
contact.Prenom = Convert.ToString(dr["Prenom"]);
contact.Ville = Convert.ToString(dr["Ville"]);
ContactList.Add(contact);
}
dr.Close();
return(ContactList);
}
}
private void BindDataGrid()
{
ContactsSQLHelper conSql = new ContactsSQLHelper();
using (MySqlCommand cmd = conSql.GetCommand("SELECT * FROM " + tablename))
{
conSql.Connection.Open();
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
}
}
public DataSet GetContactsDataSet(string SQL)
{
DataSet dataset = new DataSet();
using (MySqlCommand cmd = contactsDb.GetCommand(SQL))
{
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
contactsDb.Connection.Open();
adapter.Fill(dataset);
}
if (dataset != null && dataset.Tables.Count > 0 && dataset.Tables[0].Rows.Count > 0)
{
return(dataset);
}
else
{
return(null);
}
}
public static int DeleteContact(Contact contact)
{
string sqlstring = "DELETE FROM ct_contacts WHERE ID=?key";
using (MySqlCommand cmd = ContactsSQLHelper.GetCommand(sqlstring))
{
cmd.Parameters.Add(new MySqlParameter("key", contact.Contact_ID));
cmd.Connection.Open();
int i = cmd.ExecuteNonQuery();
cmd.Connection.Close();
return(i);
}
}
/// <summary>
/// Write the new password to the database
/// </summary>
/// <param name="username">Username for the user to change</param>
/// <param name="newpassword">New password</param>
/// <returns>An int specifying whether the SQL query to update the record was successful or not.</returns>
public static int ChangePassword(string username, string newpassword)
{
ContactsSQLHelper conSql = new ContactsSQLHelper();
string insertSQL = "UPDATE employes SET motdepasse=?pw WHERE utilisateur=?user";
using (MySqlCommand cmd = conSql.GetCommand(insertSQL))
{
cmd.Parameters.Add("?pw", ToMD5(newpassword));
cmd.Parameters.Add("?user", username);
conSql.Connection.Open();
return(cmd.ExecuteNonQuery());
}
}
public static Int32 InsertContact(Contact contact)
{
string sqlstring = "INSERT INTO ct_contacts (`Nom`, `Prenom`, `Adresse1`, `Adresse2`, `Adresse3`, `Ville`, `Code Postal`, `Fixe`, `Portable`, `Email`, `Notes`, `ActuellementInscrit`, `Comite`, `NePasContacter`) ";
sqlstring += "VALUES(?vNom, ?vPrenom, ?vAdresse1, ?vAdresse2, ?vAdresse3, ?vVille, ?vCodePostal, ?vFixe, ?vPortable, ?vEmail, ?vNotes, ?vActuellementInscrit, ?vComite, ?vNePasContacter)";
using (MySqlCommand cmd = ContactsSQLHelper.GetCommand(sqlstring))
{
cmd.Parameters.Add(new MySqlParameter("vNom", contact.Nom));
cmd.Parameters.Add(new MySqlParameter("vPrenom", contact.Prenom));
cmd.Parameters.Add(new MySqlParameter("vAdresse1", contact.Adresse1));
cmd.Parameters.Add(new MySqlParameter("vAdresse2", contact.Adresse2));
cmd.Parameters.Add(new MySqlParameter("vAdresse3", contact.Adresse3));
cmd.Parameters.Add(new MySqlParameter("vVille", contact.Ville));
cmd.Parameters.Add(new MySqlParameter("vCodePostal", contact.CodePostal));
cmd.Parameters.Add(new MySqlParameter("vFixe", contact.Fixe));
cmd.Parameters.Add(new MySqlParameter("vPortable", contact.Portable));
cmd.Parameters.Add(new MySqlParameter("vEmail", contact.Email));
cmd.Parameters.Add(new MySqlParameter("vNotes", contact.Notes));
cmd.Parameters.Add(new MySqlParameter("vActuellementInscrit", contact.ActuellementInscrit));
cmd.Parameters.Add(new MySqlParameter("vComite", contact.Comite));
cmd.Parameters.Add(new MySqlParameter("vNePasContacter", contact.NePasContacter));
cmd.Connection.Open();
cmd.ExecuteNonQuery();
// If has last inserted id, add a parameter to hold it.
if (cmd.LastInsertedId != null)
{
cmd.Parameters.Add(
new MySqlParameter("newId", cmd.LastInsertedId));
}
// Return the id of the new record. Convert from Int64 to Int32 (int).
return(Convert.ToInt32(cmd.Parameters["@newId"].Value));
}
}
public static DataSet ClassesInUseLookup()
{
DataSet dataset = new DataSet();
ContactsSQLHelper contactsDb = new ContactsSQLHelper();
string SQL = "SELECT * FROM classesinuse ORDER BY Niveau";
using (MySqlCommand cmd = contactsDb.GetCommand(SQL))
{
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = cmd;
contactsDb.Connection.Open();
adapter.Fill(dataset);
}
if (dataset != null && dataset.Tables.Count > 0 && dataset.Tables[0].Rows.Count > 0)
{
return(dataset);
}
else
{
return(null);
}
}
private static string GetUserRole(string username)
{
ContactsSQLHelper conSql = new ContactsSQLHelper();
string selectSQL = "SELECT * FROM employes WHERE utilisateur=?user";
using (MySqlCommand cmd = conSql.GetCommand(selectSQL))
{
cmd.Parameters.Add("?user", username);
conSql.Connection.Open();
using (MySqlDataReader dr = cmd.ExecuteReader())
{
dr.Read();
if (dr.HasRows)
{
return(dr.GetString(dr.GetOrdinal("role")));
}
else
{
return("");
}
}
}
}