Execute Stored Procedure and display result in View using AngularJS in ASP.Net MVC

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Hi,

I have one stored procedure 

CREATE PROCEDURE [dbo].[PayProcess] 
    -- Add the parameters for the stored procedure here
    (@Date Date)
AS
BEGIN
    SELECT 'Procedure Run sucessfully' As 'Procedure'
END
GO

After exccuting stored procedure i am getting o.p like below in sql

Procedure

Procedure Run successfully

After that display the result in View

Note: If I try to execute normal query it was generating result

but if i try to use stored procudure not getting any result

    [HttpPost]
    public JsonResult PayProcess(DateTime date)
    {
        // Call your Procedure. 
        try
        { 
            string result = db.GetPayProcess(date).FirstOrDefault();
            //string result = "select * from Report where Id=2";
            //var result = db.GetPayProcess(date).ToString();
            SqlConnection con = new SqlConnection();
            string connectionstring = ConfigurationManager.ConnectionStrings["CMSR"].ConnectionString;
            con.ConnectionString = connectionstring;
            SqlCommand cmd;
            DataTable dtDet = new DataTable();
            SqlDataAdapter sdaDet = new SqlDataAdapter();
            cmd = new SqlCommand(result, con);
            sdaDet.SelectCommand = cmd;
            dtDet = new DataTable();
            sdaDet.Fill(dtDet);
            List<Dictionary<string, object>> lstReport = GetTableRows(dtDet);
 
            return Json(lstReport, JsonRequestBehavior.AllowGet);
        }
        catch (Exception ex)
        {
            return Json(ex.Message, JsonRequestBehavior.AllowGet);
        }
 
    }
    public List<Dictionary<string, object>> GetTableRows(DataTable dtData)
    {
        List<Dictionary<string, object>>
        lstRows = new List<Dictionary<string, object>>();
        Dictionary<string, object> dictRow = null;
 
        foreach (DataRow dr in dtData.Rows)
        {
            dictRow = new Dictionary<string, object>();
            foreach (DataColumn col in dtData.Columns)
            {
                dictRow.Add(col.ColumnName, dr[col]);
            }
            lstRows.Add(dictRow);
        }
        return lstRows;
    }

 

Posted 2 months ago Modified on 2 months ago

Hi mahesh213,

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

Note: Make sure your stored procedure returns select query. So that it can be executed fo fill the DataTable.

Namespaces

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

Controller

public class HomeController : Controller
{
    // GET: /Home/
    public ActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public JsonResult PayProcess(DateTime date)
    {
        SqlConnection con = new SqlConnection();
        string connectionstring = ConfigurationManager.ConnectionStrings["CMSR"].ConnectionString;
        con.ConnectionString = connectionstring;
        SqlCommand cmd = new SqlCommand("PayProcess", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Date", date);
        con.Open();
        string result = Convert.ToString(cmd.ExecuteScalar());
        con.Close();

        con = new SqlConnection();
        con.ConnectionString = connectionstring;
        DataTable dtDet = new DataTable();
        SqlDataAdapter sdaDet = new SqlDataAdapter();
        cmd = new SqlCommand(result, con);
        sdaDet.SelectCommand = cmd;
        sdaDet.Fill(dtDet);

        List<Dictionary<string, object>> lstReport = GetTableRows(dtDet);
        return Json(lstReport, JsonRequestBehavior.AllowGet);
    }

    public List<Dictionary<string, object>> GetTableRows(DataTable dtData)
    {
        List<Dictionary<string, object>>
        lstRows = new List<Dictionary<string, object>>();
        Dictionary<string, object> dictRow = null;

        foreach (DataRow dr in dtData.Rows)
        {
            dictRow = new Dictionary<string, object>();
            foreach (DataColumn col in dtData.Columns)
            {
                dictRow.Add(col.ColumnName, dr[col]);
            }
            lstRows.Add(dictRow);
        }
        return lstRows;
    }
}

View

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Index</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.5.5/angular.js"></script>
    <script type="text/javascript">
        var app = angular.module('MyApp', []);
        app.controller('MyController', ['$scope', '$http', function ($scope, $http, $window) {
            $scope.PayProcess = function GetItems() {
                var dt = $scope.date.toISOString().split('T')[0];
                $http({
                    method: 'Post',
                    url: 'Home/PayProcess/',
                    params: { date: dt }
                }).success(function (data, status, headers, config) {
                    $scope.Customers = data;
                }).error(function (data, status, headers, config) {
                    $scope.message = 'Unexpected Error';
                });
            }
        } ]);
    </script>
</head>
<body ng-app="MyApp" ng-controller="MyController">
    <div class="container">
        <div>
            <div id="wrapper" class="clearfix">
                <form name="userForm" novalidate>
                <div class="form-horizontal">
                    <div class="form-row">
                        <div class="col-md-4">
                            <label for="COI_Name">
                                Date
                            </label>
                            <input type="date" ng-model="date" />
                        </div>
                    </div>
                    <div>
                    </div>
                    <div class="form-group">
                    </div>
                    <div class="form-group" style="width: 120%; text-align: center; padding: 10px;">
                        <div class="col-md-offset-2 col-md-5">
                            <p>
                                <button ng-model="IsVisible" ng-click="PayProcess()">
                                    <span class="glyphicon glyphicon-ok"></span>Submitt
                                </button>
                            </p>
                        </div>
                    </div>
                </div>
                </form>
            </div>
            <table class="table table-responsive">
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Country</th>
                </tr>
                <tr ng-repeat="customer in Customers">
                    <td>{{customer.Id}}</td>
                    <td>{{customer.Name}}</td>
                    <td>{{customer.Country}}</td>
                </tr>
            </table>
        </div>
    </div>
</body>
</html>

Screenshot