I recently had to write a method that would take a single row from a DataTable and expand that entry based on a start and end date and then determine which rows intersected with a range of dates.
The process to accomplish this seemed fairly straight forward:
- Clone the original table.
- Populate the new table with the correct number of entries.
- Add the new rows back that fall within the range of dates.
Original Item:
| ItemId | StartDate | EndDate | Time |
| 12345 | 3/5/2008 | 3/8/2008 | 8:00 AM |
Expanded Items:
| ItemId | StartDate | EndDate | Time |
| 12345 | 3/5/2008 | 3/8/2008 | 8:00 AM |
| 12345 | 3/6/2008 | 3/8/3008 | 8:00 AM |
| 12345 | 3/7/2008 | 3/8/2008 | 8:00 AM |
| 12345 | 3/8/2008 | 3/8/2008 | 8:00 AM |
Expected Results for Range of 3/6/2008 to 3/8/2008
| ItemId | StartDate | EndDate | Time |
| 12345 | 3/6/2008 | 3/8/3008 | 8:00 AM |
| 12345 | 3/7/2008 | 3/8/2008 | 8:00 AM |
| 12345 | 3/8/2008 | 3/8/2008 | 8:00 AM |
When I originally started out with creating this method my thought was to create only the rows that would be needed. So I began writing a bunch of branching logic that would compare the start date and the range start, compare the range end and end date, etc.; trying to determine how many days needed to be created and what day to start with. It quickly became very complicated and difficult for me to determine what I was doing. So I went back to the drawing board and figured out that with the help of the DataTable and DataRow classes in .NET that I could just expand the entire original item and then just select the rows where that intersected with my date range.
Code
Here is version of the method that I ended up with, I basically just removed the business logic that was specific to our application.
public DataTable ExpandRows(DataTable original, DateTime rangeStart, DateTime rangeEnd)
{
DataTable expanded = original.Clone();
//Assumes only one row for demo purposes
DateTime itemStart = DateTime.Parse(original.Rows[0]["StartDate"].ToString());
DateTime itemEnd = DateTime.Parse(original.Rows[0]["EndDate"].ToString());
int numDays = itemEnd.Subtract(itemStart).Days;
for (int i = 0; i <= numDays; i++)
{
DataRow newRow = expanded.NewRow();
newRow.ItemArray = original.Rows[0].ItemArray;
newRow["StartDate"] = itemStart.AddDays(i);
expanded.Rows.Add(newRow);
}
//select the rows that fall within the specified range of dates.
DataRow[] addRows =
expanded.Select(
string.Format("StartDate >= '{0}' AND StartDate <= '{1}'", rangeStart.ToShortDateString(),
rangeEnd.ToShortDateString()));
//Remove the original row,since we do not know if it falls within range.
original.Rows.Remove(original.Rows[0]);
//add expanded rows that fall within the range
int numRowsToAdd = addRows.Length;
for (int j = 0; j < numRowsToAdd; j++)
{
original.ImportRow(addRows[j]);
}
return original;
}
Limiting Results
Because I was using this method on a search page and I wanted to limit the results, because if you have an item with a large start/end date span and as well as a large range start/end date span there cold be hundreds or thousands of rows in the table. To limit the results I used a technique that I found in the ASP.NET Forums that shows how to add a unique id to the table and only return the a specified number of rows. So here is the final code with the limit the number of rows. Below is the final code:
public DataTable ExpandRows(DataTable original, DateTime rangeStart, DateTime rangeEnd, int maxRowCount)
{
DataTable expanded = original.Clone();
//Assumes only one row for demo purposes
DateTime itemStart = DateTime.Parse(original.Rows[0]["StartDate"].ToString());
DateTime itemEnd = DateTime.Parse(original.Rows[0]["EndDate"].ToString());
int numDays = itemEnd.Subtract(itemStart).Days;
for (int i = 0; i <= numDays; i++)
{
DataRow newRow = expanded.NewRow();
newRow.ItemArray = original.Rows[0].ItemArray;
newRow["StartDate"] = itemStart.AddDays(i);
expanded.Rows.Add(newRow);
}
//select the rows that fall within the specified range of dates.
DataRow[] addRows =
expanded.Select(
string.Format("StartDate >= '{0}' AND StartDate <= '{1}'", rangeStart.ToShortDateString(),
rangeEnd.ToShortDateString()));
//Remove the original row,since we do not know if it falls within range.
original.Rows.Remove(original.Rows[0]);
//add expanded rows that fall within the range
int numRowsToAdd = addRows.Length;
for (int j = 0; j < numRowsToAdd; j++)
{
original.ImportRow(addRows[j]);
}
if (maxRowCount > 0)
{
if (original.Rows.Count > maxRowCount)
{
DataTable autoIds = new DataTable();
DataColumn column = new DataColumn();
column.DataType = typeof(Int32);
column.AutoIncrement = true;
column.AutoIncrementSeed = 1;
column.AutoIncrementStep = 1;
column.ColumnName = "AutoRowId";
autoIds.Columns.Add(column);
autoIds.Merge(original, true, MissingSchemaAction.AddWithKey);
DataTable truncated = original.Clone();
DataRow[] limitedRows = autoIds.Select(string.Format("AutoRowId<={0}", maxRowCount));
for (int z = 0; z < maxRowCount; z++)
{
truncated.ImportRow(limitedRows[z]);
}
return truncated;
}
}
return original;
}
Lessons Learned
Here are some of the things that I learned along the way.
- Sometimes you need to step back and reassess the path you have initially chosen, it may not always be the best one. And in this case I came up with something that ended up being much simpler and more efficient.
- The importance of Unit Tests. I have been experimenting with using Test Driven Development (TDD) recently. So I was decided to switch my approach I was able to feel confident that the code was still working as expected, because I had immediate feedback as I made changes.
- You cannot directly insert a datarow from one table into another. If you try to do this you will get the error: "This row already belongs to another table". Instead, use ImportRow to copy the row into another DataTable.
- I was using a DataView to do some sorting on the DataTable like the following,
DataView dataView = new DataView(original, string.Empty, "StartDate ASC", DataViewRowState.OriginalRows);
However, because I was removing the original rows and adding new ones, the table was missing the original rows and the newly added ones; Because as the name states, it was looking at the original rows in the table... I needed to use the setting DataViewRowState.CurrentRows to display the rows as they currently existed in the table.
