Populate (Bind) AngularJS UI Grid from database using WebService in ASP.Net

Last Reply 2 months ago By dharmendr

Posted 2 months ago

How to bind AngularJS UI Grid from database.

Posted 2 months ago Modified on 2 months ago

Hi rani,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.7.8/angular.min.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/angular-ui-grid/4.8.1/ui-grid.min.js"></script>
    <link type="text/css" rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/angular-ui-grid/4.8.1/ui-grid.css" />
    <script type="text/javascript">
        var app = angular.module('MyApp', ['ui.grid']);
        app.controller('MyController', function ($scope, $http) {
            $scope.gridOptions = {};
            $http.post("WebService.asmx/GetEmployees", { headers: { 'Content-Type': 'application/json'} })
            .then(function (response) {
                $scope.gridOptions = {
                    data: eval(response.data.d),
                    columnDefs: [
                            { field: 'Id', width: '75', enableSorting: false },
                            { field: 'Name', width: '200', enableSorting: true },
                            { field: 'City', width: '75', enableSorting: true },
                            { field: 'Country', width: '100', enableSorting: true }
                        ]
                };
            });
        });
    </script>
</head>
<body ng-app="MyApp" ng-controller="MyController">
    <div ui-grid="gridOptions" style="width: 470px">
    </div>
</body>
</html>

WebService

C#

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    [WebMethod]
    public string GetEmployees()
    {
        List<object> employees = new List<object>();
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("SELECT EmployeeID,FirstName,LastName,City,Country FROM Employees", con);
            cmd.CommandType = CommandType.Text;
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                employees.Add(new
                {
                    ID = Convert.ToInt32(dr["EmployeeID"]),
                    Name = dr["FirstName"].ToString() + " " + dr["LastName"].ToString(),
                    City = dr["City"].ToString(),
                    Country = dr["Country"].ToString()
                });
            }
        }
        JavaScriptSerializer serializer = new JavaScriptSerializer();

        return serializer.Serialize(employees);
    }
}

VB.Net

Imports System
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Script.Serialization
Imports System.Web.Services

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function GetEmployees() As String
        Dim employees As List(Of Object) = New List(Of Object)()
        Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
            Dim cmd As SqlCommand = New SqlCommand("SELECT EmployeeID,FirstName,LastName,City,Country FROM Employees", con)
            cmd.CommandType = CommandType.Text
            con.Open()
            Dim dr As SqlDataReader = cmd.ExecuteReader()

            While dr.Read()
                employees.Add(New With {
                .ID = Convert.ToInt32(dr("EmployeeID")),
                .Name = dr("FirstName").ToString() & " " + dr("LastName").ToString(),
                .City = dr("City").ToString(),
                .Country = dr("Country").ToString()
            })
            End While
        End Using
        Dim serializer As JavaScriptSerializer = New JavaScriptSerializer()

        Return serializer.Serialize(employees)
    End Function
End Class

Screenshot