public static ObservableCollection <Materiel> GetFilteredMateriel(int SelectedClient, int SelectedSite) { List <Materiel> result = new List <Materiel>(); using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GestionMatos"].ToString())) { conn.Open(); using (SqlCommand cmd = new SqlCommand("Select * from materiel join site on materiel.id_site=site.id_site join client on materiel.id_client=client.id_client where client.id_client=@selectedclient AND site.id_site = @selectedsite", conn)) { cmd.Parameters.AddWithValue("@selectedclient", SelectedClient); cmd.Parameters.AddWithValue("@selectedsite", SelectedSite); using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Materiel filteredmateriels = new Materiel(); filteredmateriels.Idm = Convert.ToInt32(rdr["id_materiel"]); filteredmateriels.NomMateriel = rdr["Nom"].ToString(); result.Add(filteredmateriels); } } } conn.Close(); } var oc = new ObservableCollection <Materiel>(); result.ForEach(x => oc.Add(x)); return(oc); }
public static ObservableCollection <Materiel> GetMateriel() { List <Materiel> result = new List <Materiel>(); using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GestionMatos"].ToString())) { conn.Open(); using (SqlCommand cmd = new SqlCommand("Select * from materiel join site on materiel.id_site=site.id_site join client on materiel.id_client=client.id_client", conn)) { using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Materiel materiel = new Materiel(); materiel.Idm = Convert.ToInt32(rdr["id_materiel"]); materiel.NomMateriel = rdr["Nom"].ToString(); materiel.Description = rdr["Description"].ToString(); materiel.Numero = Convert.ToInt32(rdr["Numero"]); materiel.MTBF = Convert.ToInt32(rdr["MTBF"]); materiel.NomSite = rdr["Nom_site"].ToString(); materiel.NomClient = rdr["Nom_client"].ToString(); materiel.Dateinterv = Convert.ToDateTime(rdr["date_interv"]); result.Add(materiel); } } } conn.Close(); } var oc = new ObservableCollection <Materiel>(); result.ForEach(x => oc.Add(x)); return(oc); }
private static void Update(Materiel materiel, SqlConnection conn, SqlTransaction tran) { using (SqlCommand cmd = new SqlCommand("update materiel set Nom = @name, Description = @des, Numero = @num, MTBF = @mtbf, where id_materiel = @id", conn, tran)) { cmd.Parameters.AddWithValue("@id", materiel.Idm); cmd.Parameters.AddWithValue("@name", materiel.NomMateriel); cmd.Parameters.AddWithValue("@des", materiel.Description); cmd.Parameters.AddWithValue("@num", materiel.Numero); cmd.Parameters.AddWithValue("@mtbf", materiel.MTBF); cmd.Parameters.AddWithValue("@dint", materiel.Dateinterv); cmd.Parameters.AddWithValue("@idint", materiel.Idinterv); cmd.ExecuteNonQuery(); } }
private static void Insert(Materiel materiel, SqlConnection conn, SqlTransaction tran) { int mtbf = materiel.MTBF; DateTime myDateTime = DateTime.Now; string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd"); using (SqlCommand cmd = new SqlCommand("insert into materiel(Nom, Description, Numero, MTBF, id_site, id_client, date_interv) values (@name, @des, @num, @mtbf, @idsite, @idclient, (DATEADD(year,@mtbf,@formatedDate)))", conn, tran)) { cmd.Parameters.AddWithValue("@id", materiel.Idm); cmd.Parameters.AddWithValue("@name", materiel.NomMateriel); cmd.Parameters.AddWithValue("@des", materiel.Description); cmd.Parameters.AddWithValue("@num", materiel.Numero); cmd.Parameters.AddWithValue("@mtbf", materiel.MTBF); cmd.Parameters.AddWithValue("@idsite", materiel.Idsite); cmd.Parameters.AddWithValue("@idclient", materiel.Idclient); cmd.Parameters.AddWithValue("@formatedDate", sqlFormattedDate); cmd.ExecuteNonQuery(); } }