In C# ,DataTable is a class that represents data in the form of rows and columns in a table similar to the tables we create in SQL database. It is the central object in the ADO.NET library and is also used by other objects such as DataView, DataSet etc.
Select Method in DataTable
Select method basically retrieves an array of rows (DataRow) from DataTable based on the specific criteria provided.
Let’s understand and learn about Select in detail as below:
Definition of Select Method in the DataTable Class
public DataRow[] Select();
public DataRow[] Select(string filterExpression, string sort,
DataViewRowState recordStates);
public DataRow[] Select(string filterExpression, string sort);
public DataRow[] Select(string filterExpression);
//Here, DataRow [] is an array that returns a row in the data table.
Below is a simple example to return a DataRow from select method:
private void GetRows()
{
DataTable table1 = new DataTable("Employees");
DataRow[] rows = table1.Select();
for (int i = 0; i < rows.Length; i++)
{
Console.WriteLine(rows[i][ "Organisation Name" ]);
}
}Example of C# program that uses DataTable Select method
static void Main()
{
// Create a table of 10 different students.
// Store their roll number, exam results and date
// of admission
DataTable table = new DataTable("Students");
table.Columns.Add(new DataColumn("RollNumber", typeof(int)));
table.Columns.Add(new DataColumn("Result", typeof(char)));
table.Columns.Add(new DataColumn("AdmissionDate",
typeof(DateTime)));
table.Rows.Add(100, 'P', new DateTime(2000, 9, 2));
table.Rows.Add(101, 'F', new DateTime(1998, 2, 11));
table.Rows.Add(102, 'P', new DateTime(1999, 1, 6));
table.Rows.Add(103, 'P', new DateTime(2001, 3, 29));
table.Rows.Add(104, 'F', new DateTime(2000, 8, 7));
table.Rows.Add(105, 'F', new DateTime(1998, 4, 8));
table.Rows.Add(106, 'P', new DateTime(2001, 10, 15));
table.Rows.Add(107, 'F', new DateTime(2001, 7, 3));
table.Rows.Add(108, 'P', new DateTime(2001, 10, 25));
table.Rows.Add(109, 'F', new DateTime(2001, 1, 1));
//Search for Students who have passed (have result as 'P')
DataRow[] output1 = table.Select(" Result = 'P' ");
Console.WriteLine(" Output of Select Query 1 : ");
foreach (DataRow row in output1)
{
Console.WriteLine("Roll Number : {0} , Result : {1} ,
Admission Date : {2} ",
row[0], row[1], row[2]);
}
// Search for Students above RollNumber 105 and have admission
// date higher than 5/10/2001
DataRow[] output2 = table.Select(" RollNumber > = 105
AND AdmissionDate > #5/10/2001# ");
Console.WriteLine(" Output of Select Query 2 : ");
foreach (DataRow row in output2)
{
Console.WriteLine(" Roll Number : {0} ,
Result : {1} , Admission Date : {2} ",
row[0], row[1], row[2]);
}
//Search for Students above RollNumber 105
//who have passed and have admission date higher than 5/10/2001
DataRow[] output3 = table.Select(" Result = 'P'
AND RollNumber >= 105
AND AdmissionDate > #5/10/2001#");
Console.WriteLine(" Output of Select Query 3 : ");
foreach (DataRow row in output3)
{
Console.WriteLine(" Roll Number : {0} , Result : {1} ,
Admission Date : {2} ",
row[0], row[1], row[2]);
}
}In the above code , we create a table with the data of 10 different students . The data consists of the roll number of the students, their overall result (either pass or fail) and the date of their admission. As shown, the DataTable is created using the syntax shown above. The columns in the DataTable are declared using table.Columns.Add() and the names and the types are defined for each. The data of 10 students is added using table.Rows.Add().
Output
3 select queries are then provided. The 1st select query searches for the students who have passed i.e. have result grade as “P”. Output below:
The 2nd select query is such that the output should contain students whose roll number is more than 105 and have admission date after 5/10/2001. Output as below:
The 3rd select query looks for students that have a combination of both first and second search query i.e. they have roll number more than 105 , have passed and have admission date after 5/10/2001. Output as below:
The output that is calculated of these select queries gets stored in the arrays of type DataRow called output1, output2 and output3. ForEach loop then iterates over the newly declared arrays output1, output2 and output3 in order to display the values of the select queries stored in these arrays.
We can notice that the output2 and output3 arrays have only those values that satisfy all the conditions in that particular select query due to the ‘AND’ clause.
Uses of DataTable
DataTable class contains methods that help with some highly crucial operations such as filtering data, sorting data, copying data, removing data, duplicating data and many more.
Properties of DataTable
DataTable in C# has various useful properties. Let us look at some of them.
- TableName property sets the name of the table.
- Rows and Columns represent all the rows and columns of the table respectively.
- Constraints property lists all the constraints in the table.
- DefaultView returns a customized view of the datatable .
- Dataset returns the particular dataset .
- PrimaryKey – returns primary keys for a table in an array.
- ParentRelation and ChildRelation – which return the tables related as parent and child table to the given table respectively .
Methods in DataTable class
The methods listed below perform the mentioned operations:
- Clone(), as the name suggests , is a method to create a clone (duplicate) the entire data table along with the schema.
- Copy() is a method that copies and Clear is a method that deletes all the data from the data table and the schema.
- NewRow() creates a new row in the table while Reset resets a table to its original form.
- AcceptChanges() to commit all the changes made since the last commit.
- RejectChanges()to reject all the changes made since the last commit
Conclusion
We looked at the Select method and its overloads in the DataTable class with Examples. Please let us know about your suggestions in the comment box.