Some useful datatable extension methods we should know

Niladri.Biswas
Posted by in C# category on for Beginner level | Points: 250 | Views : 6653 red flag
Rating: 3 out of 5  
 1 vote(s)

Some datatable extension methods we need often in projects

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

Name Age Salary
Subtraction Result -4 -400
Name1 1 100
Name2 2 200
Name3 3 300

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

Name Age Salary
Multiplication Result 6 6000000
Name1 1 100
Name2 2 200
Name3 3 300

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

Name Age Salary
Division Result 0.16666666666666666 0.0016666666666666667
Name1 1 100
Name2 2 200
Name3 3 300

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

Name Age Salary
Average 2 200
Name1 1 100
Name2 2 200
Name3 3 300

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

Name
Age
Salary

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

{[Name, System.String]}
{[Age,System.Int32]}
{[Salary,System.Double]}

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

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)