DataTable is essential part ADO.Net . DataTables is created in
memory and hold data in tabular format. DataTable comes under the
namespace System.Data. DataSet content one or multiple DataTable .
DataSet contain table and data in XML format. Here is some commonly
used attribute of a DataTable.
Datatable play an important role in ado.Net framework. DataTable can be created programmatically and data and data column can be added deleted programmatically . DataSet content one or multiple table. DataTable allow us select filter data, return as data view. DataTable is faster in performance and it is in memory representation of a data.
1) Dynamically add row and column to Datatable
2) Delete row and column from the Datatable
John1|500
John2|600
John3|700
-----------------After Delete Row-----------------
John2|600
John3|700
-----------------After Delete Column RollNumber-----------------
John2|
John3|
3) Filter criteria on the Datatable
-
Constructor
-
DataTable : Create an instance of the DataTable with new operator.
-
DataTable (string tableName) : Create a new instance of a DataTable with specific name.
-
Property
-
Columns : Get the collection of columns in a DataTable.
-
DataSet : Get the parent DataSet of a table.
-
DefaultView: Give the view of a specific query /filter condition output.
-
HasError : Get indication that a DataTable has error or not.
-
Rows : Get the collection of rows.
-
TableName : Get hot Set the name of the table.
-
Method
-
AcceptChanges : Commint all changes since the DataTable has last loaded.
-
Clear : Clear all rows and column data.
-
Copy : Copy a DataTable to another DataTable .
-
Dispose : Release all resource hold by the DataTable .
-
Load : Fill DataTable with value from Data sources.
-
Merge : Merge a DataTable with another table.
-
NewRow: Create a new row with same structure of the table row, that can hold data.
-
ReadXML : Read XML schema with data and insert into the data table.
-
RejectChanges : Rollback all changes that have been made since the DataTable last loaded.
-
Select : Return Collection of data row when filter criteria apply.
-
Write XML : Write the current DataTable to XML File.
Datatable play an important role in ado.Net framework. DataTable can be created programmatically and data and data column can be added deleted programmatically . DataSet content one or multiple table. DataTable allow us select filter data, return as data view. DataTable is faster in performance and it is in memory representation of a data.
1) Dynamically add row and column to Datatable
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Data.Sql;
using
System.Data.OleDb;
namespace
ConsoleApplication1
{
class
Program
{
static
void
Main(string[]
args)
{
DataSet
ds
= new
DataSet();
DataTable
dt
= new
DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("RollNumber");
DataRow
nr1
= dt.NewRow();
nr1["Name"]
= "John1";
nr1["RollNumber"]
= 500;
dt.Rows.Add(nr1);
DataRow
nr2
= dt.NewRow();
nr2["Name"]
= "John2";
nr2["RollNumber"]
= 600;
dt.Rows.Add(nr2);
DataRow
nr3
= dt.NewRow();
nr3["Name"]
= "John3";
nr3["RollNumber"]
= 700;
dt.Rows.Add(nr3);
ds.Merge(dt);
DataTable
dt2
= new
DataTable();
dt2.Columns.Add("Address");
dt2.Columns.Add("ZIP");
DataRow
nr4
= dt2.NewRow();
nr4["Address"]
= "3,Kolkata";
nr4["ZIP"]
= 500;
dt2.Rows.Add(nr4);
DataRow
nr5
= dt2.NewRow();
nr5["Address"]
= "4,Mumbai";
nr5["ZIP"]
= 600;
dt2.Rows.Add(nr5);
DataRow
nr6
= dt2.NewRow();
nr6["Address"]
= "5,Bombay";
nr6["ZIP"]
= 700;
dt2.Rows.Add(nr6);
ds.Merge(dt2);
for
(int
i
= 0; i < dt.Rows.Count; i++)
{
for
(int
l
= 0; l < dt.Columns.Count; l++)
{
Console.Write(dt.Rows[i][l].ToString());
if
(l
== 0)
Console.Write("|");
if
(l
== 1)
Console.WriteLine("");
}
}
Console.ReadKey();
}
}
}
Output :
John1|500
John2|600
John3|700
John2|600
John3|700
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Data.Sql;
using
System.Data.OleDb;
namespace
ConsoleApplication1
{
class
Program
{
static
void
Main(string[]
args)
{
DataTable
dt
= new
DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("RollNumber");
DataRow
nr1
= dt.NewRow();
nr1["Name"]
= "John1";
nr1["RollNumber"]
= 500;
dt.Rows.Add(nr1);
DataRow
nr2
= dt.NewRow();
nr2["Name"]
= "John2";
nr2["RollNumber"]
= 600;
dt.Rows.Add(nr2);
DataRow
nr3
= dt.NewRow();
nr3["Name"]
= "John3";
nr3["RollNumber"]
= 700;
dt.Rows.Add(nr3);
Console.WriteLine("-----------------Original
Table Data-----------------");
for
(int
i
= 0; i < dt.Rows.Count; i++)
{
for
(int
l
= 0; l < dt.Columns.Count; l++)
{
Console.Write(dt.Rows[i][l].ToString());
if
(l==0)
Console.Write("|");
if
(l==1)
Console.WriteLine("");
}
}
//Delete
a row
dt.Rows[0].Delete();
Console.WriteLine("-----------------After
Delete Row-----------------");
for
(int
i
= 0; i < dt.Rows.Count; i++)
{
for
(int
l
= 0; l < dt.Columns.Count; l++)
{
Console.Write(dt.Rows[i][l].ToString());
if
(l
== 0)
Console.Write("|");
if
(l
== 1)
Console.WriteLine("");
}
}
dt.Columns.Remove("RollNumber");
Console.WriteLine("-----------------After
Delete Column RollNumber-----------------");
for
(int
i
= 0; i < dt.Rows.Count; i++)
{
for
(int
l
= 0; l < dt.Columns.Count; l++)
{
Console.Write(dt.Rows[i][l].ToString());
if
(l
== 0)
Console.Write("|");
Console.WriteLine("");
}
}
Console.ReadKey();
}
}
}
OutPut :
-----------------Original Table
Data-----------------John1|500
John2|600
John3|700
-----------------After Delete Row-----------------
John2|600
John3|700
-----------------After Delete Column RollNumber-----------------
John2|
John3|
3) Filter criteria on the Datatable
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Data.Sql;
using
System.Data.OleDb;
namespace
ConsoleApplication1
{
class
Program
{
static
void
Main(string[]
args)
{
DataTable
dt
= new
DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("RollNumber");
DataRow
nr1
= dt.NewRow();
nr1["Name"]
= "John1";
nr1["RollNumber"]
= 500;
dt.Rows.Add(nr1);
DataRow
nr2
= dt.NewRow();
nr2["Name"]
= "John2";
nr2["RollNumber"]
= 600;
dt.Rows.Add(nr2);
DataRow
nr3
= dt.NewRow();
nr3["Name"]
= "John3";
nr3["RollNumber"]
= 700;
dt.Rows.Add(nr3);
Console.WriteLine("-----------------Original
Table Data-----------------");
for
(int
i
= 0; i < dt.Rows.Count; i++)
{
for
(int
l
= 0; l < dt.Columns.Count; l++)
{
Console.Write(dt.Rows[i][l].ToString());
if
(l
== 0)
Console.Write("|");
if
(l
== 1)
Console.WriteLine("");
}
}
Console.WriteLine("-------Example
of equal filter criteria :RollNumber=600----");
DataRow[]
drs= dt.Select("RollNumber=600");
foreach
(DataRow
d
in
drs)
{
Console.Write(d[0]+"|"+d[1]);
}
Console.WriteLine("");
Console.WriteLine("-------Example
of Greater than filter criteria :RollNumber>600----");
DataRow[]
drs1 = dt.Select("RollNumber>600");
foreach
(DataRow
d
in
drs1)
{
Console.Write(d[0]
+ "|"
+
d[1]);
}
Console.WriteLine("");
Console.WriteLine("-------Example
of Range filter criteria :RollNumber>500 AND RollNumber);
DataRow[]
drs2 = dt.Select("RollNumber>500
AND RollNumber);
foreach
(DataRow
d
in
drs2)
{
Console.Write(d[0]
+ "|"
+
d[1]);
}
Console.WriteLine("");
Console.WriteLine("-------Example
of Range filter criteria :Name like 'John3'----");
DataRow[]
drs3 = dt.Select("Name
like '%John3%'");
foreach
(DataRow
d
in
drs3)
{
Console.Write(d[0]
+ "|"
+
d[1]);
}
Console.ReadKey();
}
}
}
Output :
-----------------Original
Table
Data-----------------
John1|500
John2|600
John3|700
-------Example of equal filter criteria :RollNumber=600----
John2|600
-------Example of Greater than filter criteria :RollNumber>600----
John3|700
-------Example of Range filter criteria :RollNumber>500 AND RollNumberJohn2|600
-------Example of Range filter criteria :Name like 'John3'----
John3|700
John1|500
John2|600
John3|700
-------Example of equal filter criteria :RollNumber=600----
John2|600
-------Example of Greater than filter criteria :RollNumber>600----
John3|700
-------Example of Range filter criteria :RollNumber>500 AND RollNumberJohn2|600
-------Example of Range filter criteria :Name like 'John3'----
John3|700
