دریافت داده های JSON از کوئری های SQL Server

پرووید

دسته های مقالات

قابلیت های جدید SQL Server 2016

SQL Server 2016 قابلیت جدید برای دریافت داده های یک جدول در قالب JSON را دارد. با توجه به این موضوع که امروزه بسیار از وب سرویس ها با داده های JSON کار می کنند، وجود چنین قابلیتی می تواند بسیاری از Action Method هایی که درون Controller ها تعریف شده و کلاینت ها توسط AJAX به آنها درخواست می دهند را ساده کند. در واقع، Action Method ها می توانند در قالب Wrapper هایی ساده برای کوئری های SQL ایفای نقش کنند. از دیگر قابلیت های SQL Server 2016 می توان به temporal table ها اشاره کرد که با یاری خدا در مقالات بعدی به آن اشاره می کنیم.

پشتیبانی از JSON در SQL Server 2016 از کلیدواژه ها و توابعی تشکیل شده است که می توانید از آنها در کوئری ها استفاده کنید. علی رغم روشی که در کار با XML در SQL Server وجود دارد، برای کار با JSON، نوع داده ای خاصی (مثلاً JSON) وجود ندارد. برای استفاده از JSON، کافی است که داده های خود را به در ستون های با نوع داده ای VarChar(Max) ذخیره کنید. نکته ی جالب همین است که در صورت عدم وجود نوع داده ای JSON، قابلیت کوئری گرفتن از داده های JSON وجود دارد. علاوه بر این، ذخیره کردن داده های JSON در سطرهای SQL Server نیز قابلیت جالبی است که در یک مقاله ی دیگر به آن اشاره خواهیم کرد.

نکات مهم

قبل از اینکه بحث مربوط به این قابلیت جذاب را مطرح کنیم، سه نکته ی اساسی و مهم را باید در نظر بگیرید:

  • در حال حاضر در Entity Framework و LINQ قابلیتی برای کار کردن با JSON و TSQL وجود ندارد. برای دسترسی و کار کردن با JSON در دات نت باید با دستورات ساده و قدیمی ADO.NET کار کنید. البته، این موضوع که Entity Framework Core که از آپشن های پیکربندی بیشتری برخوردار است، بتواند روزی از این قابلیت برخوردار باشید دور از توقع نیست.
  • بدون LINQ، استفاده از IQueryable به عنوان نوع بازگشتی یک Action Method کار بیهوده ای است. چرا که کار با OData برای کاربر در چنین شرایطی قابل انجام نیست.
  • اگر از ASP.NET Web API و نوع داده ای JSON در SQL Server استفاده می کنید، دیگر نمی توانید از Content Negotiation استفاده کنید. به عبارت دیگر، متدهای شما همیشه نوع داده ای JSON را به عنوان خروجی برخواهند گشت.

دریافت داده های JSON از SQL Server

کد زیر Action Method ی را نشان می دهد که با استفاده از دستورات ساده ی ADO.NET که قالب TSQL یک Command را به سمت سرور فرستاده و داده ها در قالب JSON دریافت می کنند. کلمه ی کلیدی FOR JSON AUTO در پایان دستورات باعث می شود که داده ها به صورت JSON برگرداننده شوند.

 

Public Function GetJson() As String
Dim cn As SqlConnection
Dim cmd As SqlCommand
Dim rdr As SqlDataReader
Dim res As String = String.Empty

cn = New SqlConnection("…connection string…")
cmd = cn.CreateCommand()
cmd.CommandText = "Select * " &
" From Customers " &
"FOR JSON AUTO;"
cn.Open()
rdr = cmd.ExecuteReader()
rdr.Read()
res = rdr(0)
cn.Close()

Return res
End Function

با فرض اینکه جدول Customers حاوی دو سطر باشد، دستورات بالا داده هایی شبیه به زیر را بر می گردانند.

 

[{"Id":1,"FirstName":"Peter","LastName":"Vogel","BirthDate":"1953-05-31"},
{"Id":2,"FirstName":"Jan","LastName":"Vogel","BirthDate":"1955-04-08"}]

برای دریافت داده ها به صورت تکی (بدون دریافت در قالب یک آرایه) کافی است از کلمه ی کلیدی Without_Array_Wrapper مانند کد زیر استفاده کنید:

 

Select *
From Customers
Where Id = 1
FOR JSON AUTO, Without_Array_Wrapper;

خروجی کد بالا چیزی شبیه به زیر خواهد بود:

 

"{"Id":1,"FirstName":"Peter","LastName":"Vogel","BirthDate":"1953-05-31"}"

دقت کنید که اگر از کلمه ی کلیدی Without_Array_Wrapper استفاده کردید ولی داده های مربوط به بیش از یک سطر را دریافت کردید (مثلاً شرط Where Id = 1

را حذف کنید.)، هیچ خطایی ایجاد نمی شود ولی داده های دریافتی ممکن است برای هیچ کلاینتی معنا نداشته باشند.

می توانید برای داده های دریافتی یک نام در نظر بگیرید. برای مثال، کد زیر اطلاعات دریافتی را در یک Object به اسم Customers قرار داده است. این کار با استفاده از کلمه ی کلیدی Root انجام می شود.

 

Select *
From Customers
Where Id = 1
FOR JSON AUTO, Root('Customers')

نتیجه ی دستور بالا چیزی شبیه به داده های زیر خواهد بود:

 

"{"Customers":[
{"Id":1,"FirstName":"Peter","LastName":"Vogel","BirthDate":"1953-05-31"},
{"Id":2,"FirstName":","LastName":"Vogel","BirthDate":"1955-04-08"}
]
}"

فرمت بندی کردن ستون ها

نکته ی مهمی که باید به آن دقت کرد این است که با ستون های خالی که حاوی NULL هستند به چه صورت رفتار خواهد شد. در مثال زیر، ستون FirstName حاوی یک رشته به طول صفر می باشد، بنابراین، ستون متناظر به آن در داده های دریافتی قرار داده شده است.

 

[{"Id":2,"FirstName":","LastName":"Vogel","BirthDate":"1955-04-08"}]

اما در مثال زیر، به دلیل اینکه ستون FirstName حاوی NULL می باشد، در داده های دریافتی قرار نخواهد گرفت.

 

[{"Id":3,"LastName":"van de Velde","BirthDate":"1981-12-23"}]

اگر قصد دارید یک ستون، علی رغم اینکه حاوی NULL است در نتایج ظاهر شود، می بایست از کلید واژه ی Include_Null_Values شبیه کد زیر استفاده کنید:

 

Select *
From Customers
FOR JSON AUTO, Include_Null_Values

با استفاده از این گزینه، ستونی که حاوی NULL می باشد در نتیجه ی کوئری حضور خواهد داشت اما رشته ی null برای آن در نظر گرفته می شود.

 

[{"Id":3,"FirstName":null,"LastName":"van de Velde","BirthDate":"1981-12-23"}]

نام گذاری و تو در تویی نتایج

می توانید با در نظر گرفتن نام های مستعار (Alias) برای ستون های داده های JSON نام های متفاوتی در نظر بگیرید:

Select FirstName As FName
From Customers
Where Id = 1
FOR JSON AUTO

نتیجه ی دستور بالا به صورت زیر خواهد بود:

“[{“”FName””:””Peter””}]”

با استفاده از کلمه ی کلیدی FOR JSON AUTO می توانید جداول مرتبط را با هم ترکیب کرده و نتیجه را در قالب اشیای تو در تو (Nested Objects) دریافت کنید. کد زیر دو جدول Customer و SalesOrder را با هم ترکیب می کند.

Select *
From Customers As C JOIN
SalesOrders As S
On C.Id = S.CustId
Where C.Id = 1
FOR JSON AUTO

نتیجه ی دستور بالا داده زیر است که در آن دو شی SalesOrder در درون شی Customer قرار گرفته است. دقت کنید که شی SalesOrder توسط نام مستعار S در دستور SQL مشخص شده است:

“[{“”Id””:1,””FirstName””:””Peter””,””LastName””:””Vogel””,””BirthDate””:””1953-05-31″”,
“”S””:[
{“”Id””:1,””OrderDate””:””2017-10-12″”,””CustId””:””1″”},
{“”Id””:2,””OrderDate””:””2017-09-09″”,””CustId””:””1″”}
]
}
]”

همانطور که می بینید، در نظر گرفتن نام های مستعار معنادار برای جداول می تواند خوانایی را افزایش دهد.

نکته ی آخر اینکه می توانید با استفاده از کلمه ی کلیدی FOR JSON PATH به جای FOR JSON AUTO اشیا تو در تو را در نتیجه ی دریافتی اجبار کنید. با استفاده از این روش، ستون هایی که نام مستعار یکسان دارند در درون اشیا JSON گروه بندی می شوند. دستور زیر با در نظر گرفتن نام های مستعار Name.First و Name.Last برای ستون های FirstName و LastName داده های JSON را به صورت تو در تو ایجاد می کنیم. نکته ی بعدی این که در استفاده از این روش می توانید نام های مستعار را هم درون تک کوتیشین و هم براکت قرار دهید. در مثال زیر، هر دوی این دو مورد را مشاهده می کنید.

Select id, FirstName as [Name.First], ‘Name.Last’
From Customers
Where Id = 1
FOR JSON PATH

نتیجه ی دستور بالا، داده های زیر است:

“[{“”id””:1,
“”Name””:{
“”First””:””Peter””,
“”Last””:””Vogel””
}
}]”

امیدواریم که از این مقاله ی آموزشی از وبسایت پرووید استفاده ی مفید کرده باشید.

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *