Sessions in SQL Server 2016

In .Net we have Session object which store and retrieve values for a user as user navigates ASP.NET pages in a Web application for that session. 

With Sql Server 2016 we are getting the similar feature in Sql Server, where we can store multiple key and value pairs which are accessible throughout that session. The key and value pairs can be set by the sp_set_session_context system stored procedure and these set values can be retrieved one at a time by using the 

SESSION_CONTEXT built in function.

SYNTAX of sp_set_session_context:

sp_set_session_context [ @key= ] 'key', [ @value= ] 'value'


    [ , [ @read_only = ] { 0 | 1 } ]


Where: @key parameter is of type SYSNAME, @value is of type 


SQL_VARIANT and @read_only parameter is of type BIT

SYNTAX of SESSION_CONTEXT

SESSION_CONTEXT(N'key')

Where:’key’ parameter to this function is of type SYSNAME and the return type of this function is SQL_VARIANT

 

EXAMPLE 1: This example demonstrates how we can set the session context key named Employee Id with it’s value and retrieving this set keys value.

--Set the session variable EmployeeId value

EXEC sp_set_session_context 'EmployeeId', 1

--Retrieve the session variable EmployeeId value

SELECT SESSION_CONTEXT(N'EmployeeId') AS EmployeeId

ScreenShot:

Note: If we skip the Prefix N then we will get the Error.

From the result it is clear that the SESSION_CONTEXT function always expects the key parameter passed to it should of type NVarchar.

EXAMPLE 2: update the Session variable value

--Set session variable EmployeeName value

EXEC sp_set_session_context @key = 'EmployeeName', 

  @value='Uday'

--Retrieve the session variable EmployeeName value

SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName

--Re-Set the EmployeeName session variable value

EXEC sp_set_session_context @key ='EmployeeName',

  @value='Rajakonda Uday'

--Retrieve the update session variable EmployeeName value

SELECT SESSION_CONTEXT(N'EmployeeName') EmployeeName

ScreenShot:

Example 3: Read Only Session Variable

--create a readonly session variable Actor

EXEC sp_set_session_context @key = 'Actor', @value='Number 1 Hero of Tollywood', 

  @read_only = 1

--Retrieve the session variable Actor value

SELECT SESSION_CONTEXT(N'Actor') Actor

ScreenShot

Example 4: Session variable can be accessed in stored procedure which is set somewhere outside.

Let us create a stored procedure GetCountry

CREATE PROCEDURE GetCountry AS BEGIN     SELECT SESSION_CONTEXT(N'Country') Country END --create a readonly session variable Country EXEC sp_set_session_context @key = 'Country', @value='India' --Retrieve the session variable Country value SELECT SESSION_CONTEXT(N'Country') Country Now in the same session execute the stored procedure GetCountry and see whether this SP has access to the Session variable Country: RESULT:   when we try to fetch a non-existent session variable  SELECT SESSION_CONTEXT(N'Continent') Continent

ViewData, ViewBag, TempData

ViewData:

Data travels from the controller to the view via a ViewDataDictionary. This ViewDataDictionary is a dictionary class; we call this class "ViewData".

Passes the data from the controller to the view

It is a dictionary object that is derived from ViewDataDictionary

Note: ViewData is a dictionary which can contain key-value pairs where each key must be string.

It only transfers data from controller to view, not vice-versa. It is valid only during the current request.

Points to Remember :

1. ViewData transfers data from the Controller to View, not vice-versa.
2. ViewData is derived from ViewDataDictionary which is a dictionary type.
3. ViewData's life only lasts during current http request. ViewData values will be cleared if redirection occurs.
4. ViewData value must be type cast before use.
5. ViewBag internally inserts data into ViewData dictionary. So the key of ViewData and property of ViewBag must NOT match.
6. Data is stored as Object in ViewData.
7. While retrieving, the data it needs to be Type Casted to its original type as the data is stored as objects and it also requires NULL checks while retrieving.

Example:

public class FirstController : Controller{    // GET: First    public ActionResult Index()    {        ViewData["Message"] = "Hello MVC!";        return View();    }}

View:

<html>

<head>

    <meta name="viewport" content="width=device-width"/>

    <title>Index</title>

</head>

<body>

    <div>

        @ViewData["Message"]

    </div>

</body>

</html>

OutPut:

Example 2:

//Controller Code 

 public ActionResult Index()  {        List<string> Student = new List<string>();        Student.Add("Uday");        Student.Add("Rajakonda");        Student.Add("Kittu");           ViewData["Student"] = Student;        return View();  }  

View:

//page code  
foreach (var student in ViewData["Student"] as List<string>)  
        { %>  
    <li><%: student%></li>  
    <% } %>  

</ul> 

Ex:3 
public class StudentController : Controller
    {
        IList<Student> studentList = new List<Student>() { 
                    new Student(){ StudentID=1, StudentName="A", Age = 21 },
                    new Student(){ StudentID=2, StudentName="B", Age = 25 },
                    new Student(){ StudentID=3, StudentName="C", Age = 20 },
                    new Student(){ StudentID=4, StudentName="D", Age = 31 },
                    new Student(){ StudentID=5, StudentName="E", Age = 19 }
                };
        // GET: Student
        public ActionResult Index()
        {
            ViewBag.TotalStudents = studentList.Count();

            return View();
        }

    }

<label>Total Students:</label>  @ViewBag.TotalStudents

ViewBag
ViewBag is just a dynamic wrapper around ViewData. With it you don't need to write the dynamic keyword, it takes the dynamic keyword internally.
We often call ViewBag "A dynamic data library".

• 
  
• Doesn't require type casting
• It is a dynamic property

TempData
TempData helps in maintaining data when you move from one controller to another controller. For maintaining data it uses a session variable (internally).

It is a dictionary object that is derived from ViewDataDictionary.

•  It requires typecasting for:
  • Getting Data
  • Check NULL values
  • Avoiding Errors

• It is used to store one time messages like
  • Error Messages
  • Validation Messages
•  It's life is very short

•  It's a Key-Value-Collection object

Data maintenance
This table represents the data maintenance between a controller and a view in various aspects/scenarios.

NEW Features of SQL Server

DROPIFEXISTS:

A new syntax has been introduced to check if an object exists before dropping it. Previously, if you wanted to check if a procedure existed before you dropped it, you had to write a statement like this
/****** Object:  StoredProcedure [its].[updQueueItemEmailStatus]    ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[its].[updQueueItemEmailStatus]') AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE [its].[updQueueItemEmailStatus]
END
GO

Now with 2016 you can write this as

Syntax:
DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME
IF EXISTS: It is an optional clause and if it is mentioned in the DROP statement then it checks the existence of the object, if it exists it will drop otherwise continues executing the next statement in the block without raising any issues.


DROP PROCEDURE IF EXISTS [its].[updQueueItemEmailStatus]

Note: Now if the stored procedure does not exists it won't throw any Error
If the stored procedure doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the stored procedure WelcomeMessage which is already dropped.


Example:

If the table doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Table dbo.Customers which is already dropped.

If the Database doesn’t exists it will not raise any error, it will continue executing the next statement in the batch. Let’s try to re-drop the Database SqlHintsDemoDB which is already dropped.

In the similar way we can drop Schema And Views


2 JSON support

Similar like XML, SQL Server now supports the JSON format. You can for example convert tabular data to JSON using the FOR JSON clause. An example:

SELECT NAME,first_name,entity_key,name_key FROM its_name
FOR JSON AUTO


Output:

OPENJSON - OPENJSON is a table-value function, which accepts some text which includes a JSON value and returns rows in the required format. FOR JSON, converts the rows into JSON format, but OPENJSON will convert back the JSON text into a table of rows.

IsJSON (< JSON text >) - This function verifies, whether the given text is formatted according to JSON standards/rules.

JSON_Value () - This is a scalar function and it parses the JSON text then extracts a value if it exists in the path arguments. This functionality is similar to 

XPATH (XML Path) which is used for selecting nodes from XML text. In addition, XPath may be used to compute values (e.g. strings, numbers or Boolean values) from the content of an XML document.

FOR JSON PATH – We can define the JSON structure in the query by defining column alias with a dot (.) separated value (for example ‘Root.level1’). This Functionality is similar to FOR XML PATH.

Example 1: Basic FOR JSON PATH example

SELECT 'Uday' FirstName, 'Kumar' LastName ,'Rajakonda' SurName

FOR JSON PATH

Output:

[{"FirstName":"Uday","LastName":"Kumar","SurName":"Rajakonda"}]

Example 2 :Basic FOR JSON AUTO requires at-least one table for generating the JSON output

SELECT 'Uday' FirstName, 'Kumar' LastName ,'Rajakonda' SurName

FOR JSON AUTO

RESULT:

Msg 13600, Level 16, State 1, Line 10

FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.

From the above result it is clear that the FOR JSON AUTO clause works only if at-least one table is mentioned in the from clause.

Example 3: FOR JSON PATH/AUTO example where column names are not specified in the select list, instead * is mentioned

Example 4: FOR JSON PATH/AUTO example where required columns in the JSON output are specified in the SELECT clause

PRINT '******* FOR JSON PATH output *******'
SELECT top 5 name,name_suffix_key,entity_key FROM its_name  FOR JSON PATH
GO
PRINT '******* FOR JSON AUTO output *******'
SELECT top 5 name,name_suffix_key,entity_key FROM its_name FOR JSON AUTO

Output:

Example 5: To include NULL values in the JSON output, we need to specify the property INCLUDE_NULL_VALUES in the FOR JSON clause

Note: If this option is not specified, in case of NULL value the name-value pair will be removed from the JSON output  

PRINT '******* FOR JSON PATH output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name  FOR JSON PATH,INCLUDE_NULL_VALUES

GO

PRINT '******* FOR JSON AUTO output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name FOR JSON AUTO,INCLUDE_NULL_VALUES

Output: 

Example 6 :We can use the ROOT option in the FOR JSON clause to generate a wrapper object around the generated JSON output. In the below example the ROOT option creates a Customers JSON wrapper object around the generated JSON output:

PRINT '******* FOR JSON PATH output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name  FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('Names')

GO

PRINT '******* FOR JSON AUTO output *******'

SELECT top 5 name,name_suffix_key,entity_key FROM its_name FOR JSON AUTO,INCLUDE_NULL_VALUES,ROOT('Names')

Example 7: In case of FOR JSON PATH clause using “.” Symbol in the column aliases, we can name the each object in the resultant JSON array as shown below:

SELECT   name_key[Name.namekey],name[Name.name] FROM ITS_NAME FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('Names')

Output:

Using “.” symbol in the column aliases doesn’t have any effect in the resulting JSON output in case of FOR JSON AUTO as shown below:


using JSON AUTO


SELECT   name_key[Name.namekey],name[Name.name] FROM ITS_NAME FOR JSON Auto,INCLUDE_NULL_VALUES,ROOT('Names')


Output:

Example 8: We can convert each row into a JSON object with multiple sub-objects by using “.” Symbol in the column alias as shown below:

SELECT   name_key[Name.namekey],name[Name.name],entity_key[Entity.entityKey] FROM ITS_NAME FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('Names')

OutPut:

3 FORMATMESSAGE

FROM 2016, We can supply our own String
Declare @str varchar(max)
set @str=FORMATMESSAGE('Hello %s,Uday %s' ,'RajaKonda', 'Kumar')
select @str

4 STRING_SPLIT

In previous release we used to write lot of code ( we used to create a function to split the string).
Old way of writing code
Example
DECLARE @string VARCHAR(800) = '001,002,003,004'
SELECT * FROM [dbo].[fnSplitString] (@string,',')

But with sql server 2016 we can write as
DECLARE @string VARCHAR(800) = '001,002,003,004'
SELECT * FROM STRING_SPLIT (@string,',')


Output:

Why C Sharp Supports Multiple Inheritance with Interface only.

Multiple Inhertiance

A class which derived from more than one class it is called Multiple inheritance

-----------------------------------------------------------------------------------------

Example:

public class Employee

    {

        public virtual void DoWork()

        {

            //Do work

        }

    }

    public class Manager:Employee

    {

        public override void DoWork()

        {

            //Manager's implementation of do work

        }

    }

    public class Developer : Employee

    {

        public override void DoWork()

        {

            //Deveoper's implementation of do work

        }

    }

    public class JuniorDeveloper:Manager, Developer// Compile Time Error :Cannot have multiple base classes but assume that it were possible

    { }

----------------------------------------------------------------------------------

public class A

    {

        public virtual void A_Method()

        {

            Console.WriteLine("Class A Method");

        }

    }

    public class B : A

    {

        public override void A_Method()

        {

            Console.WriteLine("Class B Method");

        }

    }

    public class C : A

    {

        public override void A_Method()

        {

            Console.WriteLine("Class C Method");

        }

    }

    public class D : B, C  // If Multiple inheritence is possible in C# then

    {

        public override void A_Method()

        {

            Console.WriteLine("Class C Method");

        }

    }

    public static void main()

    {

        D objD = new D();

        objD.A_Method();// Making object of class D and calling overloaded method A_method will 

                        //confuse the compiler which class method to call as both inherited class methods has been overloaded.

    }

Explanation:

In the code above we have the A_Method()

which is defined by class A. But, the problem is that class D derives from both classes B and C, which both derive from class A. This means that there are essentially 2 copies of the A_Method()

that are available because there are 2 instances of A in D’s class hierarchy.

And the compiler will give an error and say that the reference to A_Method() is ambiguous.

-------------------------------------------------------------------------------------

Step1 Interface Definition

public interface IEmployee
{
   string GetEmployeeId();
}
The above code contains a method signature named DoWork ().
 Step 2 : Interface Implementation
As I have already defined an interface IEmployee in the above code snippet. Now if I want to implement the interface to my class Engineer as shown below.
public class Engineer:IEmployee
{

}
Step 3: 

Now if I execute the above code I will get a compile time error

-----------------------------------------------------------------------------------

Step 4:

The correct implementation of the above code is shown below

As we can see from the above code the signature of the GetEmployeeId() method in the Engineer class is same as the signature of the method in the (IEmployee)Interface.

Step 5:

Interface can only “inherit” from the interface only. Suppose if we want to extend the implementation of an interface in some part of our project without disturbing the already existing interface, in that case we can create a new interface and inherit it into new interface as shown below.

Example:

public interface Ibase1
    {
        void message();
    }
    public interface Ibase2
    {
        void message();
    }
    public class child : Ibase1, Ibase2
    {
        public void message()
        {
            Console.WriteLine("Hello Multiple Inheritance");
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            child obj = new child();
            obj.message();
            Console.ReadLine();
        }
    }

Output:

Important Points to Remembers 
    Class FirstClass { }
    Class SecondClass { }

interface X { }
interface Y { }
You can inherit like the following:

class NewClass : X, Y { }
In the above code, the class "NewClass" is created from multiple interfaces.


class NewClass : FirstClass, X { }
In the above code, the class "NewClass" is created from interface X and class "FirstClass".

WRONG:

class NewClass : FirstClass, SecondClass  { }
C# doesn't support Multiple inheritance

The above code is wrong in C# because the new class is created from class "FirstClass" and class "SecondClass". The reason is that C# does not allows multiple inheritance.



Finally Note: 
Point 1: 
When we use the Multiple inheritance , we use more than one class. Lets  us Assume one condition  like below
class A and class B are base classes and class c is is multiple inherting it and where class c is inheriting a function ,ok it may be possible that this function with same name and same signature can present in both class A and Class B .That time how the compiler will know that which function it should take wheather from class A or class B.
So this time Multiple inheritanc won't work .So avoiding this problem we use Interface. In interface we just declare a function  and in derived class we write the implemenation.

Point 2:
C# does not allow multiple inheritance because of ambiguity.