Get item text id and value to save in database

Question

MY WHOLE CODE

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);
//}
//Your c# code should be
public ActionResult GetAllColumnNames(string tbl) // At this tbl I get the country name i also want to get Country ID
{
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 = from p in dt.AsEnumerable()
select new
{
CountryId = p.Field<int>("CountryId"),
CountryName = p.Field<string>("CountryName")
};
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);
}
}
}

SCRIPT CODE

$(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 < len; ++i)
options.push('<option value="' + data[i] + '">' + data[i] + '</option>');
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 < len; ++i)
options.push('<option value="' + data[i] + '">' + data[i] + '</option>');
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('<th>' + col + '</th>');
}
$table.find('thead').append('<tr>' + columns.join('') + '</tr>');
var rows = [];
for (var i = 0; i < json.length; i++) {
var row = json[i];
var tds = [];
for (var col in row) {
tds.push('<td>' + row[col] + '</td>');
}
rows.push('<tr>' + tds.join() + '</tr>');
}
$table.find('tbody').append(rows.join(''));
}
}