.NET  

Explaining Tanspose DataTable Data in C#

In this walk-through, you will learn how to transpose the data of the DataTable object and get answers to the following questions.

  • Create DataTable programmatically.
  • Create Columns with DataType.
  • Add rows manually to DataTable.
  • Using of StringBuilder.
  • Program to convert DataTable data to Transpose.

Create Project

First create Project Asp.Net WebForm.

Right-click on Project and Add a new WebForm page. In our walk-through, we created a WebForm1.aspx page.

First, we work on DataTable.

// Create a new DataTable.

DataTable table = new DataTable("Friends");

Note: System.Data namespace required for DataTable.

Create Column

// Define columns.

DataColumn column;

Add Columns: Fullname, City, Phone.

// Create and add the Fullname column.
DataColumn column = new DataColumn
{
    DataType = Type.GetType("System.String"),
    ColumnName = "Fullname"
};
table.Columns.Add(column);

// Create and add the City column.
column = new DataColumn
{
    DataType = Type.GetType("System.String"),
    ColumnName = "City"
};
table.Columns.Add(column);

// Create and add the Phone column.
column = new DataColumn
{
    DataType = Type.GetType("System.String"),
    ColumnName = "Phone"
};
table.Columns.Add(column);

Manually add the rows to the DataTable:

table.Rows.Add("Ramesh", "Pune", "54654646");
table.Rows.Add("Suresh", "Nashik", "333334646");
table.Rows.Add("Mangesh", "Pandharpur", "3555546");
table.Rows.Add("Jayesh", "Shirdi", "388889996");
table.Rows.Add("Pritesh", "Narayanpur", "98999996");
table.Rows.Add("Rajesh", "Aakalkot", "77889996");
table.Rows.Add("Kamlesh", "Manmad", "8787896");
table.Rows.Add("Gudakesh", "Bhimashankar", "656896");
table.Rows.Add("Indresh", "Raver", "689789896");

First, we check whether DataTable was created perfectly or not. For that, we bind with GridView Control.

// Bind with DataTable object.
GridView1.DataSource = table;
GridView1.DataBind();

Output

Transpose-Data-DataTable-DotNet-NET-CSharp-C#

Now, we convert DataTable data to Transpose.

We Transpose the DataTable dynamically within two steps, Auto-generate header and fill data as per columns of datatable.

Step 1. Create an HTML Table and populate the table headers with Data Updation.

Step 2. Fill data by replacing [DataCol" + xcolNo.ToString() + "]"

Variable DataCol is replaced with DataVal.

There are the following two methods that complete our transpose functionalities.

  • GetHeader: Generate header and write complete table with DataCol
  • GetData: This will generate columns within the above table created with GetHeaders.

Output

Transpose-DataTable-DotNet-NET-CSharp-c#

WebForm1.aspx file Code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebFormLearn.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">

    <title></title>

    <style>
        table, tr, td {
            border: 1px solid black
        }

    </style>
</head>

<body>

    <form id="form1" runat="server">
        <div>
            <br />
            <br />
            <br />
            <br />

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

            <br />
            <br />
            <br />
            <br />

            <asp:Literal ID="ltTable" runat="server"></asp:Literal>
        </div>
    </form>

</body>
</html>

WebForm1.aspx.cs file code

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebFormLearn
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            // Create a new DataTable
            DataTable table = new DataTable("Friends");

            // Define columns
            DataColumn column;

            column = new DataColumn
            {
                DataType = Type.GetType("System.String"),
                ColumnName = "Fullname"
            };
            table.Columns.Add(column);

            column = new DataColumn
            {
                DataType = Type.GetType("System.String"),
                ColumnName = "City"
            };
            table.Columns.Add(column);

            column = new DataColumn
            {
                DataType = Type.GetType("System.String"),
                ColumnName = "Phone"
            };
            table.Columns.Add(column);

            // Add sample rows
            table.Rows.Add("Ramesh", "Pune", "54654646");
            table.Rows.Add("Suresh", "Nashik", "333334646");
            table.Rows.Add("Mangesh", "Pandharpur", "3555546");
            table.Rows.Add("Jayesh", "Shirdi", "388889996");
            table.Rows.Add("Pritesh", "Narayanpur", "98999996");
            table.Rows.Add("Rajesh", "Aakalkot", "77889996");
            table.Rows.Add("Kamlesh", "Manmad", "8787896");
            table.Rows.Add("Gudakesh", "Bhimashankar", "656896");
            table.Rows.Add("Indresh", "Raver", "689789896");

            // Build HTML table string
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("<table border='1'>");
            sb.AppendLine(GetHeader(table));
            sb.AppendLine("</table>");

            string TableStrValue = sb.ToString();

            int xColNo = 0;
            for (int i = 0; i < table.Columns.Count; i++)
            {
                xColNo++;
                string DataVal = GetData(table, i);
                string DataCol = "[DataCol" + xColNo + "]";
                TableStrValue = TableStrValue.Replace(DataCol, DataVal);
            }

            // Bind to GridView
            GridView1.DataSource = table;
            GridView1.DataBind();

            // Set generated HTML table
            ltTable.Text = TableStrValue;
        }

        public string GetHeader(DataTable dt)
        {
            StringBuilder headerString = new StringBuilder();

            if (dt != null && dt.Columns.Count > 0)
            {
                int xcolNo = 0;
                foreach (DataColumn col in dt.Columns)
                {
                    xcolNo++;
                    headerString.AppendLine("<tr>");
                    headerString.AppendLine("<td>");
                    headerString.AppendLine(col.ColumnName);
                    headerString.AppendLine("</td>");
                    headerString.AppendLine("[DataCol" + xcolNo + "]");
                    headerString.AppendLine("</tr>");
                }
            }

            return headerString.ToString();
        }

        public string GetData(DataTable dt, int ColNo)
        {
            StringBuilder DataString = new StringBuilder();

            if (dt != null && dt.Columns.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    DataString.AppendLine("<td>");
                    DataString.AppendLine((string)row[ColNo]);
                    DataString.AppendLine("</td>");
                }
            }

            return DataString.ToString();
        }
    }
}

Happy Coding!

Membership not found