In this post we will cover simple steps to get table data from Sql query And bind data to select element Using Jquery And MVC using dictionary and without using models.
To do this we can use ajax which will get table data from sql table and bind to select and
create a dictionary in c# which use object parameters to bind dynamic data.
We will get our data to datatable and pass to an dictionary creator function.
public JsonResult GetTableDataInJsonFormat(Int64 id=0)
{
try
{
DataTable dt = GetAllColumns(Convert.ToInt64(PrimaryKey.ToString()));
ViewBag.AllColumns = GetColumnsInDictionaryFormat(dt);
return Json(new { success = true, msg = "", AllColumns = Newtonsoft.Json.JsonConvert.SerializeObject(ViewBag.AllColumns)}, JsonRequestBehavior.AllowGet);
}
catch(Exception ex)
{
return Json(new { success = false, msg = "Error: " + ex.Message.ToString(), AllColumns = "" }, JsonRequestBehavior.AllowGet);
}
}
static public Dictionary<String, Object> GetColumnsInDictionaryFormat(DataTable dt)
{
Dictionary<String, Object> Columns = new Dictionary<String, Object>();
List<Dictionary<String, Object>> rows = new List<Dictionary<String, Object>>();
try
{
foreach (System.Data.DataRow dr in dt.Rows)
{
Dictionary<String, Object> row = new Dictionary<string, object>();
row.Add("Id", dr["Id"].ToString());
row.Add("ColumnOneDynamicName", dr["ColumnOne"].ToString());
row.Add("ColumnTwoDynamicName", dr["ColumnTwo"].ToString());
rows.Add(row);
}
Columns.Add("rows", rows);
}
catch {}
return Columns;
}
This will convert our datatable to dictionary format to use in ajax or to process dictionary.
Now we can use it in view as below and bind to select.
function addColumns() {
var selectData = $('#dropdownId').find(':selected').val();
$.ajax({
type: "GET",
url: '/GetTableDataInJsonFormat/' + selectData,
data: '',
success: function (data) {
if (data.success) {
data = jQuery.parseJSON(data.AllColumns);
var str = '';
$.each(data['rows'], function (key, element) {
var tmpColumn = element["ColorCode"].toLowerCase() == "#ffffff" ? "black" : "white";
if (element["ColumnOneDynamicName"].toLowerCase() == "matchcase") {
str += "<option value='" + element['Id'] + "' selected='selected'>" + element['ColumnOneDynamicName'] + "</option>";
} else {
str += "<option value='" + element['Id'] + "' >" + element['ColumnOneDynamicName'] + "</option>";
}
});
$('#dropdownId').html(str);
} else {
$('#dropdownId').html('');
}
}
});
}