Menu ▼


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





comments powered by Disqus


Search dotnetlearners.com