c# - How to store json data into MySQL

Question

I have this JSON data on my REST API and I want to store it in my MySQL server

JSON Data

[
    {
        "res_order_num": "1",
        "menu_code": "1",
        "menu_quantity": "2",
        "menu_total_price": "60",
        "res_no": "1",
        "menu_name": "Adobo Manok"
    },
    {
        "res_order_num": "2",
        "menu_code": "5",
        "menu_quantity": "2",
        "menu_total_price": "90",
        "res_no": "1",
        "menu_name": "Pritong Bangus"
    }
]

So how do I store it in my MySQL.

Is you have any error while saving data? or do you have any code done so far? — er-sho
– er-sho, Commented Aug 4, 2018 at 5:59
I have this code on my windows form c# HttpClient client = new HttpClient(); var response = await client.GetStringAsync("example/Restserver/index.php/users/view"); var persons = JsonConvert.DeserializeObject<List<Member>>(response); load_1 = persons.Count; dataGridMember.DataSource = persons; — ropenrom24
– ropenrom24, Commented Aug 4, 2018 at 6:05
@ropenrom24, the above code you shown is for fetching data from api and display in datagridview ? Is you have to fetch json data from api or give it to api to database save ? — er-sho
– er-sho, Commented Aug 4, 2018 at 6:09
Yes Sir, it fetch json data and display it in DataGridview. What want to know is how to store it directly on my local database instead of displaying on datagridview — ropenrom24
– ropenrom24, Commented Aug 4, 2018 at 6:12

Hossein · Accepted Answer · 2018-08-04 06:15:11Z

4

Here the solution:

var persons = JsonConvert.DeserializeObject<List<Member>>(response);

now you can loop through items like below.

foreach(Member person in persons )
{
    // add to db your each person
}

and here how you can insert to db : for ADO.Net : https://msdn.microsoft.com/en-us/library/ms233812.aspx

for EntityFramework : https://msdn.microsoft.com/en-us/library/ee712907(v=vs.113).aspx

edited Aug 4, 2018 at 6:15

answered Aug 4, 2018 at 6:11

Hossein

3,1233 gold badges18 silver badges35 bronze badges

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

3 Comments

er-sho Over a year ago

,if json have 1000 persons then foreach iterate 1000 times and 1000 times database call so this require too much execution time and database trip

Hossein Over a year ago

@ershoaib you can open connection and add you 1000 record then close in Ado.Net and in EF just after foreach u call SaveChanges();.

er-sho Over a year ago

No no this takes 1000 round trip to database, i think it good to use bulk insert

er-sho · Accepted Answer · 2018-08-04 06:55:15Z

If your json contains from 0.....N number of persons then BatchUpdate is good solution for you this may reduce your database round trip if you have performance requirement

The following method contains sample of BatchUpdate

public void BatchUpdateToMySqlServer()
{
    var persons = JsonConvert.DeserializeObject<List<Member>>(response);

    DataTable dt = new DataTable();
    dt.Columns.Add(new DataColumn("res_order_num", typeof(System.String)));
    dt.Columns.Add(new DataColumn("menu_code", typeof(System.String)));
    dt.Columns.Add(new DataColumn("menu_quantity", typeof(System.String)));
    dt.Columns.Add(new DataColumn("menu_total_price", typeof(System.String)));
    dt.Columns.Add(new DataColumn("res_no", typeof(System.String)));
    dt.Columns.Add(new DataColumn("menu_name", typeof(System.String)));

    foreach (var item in persons)
    {
        DataRow dr = dt.NewRow();
        dr["res_order_num"] = item.res_order_num;
        dr["menu_code"] = item.menu_code;
        dr["menu_quantity"] = item.menu_quantity;
        dr["menu_total_price"] = item.menu_total_price;
        dr["res_no"] = item.res_no;
        dr["menu_name"] = item.menu_name;
        dt.Rows.Add(dr);
    }


    MySqlConnection con = new MySqlConnection("Your Connection String");
    if (con.State == ConnectionState.Open)
    {
        con.Close();
    }
    con.Open();
    MySqlCommand cmd = new MySqlCommand("Your Insert Command", con);
    cmd.CommandType = CommandType.Text;

    cmd.UpdatedRowSource = UpdateRowSource.None;

    MySqlDataAdapter da = new MySqlDataAdapter();
    da.InsertCommand = cmd;
    da.UpdateBatchSize = 100000; //If you json contains 100000 persons object;
    int records = da.Update(dt);
    con.Close();
}

Here is good article if you have to use StoedProcedure as insert command.

Corey Johnson · Accepted Answer · 2018-08-04 06:06:50Z

0

Without additional information; it doesn't appear you have much existing code. If you're trying to get started with saving data to a MySQL database in c#; Microsoft provides this tutorial. However there's not much people can do to answer this question without additional info.

Tutorial: https://msdn.microsoft.com/en-us/library/0f92s97z.aspx

People might be able to give better answers if you include the following:

Do you have existing code in place that reads or writes to MySQL?
Do you have a MySQL database set up?
Does your database have tables that match the data represented in the JSON?

answered Aug 4, 2018 at 6:06

Corey Johnson

1146 bronze badges

1 Comment

ropenrom24 Over a year ago

Yes sir I have a MySQL database set up on localhost and the database tables matches the JSON data.

Collectives™ on Stack Overflow

c# - How to store json data into MySQL

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related