How can I export all table all records in a database into json using C#

Question

I am creating a ERP tool for a supermarket. The owner has two supermarkets in two different places. So, to manage the supermarket the local database (mySQL) should be synchronized to the web server. Currently I am using the following C# code to export all records of a table(sales_products) from my database by filtering the records using columns added_on and last_updated. My database contains more than 20 tables and more records.

private void button1_Click(object sender, EventArgs e)
{
        string json = string.Empty;
        List<object> objects = new List<object>();
        MySqlConnection _dbConnection = new MySqlConnection("Server = localhost; Database = app_erp_suneka; Uid = root; Pwd = ;");
        {
           _dbConnection.Open();// .Open();
           using (MySqlCommand command = _dbConnection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM sales_products";
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        IDictionary<string, object> record = new Dictionary<string, object>();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            record.Add(reader.GetName(i), reader[i]);
                        }
                        objects.Add(record);
                    }
                }
            }
        }
        json = JsonConvert.SerializeObject(objects);
        using (StreamWriter sw = new StreamWriter(File.Create(@"C:\Users\SAKTHY-PC\Desktop\path.json")))// "C:\\path\\file.json")))
        {
            sw.Write(json);
        }
}

My Question is:

How can I export all records to json file from all tables using C# ?

Possible duplicate of: https://stackoverflow.com/q/5083709/3991696 — vishwarajanand, Jun 18 '18 at 10:42
But my question is how can I export to json from all tables. — S.Sakthybaalan, Jun 18 '18 at 10:45
In your question, you are querying only from `sales_products` Quite confusing! Why don't you query for all tables in databases and then insert into a JSON array accordingly? — vishwarajanand, Jun 18 '18 at 10:47
Without creating entity objects and filling them, you're going to have to write something that creates the object hierarchy for you. — Lasse V. Karlsen, Jun 18 '18 at 10:47
Synchronization is not trivial, I think you will have significant challenges. Is it out of the question to use a single database or replication set as provided by Azure or AWS? — Crowcoder, Jun 18 '18 at 10:51
@ vishwarajanand: here I query from sales_products table. I want all table records to upload to the server. So the other branch can easily handle them. — S.Sakthybaalan, Jun 18 '18 at 10:53
Possible duplicate of [convert from SqlDataReader to JSON](https://stackoverflow.com/questions/5083709/convert-from-sqldatareader-to-json) — habib, Jun 18 '18 at 12:47

score 1 · Accepted Answer · answered Jun 20 '18 at 04:36

JSON only has a limited number of data types (string, floating-point number, Boolean, null); you may lose precision by exporting your MySQL data to JSON (because DATETIME, TIMESTAMP, GUID, BLOB, etc., will have to be converted to a string).

But if you still want to export a database to JSON, first you need to find all the tables in the database (by querying the information_schema.tables table), then iterate over each table, selecting all the rows and dumping them to JSON. Because this may be a lot of data, to avoid running out of memory you'll need to stream the results to your output file (instead of creating a large number of objects in memory then converting them to JSON). This requires using a low-level JSON writing API, so you need to ensure that WriteStartObject and WriteEndObject calls are paired correctly to create valid JSON.

The following program snippet demonstrates this technique:

using (var connection = new MySqlConnection("Server = localhost; Database = app_erp_suneka; Uid = root; Pwd = ;"))
{
    connection.Open();

    // get the names of all tables in the chosen database
    var tableNames = new List<string>();
    using (var command = new MySqlCommand(@"SELECT table_name FROM information_schema.tables where table_schema = @database", connection))
    {
        command.Parameters.AddWithValue("@database", "app_erp_suneka");
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
                tableNames.Add(reader.GetString(0));
        }
    }

    // open a JSON file for output; use the streaming JsonTextWriter interface to avoid high memory usage
    using (var streamWriter = new StreamWriter(@"C:\Temp\app_erp_suneka.json"))
    using (var jsonWriter = new JsonTextWriter(streamWriter) { Formatting = Newtonsoft.Json.Formatting.Indented, Indentation = 2, IndentChar = ' ' })
    {
        // one array to hold all tables
        jsonWriter.WriteStartArray();

        foreach (var tableName in tableNames)
        {
            // an object for each table
            jsonWriter.WriteStartObject();
            jsonWriter.WritePropertyName("tableName");
            jsonWriter.WriteValue(tableName);
            jsonWriter.WritePropertyName("rows");

            // an array for all the rows in the table
            jsonWriter.WriteStartArray();

            // select all the data from each table
            using (var command = new MySqlCommand($@"SELECT * FROM `{tableName}`", connection))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    // write each row as a JSON object
                    jsonWriter.WriteStartObject();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        jsonWriter.WritePropertyName(reader.GetName(i));
                        jsonWriter.WriteValue(reader.GetValue(i));
                    }
                    jsonWriter.WriteEndObject();
                }
            }

            jsonWriter.WriteEndArray();
            jsonWriter.WriteEndObject();
        }

        jsonWriter.WriteEndArray();
    }
}

Thank you bro. But I got the following error. `An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll Additional information: Table 'app_erp_suneka.{tablename}' doesn't exist` — S.Sakthybaalan, Jun 20 '18 at 04:50
And the json file may not be huge. Because I will filtered the records by using `added_on` and `last_updated` columns in each table in the database. for example `SELECT * FROM WHERE (last_updated <= @dtp_last_updated) AND (added_on <= @dtp_last_updated)` — S.Sakthybaalan, Jun 20 '18 at 05:00
@SAKTHY That string is using interpolation, which requires the leading `$` character. Without that, you will need to write the SQL as `"SELECT * FROM " + tableName + " WHERE ..."`. — Bradley Grainger, Jun 20 '18 at 05:14
@SAKTHY If this helped you, please accept the answer: https://stackoverflow.com/help/someone-answers — Bradley Grainger, Jun 20 '18 at 05:27
I am unable to validate the exported JSON. This is the exported JSON file : https://raw.githubusercontent.com/vbsaba1992/sample/master/newSample.json. But while validating is says a missing comma between `line 181` and `182` — S.Sakthybaalan, Jun 25 '18 at 17:05
@SAKTHY that output looks like it could only happen if `WriteEndArray` was called twice in a row, but I don't see how that could happen with the code I provided. Did you make any modifications to the code? — Bradley Grainger, Jun 25 '18 at 17:16
@SAKTHY The code looks fine; at this point I'm not sure what could be causing the (invalid JSON) output `} ] ] {` in your results. The code works fine for me here (with a different local database), and it seems to be running fine for all your other tables. — Bradley Grainger, Jun 25 '18 at 20:10
Thank you so much for your kind reply. I again export the Json, but I is valid. I also do not know what is happening. I think before I use the Json, I want to validate it. — S.Sakthybaalan, Jun 26 '18 at 10:06
@SAKTHY It's possible there's an error in how we're using JsonWriter. To try to work around this (if your tables aren't too large, and you have enough memory), you could buffer everything in memory first, then write it all out with `JsonSerializer`. This would be done by combining your original code with my code that loops over all tables in the database. — Bradley Grainger, Jun 26 '18 at 11:44

score 0 · Answer 2 · answered Jun 26 '18 at 12:51

0

@Bradley Grainger, Nice to hear. But I can not ensure, that all the time my local dB has least records to use JsonSerializer. In the function time(like New Year or Christmas...), there will be more transactions, so the dB will huge and huge.

answered Jun 26 '18 at 12:51

S.Sakthybaalan

499
6
20

How can I export all table all records in a database into json using C#

2 Answers2

Linked