MEAN Stack and .Net (Asp.Net MVC,SQL Server ,WCF) Web Development

How to Create Cursor

declare @tempt table

(

ID int null,

Name varchar(100) null

)

DECLARE @vendor_id int, @vendor_name nvarchar(50),

@message varchar(80), @product nvarchar(50);

PRINT '-------- Vendor Products Report --------';

-- Declare Cursor – untill it deallocate

-- You Con’t declare another cursor with same name.

DECLARE vendor_cursor CURSOR FOR

SELECT ID,Ag_Fname

from AgentMaster

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor

INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @message = '----- Vendor: ' +

@vendor_name

PRINT @message

insert INTO @tempt values (@vendor_id, @vendor_name)

FETCH NEXT FROM vendor_cursor

INTO @vendor_id, @vendor_name

END

CLOSE vendor_cursor;

select * from @tempt

-- Deallocate Cursor

DEALLOCATE vendor_cursor;

Trigger is procedural code that is automatically executed in response to certain events on a specific table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

Triggers are of 3 types in SQL Server 2005:

1. DML Triggers
- AFTER Triggers
- INSTEAD OF Triggers { INSERT, UPDATE, and DELETE }
2. DDL Triggers {if any schema change.}
3. CLR Triggers

Create trigger Delete_TrigerName

on ReferanceTable

for Delete

--Checking for Table exist.

--if Table not exist then create a table with same schema of operation table

if NOT EXISTS(SELECT * FROM information_schema.tables

WHERE TABLE_CATALOG = 'DATABASE-NAME'

AND table_name = 'Backup-TABLE-NAME')

begin

-- Copy full table with creating table of copied table schema

select * into Backup-TABLE-NAME from deleted

--Set Identity column Off. Otherwise we will not able to track the previous position of table.

SET IDENTITY_INSERT [Backup-TABLE-NAME ] off

end

else

begin

INSERT INTO Backup-TABLE-NAME ([Column1],[ Column2],[ Column3])

SELECT [Column1],[ Column2],[ Column3]

FROM deleted

end

--select * from Backup-TABLE-NAME

--select * from AgentMaster

--delete from AgentMaster where Ag_ApplNo=22

Pan Number format and Duplication entry validation in KendoUi

Checking from database using json and Controler's action where controler is Validation and Action is CheckPanNo . Then we check format using test function. and error message set to messages section of kendoValidator function.

Java Script

///Pan No Duplication checking

var validatable = $("#Pan").kendoValidator({

onfocusout: true,

onkeyup: true,

rules: {

PanNo: function (input) {

$.post("/Validation/CheckPanNo", { PanNo: $("#Pan").val() }, function (data) { b1 = data; })

return b1;

PanFormat: function (input) {

// return validatePanCard('Pan');

var value = input.val();

var regex1 = /^[A-Z]{5}\d{4}[A-Z]{1}$/;

if (!regex1.test(value) || value.length != 10) {

return false;

}

return true;

}

messages: {

PanNo: "Already Exist",

PanFormat: "Not a currect format"

}

}).data("kendoValidator");

Action

[HttpPost]

public JsonResult CheckPanNo(string PanNo)

{

bool IsOk = projectRepository.CheckPanNumber(PanNo);

return Json(IsOk, JsonRequestBehavior.AllowGet);

}

How to Binding Dropdown Using Knockout JS

Using jquery, we access date from Action called Ko_ShowBranch in a controller caller ListObject. This action access data from database using a function called ShowAll_Branch() and to return Json result in text and value property we write below line this way.

ShowAll_Branch().Select(p => new { text =p.BranchName , value = p.ID.ToString() });

In Html Value property and Text property are set using optionsValue: and optionsText:

Javascript

viewModel = {

Branch: ko.observableArray()

};

$(function () {

$.getJSON('http://localhost:3400/ListObject/Ko_ShowBranch', null,

function (response) {

viewModel.Branch(response);

});

ko.applyBindings(viewModel);

});

Html

<select data-bind="options: Branch, optionsCaption: 'Choose Branch...',

optionsValue: function(item) { return item.value; },

optionsText: function(item) { return item.text; }" id="Branch" name="Branch"> </select>

Controler

public JsonResult Ko_ShowBranch()

{

var result = projectRepository.ShowAll_Branch().Select(p => new { text = p.BranchName, value = p.ID.ToString() });

return Json(result, JsonRequestBehavior.AllowGet);

}

Kendo - KnockOut Grid Bind

HTML

JavaScript

$(document).ready(function () {

$.ajaxSetup({ async: false });

//var Data= [{ "ShipCity": "Kolkata", "ShipName": "Pinaki GROUP" }, { "ShipCity": "Mumbai", "ShipName": "Pinaki SOL"}];

var Data = null;

function DataFunction() {

$.post("/BkCode/BookResult", null, function (d) {

Data = d;

});

return Data;

}

var ViewModel = function () {

this.gridConfig = {

data: null,

dataSource: {

type: "odata",

data: DataFunction(), //This is use to access data from database using jQuery

// and return data in Json format

//data: Data,

schema: {

model: {

fields: {

ShipCity: {

type: "string"

ShipName: {

type: "string"

}

pageSize: 10 //,

//serverPaging: true,

//serverFiltering: true,

//serverSorting: true

columns: [{

field: "ShipCity",

title: "Id",

filterable: false

{

field: "ShipName",

title: "Company Name",

filterable: true

{

command: ["edit", "destroy"],

filterable: false

}] ,

height: 550,

scrollable: false,

pageable: true,

sortable: true,

groupable: true,

filterable: true,

editable: "inline", //popup

save: function () {

this.refresh();

}

};

ko.applyBindings(new ViewModel());

})

</script>

C#

public JsonResult BookResult()

{

var result = from s in bk.GetPrmCompanies()

select new book{

ShipCity = s.Id,

ShipName = s.CoNames

};

DB.Configuration.ProxyCreationEnabled = false;

return Json(result, JsonRequestBehavior.AllowGet); //Content(sd, "text/xml");

}

MEAN Stack and .Net (Asp.Net MVC,SQL Server ,WCF) Web Development - Professional Development Help

Pages

App Installs

Wednesday, May 22, 2013