Call Database from JSON in ASP.NET
In this Article, We will see how to use jQuery and JSON to get data from a database and loop it and showing the value in javascript through alert box.
The aim of this article is converting the datatable or dataset to json and get the values to on success method of the javscript ajax( you can use any control to assing the values from here) from the same page.
How many pages Used: Here I used only one default page.the javascript calls the method (getempDetails) in its code behind only.
What is JSON ?
JSON is a lightweight data-interchange format and platform independent. When you want to develop AJAX based applications using JavaScript or jQuery then to interchange the data JSON is very useful.
The jQuery call in the ASPX page:
<script type="text/javascript">
function test() {
$.ajax({
type: "POST",
data: "{}",
url: 'Default.aspx/getempDetails,
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (data) {
alert(data.d); // this is Json data object
var resultSet = data.d;
var html = "";
$.each(resultSet, function (key, obj) {
alert(obj.FirstName); // this is the result
});
},
error: function () {
alert('failure');
}
});
</script>
//get .net namespaces (ctrl+shift+f10 at class where u get error)
Code behind class is as follows (Note: please take methods into your class, dont directly copy and paste the code.)
[System.Web.Script.Services.ScriptService]
public partial class Class : System.Web.UI.Page
{
#region Page Events
// you can proceed with your regular events
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click1(object sender, EventArgs e)
{
}
#endregion
#region Ajax Request method
[WebMethod]
public static string getempDetails()
{
try
{
var s = new System.Web.Script.Serialization.JavaScriptSerializer();
SqlConnection con = new SqlConnection("give connection string");
SqlDataAdapter da = new SqlDataAdapter("Select * from tabEmployees", con);
DataSet ds = new DataSet();
da.Fill(ds);
string jsonData = GetJson(ds.Tables[0]);
return jsonData;
}
catch(Exception)
{
}
return json;
}
public static string GetJson(DataTable dt)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer =
new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<string, object>> rows =
new List<Dictionary<string, object>>();
Dictionary<string, object> row = null;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
#endregion
#region UserDefined Funcitons
public static string DataSetToJSON(DataSet ds)
{
Dictionary<string, object> dict = new Dictionary<string, object>();
foreach (DataTable dt in ds.Tables)
{
object[] arr = new object[dt.Rows.Count + 1];
for (int i = 0; i <= dt.Rows.Count - 1; i++)
{
arr[i] = dt.Rows[i].ItemArray;
}
dict.Add(dt.TableName, arr);
}
JavaScriptSerializer json = new JavaScriptSerializer();
return json.Serialize(dict);
}
#endregion
}
Conclusion: this article is simply specifies , how simply convert and serialize the data table or dataset to json and use the data according to our requirement.
