Add and Delete Control Names in database if not exists based on condition using AngularJS in ASP.Net MVC

Last Reply 19 days ago By dharmendr

Posted 19 days ago

Hi,

Check and display control based on Database column using AngularJS in ASP.Net MVC

after clicking of button need to chek whether relevant control present in in Rquery or not

if not delete that control from database(If i have added before)

Ex:

SELECT dbo.emp.name, dbo.edata.oid, dbo.edata.qty, dbo.edata.Rate, dbo.edata.MDate FROM dbo.edata INNER JOIN dbo.emp ON dbo.edata.id = dbo.emp.id where emp.id = {Empname} and edata.MDate >= {FDate} and emp.id = {Empname1} and edata.MDate <= {TDate}

in above query i have 4 controls

EmpName ,Fdate Empname1 and Tdate

after creating of controls these can be saved to database

like below

RId RSId CType Cname FromTable DisplayCname Valuecname Date

1 1 dropdown EmpName emp name id null

1 2 datepicker FDate null null null Today

1 3 dropdown EmpName1 emp name id null

1 4 datepicker TDate null null null Yesday

Later i have updated my query like below

in below query i have added one new control Gender and Removed 2 controls from that query

SELECT dbo.emp.name, dbo.edata.oid, dbo.edata.qty, dbo.edata.Rate, dbo.edata.MDate FROM dbo.edata INNER JOIN dbo.emp ON dbo.edata.id = dbo.emp.id where emp.id = {Empname} and edata.MDate >= {FDate} and Gender={Gender}

After clciking of edit button i need to remove EmpName1 and TDate from database since these controls are present in query

and add one control Gender since i have added new

RId RSId CType Cname FromTable DisplayCname Valuecname Date

1 1 dropdown EmpName emp name id null

1 2 datepicker FDate null null null Today

1 5 Gender

Could you please help me

Posted 19 days ago

Hi mahesh213,

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

Model

public class OrderDetails
{
    public Report1 Orders { get; set; }
    public List<ReportSetting> Items { get; set; }
}

Controller

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

    public JsonResult GetReports()
    {
        TestEntities db = new TestEntities();
        return Json(db.Report1.ToList(), JsonRequestBehavior.AllowGet);
    }

    [HttpPost]
    public JsonResult Edit(int rID)
    {
        TestEntities db = new TestEntities();
        Report1 report = (from o in db.Report1 where o.RId == rID select o).FirstOrDefault();
        string query = report.RQuery;
        System.Text.RegularExpressions.Regex regex = new System.Text.RegularExpressions.Regex(@"\{.*?\}");
        System.Text.RegularExpressions.MatchCollection matches = regex.Matches(query);
        // Get Placeholder from RQuery.
        List<string> cnames = new List<string>();
        foreach (var item in matches)
        {
            cnames.Add(item.ToString().Replace("{", "").Replace("}", "").Trim());
        }
        // Delete CName from ReportSettings that doesnot exist in RQuery.
        List<ReportSetting> deleteSettings = (from i in db.ReportSettings
                                                where i.RId == rID && !cnames.Contains(i.CName)
                                                select i).ToList();
        foreach (ReportSetting reportSetting in deleteSettings)
        {
            db.ReportSettings.DeleteObject(reportSetting);
            db.SaveChanges();
        }
        // Get ReportSettings records.
        List<ReportSetting> listSetting = new List<ReportSetting>();
        var settings = (from i in db.ReportSettings
                        where i.RId == rID
                        select new
                        {
                            RSId = i.RSId,
                            CName = i.CName,
                            CType = i.CType,
                            FromTable = i.FromTable,
                            DisplayCName = i.DisplayCName,
                            ValueCName = i.ValueCName,
                            Date = i.Date,
                        }).ToList();
        foreach (var setting in settings)
        {
            listSetting.Add(new ReportSetting { RSId = setting.RSId, CName = setting.CName, CType = setting.CType, FromTable = setting.FromTable, DisplayCName = setting.DisplayCName, ValueCName = setting.ValueCName, Date = setting.Date });
        }
        // Get new CNames.
        var settingsNotInDb = cnames.Where(p => !settings.Any(p2 => p2.CName == p));
        foreach (var item in settingsNotInDb)
        {
            listSetting.Add(new ReportSetting { RId = rID, CName = item.Trim(), CType = "" });
            // Insrting new CNames in database.
            db.ReportSettings.AddObject(new ReportSetting { RSId = db.ReportSettings.Max(x => x.RSId) + 1, RId = rID, CName = item.Trim(), CType = "" });
            db.SaveChanges();
        }

        OrderDetails details = new OrderDetails();
        details.Orders = report;
        details.Items = listSetting;
        return Json(details, JsonRequestBehavior.AllowGet);
    }
}

View

<html>
<head>
    <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" src="https://cdn.jsdelivr.net/npm/angular-utils-pagination@0.11.1/dirPagination.js"></script>
    <script type="text/javascript">
        var app = angular.module('MyApp', ['angularUtils.directives.dirPagination'])
        app.controller("MyController", ['$scope', '$http', '$anchorScroll', 'myService', function ($scope, $http, $anchorScroll, myService) {
            var getReports = myService.GetReports();
            getReports.then(function (response) {
                $scope.reports = response.data;
            });
            var RId;
            //Edit code for ReportSettings
            $scope.Edit = function (rId) {
                RId = rId;
                $.post("/Home/Edit/", { rID: rId }, function (r) {
                    RId = r.Orders.RId;
                    var id1 = RId;
                    $scope.Details = [];
                    for (var i = 0; i < r.Items.length; i++) {
                        var customer = {};
                        var itemId = r.Items[i].RSId;
                        customer.RId = r.Orders.RId;
                        customer.RSId = r.Items[i].RSId;
                        customer.CName = r.Items[i].CName;
                        customer.CType = r.Items[i].CType;
                        customer.FromTable = r.Items[i].FromTable;
                        customer.DisplayCName = r.Items[i].DisplayCName;
                        customer.ValueCName = r.Items[i].ValueCName;
                        customer.Date = r.Items[i].Date;
                        $scope.Details.push(customer);
                        if (r.Items[i].CType.toLowerCase() == "dropdown") {
                            $scope.Details[i]["isDisabledDate"] = true;
                            $scope.Details[i]["isDisabledFromTable"] = false;
                            $scope.Details[i]["isDisabledDisplayCName"] = false;
                            $scope.Details[i]["isDisabledValueCName"] = false;
                            $scope.Details[i].Date = "";
                        }
                        else if (r.Items[i].CType.toLowerCase() == "datepicker") {
                            $scope.Details[i]["isDisabledFromTable"] = true;
                            $scope.Details[i]["isDisabledDisplayCName"] = true;
                            $scope.Details[i]["isDisabledValueCName"] = true;
                            $scope.Details[i]["isDisabledDate"] = false;
                            $scope.Details[i].FromTable = "";
                            $scope.Details[i].DisplayCName = "";
                            $scope.Details[i].ValueCName = "";
                        }
                        else if (r.Items[i].CType.toLowerCase() == "textbox") {
                            $scope.Details[i]["isDisabledFromTable"] = true;
                            $scope.Details[i]["isDisabledDisplayCName"] = true;
                            $scope.Details[i]["isDisabledValueCName"] = true;
                            $scope.Details[i]["isDisabledDate"] = true;
                            $scope.Details[i].FromTable = "";
                            $scope.Details[i].DisplayCName = "";
                            $scope.Details[i].ValueCName = "";
                            $scope.Details[i].Date = "";
                        }
                    }
                    $scope.$apply();
                });
            }

        } ]);
        app.service("myService", function ($http) {
            this.GetReports = function (user) {
                return $http.get("/Home/GetReports");
            }
        });
    </script>
</head>
<body ng-app="MyApp" ng-controller="MyController">
    <div id="dvContainer">
        <div>
            <div class="table-responsive ">
                <table id="dvData" cellpadding="12" class="table table-bordered table-hover table-striped"
                    style="margin-left: 20px; margin-right: 20px;">
                    <tr class="success">
                        <th><b>RNo</b></th>
                        <th><b>RName</b></th>
                        <th><b>RQuery</b></th>
                        <th><b>ControlActions</b></th>
                    </tr>
                    <tr dir-paginate="report in reports|itemsPerPage:10">
                        <td>
                            <input type="hidden" ng-model="report.RId" />{{report.RNo}}
                        </td>
                        <td>{{report.RName}}</td>
                        <td>{{report.RQuery }}</td>
                        <td><a class="glyphicon glyphicon-edit" ng-click="Edit(report.RId)" href=""></a></td>
                    </tr>
                </table>
            </div>
        </div>
    </div>
    <hr />
    <div id="Div1">
        <div>
            <div class="table-responsive ">
                <table id="tblOrders" class="table table-responsive table-bordered">
                    <tr class="success">
                        <th>Cname</th>
                        <th>Ctype</th>
                        <th>From Table</th>
                        <th>DisplayCName</th>
                        <th>ValueCName</th>
                        <th>Date</th>
                    </tr>
                    <tbody dir-paginate="detail in Details|itemsPerPage:10">
                        <tr>
                            <td>{{detail.CName}}</td>
                            <td>
                                <select id="ddlCType" ng-model="detail.CType" class="form-control" ng-change="TypeChange(CType,detail.Cname)">
                                    <option value="">Select Value</option>
                                    <option value="Dropdown">Dropdown</option>
                                    <option value="Datepicker">DatePicker</option>
                                    <option value="Textbox">Textbox</option>
                                </select>
                            </td>
                            <td><input type="text" class="form-control" ng-model="detail.FromTable" ng-disabled="detail.isDisabledFromTable" /></td>
                            <td><input type="text" class="form-control" ng-model="detail.DisplayCName" ng-disabled="detail.isDisabledDisplayCName" /></td>
                            <td><input type="text" class="form-control" ng-model="detail.ValueCName" ng-disabled="detail.isDisabledValueCName" /></td>
                            <td>
                                <select id="ddlDate" ng-model="detail.Date" class="form-control" ng-disabled="detail.isDisabledDate">
                                    <option value="">Select</option>
                                    <option value="Today">Today</option>
                                    <option value="Yesterday">Yesterday</option>
                                </select>
                            </td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</body>
</html>

When you click on Edit based on the Rid Cname are removed from ReportSetting table if not exist in RQuery and new CNames are added in the ReportSetting table.