Everywhere JSON so why not in SQL SERVER–New feature in SQL SERVER 2016

If you are a developer then surely you might have used JSON (JavaScript Object Notation) but, if not then don’t worry you might use sooner than later. JSON is kind of ecosystem which is most popular in the various area for exchanging the data. If you talk about charting solution, AJAX, Mobile services or any 3rd party integration then generally JSON is the first choice of the developers.

 

If you see nowadays most of the NOSQL database like Microsoft Azure Document DB, MONGODB etc. also using JSON ecosystem and some of them are based on JSON.

 

As it is such a popular growing system So, why not in SQL SERVER?

In SQL SERVER 2016 JSON introduced. This we can say a step or bridge between NON-relation database and relational database by Microsoft SQL SERVER

 

SQL Server 2016 providing following capabilities when you are using JSON

  1. Parse JSON by relation query
  2. Insert & update  JSON using query
  3. Store JSON in database

 

If you see it then conceptually it is similar to XML data type which you might use in SQL SERVER.

The good thing  in SQL SERVER 2016 for JSON there is no Native data type.  This will help in migration from any NOSQL to SQL SERVER.

 

SQL server provides bidirectional JSON formatting which you can utilize in a various way. Suppose data is coming from the external source in the JSON format then you can parse it and store in table structure (if required) in another case external source require data in JSON format while data in SQL SERVER in tabular format so both the purpose can easily solve with  SQL SERVER’s JSON feature.

 

Now, let’s jump directly to the practical to check JSON capabilities in SQL SERVER

 

1) FOR JSON AUTO

It is similar to  FOR XML AUTO.  It will return JSON object of selected column where column name is treated as a Key or in other words we can say it will format the query result in JSON.

 

JSON_Feature_Indiandotnet_1

when you run above command the result will be like as shown in below figure.

JSON_Feature_Indiandotnet_2

 

2) FOR JSON PATH: –

It’s exactly like JSON auto the only difference is instead of SQL SERVER we have full control over the format. JSON Auto take predefined column schema while with JSON path we can create a complex object.

For example, we are using AdventureWorks Sales order table and joining that with product table to get sub-node. If you see in below image we have added Root node as well. This root Node can be added in JSON auto as well if required.

JSON_Feature_Indiandotnet_3

 

Now, when you run the above query we can get complex JSON object as follows

JSON_Feature_Indiandotnet_4

3) IsJSON function:-

By the name, it is clear that this is a validating function.

To cross check whether the provided string is a valid JSON or not we can run ISJSON.

JSON_Feature_Indiandotnet_5

 

4) JSON_VALUE:-

  By the name, it is clear that if you want to get the value of the particular key of JSON then you can use this beautiful function which is JSON_VALUE.

JSON_Feature_Indiandotnet_6

5) OPENJSON function:-

This is a very beautiful function which you can use to parse external schema. Suppose, you got a JSON string from a mobile service which you will directly pass to SQL Sever and SQL SERVER stored procedure will do rest of the operation to parse it. The parsing and other operation can be easily handled by OPENJSON. The only tweak here that it required database compatibility level 130   which you need to do (if not compatible with level 130)

JSON_Feature_Indiandotnet_7

 

There are many other interesting things which we will cover later.

Please, provide your inputs.

RJ

FIFA world cup 2014 with Angular.js & ASP.NET – Part- I

Dear Friends,
Today FIFA world cup 2014 is going to start, So I just thought to make something which have colors of FIFA world cup 2014 with my learning.
In this post I am sharing how to create a simple Angular.Js page in ASP.NET which give you FIFA 2014 Group Information.
Step 1:- Add a new project of ASP.NET and add Angular.Js in the project you can download Angular.js from here.
https://angularjs.org/
Or you can add angular js from NUGET package also.

Json
Step 2:– Now add as JavaScript file. I have added myApp.js file in project
Now we need to create a JSON for groups as shown below
Step 3:- Add reference of Angular.js and myApp.js in your project,

body_ng_App
Step 4:- Now next step is to add an Angular module to add this we need to write following line

var app = angular.module(‘App’, []);

Step 5: Once Module is added now we need to add a controller in this controller we need to declare a variable and assign JSON object which we have declare earlier.

Fotballngcontroller

Step 6: OK cool now your myAPP.js work is done.

Now move to design view of page. In this firstly assign the module to page
Currently I have assign this module in body tag.
Note: all the Angular related property started here with ng-
So in body tag I have written following property as shown in fig

Step 7:– Once we have added the module now we assign controller to a div in which we need JSON data of FIFA world cup 2014, So it can be added using ng-controller as shown in below fig

Step 7:- Now if you see JSON data it has multiple group like GROUP A , GROUP B etc so we require ng-repeat attribute of Angular.js. Tthis ng-repeat will reference controller’s variable which we have assigned in myapp.js.
Now for each group data we will create a div which have team name, team code, and team flag in that group. For this we need to write following code.

angular_FootBall_Repeat_Group

The best thing here is to access the property or value you just need to write curly barces {} and within this the property which you want to access.

Now when you run it. It will look like something like below screen.

Group_Vision

In next post we will show Match schedule with Angular.Js in asp.net.

Enjoy FIFA 🙂
Regards
Rajat Jaiswal

Book information by ISBN number using ASP.NET from Google API

Dear Friends,
In last 2 posts we have talked about ISBNDB.com(https://indiandotnet.wordpress.com/2012/10/24/book-detail-by-isbn-number-using-asp-net/)

& Wordcat.org (https://indiandotnet.wordpress.com/2012/10/24/book-details-by-isbn-using-asp-net-from-worldcat-org/).
Now here I am talking about one more great service provider which Google. We can use Google API to fetch book detail using ISBN number.
To use Google API you have to create account and register for API.

In this example I am not using API, I am just trying to access the detail directly using URL.
Below URL is used for providing the detail of the book using ISBN

https://www.googleapis.com/books/v1/volumes?q=0123344+ISBN

So let us start to fetch detail

As the response of this URL in JSON so for change I have used JSON parser here I have used JSON.NET which you can download from codeplex site.

The design of the page is same one textbox for ISBN and one search button. On search button click I wrote following code.

‘Step 1:- google API url responsible for returning the book detail in JASON format
Private Const GOOGLEAPIURL As String = “https://www.googleapis.com/books/v1/volumes?q=”

If Me.txtISBN.Text <> String.Empty Then
Dim requestURL As String

‘Step 2:- Reformed the URL to target particular ISBN number
requestURL = GOOGLEAPIURL + Me.txtISBN.Text.Trim() + “+isbn”

‘Step 3: created Http webrequest for URL
Dim wr As HttpWebRequest = HttpWebRequest.Create(requestURL)
‘Step4 : get the response of web request in http web response object
Dim resp As HttpWebResponse = wr.GetResponse()

‘Step 5: passes the response stream in stream reader
Dim sreader As New StreamReader(resp.GetResponseStream())
‘Step 6: parsing the reader(which is in Jason format) using JASON.NET

Dim rss = JObject.Parse(sreader.ReadToEnd())

‘Step 7: if object find the fetch the detail

If rss Is Nothing = False AndAlso rss.Count > 0 Then
Me.lblBookName.Text = rss.Item(“items”)(0).Item(“volumeInfo”).Item(“title”).ToString()
Me.lblAuthor.Text = rss.Item(“items”)(0).Item(“volumeInfo”).Item(“authors”).ToString().Replace(“[“, “”).Replace(“]”, “”).ToString()
Me.lblPublisher.Text = rss.Item(“items”)(0).Item(“volumeInfo”).Item(“publisher”).ToString()
Else
Me.lblMessage.Text = “Sorry ISBN Number not found, Please try different number.”
End If
Else
Me.lblMessage.Text = “Please enter the ISBN number”
End If

So in this way we have accessed book information from Google API URL without using API key.
Thanks & best regards,
Rajat Jaiswal

AJAX Basic Part – III

Hi,

today we will do next  topic so lets begin

5. What is JSON ?
Ans: JSON is JavaScript Object Notation. It’s a light weight Data interchange format which is independent from language. Its easy to read & Write by human & easy to parse & genrate by language. The attributes is seprated with “,” (comma) and value of attributes are define after colon symbol(“:”) .
Ex:- {Data:[{“fname” : “RAJAT”, “Lname” : “JAISWAL” }]}
The above is JSON example in which there are two attributes fname, Lname and the are seprated by “,” (comma) symbol.
And there value is “RAJAT” & JAISWAL” which are define after “:” (colon) symbol.
I am attaching  project file just go through it.

One of the most important work here is to parse json

We do response with following format

 

 

 

 {“Bank”:[{“lngId” : “1”, “strCode” :”ICICI” ,”strBank” : “ICICI BANK” },{“lngId” : “2”, “strCode” :”HDFC” ,”strBank” : “HDFC BANK” }]}

 When we parse with json then here is the code for it.

  function myServerResponse() {
            if (myServerRequest.readyState == 4) {
                if (myServerRequest.status == 200) {
                    var tdata;
                    tdata = eval(‘(‘+ myServerRequest.responseText +’)’);
                    var intI;
                    intI = 0;
                    var strResult =”<table border=’1′ cellspacing=’4′ cellpadding=’5′><TR><TD>lngId</td><td>code</td><td>Bank</td></tr>”
                    for (intI = 0; intI <= tdata.bank.length – 1; intI++) {
                      strResult = strResult + “<tr><td>” + tdata.bank[intI].lngId + “</td><td>” + tdata.bank[intI].strCode + “</td><td>” + tdata.bank[intI].strBank + “</td></tr>”;
                    }
                   
                    strResult = strResult + “</table>”
                    document.getElementById(‘dvResult’).innerHTML = strResult;
                } else {
                    alert(‘error’);
                }
            }
            return false;

        }

 Here we first eval the responseText then  we just use it as collection array as shown in above example.

tdata.bank[0].lngId = 1

tdata.bank[0].strCode =”ICICI”

tdata.bank[0].strBank =”ICICI BANK”

in the above manner we use the Json which is meaning ful and simple format.

for more information check out the attached project. bankmasterProject

Thanks

Rajat