Some useful datatable extension methods we should know

Introduction

In our recent project, we have encountered some situations regarding the data table for which there was no inbuilt extension methods. Henceforth, we wrote our own and I am presenting those here.

List of DataTable Extension methods presented here

1. Subtract
2. Multiply
3. Divide
4. Average
5. GetDatatableColumnNames
6. GetDatatableColumnNamesAndDataType
7. IsNull
8. HasRows

1.   Subtract

The Subtract method will perform a subtraction of a-b-c fashion.

Let's look into the code presented below

	
/// <summary>
/// Subtraction of data rows in a datatable 
/// </summary>
/// <param name="dt"></param>
/// <param name="insertAt"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static DataTable Subtract(this DataTable dt, int insertAt, string columnName)
{
DataRow dr;
List<object> lstColumnResult = new List<object>();
int res = 0;
//Add the first column name
lstColumnResult.Add(columnName);
for (int i = 1; i < dt.Columns.Count; i++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
if (row == 0)
{
res = res + Convert.ToInt32(dt.Rows[row][i]);
}
else
{
res = res - Convert.ToInt32(dt.Rows[row][i]);
}
}
lstColumnResult.Add(res);
res = 0;
}
//add a new data row
dr = dt.NewRow();
dr.ItemArray = lstColumnResult.ToArray();
//insert the row at the position specified
dt.Rows.InsertAt(dr, insertAt);
//accept the changes
dt.AcceptChanges();
return dt;
}

Usage

First let us create a function that will return a datatable as under

private DataTable FillDataTable()
{
DataTable dt = new DataTable("DataTable1");
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Age", typeof(int));
dt.Columns.Add("Salary", typeof(double));
for (int i = 1; i <= 3; i++)
{
dt.Rows.Add("Name" + i.ToString(), i, i * 100);
}
return dt;
}

N.B.~ We will use this function across all other examples

Next, invoke the Subtract extension method as under

DataTable dt = FillDataTable();
var result = dt.Subtract(0, "Subtraction Result");

Output is as under

Code Explanation for the Subtract Extension Method

Consider the below code

int res = 0;
for (int i = 1; i < dt.Columns.Count; i++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
if (row == 0)
{
res = res + Convert.ToInt32(dt.Rows[row][i]); // for first row
}
else
{ 
res = res - Convert.ToInt32(dt.Rows[row][i]); // from second row onwards
}
}
lstColumnResult.Add(res); 
res = 0;
}

What we are upto is that we are going to perform a subtraction operation where the result will be of a-b-c. So, the first element is 'a'. If the first row is encounter, then the value will not change i.e. 0+a will be result to 'a' only. When the second row is encounter, we are subtracting the value of second row from the first one.

Once this operation is done, for a particular column, we are adding the result to the lstColumnResult. The res varible is set to it's initial state (res = 0) for the operation to be carried out for the next column (if it is available)

After that, we are inserting the row at the position indicitaed by the user and hence the result

2.   Multiply

The code is as under

	
/// <summary>
/// Multiplication of data rows in a datatable 
/// </summary>
/// <param name="dt"></param>
/// <param name="insertAt"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static DataTable Multiply(this DataTable dt, int insertAt, string columnName)
{
DataRow dr;
List<object> lstColumnResult = new List<object>();
int res = 1;
//Add the first column name
lstColumnResult.Add(columnName);
for (int i = 1; i < dt.Columns.Count; i++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
res = res * Convert.ToInt32(dt.Rows[row][i]);
}
lstColumnResult.Add(res);
res = 1;
}
//add a new data row
dr = dt.NewRow();
dr.ItemArray = lstColumnResult.ToArray();
//insert the row at the position specified
dt.Rows.InsertAt(dr, insertAt);
//accept the changes
dt.AcceptChanges();
return dt;
}

Invoke the Multiply extension method as under

DataTable dt = FillDataTable();
var result = dt.Multiply(0, "Multiplication Result");

Output is as under

3.   Divide

The code is as under

	
/// <summary>
/// Division of data rows in a datatable 
/// </summary>
/// <param name="dt"></param>
/// <param name="insertAt"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static DataTable Divide(this DataTable dt, int insertAt, string columnName)
{
DataRow dr;
List<object> lstColumnResult = new List<object>();
double res = 1;
//Add the first column name
lstColumnResult.Add(columnName);
for (int i = 1; i < dt.Columns.Count; i++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
if (row == 0)
{
res = Convert.ToDouble(dt.Rows[row][i]);
}
else
{
res = res / Convert.ToDouble(dt.Rows[row][i]);
}
}
lstColumnResult.Add(res);
res = 1;
}
//add a new data row
dr = dt.NewRow();
dr.ItemArray = lstColumnResult.ToArray();
//insert the row at the position specified
dt.Rows.InsertAt(dr, insertAt);
//accept the changes
dt.AcceptChanges();
return dt;
}

Invoke the Divide extension method as under

DataTable dt = FillDataTable(); 
var result = dt.Divide(0, "Division Result");

Output is as under

4.   Average

The code is as under

/// <summary>
/// Average of data rows in a datatable 
/// </summary>
/// <param name="dt"></param>
/// <param name="insertAt"></param>
/// <param name="columnName"></param>
/// <returns></returns>
public static DataTable Average(this DataTable dt, int insertAt, string columnName)
{
DataRow dr;
List<object> lstColumnResult = new List<object>();
int res = 0;
int rowCount = dt.Rows.Count;
//Add the first column name
lstColumnResult.Add(columnName);
for (int i = 1; i < dt.Columns.Count; i++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
res = res + Convert.ToInt32(dt.Rows[row][i]);
}
lstColumnResult.Add(res / rowCount);
res = 1;
}
//add a new data row
dr = dt.NewRow();
dr.ItemArray = lstColumnResult.ToArray();
//insert the row at the position specified
dt.Rows.InsertAt(dr, insertAt);
//accept the changes
dt.AcceptChanges();
return dt;
}

Invoke the Average extension method as under

DataTable dt = FillDataTable();
var result = dt.Average(0, "Average");

Output is as under

5.   GetDatatableColumnNames

The code is as under

	
/// <summary>
/// Lists the column names in a datatable
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static List<string> GetDatatableColumnNames(this DataTable dt)
{
List<string> lstColumnNames = new List<string>();
Enumerable.Range(0, dt.Columns.Count)
.ToList()
.ForEach(i => lstColumnNames.Add(dt.Columns[i].ColumnName));
return lstColumnNames;
}

Invoke the GetDatatableColumnNames extension method as under

DataTable dt = FillDataTable(); 
var columnNames = dt.GetDatatableColumnNames();

Output is as under

Code Explanation for the GetDatatableColumnNames Extension Method

By using the Range extension method of the Enumerable class, we are looping thru all the columns and adding the column names to the lstColumnNames Generic List

6.   GetDatatableColumnNamesAndDataType

The code is as under

	
/// <summary>
/// Lists the details of Column names and their types in a datatable
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static Dictionary<string, object> GetDatatableColumnNamesAndDataType(this DataTable dt)
{
Dictionary<string, object> dictColumnNameAndType = new Dictionary<string, object>();
Enumerable.Range(0, dt.Columns.Count)
.ToList()
.ForEach(i => dictColumnNameAndType.Add(dt.Columns[i].ColumnName, dt.Columns[i].DataType));
return dictColumnNameAndType;
}

Invoke the GetDatatableColumnNamesAndDataType extension method as under

DataTable dt = FillDataTable(); 
var columnNames_DataTypes = dt.GetDatatableColumnNamesAndDataType();

Output is as under

Code Explanation for the GetDatatableColumnNamesAndDataType Extension Method

By using the Range extension method of the Enumerable class, we are looping thru all the columns and adding the column names and datatypes of the columns to the dictColumnNameAndType Dictionary object

7.   IsNull

The code is as under

/// <summary>
/// Checks if a datatable is null
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static bool IsNull(this DataTable dt)
{
return dt == null ? true : false;
}

Invoke the IsNull extension method as under

DataTable dt = FillDataTable();
var isNull = dt.IsNull();

Output is : False

Code Explanation for the IsNull Extension Method

Checks if the datatable in null or not and returns the appropriate boolean value

8.   HasRows

The code is as under

/// <summary>
/// Checks if a datatable has rows
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static bool HasRows(this DataTable dt)
{
return dt.Rows.Count > 0 ? true : false;
}

Invoke the HasRows extension method as under

DataTable dt = FillDataTable(); 
var hasRows = dt.HasRows();

Output is : True

Code Explanation for the HasRows Extension Method

Checks if the datatable has rows and returns the appropriate boolean value

Conclusion

Hope this write up will help others who has a similar kind of requirement. You can modify the code as per your requirement. Share your feedback about the topic

Thanks for reading