How to Get XML and Json format through SQL SERVER

How to Get XML and Json format through SQL SERVER.......

Here I will explain that how to get  sql data into xml and json format. As we know in SQL SERVER Xml data type exists. json is light waited as compare to xml and it is easy to use through JavaScript and jquery. so developer first choice to move json. some of the developers are using external function for converting sql data to json format. But we can do it by some modification of our sql query as shown below.
json is most same as the xml. suppose we have a table employee which has five columns id and name,gender,salary,doj(Date of Joining)

--Sql Query------------------

create database test

use test

-- CREATE TABLE WHICH NAME EMPLOYEE

create table Employee

(

EmpId int primary key,

Name varchar(50),

Gender varchar(50),

Salary int,

Doj date

)

-- insert  data-----

insert into Employee values (1,'Kush Tiwari','M',123456,'09/09/1987')

--how to get  sql data into xml  format

select * from employee for xml path('Node')

Result

<Node>

<EmpId>1</EmpId>

<Name>Kush Tiwari</Name>

<Gender>M</Gender>

<Salary>123456</Salary>

<Doj>1987-09-09</Doj>

</Node>

<Node>

<EmpId>2</EmpId>

<Name>Suresh Singh</Name>

<Gender>M</Gender>

<Salary>123456</Salary>

<Doj>1987-09-09</Doj>

</Node>

--how to get sql data into json format.............

select '[' + STUFF((

select

',

{

"EmpId":' + cast(EmpId as varchar(5))+ ',

"Name" :"' + Name + '"'+',

"Gender":"' + Gender + '"'+',

"Salary":' + cast(Salary as varchar(10)) +',

"Doj":"' + cast(Doj as varchar(20)) + '"'+',

}'

from employee

for xml path(''), type

).value('.', 'varchar(max)'), 1, 1, '') + ']'

Result

[{

"EmpId":1,

"Name" :"Kush Tiwari",

"Gender":"M",

"Salary":123456,

"Doj":"1987-09-09",

},

{

"EmpId":2,

"Name" :"Suresh Singh",

"Gender":"M",

"Salary":123456,

"Doj":"1987-09-09",

}]

Read More

How to get last identity column value from table. Different between @@IDENTITY, SCOPE_IDENTITY(),IDENT_CURRENT

How to get last identity column value from table. Different between @@IDENTITY, SCOPE_IDENTITY(),IDENT_CURRENT

	Session	Scope
@@IDENTITY	Same Session	Any  scope value
SCOPE_IDENTITY()	Same Session	Local scope value
IDENT_CURRENT()	May be different	Depends on table name passed in parameter

Discription:

SELECT @@IDENTITY: returns the last identity value generated for any table in the current session, across all scopes(i.e. global scope).

SELECT IDENT_CURRENT : returns the last identity value generated for any table in the current session and the current scope(i.e. local scope).

SELECT SCOPE_IDENTITY(): returns the last identity value generated for a specific table in any session and any scope(i.e. global scope).

use Test

--First Table

create table Test1

(

Id int identity(1,1),

Name varchar(50)

)

--Second Table

create table Test2

(

Id int identity(1,1),

Name varchar(50)

)

insert into Test1 values('Kush')

insert into Test1 values('Lav')

select * from Test1

--How to get last identity column value from table

select SCOPE_IDENTITY()

--Result=   2

select SCOPE_IDENTITY()

select @@IDENTITY

--Result=   2,2

--create  trigger  on Test1 Table

create trigger tr1 on Test1

after insert

as

begin

insert into Test2 values('yyyyy')

end

--select  record  from both table

select *  from test1 -- 2 record

select *  from test2  -- blank

insert  into test1 values ('Dinesh singh')

select SCOPE_IDENTITY()

select @@IDENTITY

--Result 3 ,1

Note :  SCOPE_IDENTITY(),@@IDENTITY both are return last generated column from table but  different  b/w SCOPE_IDENTITY() work in with same connection or same session and same scope  , @@IDENTITY work in same session and across any scope 

select SCOPE_IDENTITY()

select @@IDENTITY

SELECT IDENT_CURRENT('Test1')

SELECT IDENT_CURRENT('Test2');

--Result – 3 1 3 1

Read More

What is JqGrid and implementing JqGrid in ASP.NET MVC

What is JqGrid and  implementing JqGrid in ASP.NET MVC

JqGrid  is an Ajax-enabled JavaScript control that provides solutions for representing and manipulating tabular data on the web. Since the grid is a client-side solution loading data dynamically through Ajax callbacks, it can be integrated with any server-side technology; PHP, ASP, JSP/Java Servlets, ColdFusion, Perl, etc.

-- Sql Query........................................

create database test

use test

create table Employee

(

EmpId int primary key,

Name varchar(50),

Gender varchar(50),

Salary int,

Doj date

)

First we  will take  Asp.Net Mvc Project  and right  click on solution Explore add JQuery.jqGrid  from NuGet  like  given below picture 

 after  we  will  work on Default Controller which name  HomeController

Code  for HomeController..........................................

 using System.Web.Mvc;

using JqGridConcept.Models;

using System;

using System.Linq;

using System.Data.Entity;

namespace JqGridConcept.Controllers

{

public class HomeController : Controller

{

// GET: Home

public ActionResult Index()

{

return View();

}

testEntities database = new testEntities();

// code  for get Employee Data  from table..................

public JsonResult GetEmployees(string sidx, string sort, int page, int rows, bool _search, string searchField, string searchOper, string searchString)

{

sort = (sort == null) ? "" : sort;

int pageIndex = Convert.ToInt32(page) - 1;

int pageSize = rows;

var empList = database.Employees.Select(emp => new

{

emp.EmpId,

emp.Name,

emp.Salary,

emp.Gender,

emp.Doj

});

if (_search)

{

switch (searchField)

{

case "EmpId":

empList = empList.Where(t => t.EmpId.ToString().Contains(searchString));

break;

case "Name":

empList = empList.Where(m => m.Name.Contains(searchString));

break;

case "Gender":

empList = empList.Where(m => m.Gender.Contains(searchString));

break;

case "Salary":

empList = empList.Where(m => m.Salary.ToString().Contains(searchString));

break;

case "Doj":

empList = empList.Where(m => m.Doj.ToString().Contains(searchString));

break;

}

}

int totalRecords = empList.Count();

var totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows);

if (sort.ToUpper() == "DESC")

{

empList = empList.OrderByDescending(t => t.Name);

empList = empList.Skip(pageIndex * pageSize).Take(pageSize);

}

else

{

empList = empList.OrderBy(t => t.Name);

empList = empList.Skip(pageIndex * pageSize).Take(pageSize);

}

var jsonData = new

{

total = totalPages,

page,

records = totalRecords,

rows = empList

};

return Json(jsonData, JsonRequestBehavior.AllowGet);

}

// code  for insert   data  in Employee table................

[HttpPost]

//public string Create([Bind(Exclude = "EmpId")] Employee Model)

public string Create(Employee Model)

{

string msg;

try

{

if (ModelState.IsValid)

{

// Model.EmpId = Guid.NewGuid().ToString();

database.Employees.Add(Model);

database.SaveChanges();

msg = "Saved Successfully";

}

else

{

msg = "Validation data not successfully";

}

}

catch (Exception ex)

{

msg = "Error occured:" + ex.Message;

}

return msg;

}

// code  for update   data  in Employee table...............

public string Edit(Employee Model)

{

string msg;

try

{

if (ModelState.IsValid)

{

database.Entry(Model).State = EntityState.Modified;

database.SaveChanges();

msg = "Update  Successfully";

}

else

{

msg = "Validation data not successfully";

}

}

catch (Exception ex)

{

msg = "Error occured:" + ex.Message;

}

return msg;

}

// code  for delete   data  in Employee table

public string Delete(string Id)

{

int empId = Convert.ToInt32(Id);

var result = database.Employees.Where(m => m.EmpId == empId).FirstOrDefault();

if (result != null)

{

database.Employees.Remove(result);

database.SaveChanges();

return "Deleted successfully";

}

else

{

return "Some Error";

}

}

}

}

Code  for Index.cshtml..........................................

@{

Layout = null;

}

<!DOCTYPE html>

<html>

<head>

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

<title>Index</title>

<link href="~/Content/themes/base/minified/jquery-ui.min.css" rel="stylesheet" />

<link href="~/Content/jquery.jqGrid/ui.jqgrid.css" rel="stylesheet" />

<script src="~/Scripts/jquery-2.2.3.min.js"></script>

<script src="~/Scripts/i18n/grid.locale-en.js"></script>

<script src="~/Scripts/jquery-ui-1.10.0.js"></script>

@*<script src="~/Scripts/jquery-ui-1.10.4.js"></script>*@

<script src="~/Scripts/jquery.jqGrid.min.js"></script>

<script type="text/javascript">

$(function ()

{

$("#jqGrid").jqGrid(

{

url: "/Home/GetEmployees",

datatype: 'json',

mtype: 'Get',

colNames: ['EmpId', 'Name','Gender', 'Salary', 'Joining Date'],

colModel: [

//{ key: true, hidden: true, name: 'EmpId', index: 'EmpId', editable: true },

{ key: true,  name: 'EmpId', index: 'EmpId', editable: true },

{ key: false, name: 'Name', index: 'Name', editable: true },

{ key: false, name: 'Gender', index: 'Gender', editable: true, edittype: 'select', editoptions: { value: { 'M': 'Male', 'F': 'Female', 'N': 'None' } } },

{ key: false, name: 'Salary', index: 'Salary', editable: true },

{ key: false, name: 'Doj', index: 'Doj', editable: true, formatter: 'date', formatoptions: { newformat: 'd/m/Y' } }],

pager: jQuery('#jqControls'),

rowNum: 10,

rowList: [10, 20, 30, 40, 50],

height: '100%',

viewrecords: true,

caption: 'Employee Records',

emptyrecords: 'No Employee Records are Available to Display',

jsonReader:

{

root: "rows",

page: "page",

total: "total",

records: "records",

repeatitems: false,

Id: "0"

},

autowidth: true,

multiselect: false

}).navGrid('#jqControls', {

edit: true, add: true, del: true, search: true,

searchtext: "Search Employee", refresh: true

},

{

zIndex: 100,

url: '/Home/Edit',

closeOnEscape: true,

closeAfterEdit: true,

recreateForm: true,

afterComplete: function (response) {

if (response.responseText) {

alert(response.responseText);

}

}

},

{

zIndex: 100,

url: "/Home/Create",

closeOnEscape: true,

closeAfterAdd: true,

afterComplete: function (response) {

if (response.responseText) {

alert(response.responseText);

}

}

},

{

zIndex: 100,

url: "/Home/Delete",

closeOnEscape: true,

closeAfterDelete: true,

recreateForm: true,

msg: "Are you sure you want to delete Employee... ? ",

afterComplete: function (response) {

if (response.responseText) {

alert(response.responseText);

}

}

},

{

zIndex: 100,

caption: "Search Employee",

sopt: ['cn']

});

});

</script>

</head>

<body>

<h2>Employee Information</h2>

<div>

<table id="jqGrid"></table>

<div id="jqControls"></div>

</div>

</body>

</html>

Result

Read More