1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
| namespace DAL
{
public class OrganeProvider
{
public List<OrganeEntity> LoadData()
{
List<OrganeEntity> list = new List<OrganeEntity>();
using (OleDbConnection cn = new OleDbConnection())
{
string DatabaseName = "Base_Rexx1.accdb";
string pathBase = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), @"SNCF\Projet REXX");
string DatabaseNameL = System.IO.Path.Combine(pathBase, DatabaseName);
cn.ConnectionString = cn.ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};", DatabaseNameL);
cn.Open();
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandText =
" SELECT table_organe.[id] AS idOrgane,table_serie.[Serie] AS serie,table_serie.[id] AS idSerie,table_lieux.[Lieux] As lieu,table_lieux.[id] AS idLieu,table_famille.Famille As famille,table_famille.[ID] AS idFamille,table_organe.[Libelle]," +
" table_organe.[Description], table_Piece.Symbole AS Symbole,table_piece.Nom As piece,table_piece.idPiece AS idPiece" +
" FROM (((((rle_serie_lieu_organe " +
" INNER JOIN [table_organe] ON [rle_serie_lieu_organe].[idOrgane] = [table_organe].[id])" +
" INNER JOIN [table_serie] ON [rle_serie_lieu_organe].[idserie] = [table_serie].[id])" +
" INNER JOIN [table_lieux] ON [rle_serie_lieu_organe].[idLieux] = [table_lieux].[id])" +
" INNER JOIN [table_famille] ON [table_organe].[Famille] = [table_famille].[id])" +
" LEFT JOIN [rle_affectation_organe_piece] ON [rle_serie_lieu_organe].[idSerie] = [rle_affectation_organe_piece].[idSerie]" +
" AND [rle_serie_lieu_organe].[idOrgane] = [rle_affectation_organe_piece].[idOrgane]" +
" AND [rle_serie_lieu_organe].[idLieux] = [rle_affectation_organe_piece].[idLieux])" +
" LEFT JOIN [table_piece] ON [rle_affectation_organe_piece].[idPiece] = [table_piece].[idPiece]";
cmd.Connection = cn; ;
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
OrganeEntity o = new OrganeEntity();
FamilleEntity f = new FamilleEntity();
SerieEntity s = new SerieEntity();
LieuEntity l = new LieuEntity();
f.idFamille = (int)rdr["idFamille"];
f.nom = rdr["famille"] == DBNull.Value ? string.Empty : rdr["famille"].ToString();
s.idSerie = (int)rdr["idSerie"];
s.Nom = rdr["serie"].ToString();
l.idLieu = (int)rdr["idLieu"];
l.Nom = rdr["lieu"].ToString();
if (rdr["idOrgane"] != DBNull.Value)
{
o.idOrgane = (int)rdr["idOrgane"];
}
o.libelle = rdr["Libelle"] == DBNull.Value ? string.Empty : rdr["Libelle"].ToString();
o.description = rdr["Description"] == DBNull.Value ? string.Empty : rdr["Description"].ToString();
o.famille = f;
if(rdr["idPiece"] != DBNull.Value)
{
PieceEntity P = new PieceEntity();
P.idPiece = (int)rdr["idPiece"];
P.nom = rdr["piece"].ToString();
if(rdr["Symbole"]!= DBNull.Value)
{
P.symbole = (int)rdr["Symbole"];
}
o.piece = P;
}
o.famille = f;
o.serie = s;
o.lieu = l;
list.Add(o);
}
}
}
}
return list;
}
}
} |
Partager