Dot Net Code Reference: December 2013

Monday 9 December 2013

Change TabIndex on Hide/Show Elements - JQuery

Hello Friends,

This scenario comes sometimes. when we need conditional tabbing with selecting some option will hide some elements or show some elements so in that case we need to change TabIndex dynamically like the following image.
When I select Cat2 in Dropdownlist all textboxes are visiable.

Now when I select Cat3 Textbox 4,6 and 9 are invisiable and Textbox7 comes in place of Textbox4. so after Textbox3 Cursor should move to Textbox7 and then Textbox5. so I need to change Tabindex of Textbox7 to 4.

for that I am using JQuery's nextuntil() function. so I can replace the following elements of Hidden Element to new TabIndex into the same div.

Here in Last Element of the Div I had added class="last" attribute. so JQuery function will stop assigning new Index to the Elements.

Friday 6 December 2013

Bind JQgrid with Dynamic Columns in MVC

Hello Friends,
In this blog I am creating JQgrid with dynamic columns.

In this example Every time I am giving Connection String and Query dynamically to JQgrid to bind the data.
Code is as follows

<div class="editor-label">
@Html.Label("ConnectionString")
</div>
<div class="editor-field">
@Html.TextBox("ConnectionString")
</div>

<div class="editor-label">
@Html.Label("Query")
</div>
<div class="editor-field">
@Html.TextArea("Query")
</div>

<button type="submit" id="btnGetData" name="Command" value="GetData" onclick="return GetData();" class="btn">Get Data</button>

<div id="dvJqGrid" class="dvjq">
<table id="grid" class="scroll"></table>
<div id="pager" class="scroll" style="text-align: center;"></div>
</div>

<script type="text/javascript">
function GetData() {
setTimeout(function () { $('#dvLoader').show();},1);
var ConnectionString = document.getElementById("ConnectionString").value;
var Query = document.getElementById("Query").value;

$("#dvJqGrid").empty();
$("#dvJqGrid").append($("<table>").attr("id", "grid"));
$("#dvJqGrid").append($("<div>").attr("id", "pager"));

$.ajax(
{
type: "POST",
url: "/QueryPad/JQGridGetDataWithColumn",
data: { "ConnectionString": ConnectionString, "Query": Query },
dataType: "json",
success: function (result) {
$("#Result").val(result.Msg);
colD = result.colData;
colN = JSON.parse(result.colNames);
colM = JSON.parse(result.colModel);

var SQL = ConnectionString;
var Oracle = ConnectionString;
jQuery("#grid").jqGrid({
jsonReader: {
cell: "",
id: "0"
},
url: '/QueryPad/JQGridGetData',
postData: { ConnectionString: ConnectionString, Query: Query, QueryType: QType },
datatype: 'json',
mtype: 'POST',
datastr: colD,
colNames: colN,
colModel: colM,
pager: jQuery('#pager'),
rowNum: 5,
page: 1,
rowList: [5, 10, 20, 50],
viewrecords: true,
loadonce: false
});
$('#dvLoader').hide();
},
error: function (x, e) {
$('#dvLoader').hide();
}
});
setTimeout(function () { $('#dvLoader').hide(); }, 1000);
return false;
}
</script>

public string JQGridGetData(string sidx, string sord, int page, int rows,string ConnectionString,string Query)
{
SqlConnection con = new SqlConnection(ConnectionString);
try
{
con.Open();
SqlTransaction sqlTran = con.BeginTransaction();
try
{
SqlCommand cmd1 = new SqlCommand(Query, con);
cmd1.Transaction = sqlTran;
DataTable dt = new DataTable();
SqlDataReader dr = cmd1.ExecuteReader();
dt.Load(dr);
if (dt.Rows.Count > 0)
{
string StringData = ConvertDataTabletoString(dt);
sqlTran.Commit();
con.Close();
return StringData;
}
else
{
Exception exx = new Exception();
throw exx;
}
}
catch (Exception ex)
{
sqlTran.Rollback();
con.Close();
ViewBag.Result = ex.Message;
return "";
}
}
catch (Exception ex)
{
con.Close();
return "";
}

}

public JsonResult JQGridGetDataWithColumn(string ConnectionString, string Query)
{
SqlConnection con = new SqlConnection(ConnectionString);
try
{
con.Open();
SqlTransaction sqlTran = con.BeginTransaction();
try
{
SqlCommand cmd1 = new SqlCommand(Query, con);
cmd1.Transaction = sqlTran;
DataTable dt = new DataTable();
SqlDataReader dr = cmd1.ExecuteReader();
dt.Load(dr);
if (dt.Rows.Count > 0)
{
sqlTran.Commit();
con.Close();
string StringData = ConvertDataTabletoString(dt);
string[] columns = dt.Columns.Cast<DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
string columnNames = ConvertStringArrayToString(columns);
string columnModel = ConvertStringArrayToStringModel(columns);
return Json(new { colData = StringData, colNames = columnNames, colModel = columnModel, Msg = "Data Successfully Loaded" }, JsonRequestBehavior.AllowGet);
}
else
{
Exception exx = new Exception();
throw exx;
}
}
catch (Exception ex)
{
sqlTran.Rollback();
con.Close();
ViewBag.Result = ex.Message;
return Json(new {Msg = ex.Message },JsonRequestBehavior.AllowGet);
}
}
catch (Exception ex)
{
con.Close();
return Json(new { Msg = ex.Message }, JsonRequestBehavior.AllowGet);
}
}

Bind JQgrid in MVC

Hello Friends,
Here is simple example how to bind JQgrid in MVC.

<table id="grid" class="scroll"></table>
<div id="pager" class="scroll" style="text-align: center;"></div>

$(document).ready(function () {

$("#grid").jqGrid({
url: "/QueryPad/GetData",
datatype: "json",
mtype: "GET",
colNames: ["Product ID", "Name", "Price"],
colModel: [
{ name: "ProductID", index: "ProductID" },
{ name: "Name", index: "Name" },
{ name: "Price", index: "Price" }
],
rowNum: 1,
rowList: [20, 50, 100],
pager: "#pager-json",
sortname: "ProductID",
sortorder: "ASC",
viewrecords: true,
caption: "Get Data"
});
});

public string GetData()
{
string ConnectionString = ""; // Put your connection string here
string Query = "Select * from Product";
SqlConnection con = new SqlConnection(ConnectionString);
try
{
SqlCommand cmd1 = new SqlCommand(Query, con);
DataTable dt = new DataTable();
SqlDataReader dr = cmd1.ExecuteReader();
dt.Load(dr);
string StringData = ConvertDataTabletoString(dt);
con.Close();
return StringData;
}
catch (Exception ex)
{
con.Close();
ViewBag.Result = ex.Message;
return "";
}
}

public string ConvertDataTabletoString(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;
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);
}

Open Dialogbox in jQuery UI MVC

Hello Friends,
Open a Dialogbox is required so manytimes in our porject. so use the following code for this.