SQL Server Analysis Services (SSAS)

SQL Server Analysis Services  introduces many enhancements for tabular models. These include:

  • Tabular mode as the default installation option for Analysis Services. (CTP 2.0)
  • Object-level security to secure the metadata of tabular models. (CTP 2.0)
  • Date relationships to easily create relationships based on date fields. (CTP 2.0)
  • New Get Data (Power Query) data sources, and existing DirectQuery data sources support for M queries. (CTP 2.0)
  • DAX Editor for SSDT. (CTP 2.0)
  • Encoding hints, an advanced feature for optimizing data refresh of large in-memory tabular models. (CTP 1.3)
  • Support for the 1400 Compatibility level for tabular models. To create new or upgrade existing tabular model projects to the 1400 compatibility level,
  • A modern Get Data experience for tabular models at the 1400 compatibility level.
  • Hide Members property to hide blank members in ragged hierarchies. (CTP 1.1)
  • New Detail Rows end-user action to Show Details for aggregated information. SELECTCOLUMNS and DETAILROWS functions for creating Detail Rows expressions. (CTP 1.1)
  • DAX IN operator for specifying multiple values. (CTP 1.1)

Advantages of ASP.NET Framework

                                                              MOST IMPORTANT ADVANTAGES & REASON TO CHOOSE ASP.NET FRAMEWORK

  • .NET allows the aptitude for any user to access data or database and return results.
  • Entire flexibility for viewing in multiple browsers including internet explorer and Mozilla Firefox.
  • .NET framework decreases the size of the entire written code which is generally used to create large applications.
  • It is easily integrated with other Microsoft applications.
  • .NET is purely server side technology.
  • Framework ASP.NET code boasts of high-security features in its internal setup and makes safe & secure applications.
  • It is easy to use for creating dynamic web pages and has the ability to dynamically edit any of the content on the web page.
  • Microsoft .NET stage offers rich Better interface controls.
  • It provides MVC Architecture (model view controller) to make versatile applications.
  • Microsoft .NET applications can be effortlessly referred on any Microsoft server. The structure stimulates one-time setup creation to convey application quicker.
  • Web server continually monitors the pages, applications, and components running on it. If its attention any infinite loops, memory leaks and other illegal activities, it quickly destroys those activities and restarts itself.
  • It provides more stability and scalability.

CRUD Operations using AngularJS and WebAPI with MVC

Steps to build MVC CRUD operations using AngularJS and WebAPI

Step1: 

Create a MVC web application in visual studio

Step-2: 

Create a MDF file inside APP_Data folder.

Step-3: 

 Now create a table and single stored procedure to perform CRUD operations.

 

CREATE TABLE [dbo].[Employee]
(
       [Id] INT NOT NULL IDENTITY (1,1),
       [Name] nvarchar(50) Not null,
       [Address] nvarchar(50) not null,
       [Country] nvarchar(50) not null,
       [City] nvarchar(50) not null,
       [Mobile] nvarchar(10) not null,
       PRIMARY KEY CLUSTERED([Id] ASC)
);

Stored Procedure for CRUD Operation

CREATE PROCEDURE USP_InsUpdDelEmployee
@id int,
@name nvarchar(50),
@address nvarchar(50),
@Country nvarchar(50),
@city nvarchar(50),
@mobile nvarchar(50),
@type varchar(10)
 
As
Begin
if (@type='Ins') Begin
Insert into Employee
Values (@name,@address,@Country,@city,@mobile)
  End
  if(@type='Upd') Begin
  Update Employee Set Name=@name, Address=@address, Country=@Country, 
City=@city,Mobile=@mobile where Id=@id
  End
  if(@type='Del') Begin
  Delete from Employee where Id=@id
  end
  if(@type='GetById') Begin
 
  Select * from Employee where Id=@id
  End
  Select * From Employee
  End

 

Step-4:

Right click on models and create entity-data model then automatically connection will be created in web.config file.

Step-5:

Right click on controllers folder,create webapi empty controller and paste below code

public class EmployeeAPIController : ApiController
{

[HttpGet]
public List Get()
{
List emplist = new List();
using (MydatabaseEntities dc = new MydatabaseEntities())
{
var results = dc.USP_InsUpdDelEmployee(0, "", "", "", "", "", "Get").ToList();
foreach(var result in results)
{
var employee = new Employee()
{
Id = result.Id,
Name = result.Name,
Address = result.Address,
Country = result.Country,
City = result.City,
Mobile = result.Mobile
};
emplist.Add(employee);
}
return emplist;
}
}

public Employee Get(int id)
{
using (MydatabaseEntities dc = new MydatabaseEntities())
{
Employee employee = dc.Employees.Find(id);
if (employee == null)
{
throw newHttpResponseException(Request.CreateResponse(HttpStatusCode.NotFound));
}
return employee;
}
}

public HttpResponseMessage Post(Employee employee)
{
if (ModelState.IsValid)
{
using (MydatabaseEntities dc = new MydatabaseEntities())
{
var emplist = dc.USP_InsUpdDelEmployee
(0, employee.Name, employee.Address, employee.Country, employee.City, employee.Mobile,"Ins").ToList();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, emplist);
return response;
}
} else
{
return Request.CreateResponse(HttpStatusCode.BadRequest, ModelState);
}
}

public HttpResponseMessage Put(Employee employee)
{
List emplist = newList();
if (!ModelState.IsValid)
{
return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
}
using(MydatabaseEntities dc=new MydatabaseEntities())
{
try
{
emplist = dc.USP_InsUpdDelEmployee
(employee.Id, employee.Name, employee.Address, employee.Country, employee.City, employee.Mobile, "Upd").ToList();

}
catch (DbUpdateConcurrencyException ex)
{

returnRequest.CreateErrorResponse(HttpStatusCode.NotFound,ex);
}
return Request.CreateResponse(HttpStatusCode.OK, emplist);
}
}

public HttpResponseMessage Delete(int id)
{
using(MydatabaseEntities dc=new MydatabaseEntities())
{
List emplist = newList();
var results = dc.USP_InsUpdDelEmployee(id, "", "", "", "", "", "GetById").ToList();
if (results.Count==0)
{
return Request.CreateResponse(HttpStatusCode.NotFound);
}
try
{
emplist = dc.USP_InsUpdDelEmployee(id, "", "", "", "", "", "Del").ToList();
}
catch (DbUpdateConcurrencyException ex)
{
return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
throw;
}
return Request.CreateResponse(HttpStatusCode.OK, emplist);
}
}

protected override void Dispose(bool disposing)
{
using (MydatabaseEntities dc = new MydatabaseEntities())
dc.Dispose();
base.Dispose(disposing);
}
}
}

Step-6:

Create another controller for MVC to create view.

 


public class TestController : Controller
    {
        // GET: Test
        public ActionResult Index()
        {
            return View();
        }
    }

Step-7:

Load angular js files from this url: https://www.angularjs.org/ (or) use CDN files.

Step-8:

Create javascript file to write angular code like below.


var app = angular.module('myapp', []);
app.controller('employeeController', ['$scope', '$http', employeeController]);
 
function employeeController($scope,$http)
{
    $scope.loading = true;
    $scope.updateShow = false;
    $scope.addShow = true;
    $http.get('/api/EmployeeAPI', this.newemployee).success(function (data) {
        $scope.employees = data;
    }).error(function () {
        $scope.error = 'An error has occured while posting';
    });
 
    $scope.add = function () {
        $scope.loading = true;
        $http.post('/api/EmployeeAPI', this.newemployee).success(function (data) {
            $scope.employees = data;
            $scope.updateShow = false;
            $scope.addShow = true;
            $scope.newemployee = '';
        }).error(function (data) {
            $scope.error = "error while saving" + data;
        });
    }
 
    $scope.edit = function () {
        var Id = this.employee.Id;
        $http.get('/api/EmployeeAPI/' + Id).success(function (data) {
            $scope.newemployee = data;
            $scope.updateShow = true;
            $scope.addShow = false;
        }).error(function () {
            $scope.error = "An Error has occured while loading posts!";
        });
    }
    $scope.update = function () {
        $scope.loading = true;
        console.log(this.newemployee);
        $http.put('/api/EmployeeAPI/', this.newemployee).success(function (data) {
            $scope.employees = data;
            $scope.updateShow = false;
            $scope.addShow = true;
            $scope.newemployee = '';
        }).error(function (data) {
            $scope.error = "An Error has occured while Updating employee! " + data;
        });
    }
    $scope.delete = function () {
        var Id = this.employee.Id;
        $scope.loading = true;
        $http.delete('/api/EmployeeAPI/' + Id).success(function (data) {
            $scope.employees = data;
        }).error(function (data) {
            $scope.error = "An Error has occured while Delete employee! " + data;
        });
    }
    $scope.cancel = function () {
        $scope.updateShow = false;
        $scope.addShow = true;
        $scope.newemployee = '';
    }
}

Step-9:

Create an index view and paste below code

 

@{

    Layout = “~/Views/Shared/_Layout.cshtml”;

}

<html ng-app=”myapp”>

<head>

    <title>MVC AngularJs With WebApi</title>

</head>

<body>

    <div ng-controller=”employeeController class=”container”>

        <div class=”row”>

            <div class=”col-md-12″>

                <h3 class=”header”>EMPLOYEE CRUD USING ANGULARJS AND WEBAPI</h3>

            </div>

        </div>

        <div class=”row”>

            <div class=”col-md-12″>

                <strong class=”error”>{{error}}</strong>

                <form name=”addemployee” style=”width600pxmargin0px auto;“>

                    <div class=”form-group”>

                        <label for=”cname” class=”col-sm-2 control-label”>Name:</label>

                        <div class=”col-sm-10 space”>

                            <input type=”text” class=”form-control” id=”cname”placeholder=”please enter your name” ng-model=”newemployee.Name required />

                        </div>

                    </div>

                    <div class=”form-group”>

                        <label for=”address” class=”col-sm-2 control-label”>Address:</label>

                        <div class=”col-sm-10 space”>

                            <textarea class=”form-control” id=”address”placeholder=”please enter your address” ng-model=”newemployee.Address required></textarea>

                        </div>

                    </div>

                    <div class=”form-group”>

                        <label for=”country” class=”col-sm-2 control-label”>Domicile:</label>

                        <div class=”col-sm-10 space”>

                            <input type=”text” class=”form-control” id=”country”placeholder=”please enter your native” ng-model=”newemployee.Country required />

                        </div>

                    </div>

                    <div class=”form-group”>

                        <label for=”city” class=”col-sm-2 control-label”>Town:</label>

                        <div class=”col-sm-10 space”>

                            <input type=”text” class=”form-control” id=”city”placeholder=”please enter your town” ng-model=”newemployee.City required />

                        </div>

                    </div>

                    <div class=”form-group”>

                        <label for=”mobile” class=”col-sm-2 control-label”>Contact:</label>

                        <div class=”col-sm-10 space”>

                            <input type=”text” class=”form-control” id=”mobile”placeholder=”please enter your Contact no.” ng-model=”newemployee.Mobilerequired />

                        </div>

                    </div>

                    <br />

                    <div class=”form-group space”>

                        <div class=”col-sm-offset-2 col-sm-10″>

                            <input type=”submit” value=”Insert” ng-click=”add()ng-show=”addShow ng-disabled=”!addemployee.$valid class=”btn btn-primary” />

                            <input type=”submit” value=”Update” ng-click=”update() ng-show=”updateShow ng-disabled=”!addemployee.$validclass=”btn btn-primary” />

                            <input type=”button” value=”Reset” ng-click=”cancel() class=”btn btn-primary” />

                        </div>

                    </div>

                    <br />

                </form>

            </div>

        </div>

        <div class=”row”>

            <div class=”col-md-12″>

                <div class=”table-responsive”>

                    <table class=”table table-bordered table-hover” style=”width800pxmargin-left170px;“>

                        <tr>

                            <th>Name</th>

                            <th>Address</th>

                            <th>Domicile</th>

                            <th>Town</th>

                            <th>Contact</th>

                            <th>Actions</th>

                        </tr>

                        <tr ng-repeat=”employee in employees“>

                            <td>

                                <p>{{ employee.Name }}</p>

                            </td>

                            <td>

                                <p>{{ employee.Address }}</p>

                            </td>

                            <td>

                                <p>{{ employee.Country }}</p>

                            </td>

                            <td>

                                <p>{{ employee.City }}</p>

                            </td>

                            <td>

                                <p>{{ employee.Mobile }}</p>

                            </td>

                            <td>

                                <p><a ng-click=”edit()href=”javascript:void(0);”>Edit</a> | <a ng-click=”delete()href=”javascript:void(0);”>Delete</a></p>

                            </td>

                        </tr>

                    </table>

                </div>

            </div>

        </div>

    </div>

    

</body>

</html>  

 

Step-10:

Finally run the project and you get the output as you expected.

Good Luck:)

SQL SERVER – Learning new multipurpose FORMAT function

In versions prior to 2012, you need to use the CONVERT function with different styles to convert the date values into a different format. Let us learn about the FORMAT function here.

Let us create this simple dataset

CREATE TABLE #date_test(dates DATETIME)

INSERT INTO #date_test(dates)

SELECT ‘2016-10-19 10:22:34’ AS DateTest

Executing above query will return no result, but it will create a temporary table with one row.

Suppose you want to format the dates in either dd/mm/yyyy or mm/dd/yyyy format. You can do it by

SELECT dates,CONVERT(VARCHAR(10),dates,101) AS [dates_mm/dd//yyyy]

FROM #date_test

SELECT dates,CONVERT(VARCHAR(10),dates,103) AS [dates_dd/mm/yyyy]

FROM #date_test

Executing above query will return following result:

If you want to show only month name, you can use

SELECT DATENAME(month,dates) AS [month_name] FROM #date_test

Executing above query will return following result:

If you want to show only day name, you can use

SELECT DATENAME(weekday,dates) AS [day_name] FROM #date_test

Executing above query will return following result:

As you can see, you need to use either CONVERT or different functions based on what you want to do. This requires the usage of many different functions.

But with version 2012 it is very easy. All you need to do is just make use of FORMAT function

SELECT dates,FORMAT(dates,’dd/MM/yyyy’) AS [dates_dd/mm/yyyy] FROM #date_test

 

SELECT dates,FORMAT(dates,’MM/dd/yyyy’) AS [dates_mm/dd/yyyy] FROM #date_test

Executing above query will return following result:

Just use change the second parameter accordingly

To display month name, or day name

SELECT dates,FORMAT(dates,’MMMM’) AS [month_name] FROM #date_test

 

SELECT dates,FORMAT(dates,’dddd’) AS [day_name] FROM #date_test

Executing above query will return following result:

You can also display long formatted dates with customized text included

SELECT dates,FORMAT(dates,'”The given date is ” MMMM d, yyyy’) AS long_format

FROM #date_test

Executing above query will return following result:

Now let us run following script

SELECT dates,FORMAT(dates,'”This is generated on” M/d/y “at” hh:mm:ss’) AS long_format FROM #date_test

Executing above query will return following result:

Not only you can do date time formatting, you can also do number formatting using the FORMAT function.

Suppose you want to always make 6 digits number and if the length is less than 6, you want to prefix leading zeroes to make the total length as 6, you can do it by

SELECT 935 AS [number], FORMAT(935,’000000′) AS length_6

Executing above query will return following result:

I believe this new function FORMAT is very handy function and after using this, you may not have to remember many different functions to the same task.

Multilevel Grid in MVC using Jquery

Steps To build Multilevel Grid in MVC

Step1:- Create Empty MVC application in visual studio

Step-2: – Create a MDF file inside App_Data folder.

Step-3:- Now create a table for retrieving the data here i took employees as table name.

CREATE TABLE [dbo].[Employees] (

   [EmployeeID]     INT           NOT NULL,

   [LastName]        NVARCHAR (50) NULL,

   [FirstName]       NVARCHAR (50) NULL,

   [Title]           NVARCHAR (50) NULL,

   [TitleOfCourtesy] NVARCHAR (50) NULL,

   [BirthDate]       DATETIME      NULL,

   [HomePhone]       NVARCHAR (50) NULL,

   [ReportsTo]       INT           NULL,

   CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ([EmployeeID] ASC)

);

 After creating the table insert some data in that table like this

If you observe the above table, we have “ReportsTo” column this column contains EmployeeID values and based on this column we can assign employees to CEO, Project manager and Team Leader.

Step-4:- Create one ADO.Net Entity Model to Employees table. In solution you can see my entity model in below snippet.

 

Step-6:- Right click on controllers folder, create Controller.

public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
List<Employee> list = new List<Employee>();
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
list = dc.Employees.OrderBy(a => a.EmployeeID).ToList();
}
return View(list);

}
}

MyDatabaseEntities is class which inherited by DbContext. using “dc” we retrieve all employees details and assign to the list.

Employee.cs:-

public string HomePhone { get; set; }

public Nullable<int> ReportsTo { get; set; }

}

Step7:- Now Right click on Index method of HomeController and add view.

 

Index.cshtml:-

 

@model List<MultilevelGrid.Employee>

 

<h2>Display Multilevel Grid</h2>

 

@* Jquery for Collapse/Expend *@

<script src="http://ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js"></script>

<script type="text/javascript">

$(document).ready(function () {

$('.icon').on('click', function () {

var $pRow = $(this).parents('tr');

var $nextRow = $pRow.next('tr');

$nextRow.toggle();

$(this).toggleClass('icon-s icon-e');

});

})

</script>

 

@* CSS for dispalay the grid *@

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />

<style>

.container {

min-width: 500px;

overflow: hidden;

}

.innerTable table {

margin: 0px auto;

border-left-width: 4px;

}

td.innerTable {

padding-left: 20px !important;

}

.icon {

background-repeat: no-repeat;

width: 16px;

height: 16px;

display: inline-block;

float: left;

color: navy;

}

.icon-e {

background-position: -32px -16px;

}

.icon-s {

background-position: -64px -16px;

}

</style>

 

@*Call NetedData.cshtml Page for displaying the grid*@

<div class="container">

@NestedData.GetHtml(Model, 0)

</div>

 

 

       

In above view we add Employee.cs file as model at top of Index.cshtml. End of the coding we call the NestedData.cshtml page.

 

Step8:- Create NestedData.cshtml page for displaying the multilevel grid.

 

@helper GetHtml(List<MultilevelGrid.Employee> employeeList, int parentID)

{

 <table class=”table table-bordered”>

   @{

     int currentID = 0;

     int rowIndex = 0;

     foreach (var i in employeeList.Where(a => a.ReportsTo.Equals(parentID)))

      {

        if (i.EmployeeID == currentID)

         {

           continue;

         }

        else

         {

          if (rowIndex == 0)

            {

              <thead>

                <tr>

                   <th>Employee ID</th>

                   <th>Employee Name</th>

                   <th>Designation</th>

                   <th>Contact Number</th>

                </tr>

               </thead>

             }

        rowIndex++;

        currentID = i.EmployeeID;

         var Sub = employeeList.Where(a =>                                                                                                                                                                                                    a.ReportsTo.Equals(i.EmployeeID)).ToList();

        var newEmployeeList = employeeList.

 Where(a => !a.EmployeeID.Equals(i.EmployeeID)).ToList();

        <tbody>

          <tr>

            <td>

               @if (Sub.Count > 0)

                {

                 <a class=”icon icon-e” style=”float:left; cursor:pointer;”>>></a>

                }

               else

                {

                 <span style=”display:inline-block;width:14px”>&nbsp;</span>

                }

                 @i.EmployeeID

                 </td>

                 <td>@i.TitleOfCourtesy @i.FirstName @i.LastName</td>

                 <td>@i.Title</td>

                 <td>@i.HomePhone</td>

            </tr>

            <tr style=”display:none;”>

                 @if (Sub.Count > 0)

                  {

                    <td colspan=”4″ class=”innerTable”>

                      @NestedData.GetHtml(newEmployeeList, i.EmployeeID)

                    </td>

                   }

                  else

                   {

                     <td colspan=”4″></td>

                   }

              </tr>

         </tbody>

        }

      }

    }

</table>

}

 

In above code we retrieve the two categories of employees based “ReportsTo” column.i.e:

 

1)var Sub = employeeList.Where

(a => a.ReportsTo.Equals(i.EmployeeID)).ToList();

2) var newEmployeeList = employeeList.Where

(a =>!a.EmployeeID.Equals(i.EmployeeID)).ToList();

 

Step9:- Now run the application, by default the application load like this.

If you click on “>>” button, the next row will be expanded like this.

 

The above output indicates, the Project manager and Team Leader are working under CEO. Again if you click on second EmployeeID it will be expanded.

 

 

It’s indicates the Jr.Developer works under Project Manager.

 

This all explains how multilevel grid worked on employees table. Thanks!

Passing XML as a parameter in stored procedure and inserting using ADO.NET code with Example In MVC

XML_Stored_Proc

if we have number of parameters. We can append all parameters to xml parameter and passing as a single parameter in xml. This if helpful for more performance to a stored procedure.

Follow the Below steps 

Step-1: Create Table

Table

Step-2:  Create Procedure like below

CREATE PROCEDURE  [dbo].[USP_XMLInsert]
(
@xmlx xml
)
AS
BEGIN
SET NOCOUNT ON;
      INSERT INTO Tbl_Customer
      SELECT
      Customer.value(‘(Name/text())[1]’,’NVARCHAR(50)‘) as Name , –TAG
      Customer.value(‘(City/text())[1]’,’NVARCHAR(50)‘) as City, –TAG
      Customer.value(‘(IndustryType/text())[1]’,’NVARCHAR(50)‘) as IndustryType –TAG
      FROM
      @xmlx.nodes(‘/Customer‘)AS TEMPTABLE(Customer)

END

GO

Step 3: Create sample MVC application and create model class in models folder and create properties like below;

namespace Insert_XMLPROC.Models
{
    public class Customer
    {
        public string Name { get; set; }
        public string City { get; set; }
        public string IndustryType { get; set; }
    }
}

Step 4: Create controller in controller folder and write insert logic

    public class CustomerController : Controller
    {
        // GET: Customer
        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Index(Customer objcustomer)
        {
            StringBuilder objstring = new StringBuilder();
            objstring.Append(“<Customer>“);
            objstring.Append(“<Name>“);
            objstring.Append(objcustomer.Name);
            objstring.Append(“</Name>“);
            objstring.Append(“<City>“);
            objstring.Append(objcustomer.City);
            objstring.Append(“</City>“);
            objstring.Append(“<IndustryType>“);
            objstring.Append(objcustomer.IndustryType);
            objstring.Append(“</IndustryType>“);
            objstring.Append(“</Customer>“);

            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(objstring.ToString());

            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[“MyConnection“].ToString());
            con.Open();
            SqlCommand cmd = new SqlCommand(“USP_XMLInsert“, con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue(“@xmlx“, xmlDoc.InnerXml);
            cmd.ExecuteNonQuery();
            return View();
        }
    }


Step 5: Create index view and write insert logic

@model Insert_XMLPROC.Models.Customer

@{
    ViewBag.Title = “Index“;
}

<h2>Index</h2>

<script src=“~/Scripts/jquery-1.10.2.js”></script>
<script src=”~/Scripts/jquery-1.10.2.min.js“></script>
<div>
    <table>
        <tr>
            <td>Name</td>
            <td>
                <input type=”text” id=”txtName” />
            </td>
        </tr>
        <tr>
            <td>City</td>
            <td>
                <input type=”text” id=”txtCity” />
            </td>
        </tr>
        <tr>
            <td>IndustryType</td>
            <td>
                <input type=”text” id=”txtIndustryType” />
            </td>
        </tr>
        <tr>
            <td>
                <input type=”button” id=”btnsubmit” value=”Save” />
            </td>
        </tr>
    </table>

    <script type=“text/javascript”>
        $(document).ready(function () {
            $(‘#btnsubmit‘).click(function () {
                $.ajax(
                {
                    type: “POST“,
                    url: ‘/Customer/Index‘,
                    data: {
                        Name: $(‘#txtName‘).val(),
                        City: $(‘#txtCity‘).val(),
                        IndustryType: $(‘#txtIndustryType‘).val(),
                    }

                });

            });
        });
    </script>
</div>


TempData in MVC

TempData used to store temporary data which can be used in the subsequent request by the user. TempData will be cleared out after the completion of a subsequent request.

TempData is useful when you want to transfer non-sensitive data from one action method to another action method of the same or a different controller as well as redirects. It is dictionary type which is derived from TempDictionary.

Adding a key-value pair in TempData as shown in the below example.

Example: TempData

public class AventController : Controller
{
    // GET: Student
    public AventController()
    {

    }
    public ActionResult Index()
    {
        TempData["name"] = "Test data";
        TempData["age"] = 30;

        return View();
    }

    public ActionResult About()
    {
        string userName;
        int userAge;
        
        if(TempData.ContainsKey("name"))
            userName = TempData["name"].ToString();
    
        if(TempData.ContainsKey("age"))
            userAge = int.Parse(TempData["age"].ToString());
    
        // do something with userName or userAge here 

        return View();
    }
}

In the above code, we added data into TempData and accessed the same data using a key inside another action method. Notice that we have converted values into the appropriate type.

 

As you can see in the above example, we add test data in TempData in the first request and in the second subsequent request we access test data from TempData which we stored in the first request. However, you can’t get the same data in the third request because TempData will be cleared out after second request.

Call TempData.Keep() to retain TempData values in a third consecutive request.

Example: TempData.Keep()

public class AventController : Controller
{
    // GET: Student
    public AventController()
    {

    }
    public ActionResult Index()
    {
        TempData["myData"] = "Test data";
        return View();
    }

    public ActionResult About()
    {
        string data;
        
        if(TempData["myData"] != null)
            data = TempData["myData"] as string;
        
        TempData.Keep();
        
        return View();
    }

    public ActionResult Contact()
    {
        string data;
        
        if(TempData["myData"] != null)
            data = TempData["myData"] as string;
            
        return View();
    }

}

ViewData in MVC

ViewData is similar to ViewBag. We use to  transfer the data from Controller to View.

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

The following figure illustrates the ViewData.

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

The following example demonstrates how to transfer data from controller to view using ViewData.

Example: ViewData in Action method

public ActionResult Index()
{
    IList<Student> studentList = new List<Student>();
    studentList.Add(new Student(){ StudentName = "Bill" });
    studentList.Add(new Student(){ StudentName = "Steve" });
    studentList.Add(new Student(){ StudentName = "Ram" });

    ViewData["students"] = studentList;
  
    return View();
}

In the above example, we have added a student list with the key “students” in the ViewData dictionary. So now, the student list can be accessed in a view as shown below.

Example: Access ViewData in a Razor View

<ul>
@foreach (var std in ViewData["students"] as IList<Student>)
{
    <li>
        @std.StudentName
    </li>
}
</ul>

Notice that we need to cast  the ViewData values to the appropriate data type.

We can also add a KeyValuePair into ViewData as shown below.

Example: Adding KeyValuePair in ViewData

public ActionResult Index()
{
    ViewData.Add("Id", 1);
    ViewData.Add(new KeyValuePair<string, object>("Name", "Bill"));
    ViewData.Add(new KeyValuePair<string, object>("Age", 20));

    return View();
}

ViewData and ViewBag both use the same dictionary internally. So we cannot have ViewData Key matches with the property name of ViewBag, otherwise it will throw a runtime exception.

Example: ViewBag and ViewData

public ActionResult Index()
{
    ViewBag.Id = 1;

    ViewData.Add("Id", 1); // throw runtime exception as it already has "Id" key
    ViewData.Add(new KeyValuePair<string, object>("Name", "Bill"));
    ViewData.Add(new KeyValuePair<string, object>("Age", 20));

    return View();
}

ViewBag in ASP.NET MVC

ViewBag is useful to transfer temporary data (which is not included in model) from the controller to the view. The viewBag is a  dynamic type property of ControllerBase class which is the base class of all the controllers.

The following figure illustrates the ViewBag.

In the above figure, it attaches Name property to ViewBag with the dot notation and assigns a string value to “Bill” to it in the controller. This can be accessed in the view like @ViewBag.Name. (@ is razor syntax to access the server side variable.)

You can assign any number of properties and values to ViewBag. If you assign the same property name multiple times to ViewBag, then it will only consider last value assigned to the property.

 

The following example demonstrates how to transfer data from controller to view using ViewBag.

Example: Set ViewBag in Action method

namespace MVC_BasicTutorials.Controllers
{
    public class StudentController : Controller
    {
        IList<Student> studentList = new List<Student>() { 
                    new Student(){ StudentID=1, StudentName="Steve", Age = 21 },
                    new Student(){ StudentID=2, StudentName="Bill", Age = 25 },
                    new Student(){ StudentID=3, StudentName="Ram", Age = 20 },
                    new Student(){ StudentID=4, StudentName="Ron", Age = 31 },
                    new Student(){ StudentID=5, StudentName="Rob", Age = 19 }
                };
        // GET: Student
        public ActionResult Index()
        {
            ViewBag.TotalStudents = studentList.Count();

            return View();
        }

    }
}

In the above example, we want to display the total number of students in a view for the demo. So, we have attached the TotalStudents property to the ViewBag and assigned the student count using studentList.Count().

Now, in the Index.cshtml view, you can access ViewBag.TotalStudents property and display all the student info as shown below.

Example: Acess ViewBag in a View

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

Output:

Total Students: 5

ViewBag doesn’t require typecasting while retriving values from it.

Internally, ViewBag is a wrapper around ViewData. It will throw a runtime exception, if the ViewBag property name matches with the key of ViewData.

Partial Views in MVC

Partial View:

In this section you will learn about partial views in ASP.NET MVC.

What is Partial View?

Partial view is a reusable view, which can be used as a child view in multiple other views. It eliminates duplicate coding by reusing same partial view in multiple places. You can use the partial view in the layout view, as well as other content views.

To start with, let’s create a simple partial view for the following navigation bar for demo purposes. We will create a partial view for it, so that we can use the same navigation bar in multiple layout views without rewriting the same code everywhere.

sample partial view
Partial View

The following figure shows the html code for the above navigation bar. We will cut and paste this code in a seperate partial view for demo purposes.

Partial Views

Create New Partial View:

To create a partial view, right click on Shared folder -> select Add -> click on View..

Note :If a partial view will be shared with multiple views of different controller folder then create it in the Shared folder, otherwise you can create the partial view in the same folder where it is going to be used.

In the Add View dialogue, enter View name and select “Create as a partial view” checkbox and click Add.

Partial Views

We are not going to use any model for this partial view, so keep the Template dropdown as Empty (without model) and click Add. This will create an empty partial view in Shared folder.

Now, you can cut the above code for navigation bar and paste it in _HeaderNavBar.cshtml as shown below:

Partial View: _HeaderNavBar.cshtml
<div class="navbar navbar-inverse navbar-fixed-top">
    <div class="container">
        <div class="navbar-header">
            <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
                <span class="icon-bar"></span>
            </button>
            @Html.ActionLink("Application name", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
        </div>
        <div class="navbar-collapse collapse">
            <ul class="nav navbar-nav">
                <li>@Html.ActionLink("Home", "Index", "Home")</li>
                <li>@Html.ActionLink("About", "About", "Home")</li>
                <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
            </ul>
        </div>
    </div>
</div>

Thus, you can create a new partial view. Let’s see how to render partial view.

Render Partial View:

You can render the partial view in the parent view using html helper methods: Partial() or RenderPartial() or RenderAction(). Each method serves different purposes. Let’s have an overview of each method and then see how to render partial view using these methods.

Html.Partial():

@Html.Partial() helper method renders the specified partial view. It accept partial view name as a string parameter and returns MvcHtmlString. It returns html string so you have a chance of modifing the html before rendering.

The following table lists overloads of the Partial helper method:

Helper Method Description
MvcHtmlString Html.Partial(string partialViewName) Renders the given partial view content in the referred view.
MvcHtmlString Html.Partial(string partialViewName,object model) Renders the partial view content in the referred view. Model parameter passes the model object to the partial view.
MvcHtmlString Html.Partial(string partialViewName, ViewDataDictionary viewData) Renders the partial view content in the referred view. View data parameter passes view data dictionary to the partial view.
MvcHtmlString Html.Partial(string partialViewName,object model, ViewDataDictionary viewData) Renders the partial view content in the referred view. Model parameter passes the model object and View data passes view data dictionary to the partial view.

Html.RenderPartial():

The RenderPartial helper method is same as the Partial method except that it returns void and writes resulted html of a specified partial view into a http response stream directly.

Helper method Description
RenderPartial(String partialViewName) Renders the specified partial view
RenderPartial(String partialViewName, Object model) Renders the specified partial view and set the specified model object
RenderPartial(String partialViewName, ViewDataDictionary viewData) Renders the specified partial view, replacing its ViewData property with the specified ViewDataDictionary object.
RenderPartial(String partialViewName, Object model, ViewDataDictionary viewData) Renders the specified partial view, replacing the partial view’s ViewData property with the specified ViewDataDictionary object and set the specified model object

Html.RenderAction():

The RenderAction helper method invokes a specified controller and action and renders the result as a partial view. The specified Action method should return PartialViewResult using the Partial() method.

Name Description
RenderAction(String actionName) Invokes the specified child action method and renders the result in the parent view.
RenderAction(String actionName, Object routeValue) Invokes the specified child action method using the specified parameters and renders the result inline in the parent view.
RenderAction(String actionName, String controllerName) Invokes the specified child action method using the specified controller name and renders the result inline in the parent view.
RenderAction(String actionName, RouteValueDictionary routeValues) Invokes the specified child action method using the specified parameters and renders the result inline in the parent view.
RenderAction(String actionName, String controllerName, Object routeValue) Invokes the specified child action method using the specified parameters and controller name and renders the result inline in the parent view.
RenderAction(String actionName, String controllerName, RouteValueDictionary routeValues) Invokes the specified child action method using the specified parameters and controller name and renders the result inline in the parent view.

So now, we can use any of the above rending methods to render the _HeaderNavBar partial view into _Layout.cshtml. The following layout view renders partial view using the RenderPartial() method.

Example: Html.RenderPartial()
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - My ASP.NET Application</title>
    @Styles.Render("~/Content/css") 
    @Scripts.Render("~/bundles/modernizr")
</head>
<body>
    @{
        Html.RenderPartial("_HeaderNavBar");   
    }
    <div class="container body-content">
        @RenderBody()
        
        <hr />
        <footer>
            <p>&copy; @DateTime.Now.Year - My ASP.NET Application</p>
        </footer>
    </div>

    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>
</html>

 

Note :RenderPartial returns void, so a semicolon is required at the end and so it must be enclosed in the braces.

The following layout view uses the Partial method to render partial view_HeaderNavBar.cshtml.

Example: Html.Partial()
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - My ASP.NET Application</title>
    @Styles.Render("~/Content/css") 
    @Scripts.Render("~/bundles/modernizr")
</head>
<body>
     @Html.Partial("_HeaderNavBar")   
    <div class="container body-content">
        @RenderBody()
        
        <hr />
        <footer>
            <p>&copy; @DateTime.Now.Year - My ASP.NET Application</p>
        </footer>
    </div>

    @Scripts.Render("~/bundles/jquery")
    @Scripts.Render("~/bundles/bootstrap")
    @RenderSection("scripts", required: false)
</body>
</html>

 

Note :@Html.Partial() method doesn’t need to be in code block because it returns a html string.

You will see following UI in browser when you run the application.

Index.cshtml