How to Get and Display Table Data from Database using AngularJs in Asp.net MVC

Introduction:

Today in this article, I will explain How to get and display table data from database using AngularJs in Asp.net MVC application. In this example I have used Ado.Net for database operation and AngularJs2.           

In this example, we will fetch table record from MS SQL database and display in tabular form.

Ok, let’s start to retrieve data from database using AngularJs in asp.net MVC

Follow these steps in order to implement “Get and Display data from database using AngularJs in Asp.net MVC”

Step1: Create New Project.

 Go to File > New > Project > Web > Asp.net MVC web project > Enter Application Name > Select your project location > click to add button > It will show new dialog window for select template > here we will select empty project > then click to ok

Step2: Create a table and Stored procedure in the database.

In this example, I have used fallowing table for display record in the tabular form.

CREATE TABLE [dbo].[tbl_registration] (

    [Sr_no]    INT            IDENTITY (1, 1) NOT NULL,

    [Email]    NVARCHAR (100) NULL,

    [Password] NVARCHAR (MAX) NULL,

    [Name]     VARCHAR (MAX)  NULL,

    [Address]  NVARCHAR (MAX) NULL,

    [City]     NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_tbl_registration] PRIMARY KEY CLUSTERED ([Sr_no] ASC)

);

Now create stored procedure for fetching all record from table

Create proc Sp_Get_registration

as

begin

Select * from tbl_registration

end

Run above script in MS SQL Server and click to execute button

Step3: Add Connection string in web.config file

 Here I have added connection string in ‘web.config’ file under Configuration section as follows

<connectionStrings>

    <add name="con" connectionString="Data Source=.;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>

  </connectionStrings>

Step4: Create a controller.

Go to Solutions Explorer > right click on controller folder > Add Controller > Enter Controller name > Select template “empty MVC Controller   ” > Add.

Here I have created a controller name as  “HomeController”

Step5: Create Database Access layer.

Here in this example, I have used Ado.net as database operation so we need to create a class for all database operations. Here I have created ‘db.cs’ class.

 Go to Solutions Explorer > right click on project solution> Add New Folder > Enter Folder name (Here I rename it as ‘database_access_layer’) > right click on folder > Add new class.

Now add the fallowing method into Database access class that access all record from the database table.

public class db

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

 

        public DataSet Getrecord()

        {

            SqlCommand com = new SqlCommand("Sp_Get_registration", con);

            com.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

    }

Entire db.cs class will be as fallows

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

namespace Angulartable.database_Access_layer

{

    public class db

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

 

        public DataSet Getrecord()

        {

            SqlCommand com = new SqlCommand("Sp_Get_registration", con);

            com.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

    }

}

Step6: Add new action method in a controller for fetch data from the database and return record as JSON result.

Here I have created ‘getrecord’ action method in HomeController, that look like as fallows   

public JsonResult getrecord()

        {

            DataSet ds = dblayer.Getrecord();

            List<registration> listreg = new List<registration>();

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                listreg.Add(new registration

                {

                    Sr_no=Convert.ToInt32(dr["Sr_no"]),

                    Email=dr["Email"].ToString(),

                    Password=dr["Password"].ToString(),

                    Name=dr["Name"].ToString(),

                    Address=dr["Address"].ToString(),

                    City=dr["City"].ToString()

                });

            }

            return Json(listreg, JsonRequestBehavior.AllowGet);

        }

Entire HomeController look like as follows: 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using Angulartable.Models;

using System.Data;

using System.Data.SqlClient;

namespace Angulartable.Controllers

{

    public class HomeController : Controller

    {

        //

        // GET: /Home/

        database_Access_layer.db dblayer = new database_Access_layer.db();

 

 

        public JsonResult getrecord()

        {

            DataSet ds = dblayer.Getrecord();

            List<registration> listreg = new List<registration>();

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                listreg.Add(new registration

                {

                    Sr_no=Convert.ToInt32(dr["Sr_no"]),

                    Email=dr["Email"].ToString(),

                    Password=dr["Password"].ToString(),

                    Name=dr["Name"].ToString(),

                    Address=dr["Address"].ToString(),

                    City=dr["City"].ToString()

                });

            }

            return Json(listreg, JsonRequestBehavior.AllowGet);

        }

    }

}

Step7: Add model class for table

Go to Model folder >> Right click on model >> Add new class

Here I have added class as ‘registration.cs’.  It contain fallowing code

public class registration

    {

        public int Sr_no { get; set; }

        public string Email { get; set; }

        public string Password { get; set; }

        public string Name { get; set; }

        public string Address { get; set; }

        public string City { get; set; }

    }

Step8: Add New JS file for AngularJS Controller and AngullarJS library file

Go to Solution Explorer > Right Click on folder (where you want to saved your AngularJS controller JS files, here I have created a folder named "AngularController"  under Script Folder) > Add > Select Javascript file > Enter name > Add.

We write fallowing code into this file

var app = angular.module('Homeapp', []);

app.controller('HomeController', function ($scope, $http) {

    $http.get('/Home/getrecord').then(function (d) {

        $scope.regdata = d.data;

    }, function (error) {

        alert('failed');

    });

});

Here I have created an angular controller named as ‘HomeController’ with parameter $http and $scope

$http: $http is an AngularJS service for reading data from remote servers. It makes a request to the server and returns a response.

$Scope: $Scope is the binding part between the HTML (view) and the JavaScript (controller). It is an object with the available properties and methods. $Scope is available for both the view and the controller.

Step9:  Add new action into the controller for show data from the database.

 Here I have added ‘Index’ into ‘HomeController’. Please write fallowing code.

public ActionResult Index()

        {

            return View();

        }

Step10: Add view for action in controller & design.

Right Click on Action Method > Add View > Enter View Name > Select View Engine (Razor) > Add.

It has fallowing code and designs:

@{

    Layout = null;

}

 

<!DOCTYPE html>

 

<html>

<head>

    <meta name="viewport" content="width=device-width" />

    <title>Index</title>

    <style>

        .tableData {

            border-left: solid 1px #D8C3C3;

            border-top: solid 1px #D8C3C3;

        }

 

            .tableData tr {

            }

 

            .tableData td, .tableData th {

                border-right: solid 1px #D8C3C3;

                border-bottom: solid 1px #D8C3C3;

                text-align: left;

                padding: 5px;

            }

 

            .tableData td {

            }

 

            .tableData th {

                background-color: #FAFAFA;

                padding: 7px 5px;

                border-bottom-color: #9C9C9C;

            }

 

        .odd {

            background-color: #f3f3f3;

        }

 

        .even {

            background-color: #ffffff;

        }

    </style>

 

</head>

<body ng-app="Homeapp">

    <div ng-controller="HomeController">

        <table class="tableData">

            <tr>

               <th>Sr no</th>

                <th>Email</th>

                <th>Password</th>

                <th>Name</th>

                <th>Address</th>

                <th>City</th>

            </tr>

            <tr ng-repeat="e in regdata" ng-class-even="'even'" ng-class-odd="'odd'">

                <td>{{e.Sr_no}}</td>

                <td>{{e.Email}}</td>

                <td>{{e.Password}}</td>

                <td>{{e.Name}}</td>

                <td>{{e.Address}}</td>

                <td>{{e.City}}</td>

            </tr>

        </table>

    </div>

    <script src="~/Script/angular.min.js"></script>

    <script src="~/Script/AngularController/Homejs.js"></script>

</body>

</html>

Step 11: Run Application.

We have done all steps, Now It’s time to run the application