Explaining Tanspose DataTable Data in C#

Manoj Kalla
1d
526
0
7

Article

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!

Up Next

Ebook Download

View all

Diving Into Microsoft .NET Entity Framework

Read by 13.4k people

Download Now!

Learn

View all

(0) Comments