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;
 
        }

 

AngularJS Ajax

AngularJS uses $http core service to read and send data to remote servers via the browser's XMLHttpRequest object or via JSONP.

In ingularJS you can send requests in different ways some I explained below

1. AJAX calls via the $http service.
2. JSONP calls via the $http service.
3. REST type calls.

The $http service is a function which takes a single argument -a configuration object, that is used to generate an HTTP request and returns a promise.

     // Simple GET request example:
        $http({
            method: 'GET',
            url: '/someUrl'
        }).then(function successCallback(response) {
            // this callback will be called asynchronously
            // when the response is available
        }, function errorCallback(response) {
            // called asynchronously if an error occurs
            // or server returns response with an error status.
        });


The response object has following properties

data {string|Object} : The response body transformed with the transform functions.
status {number}:  HTTP status code of the response.
headers  {function([headerName])}  :Header getter function.
config {Object} : The configuration object that was used to generate the request.
statusText {string} :  HTTP status text of the response.

With $http service you can use follwing shortcut methods,

$http.get
$http.head
$http.post
$http.put
$http.delete
$http.jsonp
$http.patch

1. AJAX calls via the $http service

AngularJS uses $http service to read and send data to servers, server makes call to database and retrieves required records. Practically AngularJS reads data in Json format once data is ready $http server reads data from server, for more understanding check following code.

Code Sample:

function EmployeeController($scope,$http) {
    var url = "EmployeeData.txt";
 
   $http.get(url).success( function(response) {
      $scope.Employees = response;    });
  }


2. JSONP calls via the $http service

$http service provides get, post and other functions mapped to http methods, like “get” and “post” it also provides the function that we’ll need “jsonp”.

JSONP is short for "JSON with Padding" and data returned using jsonp is formatted differently, here the ‘p’ in Jsonp stands for padding which means simply “Named Function”.

Syntax:

jsonp(url, [config]);

Parameter

Description

URL(String)

Relative or absolute URL specifying the destination of the request. The name of the callback should be the string JSON_CALLBACK.

Config(Object)

This is optional configuration object.

 

How it works

1.  A JSONP request is not sent like simple AJAX calls using XHR objects. Instead, a <script> element is created and inserted into the HTML page.

 The src attribute contains the URL of the remote service to call with the JSONP call. This URL should include any parameters you need to send to the remote service.

 

2. When the <script> element is inserted into the HTML page, the browser will load the script from the given URL. That enables the remote service to send JavaScript back to your application for execution.

The JavaScript returned by the remote service should be a function call to an existing JavaScript function in your HTML page.

Note
First ensure your server supports Jsonp requests reason Jsonp requests require explicit support on the server side.

Code Sample

    var url = http://codechef4u.com/testdata.json?callback=JSON_CALLBACK";
    var responseData = $http.jsonp( url,
                 {  params : {
                     p1 : "test 1"
                      ,p2 : "test 2"
                 }
                 }
                );

 

    responseData.success(function(testData) {

        // your code with returned object

        // ( in the "data" parameter ).

    });

3. AngularJS Rest type Calls

You can use rest service in angular js follwing are some options I explained ,

Option 1: Using $http service

AngularJS $http service sends AJAX requests to web services and receiving data from them, using JSON (Exactly what required for REST services).

Example:

 $http({ method: 'GET', url: '/codechef4u.com/xyzservice' }).
  success(function (data, status, headers, config) {
      // your code on data
  }).
  error(function (data, status, headers, config) {
      // if any error log or report code
  });


Option 2: $resource service

You can try AngularJS another service named $resource service, the $resource service which provides access to REST services in a more high-level fashion. You can use following link for more details,

https://docs.angularjs.org/api/ngResource/service/$resource

Example:

var UsersData = $resource('/user/:userId',{ userId: '@id' });
        var user = UsersData.get({ userId: 123 }, function () {
            user.nkendre = true;
            user.$save();
        });

Option 3: Third party tools

You can try also third-party solutions, such as Restangular. See its documentation on how to use it. Go through documentation using following GitHub link.

https://github.com/mgonto/restangular

 

AngularJS Ajax Code Example

View 

    <div ng-app="myAjaxApp" ng-controller="myAjaxCtrl">
            <ul ng-repeat="post in posts">
                <li>{{post.Title}}</li>
            </ul>
        </div>


script

        var app = angular.module("myAjaxApp", []);
        app.controller("myAjaxCtrl", function ($scope, $http) {
            $http.get('posts.json').
 
              success(function (data, status, headers, config) {
                  $scope.posts = data;
              }).
              error(function (data, status, headers, config) {
                  // log error
                  alert('some error')
              });
        });

 

Jason File 

[{ "Title":"learn angular", "Description":"test data for angular" },
 { "Title":"build an angular app", "Description":"another dasdf sdf "}
 ]

  धन्यवाद मित्रानो ... 


AngularJS Form

Form is group of related html controls together, you can use from to wire required controls and reuse it.

You can use html controls like,

1. Input Fields

2. Select Fields

3. Buttons

i.e Text-area, textbox, radio-button, checkbox, button etc.

Data binding with Forms

AngularJS provides some features for binding data of HTML form input fields to the model object ($scope). These features makes it easier to work with forms.

The ng-model directive provides the two-way data-binding by synchronizing the model to the view, as well as view to the model.

In following example we used two way data binding for form textbox controls,

<div ng-controller="MyEmoloyeeCntrlr">

  <form>

   First name :<input type="text" name="firstName" ng-model="Employee.FirstName"> <br />

   Last name: <input type="text" name="lastName" ng-model="Employee.LastName"> <br />

  </form>

 

   <div>

      {{Employee.FirstName |uppercase }} {{Employee.LastName | uppercase}}

   </div>

  </div>

 

    <script>

        angular.module("myFormApp", [])

                .controller("MyEmoloyeeCntrlr", function ($scope) {

                    $scope.Employee = {};

                    $scope.Employee.FirstName = "Nagnath";

                    $scope.Employee.LastName = "Kendre";

                });

    </script>

 

Form control events and validation

AngularJS provides some event directives which can be associated with the HTML controls.

Following are some AngularJS supported events,

ng-mousedown
ng-mouseover
ng-click
ng-dbl-click
ng-mouseenter
ng-keydown
ng-keyup
ng-change etc.

Following example with ng-click reset form data,

Example

   <div ng-controller="MyEmoloyeeCntrlr">
     <form>
      Full name :<input type="text" name="firstName" ng-model="Employee.FullName"> <br />
            <button ng-click="Employee.FullName = ''">
                Reset
            </button>
      </form>
   
    </div>
    <script>
        angular.module("myFormApp", [])
                .controller("MyEmoloyeeCntrlr", function ($scope) {
                    $scope.Employee = {};
                    $scope.Employee.FullName = "Nagnath Kendre";
                });
    </script>

 

Validation

You can use following CSS to display validity of each form control.

ng-valid: the model is valid
ng-invalid: the model is invalid
ng-valid-[key]: for each valid key added by $setValidity
ng-invalid-[key]: for each invalid key added by $setValidity
ng-pristine: the control hasn't been interacted with yet
ng-dirty: the control has been interacted with
ng-touched: the control has been blurred
ng-untouched: the control hasn't been blurred
ng-pending: any $asyncValidators are unfulfilled

Note: “novalidate” is used to disable browser's native form validation.

Validation Example

    <!--Angular view Code-->
    <div ng-controller="EmployeeController">
        <form novalidate class="css-form">
            User Name :<input type="text" name="UserName" ng-model="Employee.UserName" required> <br />
            Full Name :<input type="text" name="Name" ng-model="Employee.Name"> <br />
            Email :<input type="email" ng-model="Employee.Email"> <br />
          
            <button ng-click="reset()"> Reset</button>
            <input type="submit" ng-click="update(Employee)" value="Save" />
 
            <pre>user = {{Employee | json}}</pre>
            <pre>Updated Data = {{UpdatedData | json}}</pre>
        </form>
 
    </div>
 
    <!--css validation-->
    <style type="text/css">
        .css-form input.ng-invalid.ng-touched {
            background-color: #0094ff;
        }
 
        .css-form input.ng-valid.ng-touched {
            background-color: #ff6a00;
        }
    </style>
 
    <!--Angular script Code-->
    <script>
       angular.module('myApp', []).controller('EmployeeController', ['$scope', function ($scope) {
           $scope.UpdatedData = {};
 
           $scope.update = function (Employee) {
               $scope.UpdatedData = angular.copy(Employee);
           };
 
           $scope.reset = function () {
               $scope.Employee = angular.copy($scope.UpdatedData);
           };
 
           $scope.reset();
        }]);
    </script>

 

Binding data to form control and control state

Form is an instance of form controller, you can publish form instance into scope using name attribute.

The input control that has ng-moel directive that holds instance of ngModelController, In this scenario a control instance can be published as a property of the form instance using the name attribute on the input control. The name attribute specifies the name of the property on the form instance.

With above explanation we can conclude that the internal state of both the form and the control is available for binding in the view using the standard binding primitives.

Example:

<body ng-app="myApp" >
 
    <!--Angular view Code with form name and control name attribute-->
    <div ng-controller="EmployeeController">
        <form name="EmployeeForm" class="css-form" novalidate>
            User Name :<input type="text" name="uName" ng-model="Employee.UserName" required> <br />
 
            <div ng-show="EmployeeForm.$submitted || EmployeeForm.uName.$touched">
                <div ng-show="EmployeeForm.uName.$error.required">User Name is required field.</div>
            </div>
            Full Name :<input type="text" name="fName" ng-model="Employee.Name"> <br />
            Email :<input type="email" ng-model="Employee.Email" name="uEmail" required> <br />
 
            <div ng-show="EmployeeForm.$submitted || EmployeeForm.uEmail.$touched">
                <span ng-show="EmployeeForm.uEmail.$error.required">Tell us your email.</span>
                <span ng-show="EmployeeForm.uEmail.$error.email">This is not a valid email.</span>
            </div>
 
            <button ng-click="reset()"> Reset</button>
            <input type="submit" ng-click="update(Employee)" value="Save" />
 
            <pre>user = {{Employee | json}}</pre>
            <pre>Updated Data = {{UpdatedData | json}}</pre>
        </form>
 
    </div>
 
    <!--Angular script Code used state in data binding and validation-->
    <script>
       angular.module('myApp', []).controller('EmployeeController', ['$scope', function ($scope) {
         
           $scope.UpdatedData = {};
           $scope.update = function (Employee) {
               $scope.UpdatedData = angular.copy(Employee);
           };
 
           $scope.reset = function (EmployeeForm) {
               if (EmployeeForm) {
                   EmployeeForm.$setPristine();
                   EmployeeForm.$setUntouched();
               }
               $scope.Employee = angular.copy($scope.UpdatedData);
           };
 
           $scope.reset();
        }]);
    </script>
 
</body>

 धन्यवाद मित्रानो ...