Execute SQL query in Controller using AngularJS in ASP.Net MVC

Last Reply 2 months ago By dharmendr

Posted 2 months 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

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

In above code i have exceute query based on controls

assume in this code I don't have controls

if no controls present then execute simple query and or else controls query we have only simple query

just i need to execute this query and display records ongrid 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.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) {
                      $scope.Employees=data;

                    // Assign scope to bind gridview.
                }).error(function (err) {
                })
            }
        }]);
    </script>

Please try to modify in this code 

Thanks

Posted 2 months ago

Hi mahesh213,

You need to check the parameter value.

If there is value in parameter then control is present.

So replace the place holder with value and execute the query else execute the database query.

Check with the below code.

C#

[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();
    // Check values if not null then control present and replace value.
    if (values.details != null)
    {
        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);
}