//Constructeurs de M_Criime
public M_Crime(string id, string date, string borough, int coord_X, int coord_Y, M_Crime_description crime_desc, M_Juridiction jurisdiction)
{
this.id = id;
this.date = date;
this.borough = borough;
this.coord_X = coord_X;
this.coord_Y = coord_Y;
this.crime_desc = crime_desc;
this.jurisdiction = jurisdiction;
}
public List <M_Crime> RecupererCrimes(string date, string quartier, string id)
{
List <M_Crime> ListeC = new List <M_Crime>();
M_Crime MCrime = new M_Crime();
string borough;
int coord_X;
int coord_Y;
//connection à la base de données Mysql
string connectionString = "SERVER=localhost; PORT=3306; DATABASE=NY_Crimes; UId=esilvs6; PASSWORD=esilvs6;";
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Open();
MySqlCommand command = connection.CreateCommand();
//Traitement des différenrs cas selon les textbox remplies
if (id != "")
{
command.CommandText = "SELECT c.id, date, borough, coord_X, coord_Y, description, name FROM crime c, crime_description cd, jurisdiction j WHERE c.id = " + id + " AND cd.id = crime_description_id AND j.id = jurisdiction_id;";
}
else if (date != "" && quartier != "")
{
command.CommandText = "SELECT c.id, date, borough, coord_X, coord_Y, description, name FROM crime c, crime_description cd, jurisdiction j WHERE date = '" + date + "' AND borough = '" + quartier + "' AND cd.id = crime_description_id AND j.id = jurisdiction_id;";
}
else if (date == "" && quartier != "")
{
command.CommandText = "SELECT c.id, date, borough, coord_X, coord_Y, description, name FROM crime c, crime_description cd, jurisdiction j WHERE borough = '" + quartier + "' AND cd.id = crime_description_id AND j.id = jurisdiction_id;";
}
else if (date != "" && quartier == "")
{
command.CommandText = "SELECT c.id, date, borough, coord_X, coord_Y, description, name FROM crime c, crime_description cd, jurisdiction j WHERE date = '" + date + "' AND cd.id = crime_description_id AND j.id = jurisdiction_id;";
}
MySqlDataReader reader;
reader = command.ExecuteReader();
while (reader.Read())
{
M_Crime_description McrimeDesc = new M_Crime_description();
M_Juridiction MJurisdiction = new M_Juridiction();
id = reader.GetValue(0).ToString();
date = reader.GetValue(1).ToString();
borough = reader.GetValue(2).ToString();
coord_X = int.Parse(reader.GetValue(3).ToString());
coord_Y = int.Parse(reader.GetValue(4).ToString());
McrimeDesc.Description = reader.GetValue(5).ToString();
MJurisdiction.Name = reader.GetValue(6).ToString();
MCrime = new M_Crime(id, date, borough, coord_X, coord_Y, McrimeDesc, MJurisdiction);
ListeC.Add(MCrime);
}
connection.Close();
return(ListeC);
}
