C# - Storing JSON into MySQL Database [duplicate]

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
– Charlieface, Commented Feb 13, 2021 at 22:51

Fabio · Accepted Answer · 2021-02-13 23:50:01Z

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, 2021 at 23:50

answered Feb 13, 2021 at 22:52

Fabio

32.5k4 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Charlieface Over a year ago

MySqlParameter and you can just do .Add twice or use collection initializer, it's much simpler

SunAwtCanvas Over a year ago

Fabio, What's SqlType? Do i have to Using some library for that?

Fabio Over a year ago

@Denise, this is MySqlDbType you don't need to add any other libraries.

Collectives™ on Stack Overflow

C# - Storing JSON into MySQL Database [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related