public static int Update(string CurCin, FicheInformations fi)
{
string request = "UPDATE FicheInformation SET CIN=@CIN, Adresse=@Adresse,Civilite=@Civilite, CodePostal=@CodePostal, CoEncadrant=@CoEncadrant, dateNaissance=@dateNaissance, Ville=@Ville," +
"Email=@Email, Employeur=@Employeur, Encadrant=@Encadrant,Gouvernorat=@Gouvernorat,Sujet=@Sujet," +
"LaboUniteeRecherche=@LaboUniteeRecherche, LaboUniteeRechercheCotutelle=@LaboUniteeRechercheCotutelle, Specialite=@Specialite," +
"Nationalite=@Nationalite, NomPrenom=@NomPrenom, PaysOrigine=@PaysOrigine, Profession=@Profession, Telephone=@Telephone, VilleNaissance=@VilleNaissance" +
" WHERE CIN = @CurCin";
SqlCeCommand cmd = new SqlCeCommand(request, Program.cnn);
cmd.Parameters.Add("@CIN", SqlDbType.NVarChar).Value = fi.CIN;
cmd.Parameters.Add("@NomPrenom", SqlDbType.NVarChar).Value = fi.NOMPRENOM;
cmd.Parameters.Add("@Civilite", SqlDbType.NVarChar).Value = fi.CIVILITE;
cmd.Parameters.Add("@DateNaissance", SqlDbType.NVarChar).Value = fi.DATENAISSANCE;
cmd.Parameters.Add("@VilleNaissance", SqlDbType.NVarChar).Value = fi.VILLENAISSANCE;
cmd.Parameters.Add("@PaysOrigine", SqlDbType.NVarChar).Value = fi.PAYSORIGINE;
cmd.Parameters.Add("@Nationalite", SqlDbType.NVarChar).Value = fi.NATIONALITE;
cmd.Parameters.Add("@Adresse", SqlDbType.NVarChar).Value = fi.ADRESSE;
cmd.Parameters.Add("@Ville", SqlDbType.NVarChar).Value = fi.VILLE;
cmd.Parameters.Add("@CodePostal", SqlDbType.NVarChar).Value = fi.CODEPOSTAL;
cmd.Parameters.Add("@Gouvernorat", SqlDbType.NVarChar).Value = fi.GOUVERNORAT;
cmd.Parameters.Add("@Telephone", SqlDbType.NVarChar).Value = fi.TELEPHONE;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = fi.EMAIL;
cmd.Parameters.Add("@Profession", SqlDbType.NVarChar).Value = fi.PROFESSION;
cmd.Parameters.Add("@Employeur", SqlDbType.NVarChar).Value = fi.EMPLOYEUR;
cmd.Parameters.Add("@Specialite", SqlDbType.NVarChar).Value = fi.SPECIALITE;
cmd.Parameters.Add("@LaboUniteeRecherche", SqlDbType.NVarChar).Value = fi.LABOUNITEERECHERCHE;
cmd.Parameters.Add("@LaboUniteeRechercheCotutelle", SqlDbType.NVarChar).Value = fi.LABOUNITEERECHERCHECOTUTELLE;
cmd.Parameters.Add("@Encadrant", SqlDbType.NVarChar).Value = fi.ENCADRANT;
cmd.Parameters.Add("@CoEncadrant", SqlDbType.NVarChar).Value = fi.COENCADRANT;
cmd.Parameters.Add("@Sujet", SqlDbType.NVarChar).Value = fi.SUJET;
cmd.Parameters.Add("@CurCin", SqlDbType.NVarChar).Value = CurCin;
return(DataBaseAccessUtilities.NonQueryRequest(cmd));
}
public static int Insert(FicheInformations fi)
{
string StrSQL = "INSERT INTO FicheInformation VALUES (@CIN,@NomPrenom,@Civilite,@DateNaissance,@VilleNaissance,@PaysOrigine" +
",@Nationalite,@Adresse,@Ville,@CodePostal,@Gouvernorat,@Telephone,@Email,@Profession,@Employeur,@Specialite," +
"@LaboUniteeRecherche,@LaboUniteeRechercheCotutelle,@Encadrant,@CoEncadrant,@Sujet)";
SqlCeCommand cmd = new SqlCeCommand(StrSQL, Program.cnn);
cmd.Parameters.Add("@CIN", SqlDbType.NVarChar).Value = fi.CIN;
cmd.Parameters.Add("@NomPrenom", SqlDbType.NVarChar).Value = fi.NOMPRENOM;
cmd.Parameters.Add("@Civilite", SqlDbType.NVarChar).Value = fi.CIVILITE;
cmd.Parameters.Add("@DateNaissance", SqlDbType.NVarChar).Value = fi.DATENAISSANCE;
cmd.Parameters.Add("@VilleNaissance", SqlDbType.NVarChar).Value = fi.VILLENAISSANCE;
cmd.Parameters.Add("@PaysOrigine", SqlDbType.NVarChar).Value = fi.PAYSORIGINE;
cmd.Parameters.Add("@Nationalite", SqlDbType.NVarChar).Value = fi.NATIONALITE;
cmd.Parameters.Add("@Adresse", SqlDbType.NVarChar).Value = fi.ADRESSE;
cmd.Parameters.Add("@Ville", SqlDbType.NVarChar).Value = fi.VILLE;
cmd.Parameters.Add("@CodePostal", SqlDbType.NVarChar).Value = fi.CODEPOSTAL;
cmd.Parameters.Add("@Gouvernorat", SqlDbType.NVarChar).Value = fi.GOUVERNORAT;
cmd.Parameters.Add("@Telephone", SqlDbType.NVarChar).Value = fi.TELEPHONE;
cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = fi.EMAIL;
cmd.Parameters.Add("@Profession", SqlDbType.NVarChar).Value = fi.PROFESSION;
cmd.Parameters.Add("@Employeur", SqlDbType.NVarChar).Value = fi.EMPLOYEUR;
cmd.Parameters.Add("@Specialite", SqlDbType.NVarChar).Value = fi.SPECIALITE;
cmd.Parameters.Add("@LaboUniteeRecherche", SqlDbType.NVarChar).Value = fi.LABOUNITEERECHERCHE;
cmd.Parameters.Add("@LaboUniteeRechercheCotutelle", SqlDbType.NVarChar).Value = fi.LABOUNITEERECHERCHECOTUTELLE;
cmd.Parameters.Add("@Encadrant", SqlDbType.NVarChar).Value = fi.ENCADRANT;
cmd.Parameters.Add("@CoEncadrant", SqlDbType.NVarChar).Value = fi.COENCADRANT;
cmd.Parameters.Add("@Sujet", SqlDbType.NVarChar).Value = fi.SUJET;
return(DataBaseAccessUtilities.NonQueryRequest(cmd));
}
public static void AddNew(FicheInformations fi)
{
if (DALFicheInformations.checkKeyUnicity(fi.CIN) == false)
{
DALFicheInformations.Insert(fi);
}
else
{
throw new MyException("Erreur dans l'ajout d'une fiche ", "Le CIN saisi est déja utilisé", "BLL");
}
}
public static int update(FicheInformations CurFi, FicheInformations NewFi)
{
if (CurFi.CIN != NewFi.CIN)
{
if (DALFicheInformations.checkKeyUnicity(NewFi.CIN) == false)
{
return(DALFicheInformations.Update(CurFi, NewFi));
}
else
{
throw new MyException("Erreur dans la modification de la Fiche", "Le nouveau CIN saisi est déja utilisé", "BLL");
}
}
else
{
return(DALFicheInformations.Update(CurFi, NewFi));
}
}
public static void ReadExcel(string fileName, string fileExt)
{
string conn = string.Empty;
if (fileExt.CompareTo(".xls") == 0)
{
conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007
}
else
{
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes';"; //for above excel 2007
}
using (OleDbConnection con = new OleDbConnection(conn))
{
try
{
Diplome D;
FicheInformations FI;
Inscription I;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlBook;
Excel.Worksheet xlSheet;
xlBook = xlApp.Workbooks.Open(fileName);
xlApp.Visible = true;
xlSheet = xlBook.ActiveSheet;
for (int i = 2; i <= xlSheet.UsedRange.Rows.Count; i++)
{
D = new Diplome();
FI = new FicheInformations();
I = new Inscription();
FI.CIN = readCell(i, "B", xlSheet);
FI.NOMPRENOM = readCell(i, "C", xlSheet);
FI.CIVILITE = readCell(i, "E", xlSheet);
FI.DATENAISSANCE = readCell(i, "D", xlSheet);
FI.VILLENAISSANCE = readCell(i, "BB", xlSheet);
FI.PAYSORIGINE = readCell(i, "G", xlSheet);
FI.NATIONALITE = readCell(i, "F", xlSheet);
FI.ADRESSE = readCell(i, "H", xlSheet);
FI.VILLE = readCell(i, "J", xlSheet);
FI.CODEPOSTAL = readCell(i, "K", xlSheet);
FI.GOUVERNORAT = readCell(i, "I", xlSheet);
FI.TELEPHONE = readCell(i, "L", xlSheet);
FI.EMAIL = readCell(i, "M", xlSheet);
FI.PROFESSION = readCell(i, "N", xlSheet);
FI.EMPLOYEUR = readCell(i, "O", xlSheet);
FI.SPECIALITE = readCell(i, "P", xlSheet);
FI.LABOUNITEERECHERCHE = readCell(i, "Q", xlSheet);
FI.LABOUNITEERECHERCHECOTUTELLE = readCell(i, "S", xlSheet);
FI.ENCADRANT = readCell(i, "T", xlSheet);
FI.COENCADRANT = readCell(i, "U", xlSheet);
FI.SUJET = readCell(i, "V", xlSheet);
BLFicheInformation.AddNew(FI);
if (xlSheet.UsedRange.Cells[i, "AG"].Value != null)
{
D.ANNEE = readCell(i, "AG", xlSheet);
D.DIPLOME = readCell(i, "AI", xlSheet);
D.SPECIALITE = readCell(i, "AH", xlSheet);
D.MENTION = "";
D.INSTITUTION = readCell(i, "AJ", xlSheet);
D.CIN = readCell(i, "B", xlSheet);
BLDiplome.AddNew(D);
}
if (xlSheet.UsedRange.Cells[i, "AK"].Value != null)
{
D.ANNEE = readCell(i, "AK", xlSheet);
D.DIPLOME = readCell(i, "AM", xlSheet);
D.SPECIALITE = readCell(i, "AL", xlSheet);
D.MENTION = "";
D.INSTITUTION = readCell(i, "AN", xlSheet);
D.CIN = readCell(i, "B", xlSheet);
BLDiplome.AddNew(D);
}
if (xlSheet.UsedRange.Cells[i, "AO"].Value != null)
{
D.ANNEE = readCell(i, "AO", xlSheet);
D.DIPLOME = readCell(i, "AQ", xlSheet);
D.SPECIALITE = readCell(i, "AP", xlSheet);
D.MENTION = "";
D.INSTITUTION = readCell(i, "AR", xlSheet);
D.CIN = readCell(i, "B", xlSheet);
BLDiplome.AddNew(D);
}
if (xlSheet.UsedRange.Cells[i, "AS"].Value != null)
{
D.ANNEE = readCell(i, "AS", xlSheet);
D.DIPLOME = readCell(i, "AU", xlSheet);
D.SPECIALITE = readCell(i, "AT", xlSheet);
D.MENTION = "";
D.INSTITUTION = readCell(i, "AV", xlSheet);
D.CIN = readCell(i, "B", xlSheet);
BLDiplome.AddNew(D);
}
if (xlSheet.UsedRange.Cells[i, "AW"].Value != null)
{
D.ANNEE = readCell(i, "AW", xlSheet);
D.DIPLOME = readCell(i, "AY", xlSheet);
D.SPECIALITE = readCell(i, "AX", xlSheet);
D.MENTION = "";
D.INSTITUTION = readCell(i, "AZ", xlSheet);
D.CIN = readCell(i, "B", xlSheet);
BLDiplome.AddNew(D);
}
if (xlSheet.UsedRange.Cells[i, "W"].Value != null)
{
I.ANNEEUNIVERSITAIRE = readCell(i, "W", xlSheet);
I.NIVEAU = readCell(i, "X", xlSheet);
I.COMMENTAIRE = "";
I.CIN = readCell(i, "B", xlSheet);
BLInscription.AddNew(I);
}
if (xlSheet.UsedRange.Cells[i, "Y"].Value != null)
{
I.ANNEEUNIVERSITAIRE = readCell(i, "Y", xlSheet);
I.NIVEAU = readCell(i, "Z", xlSheet);
I.COMMENTAIRE = "";
I.CIN = readCell(i, "B", xlSheet);
BLInscription.AddNew(I);
}
if (xlSheet.UsedRange.Cells[i, "AA"].Value != null)
{
I.ANNEEUNIVERSITAIRE = readCell(i, "AA", xlSheet);
I.NIVEAU = readCell(i, "AB", xlSheet);
I.COMMENTAIRE = "";
I.CIN = readCell(i, "B", xlSheet);
BLInscription.AddNew(I);
}
if (xlSheet.UsedRange.Cells[i, "AC"].Value != null)
{
I.ANNEEUNIVERSITAIRE = readCell(i, "AC", xlSheet);
I.NIVEAU = readCell(i, "AD", xlSheet);
I.COMMENTAIRE = "";
I.CIN = readCell(i, "B", xlSheet);
BLInscription.AddNew(I);
}
if (xlSheet.UsedRange.Cells[i, "AE"].Value != null)
{
I.ANNEEUNIVERSITAIRE = readCell(i, "AE", xlSheet);
I.NIVEAU = readCell(i, "AF", xlSheet);
I.COMMENTAIRE = "";
I.CIN = readCell(i, "B", xlSheet);
BLInscription.AddNew(I);
}
}
xlBook.Close();
xlApp.Quit();
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
}
public static int Update(FicheInformations CurFi, FicheInformations NewFi)
{
return(Update(CurFi.CIN, NewFi));
}
