Coding Nuances

Get Registry key value for 32 and 64 bit machines

2013-11-25T19:06:00.001+05:30

Here is the snippet that pulls a LOCALMACHINE\SOFTWARE

Binding html select using knockoutjs and asp.net mvc

2013-11-25T18:42:00.001+05:30

Suppose we have a ViewModel like this

The markup to populate HTML Select should be

Server side paging, filtering and sorting using datatables

2013-11-05T20:11:00.000+05:30

Nothing better than writing some code to explain a feature. This post shows how to do server-side paging in a table using datatables.net
The idea is to display a table with a large amount of data (68k rows). As it's not a good practice to load all at once, it' better to implement server-side paging.

First download datatables.net from here and reference them in your html file.

In our sample we are using a table Tasks with 3 fields, ie, TaskID, TaskName, Complete

Lets create the HTML markup first

Now lets wire up datatable to the table

Here as you can see TaskID and TaskName are sortable and searchable.
Now, the model that is passed from datatables to server-side

Finally, the controller method

That's all to it. Happy coding!

Utility function to make an array observable in knockoutjs

2013-11-05T19:07:00.000+05:30

Suppose we have an array

var employees = [
    { EmployeeID: 1, EmployeeName: "Naveen" },
    { EmployeeID: 2, EmployeeName: "Shebin" }
];

We can make it an observable array by calling it like this

var observableEmployees = ko.observableArray( employees );

But as per knockoutjs documentation,

Simply putting an object into an observableArray doesn’t make all of that object’s properties themselves observable. Of course, you can make those properties observable if you wish, but that’s an independent choice. An observableArray just tracks which objects it holds, and notifies listeners when objects are added or removed.

This means that any change in array will be remembered. ie, adding and removing elements in an array is observed. But changes to properties are not remembered. That is if employee[0].EmployeeName is changed to Noah, it wont be remembered. To do that we have to make each property ko.observable. Here is a small utility function to do so.

function MakeArrayKoObservableObjectArray(arr) {
    var observableArr = [];
    for (var i = 0; i < arr.length; i++) {
        var observableObj = {}, obj = arr[i];
        for (var prop in obj) {
            if (obj.hasOwnProperty(prop)) {
                observableObj[prop] = ko.observable(obj[prop]);
            }
        }
        observableArr.push(observableObj);
    }
    return observableArr;
}

Now we can call it like this.

employees = MakeArrayKoObservableObjectArray( employees  );
var observableEmployees = ko.observableArray( employees );

Happy Coding!

Remote validation using jQuery Validate

2013-11-04T06:08:00.003+05:30

This is how jQuery validate is done in ASP.NET MVC

Markup

<form id="manager-form" action="">
    <p>
        <label style="display:inline-block; width: 200px;">Manager Name</label>
        <input type="text" id="Manager" name="Manager" />
    </p>
    <p>
        <input type="submit" id="saveManager" class="btn btn-primary" value="Save Sales Person" />
    </p>
</form>

JavaScript

    function SetValidationRules(){
        $("#manager-form").validate({
            onkeyup: false,
            rules: {
                Manager: {
                    required: true,
                    minlength: 3,
                    remote: {
                        url: '/Manager/IsManagerNameTaken',
                        type: 'POST',
                        dataType: 'json',
                        data: {
                            managerName: function () {
                                return $('#Manager').val();
                            }
                        }
                    }
                }
            },
            messages: {
                Manager: {
                    required: "Manager name is required.",
                    minlength: "Manager name should be 3 atleast characters."
                }
            }
        });
    }

Controller

    public ActionResult IsManagerNameTaken(string managerName)
    {
        var result = ManagerRepository.IsManagerNameTaken(managerName) ?
            "Manager name is already taken. Try another!" : "";
        return Json(result);
    }

Thats all to it. Happy coding!

Overriding jQuery ajaxStart and ajaxStop locally

2013-11-02T04:32:00.000+05:30

While performing ajax calls, I use to provide provide visual indicators for better user experience.
My preferred method is showing a modal at jQuery.ajaxStart which gets hidden at jQuery.ajaxStop

The code goes like this

    $(document).on("ajaxStart", function () {
        $("#jQueryAjaxmodal").modal("show");
    }).on("ajaxStop", function () {
        $("#jQueryAjaxmodal").modal("hide");
    });

But this causes problem when one uses something like jQuery.autocomplete as ajaxStart fires on every keyup. To override this for a particular page, do this

1. Namespace the ajaxStart and ajaxStop like this

    $(document).on("ajaxStart.myblock", function () {
        $("#jQueryAjaxmodal").modal("show");
    }).on("ajaxStop.myblock", function () {
        $("#jQueryAjaxmodal").modal("hide");
    });

2. Now on the DOM Ready of the page on which you wanna override this behaviour, unbind it

    $(document).on("ready", function () {
        $(document).off(".myblock");
    });

Courtesy: jQuery should I use multiple ajaxStart/ajaxStop handling

Validating date in JavaScript

2013-04-03T16:58:00.002+05:30

People make me want to cry when they validate date using regular expression.

Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.

IMHO, its much simpler to use Date.Parse. For example,

var myDateString = '01-Aug-12';
var isValidDate = !isNaN( Date.parse( myDateString ));

Happy Coding!

Steps to Register an application for web access with Google

2012-06-06T19:57:00.004+05:30

Steps

1. Go to https://code.google.com/apis/console/
2. Now click on "Create Project..."
3. Click on "API Access"on the left menu.
4. Now "Create an OAuth 2.0 client ID..." on the center
5. Enter a Product Name and Product Logo(optional) and click Next
6. Now select the application type as "Web Application"
7. Enter the site or host name where you want this application deployed.
( In this demo I am setting it as http://localhost:4515/GoogleOAuth/oauth2callback.aspx)
8. Click to "Create Client ID" and you will be redirected to a page with
a. Client ID
b. Client Secret
C. Email
d. Redirect URLs
e. JavaScript Origins

Now you Google Application of type Web Application has been successfully registered.
Please store all the information securely so that you can use it at a later stage.

Happy Coding :)

Tutorial - Post data on a Facebook Company Page as Admin

2011-04-15T19:15:00.010+05:30

Its been a while I have blogged about something.
Have been working on the Facebook API and one of the requirement is to post data into a Facebook company page from one's website.

Here is a step by step procedure on posting data to a company page(on which you have admin rights) using the Facebook API.
Feel free to skip the first two steps if you know how to set up a Facebook Application and to create pages in Facebook.

Step 1 - Creating a Facebook Application

Sign in to your Facebook Account and key in the following URL to your address bar

http://www.facebook.com/developers/createapp.php

Set Application Name as "My Demo Application"

Now I am adding the following data also to the Website tab of the core settings
(Lets come to that later)

Set Site URL as http://localhost/FacebookPost/

After entering, click on the Save Changes Button.
Now you will be re directed to a page where your App Settings are displayed.
Copy the values App ID, App Key and App Secret and store them securely.

Step 2 - Creating a Facebook Page

Now, key in the following URL to your address bar

http://www.facebook.com/pages/create.php

Now select the Page Type. Here is what I selected.

Type : Company, Organisation (Click on the type you want to create.
Category : Computers/Technology
Comapany Name: MyCompany

Now agree to the Facebook Pages Terms and click Get Started.
Your page is created by Facebook and you will be landed on a URL like that
http://www.facebook.com/pages//?created
Please copy the PageID and store it securely.

Step 3 - Create a website locally in your system

Open up your Visual Studio and Create an empty website.
Create a page "Default.aspx" and run the website and allow the web.config to be created.

(I have mapped my site to my IIS with the application name FacebookPost.
Please note that it is being done to match the Site URL I have set at the Facebook Application Page)

Step 4 - Pick a Facebook C# SDK to communicate with the Facebook Graph API.

My research came up with two options
1. Use the Facebook's own lesser used SDK found at GitHub ( link here )

2. Use the more widely used third party C# SDK hosted at Codeplex ( link here )

I preferred Facebook's own SDK. Actually, you can also write your own API if you want. Its set of simple WebRequest/WebResponse. So I went to GitHub where the SDK was hosted and downloaded the folder named facebook and changed it to Facebook ( :) Pascal Casing) and pasted it inside my App_Code

Step 5 - Modify your AppSettings at web.config

Remember those AppID, AppKey, AppSecret, PageID I asked you to keep securely?
Lets now store them at AppSettings of web.config.
(Ideally you would be much better of encrypting them, but details on encrypting is beyond the scope of this tutorial.)

<appSettings>
    <add key="AppID" value="Your App ID"/>
    <add key="AppKey" value="Your App Key"/>
    <add key="AppSecret" value="Your App Secret"/>
    <add key="PageID" value="Your Page ID"/>
  </appSettings>

Step 6 - Create a form for entering the data

Open up Default.aspx in Visual Studio and change Source like this.

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Codovations Demo - Posting to Facebook Company Page</title>
    <style type="text/css">
        .title {
            display:inline-block;
            vertical-align:top;
            width:100px;   
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <label class="title">Message</label><asp:TextBox ID="MessageText" runat="server" TextMode="MultiLine" Width="200px"></asp:TextBox><br />
    <label class="title">Name</label><asp:TextBox ID="NameText" runat="server" Width="200px"></asp:TextBox><br />
    <label class="title">Description</label><asp:TextBox ID="DescriptionText" runat="server" TextMode="MultiLine" Width="200px"></asp:TextBox><br />
    <label class="title">Picture(URL)</label><asp:TextBox ID="PictureText" runat="server" Width="200px"></asp:TextBox><br />
    <label class="title">Caption</label><asp:TextBox ID="CaptionText" runat="server" Width="200px"></asp:TextBox><br />
    <label class="title">Link</label><asp:TextBox ID="LinkText" runat="server" Width="200px"></asp:TextBox><br />
    <label class="title"></label>
        <asp:Button ID="PostToCompanyButton" runat="server" 
                Text="Post to Company Page" onclick="PostToCompanyButton_Click"/>
    </div>
    </form>
</body>
</html>

Step 7 - Posting data on the Company Page.

This is the trickiest part in this project. Facebook provides no straight forward way to post data to a company page. Here is how we achieve that.

First we will request oAuth from Facebook and on successful authorisation we get an access token.
Using the token we fetch "me/accounts" and will check in that account array, an account that matches the stored PageID. If found, we fetch the associated accesstoken for that page. When we get that we post the content to "me/feeds" using that accesstoken like this.

if (Request.Params["code"] != null)
{
    Status.Text = "";
    string accessToken = GetAccessToken(Request.Params["code"]);
    FacebookAPI api = new FacebookAPI(accessToken);

    JSONObject me = api.Get("me/accounts");
    Dictionary dictionary = me.Dictionary;
    JSONObject myPage = me.Dictionary["data"].Array
        .SingleOrDefault(d => d.Dictionary["id"].String.Equals(ConfigurationManager.AppSettings["PageID"].ToString()));
    if (myPage != null)
    {
        Dictionary parameters = Session["DataForFacebook"] as Dictionary;
        Session.Remove("DataForFacebook");
        string newToken = myPage.Dictionary["access_token"].String;
        var app = new FacebookAPI(newToken);
        var response = app.Post("me/feed", parameters);
        if (response != null && response.Dictionary[""] != null)
        {
            Status.Text = "Data posted successfully to company page.";
            ClearData();
        }
        else
        {
            Status.Text = "An error occured while posting.";
        }
    }
}

Two helper methods used are

private string GetAccessToken(string accessToken)
    {       
        Dictionary args = GetOauthTokens(accessToken);
        return args["access_token"];
    }

    private Dictionary GetOauthTokens(string accessToken)
    {
        Dictionary tokens = new Dictionary();
        try
        {
            string url = String.Format("{0}client_id={1}&redirect_uri={2}&client_secret={3}&code={4}&scope={5}",
                            "https://graph.facebook.com/oauth/access_token?",
                            ConfigurationManager.AppSettings["AppID"].ToString(),
                            "http://localhost/FacebookPost/Default.aspx",
                            ConfigurationManager.AppSettings["AppSecret"].ToString(),
                            accessToken,
                            "publish_stream,offline_access,manage_pages");

            HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest;
            using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
            {
                StreamReader reader = new StreamReader(response.GetResponseStream());
                string retVal = reader.ReadToEnd();

                foreach (string token in retVal.Split('&'))
                {
                    tokens.Add(token.Substring(0, token.IndexOf("=")),
                        token.Substring(token.IndexOf("=") + 1, token.Length - token.IndexOf("=") - 1));
                }
            }
        }
        catch (Exception err)
        {

        }
        return tokens;
    }

Thats all to it. Happy coding.

Generating PDF from DataTable using iTextSharp

2011-01-18T16:55:00.000+05:30

iTextSharp is a wonderful open-source tool for generating PDF in C#.

Here is a simple generic helper class for Exporting data from a DataTable to a PDF file.

using System;
using System.Web;
using System.Data;

using iTextSharp.text;
using iTextSharp.text.pdf;

namespace yetanothercoder
{
    /// 
    /// Summary description for CreatePdf
    /// 
    public class PDFExporter
    {

        private readonly DataTable dataTable;
        private readonly string fileName;
        private readonly bool timeStamp;

        public PDFExporter(DataTable dataTable, string fileName, bool timeStamp)
        {
            this.dataTable = dataTable;
            this.fileName = timeStamp ? String.Format("{0}-{1}", fileName, GetTimeStamp(DateTime.Now)) : fileName;
            this.timeStamp = timeStamp;
        }

        public void ExportPDF()
        {
            HttpResponse Response = HttpContext.Current.Response;
            Response.Clear();
            Response.ContentType = "application/octet-stream";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".pdf");

            // step 1: creation of a document-object
            Document document = new Document(PageSize.A4, 10, 10, 90, 10);

            // step 2: we create a writer that listens to the document
            PdfWriter writer = PdfWriter.GetInstance(document, Response.OutputStream);

            //set some header stuff
            document.AddTitle(fileName);
            document.AddSubject(String.Format("Table of {0}", fileName));
            document.AddCreator("www.yetanothercoder.com");
            document.AddAuthor("naveenj");

            // step 3: we open the document
            document.Open();

            // step 4: we add content to the document
            CreatePages(document);

            // step 5: we close the document
            document.Close();
        }

        private void CreatePages(Document document)
        {
            document.NewPage();
            document.Add(FormatPageHeaderPhrase(dataTable.TableName));
            PdfPTable pdfTable = new PdfPTable(dataTable.Columns.Count);
            pdfTable.DefaultCell.Padding = 3;
            pdfTable.WidthPercentage = 100; // percentage
            pdfTable.DefaultCell.BorderWidth = 2;
            pdfTable.DefaultCell.HorizontalAlignment = Element.ALIGN_CENTER;

            foreach (DataColumn column in dataTable.Columns)
            {
                pdfTable.AddCell(FormatHeaderPhrase(column.ColumnName));
            }
            pdfTable.HeaderRows = 1;  // this is the end of the table header
            pdfTable.DefaultCell.BorderWidth = 1;

            foreach (DataRow row in dataTable.Rows)
            {
                foreach (object cell in row.ItemArray)
                {
                    //assume toString produces valid output
                    pdfTable.AddCell(FormatPhrase(cell.ToString()));
                }
            }

            document.Add(pdfTable);
        }

        private static Phrase FormatPageHeaderPhrase(string value)
        {
            return new Phrase(value, FontFactory.GetFont(FontFactory.TIMES, 10, Font.BOLD, new BaseColor(255, 0, 0)));
        }

        private static Phrase FormatHeaderPhrase(string value)
        {
            return new Phrase(value, FontFactory.GetFont(FontFactory.TIMES, 8, Font.UNDERLINE, new BaseColor(0, 0, 255)));
        }

        private Phrase FormatPhrase(string value)
        {
            return new Phrase(value, FontFactory.GetFont(FontFactory.TIMES, 8));
        }

        private string GetTimeStamp(DateTime value)
        {
            return value.ToString("yyyyMMddHHmmssffff");
        }
    }
}

Now if you have an Export to Excel Button, you can generate pdf easily like this

protected void ExportToPDFLink_Click(object sender, EventArgs e)
{
    PDFExporter pdf = new PDFExporter(GetCostumers(), "customer", true);
    pdf.ExportPDF();
}

SQLite in ASP.NET Web Forms

2011-01-18T14:56:00.003+05:30

What is SQLite?

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite is a must have tool for manipulating database for small to mid size projects, there by cutting the cost of maintaining an extra database server. Its very fast and has less percentage of crashing also.
Next, download an SQLite Manager from which we can create and manipulate a sample database and its objects. For that I used the SQLite Manager add on for Firefox. Feel free to use any other tools.
Now we are all set to use the SQLite database in our ASP.NET application.

Steps to follow

1. Create a Sample Database called Northwind.sqlite using the firefix addon.
2. Add a table named Customers to it with some fields and some dummy data to it.
3. Create an empty website in .NET 3.5 using Visual Studio
4. Create an App_Data folder in it and place the Northwind.sqlite to it
5. Add to the empty website as reference, the dll

using System.Data.SQLite

6. Create a default page in your application and place a gridview in that

<asp:GridView ID="CustomersGrid" runat="server">
</asp:GridView>

7. At code behind, add reference to System.Data.SQLite;
8. Write the code to get data from Customers Table here.

private void BindCustomerGrid()
{
    string commandText = "SELECT * FROM Customers";
    string path_to_db = System.IO.Path.Combine(Server.MapPath("."), "App_Data", "Northwind.sqlite3");
    string connectionString = String.Format("Data Source={0};Version=3;", path_to_db);
    DataTable dataTable = new DataTable();
    using (SQLiteConnection connection = new SQLiteConnection(connectionString))
    {
        using (SQLiteCommand cmd = new SQLiteCommand(commandText, connection))
        {
            cmd.CommandType = CommandType.Text;
            using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd))
            {
                adapter.Fill(dataTable);
            }
        }
    }
    CustomersGrid.DataSource = dataTable;
    CustomersGrid.DataBind();
}

Thats all to it. Happy Coding!

Maintaining CheckBox State of multiple GridViews

2010-12-07T18:55:00.004+05:30

Almost two years back, I did a post on Maintaining State of Checkbox while Paging in Gridview

This post is an extension to it. this is the code to maintain CheckBox states of multiple gridviews in a page.

The logic is simple. We are storing the primary keys of the checkboxes that are checked into a List<T> where T = data type of the primary key. This is done at PageIndexChanging before we change the page index and re-bind the GridView. And at RowDataBound we are checking whether the DataKeyName(primary key) of each row is in the List. If present , we mark the CheckBox as checked.

At ASPX

<asp:GridView ID="gvProducts" runat="server" 
            AllowPaging="True" 
            AutoGenerateColumns="False"
            DataKeyNames="ProductID"
            OnPageIndexChanging="gvProducts_PageIndexChanging" 
            OnRowDataBound="gvProducts_RowDataBound">
    <Columns>
        <asp:TemplateField HeaderText="Select">
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False"
        ReadOnly="True" SortExpression="ProductID" />
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
    </Columns>
</asp:GridView>
<asp:GridView ID="gvCustomers" runat="server" 
            AllowPaging="True" 
            AutoGenerateColumns="False"
            DataKeyNames="CustomerID"
            OnPageIndexChanging="gvCustomers_PageIndexChanging" 
            OnRowDataBound="gvCustomers_RowDataBound">
    <Columns>
        <asp:TemplateField HeaderText="Select">
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="CustomerID" HeaderText="ID" InsertVisible="False" />
        <asp:BoundField DataField="CompanyName" HeaderText="CompanyName"  />
    </Columns>
</asp:GridView>

At ASPX.CS

private List<int> ProductIDs
{
    get
    {
        if (this.ViewState["ProductIDs"] == null)
        {
            this.ViewState["ProductIDs"] = new List<int>();
        }
        return this.ViewState["ProductIDs"] as List<int>;
    }
}

private List<string> CustomerIDs
{
    get
    {
        if (this.ViewState["CustomerIDs"] == null)
        {
            this.ViewState["CustomerIDs"] = new List<string>();
        }
        return this.ViewState["CustomerIDs"] as List<string>;
    }
}

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGrid(gvProducts, "Products");
        BindGrid(gvCustomers, "Customers");
    }
}

protected void gvProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    foreach (GridViewRow gvr in gvProducts.Rows)
    {
        CheckBox chkSelect = gvr.FindControl("chkSelect") as CheckBox;
        if (chkSelect != null)
        {
            int productID = Convert.ToInt32(gvProducts.DataKeys[gvr.RowIndex]["ProductID"]);
            if (chkSelect.Checked && !this.ProductIDs.Contains(productID))
            {
                this.ProductIDs.Add(productID);
            }
            else if (!chkSelect.Checked && this.ProductIDs.Contains(productID))
            {
                this.ProductIDs.Remove(productID);
            }
        }
    }
    gvProducts.PageIndex = e.NewPageIndex;
    BindGrid(gvProducts, "Products");
}

protected void gvProducts_RowDataBound(object sender, GridViewRowEventArgs e)
{
    GridViewRow gvr = e.Row;
    if (gvr.RowType == DataControlRowType.DataRow)
    {
        CheckBox chkSelect = gvr.FindControl("chkSelect") as CheckBox;
        if (chkSelect != null)
        {
            int productID = Convert.ToInt32(gvProducts.DataKeys[gvr.RowIndex]["ProductID"]);
            chkSelect.Checked = this.ProductIDs.Contains(productID);
        }
    }
}

protected void gvCustomers_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    foreach (GridViewRow gvr in gvCustomers.Rows)
    {
        CheckBox chkSelect = gvr.FindControl("chkSelect") as CheckBox;
        if (chkSelect != null)
        {
            string customerID = gvCustomers.DataKeys[gvr.RowIndex]["CustomerID"].ToString();
            if (chkSelect.Checked && !this.CustomerIDs.Contains(customerID))
            {
                this.CustomerIDs.Add(customerID);
            }
            else if (!chkSelect.Checked && this.CustomerIDs.Contains(customerID))
            {
                this.CustomerIDs.Remove(customerID);
            }
        }
    }
    gvCustomers.PageIndex = e.NewPageIndex;
    BindGrid(gvCustomers, "Customers");
}

protected void gvCustomers_RowDataBound(object sender, GridViewRowEventArgs e)
{
    GridViewRow gvr = e.Row;
    if (gvr.RowType == DataControlRowType.DataRow)
    {
        CheckBox chkSelect = gvr.FindControl("chkSelect") as CheckBox;
        if (chkSelect != null)
        {
            string customerID = gvCustomers.DataKeys[gvr.RowIndex]["CustomerID"].ToString();
            chkSelect.Checked = this.CustomerIDs.Contains(customerID);
        }
    }
}

private DataTable PopulateData(string tableName)
{
    DataTable dt = new DataTable();
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
    {
        string sql = String.Format("SELECT * FROM {0}", tableName);
        using (SqlDataAdapter adap = new SqlDataAdapter(sql, conn))
        {
            adap.Fill(dt);
        }
    }
    return dt;
}

private void BindGrid(GridView gvTemp, string tableName)
{
    gvTemp.DataSource = PopulateData(tableName);
    gvTemp.DataBind();
}

Happy Coding!

P.S: Database in use is Northwind Sample Database.

Insert Arabic Words into Database

2010-12-07T11:03:00.003+05:30

Often at forums.asp.net we come across the problem that occurs while inserting foreign language to the database. Supopose I wanted to insert 'اتصالات' to a table named 'table_test'. When I look at the entry at all I see is ?????.

Why did this happen? Its because the field we enter the data needs to be having COLLATE Arabic.

To understand that we must first understand what collation is.

What is collation?

Collation refers to a set of rules that determine how data is sorted and compared.
Character data is sorted using rules that define the correct character sequence,
with options for specifying
1) case-sensitivity,
2) accent marks,
3) kana character types and
4)character width.

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive. A computer treats A and a differently because it uses ASCII code to differentiate the input. The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive. A computer treats a and á differently because it uses ASCII code for differentiating the input. The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Now we have identified the problem. Lets try to solve it using an example.
In this example I am saving a textbox value to database on the click of a button.

Suppose I have a table named table_test with two columns
1. [myid] int auto
2. [mytext] nvarchar(50)

I want to enter the arabic word to the field mytext.

Step1
At Database provide collate to the field on which we want to insert the arabic word

ALTER TABLE test_table
ALTER COLUMN mytext VARCHAR(50) COLLATE Arabic_CI_AI

Step 2

protected void myButton_Click(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["forums_ConnectionString"].ConnectionString))
    {
        conn.Open();
        string sql = String.Format("INSERT INTO test_table (mytext) VALUES (N'{0}')",
            mytext.Text.Trim());
        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
        }
    }
}

Please note that I have put N' before the value.
Read the article Why do some SQL strings have an 'N' prefix? if you want to know its significance.

Prevent site from getting loaded on iframe

2010-11-16T10:16:00.004+05:30

What if somebody loads your obfuscated fancy javascript or AJAX methods to drive traffic to their site utilising your bandwidth? Here is a simple solution in javascript.

<script type="text/javascript">
//<![CDATA[
    if (window.top !== window.self) {
        document.write = "";
        window.top.location = window.self.location;
        setTimeout(function () {
            document.body.innerHTML = '';
        }, 1);
        window.self.onload = function (evt) {
            document.body.innerHTML = '';
        }; 
    }
//]]>
</script>

Paste that javascript snippet inside your head tag of your html.
Twitter(Dustin Diaz) uses that at the Twitter Profile Widget Page. :(
I found it the hard way (lol). Way to go Dustin!

HTML Encode Decode in Javascript/jQuery

2010-11-15T11:36:00.000+05:30

Last day, I had a resolute and unyielding need to HTML Decode a string.
I saw many solutions but was not satisfied.
Then I found the StackOverFlow reference which is the most optimal solution I have seen till date. Just pasting it here for easy reference.

function htmlEncode(value){ 
  return $('<div/>').text(value).html(); 
} 

function htmlDecode(value){ 
  return $('<div/>').html(value).text(); 
}

Did I say jQuery? I sure did say optimal :P
Happy Coding!

Dependency: jQuery

Different ways to create a DataTable and set Schema

2010-11-10T15:32:00.003+05:30

Was hanging out StackOverflow and I saw Marc Gravell initializing the DataTable in a simpler and cleaner way. Here goes

DataTable dataTable = new DataTable
{
    Columns = {
        {"ID", typeof(int)},
        {"Name", typeof(string)},
        "Location"
    },
    TableName="NaveenTest"
};
dataTable.Rows.Add(1, "Naveen", "Coder");

Please note that if you don't specify the type it will automatically be converted to string. See how "Location" is initialised

And my old method

DataTable dtOld = new DataTable("NaveenTest");
dtOld.Columns.Add("MyID", typeof(int));
dtOld.Columns.Add("Name");
dtOld.Columns.Add("Location");
DataRow drOld = new DataRow();
drOld[0] = 1;
drOld[1] = "Naveen";
drOld[2] = "Coder";
dtOld.Rows.Add(drOld);

Aaarghhh...

A lot cleaner!
What do you say?

Gray Scale images in all Browsers

2009-12-22T14:25:00.004+05:30

Its been a while since I blogged on anything.
Today I came across a post at forums.asp.net: change color image to gray scale

GrayScale - Converts the colors of the object to 256 shades of gray.

An example will be

Since GrayScale filter is a filter present in the Internet Explorer,
we can achieve this result in IE simply by using the css property

.greyscale_filter{ 
 filter: gray;
}

.greyscale_filter{
 -ms-filter: "progid:DXImageTransform.Microsoft.BasicImage(grayscale=1)";
 filter: progid:DXImageTransform.Microsoft.BasicImage(grayscale=1);
}

Here the second method is more advanced and must have Internet Explorer 5.5 or later to work properly.

There are number of filters in IE apart from GrayScale. Here are some lists

1. Static filters by MSDN
2. CSS Visual Filters
3. IE Multimedia filters reference

This is the case with Internet Explorer.
Now the big question.

Why does not the GrayScale filter work with browsers like Chrome, Firefox and Safari?
Simple. The filter is specific to IE only.

While I was searching through the internet for a good solution, I found this at SO
There I discovered a wonderful javascript coder named James Padolsey who is just 19!

Copied his GrayScale.js which according to James Padolsey is

Grayscale.js is an experimental attempt to emulate Microsoft's
proprietary 'grayscale' filter (available in most IE versions).

And it worked like a charm.

So here are the steps involved in achieving the hover effect

1. Grab the GrayScale.js from James Padolsey's site. (The link is here)

2. Add refernce to jQuery. Please note that jQuery is not required for grayscale.js - it's only used for this demo
Its better to refer jQuery like this (Read Dave Ward ka Encosia on this topic)

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>

than

<script type="text/javascript" src="/js/jQuery.min.js"></script>

3. Add a small javascript snippet like this

$(document).ready(function(){
     $('img.greyonhover').hover(function(){
    grayscale(this);
   }, function(){
    grayscale.reset(this);
   })
  });

4. Assign the class="greyonhover" for the image you want to implement grayscale effect onhover

<img class="greyonhover" src="images/naveenj-thumb.jpg" />

This is all you have to do to achieve greying effect on hover. Download Source Code(9k)

Selectable GridViewRow using Javascript

2009-10-21T12:49:00.005+05:30

Hope you have read my post about Selectable GridViewRow
If you haven't done that please do that before you proceed further.
This is a sort of sequel to that post.

Here I am making a GridView Selectable using __doPostBack(...) function

Suppose you have a GridView populated from the Products table of the NorthWind Database.

So the ASPX looks like this

<asp:GridView ID="gvwProducts" runat="server"
AutoGenerateColumns="False" 
DataKeyNames="ProductID"
DataSourceID="sdsProducts"
OnRowDataBound="gvwProducts_RowDataBound">
<Columns>
<asp:TemplateField HeaderText="Contact Name">
<ItemTemplate>
<asp:Label ID="lblProductName" runat="server"
Text='<%#Eval("ProductName") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Stock">
<ItemTemplate>
<asp:Label ID="lblunitsInStock" runat="server"
Text='<%#Eval("UnitsInStock") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>

</Columns>
<SelectedRowStyle ForeColor="Green" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="sdsProducts" runat="server" 
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT ProductID, ProductName, UnitsInStock FROM Products"
SelectCommandType="Text">
</asp:SqlDataSource>

Now on RowDataBound lets set the click attribute for selectable row.
You can also use RowCreated for this.
Note that we are also giving some fancy color changes in onmouseover and onmouseout events.
Discard them if you dont want color changes.

protected void gvwProducts_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
//e.Row.Attributes["onmouseover"] = "this.style.color='DodgerBlue';this.style.cursor='hand';";
e.Row.Attributes["onmouseover"] = "javascript:return ChangeRowColor('m_over', this.style)";
//e.Row.Attributes["onmouseout"] = "this.style.color='Black';";
e.Row.Attributes["onmouseout"] = "javascript:return ChangeRowColor('m_out', this.style)";
e.Row.Attributes["onclick"] = "javascript:return __doPostBack('" + gvwProducts.ClientID.Replace('_','$') + "', 'Select$" + e.Row.RowIndex + "');";
e.Row.ToolTip = "Click on the row to select it";
}
}

Now register these postbacks for event validation at Page_Render to avoid

System.ArgumentException: Invalid postback or callback argument. Event validation is enabled using in configuration or <%@ Page EnableEventValidation="true" %> in a page. For security purposes, this feature verifies that arguments to postback or callback events originate from the server control that originally rendered them. If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method in order to register the postback or callback data for validation

protected override void Render(HtmlTextWriter writer)
{
// .NET will refuse to accept "unknown" postbacks for security reasons. 
// Because of this we have to register all possible callbacks
// This must be done in Render, hence the override
for (int i = 0; i < gvwProducts.Rows.Count; i++)
{
Page.ClientScript.RegisterForEventValidation(new System.Web.UI.PostBackOptions(this.gvwProducts, "Select$" + i.ToString()));
}
// Do the standard rendering stuff
base.Render(writer);
}

Now compare the RowDataBound event to first example and you note some changes in the way the row attributes for onmouseover and onmouseout are set. (Check the commented lines in the RowDataBound event) What we do here is to set a foreground color "Green" to the Selected Row and persists that color onmouseout and onmouseover of the selected row. Inorder to achieve that, place this javascript function on the page.

function ChangeRowColor(eventtype, rowStyle){
if(rowStyle.color.toLowerCase() != "green"){
if(eventtype == "m_over"){
rowStyle.color='DodgerBlue';
rowStyle.cursor='pointer';
}
else{
rowStyle.color='Black';
}
}
else{
rowStyle.cursor='default';
}
}

Now some developers may get the

__doPostBack(...) object required

To circumvent this error place these on the ASPX Page Two Hidden Fields

<input type ="hidden" name ="__EVENTTARGET" value ="" />
<input type ="hidden" name ="__EVENTARGUMENT" value ="" />

Javascript Function

//

Happy Coding!

Selectable GridViewRow

2009-10-19T21:12:00.009+05:30

I have always found using Select Button in GridView to select a row cumbersome.
It would be always better to click anywhere on the GridViewRow and Select the row.

Here's a snippet on how to do it.

On RowDataBound

protected void gvwProducts_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{

e.Row.Attributes["onmouseover"] = "this.style.color='DodgerBlue';this.style.cursor='hand';";
e.Row.Attributes["onmouseout"] = "this.style.color='Black';";
e.Row.Attributes["onclick"] = ClientScript.GetPostBackClientHyperlink(this.gvwProducts, "Select$" + e.Row.RowIndex);
e.Row.ToolTip = "Click on the row to select it";
}
}

Now if we run the code and click on the row we will get this error

System.ArgumentException: Invalid postback or callback argument.
Event validation is enabled using in configuration
or <%@ Page EnableEventValidation="true" %> in a page.
For security purposes, this feature verifies that arguments to postback or callback events originate
from the server control that originally rendered them.
If the data is valid and expected, use the ClientScriptManager.RegisterForEventValidation method
in order to register the postback or callback data for validation.

To avoid this we override the Page's Render Event.
Inside that event we register an event reference for validation on each GridViewRow

The overridden Page Render

protected override void Render(HtmlTextWriter writer)
{
// .NET will refuse to accept "unknown" postbacks for security reasons. 
// Because of this we have to register all possible callbacks
// This must be done in Render, hence the override
for (int i = 0; i < gvwProducts.Rows.Count; i++)
{
Page.ClientScript.RegisterForEventValidation(new System.Web.UI.PostBackOptions(gvwProducts, "Select$" + i.ToString()));
}
// Do the standard rendering stuff
base.Render(writer);
}

Here I am posting the ASPX markup of the GridView also The ASPX

<asp:GridView ID="gvwProducts" runat="server"
AutoGenerateColumns="False" 
DataKeyNames="ProductID"
DataSourceID="sdsProducts"
OnRowDataBound="gvwProducts_RowDataBound">
<Columns>
<asp:TemplateField HeaderText="Contact Name">
<ItemTemplate>
<asp:Label ID="lblProductName" runat="server"
Text='<%#Eval("ProductName") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Stock">
<ItemTemplate>
<asp:Label ID="lblunitsInStock" runat="server"
Text='<%#Eval("UnitsInStock") %>'>
</asp:Label>
</ItemTemplate>
</asp:TemplateField>

</Columns>
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="sdsProducts" runat="server" 
ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="SELECT ProductID, ProductName, UnitsInStock FROM Products"
SelectCommandType="Text">
</asp:SqlDataSource>

Just paste the last xml snippet on ASPX and first two code snippets on cs page. Change the connectionstring to the one you have and run the code to see the result. We are not finished yet From time to time, some developers will experience the "__doPostBack object expected" error in the page Postback. This occurs at the second time we try to fire the row click event The Reason

We add onclick feature for GridView in the RowDataBound event using ClientScript.GetPostBackClientHyperlink(...) But RowDataBound event gets fired only the first time(at !IsPostBack). This creates the __doPostBack() function when the page is first time loaded. Now from the second time onwards(at IsPostBack) RowDataBound doesn't get fired. So no ClientScript.GetPostBackClientHyperlink(...) is called and no __doPostBack() function is created

The solution. Just place this line under Page_Load event

protected void Page_Load(object sender, EventArgs e)
{
ClientScript.GetPostBackClientHyperlink(this, "");
}

Happy Coding! For Better understanding please read Selectable GridViewRow using JavaScript Points to Note: 1. I have given a SelectedRowStyle to show that the row is actually selected by giving it a different color. 2. The table used is Products from Microsoft's Sample DataBase Northwind

Sorting and Paging a GridView

2009-08-27T18:12:00.004+05:30

Sorting can be tricky. But maintaining the sort order on paging is trickier
Here is a sample code for that

DataBase used is Northwind.
Table used is Products.

The ASPX



<asp:GridView ID="GridView1" runat="server"
                AllowPaging="true"
                AllowSorting="true"
                AutoGenerateColumns="false"
                DataKeyNames="ProductID"
                PageSize="10"
                OnPageIndexChanging="GridView1_PageIndexChanging"
                OnSorting="GridView1_Sorting">
    <Columns>
        <asp:TemplateField HeaderText="ID" SortExpression="ProductID">
            <ItemTemplate>
                <asp:Label ID="lblProductID" runat="server"
                            Text='<%#Eval("ProductID") %>'>
                </asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Name" SortExpression="ProductName">
            <ItemTemplate>
                <asp:Label ID="lblProductName" runat="server"
                            Text='<%#Eval("ProductName") %>'>
                </asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Stock" SortExpression="UnitsInStock">
            <ItemTemplate>
                <asp:Label ID="lblUnitsInStock" runat="server"
                            Text='<%#Eval("UnitsInStock") %>'>
                </asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

The ASPX.CS



private const string ASCENDING = " ASC";
private const string DESCENDING = " DESC";
static private DataView dvProducts;

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        dvProducts = new DataView(GetProductsFromDataTable());
        BindGridView();
    }
}

protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
{
    string sortExpression = e.SortExpression;

    if (GridViewSortDirection == SortDirection.Ascending)
    {
        GridViewSortDirection = SortDirection.Descending;
        dvProducts.Sort = sortExpression + DESCENDING;
        BindGridView();
    }
    else
    {
        GridViewSortDirection = SortDirection.Ascending;
        dvProducts.Sort = sortExpression + ASCENDING;
        BindGridView();
    }   
}

protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    GridView1.PageIndex = e.NewPageIndex;
    BindGridView();
}

private void BindGridView()
{
    GridView1.DataSource = dvProducts;
    GridView1.DataBind();
}

public SortDirection GridViewSortDirection
{
    get
    {
        if (ViewState["sortDirection"] == null)
            ViewState["sortDirection"] = SortDirection.Ascending;

        return (SortDirection)ViewState["sortDirection"];
    }
    set { ViewState["sortDirection"] = value; }
}

private DataTable GetProductsFromDataTable()
{
    DataTable dtProductsTemp = new DataTable();
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
    {
        using (SqlDataAdapter adapProducts = new SqlDataAdapter("Select * from Products", conn))
        {
            adapProducts.Fill(dtProductsTemp);
        }
    }
    return dtProductsTemp;
}

Extra namespace used



using System.Data.SqlClient;

Paging in Repeater

2009-07-18T16:51:00.012+05:30

Heres a sample on how to implement Paging on a Repeater.

The Output

The ASPX


 <asp:Repeater ID="rptrProducts" runat="server">
        <HeaderTemplate>
            <table border="1">
                <tr style="background-color:Gray;">
                    <td>
                        Product Name
                    </td>
                    <td>
                        Quantity Per Unit
                    </td>
                    <td>
                        Units On Order
                    </td>
                    <td>
                        Discontinued
                    </td>
                </tr>
        </HeaderTemplate>
        <ItemTemplate>
            <tr>
                <td>
                    <asp:Label ID="lblProductName" runat="server" Text='<%#Eval("ProductName") %>'></asp:Label>
                </td>
                <td>
                    <asp:Label ID="lblQuantityPerUnit" runat="server" Text='<%#Eval("QuantityPerUnit") %>'></asp:Label>
                </td>
                <td>
                    <asp:Label ID="lblUnitsOnOrder" runat="server" Text='<%#Eval("UnitsOnOrder") %>'></asp:Label>
                </td>
                <td>
                    <asp:CheckBox ID="chkDiscontinued" runat="server" Checked='<%#Eval("Discontinued") %>'
                        Enabled="false" />
                </td>
            </tr>
        </ItemTemplate>
    </asp:Repeater>
    <tr align="right" style="background-color:Gray;">
        <td colspan="4">
            <asp:LinkButton ID="lnkFirst" runat="server" 
                            ForeColor="Black"
                            Text="First" onclick="lnkFirst_Click">
            </asp:LinkButton>&nbsp;
            <asp:LinkButton ID="lnkPrevious" runat="server" 
                            ForeColor="Black"
                            Text="Previous" 
                            OnClick="lnkPrevious_Click">
            </asp:LinkButton>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            Now Showing Page&nbsp;
            <asp:DropDownList ID="ddlpageNumbers" runat="server"
                                AutoPostBack="true" 
                                OnSelectedIndexChanged="ddlpageNumbers_SelectedIndexChanged">
            </asp:DropDownList>&nbsp;of&nbsp;
            <asp:Label ID="lblTotalPages" runat="server">
            </asp:Label>&nbsp;Pages.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            <asp:LinkButton ID="lnkNext" runat="server" 
                            ForeColor="Black"
                            Text="Next" onclick="lnkNext_Click">
            </asp:LinkButton>&nbsp;
            <asp:LinkButton ID="lnkLast" runat="server" 
                            ForeColor="Black"
                            Text="Last" onclick="lnkLast_Click">
            </asp:LinkButton>
        </td>
    </tr>
</table>

The ASPX.CS


public partial class Repeaters_Paging : System.Web.UI.Page
{
    #region "Local Variables and Declarations"

    private const bool blnAllowPaging = true;
    private const int iPageSize = 10;

    private static PagedDataSource pgdProducts = new PagedDataSource();

    #endregion
        
    #region "Page Load Event"

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindRepeater();
        }
    }

    #endregion

    #region "Footer LinkButtons"

    protected void lnkFirst_Click(object sender, EventArgs e)
    {
        ThisPageNumber = 0;
        BindRepeater();
    }

    protected void lnkPrevious_Click(object sender, EventArgs e)
    {
        --ThisPageNumber;
        BindRepeater();
    }

    protected void lnkNext_Click(object sender, EventArgs e)
    {

        ++ThisPageNumber;
        BindRepeater();
    }

    protected void lnkLast_Click(object sender, EventArgs e)
    {
        ThisPageNumber = pgdProducts.PageCount - 1;
        BindRepeater();
    }

    #endregion

    #region "Footer DropDownList"

    protected void ddlpageNumbers_SelectedIndexChanged(object sender, EventArgs e)
    {
        ThisPageNumber = ddlpageNumbers.SelectedIndex;
        BindRepeater();
    }

    #endregion

    #region "Custom Functions"

    private void BindRepeater()
    {
        pgdProducts.AllowPaging = blnAllowPaging;
        pgdProducts.PageSize = iPageSize;
        pgdProducts.DataSource = GetProductsDataView();

        pgdProducts.CurrentPageIndex = ThisPageNumber;
        lblTotalPages.Text = pgdProducts.PageCount.ToString();
        FillPagesDropDownList(pgdProducts.PageCount);

        lnkFirst.Enabled = !pgdProducts.IsFirstPage;
        lnkPrevious.Enabled = !pgdProducts.IsFirstPage;
        lnkNext.Enabled = !pgdProducts.IsLastPage;
        lnkLast.Enabled = !pgdProducts.IsLastPage;
        ddlpageNumbers.SelectedIndex = pgdProducts.CurrentPageIndex; 

        rptrProducts.DataSource = pgdProducts;
        rptrProducts.DataBind();
    }

    private DataView GetProductsDataView()
    {
        DataTable dtProductsTemp = new DataTable();
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
        {
            using (SqlDataAdapter adapProducts = new SqlDataAdapter("Select * from Products", conn))
            {
                adapProducts.Fill(dtProductsTemp);
            }
        }
        return dtProductsTemp.DefaultView;

    }

    private void FillPagesDropDownList(int iTotalPages)
    {
        ddlpageNumbers.Items.Clear();
        for (int i = 1; i <= iTotalPages; i++)
        {
            ddlpageNumbers.Items.Add(new ListItem(i.ToString(), i.ToString()));
        }
    }
    
    #endregion

    #region "Properties"

    private int ThisPageNumber
    {
        get
        {
            object obj = ViewState["ThisPageNumber"];
            return (obj == null) ? 0 : (int)obj;
        }
        set
        {
            this.ViewState["ThisPageNumber"] = value;
        }
    }

    #endregion

}

Extra Namespace used


using System.Data.SqlClient;

P.S: DataBase used is Northwind

SubTotals in GridView

2009-05-15T23:34:00.018+05:30

To display Sub Totals in a GridView is a situation that arise frequently.

Here's a sample on how to do it using GridView.

First we need a table TBL_Patients. Here's the structure

Now lets populate some sample values to it.

The ASPX



<asp:GridView ID="gvwPatientBill" runat="server"
                    AutoGenerateColumns="false"
                    DataKeyNames="BillID"
                    GridLines="Both"
                    OnRowDataBound="gvwPatientBill_RowDataBound">
        <Columns>
            <asp:TemplateField HeaderText="Bill ID">
                <ItemTemplate>
                    <asp:Label ID="lblBillID" runat="server"
                            Text='<%# Eval("BillID") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Patient ID">
                <ItemTemplate>
                    <asp:Label ID="lblPatientID" runat="server"
                            Text='<%# Eval("PatientID") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Patient Name">
                <ItemTemplate>
                    <asp:Label ID="lblPatientName" runat="server"
                            Text='<%# Eval("PatientName") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Patient Ward">
                <ItemTemplate>
                    <asp:Label ID="lblPatientWard" runat="server"
                            Text='<%# Eval("PatientWard") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Bill Amount">
                <ItemTemplate>
                    <asp:Label ID="lblBillAmount" runat="server"
                            Text='<%# Eval("BillAmount") %>'>
                    </asp:Label>
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
        <HeaderStyle BackColor="Black" ForeColor="White" />
    </asp:GridView>

The ASPX.CS


int iPatientIDCount = 0;
int iRowsCount = 0;
int iAddBills = 0;
string sPatientID = "";

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView();
    }
}

private void BindGridView()
{
    DataTable dtPatientBills = new DataTable();
    string strSelectCommand = "SELECT * FROM TBL_PatientBills ORDER BY PatientID";
    using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString))
    {
        using (SqlDataAdapter adapPatientBills = new SqlDataAdapter(strSelectCommand, sqlConn))
        {
            adapPatientBills.Fill(dtPatientBills);
        }
    }
    iRowsCount = dtPatientBills.Rows.Count - 1;
    gvwPatientBill.DataSource = dtPatientBills;
    gvwPatientBill.DataBind();
}

protected void gvwPatientBill_RowDataBound(object sender, GridViewRowEventArgs e)
{

    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        string sName = "";
        if (e.Row.RowIndex == 0)
        {
            sPatientID = ((Label)e.Row.FindControl("lblPatientID")).Text;
            iAddBills = Convert.ToInt32(((Label)e.Row.FindControl("lblBillAmount")).Text);
        }
        else
        {
            sName = ((Label)gvwPatientBill.Rows[e.Row.RowIndex - 1].FindControl("lblPatientName")).Text;
            iPatientIDCount = iPatientIDCount + 1;
            if (sPatientID == ((Label)e.Row.FindControl("lblPatientID")).Text)
            {
                iAddBills += Convert.ToInt32(((Label)e.Row.FindControl("lblBillAmount")).Text);
            }
            else
            {
                sPatientID = ((Label)e.Row.FindControl("lblPatientID")).Text;
                Table tblTemp = (Table)this.gvwPatientBill.Controls[0];
                int intIndex = tblTemp.Rows.GetRowIndex(e.Row);
                GridViewRow gvrSubTotal = CreateGridViewRow(
                        intIndex,
                        "#8FD8D8",
                        gvwPatientBill.Columns.Count,
                        "Total for " + sName + " in " + iPatientIDCount + " Entries: " + iAddBills,
                        20);
                tblTemp.Controls.AddAt(intIndex, gvrSubTotal);
                iPatientIDCount = 0;
                iAddBills = Convert.ToInt32(((Label)e.Row.FindControl("lblBillAmount")).Text);
            }
            if (iRowsCount == e.Row.RowIndex)
            {
                sName = ((Label)e.Row.FindControl("lblPatientName")).Text;
                Table tblTemp = (Table)this.gvwPatientBill.Controls[0];
                int intIndex = tblTemp.Rows.GetRowIndex(e.Row) + 1;

                GridViewRow gvrLast = CreateGridViewRow(
                        intIndex,
                        "#8FD8D8",
                        gvwPatientBill.Columns.Count,
                        "Total for " + sName + " in " + iPatientIDCount + " Entries: " + iAddBills,
                        20);
                tblTemp.Controls.AddAt(intIndex, gvrLast);
            }
        }
    }
}

private GridViewRow CreateGridViewRow(int iCurrentIndex, string sTableBackColor,int iTableColumnSpan, string sTableText, int iTableHeight)
{
    GridViewRow gvrTemp = new GridViewRow(iCurrentIndex, iCurrentIndex, DataControlRowType.Separator, DataControlRowState.Normal);
    TableCell cellTemp = new TableCell();
    cellTemp.BackColor = System.Drawing.ColorTranslator.FromHtml("#8FD8D8");
    cellTemp.Font.Bold = true;
    cellTemp.ColumnSpan = iTableColumnSpan;
    cellTemp.HorizontalAlign = HorizontalAlign.Left;
    cellTemp.Text = sTableText;
    cellTemp.Height = Unit.Pixel(iTableHeight);
    gvrTemp.Cells.Add(cellTemp);
    return gvrTemp;
}

And the final result looks like this.

NB: Its a no-no if you wanna implement Paging and Sorting here. For that, we have to use the wonderful third party
GridView controls like Teleriks RadGrid and all

Paging a Filtered GridView

2009-03-25T13:25:00.002+05:30

In many forums there are people struggling with filtering and paging in gridview.
When separated, both filtering and paging works smoothly.
But when trying to change the page index of a filtered result set, all hell will break loose.
Mostly a new page with the unfiltered result set comes into play.

Here's a sample on how to do it.

The ASPX



<table>
    <tr>
        <td>
            Search By Product Name:
        </td>
        <td>
            <asp:TextBox ID="txtProductName" runat="server"></asp:TextBox>
        </td>
        <td>
            <asp:Button ID="btnBindGridView" runat="server" 
                        Text="Populate Grid" onclick="btnBindGridView_Click" />
        </td>
    </tr>
    <tr>
        <td colspan="3">
            <asp:GridView ID="gvwProducts" runat="server" 
                            AllowPaging="true"
                            AutoGenerateColumns="false"
                            PageSize="3" 
                            OnPageIndexChanging="gvwProducts_PageIndexChanging">
                <Columns>
                    <asp:BoundField DataField="ProductName" HeaderText="Product Name"/>
                    <asp:BoundField DataField="QuantityPerUnit" HeaderText="Quantity"/>
                    <asp:BoundField DataField="UnitPrice" HeaderText="Price"/>
                </Columns>
            </asp:GridView>
        </td>
    </tr>
</table>

The ASPX.CS



protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView();
    }
}

protected void btnBindGridView_Click(object sender, EventArgs e)
{
    BindGridView();
}

protected void gvwProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvwProducts.PageIndex = e.NewPageIndex;
    BindGridView();
}

private void BindGridView()
{
    DataTable dtProducts = new DataTable();
    string strSelectCommand = "SELECT * FROM Products" 
            + ((txtProductName.Text.Trim() == "") ? "" : (" WHERE ProductName LIKE '" + txtProductName.Text.Trim() + "%'"));
    using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
    {
        using (SqlDataAdapter adapProducts = new SqlDataAdapter(strSelectCommand, sqlConn))
        {
            adapProducts.Fill(dtProducts);
        }
    }
    gvwProducts.DataSource = dtProducts;
    gvwProducts.DataBind();
}

DataBase used --> Northwind
Extra Namespace used -->System.Data.SqlClient;

Hope this helps!

Filtered GridView using DataList

2009-03-16T11:06:00.007+05:30

Many times the requirement comes like this.

need to show the first letter of products as a link outside grid and when the user clicks on it the grid will be showing the result with that particular product only.

Can be easily done using GridView and DataList.
Database used is Northwind.

The ASPX



<asp:DataList ID="dlstProducts" runat="server"
               RepeatDirection="Horizontal">
   <ItemTemplate>
       <asp:LinkButton ID="lnkFirstLetters" runat="server"
                       Text='<%# Bind("ProductNameFirstLetter") %>'
           onclick="lnkFirstLetters_Click">
       </asp:LinkButton>
   </ItemTemplate>
</asp:DataList>

<asp:GridView ID="gvwProducts" runat="server"
               AutoGenerateColumns="false"
               DataKeyNames="ProductID">
   <Columns>
       <asp:TemplateField HeaderText="Product Name">
           <ItemTemplate>
               <asp:Label ID="lblProductName" runat="server" Text='<%# Bind("ProductName") %>'>
               </asp:Label>
           </ItemTemplate>
       </asp:TemplateField>
       <asp:TemplateField HeaderText="Quantity Per Unit">
           <ItemTemplate>
               <asp:Label ID="lblQuantityPerUnit" runat="server" Text='<%# Bind("QuantityPerUnit") %>'>
               </asp:Label>
           </ItemTemplate>
       </asp:TemplateField>
   </Columns>
</asp:GridView>

The ASPX.CS



public partial class GridView_GridViewDataListFirstAlphabet : System.Web.UI.Page
{
   DataTable dtProducts = new DataTable();

   protected void Page_Load(object sender, EventArgs e)
   {
       if (!IsPostBack)
       {
           InitialBindOfControls();
       }
   }

   protected void lnkFirstLetters_Click(object sender, EventArgs e)
   {
       BindGridView(((LinkButton)sender).Text.Trim());
   }

   private void InitialBindOfControls()
   {
       BindGridView("Initial");
       BindDataList();
   }

   private void BindGridView(string strChar)
   {
       string strQuery = "SELECT ProductID, ProductName, QuantityPerUnit FROM Products "
                   + ((strChar == "Initial") ? "" : "WHERE ProductName LIKE'" + strChar + "%'")
                   + "ORDER BY ProductName";
       using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString()))
       {
           using (SqlDataAdapter adapProducts = new SqlDataAdapter(strQuery, sqlConn))
           {
               adapProducts.Fill(dtProducts);
               gvwProducts.DataSource = dtProducts;
               gvwProducts.DataBind();
           }
       }
   }

   private void BindDataList()
   {
       if (gvwProducts.Rows.Count != 0)
       {
           DataTable dtTemp = new DataTable();
           dtTemp.Columns.Add("ProductNameFirstLetter");
           dtTemp.Rows.Add(((Label)gvwProducts.Rows[0].FindControl("lblProductName")).Text.Substring(0, 1));
           foreach (GridViewRow gvrProducts in gvwProducts.Rows)
           {
               if (dtTemp.Rows[dtTemp.Rows.Count - 1][0].ToString() != ((Label)gvrProducts.FindControl("lblProductName")).Text.Substring(0, 1))
               {
                   dtTemp.Rows.Add(((Label)gvrProducts.FindControl("lblProductName")).Text.Substring(0, 1));
               }
           }
           dlstProducts.DataSource = dtTemp;
           dlstProducts.DataBind();
       }
   }
}

Extra Namespace used


using System.Data.SqlClient;

Maintaining State of CheckBox while Paging in GridView

2009-02-02T09:56:00.012+05:30

The Logic

We are storing the primary keys of the checkboxes that are checked into a List where T = data type of the primary key. This is done at PageIndexChanging before we change the page index and re-bind the GridView. And at RowDataBound we are checking whether the DataKeyName(primary key) of each row is in the List. If present , we mark the CheckBox as checked.

Heres the sample code.

The ASPX

<asp:LinkButton ID="btnSelectAllPage" runat="server" 
            CommandName="SelectAllPage" 
            Text="Select All Pages"
            OnCommand="SelectDeselectAllPages" />|
<asp:LinkButton ID="btnDeselectAllPage" runat="server" 
            CommandName="DeselectAllPage" 
            Text="Deselect All Pages"
            OnCommand="SelectDeselectAllPages" />|
<asp:LinkButton ID="btnSelectAll" runat="server" 
            CommandName="SelectAll" 
            Text="Select Page"
            OnCommand="SelectDeselect" />|
<asp:LinkButton ID="btnDeselectAll" runat="server" 
            CommandName="DeselectAll" 
            Text="Deselect Page"
            OnCommand="SelectDeselect" />

<asp:GridView ID="gvProducts" runat="server" 
            AllowPaging="True" 
            AutoGenerateColumns="False"
            DataKeyNames="ProductID"
            OnPageIndexChanging="gvProducts_PageIndexChanging" 
            OnRowDataBound="gvProducts_RowDataBound">
    <Columns>
        <asp:TemplateField HeaderText="Select">
            <ItemTemplate>
                <asp:CheckBox ID="chkSelect" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
    <asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False"
    ReadOnly="True" SortExpression="ProductID" />
    <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
    </Columns>
</asp:GridView>
<asp:SqlDataSource ID="sdsProducts" runat="server"
                ConnectionString='<%$ ConnectionStrings:NorthwindConnectionString %>'
                SelectCommand="SELECT * FROM Products">
</asp:SqlDataSource>

The ASPX.CS

private List<int> ProductIDs
{
    get
    {
        if (this.ViewState["ProductIDs"] == null)
        {
            this.ViewState["ProductIDs"] = new List<int>();
        }
        return this.ViewState["ProductIDs"] as List<int>;
    }
}

protected void SelectDeselect(object sender, CommandEventArgs e)
{
    foreach (GridViewRow gvr in gvProducts.Rows)
    {
        CheckBox chkSelect = gvr.FindControl("chkSelect") as CheckBox;
        if (chkSelect != null)
        {
            chkSelect.Checked = e.CommandName.Equals("SelectAll");
        }
    }
}

protected void SelectDeselectAllPages(object sender, CommandEventArgs e)
{
    DataSourceSelectArguments dssa = new DataSourceSelectArguments();
    dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
    dssa.RetrieveTotalRowCount = true;
    DataView dv = (DataView)sdsProducts.Select(dssa);
    foreach (DataRow dr in dv.Table.Rows)
    {

        int productID = Convert.ToInt32(dr.ItemArray[0]);//ItemArray[0] cos ProductID is in the first column
        if (e.CommandName.Equals("SelectAllPage") && !this.ProductIDs.Contains(productID))
        {
            this.ProductIDs.Add(productID);
        }
        if (e.CommandName.Equals("DeselectAllPage") && this.ProductIDs.Contains(productID))
        {
            this.ProductIDs.Remove(productID);
        }
    }
    foreach (GridViewRow gvr in gvProducts.Rows)
    {
        CheckBox chkSelect = gvr.FindControl("chkSelect") as CheckBox;
        if (chkSelect != null)
        {
            chkSelect.Checked = e.CommandName.Equals("SelectAllPage");
        }
    }
}

protected void gvProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    foreach (GridViewRow gvr in gvProducts.Rows)
    {
        CheckBox chkSelect = gvr.FindControl("chkSelect") as CheckBox;
        if (chkSelect != null)
        {
            int productID = Convert.ToInt32(gvProducts.DataKeys[gvr.RowIndex]["ProductID"]);
            if (chkSelect.Checked && !this.ProductIDs.Contains(productID))
            {
                this.ProductIDs.Add(productID);
            }
            else if (!chkSelect.Checked && this.ProductIDs.Contains(productID))
            {
                this.ProductIDs.Remove(productID);
            }
        }
    }
}

protected void gvProducts_RowDataBound(object sender, GridViewRowEventArgs e)
{
    GridViewRow gvr = e.Row;
    if (gvr.RowType == DataControlRowType.DataRow)
    {
        CheckBox chkSelect = gvr.FindControl("chkSelect") as CheckBox;
        if (chkSelect != null)
        {
            int productID = Convert.ToInt32(gvProducts.DataKeys[gvr.RowIndex]["ProductID"]);
            chkSelect.Checked = this.ProductIDs.Contains(productID);
        }
    }
}

As an extra enhancement, we are also giving provision for selecting, deselecting both page and the whole rows in every page also here.

P.S: We are using Northwind Sample DataBase by Microsoft, so if you dont have it download it.

Courtesy: A wonderful post by Ed Bruck at forums.asp.net