DataTable Methods in C# with Examples
Posted by Superadmin on November 15 2023 10:58:43

DataTable Methods in C# with Examples

In this article, I am going to discuss some important DataTable Methods in C# with Examples. Please read our previous article, where we discussed ADO.NET DataTable with Examples. At the end of this article, you will understand the Copy, Clone, Remove and Delete methods of the DataTable object. 

 

Example to understand DataTable Methods in C# using SQL Server:

We are going to use the following student table to understand the SqlDataAdapter object.

 

Example to understand DataTable Methods in C# using SQL Server

Please use the SQL script below to create a database called StudentDB and a table called Student with the required sample data.

 

CREATE DATABASE StudentDB;
 GO 
 
 USE StudentDB;
 GO 
 
 CREATE TABLE Student(
Id INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(50),
Mobile VARCHAR(50)
)
 GO 
 
 INSERT INTO Student VALUES (101, 'Anurag', 'Anurag@dotnettutorial.net', '1234567890')
 INSERT INTO Student VALUES (102, 'Priyanka', 'Priyanka@dotnettutorial.net', '2233445566')
 INSERT INTO Student VALUES (103, 'Preety', 'Preety@dotnettutorial.net', '6655443322')
 INSERT INTO Student VALUES (104, 'Sambit', 'Sambit@dotnettutorial.net', '9876543210')
 GO
Example: Using DataTable in C#

We need to fetch all the data from the student table and then store the data in a data table, and finally, using a for each loop to display the data in the console. The following code exactly does the same thing. In the following example, we are creating a data table and filling the data table using the Fill method of the SqlDataAdapter object.

 

using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 
 namespace AdoNetConsoleApplication
 { 
class Program
{ 
static void Main(string[] args) 
{ 
try 
{ 
string ConnectionString = "data source=.; database=StudentDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString)) 
{ 
SqlDataAdapter da = new SqlDataAdapter("select * from student", connection);
DataTable dt = new DataTable();
da.Fill(dt);
foreach (DataRow row in dt.Rows) 
{ 
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
} 
} 
} 
catch (Exception e) 
{ 
Console.WriteLine("OOPs, something went wrong.\n" + e);
} 
 
Console.ReadKey();
} 
} 
 }
Output:

DataTable in C#

Copying and Cloning the DataTable in C#:

If you want to create a full copy of a data table, then you need to use the Copy method of the DataTable object, which will copy the DataTable data and its schema. But if you want to copy the data table schema without data, then you need to use the Clone method of the data table. The following example shows the use of both the clone and copy methods.

  

using System; 
 using System.Data; 
 using System.Data.SqlClient; 
 
 namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = "data source=.; database=StudentDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter("select * from student", connection);
DataTable originalDataTable = new DataTable();
da.Fill(originalDataTable);
Console.WriteLine("Original Data Table : originalDataTable");
foreach (DataRow row in originalDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
Console.WriteLine();
Console.WriteLine("Copy Data Table : copyDataTable");
DataTable copyDataTable = originalDataTable.Copy();
if (copyDataTable != null)
{
foreach (DataRow row in copyDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
}
Console.WriteLine();
Console.WriteLine("Clone Data Table : cloneDataTable");
DataTable cloneDataTable = originalDataTable.Clone();
if (cloneDataTable.Rows.Count > 0)
{
foreach (DataRow row in cloneDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
}
else
{
Console.WriteLine("cloneDataTable is Empty");
Console.WriteLine("Adding Data to cloneDataTable");
cloneDataTable.Rows.Add(101, "Test1", "Test1@dotnettutorial.net", "1234567890");
cloneDataTable.Rows.Add(101, "Test2", "Test1@dotnettutorial.net", "1234567890");
foreach (DataRow row in cloneDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
}
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n" + e);
}
Console.ReadKey();
}
}
}
Output:

Copying and Cloning the DataTable in C#

Deleting Data Row from a DataTable in C#:

You can delete a DataRow from the DataRowCollection by calling the Remove method of the DataRowCollection, or by calling the Delete method of the DataRow object.

The Remove method will remove the row from the collection, whereas the Delete method marks the DataRow for removal. The actual removal will occur when you call the AcceptChanges method. If you want to roll back, then you need to use the RejectChanges method, which will roll back to the previous state. The RejectChanges method will copy the Original data row version to the Current data row version.

Delete Method Example:
using System;
using System.Data;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = "data source=.; database=StudentDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter("select * from student", connection);
DataTable originalDataTable = new DataTable();
da.Fill(originalDataTable);
Console.WriteLine("Before Deletion");
foreach (DataRow row in originalDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
Console.WriteLine();
foreach (DataRow row in originalDataTable.Rows)
{
if (Convert.ToInt32(row["Id"]) == 101)
{
row.Delete();
Console.WriteLine("Row with Id 101 Deleted");
}
}
originalDataTable.AcceptChanges();
Console.WriteLine();
Console.WriteLine("After Deletion");
foreach (DataRow row in originalDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n" + e);
}
Console.ReadKey();
}
}
}
Output:

Deleting Data Row from a DataTable in C# using Delete Method

Remove method example:
using System;
using System.Data;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = "data source=.; database=StudentDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter("select * from student", connection);
DataTable originalDataTable = new DataTable();
da.Fill(originalDataTable);
Console.WriteLine("Before Deletion");
foreach (DataRow row in originalDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
Console.WriteLine();
foreach (DataRow row in originalDataTable.Select())
{
if (Convert.ToInt32(row["Id"]) == 101)
{
originalDataTable.Rows.Remove(row);
Console.WriteLine("Row with Id 101 Deleted");
}
}
Console.WriteLine();
Console.WriteLine("After Deletion");
foreach (DataRow row in originalDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n" + e);
}
Console.ReadKey();
}
}
}
Output:

Deleting Data Row from a DataTable in C# using Remove Method

RejectChanges Method example:
using System;
using System.Data;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
try
{
string ConnectionString = "data source=.; database=StudentDB; integrated security=SSPI";
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlDataAdapter da = new SqlDataAdapter("select * from student", connection);
DataTable originalDataTable = new DataTable();
da.Fill(originalDataTable);
Console.WriteLine("Before Deletion");
foreach (DataRow row in originalDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
Console.WriteLine();
foreach (DataRow row in originalDataTable.Rows)
{
if (Convert.ToInt32(row["Id"]) == 101)
{
row.Delete();
Console.WriteLine("Row with Id 101 Deleted");
}
}
//Rollbacking the Data
originalDataTable.RejectChanges();
Console.WriteLine();
Console.WriteLine("Rollbacking the Changes");
foreach (DataRow row in originalDataTable.Rows)
{
Console.WriteLine(row["Name"] + ", " + row["Email"] + ", " + row["Mobile"]);
}
}
}
catch (Exception e)
{
Console.WriteLine("OOPs, something went wrong.\n" + e);
}
Console.ReadKey();
}
}
}
Output:

DataTable Methods in C#

In the next article, I am going to discuss ADO.NET DataSet in detail. In this article, I try to explain important DataTable Methods in C# with examples. I hope this DataTable method in C# with Examples article will help you with your needs. I would like to have your feedback. Please post your feedback, questions, or comments about this DataTable Methods in C# with Examples article.

 

New layer...

 

N