Display record on HTML Table based upon dynamic control values using AngularJS in ASP.Net MVC

Last Reply 14 days ago By dharmendr

Posted 14 days ago

Hi,

I have 2 tables in database like below

Report1

RId RName RNo Rquery

1 Invoice 233        select                OrderName,OrderDate,RAI_Item,RAI_Quantity,RAI_Price,RAI_Total f from Orders5 o inner join Items6 i on o.OrderId=i.OrderId where OrderDate between { FromDate} and {ToDate} and RAI_Item={RAI_Item} and OrderName={OrderName}

ReportSetting

RSId RId CName CType FromTable DisplayCName ValueCName Date

1 1 RAI_Item Dropdown MA_RAI RAI_Name RAI_Id null

2 1 FromDate DatePicker null null null Today

3 1 ToDate Datepicker null null null Yesterday

4 1 OrderName Textbox null null null null

After opening of form based upon report name it can display some relevant controllers with relevant values

Generate controls dynamically from database value using AngularJS in ASP.Net MVC

Ex:(i have picked some values from relevant controllers)

RAI_Item :34(I picked one value from database)

FromDate:19/3/2019
To :21/4/2019

OrderName :67 

after clicking of refresh button i need to Fire one query where i had stored in database (Report1 table) and replaced with relevant values in below query

Ex: select OrderName,OrderDate,RAI_Item,RAI_Quantity,RAI_Price,RAI_Total f from Orders5 o inner join Items6 i on o.OrderId=i.OrderId where OrderDate between { FromDate} and {ToDate} and RAI_Item={RAI_Item} and OrderName={OrderName}

what ever values i have mentioned in flower brackert values replace with our values (in controllers i have entered some values)

and generate a grid view like below format

Excepted O/p

OrderName OrderDate RAI_Item RAI_Quantity RAI_Price RAI_Total

67 3/20/2019 34 3 4 12

Could you please help me

You are viewing reply posted by: dharmendr 14 days ago.
Posted 14 days ago Modified on 11 days ago

Hi mahesh213,

Refer below code and modify accordingly.

Controller

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

    [HttpGet]
    public ActionResult GetReportNames()
    {
        TestEntities db = new TestEntities();
        var coun = db.Report1.Select(model => new { model.RId, model.RName }).ToList();
        return Json(coun, JsonRequestBehavior.AllowGet);
    }

    [HttpPost]
    public JsonResult getAll(int Id)
    {
        TestEntities db = new TestEntities();
        List<Details> employeeList = (from E in db.ReportSettings
                                        where E.RId == Id
                                        orderby E.RSId
                                        select new Details
                                        {
                                            RSId = E.RSId,
                                            RId = E.RId,
                                            CName = E.CName,
                                            CType = E.CType,
                                            ValueCName = E.ValueCName,
                                            DisplayCName = E.DisplayCName,
                                            FromTable = E.FromTable,
                                            Date = E.Date
                                        }).ToList();
        foreach (Details details in employeeList)
        {
            if (details.CType.ToLower() == "dropdown")
            {
                var coun = db.ReportSettings.Where(x => x.CType == details.CType && x.RId == details.RId && x.RSId == details.RSId).ToList();
                string fromTable = coun[0].FromTable;
                string displayCName = coun[0].DisplayCName;
                string valueCName = coun[0].ValueCName;
                // Generate dynamic query.
                string query = "SELECT " + displayCName + " AS Text," + valueCName + " AS Value FROM " + fromTable;
                // Execute dynamic query.
                SqlConnection con = new SqlConnection();
                string connectionstring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                con.ConnectionString = connectionstring;
                SqlCommand cmd = new SqlCommand(query, con);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                List<SelectListItem> values = new List<SelectListItem>();
                while (sdr.Read())
                {
                    values.Add(new SelectListItem
                    {
                        Text = sdr["Text"].ToString(),
                        Value = sdr["Value"].ToString()
                    });
                }
                con.Close();
                // Adding options for DropDownList.
                details.DDLValues = values;
            }
            else if (details.CType.ToLower() == "datepicker")
            {
                // Settting DatePicker value to display default text.
                switch (details.Date.ToLower())
                {
                    case "today":
                        details.Date = DateTime.Now.ToString("yyyy-MM-dd");
                        break;
                    case "yesterday":
                        details.Date = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
                        break;
                    default:
                        break;
                }
            }
        }

        return Json(employeeList, JsonRequestBehavior.AllowGet);
    }

    [HttpPost]
    public JsonResult PopulateTable(Detail values)
    {
        TestEntities db = new TestEntities();
        // Getting the query from Report1 table.
        string query = db.Report1.Where(x => x.RId == values.RId).Select(x => x.Rquery).FirstOrDefault();
        for (int i = 0; i < values.details.Count; i++)
        {
            string key = "{" + values.details[i].Text + "}";
            string value = "'" + values.details[i].Value + "'";
            // Replace the place holder with value.
            query = query.Replace(key, value);
        }
        // Execute your final query and return json result and bind the gridview on success function.
        // Your code to return json.
        return Json(null, JsonRequestBehavior.AllowGet);
    }

    public class Detail
    {
        public int RId { get; set; }
        public List<SelectListItem> details { get; set; }
    }

    public class Details
    {
        public int RSId { get; set; }
        public int? RId { get; set; }
        public string CName { get; set; }
        public string CType { get; set; }
        public string ValueCName { get; set; }
        public string DisplayCName { get; set; }
        public string FromTable { get; set; }
        public string Date { get; set; }
        public List<SelectListItem> DDLValues { get; set; }
    }
}

View

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.7/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <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("MyControll", ['$scope', '$http', '$filter', function ($scope, $http, $filter) {
            GetReportName();
            function GetReportName() {
                $scope.reports = [];
                $http({
                    method: 'Get',
                    url: '/Home/GetReportNames'
                }).success(function (data, status, headers, config) {
                    $scope.reports = data;
                }).error(function (data, status, headers, config) {
                    $scope.message = 'Unexpected Error';
                });
            }

            $scope.Change = function () {
                var Id1 = $scope.RName;
                if (Id1 != null) {
                    $http({
                        method: 'POST',
                        url: '/Home/getAll/',
                        params: { Id: Id1 }
                    }).success(function (data, status, headers, config) {
                        for (var i = 0; i < data.length; i++) {
                            if (data[i].Date != null) {
                                data[i].Date = new Date(data[i].Date);
                            }
                        }
                        $scope.items = data;
                    }).error(function (data, status, headers, config) {
                        $scope.items = 'Unexpected Error';
                    });
                }
            }

            function ConvertDate(d) {
                var parts = d.split(" ");
                var months = { Jan: "01", Feb: "02", Mar: "03", Apr: "04", May: "05", Jun: "06", Jul: "07", Aug: "08", Sep: "09", Oct: "10", Nov: "11", Dec: "12" };
                return parts[3] + "/" + months[parts[1]] + "/" + parts[2];
            }
            $scope.Save = function () {
                var Id1 = $scope.items;
                var rId = $scope.RName;
                var details = {};
                var array = [];
                for (var i = 0; i < $scope.items.length; i++) {
                    var data = {};
                    data.Text = $scope.items[i].CName;
                    if ($scope.items[i].CType.toLowerCase() == 'dropdown') {
                        data.Value = $scope.items[i].FromTable;
                    } else if ($scope.items[i].CType.toLowerCase() == 'datepicker') {
                        data.Value = ConvertDate($scope.items[i].Date.toString());
                    } else if ($scope.items[i].CType.toLowerCase() == 'textbox') {
                        data.Value = $scope.items[i].ValueCName;
                    }
                    array.push(data)
                    details.RId = rId;
                    details.details = array;
                }
                $http({
                    method: "Post",
                    url: "/Home/PopulateTable",
                    dataType: 'json',
                    headers: { "Content-Type": "application/json" },
                    data: '{values: ' + JSON.stringify(details) + '}'
                }).success(function (data) {
                    // Assign scope to bind gridview.
                }).error(function (err) {
                })
            }
        }]);
    </script>
</head>
<body ng-app="MyApp" ng-controller="MyControll">
    <div class="container">
        <div id="wrapper" class="clearfix">
            <form name="userForm" novalidate>
                <h4 class="modal-title" style="text-align: center;">
                    Report Details
                </h4>
                <div class="form-horizontal">
                    <div class="form-row">
                        <div class="col-md-4">
                            <label for="RName">
                                ReportName
                            </label>
                            <select class="form-control" id="RName" select2="" ng-model="RName" containercssclass="all"
                                    ng-options="c.RId as c.RName for c in reports" ng-disabled="disabled" ng-change="Change()">
                                <option value="">Select Name</option>
                            </select>
                        </div>
                    </div>
                </div>
            </form>
        </div>
        <br />
        <div class="well">
            <div ng-repeat="report in items">
                <div class="row">
                    <div class="col-md-2">
                        <label for="RNo">
                            {{report.CName}}
                        </label>
                    </div>
                    <div class="col-md-3" id="dvDynamic">
                        @*Dynamic Controls display based on CType Start*@
                        <select class="form-control" ng-model="report.FromTable" ng-show="report.CType.toLowerCase()=='dropdown'"
                                ng-options="c.Text as c.Text for c in report.DDLValues">
                            <option value="">Select</option>
                        </select>
                        <input type="date" class="form-control" ng-model="report.Date" ng-show="report.CType.toLowerCase()=='datepicker'" />
                        <input type="text" class="form-control" ng-model="report.ValueCName" ng-show="report.CType.toLowerCase()=='textbox'" />
                        @*Dynamic Controls display based on CType End*@
                    </div>
                </div>
            </div>
            <input type="button" value="Save" ng-click="Save()" />
        </div>
    </div>
</body>
</html>

Screenshots

The Form

Values in Controller

The query after replacing flower brackets