Introduction
Stored Procedures are faster as compared to LINQ query and they can take the full advantage of SQL features.
This article mainly focuses on Stored Procedure in SQL Server 2008/2012. We will use MVC framework along with angularJS for CRUD operation by the help of Stored Procedure. In this article following topics and features will be covered:
- How to create a Stored Procedure in SQL server 2008/2012
- CRUD using Stored Procedure in .NET MVC along with angularJS
- Stored Procedure for single object data
- Stored Procedure for list data (as XML data and Data Table)
- Transaction in Stored Procedure
Download Project Files
Setup Your Database
The following data table will be used to show how stored procedure work with your application.
dbo.t_Student
Here in the above tables all the fields are kept non nullable and the Identity Specification for the primary keys are auto incremented by 1 as shown below.
Stored Procedure Basics
Before creating your first procedure let's have some introduction about naming convention and format.
Since default database procedures uses sp_Procedure_Name as their naming convention so we will use it little bit differently to separate our procedures. We will use usp_ProcedureName as our user defined procedure. To pass a parameter in the procedure we have to use "@" sign before that parameter name and then declare its data type as given in the corresponding table. Below is a sample of how to create a procedure.
CREATE PROCEDURE usp_ProcedureName(
@parameter1Name parameter1Type, @parameter2Name parameter2Type, ...)
AS
BEGIN
SET NOCOUNT ON;
END
GO
Create Required Procedures (Single Object)
To create a procedure go to UniversityDB > Programmability > Stored Procedure as shown below.
Then right click to Stored Procedure folder and then click to Stored Procedure.... A script window will open as shown below and then all you need to do is write your desired procedure.
Just change the abobe default structure as below which is created to save student data (as a single object). After writing the below script just execute your script and you are good to go. Hey! your first procedure has been created. You will find all the user defined procedures in Stored Procedure folder. Below are the Save, Update, Delete, and Select procedure for single data object.
usp_SaveStudent
CREATE PROCEDURE usp_SaveStudent(
@firstName varchar(100), @lastName varchar(100), @email varchar(100),
@address varchar(150), @enrollDate datetime, @departmentId int)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO t_Student(Student_FirstName, Student_LastName, Student_Email, Student_Address,
Student_EnrollmentDate, Student_DepartmentId)
VALUES (@firstName, @lastName, @email, @address, @enrollDate, @departmentId)
END
GO
usp_UpdateStudent
CREATE PROCEDURE usp_UpdateStudent(
@id int, @firstName varchar(100), @lastName varchar(100), @email varchar(100),
@address varchar(150), @enrollDate datetime, @departmentId int)
AS
BEGIN
SET NOCOUNT ON;
UPDATE t_Student
SET Student_FirstName = @firstName, Student_LastName = @lastName, Student_Email = @email,
Student_Address = @address, Student_EnrollmentDate = @enrollDate, Student_DepartmentId = @departmentId
WHERE Student_Id = @id
END
GO
usp_DeleteStudent
CREATE PROCEDURE usp_DeleteStudent(@id int)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM t_Student WHERE Student_Id = @id
END
GO
usp_GetAllStudentData
CREATE PROCEDURE usp_GetAllStudentData
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM t_Student
END
GO
Create Required Procedures (Object List)
To save list data by stored procedure we need to create user defined table (not the actual database table, just a replica) first. After creating new data type we have to use that data type for the parameter we pass through procedure.
In this part we will create procedure to save list of data to the database by stored procedure. At first we will pass a XML data string to the procedure and then a Data Table from which INSERT query will save all the data.
usp_SaveStudentListFromXml
CREATE PROCEDURE usp_SaveStudentListFromXml(@xmlData xml)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO t_Student(Student_FirstName, Student_LastName, Student_Email, Student_Address,
Student_EnrollmentDate, Student_DepartmentId)
SELECT
std.query('Student_FirstName').value('.','varchar(100)') as LCAItem_LCAFNo,
std.query('Student_LastName').value('.','varchar(100)') as LCAItem_HSCode,
std.query('Student_Email').value('.','varchar(100)') as LCAItem_Unit,
std.query('Student_Address').value('.','varchar(150)') as LCAItem_Quantity,
std.query('Student_EnrollmentDate').value('.','datetime') as LCAItem_TotalPrice,
std.query('Student_DepartmentId').value('.','int') as LCAItem_UnitPrice
FROM @xmlData.nodes('/STUDENTS/STUDENT') as XMLData(std)
END
GO
usp_SaveStudentListFromDataTable
CREATE TYPE dbo.StudentType AS TABLE(
Student_FirstName varchar(100),
Student_LastName varchar(100),
Student_Email varchar(100),
Student_Address varchar(150),
Student_EnrollmentDate datetime,
Student_DepartmentId int
)
GO
CREATE PROCEDURE usp_SaveStudentListFromDataTable(@studentList dbo.StudentType readonly)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO t_Student(Student_FirstName, Student_LastName, Student_Email, Student_Address,
Student_EnrollmentDate, Student_DepartmentId)
SELECT Student_FirstName, Student_LastName, Student_Email, Student_Address,
Student_EnrollmentDate, Student_DepartmentId FROM @studentList
END
GO
For now these are the database elements we need to create our application. Some points will be described in the code when description needed to relate the database variables with the C# variables and code.
Working in MVC Application
- First create an empty MVC application (here I've worked on VS 2013 & .NET v4.5)
- Then go to Tools > Library Package Manager > Manage NuGet Packages for Solution and add/update
Bootstrap, jQuery, angularJS, and Microsoft ASP.NET MVC packages. Build your project for any error.
- Now as your project has been successfully built, right click to Views folder and add a folder named Layout. Then add a MVC 5 Layout Page (Razor) and name it
_LayoutPage. Then add the required designs, styles, and scripts as done in the demo porject attached in this article.
- In this part we will add a database model to the project. Right click in your Models folder and add an ADO .NET Entity Data Model and name it
SprocModel. Then follow the instructions to add your UniversityDB database to the project. We set the entity name as SprocEntities here. - Add a controller (MVC 5 Controller - Empty) by right clicking in Controller forlder and name it
HomeController. Then add the following references and actions to HomeController.
Make sure these references are there in your HomeController namespace section.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Xml.Linq;
using StoredProcedureMvc.Models;
Create your entity instance before creating actions.
readonly SprocEntities _db = new SprocEntities();
Then create these two Views for single object and object list page.
public ActionResult Index()
{
return View();
}
public ActionResult ListObject()
{
return View();
}
After that create the following Actions
public JsonResult GetStudentList()
{
var list = _db.usp_GetAllStudentData().ToList();
return Json(list, JsonRequestBehavior.AllowGet);
}
public ActionResult SaveStudent(t_Student student)
{
_db.usp_SaveStudent(student.Student_FirstName, student.Student_LastName, student.Student_Email,
student.Student_Address, student.Student_EnrollmentDate, student.Student_DepartmentId);
var list = _db.usp_GetAllStudentData().ToList();
return Json(list, JsonRequestBehavior.AllowGet);
}
public ActionResult UpdateStudent(t_Student student)
{
_db.usp_UpdateStudent(student.Student_Id, student.Student_FirstName, student.Student_LastName, student.Student_Email,
student.Student_Address, student.Student_EnrollmentDate, student.Student_DepartmentId);
var list = _db.usp_GetAllStudentData().ToList();
return Json(list, JsonRequestBehavior.AllowGet);
}
public ActionResult DeleteStudent(t_Student student)
{
_db.usp_DeleteStudent(student.Student_Id);
var list = _db.usp_GetAllStudentData().ToList();
return Json(list, JsonRequestBehavior.AllowGet);
}
In the following action we will convert the student list students as XML file and then send it to the procedure usp_SaveStudentListFromXml as a XML string.
public ActionResult SaveStudentAsXml(List
Here we will convert the same student list students to DataTable and then send it to procedure usp_GetAllStudentData as a Sql Parameter. Carefully follow how the parameter is created and different values of that parameter is set.
public ActionResult SaveStudentAsTable(List
- Now since all required actions have been created to
HomeController we need to add two script files for the two views Index.cshtml and ListObject.cshtml. Name those files as singleObject.js and listObject.js respectively and then add the references of these files to your Index.cshtml and ListObject.cshtml Views. - Right click in your Scripts folder and add a folder in it named CustomScripts and then create the following script files in CustomScripts folder.
- Make sure you know the basics of angularJS and how to make CRUD operations with it.
singleObject.js
var app = angular.module('myApp', [])
.controller('myCtrl', ["$scope", "$http", function ($scope, $http) {
$scope.departmentList = [
{ value: 1, name: "Computer Science & Engineering" },
{ value: 2, name: "Electrical & Electronic Engineering" },
{ value: 3, name: "Business Administration" },
{ value: 4, name: "Genetic Engineering" }
];
$scope.GetStudentList = function () {
$http({
traditional: true,
url: "/Home/GetStudentList",
method: 'GET',
contentType: "application/json",
dataType: "json"
}).success(function (data) {
if (data.length > 0) {
for (var i = 0; i < data.length; i++) {
if (data[i].Student_EnrollmentDate != null) {
var tempDate = new Date(parseInt(data[i].Student_EnrollmentDate.substr(6)));
data[i].Student_EnrollmentDate = $scope.FormatFullDate(tempDate);
}
}
$scope.studentList = data;
}
}).error(function (data) {
alert("Could not get student data!");
});
};
$scope.GetStudentList();
$scope.Save = function (std) {
$http({
traditional: true,
url: "/Home/SaveStudent",
method: 'POST',
data: JSON.stringify({ student: std }),
contentType: "application/json",
dataType: "json"
}).success(function (data) {
$scope.std = {};
if (data.length > 0) {
for (var i = 0; i < data.length; i++) {
if (data[i].Student_EnrollmentDate != null) {
var tempDate = new Date(parseInt(data[i].Student_EnrollmentDate.substr(6)));
data[i].Student_EnrollmentDate = $scope.FormatFullDate(tempDate);
}
}
$scope.studentList = data;
}
alert("Data saved successfully.");
}).error(function (data) {
alert("Could not save student data!");
});
};
$scope.Update = function (std) {
$http({
traditional: true,
url: "/Home/UpdateStudent",
method: 'POST',
data: JSON.stringify({ student: std }),
contentType: "application/json",
dataType: "json"
}).success(function (data) {
$scope.std = {};
if (data.length > 0) {
for (var i = 0; i < data.length; i++) {
if (data[i].Student_EnrollmentDate != null) {
var tempDate = new Date(parseInt(data[i].Student_EnrollmentDate.substr(6)));
data[i].Student_EnrollmentDate = $scope.FormatFullDate(tempDate);
}
}
$scope.studentList = data;
}
alert("Data updated successfully.");
}).error(function (data) {
alert("Could not update student data!");
});
};
$scope.Delete = function (item) {
var msg = confirm("Do you want to delete this student data?");
if (msg == true) {
$http({
traditional: true,
url: "/Home/DeleteStudent",
method: 'POST',
data: JSON.stringify({ student: item }),
contentType: "application/json",
dataType: "json"
}).success(function (data) {
if (data.length > 0) {
for (var i = 0; i < data.length; i++) {
if (data[i].Student_EnrollmentDate != null) {
var tempDate = new Date(parseInt(data[i].Student_EnrollmentDate.substr(6)));
data[i].Student_EnrollmentDate = $scope.FormatFullDate(tempDate);
}
}
$scope.studentList = data;
}
alert("Data deleted successfully.");
}).error(function (data) {
alert("Could not delete student data!");
});
}
};
$scope.Edit = function (item) {
$scope.std = angular.copy(item);
};
$scope.Cancel = function () {
$scope.std = {};
};
$scope.FormatFullDate = function (item) {
var year = item.getFullYear();
var month = ('0' + (item.getMonth() + 1)).slice(-2);
var day = ('0' + (item.getDate())).slice(-2);
var fullDate = year + "/" + month + "/" + day;
return fullDate;
};
}]).filter("deptDetails", function () {
return function (id) {
switch (id) {
case 1:
return "Computer Science & Engineering";
break;
case 2:
return "Electrical & Electronic Engineering";
break;
case 3:
return "Business Administration";
break;
case 4:
return "Genetic Engineering";
break;
default:
}
};
});
listObject.js
var app = angular.module('myApp', [])
.controller('myCtrl', ["$scope", "$http", function ($scope, $http) {
$scope.departmentList = [
{ value: 1, name: "Computer Science & Engineering" },
{ value: 2, name: "Electrical & Electronic Engineering" },
{ value: 3, name: "Business Administration" },
{ value: 4, name: "Genetic Engineering" }
];
$scope.GetStudentList = function () {
$http({
traditional: true,
url: "/Home/GetStudentList",
method: 'GET',
contentType: "application/json",
dataType: "json"
}).success(function (data) {
if (data.length > 0) {
for (var i = 0; i < data.length; i++) {
if (data[i].Student_EnrollmentDate != null) {
var tempDate = new Date(parseInt(data[i].Student_EnrollmentDate.substr(6)));
data[i].Student_EnrollmentDate = $scope.FormatFullDate(tempDate);
}
}
$scope.studentList = data;
}
}).error(function (data) {
alert("Could not get student data!");
});
};
$scope.GetStudentList();
$scope.studentList1 = [];
$scope.AddStudent = function (std) {
if (std != null) {
$scope.studentList1.push(std);
$scope.std = {};
} else {
alert('Please insert student info first.');
}
};
$scope.DeleteStudent = function (item) {
var index = -1;
var comArr = eval($scope.studentList1);
for (var i = 0; i < comArr.length; i++) {
if (comArr[i] === item) {
index = i;
break;
}
}
if (index === -1) {
alert("Something gone wrong");
}
$scope.studentList1.splice(index, 1);
};
$scope.SaveAsXml = function (studentList1) {
$http({
traditional: true,
url: "/Home/SaveStudentAsXml",
method: 'POST',
data: JSON.stringify({ students: studentList1 }),
contentType: "application/json",
dataType: "json"
}).success(function (data) {
$scope.std = {};
if (data.length > 0) {
for (var i = 0; i < data.length; i++) {
if (data[i].Student_EnrollmentDate != null) {
var tempDate = new Date(parseInt(data[i].Student_EnrollmentDate.substr(6)));
data[i].Student_EnrollmentDate = $scope.FormatFullDate(tempDate);
}
}
$scope.studentList = data;
$scope.studentList1 = [];
alert("Data saved successfully.");
}
}).error(function (data) {
alert("Could not save student data!");
});
};
$scope.SaveAsTable = function (studentList1) {
$http({
traditional: true,
url: "/Home/SaveStudentAsTable",
method: 'POST',
data: JSON.stringify({ students: studentList1 }),
contentType: "application/json",
dataType: "json"
}).success(function (data) {
$scope.std = {};
if (data.length > 0) {
for (var i = 0; i < data.length; i++) {
if (data[i].Student_EnrollmentDate != null) {
var tempDate = new Date(parseInt(data[i].Student_EnrollmentDate.substr(6)));
data[i].Student_EnrollmentDate = $scope.FormatFullDate(tempDate);
}
}
$scope.studentList = data;
$scope.studentList1 = [];
alert("Data saved successfully.");
}
}).error(function (data) {
alert("Could not save student data!");
});
};
$scope.Update = function (std) {
$http({
traditional: true,
url: "/Home/UpdateStudent",
method: 'POST',
data: JSON.stringify({ student: std }),
contentType: "application/json",
dataType: "json"
}).success(function (data) {
$scope.std = {};
if (data.length > 0) {
for (var i = 0; i < data.length; i++) {
if (data[i].Student_EnrollmentDate != null) {
var tempDate = new Date(parseInt(data[i].Student_EnrollmentDate.substr(6)));
data[i].Student_EnrollmentDate = $scope.FormatFullDate(tempDate);
}
}
$scope.studentList = data;
}
alert("Data updated successfully.");
}).error(function (data) {
alert("Could not update student data!");
});
};
$scope.Delete = function (item) {
var msg = confirm("Do you want to delete this student data?");
if (msg == true) {
$http({
traditional: true,
url: "/Home/DeleteStudent",
method: 'POST',
data: JSON.stringify({ student: item }),
contentType: "application/json",
dataType: "json"
}).success(function (data) {
if (data.length > 0) {
for (var i = 0; i < data.length; i++) {
if (data[i].Student_EnrollmentDate != null) {
var tempDate = new Date(parseInt(data[i].Student_EnrollmentDate.substr(6)));
data[i].Student_EnrollmentDate = $scope.FormatFullDate(tempDate);
}
}
$scope.studentList = data;
}
alert("Data deleted successfully.");
}).error(function (data) {
alert("Could not delete student data!");
});
}
};
$scope.Edit = function (item) {
$scope.std = angular.copy(item);
};
$scope.Cancel = function () {
$scope.std = {};
$scope.studentList1 = [];
};
$scope.FormatFullDate = function (item) {
var year = item.getFullYear();
var month = ('0' + (item.getMonth() + 1)).slice(-2);
var day = ('0' + (item.getDate())).slice(-2);
var fullDate = year + "/" + month + "/" + day;
return fullDate;
};
}]).filter("deptDetails", function () {
return function (id) {
switch (id) {
case 1:
return "Computer Science & Engineering";
break;
case 2:
return "Electrical & Electronic Engineering";
break;
case 3:
return "Business Administration";
break;
case 4:
return "Genetic Engineering";
break;
default:
}
};
});
In the above script files I have created the department list as a static list. You can fetch it from database as well.
- Since I have attached the demo project file with this article so I will not give the html page markups here. Rather below are the screenshots of the two pages we need to work with.
Screenchot for Single Student Object
In the above form fill all the fields and click Save button to save the student data. Click Edit icon under Action heading to edit the data, and after editing the data click the Update button to update it. Trash icon will delete the student object.
Screenchot for Student List Object
Here after filling all the fields click Add button to add that student data to the right side table. You can add as much students as you want. After adding all the students to the table click Save Student List as XML to save the students list as XML data or click Save Student List as Data Table to save the list as Data Table. Here in the second table (placed below the form) Edit & Trash icon will do the same as described for previous page.
Points of Interest
This article will mainly help the beginners and also the intermediate level developers who really like Stored Procedure and facing problem to work with list data in Stored Procedure. Many would prefer LINQ over Stored Procedure as I am (since less code required) but still Stored Procedure is a good choice for performance and security as we know. Hope this article will help you in the best ways.
Note
Discussions on SQL Transactions will be added as soon as possible with this article.
History
Demo application file missing and download problem has been solved as per feedback. Have a look.
