Maintaining CheckBox State of multiple GridViews

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.
Read more...

Insert Arabic Words into Database

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.
Read more...