如何使用C#将数据库中的所有表的所有记录导出到json中 [英] How can I export all table all records in a database into json using C#

问题描述

我正在为一家超市创建ERP工具.所有者在两个不同的地方都有两个超市.因此，要管理超市，应将本地数据库(mySQL)同步到Web服务器. 当前，我正在使用以下C#代码通过使用列added_on和last_updated过滤记录来从数据库中导出表(sales_products)的所有记录.我的数据库包含20多个表和更多记录.

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);
        }
}

我的问题是:

如何使用C#将所有记录从all tables导出到json文件?

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

推荐答案

JSON仅具有有限数量的数据类型(字符串，浮点数，布尔值，null)；通过将MySQL数据导出为JSON可能会失去精度(因为DATETIME，TIMESTAMP，GUID，BLOB等必须转换为字符串).

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).

但是，如果您仍然想将数据库导出为JSON，首先需要找到数据库中的所有表(通过查询information_schema.tables表)，然后遍历每个表，选择所有行并将其转储到JSON.因为这可能是大量数据，所以要避免用完内存，您需要将结果流式传输到输出文件(而不是在内存中创建大量对象，然后将它们转换为JSON).这要求使用低级JSON编写API，因此您需要确保WriteStartObject和WriteEndObject调用正确配对以创建有效的JSON.

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();
    }
}

这篇关于如何使用C#将数据库中的所有表的所有记录导出到json中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！