Json ใน SQL Server 2017 และสร้าง Class ใน C#

Json ใช้ในระบบของ Javascript และ การติดต่อ ระหว่าง Cloud ระหว่างหลายภาษา เช่น python, java, dotnet. SQL Server 2017 มี Feature นี้ ด้วย

ก่อนอื่นต้อง Upgrade database ก่อน
ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 130

ทดลอง Data ที่เป็น JSON

DECLARE @JSONData AS NVARCHAR(4000)
SET @JSONData = N'{
    "EmployeeInfo":{
        "FirstName":"Somchai",
        "LastName":"Saema",
        "Code":"CODED",
        "Addresses":[
            { "Address":"1234", "City":"Bangkok", "State":THAILAND"},
            { "Address":"5678", "City":"Nonthaburi", "State":"THAILAND"}
        ]
    }
}'

ตัวเช็ก ว่าเป็น Json หรือไม่
select isjson(@jsondata)
อ่านค่า
select json_value(@jsondata,'$.EmployeeInfo.Addresses[1].Address')

ดูว่าใช่หรือไม่
select isjson(@jsondata)
ดูค่า
select json_value(@jsondata,'$.EmployeeInfo.Addresses[1].Address')
select JSON_query(@jsondata,'$.EmployeeInfo.Addresses')
แก้
SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', 'Name'')
select json_value(@jsondata,'$.EmployeeInfo.FirstName')
เพิ่ม
SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.MiddleName', 'NNK')
select JSON_value(@jsondata,'$.EmployeeInfo.MiddleName')

select * from openjson(@jsondata)
Select JSON_query(@jsondata,'$.EmployeeInfo.Addresses')
แก้ ค่า
SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', 'Rakesh')
select json_value(@jsondata,'$.EmployeeInfo.FirstName')
เพิ่ม field
SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.MiddleName', 'MAI')
select JSON_value(@jsondata,'$.EmployeeInfo.MiddleName')

select * from openjson(@jsondata)

การ Update จะมา เป็น JSON ต้อง แตกออก โดยใช้ OPENJSON

DECLARE @v AS NVARCHAR(4000)

set @v='{"UserID":7,"UserName":"Somsak","RegDate":"2017-01-23T00:00:00"}'

ถ้า select * from OPENJSON(@v)
จะได้
 key .  value . type
UserId     7    2
UserName Somsak 1
RegDate 2017-01-23T00:00:00 1

บาง ค่า เราใช้ Where ได้เลย

select * from OPENJSON(@v) with ( UserID int '$.UserID', UserName varchar(50) '$.UserName', RegDate datetime '$.RegDate')

ผล
UserId UserName  RegDate
7 Somsak 2017-01-23 00:00:00.000


แล้วจึงใช้ Insert หรือ Update ได้ แทน @v ลงใน $$JSON$$

    string cmdtext = "INSERT INTO [temp_Users] ([UserID],[UserName],[RegDate])"+

                "Select UserID,UserName,RegDate from OPENJSON('$$JSON$$')with ( UserID int '$.UserID', UserName varchar(50) '$.UserName', RegDate datetime '$.RegDate')";

        

ในกรณีที่ได้ ผลจาก query มาแล้วต้องการเขียนเป็นโปรแกรมแบบ Class (no sql) ก็ทำได้โดยเข้า Web
json2charp.com และ เลือก Quicktype (ใน Visual Studio ต้องลง Nuget ของ Newtonsoft ก่อน)

เช่น ผลจาก Query
[{"UserID":1,"UserName":"Phar","RegDate":"2017-01-23T00:00:00"},{"UserID":2,"UserName":"Som"},{"UserID":3,"UserName":"Cho"}]



Generate จาก json2charp.com จะได้ Class .ให้ไปเปลี่ยน Welcome  Class เอา

namespace QuickType
{
    using System;
    using System.Collections.Generic;

    using System.Globalization;
    using Newtonsoft.Json;
    using Newtonsoft.Json.Converters;

    public partial class Welcome
    {
        [JsonProperty("UserID")]
        public long UserId { get; set; }

        [JsonProperty("UserName")]
        public string UserName { get; set; }

        [JsonProperty("RegDate", NullValueHandling = NullValueHandling.Ignore)]
        public DateTimeOffset? RegDate { get; set; }
    }

    public partial class Welcome
    {
        public static Welcome[] FromJson(string json) => JsonConvert.DeserializeObject<Welcome[]>(json, QuickType.Converter.Settings);
    }

    public static class Serialize
    {
        public static string ToJson(this Welcome[] self) => JsonConvert.SerializeObject(self, QuickType.Converter.Settings);
    }

    internal static class Converter
    {
        public static readonly JsonSerializerSettings Settings = new JsonSerializerSettings
        {
            MetadataPropertyHandling = MetadataPropertyHandling.Ignore,
            DateParseHandling = DateParseHandling.None,
            Converters =
            {
                new IsoDateTimeConverter { DateTimeStyles = DateTimeStyles.AssumeUniversal }
            },
        };
    }
}

เวลาใช้ใน SQL  c# จะเป็นแบบนี้

 string connString = @"Data Source=127.0.0.1\sql_server_demo;User ID=sa;Password=???;Initial Catalog=MyDb;Integrated Security=";

           

            string cmdtext = "SELECT *  FROM [temp_Users] FOR JSON AUTO";

            using (SqlConnection conn = new SqlConnection(connString))

            {

                conn.Open();

                using (SqlCommand cmd = new SqlCommand(cmdtext, conn))

                {

                    

                   SqlDataReader rdr= cmd.ExecuteReader();

                    // Get the values

                    while (rdr.Read())

                    {

                        //listBox1.Items.Add(rdr[0]);

                        textBox1.Text =rdr[0].ToString();

                      QuickType.Welcome[] datas=  QuickType.Welcome.FromJson(textBox1.Text);

                        string username = datas[0].UserName;

                    }

                }

            }

        }

ลดเวลาในการเขียนและ Validate โปรแกรมได้มาก