Victor’s Programming Aid

At some point, every C# programmer deals with populating, selecting, and even deleting from a DataTable. A moderately advanced operation is transferring an entire single row from an original DataTable and inserting it into a new DataTable. My original thoughtprocess is the following:

DataTable originalTable = new DataTable();
originalTable.Columns.Add("id", typeof(int));
originalTable.Columns.Add("firstName", typeof(string));
originalTable.Columns.Add("lastName", typeof(string));
originalTable.Columns.Add("age", typeof(string));
originalTable.Columns.Add("gender", typeof(string));

DataRow dr = originalTable.NewRow();
dr["id"] = 1;
dr["firstName"] = "Victor";
dr["lastName"] = "Chen";
dr["age"] = 5;
dr["gender"] = "M";
originalTable.Rows.Add(dr);

dr = originalTable.NewRow();
dr["id"] = 2;
dr["firstName"] = "John";
dr["lastName"] = "Doe";
dr["age"] = 35;
dr["gender"] = "M";
originalTable.Rows.Add(dr);

dr = originalTable.NewRow();
dr["id"] = 3;
dr["firstName"] = "Jane";
dr["lastName"] = "Doe";
dr["age"] = 32;
dr["gender"] = "F";
originalTable.Rows.Add(dr);

DataTable newTable = originalTable.Clone();
newTable.Rows.Add(originalTable.Rows[1]);

Though the above example is long, lines 1 - 30 handle creating and populating the original DataTable (If you need explanation of how this works, check out Add Rows to a DataTable in C# tutorial). Line 32 creates a new DataTable by taking the structure of the original DataTable. In Line 33, we attempt to select Row 1 from the original DataTable and add it as a new row in the new DataTable. However, the above example above does not work. It will throw the runtime error below.

This row already belongs to another table.

The correct solution takes more lines of coding (and therefore more logic), but ultimately gets the job done. It involves using a DataRow’s ItemArray method to transfer a row’s data column by column through an object array, and inserting them column by column back into a new DataRow. See the example below:

DataTable originalTable = new DataTable();
originalTable.Columns.Add("id", typeof(int));
originalTable.Columns.Add("firstName", typeof(string));
originalTable.Columns.Add("lastName", typeof(string));
originalTable.Columns.Add("age", typeof(string));
originalTable.Columns.Add("gender", typeof(string));

DataRow dr = originalTable.NewRow();
dr["id"] = 1;
dr["firstName"] = "Victor";
dr["lastName"] = "Chen";
dr["age"] = 5;
dr["gender"] = "M";
originalTable.Rows.Add(dr);

dr = originalTable.NewRow();
dr["id"] = 2;
dr["firstName"] = "John";
dr["lastName"] = "Doe";
dr["age"] = 35;
dr["gender"] = "M";
originalTable.Rows.Add(dr);

dr = originalTable.NewRow();
dr["id"] = 3;
dr["firstName"] = "Jane";
dr["lastName"] = "Doe";
dr["age"] = 32;
dr["gender"] = "F";
originalTable.Rows.Add(dr);

DataTable newTable = originalTable.Clone();
DataRow newRow = newTable.NewRow();
newRow.ItemArray = originalTable.Rows[1].ItemArray;
newTable.Rows.Add(newRow);

Keeping consistent with our original example, lines 1-30 handle creating a DataTable. On line 32, create a DataTable. On Line 32, create a new DataRow. On Line 33, I select Row 1 from the Original DataTable, take it’s ItemArray, and transfer it to the newly created DataRow from Line 32. Finally, on line 34, I transfer newRow into the newTable.

If you have more than one row you would like to copy, merely repeat this process through a loop. Happy Coding!

When binding a DataTable to a GridView, I always map every column in the DataTable to a new column in GridView (whether it be manually or automatic). I would then hide the columns I did not want to display. In this way, I could still access values in the hidden columns on GridView events such as RowDataBound. A common example of this situation is creating a hidden column for the row’s unique database id.

Since then, I have learned usage practices for a DataKey, which is native to the GridView control. In a high level overview of a DataKey, we simply specify the names of a DataTable’s columns (in which we originally wanted to hide in a GridView) in a comma separated list. The values can then be accessed via the GridView.

For a concrete example, let us assume we have the following information retrieved from a database. The column name id is our primary key from the database:

We only want to display on the GridView a firstName, lastName, and gender. However, during GridView events, we still want access to the id and age. To setup the GridView, it only takes 3 easy steps!

1. Place a GridView control on your aspx page.
2. Manually setup visible columns for firstName, lastName, and gender. (A tutorial on this step)
3. In the GridView properties and find DataKeyNames (There is an image on what this looks like at the top of this tutorial). Insert a comma separated list for that property: id,age

Now, when you compile and run the page, you will see your GridView is populated with 3 columns. Now to access the hidden values id and age only takes an if statement and one line per hidden column! We will access these values on the RowDataBound event:

protected void gvTemplate_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        int rowIndex = e.Row.DataItemIndex;
        int id = Convert.ToInt32(gvTemplate.DataKeys[rowIndex].Values["id"]);
        int age = Convert.ToInt32(gvTemplate.DataKeys[rowIndex].Values["age"]);
    }
}

In our example above, gvTemplate is the name of our GridView. We access the DataKeys property and specifically access the rowIndex of interest. Because we specified two items id and age in the GridView, we can access them by name. You can also access it by index, but using the actual index’s name makes the code more readable. The final step in the line is to convert the object into the variable type of your choice. If you want, you can shorthand the code with:

gvTemplate.DataKeys[rowIndex]["age"]

You will still need to convert the object into the variable type of your choice. In this case, the best choice is to convert an age it into a integer. And there we have it! We have successfully created a GridView without hidden columns, but still accessible hidden values!

In C#, a common function in web pages containing data in a table is allowing the web user to export to excel. Through my experiences, I have uncovered that to export from a GridView (or DataGrid) to a Microsoft Excel (xls) file is messy and not easily customizable. Instead, I found that taking the underlying DataTable and instead exporting to a Microsoft Excel Comma Separated Value (CSV) file as a much better method. Below is the snippet of code for the function.

private void exportDataTableToCsv(DataTable formattedDataTable, string filename)
{
   DataTable toExcel = formattedDataTable.Copy();
   HttpContext context = HttpContext.Current;
   context.Response.Clear();

   foreach (DataColumn column in toExcel.Columns)
   {
      context.Response.Write(column.ColumnName + ",");
   }

   context.Response.Write(Environment.NewLine);
   foreach (DataRow row in toExcel.Rows)
   {
      for (int i = 0; i < toExcel.Columns.Count; i++)
      {
         context.Response.Write(row[i].ToString().Replace(",", string.Empty) + ",");
      }

      context.Response.Write(Environment.NewLine);
   }

   context.Response.ContentType = "text/csv";
   context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
   context.Response.End();
}

To call this function, simple create a C# LinkButton or Button Web Control. In your aspx page, your control should look similar to:

<asp:LinkButton ID="lbToExcel" runat="server">Export To Excel</asp:LinkButton>

From here, the lbToExcel_Click function will call the Export to DataTable function exportDataTableToCsv defined earlier in this post. I would also advise making edits at this point to your DataTable by removing columns that were hidden in the GridView (or DataGrid). We want to pass a formattedDataTable.

If your page uses Ajax’s Update Panel controls, it is required to set the lbToExcel web control as a PostBackTrigger as opposed to an AsyncPostBackTrigger. To add a PostBackTrigger in Visual Studio 2008’s Ajax Update Panel, find the properties of the Update Panel. Under Behavior, find Triggers and choose to edit the Collection. Next, Click Add and from the Dropdown select PostBackTrigger. Next, you need to manually (without the aid of intellisense) type in the control name. In our case, the control name was lbToExcel. The result should be the code snippet example below:

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:LinkButton ID="lbToExcel" runat="server">Export To Excel</asp:LinkButton>
</ContentTemplate>
<Triggers>
<asp:PostBackTrigger ControlID="lbToExcel">
</asp:PostBackTrigger>
</Triggers>
</asp:UpdatePanel>

Overall, this has worked flawlessly for me. If you have any problems, feel free to drop a line describing your challenges and I can attempt to give you a hand!