Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

CRUD with Angular.js and Asp.net,Sql Server

Insert,Update,Delete with Asp.net,AngularJS Ajax

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.

I will explain example in detail with some following steps

Step 1(Database code MS SQL Server)

Create Sql server simple table names Employee with columns ID, Name, Email, and Address.

Employee Table

 

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] ,
            [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;
 
        }