Introduction
In this post, I am going to explain in
detail a sample example that will insert, update, delete, and display retrieved
data using simple Asp.net,AngularJS Ajax and SQL Server.
sample application in detail with steps
I will explain example in detail with
some following steps
Step 1(Database code MS SQL Server)
MsSQL Table
Create Sql server simple table names
Employee with columns ID, Name, Email, and Address.
Stored procedure:
Create stored procedure to retrieve
data; here I created stored procedure to retrieve data only
To insert, update, and delete
operation I am going to use parameterized inline query but I recommend using
stored procedure or entity framework for it.
Stored procedure to retrieve data
CREATE PROCEDURE
[dbo].[GetAllEmployees_SP]
AS
BEGIN
SELECT
[ID] ,
[EmployeeName] as Name,
[Email] as EmailID,
[Address]
FROM
[dbo].[Employee] Where
DeletedDate is null
END
Step 2(Angular JS View):
AngularJS View and Asp.net UI mix code
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.4.8/angular.min.js"></script>
<script src="Content/js/Employee.js"></script>
</head>
<body>
<form id="form1" runat="server">
<div ng-app="myApp" ng-controller="myEmpCntrl">
<table border="1" cellpadding="10" align="center" class="table table-bordered
table-striped">
<tr>
<td colspan="4">
<table>
<tr>
<td>
</td>
<td> Name:
<input type="text" id="txtEmpName" ng-model="EmpName" />
</td>
<td>
Email:
<input type="text" id="txtEmpEmail" ng-model="EmpEmail" />
</td>
<td>
Address:
<input type="text" id="txtEmpAddress" ng-model="EmpAddress" />
</td>
</tr>
<tr>
<td colspan="3" style="text-align:right">
<button ng-click="Save()">Save</button>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<th>
Employee Id
</th>
<th>
Employee Name
</th>
<th>
Address
</th>
<th>
Email Id
</th>
</tr>
<tr ng-repeat="emp in Employees|orderBy
:'Name'">
<td>
<input type="text" id="Text2" ng-model="emp.Id" />
</td>
<td>
<input type="text" id="Text1" ng-model="emp.Name" />
</td>
<td>
<input type="text" id="Text3" ng-model="emp.Address" />
</td>
<td>
<input type="text" id="Text4" ng-model="emp.EmailId" />
</td>
<td>
<button ng-click="Update(emp.Id,emp.Name,emp.Address,emp.EmailId)" >Update</button>
<button ng-click="Delete(emp.Id)" >Delete</button>
</td>
</tr>
<tr>
<td>
<button ng-click="fillList()" >Refresh</button>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Setp 3(AngularJS Controller And Model Code)
AngularJS Controller and Ajax Code (Employee.js fie)
// Angular app bootstrap start here
var app = angular.module("myApp", []);
//Angular controller
app.controller("myEmpCntrl", function ($scope, $http) {
//Angular
$scope and model data
$scope.EmpID = 0;
$scope.EmpName = "";
$scope.EmpAddress = "";
$scope.EmpEmail = "";
// Angular Ajax function used to Create
or insert data in to employee table
$scope.Save = function () {
var httpreq = {
method: 'POST',
url: 'Default.aspx/Create',
headers: {
'Content-Type': 'application/json;
charset=utf-8',
'dataType': 'json'
},
data: { EmpName: $scope.EmpName,
EmpAddress: $scope.EmpAddress, EmpEmail: $scope.EmpEmail }
}
$http(httpreq).success(function (response) {
$scope.fillList();
alert("Saved
successfully.");
})
};
// Angular ajax function deletes employee data for selected employee
$scope.Delete = function (EmpId) {
if (confirm("Are you sure want to
delete?")) {
var httpreq = {
method: 'POST',
url: 'Default.aspx/Delete',
headers: {
'Content-Type': 'application/json;
charset=utf-8',
'dataType': 'json'
},
data: { ID: EmpId }
}
$http(httpreq).success(function (response) {
$scope.fillList();
alert("Deleted
successfully.");
})
}
};
// Angular ajax function update Employee data
$scope.Update = function (EmpId, EmpName, EmpAddress, EmpEmailId) {
if (confirm("Are you sure want to
Update?")) {
var httpreq = {
method: 'POST',
url: 'Default.aspx/Update',
headers: {
'Content-Type': 'application/json;
charset=utf-8',
'dataType': 'json'
},
data: { ID: EmpId, EmpName:
EmpName, EmpAddress: EmpAddress, EmpEmail: EmpEmailId }
}
$http(httpreq).success(function (response) {
$scope.fillList();
alert("Updated
successfully.");
})
}
};
//Angular ajax function retrieve and display employee data in table
$scope.fillList = function () {
var httpreq = {
method: 'POST',
url: 'Default.aspx/GetEmployeeList',
headers: {
'Content-Type': 'application/json;
charset=utf-8',
'dataType': 'json'
},
data: {}
}
$http(httpreq).success(function (data) {
$scope.Employees = data.d;
})
};
$scope.fillList();
});
Step 4(Asp.net
Server side call to database)
Asp.net Server
side Code
//Employee class
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string EmailId { get; set; }
}
//Web method is used to insert employee data into Employee table
//using sql parameterized query
[System.Web.Services.WebMethod()]
public static void Create(string EmpName, string EmpEmail, string EmpAddress)
{
var constring = GetConnectionString();
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "INSERT INTO Employee
(EmployeeName,Email,Address) values (@EmployeeName,@Email,@Address);";
cmd.Parameters.AddWithValue("@EmployeeName", EmpName);
cmd.Parameters.AddWithValue("@Email", EmpEmail);
cmd.Parameters.AddWithValue("@Address", EmpAddress);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
//Web method is used to delete employee data from Employee table
//using sql parameterized query
[System.Web.Services.WebMethod()]
public static void Delete(int ID)
{
var constring = GetConnectionString();
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "UPDATE Employee SET
DeletedDate=Getdate() WHERE ID=@ID;";
cmd.Parameters.AddWithValue("@ID", ID);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
//Web method is used to Update employee data into Employee table
//using sql parameterized query
[System.Web.Services.WebMethod()]
public static void Update(int ID,string EmpName,string EmpAddress,string EmpEmail)
{
var constring = GetConnectionString();
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "UPDATE Employee SET
EmployeeName=@EmployeeName,Email=@Email,Address=@Address WHERE ID=@ID;";
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@EmployeeName", EmpName);
cmd.Parameters.AddWithValue("@Email", EmpEmail);
cmd.Parameters.AddWithValue("@Address", EmpAddress);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
//Web method retrieves data from sql server table
[System.Web.Services.WebMethod()]
public static List<Employee> GetEmployeeList()
{
var constring = GetConnectionString();
List<Employee> Employees = new List<Employee>();
DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("GetAllEmployees_SP", con))
{
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds);
}
}
}
if (ds != null && ds.Tables.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
Employees.Add(new Employee()
{
Id = int.Parse(dr["ID"].ToString()),
Name = dr["Name"].ToString(),
Address = dr["Address"].ToString(),
EmailId = dr["EmailID"].ToString()
});
}
return Employees;
}
//connection string
public static string GetConnectionString()
{
string constring = ConfigurationManager.ConnectionStrings["C4Example"].ConnectionString;
return constring;
}
In 2010 Google and its developers
introduced cool web development JavaScript framework AngularJS to world; it
quickly became one of the hottest web technologies. Last night (on 15 Sep
2016) finally Google released most awaiting and excited version of Angular 2 rewritten with Microsoft's
TypeScript, offers payload size and performance enhancements.