C#从数据集或数据表中使用Json.NET自定义Json

Question

有人能帮我用C#从数据表中获取自定义JSON字符串吗。

我需要下面这样的东西。我可以通过对每个循环使用来实现这一点。

[
    {"message": {"alert": "Address Updated"},"target": {"userIds": ["BKAC7759"]}},
    {"message": {"alert": "Payment Processed"},"target": {"userIds": ["BKAC7759"]}},
    {"message": {"alert": "Notice Sent"},"target": {"userIds": ["BKAC7759"]}}
]

但有没有什么方法可以让我用简单的方式做到。我的数据表包含"alert"one_answers"userIds"的值

    private string GetJsonData(int numberofRecords)
    {
        //  OleDbConnection conn = new OleDbConnection(connectionString);
        try
        {
            DataTable Test = new DataTable("A");
            Test.Columns.Add("alert");
            Test.Columns.Add("userIds");
            Test.Rows.Add("Address Updated", "BKAC7759");
            Test.Rows.Add("Payment Made", "BKAC7759");
            //Test.Rows.Add("Check Processed", "MAND1884");
            //Test.Rows.Add("Notice Mailed", "JAID3869");
            //Test.Rows.Add("DL Suspended", "AOQU4798");
            string jo = string.Empty;
            string com = ",";
            int i = 0;
            int count = Test.Rows.Count;
            string bracketright = "]";
            string bracketleft = "[";
            foreach (DataRow row in Test.Rows)
            {
                if (i == 0)
                {
                    jo = jo + bracketleft;
                }
                jo = jo + "{"message":{"alert":"" + row[0].ToString() + ""},"target":{"userIds":["" + row[1].ToString() + ""]}}";
                if (i != count - 1)
                {
                    jo = jo + com;
                }
                else
                {
                    jo = jo + bracketright;
                }
                i++;
            }
            return jo;
        }
        catch (Exception ex)
        {
            Logger.Error("GetJsonData(int numberofRecords): " + ex.Message);                
            return string.Empty;
        }
        finally
        {
            // always close the connection.
           // conn.Close();
        }                        
    }

Answer 1

您可以使用Linq+DataTableExtensions（在命名空间System.Data和系统DLL System.Data.DataSetExtensions.dll中）将表转换为匿名类型的枚举表，然后使用JSON.net将其序列化为JSON。

我注意到您的"userIds"属性是一个JSON数组。是否要组合给定警报的所有用户userId？如果是这样，您可以使用ToLookup来组合它们：

        var root = dataTable.AsEnumerable()
            .ToLookup(r => r["alert"].ToString(), r => r["userIds"].ToString())
            .Select(g => new { message = new { alert = g.Key }, target = new { userIds = g } });
        var json = JsonConvert.SerializeObject(root);

如果没有，则执行：

        var root = dataTable.AsEnumerable()
            .Select(r => new { message = new { alert = r["alert"].ToString() }, target = new { userIds = new [] { r["userIds"].ToString() } } });
        var json = JsonConvert.SerializeObject(root);

下表：

        var dataTable = new DataTable("A");
        dataTable.Columns.Add("alert");
        dataTable.Columns.Add("userIds");
        dataTable.Rows.Add("Address Updated", "BKAC7759");
        dataTable.Rows.Add("Payment Made", "BKAC7759");
        dataTable.Rows.Add("Address Updated", "MAND1884");
        dataTable.Rows.Add("Payment Made", "MAND1884");

第一个生成以下JSON:

[
    {"message":{"alert":"Address Updated"},"target":{"userIds":["BKAC7759","MAND1884"]}},
    {"message":{"alert":"Payment Made"},"target":{"userIds":["BKAC7759","MAND1884"]}}
]

第二个产生以下内容：

[
    {"message":{"alert":"Address Updated"},"target":{"userIds":["BKAC7759"]}},
    {"message":{"alert":"Payment Made"},"target":{"userIds":["BKAC7759"]}},
    {"message":{"alert":"Address Updated"},"target":{"userIds":["MAND1884"]}},
    {"message":{"alert":"Payment Made"},"target":{"userIds":["MAND1884"]}}
]