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 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 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 int Insert(string tablename, Dictionary <string, string> values) { // Create insert string command string insertSQL = InsertString(tablename, values); ContactsSQLHelper conSql = new ContactsSQLHelper(); using (MySqlCommand cmd = contactsDb.GetCommand(insertSQL)) { //contactsDb.Connection.Open(); return(cmd.ExecuteNonQuery()); } }
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(""); } } } }
public ElevesHelper() { contactsDb = new ContactsSQLHelper(); }