public static List <BaseDonnee> FindAll(int idProjet)
{
BaseDonnee myItem = null;
List <BaseDonnee> listItem = new List <BaseDonnee>();
StringBuilder mySql = new StringBuilder();
mySql.Append("SELECT ");
mySql.Append(GetSelectFields() + " ");
mySql.Append("FROM `BaseDonnee` basedonnee ");
mySql.Append("WHERE idProjet = @idProjet ");
using (MySqlConnection myConnection = new MySqlConnection(Param.MySQLConnectionString))
{
MySqlCommand myCommand = new MySqlCommand(mySql.ToString(), myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@idProjet", idProjet);
myConnection.Open();
using (MySqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
while (myReader.Read())
{
myItem = LoadADO(myReader);
listItem.Add(myItem);
}
}
myReader.Close();
}
myConnection.Close();
}
return(listItem);
}
protected void rptDatabase_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
BaseDonnee myDatabase = (BaseDonnee)e.Item.DataItem;
if (myDatabase != null)
{
LinkButton btnFavorisBaseDonnee = (LinkButton)e.Item.FindControl("btnFavorisBaseDonnee");
if (ListeBaseDonneeFavoris != null && btnFavorisBaseDonnee != null)
{
if (ListeBaseDonneeFavoris.Find(l => l.id == myDatabase.id) != null)
{
Control favOn = e.Item.FindControl("dbFavorisOn");
if (favOn != null)
{
favOn.Visible = true;
}
btnFavorisBaseDonnee.CommandName = "1";
}
else
{
Control favOff = e.Item.FindControl("dbFavorisOff");
if (favOff != null)
{
favOff.Visible = true;
}
btnFavorisBaseDonnee.CommandName = "0";
}
}
}
}
}
public static BaseDonnee Update(BaseDonnee myItem)
{
using (MySqlConnection myConnection = new MySqlConnection(Param.MySQLConnectionString))
{
StringBuilder mySql = new StringBuilder();
mySql.Append("UPDATE `BaseDonnee` SET ");
mySql.Append("`idProjet` = @idProjet, ");
mySql.Append("`libelle` = @libelle, ");
mySql.Append("`host` = @host, ");
mySql.Append("`databaseName` = @databaseName, ");
mySql.Append("`user` = @user, ");
mySql.Append("`password` = @password, ");
mySql.Append("`cheminSauvegarde` = @cheminSauvegarde ");
mySql.Append("WHERE `id` = @id ");
MySqlCommand myCommand = new MySqlCommand(mySql.ToString(), myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@id", myItem.id);
myCommand.Parameters.AddWithValue("@idProjet", myItem.idProjet);
myCommand.Parameters.AddWithValue("@libelle", myItem.libelle);
myCommand.Parameters.AddWithValue("@host", myItem.host);
myCommand.Parameters.AddWithValue("@databaseName", myItem.databaseName);
myCommand.Parameters.AddWithValue("@user", myItem.user);
myCommand.Parameters.AddWithValue("@password", myItem.password);
myCommand.Parameters.AddWithValue("@cheminSauvegarde", myItem.cheminSauvegarde);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
return(myItem);
}
public static BaseDonnee Insert(BaseDonnee myItem)
{
using (MySqlConnection myConnection = new MySqlConnection(Param.MySQLConnectionString))
{
StringBuilder mySql = new StringBuilder();
mySql.Append("INSERT INTO `BaseDonnee` ");
mySql.Append("(`idProjet`, `libelle`, ");
mySql.Append("`host`, `databaseName`, `user`, ");
mySql.Append("`password`, `cheminSauvegarde` )");
mySql.Append(" VALUES ");
mySql.Append("(@idProjet, @libelle, ");
mySql.Append("@host, @databaseName, @user, ");
mySql.Append("@password, @cheminSauvegarde );");
mySql.Append("SELECT LAST_INSERT_ID(); ");
MySqlCommand myCommand = new MySqlCommand(mySql.ToString(), myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@idProjet", myItem.idProjet);
myCommand.Parameters.AddWithValue("@libelle", myItem.libelle);
myCommand.Parameters.AddWithValue("@host", myItem.host);
myCommand.Parameters.AddWithValue("@databaseName", myItem.databaseName);
myCommand.Parameters.AddWithValue("@user", myItem.user);
myCommand.Parameters.AddWithValue("@password", myItem.password);
myCommand.Parameters.AddWithValue("@cheminSauvegarde", myItem.cheminSauvegarde);
myConnection.Open();
myItem.id = iZyMySQL.GetIntFromDBInt(myCommand.ExecuteScalar());
myConnection.Close();
}
return myItem;
}
public static BaseDonnee Load(int id)
{
BaseDonnee myItem = null;
StringBuilder mySql = new StringBuilder();
mySql.Append("SELECT ");
mySql.Append(GetSelectFields() + " ");
mySql.Append("FROM `BaseDonnee` basedonnee ");
mySql.Append("WHERE `id` = @id ");
using (MySqlConnection myConnection = new MySqlConnection(Param.MySQLConnectionString))
{
MySqlCommand myCommand = new MySqlCommand(mySql.ToString(), myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@id", id);
myConnection.Open();
using (MySqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
myReader.Read();
myItem = LoadADO(myReader);
}
myReader.Close();
}
myConnection.Close();
}
return(myItem);
}
public static List <BaseDonnee> FindFavoris(int idUtilisateur)
{
BaseDonnee myItem = null;
List <BaseDonnee> listItem = new List <BaseDonnee>();
StringBuilder mySql = new StringBuilder();
mySql.Append("SELECT ");
mySql.Append(GetSelectFields() + " ");
mySql.Append("FROM `BaseDonnee` basedonnee ");
mySql.Append("JOIN `UtilisateurFavoris` utilisateurfavoris ON utilisateurfavoris.idEntite = basedonnee.id ");
mySql.Append("WHERE utilisateurfavoris.idType = @idType AND utilisateurfavoris.idUtilisateur = @idUtilisateur");
using (MySqlConnection myConnection = new MySqlConnection(Param.MySQLConnectionString))
{
MySqlCommand myCommand = new MySqlCommand(mySql.ToString(), myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@idType", EnumTypeFavoris.BASE_DONNEE);
myCommand.Parameters.AddWithValue("@idUtilisateur", idUtilisateur);
myConnection.Open();
using (MySqlDataReader myReader = myCommand.ExecuteReader())
{
if (myReader.HasRows)
{
while (myReader.Read())
{
myItem = LoadADO(myReader);
listItem.Add(myItem);
}
}
myReader.Close();
}
myConnection.Close();
}
return(listItem);
}
public static BaseDonnee Insert(BaseDonnee myItem)
{
using (MySqlConnection myConnection = new MySqlConnection(Param.MySQLConnectionString))
{
StringBuilder mySql = new StringBuilder();
mySql.Append("INSERT INTO `BaseDonnee` ");
mySql.Append("(`idProjet`, `libelle`, ");
mySql.Append("`host`, `databaseName`, `user`, ");
mySql.Append("`password`, `cheminSauvegarde` )");
mySql.Append(" VALUES ");
mySql.Append("(@idProjet, @libelle, ");
mySql.Append("@host, @databaseName, @user, ");
mySql.Append("@password, @cheminSauvegarde );");
mySql.Append("SELECT LAST_INSERT_ID(); ");
MySqlCommand myCommand = new MySqlCommand(mySql.ToString(), myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@idProjet", myItem.idProjet);
myCommand.Parameters.AddWithValue("@libelle", myItem.libelle);
myCommand.Parameters.AddWithValue("@host", myItem.host);
myCommand.Parameters.AddWithValue("@databaseName", myItem.databaseName);
myCommand.Parameters.AddWithValue("@user", myItem.user);
myCommand.Parameters.AddWithValue("@password", myItem.password);
myCommand.Parameters.AddWithValue("@cheminSauvegarde", myItem.cheminSauvegarde);
myConnection.Open();
myItem.id = iZyMySQL.GetIntFromDBInt(myCommand.ExecuteScalar());
myConnection.Close();
}
return(myItem);
}
private void Stagiaire_Load(object sender, EventArgs e)
{
bs = BaseDonnee.RemplirListControl(lsStagiaire, "Stagiaire", "name", "idstagiaire");
txtIdStagiaire.DataBindings.Add("text", bs, "idstagiaire");
txtNum.DataBindings.Add("text", bs, "numstagiaire");
txtNom.DataBindings.Add("text", bs, "name");
txtCin.DataBindings.Add("text", bs, "cin");
}
private void btnSupprimer_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Voulez-vous supprimer cette Insription ?", "Supprission", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
bsInscription.RemoveCurrent();
}
sql = "delete from incription where idstagiaire =" + txtidStagiaire.Text + "";
BaseDonnee.exec(sql);
}
private void btnGSupprimer_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Voulez-vous vraimment supprimer ce Groupe ?", "Supprission", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
bs.RemoveCurrent();
}
sql = "delete from Groupe where idgroupe=" + txtIdgroupe.Text + "";
BaseDonnee.exec(sql);
}
private void btnSSupprimer_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Voulez-vous vraiment supprimer se Stagiaire ?", "Supprission", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
{
sql = "delete from Stagiaire where idstagiaire=" + txtIdStagiaire.Text;
BaseDonnee.exec(sql);
bs.RemoveCurrent();
}
}
private void btnInscrire_Click(object sender, EventArgs e)
{
txtId_Inscrire.Text = conteur + "";
if (txtNameUsers.Text.Equals("") || txtMatiere.Text.Equals("") || txtLogin.Text.Equals("") || txtPassword.Text.Equals(""))
{
MessageBox.Show("Vieullez remplir les champs de texte", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
BaseDonnee.exec("insert into users (nameusers,matiere,login,password,priorite) values('" + txtNameUsers.Text + "','" + txtMatiere.Text + "','" + txtLogin.Text + "','" + txtPassword.Text + "'," + (cbFonction.SelectedIndex + 1) + ")");
MessageBox.Show("Inscription Reussie", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
this.Close();
}
private static BaseDonnee LoadADO(MySqlDataReader myReader)
{
BaseDonnee myItem = new BaseDonnee();
myItem.id = iZyMySQL.GetIntFromDBInt(myReader["basedonnee_id"]);
myItem.idProjet = iZyMySQL.GetIntFromDBInt(myReader["basedonnee_idProjet"]);
myItem.libelle = iZyMySQL.GetStringFromDBString(myReader["basedonnee_libelle"]);
myItem.host = iZyMySQL.GetStringFromDBString(myReader["basedonnee_host"]);
myItem.databaseName = iZyMySQL.GetStringFromDBString(myReader["basedonnee_databaseName"]);
myItem.user = iZyMySQL.GetStringFromDBString(myReader["basedonnee_user"]);
myItem.password = iZyMySQL.GetStringFromDBString(myReader["basedonnee_password"]);
myItem.cheminSauvegarde = iZyMySQL.GetStringFromDBString(myReader["basedonnee_cheminSauvegarde"]);
return(myItem);
}
protected void btnEditDatabaseSave_Click(object sender, EventArgs e)
{
Projet myProjet = GetProjet();
if (myProjet != null)
{
bool isModif = false;
BaseDonnee myDatabase = new BaseDonnee();
if (!string.IsNullOrEmpty(hfDatabseId.Value))
{
myDatabase = BaseDonneeManager.Load(iZyInt.ConvertStringToInt(hfDatabseId.Value));
if (myDatabase != null)
{
isModif = true;
}
else
{
myDatabase = new BaseDonnee();
}
}
myDatabase.host = tbEditDatabaseHost.Text;
myDatabase.databaseName = tbEditDatabaseName.Text;
myDatabase.user = tbEditDatabaseUser.Text;
myDatabase.password = tbEditDatabasePassword.Text;
myDatabase.cheminSauvegarde = tbEditDatabaseChemin.Text;
myDatabase.idProjet = myProjet.id;
myDatabase.libelle = tbEditDatabaseLibelle.Text;
if (isModif)
{
BaseDonneeManager.Update(myDatabase);
}
else
{
BaseDonneeManager.Insert(myDatabase);
}
ListeBaseDonnees_Init();
ResetDatabaseForm();
btnAddDatabase.Enabled = true;
pnlEditDatabase.Visible = false;
// Sauvegarde
upDatabase.Update();
}
}
protected void btnSaveDatabase_Click(object sender, EventArgs e)
{
LinkButton btn = (LinkButton)sender;
if (btn != null && !string.IsNullOrEmpty(btn.CommandArgument))
{
int idDatabase = iZyInt.ConvertStringToInt(btn.CommandArgument);
BaseDonnee myDatabase = BaseDonneeManager.Load(idDatabase);
if (myDatabase != null)
{
// sauvegarde simple
if (!string.IsNullOrEmpty(BaseDonneeManager.Sauvegarde(myDatabase)))
{
// afficher erreur car pas de sauvegarde
}
}
}
}
protected void btnDownload_Click(object sender, EventArgs e)
{
LinkButton btn = (LinkButton)sender;
if (btn != null && !string.IsNullOrEmpty(btn.CommandArgument))
{
int idDatabase = iZyInt.ConvertStringToInt(btn.CommandArgument);
BaseDonnee myDatabase = BaseDonneeManager.Load(idDatabase);
if (myDatabase != null)
{
// export + récupération du chemin du fichier
string cheminZip = BaseDonneeManager.Sauvegarde(myDatabase, true);
if (File.Exists(cheminZip))
{
Response.AppendHeader("content-disposition", "attachment; filename=" + Path.GetFileName(cheminZip));
Response.ContentType = "application/zip";
Response.WriteFile(cheminZip);
}
}
}
}
private void btnGValider_Click(object sender, EventArgs e)
{
if (txtGroupe.Text.Equals(""))
{
MessageBox.Show("vieullez remplir les champs de texte svp !!", "Invertissement", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
bs.EndEdit();
Utils.activecom(true, pnGModification, pnGValidation, pnGNavigation, pnGZoneTexte);
if (op == true)
{
BaseDonnee.exec(sql + txtGroupe.Text + "')");
}
else
{
BaseDonnee.exec(sql + txtGroupe.Text + "'");
}
conteur2--;
}
}
private void btnSValider_Click(object sender, EventArgs e)
{
if (txtNum.Text.Equals("") || txtNom.Text.Equals("") || txtCin.Text.Equals(""))
{
MessageBox.Show("Vieullez remplir les champs de texte svp !!", "Invertissement", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (op == true)
{
BaseDonnee.exec(sql + txtNum.Text + ",'" + txtNom.Text + "','" + txtCin.Text + "')");
}
else
{
BaseDonnee.exec(sql + txtNum.Text + " name ='" + txtNom.Text + "',cin='" + txtCin.Text + "' where idstagiaire=" + txtIdStagiaire.Text);
}
bs.EndEdit();
BaseDonnee.remplirTable("Stagiaire");
Utils.activecom(true, pnSModification, pnSValidation, pnSNavigation, pnSZoneText);
conteur--;
}
}
private void btnvalider_Click(object sender, EventArgs e)
{
if (cbGroupe.SelectedIndex == -1)
{
MessageBox.Show("Vieullez remplir les champs", "Invertissement", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
bsInscription.EndEdit();
Utils.activecom2(false, cbGroupe, pnModification, pnValidation, pnNouveautie, pnNavigation);
}
if (op == false)
{
BaseDonnee.exec(sql + txtidStagiaire.Text + " ,idgroupe =" + cbGroupe.SelectedValue + " ,annee =" + txtAnneScholaire.Text + " where idstagiaire=" + txtidStagiaire.Text);
sql = null;
}
else
{
BaseDonnee.exec(sql + txtidStagiaire.Text + "," + cbGroupe.SelectedValue + "," + txtAnneScholaire.Text + ")");
sql = null;
}
}
protected void btnConfigDatabase_Click(object sender, EventArgs e)
{
LinkButton btn = (LinkButton)sender;
if (btn != null)
{
BaseDonnee myDatabase = BaseDonneeManager.Load(iZyInt.ConvertStringToInt(btn.CommandArgument));
if (myDatabase != null)
{
hfDatabseId.Value = btn.CommandArgument;
tbEditDatabaseHost.Text = myDatabase.host;
tbEditDatabaseName.Text = myDatabase.databaseName;
tbEditDatabaseUser.Text = myDatabase.user;
tbEditDatabasePassword.Text = myDatabase.password;
tbEditDatabaseChemin.Text = myDatabase.cheminSauvegarde;
tbEditDatabaseLibelle.Text = myDatabase.libelle;
pnlEditDatabase.Visible = true;
btnAddDatabase.Enabled = false;
}
}
upDatabase.Update();
}
private void Groupe_Load(object sender, EventArgs e)
{
bs = BaseDonnee.RemplirListControl(cbGroupe, "Groupe", "nomgroupe", "idgroupe");
txtIdgroupe.DataBindings.Add("text", bs, "idgroupe");
txtGroupe.DataBindings.Add("text", bs, "nomgroupe");
}
private void Inscription_Load(object sender, EventArgs e)
{
BaseDonnee.ouvrirConnection();
ds = new DataSet();
comStagiaire.CommandText = "select * from Stagiaire";
comGroupe.CommandText = "select * from Groupe";
comInscription.CommandText = "select * from incription";
comStagiaire.Connection = BaseDonnee.connection;
comGroupe.Connection = BaseDonnee.connection;
comInscription.Connection = BaseDonnee.connection;
daStagiaire = new SqlDataAdapter(comStagiaire);
daGroupe = new SqlDataAdapter(comGroupe);
daInscription = new SqlDataAdapter(comInscription);
daStagiaire.Fill(ds, "Stagiaire");
daGroupe.Fill(ds, "Groupe");
daInscription.Fill(ds, "incription");
cb = new SqlCommandBuilder(daInscription);
DataColumn c1 = ds.Tables["Groupe"].Columns["idgroupe"];
DataColumn c2 = ds.Tables["incription"].Columns["idgroupe"];
DataColumn c3 = ds.Tables["Stagiaire"].Columns["idstagiaire"];
DataColumn c4 = ds.Tables["incription"].Columns["idstagiaire"];
DataRelation r1 = new DataRelation("fk_Groupe_incription", c1, c2);
DataRelation r2 = new DataRelation("fk_Stagiaire_incription", c3, c4);
ds.Relations.Add(r1);
ds.Relations.Add(r2);
bsStagiaire = new BindingSource();
bsGroupe = new BindingSource();
bsInscription = new BindingSource();
bsGroupe.DataSource = ds;
bsGroupe.DataMember = "Groupe";
bsStagiaire.DataSource = ds;
bsStagiaire.DataMember = "Stagiaire";
bsInscription.DataSource = bsStagiaire;
bsInscription.DataMember = "fk_Stagiaire_incription";
lsStagiaire.DataSource = bsStagiaire;
lsStagiaire.DisplayMember = "name";
lsStagiaire.ValueMember = "idstagiaire";
cbGroupe.DataSource = bsGroupe;
cbGroupe.DisplayMember = "nomgroupe";
cbGroupe.ValueMember = "idgroupe";
txtidStagiaire.DataBindings.Add("text", bsStagiaire, "idstagiaire");
txtName.DataBindings.Add("text", bsStagiaire, "name");
cbGroupe.DataBindings.Add("selectedvalue", bsInscription, "idgroupe");
txtAnneScholaire.DataBindings.Add("text", bsInscription, "annee");
}
private static BaseDonnee LoadADO(MySqlDataReader myReader)
{
BaseDonnee myItem = new BaseDonnee();
myItem.id = iZyMySQL.GetIntFromDBInt(myReader["basedonnee_id"]);
myItem.idProjet = iZyMySQL.GetIntFromDBInt(myReader["basedonnee_idProjet"]);
myItem.libelle = iZyMySQL.GetStringFromDBString(myReader["basedonnee_libelle"]);
myItem.host = iZyMySQL.GetStringFromDBString(myReader["basedonnee_host"]);
myItem.databaseName = iZyMySQL.GetStringFromDBString(myReader["basedonnee_databaseName"]);
myItem.user = iZyMySQL.GetStringFromDBString(myReader["basedonnee_user"]);
myItem.password = iZyMySQL.GetStringFromDBString(myReader["basedonnee_password"]);
myItem.cheminSauvegarde = iZyMySQL.GetStringFromDBString(myReader["basedonnee_cheminSauvegarde"]);
return myItem;
}
public static BaseDonnee Update(BaseDonnee myItem)
{
using (MySqlConnection myConnection = new MySqlConnection(Param.MySQLConnectionString))
{
StringBuilder mySql = new StringBuilder();
mySql.Append("UPDATE `BaseDonnee` SET ");
mySql.Append("`idProjet` = @idProjet, ");
mySql.Append("`libelle` = @libelle, ");
mySql.Append("`host` = @host, ");
mySql.Append("`databaseName` = @databaseName, ");
mySql.Append("`user` = @user, ");
mySql.Append("`password` = @password, ");
mySql.Append("`cheminSauvegarde` = @cheminSauvegarde ");
mySql.Append("WHERE `id` = @id ");
MySqlCommand myCommand = new MySqlCommand(mySql.ToString(), myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@id", myItem.id);
myCommand.Parameters.AddWithValue("@idProjet", myItem.idProjet);
myCommand.Parameters.AddWithValue("@libelle", myItem.libelle);
myCommand.Parameters.AddWithValue("@host", myItem.host);
myCommand.Parameters.AddWithValue("@databaseName", myItem.databaseName);
myCommand.Parameters.AddWithValue("@user", myItem.user);
myCommand.Parameters.AddWithValue("@password", myItem.password);
myCommand.Parameters.AddWithValue("@cheminSauvegarde", myItem.cheminSauvegarde);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
return myItem;
}
