BinaryFunctions.com

Introduction

This article helps to transpose (convert rows into columns and columns into rows) a DataTable using C# code in an ASP.NET Web application (can be used in Windows Form as well).

Background

This articles uses .NET Framework 3.5, can be used in any version as DataTable is applicable to all. Readers should be familiar with basic ASP.NET, C# & DataTable concepts to understand this article.

Using the Code

This code uses a DataTable with four columns and three rows as shown below. After calling the method GenerateTransposedTable, it will give you the output which is shown in the second table.

This can be used in the DataGrid control (both in Windows and Web applications). Using a single click, we can transpose a table. This may help us in most of the reporting functionalities.

Here, I have used it in an ASP.NET web page.

Actual Table

Transposed Table

Collapse | Copy Code

<table id="TableTopGrid" 
        style="width: 100%; border-left: darkgray 1px solid;"
        cellspacing="0" cellpadding="0" align="center">                           
    <tr>
    <td style="width:100%; background-color: #66ccff" align="center">
        <asp:Button ID="btnTransposeReport" 
            runat="server" Font-Bold="True" 
            Font-Names="Tahoma"
            Font-Size="8pt" Text="Query" 
            Width="75px" ForeColor="Navy" 
            OnClick="btnTransposeReport_Click" />
    </td>
    </tr>
    <tr>
    <td width="100%" align="center" valign="top">
        <asp:DataGrid ID="GridReport" runat="server"
           Height="100%" Width="100%" HorizontalAlign="Center"
           Font-Size="8pt" EditItemStyle-Wrap="true" 
           Font-Names="Tahoma" PageSize="50"
           AutoGenerateColumns="True" 
           AllowPaging="False" AllowSorting="False" GridLines="Both"
           ShowFooter="False" BackColor="AliceBlue">
        <ItemStyle Height="18px" Width="100%" 
            HorizontalAlign="Center" CssClass="Grid" />
        <HeaderStyle Font-Size="8pt" Font-Names="Tahoma" 
            Font-Bold="False" Width="100%" Height="20px"
            ForeColor="White" BackColor="DimGray" 
            HorizontalAlign="Center" CssClass="Grid" />

        </asp:DataGrid>
       </td>
    </tr>
    <tr>
    <td style="width:100%; background-color:DimGray" align="center">
         
    </td>
    </tr>
</table>

Here is the code:

Collapse | Copy Code

protected void Page_Load(object sender, EventArgs e)
{        
     if (!IsPostBack)
     {
         GridReport.DataSource = <your datatable>;
         // This is the table I shown in Figure 1.1

         GridReport.DataBind();

         // Your other codes here (if any)
     }
}

protected void btnTransposeReport_Click(object sender, EventArgs e)
{
     DataTable inputTable = <your datatable>;
     // Table shown in Figure 1.1

     DataTable transposedTable = GenerateTransposedTable(inputTable);

     GridReport.DataSource = transposedTable;
     // Table shown in Figure 1.2

     GridReport.DataBind();
}

private DataTable GenerateTransposedTable(DataTable inputTable)
{
     DataTable outputTable = new DataTable();

     // Add columns by looping rows

     // Header row's first column is same as in inputTable
     outputTable.Columns.Add(inputTable.Columns[0].ColumnName.ToString());

     // Header row's second column onwards, 'inputTable's first column taken
     foreach (DataRow inRow in inputTable.Rows)
     {
         string newColName = inRow[0].ToString();
         outputTable.Columns.Add(newColName);
     }

     // Add rows by looping columns        
     for (int rCount = 1; rCount <= inputTable.Columns.Count - 1; rCount++)
     {
         DataRow newRow = outputTable.NewRow();

         // First column is inputTable's Header row's second column
         newRow[0] = inputTable.Columns[rCount].ColumnName.ToString();
         for (int cCount = 0; cCount <= inputTable.Rows.Count - 1; cCount++)
         {
             string colValue = inputTable.Rows[cCount][rCount].ToString();
             newRow[cCount + 1] = colValue;
         }
         outputTable.Rows.Add(newRow);
     }

     return outputTable;
}

Conclusion

You can notice that the actual report is transposed (rows into columns and columns into rows) as shown in the figure.

Tags:

Introduction:

GridView provides a very easy way to edit the data by using the in-place editing feature. Although the feature is great but sometimes we want to view the complete GridView in the edit mode and quickly edit multiple records without having to click the edit button on each row. In this article I will demonstrate how to convert the whole GridView in edit mode with a click of a button.

Database Design:

In this article I will be using a custom database called School which consists of a single table called “Users”. The Users table contains only three columns namely FirstName, LastName and UserID.

Displaying Data in the GridView Control:

The first task is to display the data in the GridView control. Take a look at the method below which is used to populate the GridView control.

private void BindData()

    {

            string connectionString = "Server=localhost;Database=School;Trusted_Connection=true";

            SqlConnection myConnection = new SqlConnection(connectionString);

            SqlDataAdapter ad = new SqlDataAdapter("SELECT UserID, FirstName, LastName FROM Users", myConnection);

            DataSet ds = new DataSet();

            ad.Fill(ds);
     gvUsers.DataSource = ds;
        gvUsers.DataBind();

    }

Depending on the data in the database your GridView will be populated.



GridView HTML Code:

The HTML code of the GridView is where all the magic happens. Let’s first take a look at the code.

    <asp:GridView ID="gvUsers" AutoGenerateColumns="False" runat="server" OnRowDataBound="gvUsers_RowDataBound" CellPadding="4" Font-Names="Verdana" ForeColor="#333333" GridLines="None">


    <Columns>   

    <asp:TemplateField HeaderText="User ID">

    <ItemTemplate>

    <asp:Label ID="lblUserID" runat="server" Text='<%# Eval("UserID") %>' />

    </ItemTemplate>     

    </asp:TemplateField>

     <asp:TemplateField HeaderText="First Name">

    <ItemTemplate>

    <asp:Label ID="lblFirstName" Visible='<%# !(bool) IsInEditMode %>' runat="server" Text='<%# Eval("FirstName") %>' />

    <asp:TextBox ID="txtFirstName" Visible='<%# IsInEditMode %>' runat="server" Text='<%# Eval("FirstName") %>' />  

    </ItemTemplate>   

    </asp:TemplateField>   

     <asp:TemplateField HeaderText="Last Name">  

    <ItemTemplate>

    <asp:Label ID="lblLastName" Visible='<%# !(bool) IsInEditMode %>' runat="server" Text='<%# Eval("LastName") %>' />   

    <asp:TextBox ID="txtLastName" Visible='<%# IsInEditMode %>' runat="server" Text='<%# Eval("LastName") %>' />  

    </ItemTemplate>     

    </asp:TemplateField>   
    </Columns>      

    </asp:GridView>

Now, check out the <Columns> section where all the TemplateFields exists. The <ItemTemplate> section contains the Label as well as the TextBox. The visibility of the Label and the TextBox depends on the IsInEditMode property which, is a public property defined in the code behind. You can think of it as a switch meaning that when the Labels are displayed then the TextBoxes are not displayed and when the TextBoxes are displayed then the Labels will not be displayed.

IsInEditMode Property:

This is a simple property that returns the Boolean value of true or false.

private bool isEditMode = false;

protected bool IsInEditMode

    {

        get { return this.isEditMode; }

        set { this.isEditMode = value; }

    }
 

I have used a simple ASP.NET Button control to change the GridView to edit mode. Check out the code below:

 

// This method will put the GridView in the edit mode

    protected void Button1_Click(object sender, EventArgs e)

    {

        isEditMode = true;

        BindData();

    }

Yup! That is all you need to change the GridView to edit mode. The isEditMode variable is set to true and it will make TextBoxes visible and the Labels invisible.



The screenshot above shows the GridView in edit mode. In order to switch back to view mode all you need to do is to change the IsInEditMode to false and call the BindData method.

GridView Update All Rows At Once

Few weeks back I wrote an article in which I explained how to change the state of the GridView in edit mode where all rows are replaced by the textboxes. You can check out the article at the link below:

http://gridviewguy.com/ArticleDetails.aspx?articleID=219

More Gridview Articles can be found on http://www.highoncoding.com/Categories/7_GridView_Control.aspx

Serveral readers asked me how to send the data to the database at one go. Well, you can contruct an XML string or appending text and send the text to the database to be executed as a query. You need to be very careful when doing this sort of thing since, you might be inviting SQL Injections to the party. You can use Server.HtmlEncode to encode the strings and then send to them to be executed as a query.

Here is the Update method which iterates through all the rows in the Gridview and creates a long update statement.

private void Update()

{

StringBuilder sb = new StringBuilder();

// build the query

foreach (GridViewRow row in GridView1.Rows)

{

sb.Append("UPDATE Users SET FirstName = '");

sb.Append((row.FindControl("txtFirstName") as TextBox).Text);

sb.Append("',");

sb.Append("LastName = '");

sb.Append((row.FindControl("txtLastName") as TextBox).Text);

sb.Append("', ");

sb.Append("ClassCode = '");

sb.Append((row.FindControl("txtClassCode") as TextBox).Text);

sb.Append("'");

sb.Append(" WHERE UserID = ");

sb.Append(Convert.ToInt32((row.FindControl("lblUserID") as Label).Text));

sb.Append(" ");

}

string connectionString = "Server=HCUBE008;Database=School;Trusted_Connection=true";

SqlConnection myConnection = new SqlConnection(connectionString);

SqlCommand myCommand = new SqlCommand(sb.ToString(), myConnection);

myConnection.Open();

myCommand.ExecuteNonQuery();

myConnection.Close();

}

More Gridview Articles can be found on http://www.highoncoding.com/Categories/7_GridView_Control.aspx

Tags: asp.net

Tags: SSRS 2008, asp.net

Tags: asp.net