SQL SERVER 2016 - JSON Data

JSON is a popular textual data format used for exchanging data in modern web and mobile applications. JSON is also used for storing unstructured data in log files or NoSQL databases. Many REST web services return results formatted as JSON text or accept data formatted as JSON. JSON is also the main format for exchanging data between web pages and web servers using AJAX calls.

SQL Server provides built-in functions and operators for following JSON data manipulation:

• Parse JSON text and read or modify values.
• Transform arrays of JSON objects into table format.
• Use any Transact SQL query on the converted JSON objects.
• Format the results of Transact-SQL queries in JSON format.

Transform JSON text to relational table:
OPENJSON is table-value function (TVF) that seeks into some JSON text, locate an array of JSON objects, iterate through the elements of array, and for each element generates one row in the output result. This feature will be available in CTP3. One example of OPENJSON function in T-SQL query is shown in the following example:

SELECT Number, Customer, Quantity
FROM OPENJSON (@JSalestOrderDetails, '$.OrdersArray')
WITH (
Number varchar(200),
Customer varchar(200),
Quantity int
) AS OrdersArray

Exporting data as JSON:
First feature that will be available in SQL Server 2016 CTP2 is ability to format query results as JSON text using FOR JSON clause. If you are familiar with FOR XML clause you will easily understand FOR JSON. When you add FOR JSON clause at the end of T-SQL SELECT query, SQL Server will take the results, format them as JSON text, and return it to client. Every row will be formatted as one JSON object, values in cells will be generated as values of JSON objects, and column names or aliases will be used as key names. Below is the syntax:

SELECT column, expression, column as alias
FROM table1, table2, table3
FOR JSON [AUTO | PATH]

AngularJS

AngularJS is an open source, JavaScript based web application development framework. It can be added to an HTML page with a <script> tag. AngularJS extends HTML attributes with Directives, and binds data to HTML with Expressions. The AngularJS framework works by first reading the HTML page, which has embedded into it additional custom tag attributes. Angular interprets those attributes as directives to bind input or output parts of the page to a model that is represented by standard JavaScript variables. The values of those JavaScript variables can be manually set within the code, or retrieved from static or dynamic JSON resources.

Definition of AngularJS as put by its official documentation is as follows:
"AngularJS is a structural framework for dynamic web applications. It lets you use HTML as your template language and lets you extend HTML's syntax to express your application components clearly and succinctly. Its data binding and dependency injection eliminate much of the code you currently have to write. And it all happens within the browser, making it an ideal partner with any server technology."

Angular allows your application to have an expanded HTML library.

Features of AngularJS are:

• AngularJS is a efficient framework that can create Rich Internet Applications (RIA).
• AngularJS provides developers an options to write client side applications using JavaScript in a clean Model View Controller (MVC) way.
• Applications written in AngularJS are cross-browser compliant. AngularJS automatically handles JavaScript code suitable for each browser.
• AngularJS is open source, completely free, and used by thousands of developers around the world. It is licensed under the Apache license version 2.0.

Common Language Infrastructure (CLI)

The Common Language Infrastructure (CLI) provides a specification for executable code and the execution environment (the Virtual Execution System) in which it runs. Executable code is presented to the VES as modules. At the center of the CLI is a unified type system, the Common Type System that is shared by compilers, tools, and the CLI itself. It is the model that defines the rules the CLI follows when declaring, using, and managing types. The CTS establishes a framework that enables cross-language integration, type safety, and high performance code execution. This clause describes the architecture of the CLI by describing the CTS. The following four areas are covered in this clause:

• The Common Type System (CTS) - The CTS provides a rich type system that supports the types and operations found in many programming languages. The CTS is intended to support the complete implementation of a wide range of programming languages.
• Metadata - The CLI uses metadata to describe and reference the types defined by the CTS. Metadata is stored (that is, persisted) in a way that is independent of any particular programming language. Thus, metadata provides a common interchange mechanism for use between tools (such as compilers and debuggers) that manipulate programs, as well as between these tools and the VES.
• The Common Language Specification (CLS) - The CLS is an agreement between language designers and framework (that is, class library) designers. It specifies a subset of the CTS and a set of usage conventions. Languages provide their users the greatest ability to access frameworks by implementing at least those parts of the CTS that are part of the CLS. Similarly, frameworks will be most widely used if their publicly exported aspects (e.g., classes, interfaces, methods, and
• fields) use only types that are part of the CLS and that adhere to the CLS conventions.
• The Virtual Execution System (VES) - The VES implements and enforces the CTS model. The VES is responsible for loading and running programs written for the CLI. It provides the services needed to execute managed code and data, using the metadata to connect separately generated modules together at runtime (late binding).

Together, these aspects of the CLI form a unifying infrastructure for designing, developing, deploying, and executing distributed components and applications. The appropriate subset of the CTS is available from each programming language that targets the CLI. Language-based tools communicate with each other and with the VES using metadata to define and reference the types used to construct the application. The VES uses the metadata to create instances of the types as needed and to provide data type information to other parts of the infrastructure (such as remoting services, assembly downloading, and security).

Test-driven development

Test-driven development (TDD a.k.a. test-first programming) represents an important change to the way most programmers think about writing code. The practice involves writing a test before each new piece of functionality is coded into the system. It is a software development process that relies on the repetition of a very short development cycle: requirements are turned into very specific test cases, then the software is improved to pass the new tests, only. This is opposed to software development that allows software to be added that isn't proven to meet requirements.

An interesting fact about bug-free code is that as the code is further developed, the bug count in it is less likely to increase than in code that is known to have bugs. Developers are more careful to ensure they do not introduce any errors to a solution that is bug free than they are when the solution is riddled with issues. It is what is known as "broken window syndrome". When code starts to fall into disrepair (has bugs), a typical belief follows that a few more bugs will not make any difference because the code is already broken. Therefore, it is important to keep the quality of the code high all the way through the process.

To develop zero-defect software, you need some tools and the correct way of thinking about quality. One of the most powerful tools is unit testing; and by developing the tests before writing the solution code, you are "turning the volume up." NUnit is a .NET Framework class library that can locate unit tests that have been written in your project. NUnit comes with a GUI windows application and a console-based application that both enable you to run the tests and see the results. Tests either pass or fail, as indicated by a green or red progress bar, respectively.

The tests were intended to increase your confidence in the code you are writing. The tests are driving the development process. By writing the tests first, you should find your focus on the code you are writing changes substantially. Your goal now is to define how the class will be used with a test method and then to get the tests to pass.