C# - Storing JSON into MySQL Database

Question

I'm working with ASP NET

Okay so I have this JSON which i stringified through JSON.stringify it looks like this :

{"mean":[296,28.166036637951827,1003],"mode":[305,28.3565417425884,1007],"min":[117,20.5116820888701,991],"max":[322,29.7130352284327,1014],"range":[205,9.201353139562602,23],"variance":[2041.708,3.56,45.257],"std":[45.185,1.887,6.727],"allReadings":[["289","28.2724154175823","1003","2021-02-13T20:04:46.000Z"],["305","28.2724154175823","1014","2021-02-13T20:04:56.000Z"],["294","28.2724154175823","991","2021-02-13T20:05:06.000Z"],["304","28.3565417425884","1007","2021-02-13T20:05:16.000Z"],["305","28.3565417425884","996","2021-02-13T20:05:26.000Z"],["309","28.3565417425884","995","2021-02-13T20:05:37.000Z"],["322","28.3565417425884","1012","2021-02-13T20:05:47.000Z"],["301","28.3565417425884","995","2021-02-13T20:05:57.000Z"],["307","28.3565417425884","1007","2021-02-13T20:06:07.000Z"],["117","28.5250147637195","999","2021-02-13T20:06:17.000Z"],["273","20.5116820888701","1003","2021-02-13T20:06:28.000Z"],["314","28.8628633488818","1007","2021-02-13T20:06:38.000Z"],["296","28.7782864935014","1011","2021-02-13T20:06:48.000Z"],["320","28.6937866171596","1007","2021-02-13T20:06:58.000Z"],["319","28.6093629596379","1005","2021-02-13T20:07:08.000Z"],["307","29.7130352284327","1010","2021-02-13T20:07:19.000Z"],["312","28.947517946563","1007","2021-02-13T20:07:29.000Z"],["321","28.8628633488818","994","2021-02-13T20:07:39.000Z"],["318","28.6937866171596","1005","2021-02-13T20:07:49.000Z"]]}

I'm trying to save this into an MySQL DB like so using this C# Code:

report = the JSON

dt = DateTime.Now.ToString()

public static void saveReport(string report, string dt)
        {
            string sql = $"INSERT INTO reports (report, date), VALUES ('{report}', '{dt}')";
            MySqlConnection con = new MySqlConnection(smartseed_con);
            con.Open();
            MySqlCommand cmd = new MySqlCommand(sql, con);
            cmd.ExecuteNonQuery();
            con.Close();
        }

However, It throws me this error.

This is how my table is designed

I'm in dire need of some help :( Could someone suggest a fix or a better way? I even tried doing .Replace('"', '*') in C# which doesn't work. The goal was to replace that " character with another one that doesn't cause this issue . :/

Use parameters `cmd.Parameters.Add(...` And dispose your connection and command objects with `using` — Charlieface, Feb 13 '21 at 22:51

Fabio · Answer 1 · 2021-02-13T23:50:01.870

1

Use SqlParameter to store values. Value in the sql parameter will be treated as value and will not be "parsed" as part of the query.

using (var connection = new MySqlConnection("connectionString"))
using (var command = connection.CreateCommand())
{
   var sql = $"INSERT INTO reports (report, date), VALUES (@report, @dt)";
   var parameters = new[]
   {
     new MySqlParameter { ParameterName = "@report", MySqlDbType = MySqlDbType.Varchar, Value = report },
     new MySqlParameter { ParameterName = "@dt", MySqlDbType = MySqlDbType.Varchar, Value = dt },
   };

   command.CommandText = sql;
   command.Parameters.AddRange(parameters);

   connection.Open();
   command.ExecuteNonQuery();
}

Note:
Do not store dates as a VARCHAR, MySql supports Date and Time types.

edited Feb 13 '21 at 23:50

answered Feb 13 '21 at 22:52

Fabio

31,528
4
33
72

`MySqlParameter` and you can just do `.Add` twice or use collection initializer, it's much simpler – Charlieface Feb 13 '21 at 22:53
Fabio, What's SqlType? Do i have to `Using` some library for that? – SunAwtCanvas Feb 13 '21 at 23:01
@Denise, this is `MySqlDbType` you don't need to add any other libraries. – Fabio Feb 13 '21 at 23:50

C# - Storing JSON into MySQL Database

1 Answers1