Transfer rows to column in C#

I was having requirement to change result of SQL in such a way that rows become columns, and columns become rows.

PersonName                Year2000               Year2001               Year2002               Year2003
------------------------- ---------------------- ---------------------- -------------------    ---------------
A                         1230                   4521                   5435                   5410
B                         1330                   4231                   5435                   1200
C                         3230                   1623                   5435                   3652
D                         4230                   2321                   5435                   1859

And need output as following

SalesYear       A                      B                      C                      D
--------------- ---------------------- ---------------------- ---------------------- ----------------------
Year2000        1230                   1330                   3230                   4230
Year2001        4521                   4231                   1623                   2321
Year2001        5435                   5435                   5435                   5435
Year2001        5410                   1200                   3652                   1859

Mr Jacob wrote post on this which is uses UNPIVOT operator which is in SQL Server 2005, you can find it here.

Here is the contributed code form Abidali Suthar [one of the developer in my team], which does the same thing but in C#.

I added extension method to DataTable, by calling SwapTable method on any DataTable instance; you can get the rows in columns and columns in rows. Lets look at extension method first.

public static class DatatTableExtension
{
    /// <summary>
    /// Extension method which transform row to column and column to row
    /// </summary>
    /// <param name="dt">Object on which we have to do operation</param>
    /// <returns>Transformed DataTable object</returns>
    public static DataTable SwapTable(this DataTable dt)
    {
        if (!(dt.Columns.Count > 0 && dt.Rows.Count > 0))
            return dt;

        DataTable dtNew = new DataTable();
        dtNew.Columns.Add(dt.Columns[0].ColumnName);

        // Creating columns for new DataTable. 
        // Adding column to new data table having name as first row of old data table
        for (int i = 0; i <= dt.Rows.Count - 1; i++)
            dtNew.Columns.Add(dt.Rows[i][0].ToString());

        DataRow row;
        // Swaping the values
        for (int k = 1; k < dt.Columns.Count; k++)
        {
            row = dtNew.NewRow();
            row[0] = dt.Columns[k].ToString();
            for (int j = 1; j <= dt.Rows.Count; j++)
                row[j] = dt.Rows[j - 1][k];

            dtNew.Rows.Add(row);
        }

        return dtNew;
    }
}

As we created extension method, its now easy to call it by creating object of DataTable. Lets see with example.

protected void Page_Load(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
    {
        con.Open();
        string strSelect = "SELECT_STATEMENT";

        SqlCommand cmd = new SqlCommand(strSelect, con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        con.Close();

        // Bind original datatable
        dgBefore.DataSource = dt;
        dgBefore.DataBind();

        // Bind swapped datatable, calling extension method on datatable5
        dgAfter.DataSource = dt.SwapTable();
        dgAfter.DataBind();
    }
}

Load method of DataTable

I found very interesting method of DataTable. There is Load method which loads the IDataReader directly to DataTable. Well this is not interestring I know lolz.

The case is like....

My query or procedure generates 4 different result set, and I have 4 different DataTable. As IDataReader has 4 result set, fatching all result set I have to use NextResult method which put my reader to next result set. like this. [Dont know this??? Have a look at this.]

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();
DataTable dt5 = new DataTable();

using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
{
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select * from TABLE1;select * from TABLE1;select * from TABLE2;select * from TABLE3";

    con.Open();
    IDataReader idr = cmd.ExecuteReader();

    dt1.Load(idr);
    idr.NextResult(); // Put DataReader to next result set
    dt2.Load(idr);
    idr.NextResult(); // Put DataReader to next result set
    dt3.Load(idr);
    idr.NextResult(); // Put DataReader to next result set. ERROR
    dt4.Load(idr);                
}

But this get fails at NextResult, why?

Because when you call Load method on DataTable, it automatical set pointer to next result set, we dont need to call NextResult here.

This is interesting thing, lets add few more on it. First we remove the NextResult method.

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();
DataTable dt5 = new DataTable();

using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
{
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select * from TABLE1;select * from TABLE2;select * from TABLE3;select * from TABLE4";

    con.Open();
    IDataReader idr = cmd.ExecuteReader();

    dt1.Load(idr);
    dt2.Load(idr);
    dt3.Load(idr);
    dt4.Load(idr);
    dt5.Load(idr);
}

Well this is working fine, can you see the dt5? I am trying to load 5th result set, but there is no such result set produced by my Sql statements, its still working!!!!, this is really interesting. Well its not succesfull operation; but its will make empty DataTable.