Loading

ADO.NET

What is DataTable Methods in C# ?. The Complete ADO.NET Developer Course 2023 [Videos].

By admin / 11-Feb-2022 / in Blog, ADO.NET / 423 Comments / 18 Likes / Share This Post

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 method 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 below SQL script to create a database called StudentDB, 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



GO

Example: Using DataTable in C#

We need to fetch all the data from the student table and then need to 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.
" + 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 not only the DataTable data but also 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.
" + 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.
" + 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.
" + 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.
" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

DataTable Methods in C#

See All

Comments (423 Comments)

Submit Your Comment

See All Posts

Related Posts

What is ADO.NET?

In this article, I am going to give you a brief introduction to ADO.NET. As a .NET developer, you should be aware of ADO.NET. At the end of this article, you will understand the following pointers in detail.

11-Feb-2022 /18 /423

How to using ADO.NET with SQL Server?

In this article, I am going to discuss ADO.NET using SQL Server Database with Examples. Please read our previous article where we discussed the Architecture of ADO.NET. At the end of this article, you will understand how to connect with SQL Server database using ADO.NET. I hope you have SQL Server installed on your machine. We are using SQL Server Management Studio (SSMS) Tool to interact with SQL Server.

11-Feb-2022 /18 /423

What is ADO.NET SqlConnection Class in C#?

In this article, I am going to discuss the ADO.NET SqlConnection Class in C# with Examples. Please read our previous article where we discussed ADO.NET using SQL Server. As part of this article, we are going to discuss the following pointers in detail.

11-Feb-2022 /18 /423