Convert and Bind Data to a Vertical DataGrid

Introduction

A data grid is a grid Table that deals with data — the controlled sharing and management of large amounts of distributed data.  The DataGrid control displays the fields of a data source as columns in a table. Each row in the control represents a record in the data source. The control supports selection, editing, deleting, paging, and sorting.

The DataGrid control with strong features is the most complicated control included within the ASP.NET framework. Like the Repeater and DataList controls, it enables to format and display records from a database table. However, it has several advanced features, such as support for sorting and paging through records, which makes it unique.

Records can be displayed in a DataGrid without using templates. A data source can be simply bound to the DataGrid, and it automatically displays the records.

Main

From a Description above I want to highlight one important line "The DataGrid control displays the fields of a data source as columns in a table. Each row in the control represents a record in the data source". What I try to explained here is to convert the DataGrid to "Each column in the DataGrid control represents a record in the data source and the fields displayed as rows in a table. So I try to convert it from horizontal record view to vertical record view and bind it to a DataGrid.

Step by step will be explained with more details here: First I retrieve the data from DataBase, then bind it to a DataSet and then convert the DataSet to a vertical mode before add it to a DataTable. From this DataTable, we can create one DataView and last to bind this DataView to a ASP.NET DataGrid.

Code Example as shown below:

The DataGrid:

    <form id="form1" runat="server">
    <div>
    <asp:datagrid ID="dgExample" runat="server">
    </asp:datagrid>
    </div>
    </form>

//On Page Load, Bind the Data
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }
}

Next is BindData syntax:

    private void BindData()
    {
        string connectionString = "Data Source=YourDBSource;Initial 
Catalog=NorthWind;UID=YourDBUserName;pwd=YourDBPassword";
        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "Select * from Orders";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable DT = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds, "UserDetails");
        //Convert the ds dataset to Vertical Mode
        ds = VerticalDataSet(ds);

        //Add data from converted DataSet to a DataTable
        for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
        {
            DT.Columns.Add(ds.Tables[0].Rows[0][i].ToString());
        }

        for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
        {
            DataRow dgrow = DT.NewRow();
            for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
            {
                dgrow[j] = ds.Tables[0].Rows[i][j];
            }

            DT.Rows.Add(dgrow);
        }

        //insert the DataTable to a DataView
        DataView dvArray = new DataView(DT);

        //Bind the DataView to a DataGrid
        dgExample.DataSource = dvArray;
        dgExample.DataBind();
    }

Last is the method to convert the dataset to vertical mode

    public DataSet VerticalDataSet(DataSet my_DataSet)
    {
        DataSet ds = new DataSet();
        foreach (DataTable dt in my_DataSet.Tables)
        {
            DataTable table = new DataTable();
            for (int i = 0; i <= dt.Rows.Count; i++)
            {
                table.Columns.Add(Convert.ToString(i));
            }
            DataRow row;
            for (int k = 0; k < dt.Columns.Count; k++)
            {
                row = table.NewRow();
                row[0] = dt.Columns[k].ToString();
                for (int j = 1; j <= dt.Rows.Count; j++)
                    row[j] = dt.Rows[j - 1][k];

                table.Rows.Add(row);
            }

            ds.Tables.Add(table);
        }
        return ds;
    }

The result will look like shown below.

Conclusion

Example used here serve illustrative purposes of DataGrid binding. In real-life situations, further enhancements can be made, such as generalizing custom records and Vertical Table to handle number of columns and records dynamically, change column name, insert style css to this DataGrid, and many others.

References

Include all the useful links or references that can help users learn about your tutorial

1. MSDN: DataGrid Class

Other Related and Popular Articles :

Author Profile : Hans Candra

Comments