SQL using sp_HelpText to view a stored procedure on a linked server

EXEC  [LinkedServerName].[DatabaseName].dbo.sp_HelpText 'storedProcName'
Advertisements

Generic ajax POST and GET method in MVC and Jquery

//POST method
var ACTIVE_REQ_CNT = 0;
function fnCallAjaxHttpPostEvent(url, postData, isAsync, showLoader, successCallback) {
 var args = Array.prototype.slice.call(arguments).slice(5);
 return $.ajax({
 async: isAsync,
 type: "POST",
 contentType: "application/json",
 url: url,
 cache: false,
 data: JSON.stringify(postData),
 beforeSend: function () {
 if (showLoader) {
 ACTIVE_REQ_CNT++;
 fnShowWaitImage();
 }
 },
 success: function (data, textStatus, jqXHR) {
 if (fnScanResponseForSessionTimeOut(jqXHR)) {
 fnSessionTimeOutHandler();
 } else {
 var callbackArgs = [];
 callbackArgs.push(data)
 callbackArgs = callbackArgs.concat(args);
 try {
 successCallback.apply(this, callbackArgs);
 } catch (ex) {
 console.error(ex);
 }
 }
 },
 failure: function (response) {
 console.error(response);
 },
 complete: function () {
 if (showLoader) {
 ACTIVE_REQ_CNT--;
 ACTIVE_REQ_CNT === 0 && fnHideWaitImage();
 }
 }
 });
}



//How to use POST method 
fnCallAjaxHttpPostEvent('@Url.Action("ActionName", "ControllerName")', paramModel , true, true, fnSaveSuccess, popupObj);

function fnSaveSuccess(data, popupObj) {
}

Here, popupObj is the extra parameter that can be fetched in successCallback function.



//GET method
var ACTIVE_REQ_CNT = 0;
function fnCallAjaxHttpGetEvent(url, param, isAsync, showLoader, successCallback) {
 var args = Array.prototype.slice.call(arguments).slice(5);
 return $.ajax({
 async: isAsync,
 type: "GET",
 contentType: "application/json",
 url: url,
 cache: false,
 data: param,
 beforeSend: function () {
 if (showLoader) {
 ACTIVE_REQ_CNT++;
 fnShowWaitImage();
 }
 },
 success: function (data, textStatus, jqXHR) {
 if (fnScanResponseForSessionTimeOut(jqXHR)) {
 fnSessionTimeOutHandler();
 } else {
 var callbackArgs = [];
 callbackArgs.push(data)
 callbackArgs = callbackArgs.concat(args);
 try {
 successCallback.apply(this, callbackArgs);
 } catch (ex) {
 console.error(ex);
 }
 }
 },
 failure: function (response) {
 console.error(response);
 },
 complete: function () {
 if (showLoader) {
 ACTIVE_REQ_CNT--;
 ACTIVE_REQ_CNT === 0 && fnHideWaitImage();
 }
 }
 });
}


//How to use GET method 

function fnGetData() {
 return fnCallAjaxHttpGetEvent('@Url.Action("ActionName", "ControllerName")', { paramId: paramId}, true, true, fnBindSucessFunction);
 }

function fnBindSuccessFunction(result){
  debugger;
}

Auditing database table data changes using Temporal Table in SQL Server 2016

Temporal Tables are generally useful in scenarios that require tracking history of data changes.

  • Enabling system-versioning on a new table for data audit
Use [NORTHWND]
CREATE TABLE Employee 
( 
 [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED 
 , [Name] nvarchar(100) NOT NULL 
 , [Position] varchar(100) NOT NULL 
 , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START 
 , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END 
 , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 
 ) 
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

 You can see EmployeeHistory Table is created with same columns

EmployeeHistory Table has no records initially.

Now Add records in Employee Table  and Update record with EmployeeId = 1.

--Insert records 
INSERT dbo.Employee(EmployeeID, Name, Position)
  VALUES
        (1,N'Dhaval','Software Developer'),
        (2,N'Yagnik','Senior Software Developer'),
        (3,N'Kaushal','Database Analyst');

--Update records
Update Employee Set Name = 'Dhaval Panchal ' Where EmployeeID = 1

 

There is a record inserted in EmployeeHistory Table with row started time and row ended time.

Nested If else conditions in Kendo-UI Grid ClientTemplate

Standard Nested If else statement/ syntax used in Kendo-ui Grid

 

columns.Bound(c => c.ItemId)
.ClientTemplate(
“# if (condition-1) { #” +
“# if(condition-2) { #” +
“content-one”
“# } #” +
“#} else { #” +
“# if(condition-2) {#” +
“content-two”
“# } #” +
“# } #”
)

 

There are three ways to use the hash syntax:

  1. Render values as HTML: #= #.
  2. Use HTML encoding to display values: #: #.
  3. Execute arbitrary JavaScript code: # if (true) { # ... non-script content here ... # } #.

C# : Convert model class from database table using stored procedure

declare @TableName sysname = 'TableName'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'

select @Result = @Result + '
    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
    select 
        replace(col.name, ' ', '_') ColumnName,
        column_id ColumnId,
        case typ.name 
            when 'bigint' then 'long'
            when 'binary' then 'byte[]'
            when 'bit' then 'bool'
            when 'char' then 'string'
            when 'date' then 'DateTime'
            when 'datetime' then 'DateTime'
            when 'datetime2' then 'DateTime'
            when 'datetimeoffset' then 'DateTimeOffset'
            when 'decimal' then 'decimal'
            when 'float' then 'float'
            when 'image' then 'byte[]'
            when 'int' then 'int'
            when 'money' then 'decimal'
            when 'nchar' then 'string'
            when 'ntext' then 'string'
            when 'numeric' then 'decimal'
            when 'nvarchar' then 'string'
            when 'real' then 'double'
            when 'smalldatetime' then 'DateTime'
            when 'smallint' then 'short'
            when 'smallmoney' then 'decimal'
            when 'text' then 'string'
            when 'time' then 'TimeSpan'
            when 'timestamp' then 'DateTime'
            when 'tinyint' then 'byte'
            when 'uniqueidentifier' then 'Guid'
            when 'varbinary' then 'byte[]'
            when 'varchar' then 'string'
            else 'UNKNOWN_' + typ.name
        end ColumnType,
        case 
            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 
            then '?' 
            else '' 
        end NullableSign
    from sys.columns col
        join sys.types typ on
            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
    where object_id = object_id(@TableName)
) t
order by ColumnId

set @Result = @Result  + '
}'

print @Result

Convert DataTable to List in c#

DataTable dt = ‘…some service called to fill this datatable….’
List<DemoModel> demoDetails = new List<DemoModel>();
demoDetails = ConvertDataTable<DemoModel>(dt);

 

public static List<T> ConvertDataTable<T>(DataTable dt)
{
List<T> data = new List<T>();
foreach (DataRow row in dt.Rows)
{
T item = GetItem<T>(row);
data.Add(item);
}
return data;
}

 

private static T GetItem<T>(DataRow dr)
{
Type temp = typeof(T);
T obj = Activator.CreateInstance<T>();

foreach (DataColumn column in dr.Table.Columns)
{
foreach (PropertyInfo pro in temp.GetProperties())
{
if (pro.Name == column.ColumnName)
pro.SetValue(obj, dr[column.ColumnName], null);
else
continue;
}
}
return obj;
}