Introduction
Many of the developers who are still confused how to use Flexigrid in ASP.NET may find this article very helpful. Flexigrid is basically a jQuery grid that has an extensive set of features. While using Flexigrid we usually retrieve data from the database via Web Services. It has features for performing searches and Add, Update and Delete operations on the data displayed in the grid. It has provision for column resizing.
Background
Many of the examples that you find on internet are usually having SQL Server as the database but this demo is based on Oracle as the back end. I would also like to mention that there is no provision of client side paging on Flexigrid yeah I know it sucks but that's how it is.
Using the code
In order to better understand the functioning of the grid and how to use it create a table in the database whose data we wish to be displayed in the Flexigrid. I have created a table whose data I want to be displayed in the grid, I have also create a stored procedure by the name of "Flex" to get the data from that table.
I have an entity class by the name of Employee in my code which is as follows:
public class Employee{
public int id { get; set; }
public string fName { get; set; }
public string lName { get; set; }
public string uRole { get; set; }
public string sal { get; set; }
}
This method will establish connectivity with the database and return data in JSON format which we can feed to any jQuery table. This will serves as the input for our flexigrid.[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public string GetData()
{
List<Employee> lst = new List<Employee>();
string strConn = ConfigurationManager.ConnectionStrings["FlexDb"].ConnectionString;
OleDbConnection cnx = new OleDbConnection(strConn);
OleDbCommand cmd = new OleDbCommand("Flex",cnx);
cnx.Open();
cmd.CommandType = CommandType.StoredProcedure;
OleDbDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
Employee e1 = new Employee();
e1.id = Convert.ToInt32(dataReader["USER_ID"]);
e1.fName = dataReader["FIRST_NAME"].ToString();
e1.lName = dataReader["LAST_NAME"].ToString();
e1.uRole = dataReader["USER_ROLE"].ToString();
e1.sal = dataReader["SALARY"].ToString();
lst.Add(e1);
}
var jss = new JavaScriptSerializer();
return jss.Serialize(lst);
}
Calling the webservice method from our .aspx page
Create an ASPX page to the site. Include flexigrid.cs, flexigrid.js and jquery.js to the header section of the page. I have used HTML5 in my project, my webservice name is FlexService. I am calling the GetData method of my webservice and retrieving json from the method and feeding that to my flexigrid. I will emphasize on this, please make sure to add a jquery reference before the flexigrid.js reference.<!DOCTYPE html />
<html lang="en">
<head>
<meta charset="UTF-8" />
<title>Flex Demo</title>
<script src="Scripts/jquery-1.2.6.min.js" type="text/javascript"></script>
<link rel="Stylesheet" href="Styles/FlexCSS/flexigrid.css" />
<link rel="Stylesheet" href="Styles/FlexCSS/flexigrid.pack.css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/jquery-ui.min.js"></script>
<script type="text/javascript" src="Scripts/FlexJS/flexigrid.js"></script>
<script type="text/javascript" src="Scripts/FlexJS/flexigrid.pack.js"></script>
<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/themes/base/jquery-ui.css" />
</head>
<body>
<form runat="server">
<div>
<table id="FlexTable" runat="server">
</table>
</div>
</form>
</body>
<script type="text/javascript">
$(document).ready(function () {
var obj;
$.ajax({
type: "post",
contentType: "application/json",
url: "FlexService.asmx/GetData",
data: "{}",
dataType: 'json',
success: function (result) {
obj = $.parseJSON(result.d);
//add data
$("#FlexTable").flexAddData(formatEmployeeResults(obj));
}
});
// init flexigrid
$("#FlexTable").flexigrid({
dataType: 'json',
colModel: [{
display: 'ID',
name: 'id',
width: 90,
sortable: true,
align: 'center'
}, {
display: 'First Name',
name: 'fName',
width: 120,
sortable: true,
align: 'left'
}, {
display: 'Last Name',
name: 'lName',
width: 120,
sortable: true,
align: 'left'
}, {
display: 'Role',
name: 'uRole',
width: 120,
sortable: true,
align: 'left'
},
{
display: 'Salary',
name: 'sal',
width: 80,
sortable: true,
align: 'left'
}],
buttons: [{ name: 'Add', bclass: 'add', onpress: function () { } },
{ name: 'Edit', bclass: 'edit', onpress: function () { } },
{ name: 'Delete', bclass: 'delete', onpress: function () { } },
{ separator: true }
],
searchitems: [{
display: 'ID',
name: 'id'
}, {
display: 'First Name',
name: 'fName',
isdefault: true
}],
sortname: "id",
sortorder: "asc",
usepager: true,
title: 'Employee',
useRp: true,
rp: 5,
showTableToggleBtn: true,
width: 590,
height: 200
});
function formatEmployeeResults(Employee) {
var rows = Array();
for (i = 0; i < Employee.length; i++) {
var item = Employee[i];
rows.push({ cell: [item.id,
item.fName,
item.lName,
item.uRole,
item.sal]
});
}
return {
total: Employee.length,
page: 1,
rows: rows
}
}
});
</script>
</html>
No comments:
Post a Comment