· 6 years ago · Jun 16, 2019, 06:18 PM
1DROP TABLE IF EXISTS coordinate;
2CREATE TABLE coordinate
3(
4 id int(10) NOT NULL auto_increment,
5 longitude double(8,4) NOT NULL,
6 latitude double(8,4) NOT NULL,
7 measurementId int(10) default NULL,
8 PRIMARY KEY (id),
9 FOREIGN KEY (measurementId)
10 REFERENCES measurement (id)
11 ON DELETE CASCADE
12);
13
14public class Coordinate
15{
16 public Coordinate()
17 {
18
19 }
20
21 public int ID { get; set; }
22
23 public double Latitude { get; set; }
24
25 public double Longitude { get; set; }
26
27 public int MeasurementId { get; set; }
28
29 ...
30}
31
32sql = "UPDATE coordinate SET longitude = '" + coordinate.Longitude + "," + " latitude = " + coordinate.Latitude + "," + " measurementId = " + coordinate.MeasurementId + "' WHERE id = " + id.ToString();
33
34sql = "UPDATE coordinate SET longitude = '55,6, latitude = 70,33, measurementId = 2' WHERE id = 2'
35
36{
37 "Message": "An error has occurred.",
38 "ExceptionMessage": "Data truncated for column 'longitude' at row 1",
39 "ExceptionType": "MySql.Data.MySqlClient.MySqlException",
40 ...
41}
42
43sql = "UPDATE coordinate SET longitude = " + coordinate.Longitude + "," + " latitude = " + coordinate.Latitude + "," + " measurementId = " + coordinate.MeasurementId + " WHERE id = " + id.ToString();
44
45sql = "UPDATE coordinate SET longitude 55,6 latitude = 70,33, measurementId = 2 WHERE id = 2"
46
47{
48 "Message": "An error has occurred.",
49 "ExceptionMessage": "You have an error in your SQL syntax; check the manual
50 that corresponds to your MySQL server version for the right syntax to use
51 near '6, latitude = 70,33, measurementId = 2 WHERE id = 2' at line 1",
52}
53
54string sql = "UPDATE coordinate SET longitude = @Longitude, latitude = @Latitude, measurementId = @MeasurementId WHERE id = @ID";
55
56 List<MySqlParameter> parameters = SqlFactory.CreateParametersFor(coordinate);
57
58 cmd = SqlFactory.CreateParameterizedQuery(sql, parameters, conn);
59
60 cmd.Parameters.Add(ID);
61
62 await cmd.ExecuteNonQueryAsync();
63
64public static MySqlCommand CreateParameterizedQuery(string sql, List<MySqlParameter> parameters, MySqlConnection connection)
65 {
66 MySqlCommand cmd = new MySqlCommand(sql, connection);
67 foreach (MySqlParameter parameter in parameters) cmd.Parameters.Add(parameter);
68 return cmd;
69 }
70
71 public static List<MySqlParameter> CreateParametersFor(Coordinate coordinate)
72 {
73 List<MySqlParameter> parameters = new List<MySqlParameter>();
74
75 MySqlParameter latitude = new MySqlParameter()
76 {
77 ParameterName = "@Latitude",
78 Value = coordinate.Latitude
79 };
80
81 MySqlParameter longitude = new MySqlParameter()
82 {
83 ParameterName = "@Longitude",
84 Value = coordinate.Longitude
85 };
86
87 MySqlParameter measurementId = new MySqlParameter()
88 {
89 ParameterName = "@MeasurementId",
90 Value = coordinate.MeasurementId
91 };
92
93 parameters.Add(latitude);
94 parameters.Add(longitude);
95 parameters.Add(measurementId);
96
97 return parameters;
98 }
99
100sql = "UPDATE coordinate SET longitude = '" + coordinate.Longitude + "', latitude = '" + coordinate.Latitude + "', measurementId = '" + coordinate.MeasurementId + "' WHERE id = " + id.ToString();
101
102using System.Globalization;
103public class Coordinate
104{
105 NumberFormatInfo nfi = new NumberFormatInfo();
106
107 public Coordinate()
108 {
109 nfi.NumberDecimalSeparator = ".";
110 }
111
112 public int ID { get; set; }
113
114 public double Latitude { get; set; }
115
116 public double Longitude { get; set; }
117
118 public int MeasurementId { get; set; }
119
120 public string GetLatitudeComma()
121 {
122 return this.Latitude.ToString(nfi);
123 }
124
125 public string GetLongitudeComma()
126 {
127 return this.Longitude.ToString(nfi);
128 }
129}
130
131sql = "UPDATE coordinate SET longitude = '" + coordinate.GetLongitudeComma() + "," + " latitude = " + coordinate.GetLatitudeComma()+ "," + " measurementId = " + coordinate.MeasurementId + "' WHERE id = " + id.ToString();