$(function () {
$.ajax({
url: '@Url.Action("GetAllTableNames", "home")',
data: {},
type: 'post',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (data) {
var ddl = $('#ddlTableNames');
var options = [];
for (var i = 0, len = data.length; i ' + data[i] + '');
ddl.append(options.join(''));
},
error: function (a, b, c) {
console.log(a, b, c);
}
});
});
function populateColumns(ddl){
var table = ddl.value;
$.ajax({
url: '@Url.Action("GetAllColumnNames", "home")',
data: JSON.stringify({'tbl':table}),
type: 'post',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (data) {
var ddl = $('#ddlColumnNames');
ddl.empty();
var options = [];
for (var i = 0, len = data.length; i ' + data[i] + '');
ddl.append(options.join(''));
},
error: function (a, b, c) {
console.log(a, b, c);
}
});
}
function getdata() {
var table = $('#ddlTableNames').val();
var columns = $('#ddlColumnNames').val().join(',');
debugger;
$.ajax({
url: '@Url.Action("GetTableData", "home")',
data: JSON.stringify({ 'tableName': table, 'columnNames':columns }),
type: 'post',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (data) {
debugger;
var json = JSON.parse(data);
generateTable(json)
},
error: function (a, b, c) {
console.log(a, b, c);
}
});
}
function generateTable(json) {
var $table = $('#tblDynamic');
$table.find('thead').empty()
$table.find('tbody').empty()
if (json && json.length > 0) {
var header = json[0];
var columns = [];
for (var col in header) {
columns.push('' + col + '');
}
$table.find('thead').append('' + columns.join('') + '');
var rows = [];
for (var i = 0; i ' + row[col] + '');
}
rows.push('' + tds.join() + '');
}
$table.find('tbody').append(rows.join(''));
}
}
// tbl i get the text value but want to get the ID
public class HomeController : Controller
{
string constr = "CONNECTION STRING";
public ActionResult Index()
{
return View();
}
public ActionResult GetAllTableNames()
{
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("select CountryName,CountryId from Countries", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
var data = dt.AsEnumerable().Select(k => k["CountryName"].ToString());
return Json(data);
}
public ActionResult GetAllColumnNames(string tbl)
{
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("SELECT c.CountryId, c.CountryName, s.CityId,s.CityName FROM (Countries c INNER JOIN Cities s ON c.CountryId = s.StateId) where c.CountryName= @tbl ", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.Parameters.AddWithValue("@tbl", tbl);
DataTable dt = new DataTable();
da.Fill(dt);
var data = dt.AsEnumerable().Select(k => k["CityName"].ToString());
return Json(data);
}
public ActionResult GetTableData(string tableName, string columnNames)
{
SqlConnection con = new SqlConnection(constr);
SqlCommand cmd = new SqlCommand("select " + columnNames + " from " + tableName, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
string data = JsonConvert.SerializeObject(dt);
return Json(data);
}
}
What I have tried:
i am getting the city name but try to get city id