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
