Introduction:
In this article we are discussing how to load data from database to Asp.Net Gridview using WebAPI.
Description:
We use WebAPI to get the data from database and then use the Jquery getJSON() method to Load JSON-encoded data from the server using a GET HTTP request.
Implementation:
In the New ASP.NET Project dialog, select the Empty template. Under “Add folders and core references for”, check WebForms , Web API. Click OK.
When using WEBAPI you will get data in client side and you need to manipulate it at client side so that the data will get populated in Gridivew. Point to note here is we cannot go with the normal approach of setting DataSource and calling DataBind method. We have various options to load data into grid like AngularJs, Jquery etc.
As mentioned earlier in this article I am using getJSON() method to fetch the value and then populate the data in gridview. Gridview will load as table at runtime we will access this table and then append the values to it.
Sample Code:
HTML:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<script src="https://code.jquery.com/jquery-2.1.4.js"></script>
<script type="text/javascript">
//Code to load the data to gridview on pageload
$(document).ready(function () {
//Here you need to provide the webapi address
$.getJSON('api/Customer')
.done(function (data) {
$.each(data, function (key, item) {
//Add the result to gridview
$("#GridView1").append("
<tr>
<td>" + item.CustomerID + "</td>
<td>" + item.CompanyName + "</td>
<td>" + item.ContactName + "</td>
<td>" + item.ContactTitle + "</td>
<td>" + item.Address + "</td>
</tr>
");
});
})
.fail(function (d) {
alert("error occured" + d.responseText);
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" Width="1000px" ShowHeaderWhenEmpty="true" CellPadding="0" CellSpacing="0">
</asp:GridView></div>
</form>
</body>
</html>
C#:
First we need to populate an empty row in GridView. Reason behind this is if we didn’t assign any datasource then the GridView won’t populate at runtime and we won’t be able to access it at client side.
protected void Page_Load(object sender, EventArgs e)
{
//Load and empty row to gridview
if (!IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.Add("CustomerID");
dt.Columns.Add("CompanyName");
dt.Columns.Add("ContactName");
dt.Columns.Add("ContactTitle");
dt.Columns.Add("Address");
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
WebAPI Code:
When we add the WebAPI references in your project you will see a Model folder and Controller folder.
Model
Here we add the Model classes which basically represents data in WebAPI. For this article we add Customer class. Right Click on the Model folder and choose Add and then select class file. Name it like Customer
public class Customer
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
public string Address { get; set; }
}
Controllers:
In controller we will add the code to fetch values from database. Right click on the Controllers folder and Choose Add and then select Controller. In code you can see an HttpGet entry added above to GetCustomers method. This is because Rest WebAPI is making use of verbs(Get,Post,Put,Delete) for each actions in Read,Create,Update,Delete respectively. You can get more details from here
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using WebApiSample.Models;
namespace WebApiSample.Controllers
{
public class CustomerController : ApiController
{
[HttpGet]
public IEnumerable<Customer> GetCustomers()
{
Customer[] aryCustomers = null;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWINDConnectionString"].ToString()))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SELECT TOP 10 CustomerID,CompanyName,ContactName,ContactTitle,Address FROM Customers";
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
List<Customer> lstcustomers = new List<Customer>();
while (reader.Read())
{
lstcustomers.Add(new Customer
{
CustomerID = reader["CustomerID"].ToString(),
CompanyName = reader["CompanyName"].ToString(),
ContactName = reader["ContactName"].ToString(),
ContactTitle = reader["ContactTitle"].ToString(),
Address = reader["Address"].ToString()
});
aryCustomers = lstcustomers.ToArray();
}
}
}
}
return aryCustomers;
}
}
}
AspdotnetCodehelp 