Awarded as DotnetFunda Most Valuable Professional (DNF MVP)

I am happy to inform you all that I have been awarded as DotNetFunda Most Valuable Professional (DNF MVP) for the contributions made to DotNetFunda.com.

Thanks Sheo Sir and other judging panel members for selecting me also in this Years’s DotnetFunda MVP.

dnfmvp

Leave a comment

How to hide columns based on values selected in checkbox

Introduction:

How to hide columns based on values selected in checkbox

Description:

In this article I am going to explain how to hide columns in grid view based on user selection in checkbox. We can achieve this requirement with both client side and server side approach. Here I am going to explain the client side approach where we uses Jquery to hide column in gridview.

Gridview will render as table with header and rows at runtime, we will use jquery selector to access this header and calculate selected column index and then hide or show accordingly.

Function to hide and display Column

 <script type="text/javascript">
        $(document).ready(function () {
            //Attach click to checkboxlist
            $("#CheckBoxList1").click(function () {
                //loop through each item in checkboxlist
                $("[id*=CheckBoxList1] input").each(function () {
                    //Get the Gridview header row
                    var tblhead = $("#GridView1 th");
                    //Get the selected column index in gridview
                    var columnIndex = tblhead.index($("table[id*=GridView1] th:contains('" + $(this).next().html() + "')")) + 1;
                    //check if current items in checkboxlist is selected or not
                    if ($(this).is(":checked")) {
                        //if so then hide that column
                        $("table[id*=GridView1]").find("th:nth-child(" + columnIndex + "), td:nth-child(" + columnIndex + ")").hide();
                    } else {
                        //if not then display the column
                        $("table[id*=GridView1]").find("th:nth-child(" + columnIndex + "), td:nth-child(" + columnIndex + ")").show();
                    }
                });
            });
        });
    </script>

Complete Code

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    	<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
    <script src="//code.jquery.com/jquery-1.10.2.js"></script>
    <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
    	<link rel="stylesheet" href="/resources/demos/style.css">
    <script type="text/javascript">
        $(document).ready(function () {
            //Attach click to checkboxlist
            $("#CheckBoxList1").click(function () {
                //loop through each item in checkboxlist
                $("[id*=CheckBoxList1] input").each(function () {
                    //Get the Gridview header row
                    var tblhead = $("#GridView1 th");
                    //Get the selected column index in gridview
                    var columnIndex = tblhead.index($("table[id*=GridView1] th:contains('" + $(this).next().html() + "')")) + 1;
                    //check if current items in checkboxlist is selected or not
                    if ($(this).is(":checked")) {
                        //if so then hide that column
                        $("table[id*=GridView1]").find("th:nth-child(" + columnIndex + "), td:nth-child(" + columnIndex + ")").hide();
                    } else {
                        //if not then display the column
                        $("table[id*=GridView1]").find("th:nth-child(" + columnIndex + "), td:nth-child(" + columnIndex + ")").show();
                    }
                });
            });
        });
    </script>
</head>
<body>
<form id="form1" runat="server">
        <asp:CheckBoxList ID="CheckBoxList1" runat="server">
            <asp:ListItem Value="AddressID">AddressID</asp:ListItem>
            <asp:ListItem Value="AddressLine1">AddressLine1</asp:ListItem>
            <asp:ListItem Value="City">City</asp:ListItem>
            <asp:ListItem Value="StateProvinceID">StateProvinceID</asp:ListItem>
            <asp:ListItem Value="PostalCode">PostalCode</asp:ListItem>
            <asp:ListItem Value="ModifiedDate">ModifiedDate</asp:ListItem>
        </asp:CheckBoxList>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource2">
            <Columns>
                <asp:BoundField DataField="AddressID" HeaderText="AddressID" />
                <asp:BoundField DataField="AddressLine1" HeaderText="AddressLine1" />
                <asp:BoundField DataField="City" HeaderText="City" />
                <asp:BoundField DataField="StateProvinceID" HeaderText="StateProvinceID" />
                <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" />
                <asp:BoundField DataField="ModifiedDate" HeaderText="ModifiedDate" />
            </Columns>
        </asp:GridView>

        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
            SelectCommand="SELECT top 20 [AddressID], [AddressLine1],City,StateProvinceID,PostalCode,ModifiedDate FROM Person.Address"></asp:SqlDataSource>
    </form>

</body>
</html>

demo

, , , , , ,

Leave a comment

How to load data from database to Asp.Net Gridview using WebAPI

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.

2016-04-30 19_11_49-Microsoft Visual Studio (Administrator)

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.

2016-04-30 14_29_24-http___localhost_52678_WebForm1.aspx - Internet Explorer

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(&quot;CustomerID&quot;);
                dt.Columns.Add(&quot;CompanyName&quot;);
                dt.Columns.Add(&quot;ContactName&quot;);
                dt.Columns.Add(&quot;ContactTitle&quot;);
                dt.Columns.Add(&quot;Address&quot;);
                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.

2016-04-30 19_20_50-WebAPISample - Microsoft Visual Studio (Administrator)

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;
        }
    }
}

, , , , , , ,

Leave a comment

How to add a character counter to AjaxControlToolkit HTMLEditorExtender control

Introduction:

How to add a character counter to AjaxControlToolkit HTMLEditorExtender control

Description:

In this article we are discussing how to add a line counter to HTML Editor Extender control. Usually it’s very straight forward to add character counter to textbox or textarea by using its id.We generally use keypress event in textbox control to call character counter function.

However when we use at HTMLEditorExtender control we will face an issue as the HTMLEditorExtender control will render as a div with ContentEditable property set to true.  Div doesn’t not have a keypress event.

gxjynaa

As a solution you can use Jquery bind method to attach DOMSubTreeModified event to div and then call the character counter function in this event.

Code to attach character counter:

<script src="https://code.jquery.com/jquery-1.11.3.js"></script>
<script type="text/javascript">
      $(document).ready(function () {
         //attach change event to div using Jquery css class selector
         $('.ajax__html_editor_extender_texteditor').bind("DOMSubtreeModified", function () {
         //call function to calcualate count on change event of div
             textCounter();
          });
     });
     function textCounter() {
          //Get the text count
          var divlength = $('.ajax__html_editor_extender_texteditor').text().length;
          //check if count greater than 100
          if (divlength &gt; 50) {
          //Trim the text
          var trimmedtext = $('.ajax__html_editor_extender_texteditor').text().substring(0, 100);
         //assign it to editor
         $('.ajax__html_editor_extender_texteditor').html(trimmedtext);
        } else{
            //reduce the allowed no of characters in textbox
            $('#remLen').val(50 - divlength);
        }
     }
</script>
Demo

TxtCounter

, , , , ,

Leave a comment

How to display larger numbers on Excel export in C#

Introduction:

In this article I am going to explain a solution to the problem with displaying large numbers (more than 15 numbers) in excel while exporting data in C#.

Issue Description:

By default, excel normally display larger numbers like 123456E+17 when the number overcomes Excel’s precision limit for numbers which is 8 digits. When we export the data from Gridivew to excel the same problem exists.

Excel

Drawback of existing Solution:

One solution to resolve this is to make the column type of excel to text instead of numbers. To this we need to add a single quote (‘) to the numbers which we are exporting. This will resolve the issue of displaying the complete number, however this solution has a drawback as it will display single quote along with the number like ‘123456789011.

Solution to the Problem

To show the number without using single quote we can use a Third Party Excel library called EPPlus. EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx). This library offers several features and methods to help the user to generate Excel sheet. You can read the complete details from the link I shared above.

Here in this fix we will use the method LoadFromDataTable to load the data from DataTable to Excel. The same case applies when you Export data from gridview and other controls.

Code

private void ExcelExport(DataTable tbl)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");
                //Load the datatable into the sheet, starting from cell A1.
                ws.Cells["A1"].LoadFromDataTable(tbl, true);
                //Write it back to the client
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=file.xlsx");
                Response.BinaryWrite(pck.GetAsByteArray());
            }
        }

Before using above code add the below references in your page

using OfficeOpenXml;
using OfficeOpenXml.Style;

Sample usage to above method

DataTable dt = new DataTable();
            dt.Columns.Add("PhoneNo");
            dt.Columns.Add("Name");
            DataRow Sample = dt.NewRow();
            Sample["PhoneNo"] = "12345678911121314";
            Sample["Name"] = "SampleName";
            dt.Rows.Add(Sample);
            ExcelExport(dt);

With above code the exported excel file will look like below

Excel2

You can use Nugget to add the EPPlus in your solution.

 

, , , , , , ,

1 Comment

How to read JSON with object name using Json.Net in C#

Introduction:
In this article I am going to explain how to read the values from Json using Json.Net library when it is having Object Name.

Description:

In this article we are using Json.Net library to parse and read values in Json. We will be using JSON serializer methods available in this dictionary.

If you don’t have Json Framework in your machine you can get it from Nugget for Json.

Sample JSon file which we use in this article

{"employees":[
{"firstName":"Test1FN", "lastName":"Test1LN"},
{"firstName":"Test2FN", "lastName":"Test2LN"}
]}

Implementation:

After adding library to your project ensure that you have added the namespace reference in your page

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

Next add a class to hold the values, note that I have used JsonProperty to map the JSON property to a .NET member or constructor parameter. This is very important as the object name format is not following key: value combination so if you don’t set this then while reading you will get exception.

[JsonObject(MemberSerialization.OptIn)]
public class User
{
   [JsonProperty("firstName", Required = Required.Always)]
   public string FirstName { get; set; }

   [JsonProperty("lastName", Required = Required.Always)]
   public string LastName { get; set; }
}

Now you can use the below code to read the values

//Load your Json string here
String jsonString = "{\"user\":[{\"firstName\": \"Test1FN\", \"lastName\": \"Doe\"},{\"firstName\": \"Test2FN\", \"lastName\": \"Test2LN\"}]}";
JToken root = JObject.Parse(jsonString);
JToken user = root["user"];
//Deserialize the Json to list
var deserializedUser = JsonConvert.DeserializeObject<List<User>>(user.ToString());

//Loop through values 
foreach (User p in deserializedUser)
{
//Your code to process the values
}

, , , ,

Leave a comment

How to Populate JSON results in Html table

Introduction:

In this article I am going to explain how to populate JSON data to table using Javascript.

Description:

Here we are going to use a Bootstrap Table plugin to load the data from Json to table. This is an easy option to load the data because parsing of Json is handled internally.

Prerequisites:

You need to add the necessary JQuery and bootstrap files to your page prior to use this plugin. In this example I have used the online JQuery and Bootstrap CDN files available. If you don’t want to use the online available cdn file then you can manually add the jquery and Bootstrap files to your solution.

Implementation:

First we will design the table according to the column in Json. Main point to note is to set the data-toggle attribute properly.

 <table id="table">
            <thead>
                <tr>
                    <th data-field="id">ID</th>
                    <th data-field="Name">Name</th>
                    <th data-field="Address">Address</th>
                </tr>
            </thead>
        </table>

Now you can use the below code to assign the  Json data to a table.

  $('#table').bootstrapTable({
                //Assigning data to table
                data: jsondata
            });

Complete Code

<html lang="en">
<head>
    <meta charset="UTF-8">
    <script src="https://code.jquery.com/jquery.min.js"></script>
    <script src="https://netdna.bootstrapcdn.com/twitter-bootstrap/2.3.2/js/bootstrap.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-table/1.10.0/bootstrap-table.min.js"></script>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-table/1.10.0/bootstrap-table.min.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript">
        $(document).ready(function () {
            //Sample Json data
            var jsondata = [
                {
                    "id": "1",
                    "Name": "Name1",
                    "Address": "Address1"
                },
                {
                    "id": "2",
                    "Name": "Name2",
                    "Address": "Address2"
                },
                {
                    "id": "3",
                    "Name": "Name3",
                    "Address": "Address3"
                },
                {
                    "id": "4",
                    "Name": "Name4",
                    "Address": "Address4"
                },
                {
                    "id": "5",
                    "Name": "Name5",
                    "Address": "Address5"
                }];

            $('#table').bootstrapTable({
                //Assigning data to table
                data: jsondata
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <table id="table">
            <thead>
                <tr>
                    <th data-field="id">ID</th>
                    <th data-field="Name">Name</th>
                    <th data-field="Address">Address</th>
                </tr>
            </thead>
        </table>
    </form>
</body>
</html>

, , , , ,

2 Comments