Introduction
DataTable represents data in table format. It also contains a collection of constraint objects that can be used to ensure the integrity of the data. Here in Read Data From DataTable in C# provides a list of code examples to create Datatable read, insert, update data from DataTable.
We will see here also how to convert DataTable to the array and generic collection.
Read Data From DataTable in C#
 |
Read Data From DataTable in C# |
Getting Started
DataTable is an ADO.NET object and member of the System.Data namespace that stores data and represents data in a table format in memory. It contains a collection of columns and rows to store data. The DataTable directly or with DataSet can be used to store or retrieve data from the Database.
The DataTable objects inherit from MarshalByValueComponent and support the ISerializable interface for .NET Framework remoting. These are the only ADO.NET objects that you can use for .NET Framework remoting.
Schema must be defined first when you are creating a DataTable programmatically.
The schema of a table is represented by columns and constraints and defines using DataColumn objects as well as ForeignKeyConstraint and UniqueConstraint objects.
The columns in a table can map to columns in a data source, contain calculated values from expressions, automatically increment their values, or contain primary key values. Using one or more columns you can build parent-child relationships between tables.
Read Data From DataTable in C#
Demonstration
The demonstration creates a DataTable programmatically and it’s Schema, list outs columns names using a loop. It retrieves data from the table. Data insertions and updations and deletions also perform in this demonstration.
This demonstration provides a block of code for each operation, see all the codes below.
C# DataTable Example
The below code example creates a DataTable programmatically and provides a name to it. The DataTable class is member of System.Data namespace is used to create data table.
//initialized DataTable and set name property
DataTable dataTable = new DataTable();
dtaTable.TableName = "MyTestTable";
Add Column to DataTable
This code example adds 5 columns into DataTable including a computed column which displays the multiplication value of column quantity and price, the column is created using DataColumn class. To add column/columns into DataTable, you have to add column objects into DataTable.Column collection property.
//initializing column and add column to table
DataColumn SnoColumn = new DataColumn();
SnoColumn.ColumnName = "Sno";
SnoColumn.DataType = typeof(int);
dataTable.Columns.Add(SnoColumn);
DataColumn ProductColumn = new DataColumn();
ProductColumn.ColumnName = "Product";
ProductColumn.DataType = typeof(string);
dataTable.Columns.Add(ProductColumn);
DataColumn QuanityColumn = new DataColumn();
QuanityColumn.ColumnName = "Quanity";
QuanityColumn.DataType = typeof(int);
dataTable.Columns.Add(QuanityColumn);
DataColumn PriceColumn = new DataColumn();
PriceColumn.ColumnName = "Price";
PriceColumn.DataType = typeof(double);
dataTable.Columns.Add(PriceColumn);
// declaration of computed column
DataColumn AmountColumn = new DataColumn("Amount", typeof(double), "Quanity*Price");
dataTable.Columns.Add(AmountColumn);
Add Column to DataTable
Add Data to DataTable
To add data to a DataTable, you must first use the NewRow method to return a new DataRow object. The NewRow method returns a row with the schema of the DataTable, as it is defined by the table's DataColumnCollection. The maximum number of rows that a DataTable can store is 16,777,216. The below code provides two examples to insert and update a row into DataTable.
//Initializing row and insert row to column
DataRow row = dataTable.NewRow();
row["Sno"] = 1;
row["Product"] = "Onida TV";
row["Quanity"] = 2;
row["Price"] = 12000.00;
dataTable.Rows.Add(row);
//Add row into table using loop
for(int i=2;i<=5;i++)
{
DataRow row1 = dataTable.NewRow();
row1["Sno"] = i;
row1["Product"] = "Onida TV";
row1["Quanity"] = i;
row1["Price"] = 12000.00;
dataTable.Rows.Add(row1);
}
Columns from DataTable.
The below code retrieves columns from DataTable and prints column information in the console screen.
//Fetching Columns from datatable.
foreach (DataColumn column in dataTable.Columns)
{
Console.WriteLine("Name of column :" + column.ColumnName);
Console.WriteLine("DataType :" + column.DataType);
}
Column values from DataTable.
The below two examples describes how to retrieves row and column data using for and foreach loop.
//Fetching data from data table using foreach loop
foreach (DataRow row1 in dataTable.Rows)
{
Console.WriteLine("Sno :" + row1["Sno"].ToString());
Console.WriteLine("Product :" + row1["Product"].ToString());
Console.WriteLine("Quanity :" + row1["Quanity"].ToString());
Console.WriteLine("Price :" + row1["Price"].ToString());
Console.WriteLine("Amount :" + row1["Amount"].ToString());
}
//Fetching data from data table using for loop
for (int i = 0; i < dataTable.Rows.Count; i++)
{
Console.WriteLine("Sno :" + dataTable.Rows[i]["Sno"].ToString());
Console.WriteLine("Product :" + dataTable.Rows[i]["Product"].ToString());
Console.WriteLine("Quanity :" + dataTable.Rows[i]["Quanity"].ToString());
Console.WriteLine("Price :" + dataTable.Rows[i]["Price"].ToString());
Console.WriteLine("Amount :" + dataTable.Rows[i]["Amount"].ToString());
}
Read Data From DataTable in C#
Insert Data into DataTable from Database
This code example provides code syntax to fetch data from the database and inserts the data into DataTable using DataAdapter.
SqlConnection sqlConnection = new SqlConnection("ConnectionString");
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "SELECT * FROM OrderMaster";
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = sqlCommand;
sqlDataAdapter.Fill(dataTable);
sqlConnection.Close();
Select Data from DataTable
// Presuming the DataTable has a column named Quantity.
string expression;
expression = "Quantity > 1";
DataRow[] foundRows;
// Use the Select method to find all rows matching the filter.
foundRows = dataTable.Select(expression);
// Print column 0 of each returned row.
for (int i = 0; i < foundRows.Length; i++)
{
Console.WriteLine(foundRows[i][0]);
}
Datatable select with multiple conditions
The DataTable.Select method supports boolean operators in the same way that you would use them in a real SQL statement:
DataRow[] results = table.Select("A = 'foo' AND B = 'bar' AND C = 'baz'");
Remove Columns from DataTable
Following example removes column and column headed from table in various way.
//Remove column by name
dataTable.Columns.Remove("ColumnName");
//Remove column by index
int colindex = 1;
dataTable.Columns.RemoveAt(colindex);
//Remove all columns
dataTable.Columns.Clear();
Remove Rows from DataTable
Following example removes row and row data from table in various way.
//Remove last column
colindex = dataTable.Columns.Count - 1;
dataTable.Columns.RemoveAt(colindex);
//Remove row by index
int rowindex = 1;
dataTable.Rows.RemoveAt(rowindex);
//Remove last row
rowindex = dataTable.Rows.Count - 1;
dataTable.Rows.RemoveAt(rowindex);
//Remove all Rows
dataTable.Rows.Clear();
Convert DataTable to Collection
The following code example describes how to convert a DataTable to generic collection. Here the datatable converted into list and array.
List<DataRow> datas = dataTable.AsEnumerable().ToList();
DataRow[] datas1 = dataTable.AsEnumerable().ToArray();
Related Articles
- Import Excel in C#
- Export DataTable To Excel in C#
- Export Table Data into XML in SQL
- Export to Excel in MVC
- Validating Excel Sheet in C#
Summary
The Read Data From DataTable in C# provided sample code to work with ADO.NET datatable, manipulate datatable, store, update and fetch data from datatable. I hope you have enjoyed it a lot.
Thanks
