Discussion :

[al-faddi]

Bonsoir!
jessai d'executé une requete paramétrée mais j'obtiens une erreur.
voici mon code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
public List<Brouillard> RechercheBrouillard2(FiltreDate filtreDate, object date)
        {
            try
            {
                var cmd = new MySqlCommand("", Connection);
                cmd.Parameters.Add(new MySqlParameter("@cum", MySqlDbType.Int64));
                cmd.Parameters["@cum"].Value = 0;
                cmd.CommandText =
                    "select mvt_date, reference, motif, recette, depense, cumul from ( select mvt_date, reference, motif, recette, depense, @cum := @cum + recette - depense as cumul, periode, mvt_id, rang from vue cross join (select  @cum:=0) as x ) as y";
                var where = "";
                switch (filtreDate)
                {
                    case FiltreDate.Annee:
                        if (string.IsNullOrWhiteSpace(where))
                            where = string.Format(" where Year(m.mvt_date) = {0}", (double)date);
                        else
                            where += string.Format(" and  Year(m.mvt_date)  = {0}", (double)date);
                        break;
                    case FiltreDate.EntreAnnee:
                        var rangei = (RangeEntier)date;
                        if (string.IsNullOrWhiteSpace(where))
                            where = string.Format(" where Year(m.mvt_date) between {0} and {1}", rangei.Debut, rangei.Fin);
                        else
                            where += string.Format(" and Year(m.mvt_date) between {0} and {1}", rangei.Debut, rangei.Fin);
                        break;
                    case FiltreDate.EntreJour:
                        var ranged = (RangeDate)date;
                        if (string.IsNullOrWhiteSpace(where))
                            where = string.Format(" where m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", ranged.Debut, ranged.Fin);
                        else
                            where += string.Format(" and m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", ranged.Debut, ranged.Fin);
                        break;
                    case FiltreDate.EntreMois:
                        var rangem = (RangeDate)date;
                        if (string.IsNullOrWhiteSpace(where))
                            where = string.Format(" where m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", rangem.Debut.Value.FirstDayOfMonth(), rangem.Fin.Value.LastDayOfMonth());
                        else
                            where += string.Format(" and m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", rangem.Debut.Value.FirstDayOfMonth(), rangem.Fin.Value.LastDayOfMonth());
                        break;
                    case FiltreDate.Jour:
                        if (string.IsNullOrWhiteSpace(where))
                            where = string.Format(" where m.mvt_date = '{0:yyyy-MM-dd}'", (DateTime)date);
                        else
                            where += string.Format(" and m.mvt_date = '{0:yyyy-MM-dd}'", (DateTime)date);
                        break;
                    case FiltreDate.Mois:
                        var rangems = new RangeDate() { Debut = ((DateTime)date).FirstDayOfMonth(), Fin = ((DateTime)date).LastDayOfMonth() };
                        if (string.IsNullOrWhiteSpace(where))
                            where = string.Format(" where m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", rangems.Debut.Value.FirstDayOfMonth(), rangems.Fin.Value.LastDayOfMonth());
                        else
                            where += string.Format(" and m.mvt_date between '{0:yyyy-MM-dd}' and '{1:yyyy-MM-dd}'", rangems.Debut.Value.FirstDayOfMonth(), rangems.Fin.Value.LastDayOfMonth());
                        break;
                } 
                var order = "";
                cmd.CommandText += where;
                order="order by mvt_id, rang";
                cmd.CommandText += order;
                Connection.Open();
                var reader = cmd.ExecuteReader();
                var rest = new List<Brouillard>();
                while (reader.Read())
                {
                    var brouillard = new Brouillard();
                    brouillard.Date = reader.GetDateTime(0);
                    brouillard.Ref = reader.GetString(1);
                    brouillard.Motif = reader.GetString(2);
                    brouillard.MontantR = reader.GetInt64(3);
                    brouillard.MontantD = reader.GetInt64(4);
                    brouillard.SoldeC = reader.GetInt64(5);
                    rest.Add(brouillard);
                }
                Connection.Close();
                return rest;
            }
            catch (MySqlException e)
            {
                Console.WriteLine(e);
            }
            Connection.Close();
            return null;
        }

mais quand je l'execute il me donne cette erreur

NB: la requête marche parfaitement dans l’éditeur de code MySql.
J'utilise WPF, MySql.
quelqu'un pourrait-il m'aider SVP.

[al-faddi]

Merci j'ai trouvé la solution en passant par une procedure stockée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
public List<Brouillard> RechercheBrouillard2(FiltreDate filtreDate, object date)
        {
            try
            {
                var cmd = new MySqlCommand("pcd_cumul", Connection);
                cmd.CommandType = CommandType.StoredProcedure;
                if (FiltreDate.EntreMois != FiltreDate.Toutes) 
                { 
                var rangem = (RangeDate)date;
                cmd.Parameters.Add(new MySqlParameter("date1", MySqlDbType.DateTime));
                cmd.Parameters.Add(new MySqlParameter("date2", MySqlDbType.DateTime));
                cmd.Parameters["date1"].Value = rangem.Debut.Value.FirstDayOfMonth();
                cmd.Parameters["date2"].Value = rangem.Fin.Value.LastDayOfMonth();
                }
 
                Connection.Open();
                var reader = cmd.ExecuteReader();
                var rest = new List<Brouillard>();
                while (reader.Read())
                {
                    var brouillard = new Brouillard();
                    brouillard.Date = reader.GetDateTime(0);
                    brouillard.Ref = reader.GetString(1);
                    brouillard.Motif = reader.GetString(2);
                    brouillard.MontantR = reader.GetInt64(3);
                    brouillard.MontantD = reader.GetInt64(4);
                    brouillard.SoldeC = reader.GetInt64(5);
                    rest.Add(brouillard);
                }
                Connection.Close();
                return rest;
            }
            catch (MySqlException e)
            {
                Console.WriteLine(e);
            }
            Connection.Close();
            return null;
        }