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.