A two-way sortable DataGrid with a colors column

• Download source - 3.49 KB

Introduction

The article explains how to develop a 2-way sortable DataGrid with a colors column. The colors of the colors column are dependent on a specific column of your choice. For example, if we are binding an Employee table present in SQL Server to the DataGrid, then we can specify that the colors column be dependent on the salaries column.

Background

Create an Employee table in SQL Server with these fields:

1. EID
2. EName
3. ESalary
4. ECity
5. ECountry

Using the code

We will specify the colors to be displayed in the colors column in the web.config file:

<add key="color0" value="Orange"/>
<add key="color1" value="SkyBlue"/>
<add key="color2" value="Black"/>
<add key="color3" value="Brown"/>
<add key="color4" value="Yellow"/>
<add key="color5" value="Red"/>
<add key="color6" value="Pink"/>
<add key="color7" value="LightCoral"/>
<add key="color8" value="MistyRose"/>
<add key="color9" value="Gray"/>

Let Color0 through Color9 be the colors added in the web.config file and we need to access them in our application. Get them and store them in a static ArrayList of type Color.

private static Color[] Colorsfromconfig = new Color[]
{
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color0"]),
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color1"]),
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color2"]),
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color3"]),
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color4"]),
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color5"]),
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color6"]),
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color7"]),
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color8"]),
    ColorTranslator.FromHtml(ConfigurationManager.AppSettings["color9"])
};

Now, we will build the DataGrid. We will set AutoGenerateColumns to false and specify our own columns. The first column of the DataGrid will be an ItemTemplate column with a label in it, whose "BackColor" will be determined by the employee salary in the particular row. All the other columns will be bound columns.

<asp:DataGrid ID="ColorsGrid" runat="server" 
       AllowSorting="true" OnSortCommand="ColorsGrid_Sorting"
       Width="100%" ForeColor="#333333" 
       GridLines="Both" AutoGenerateColumns="false">
    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
    <EditItemStyle BackColor="Gainsboro" />                   
    <SelectedItemStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
    <AlternatingItemStyle BackColor="White" />
    <ItemStyle BackColor="#EFF3FB" />
    <HeaderStyle BackColor="Aqua" Font-Bold="True"/>
    <Columns>
    <asp:TemplateColumn HeaderText="Colors" 
       ItemStyle-CssClass="ItemDatagrid" ItemStyle-Width="25">
    <ItemTemplate>   
    <asp:Label ID="ColorsLabel" runat="server" 
      BackColor='<%# GetColor(DataBinder.Eval(Container.DataItem,"ESALARY"))  %>' 
      Height="12" Width="12">
    </ItemTemplate>
    </asp:TemplateColumn>
    <asp:BoundColumn DataField="ENAME" HeaderText="Name" 
      SortExpression="ENAME" ItemStyle-Width="25">
    <asp:BoundColumn DataField="ESALARY" HeaderText="Salary" 
      SortExpression="ESALARY" ItemStyle-Width="25">
    <asp:BoundColumn DataField="ECITY" HeaderText="City" 
      SortExpression="ECITY" ItemStyle-Width="25">
    <asp:BoundColumn DataField="ECOUNTRY" HeaderText="Country" 
      SortExpression="ECOUNTRY" ItemStyle-Width="25">    
    </Columns>
</asp:DataGrid>

As you can see, in the above DataGrid block, the "BackColor" of the Label in the template column is determined by the GetColor() method. Create a method which binds the data to the DataGrid; we will call it BindData(). To pass the sort expression, make BindData() a 1-parameter method.

public void BindData(string sortexpression)
{
    string ConnectionString = 
      ConfigurationManager.ConnectionStrings[1].Name.ToString();
    SqlConnection conn = new SqlConnection(ConnectionString);
    SqlDataAdapter sad;
    sad = new SqlDataAdapter("select * from employees", conn);
    DataSet ds = new DataSet();
    sad.Fill(ds, "Employees");
    DataView dv = new DataView(ds.Tables[0]);
    ColorsGrid.DataSource = dv;
    ColorsGrid.DataBind();
}

We should also make the DataGrid two-way sortable. The code below accomplishes this:

public static int numberDiv = 0;//this a public variable
DataView dv = new DataView(ds.Tables[0]);
if (sortexpression.Length > 0)
{
    if ((numberDiv % 2) == 0)
        dv.Sort = sortexpression + " " + "ASC";
    else
        dv.Sort = sortexpression + " " + "DESC";
    numberDiv++;
}

Now we should get all the salaries from the salary column and store it in an ArrayList. We will sort this ArrayList and use it in the GetColor() method.

public ArrayList AlColors = new ArrayList(); //this a public variable
foreach (DataRowView drv in dv)
{
    object val = drv.Row.IsNull("ESALARY") ? 0 : drv["ESALARY"];
    AlColors.Add(val);
}

One interesting thing is, what to do if we have more than one employee with the same salary. I decided to display the same color for all the employees with the same salary. For this, we need to iterate through the ArrayList and check whether there are any identical values present in the ArrayList "AlColors". If they are present, we should remove the duplicate entries. For example, let's say A's salary is 2000, and B's salary is 2000, and C's Salary is also 2000. Then there would be three entries in the ArrayList. In this case, we should iterate through the ArrayList and remove the duplicate entries such that only one item with value 2000 exists in the ArrayList. The below code accomplishes this. This should be done after all the salary values are added to the ArrayList AlColors.

for (int j = 0; j < AlColors.Count; j++)
{
    int k = 0;
    object strobject2 = new object();
    string obj1 = AlColors[j].ToString();

    for (int i = 0; i < AlColors.Count; i++)
    {
        string obj2 = AlColors[i].ToString();
        if (obj1 == obj2)
        {
            strobject2 = AlColors[i];
            k++;
        }
    }
    if (k > 1)
    {
        for (int l = 1; l < k; l++)
        {
            AlColors.Remove(strobject2);
        }
    }
}

AlColors.Sort();
AlColors.Reverse();

The completed BindData() method would look like:

public void BindData(string sortexpression)
{
    string ConnectionString = 
           ConfigurationManager.ConnectionStrings[1].Name.ToString();
    SqlConnection conn = new SqlConnection(ConnectionString);
    SqlDataAdapter sad;
    sad = new SqlDataAdapter("select * from employees", conn);
    DataSet ds = new DataSet();
    sad.Fill(ds, "Employees");
    DataView dv = new DataView(ds.Tables[0]);
    if (sortexpression.Length > 0)
    {
        if ((numberDiv % 2) == 0)
            dv.Sort = sortexpression + " " + "ASC";
        else
            dv.Sort = sortexpression + " " + "DESC";
        numberDiv++;
    }        
    foreach (DataRowView drv in dv)
    {
        object val = drv.Row.IsNull("ESALARY") ? 0 : drv["ESALARY"];
        AlColors.Add(val);
    }
    for (int j = 0; j < AlColors.Count; j++)
    {
        int k = 0;
        object strobject2 = new object();
        string obj1 = AlColors[j].ToString();

        for (int i = 0; i < AlColors.Count; i++)
        {
            string obj2 = AlColors[i].ToString();
            if (obj1 == obj2)
            {
                strobject2 = AlColors[i];
                k++;
            }
        }
        if (k > 1)
        {
            for (int l = 1; l < k; l++)
            {
                AlColors.Remove(strobject2);
            }
        }
    }
    AlColors.Sort();
    AlColors.Reverse();
    ColorsGrid.DataSource = dv;
    ColorsGrid.DataBind();
}

The OnSortCommand method ColorsGrid_Sorting will look like:

public void ColorsGrid_Sorting(object sender, DataGridSortCommandEventArgs e)
{
    string sortexpression = e.SortExpression;
    BindData(sortexpression);
}

We need to call the BindData() method from the PageLoad on no post back. This would look like:

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

Now, the final part, the GetColor() method. This is the actual method which sets the BackColor for the Label in the Colors column in the ColorsGrid. We will pass the salary value from a DataGrid row to this method. This value is then compared to the values in the ArrayList, and if it is found (it will be found), the corresponding position (the ArrayList is sorted in descending order) will be obtained, and based on the position value, we will pass the appropriate color value. Below is the code which does this.

public object GetColor(object eid)
{
    int iloop = 0;
    string strobj1 = eid.ToString();
    for (int i = 0; i < AlColors.Count; i++)
    {
        object strobj2 = AlColors[i].ToString();
        if (eid.ToString() == AlColors[i].ToString())
        {
            iloop = i;
            break;
        }
    }
    if (strobj1 == "0")
        iloop = 10;
    if (iloop < 9)
        return Colorsfromconfig[iloop];
    else
        return Colorsfromconfig[9];
}

Now, after doing all these, if you run the application, the output will look like the image shown at the start of the page.

Conclusion

Well, all things end in life! Doesn't matter whether they are good or bad, so does this article. I hope this article will help you in some way. I think DataGrid is a very, very interesting control to work on. So explore the DataGrid and enjoy it. Happy coding!