SQL Stored Procedure:
create PROCEDURE [dbo].[SP_Name]
@TableName nvarchar(max)=NULL,
@FromNumber nvarchar(max)=NULL,
@ToNumber nvarchar(max)=NULL,
@SQLSortString nvarchar(max)=NULL,
@SQLFilterString nvarchar(max)=NULL,
@TotalCount int OUTPUT,
@SBGID nvarchar(50)=NULL --Any extra parameter for where clouse
AS
BEGIN
DECLARE @SQLQuery nvarchar(max);
SET @SQLQuery=N'WITH Data AS
(
SELECT top 100 percent [ID],[Name] , * ';
IF @SQLSortString IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'ROW_NUMBER() OVER (ORDER BY '+@SQLSortString+') AS ''RowNumber'''
END
ELSE
BEGIN
SET @SQLQuery=@SQLQuery+N'ROW_NUMBER() OVER (ORDER BY ID) AS ''RowNumber'''
END
IF @SQLFilterString IS NOT NULL AND @SBGID IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName+' WHERE '+@SQLFilterString+' AND SBGID='''+@SBGID+''''
END
Else
IF @SQLFilterString IS NOT NULL AND @SBGID IS NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName+' WHERE '+@SQLFilterString+''
END
Else
IF @SQLFilterString IS NULL AND @SBGID IS Not NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName+' Where SBGID='''+@SBGID+''''
END
ELSE
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName --+' Where SBGID='''+@SBGID+''''
END
SET @SQLQuery=@SQLQuery+')
SELECT *
FROM Data'
IF @FromNumber IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N' WHERE RowNumber BETWEEN '+@FromNumber+' AND '+@ToNumber --BETWEEN is inclusive
END
Else
BEGIN
SET @SQLQuery=@SQLQuery--For Excel Export
END
PRINT @SQLQuery
EXECUTE sp_executesql @SQLQuery;
IF @SQLFilterString IS NOT NULL AND @SBGID IS NOT NULL
BEGIN
DECLARE @query NVARCHAR(1000)=N'SELECT @TotalCount=COUNT(*) from '+@TableName+' WHERE ' +@SQLFilterString+' AND SBGID='''+@SBGID+''''
DECLARE @TempCount varchar(20)
set @TempCount='0'
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
ELSE
IF @SQLFilterString IS NOT NULL AND @SBGID IS NULL
BEGIN
SET @query =N'SELECT @TotalCount=COUNT(*) from '+@TableName+' WHERE ' +@SQLFilterString
set @TempCount='0'
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
ELSE
IF @SQLFilterString IS NULL AND @SBGID IS Not NULL
BEGIN
SET @query =N'SELECT @TotalCount=COUNT(*) from '+@TableName +' Where SBGID='''+@SBGID+''''
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
ELSE
BEGIN
SET @query =N'SELECT @TotalCount=COUNT(*) from '+@TableName --+' Where SBGID='''+@SBGID+''''
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
PRINT @query
PRINT @TotalCount
END
View Code:
<script type="text/javascript">
$(document).ready(function () {
function getParam(name) {
name = name.replace(/[\[]/, '\\[').replace(/[\]]/, '\\]');
var regex = new RegExp('[\\?&]' + name + '=([^&#]*)');
var results = regex.exec(location.search);
return results === null ? '' : decodeURIComponent(results[1].replace(/\+/g, ' '));
};
createGrid('View_PSK_AllSupplierRiskGrading', getParam('SBGID')); //Pass the table Name
document.getElementById('SBGName').innerHTML = getParam('SBGID');
});
function createGrid(tableName, SBGID) {
var ApiUrl = window.location.host
//alert(ApiUrl);
if (ApiUrl === "localhost:55860") {
ApiUrl = "http://localhost:55860";
}
else if (ApiUrl === "hic004994") {
ApiUrl = window.location.protocol + "//" + ApiUrl + "/PSK"
//ApiUrl = window.location.protocol + "//" + ApiUrl
//alert(ApiUrl);
}
else {
//ApiUrl = window.location.protocol + "//" + ApiUrl + "/PSK"
ApiUrl = window.location.protocol + "//" + ApiUrl
// alert(ApiUrl);
}
$("#EmpGrid").kendoGrid({
dataSource: {
type: "json",
transport: {
read: {
url: ApiUrl+'/ControlerName/GetData/',
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8"
},
parameterMap: function (data, type) {
return JSON.stringify({
SBGID: SBGID,
tableName: tableName,
page: data.page,
pageSize: data.pageSize,
skip: data.skip,
take: data.take,
sorting: data.sort === undefined ? null : data.sort,
filter: data.filter === undefined ? null : data.filter
});
}
},
schema: {
model: {
fields: {
ID: { type: "string" },
SupplierID: { type: "string" },
SupplierName: { type: "string" },
SBGID: { type: "string" },
}
},
data: function (data) {
return data.Data1;
},
total: function (data) {
return JSON.stringify(data.Total);
}
},
pageSize: 10,
serverPaging: true,
serverFiltering: true,
serverSorting: true
},
//noRecords: true,
groupable: true,
filterable: true,
sortable: {
mode: "multiple"
},
pageable: {
pageSizes: [5, 10, 20, 100],
refresh: false
},
columns:
[
{
field: "SupplierID",
title: "Supplier ID",
locked: true,
lockable: false,
width: 130,
headerAttributes: { style: "text-align:center" },
attributes: { style: "text-align:center" },
template: '# if( SupplierID === "Not Available") {# #: SupplierID # #} else {# <b style="color:blue;"> <u><a target="_blank" href="' + ApiUrl + '/SupplierProfile/Profile?supid=#=SupplierID#&SBGID=#=SBGID#">#=SupplierID#</a></u></b> #} #',
},
{
field: "SupplierName",
title: "Supplier Name",
locked: true,
lockable: false,
headerAttributes: { style: "text-align:center" },
attributes: { style: "text-align:left" },
width: 300
},
],
height: "500px",
selectable: "multiple cell",
toolbar: [{ name: 'excel', text: 'Quick Excel Export' }, { template: kendo.template($("#template").html()) }],
excel: {
fileName: "Data List.xlsx",
filterable: true,
allPages: false
},
dataBound: gridDataBound,
scroll: true,
resizable: true,
}).data("kendoGrid");
function gridDataBound(e) {
var grid = e.sender;
var filter = "No";
if (this.dataSource.filter()) {
filter = "Yes";
}
}
/////////////////
};
}
</script>
@using (Html.BeginForm("Export", "ControllerName", FormMethod.Post))
{
<input class="btn btn-primary" type="submit" value="All Data Export to Excel" />
<label>Last Updated Date : <b>@ViewData["VDRptDate"]</b></label>
<label><b style="background-color:yellow">Note: Click on the SupplierID to see the Supplier Profile View.</b></label>
}
<div id="KendoGrdView">
<div id="EmpGrid">
</div>
</div>
create PROCEDURE [dbo].[SP_Name]
@TableName nvarchar(max)=NULL,
@FromNumber nvarchar(max)=NULL,
@ToNumber nvarchar(max)=NULL,
@SQLSortString nvarchar(max)=NULL,
@SQLFilterString nvarchar(max)=NULL,
@TotalCount int OUTPUT,
@SBGID nvarchar(50)=NULL --Any extra parameter for where clouse
AS
BEGIN
DECLARE @SQLQuery nvarchar(max);
SET @SQLQuery=N'WITH Data AS
(
SELECT top 100 percent [ID],[Name] , * ';
IF @SQLSortString IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'ROW_NUMBER() OVER (ORDER BY '+@SQLSortString+') AS ''RowNumber'''
END
ELSE
BEGIN
SET @SQLQuery=@SQLQuery+N'ROW_NUMBER() OVER (ORDER BY ID) AS ''RowNumber'''
END
IF @SQLFilterString IS NOT NULL AND @SBGID IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName+' WHERE '+@SQLFilterString+' AND SBGID='''+@SBGID+''''
END
Else
IF @SQLFilterString IS NOT NULL AND @SBGID IS NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName+' WHERE '+@SQLFilterString+''
END
Else
IF @SQLFilterString IS NULL AND @SBGID IS Not NULL
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName+' Where SBGID='''+@SBGID+''''
END
ELSE
BEGIN
SET @SQLQuery=@SQLQuery+N'FROM '+@TableName --+' Where SBGID='''+@SBGID+''''
END
SET @SQLQuery=@SQLQuery+')
SELECT *
FROM Data'
IF @FromNumber IS NOT NULL
BEGIN
SET @SQLQuery=@SQLQuery+N' WHERE RowNumber BETWEEN '+@FromNumber+' AND '+@ToNumber --BETWEEN is inclusive
END
Else
BEGIN
SET @SQLQuery=@SQLQuery--For Excel Export
END
PRINT @SQLQuery
EXECUTE sp_executesql @SQLQuery;
IF @SQLFilterString IS NOT NULL AND @SBGID IS NOT NULL
BEGIN
DECLARE @query NVARCHAR(1000)=N'SELECT @TotalCount=COUNT(*) from '+@TableName+' WHERE ' +@SQLFilterString+' AND SBGID='''+@SBGID+''''
DECLARE @TempCount varchar(20)
set @TempCount='0'
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
ELSE
IF @SQLFilterString IS NOT NULL AND @SBGID IS NULL
BEGIN
SET @query =N'SELECT @TotalCount=COUNT(*) from '+@TableName+' WHERE ' +@SQLFilterString
set @TempCount='0'
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
ELSE
IF @SQLFilterString IS NULL AND @SBGID IS Not NULL
BEGIN
SET @query =N'SELECT @TotalCount=COUNT(*) from '+@TableName +' Where SBGID='''+@SBGID+''''
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
ELSE
BEGIN
SET @query =N'SELECT @TotalCount=COUNT(*) from '+@TableName --+' Where SBGID='''+@SBGID+''''
EXECUTE sp_executesql @query,N'@TotalCount varchar(20) out',@TempCount out
SET @TotalCount = @TempCount
END
PRINT @query
PRINT @TotalCount
END
------------------------------------------------------------------------------------------------------------------
View Code:
<script type="text/javascript">
$(document).ready(function () {
function getParam(name) {
name = name.replace(/[\[]/, '\\[').replace(/[\]]/, '\\]');
var regex = new RegExp('[\\?&]' + name + '=([^&#]*)');
var results = regex.exec(location.search);
return results === null ? '' : decodeURIComponent(results[1].replace(/\+/g, ' '));
};
createGrid('View_PSK_AllSupplierRiskGrading', getParam('SBGID')); //Pass the table Name
document.getElementById('SBGName').innerHTML = getParam('SBGID');
});
function createGrid(tableName, SBGID) {
var ApiUrl = window.location.host
//alert(ApiUrl);
if (ApiUrl === "localhost:55860") {
ApiUrl = "http://localhost:55860";
}
else if (ApiUrl === "hic004994") {
ApiUrl = window.location.protocol + "//" + ApiUrl + "/PSK"
//ApiUrl = window.location.protocol + "//" + ApiUrl
//alert(ApiUrl);
}
else {
//ApiUrl = window.location.protocol + "//" + ApiUrl + "/PSK"
ApiUrl = window.location.protocol + "//" + ApiUrl
// alert(ApiUrl);
}
$("#EmpGrid").kendoGrid({
dataSource: {
type: "json",
transport: {
read: {
url: ApiUrl+'/ControlerName/GetData/',
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8"
},
parameterMap: function (data, type) {
return JSON.stringify({
SBGID: SBGID,
tableName: tableName,
page: data.page,
pageSize: data.pageSize,
skip: data.skip,
take: data.take,
sorting: data.sort === undefined ? null : data.sort,
filter: data.filter === undefined ? null : data.filter
});
}
},
schema: {
model: {
fields: {
ID: { type: "string" },
SupplierID: { type: "string" },
SupplierName: { type: "string" },
SBGID: { type: "string" },
}
},
data: function (data) {
return data.Data1;
},
total: function (data) {
return JSON.stringify(data.Total);
}
},
pageSize: 10,
serverPaging: true,
serverFiltering: true,
serverSorting: true
},
//noRecords: true,
groupable: true,
filterable: true,
sortable: {
mode: "multiple"
},
pageable: {
pageSizes: [5, 10, 20, 100],
refresh: false
},
columns:
[
{
field: "SupplierID",
title: "Supplier ID",
locked: true,
lockable: false,
width: 130,
headerAttributes: { style: "text-align:center" },
attributes: { style: "text-align:center" },
template: '# if( SupplierID === "Not Available") {# #: SupplierID # #} else {# <b style="color:blue;"> <u><a target="_blank" href="' + ApiUrl + '/SupplierProfile/Profile?supid=#=SupplierID#&SBGID=#=SBGID#">#=SupplierID#</a></u></b> #} #',
},
{
field: "SupplierName",
title: "Supplier Name",
locked: true,
lockable: false,
headerAttributes: { style: "text-align:center" },
attributes: { style: "text-align:left" },
width: 300
},
],
height: "500px",
selectable: "multiple cell",
toolbar: [{ name: 'excel', text: 'Quick Excel Export' }, { template: kendo.template($("#template").html()) }],
excel: {
fileName: "Data List.xlsx",
filterable: true,
allPages: false
},
dataBound: gridDataBound,
scroll: true,
resizable: true,
}).data("kendoGrid");
function gridDataBound(e) {
var grid = e.sender;
var filter = "No";
if (this.dataSource.filter()) {
filter = "Yes";
}
}
/////////////////
};
}
</script>
@using (Html.BeginForm("Export", "ControllerName", FormMethod.Post))
{
<input class="btn btn-primary" type="submit" value="All Data Export to Excel" />
<label>Last Updated Date : <b>@ViewData["VDRptDate"]</b></label>
<label><b style="background-color:yellow">Note: Click on the SupplierID to see the Supplier Profile View.</b></label>
}
<div id="KendoGrdView">
<div id="EmpGrid">
</div>
</div>
-----------------------------------------------------------
Controller Code:
public string GetData(string tableName, string SBGID, int page, int pageSize, int skip, int take, List<SortDescription> sorting, FilterContainer filter)
{
// string tableName1 = "PSK_Financial_Metrics"; int page = 1; int pageSize = 10; int skip = 0; int take = 10; List<SortDescription> sorting = null; FilterContainer filter = null;
try
{
DataTable newDt = new DataTable();
int from = skip + 1; //(page - 1) * pageSize + 1;
int to = take * page; // page * pageSize;
string sortingStr = "";
#region Sorting
if (sorting != null)
{
if (sorting.Count != 0)
{
for (int i = 0; i < sorting.Count; i++)
{
sortingStr += ", " + sorting[i].field + " " + sorting[i].dir;
}
}
}
#endregion
#region filtering
string filters = "";
string logic;
string condition = "";
int c = 1;
if (filter != null)
{
for (int i = 0; i < filter.filters.Count; i++)
{
logic = filter.logic;
if (filter.filters[i].@operator == "eq")
{
condition = " = '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "neq")
{
condition = " != '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "startswith")
{
condition = " Like '" + filter.filters[i].value + "%' ";
}
if (filter.filters[i].@operator == "contains")
{
condition = " Like '%" + filter.filters[i].value + "%' ";
}
if (filter.filters[i].@operator == "doesnotcontains")
{
condition = " Not Like '%" + filter.filters[i].value + "%' ";
}
if (filter.filters[i].@operator == "endswith")
{
condition = " Like '%" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "gte")
{
condition = " >= '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "gt")
{
condition = " > '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "lte")
{
condition = " <= '" + filter.filters[i].value + "' ";
}
if (filter.filters[i].@operator == "lt")
{
condition = "< '" + filter.filters[i].value + "' ";
}
filters += filter.filters[i].field + condition;
if (filter.filters.Count > c)
{
filters += logic;
filters += " ";
}
c++;
}
}
#endregion
sortingStr = sortingStr.TrimStart(',');
Dictionary<int, DataTable> dt = GetGridData(tableName, SBGID, from, to, sortingStr, filters);
newDt = dt.First().Value;
string data = ConvertJson.ConvertTable(newDt);
string sampledata = "{\"Data\":" + data + ",\"Total\":" + dt.First().Key + "}";
return "{\"Data1\":" + data + ",\"Total\":" + dt.First().Key + "}";
}
catch (Exception ex)
{
return "{\"Data1\":[],\"Total\":" + 0 + "}";
}
}
public static Dictionary<int, DataTable> GetGridData(string TableName, string SBGID, int FromNumber, int ToNumber, string OrderByStr, string FilterStr)
{
string connString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlDataReader rdr = null;
if (OrderByStr == "")
{
OrderByStr = null;
}
if (FilterStr == "")
{
FilterStr = null;
}
try
{
using (SqlConnection con = new SqlConnection(connString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("PSK_GETGRIDDATA", con))
{
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
tableName1 = TableName;
cmd.Parameters.AddWithValue("@TableName", TableName);
cmd.Parameters.AddWithValue("@FromNumber", FromNumber);
cmd.Parameters.AddWithValue("@ToNumber", ToNumber);
SBGID1 = SBGID;
cmd.Parameters.AddWithValue("@SBGID", SBGID);
if (OrderByStr != "")
{
OrderByStr1 = OrderByStr;
cmd.Parameters.AddWithValue("@SQLSortString", OrderByStr);
}
if (FilterStr != "")
{
FilterStr1 = FilterStr;
cmd.Parameters.AddWithValue("@SQLFilterString", FilterStr);
}
cmd.Parameters.Add("@TotalCount", SqlDbType.Int);
cmd.Parameters["@TotalCount"].Direction = ParameterDirection.Output;
// get query results
rdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(rdr);
int total = Convert.ToInt32(cmd.Parameters["@TotalCount"].Value);
Dictionary<int, DataTable> dictionary = new Dictionary<int, DataTable>();
dictionary.Add(total, dt);
return dictionary;
}
}
}
catch (Exception ex)
{
return new Dictionary<int, DataTable>();
}
}
public static class ConvertJson
{
public static string ConvertTable(DataTable table)
{
JsonSerializerSettings serializerSettings = new JsonSerializerSettings();
serializerSettings.Converters.Add(new DataTableConverter());
string jsonData = JsonConvert.SerializeObject(table, Formatting.None, serializerSettings);
return jsonData;
}
public static string ConvertToJson(object data)
{
JObject o = JObject.FromObject(new
{
Table = data
});
JsonSerializerSettings serializerSettings = new JsonSerializerSettings();
//serializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
string jsonData = JsonConvert.SerializeObject(data);
return jsonData;
}
public static string SerializeObject(object data)
{
return JsonConvert.SerializeObject(data);
}
}
public class SortDescription
{
public string field { get; set; }
public string dir { get; set; }
}
public class FilterContainer
{
public List<FilterDescription> filters { get; set; }
public string logic { get; set; }
}
public class FilterDescription
{
public string @operator { get; set; }
public string field { get; set; }
public string value { get; set; }
}
0 Comments