AngularJS Ajax $http - Asp.net with SQL example


In this example we will see how to save, delete and show the data in html table in Asp.net with SQL using AngularJS $http

AngularJS AJAX
CS Page:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.Script.Serialization; using System.Data.SqlClient; using System.Data; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } [System.Web.Services.WebMethod()] public static void Save(string StudentName) { using (SqlConnection con = new SqlConnection(@"data source=localhost\sqlexpress;user id=sa; password=123; database=Sample;")) { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = con; cmd.CommandText = "insert into tblStudents (StudentName) values (@StudentName);"; cmd.Parameters.AddWithValue("@StudentName", StudentName); con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } } [System.Web.Services.WebMethod()] public static void Delete(int StudentID) { using (SqlConnection con = new SqlConnection(@"data source=localhost\sqlexpress;user id=sa; password=123; database=Sample;")) { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = con; cmd.CommandText = "update tblStudents set IsActive=0 where StudentID=@StudentID;"; cmd.Parameters.AddWithValue("@StudentID", StudentID); con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } } [System.Web.Services.WebMethod()] public static List<Names> GetList() { List<Names> names = new List<Names>(); DataSet ds = new DataSet(); using (SqlConnection con = new SqlConnection(@"data source=localhost\sqlexpress;user id=sa; password=123; database=Sample;")) { using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = con; cmd.CommandText = "select StudentID,StudentName from tblStudents where IsActive=1;"; using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { da.Fill(ds); } } } if (ds != null && ds.Tables.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) names.Add(new Names(int.Parse(dr["StudentID"].ToString()), dr["StudentName"].ToString())); } return names; } } public class Names { public int StudentID; public string StudentName; public Names(int _StudentID, string _StudentName) { StudentID = _StudentID; StudentName = _StudentName; } }

ASPX Page:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <style> table, th, td { border: 1px solid grey; border-collapse: collapse; padding: 5px; } table th { background-color: #274257; color: #fff; } table tr:nth-child(odd) { background-color: #f1f1f1; } table tr:nth-child(even) { background-color: #ffffff; } </style> <script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script> </head> <body> <form id="form1" runat="server"> <div ng-app="myApp" ng-controller="myCntrl"> <table> <tr> <td> Student Name : </td> <td> <input type="text" id="txtStudentName" ng-model="studetnName" /> </td> </tr> <tr> <td colspan="2" align="center"> <input type="button" value="Save" ng-click="Save()" /> </td> </tr> </table> <br /> <br /> <table> <thead> <tr> <th> StudentID </th> <th> StudentName </th> <th> </th> </tr> </thead> <tr ng-repeat="student in StudentList | orderBy : studentorder "> <td ng-bind="student.StudentID"> </td> <td ng-bind="student.StudentName"> </td> <td> <a href="#" ng-click="Delete(student.StudentID)">Delete</a> </td> </tr> </table> </div> <script> var app = angular.module("myApp", []); app.controller("myCntrl", function ($scope, $http) { $scope.studentorder = "StudetnID"; $scope.studetnName = ""; $scope.Save = function () { var httpreq = { method: 'POST', url: 'Default.aspx/Save', headers: { 'Content-Type': 'application/json; charset=utf-8', 'dataType': 'json' }, data: { StudentName: $scope.studetnName } } $http(httpreq).success(function (response) { $scope.fillList(); alert("Saved successfully."); }) }; $scope.Delete = function (SID) { 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: { StudentID: SID } } $http(httpreq).success(function (response) { $scope.fillList(); alert("Deleted successfully."); }) } }; $scope.fillList = function () { $scope.studetnName = ""; var httpreq = { method: 'POST', url: 'Default.aspx/GetList', headers: { 'Content-Type': 'application/json; charset=utf-8', 'dataType': 'json' }, data: {} } $http(httpreq).success(function (response) { $scope.StudentList = response.d; }) }; $scope.fillList(); }); </script> </form> </body> </html>

SQL Query to create table:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblStudents]( [StudentID] [int] IDENTITY(1,1) NOT NULL, [StudentName] [varchar](50) NOT NULL, [IsActive] [bit] NOT NULL CONSTRAINT [DF_tblStudents_IsActive] DEFAULT ((1)), CONSTRAINT [PK_tblStudents] PRIMARY KEY CLUSTERED ( [StudentID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO