JG Vimalan's Blog

It is all about coding!

Dynamic columns in jqGrid

In this post we are going to see how to display jqGrid with dynamic columns.
I am using the following plugins and languages for preparing this article,
a. jqGrid 4.5.4 plugin
b. jQuery 1.8.2
c. ASP.NET WebForms
d. C#
e. Visual Studio 2012

Step 1:

I am assuming that you already have an ASP.NET project configured with jqGrid plugin.
The first thing to do is, create a class as shown below to hold the column header names, model names and actual data generated at runtime.

public class Dashboard
{
private List _model = new List();
//at runtime you will know the column names, set them here.
public List Model
{
get
{
return _model;
}
set
{
_model = value;
}
}

private List _headers = new List();
//if you want to give more meaningful names for column, set them here.
//The order of model name should match with header name
public List Headers
{
get
{
return _headers;
}
set
{
_headers = value;
}
}
//Store the actual data generated at runtime here.
//I converted datatable generated at runtime in to List<Dictionary<string, object>>
public List<Dictionary<string, object>> DashboardContent { get; set; }

}

Step 2:

In your ASP.NET page, define table controls as shown below,

<table id=”ReportGrid”></table>
<div id=”ReportGridPager”></div>

Step 3:

In the code behind, write the WebMethod that returns the Dashboard object.

[WebMethod]
public static Dashboard GetReportDashboard()
{
//In the ‘GetDailyDashboard’ method, you will get dynamic data from database
//and build the datatable at runtime. Then get the columns, headers from datatable and set it to
//Model and Headers property in Dashboard object. Also convert the datatable to
//List<Dictionary<string, object>> object and set it to DashboardContent property.

return ReportQueryManager.GetDailyDashboard();
}

Step 4:

In your javascript file, which holds the logic to build jqGrid on table object use the following code,

Basically, in the AJAX success method, get the Dashboard property values and set them to
jqGrid properties and bind the data to jqGrid.

$.ajax({
url: methodUrl, //set the method url here
dataType: “json”,
type: ‘POST’,
data: null,
contentType: “application/json; charset=utf-8”,
success: function (data) {
var dataArray = data.d //here you get the Dashboard object

var reportModel = dataArray.Model //Model property in Dashboard object
var header = dataArray.Headers ////Header property in Dashboard object
var dashboardData = dataArray.DashboardContent //Actual data with column

var ColModel = [];
for (index in reportModel ) {
var colName = reportModel[index]
var colWidth = 100
var isFixed = false
var sortTypeValue = “int”
var alignPosition = ‘center’

if (colName == “Name”) {
isFixed = true
colWidth = 300
sortTypeValue = “text”
alignPosition = ‘left’
}
ColModel.push({ name: colName, index: name, frozen: isFixed, width: colWidth, sorttype: sortTypeValue,
align: alignPosition });
}

var customButton = function (options) {
ReportGrid.jqGrid(‘navButtonAdd’, pageSelector, options);
ReportGrid.jqGrid(‘navButtonAdd’, ‘#ReportGrid’ + “_toppager”, options);
};

ReportGrid.jqGrid({
datatype: “local”,
mtype: “GET”,
cmTemplate: { sortable: true, resizable: true },
height: $(document).height() – reduceGridHeight,
rowNum: 20,
rowList: [],
altRows: true,
altclass: ‘gridAltRowClass’,
gridview: true,
pgbuttons: false,
loadonce: true,
shrinkToFit: false,
autoencode: true,
sortname: null,
width: $(“#SerReportGridWrapper”).width(),
ignoreCase: true,
viewrecords: true,
sortorder: “desc”,
pager: pageSelector,
pgtext: null,
scrollrows: true,
loadui: ‘disable’,
colNames: header, //set the header object here
colModel: SerColModel, //set the model object here
caption: “Dashboard”
});

ReportGrid.jqGrid(‘navGrid’, ‘#ReportGridPager’, {
view: true, add: false, edit: false, del: false, refresh: false, search: false, viewtext: ‘View Selected Row’
}, {}, {}, {}, {},
{ width: 400 });

ReportGrid.jqGrid(‘setFrozenColumns’);

customButton({
caption: “Export To Excel”,
title: “Export To Excel”,
onClickButton: function () {
if (serReportGrid.jqGrid(‘getGridParam’, ‘records’) == 0) {
Block(ReportGridBox, “No record(s) found to export”)
}
else {
Export()
}
}
});

ReportGrid[0].addJSONData(dashboardData);
},
error: function (d) {
if (d.statusText != ‘abort’) {
$.unblockUI();
$(“#ErrorMessage”).show()
}
}
});

Thats all. Now, you will be able to display dynamic columns in your jqGrid.

Advertisements

February 23, 2014 Posted by | ASP.NET, C#.NET, JavaScript, jqGrid, JQuery, JSON | , | 2 Comments

Task Scheduler failed to start “\Event Viewer Tasks\” task for user “”. Additional Data: Error Value: 2147943785.

I got the above error when I tried to run an .exe as a task in Task Scheduler. The .exe connects to the database using a ‘Service Account’. So, in order to fix the above error, I added the “service account”in the “log on as a batch job”policy. Refer the snapshot below,

Local Group Policy Editor

December 1, 2013 Posted by | C#.NET, Windows Server 2008R2 | 1 Comment

Using files in C# unit testing

In order to use files during unit testing, the files must be added in the unit test project as shown below,

To demonstrate, I am using an xml file in my unit test.

The file should be available in the deployment folder for test methods to access. In order to achieve that, browse to Test -> Edit Test Settings -> Local.testsettings in Visual Studio menu. Test Settings window will be displayed as shown below,

Go to Deployment and Check ‘Enable Deployment’. Now add the file(s) (from test project folder) using the “Add File…” button.

In the unit test, specify the xml file name in the DeploymentItem attribute as shown below,

Now, when test is running, the file will be available in the deployment directory and it can be accessed using TestContext.DeploymentDirectory.

October 5, 2012 Posted by | C#.NET, VS 2010 | Leave a comment

This OdbcTransaction has completed; it is no longer usable.

I got this exception when I was trying to perform multiple row insert in a DB2 database using Enterprise Library 5.0 and DbTransaction.

Cause of this exception:

I was trying to invoke ExecuteNonQuery method from Database object instead of DbCommand object. And, when one of the SQL failed (due to syntax or data problem), the catch block caught the exception and tried to rollback but DbTransaction.Rollback failed and it was throwing the above exception.

Solution:

In order to solve this issue, set the transaction object in DBCommand.Transaction property and ExecuteNonQuery in DbCommand. Plus, the connection should be in open state when trying to rollback. The working code sample is given below,

internal static bool InsertRecords(List<string> sqlCollection)
{
Database database = DatabaseFactory.CreateDatabase(Utility.GetDB2ConnectionName);

using (DbConnection dbConnection = database.CreateConnection())
{
DbCommand dbCommand = dbConnection.CreateCommand();
DbTransaction dbTransaction = null;

try
{
dbConnection.Open();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Transaction = dbTransaction;

foreach (string sql in sqlCollection)
{
dbCommand.CommandText = sql;
dbCommand.CommandType = CommandType.Text;
dbCommand.ExecuteNonQuery();
}

dbTransaction.Commit();
return true;
}
catch (Exception error)
{
Log.Error(error);

if (dbTransaction != null)
dbTransaction.Rollback();
}
}

return false;
}

October 2, 2012 Posted by | ASP.NET, C#.NET, DB2, Enterprise Library 5.0, VS 2010 | | Leave a comment

DbTransaction and EnterpriseLibrary Data Application Block 5.0

Using DbTransaction with EnterpriseLibrary Data Application Block 5.0 is very easy. In this post, i am inserting multiple rows in 5 tables in sql server database. If insert fails in one table, then all transaction will be rollback.

The complete code is given below,

using System;
using System.Collections.Generic;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Db2_EntLib5._0_Transaction
{
class Program
{
static void Main(string[] args)
{
List<string> sqlCollection = new List<string>();

sqlCollection.Add(“INSERT INTO TABLE_A (Name) VALUES (‘A0’)”);
sqlCollection.Add(“INSERT INTO TABLE_A (Name) VALUES (‘A1’)”);
sqlCollection.Add(“INSERT INTO TABLE_A (Name) VALUES (‘A2’)”);
sqlCollection.Add(“INSERT INTO TABLE_A (Name) VALUES (‘A3’)”);
sqlCollection.Add(“INSERT INTO TABLE_B (Name) VALUES (‘B’)”);
sqlCollection.Add(“INSERT INTO TABLE_C (Name) VALUES (‘C’)”);
sqlCollection.Add(“INSERT INTO TABLE_D (Name) VALUES (‘D0’)”);
sqlCollection.Add(“INSERT INTO TABLE_D (Name) VALUES (‘D1’)”);
sqlCollection.Add(“INSERT INTO TABLE_E (Name) VALUES (‘12345678901234567890123456789012345678901234567890’)”);
sqlCollection.Add(“INSERT INTO TABLE_F (Name) VALUES (null)”); //null value is not allowed. So, exception will occur here.

Database database = DatabaseFactory.CreateDatabase(“DBConn”);

using (DbConnection connection = database.CreateConnection())
{
DbTransaction transaction = null;

try
{
if (connection.State != System.Data.ConnectionState.Open)
connection.Open();

transaction = connection.BeginTransaction();

foreach (string sql in sqlCollection)
{
database.ExecuteNonQuery(transaction, System.Data.CommandType.Text, sql);
}

transaction.Commit();
}
catch (Exception error)
{
Console.WriteLine(error);
transaction.Rollback();
}
finally
{
transaction.Dispose();
connection.Close();
}
}

Console.Read();
}
}
}

September 29, 2012 Posted by | C#.NET, Enterprise Library 5.0 | , | Leave a comment

Clickable GridView with Postback in ASP.NET 4.0

In this post, I will show you how to make a GridView row clickable and read the selected row values after postback.

Step 1:

Create an ASP.NET 4.0 web application. Add a gridview control in the application.

Step 2:

Create RowCreated and RowDataBound events and set their values as shown below,

protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes.Add(“onclick”, “CreatePostBack(this);”);
}
}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
e.Row.Attributes.Add(“onmouseover”, “mouseIn(this);”);
e.Row.Attributes.Add(“onmouseout”, “mouseOut();”);
}
}

Here, the CreatePostBack(this); is a javascript function. In this function call the _doPostBack function and pass the necessary parameters as shown below,

<script type=”text/javascript”>
function mouseIn(row) {
document.body.style.cursor = ‘pointer’;
}
function mouseOut() {
document.body.style.cursor = ‘auto’;
}

function __doPostBack(eventTarget, eventArgument) {
var theForm = document.forms[‘ctl01’];
if (!theForm) {
theForm = document.ctl01;
}
if (!theForm.onsubmit || (theForm.onsubmit() != false)) {
theForm.__EVENTTARGET.value = eventTarget;
theForm.__EVENTARGUMENT.value = eventArgument;
theForm.submit();
}
}

function CreatePostBack(row) {

__doPostBack(‘GridView1’, row.rowIndex);

//in this example, i am just passing the rowIndex. You can read the selected row values and pass them.
}
</script>

Step 3:

Add two hidden fields as shown below for capturing the  event target and event argument,

<input type=”hidden” name=”__EVENTTARGET” id=”__EVENTTARGET” value=”” />
<input type=”hidden” name=”__EVENTARGUMENT” id=”__EVENTARGUMENT” value=”” />

Step 4:

In the Page Load event, set the data source for the gridview control and the code for reading the values when there is a post back,

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GridView1.DataSource = GetUsers(); //use your method for getting datasource value here
GridView1.DataBind();
}
else if (Request.Form[“__EVENTTARGET”] == “GridView1”)
{
var arg = Request.Form[“__EVENTARGUMENT”];
//now you got the selected row values
}
}

Step 5:

Thats all! The Gridview markup is given below for your reference,

<asp:Content ID=”Content2″ ContentPlaceHolderID=”MainContent” runat=”server”>
<input type=”hidden” name=”__EVENTTARGET” id=”__EVENTTARGET” value=”” />
<input type=”hidden” name=”__EVENTARGUMENT” id=”__EVENTARGUMENT” value=”” />
<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”false”
onrowcreated=”GridView1_RowCreated” onrowdatabound=”GridView1_RowDataBound”>
<Columns>

….

</Columns>
</asp:GridView>
</asp:Content>

So, whenever a gridview row is clicked, the postback will happen and you can read the selected row values.

The working sample can be downloaded from here,

https://docs.google.com/file/d/0By3at1G2EGgOYTNYQ1ZuQi11aXc/edit

File->Download

September 15, 2012 Posted by | ASP.NET, C#.NET, VS 2010 | , | 9 Comments