DataTable Methods in C#

DataTable Methods in C#

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 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 below SQL script to create a database called StudentDB, a table called Student with the required test 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 need to store the data in a data table and finally using a foreach 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 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 copies 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 data table. The following example shows, the use of both clone and copy method.

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 occurs when you call the AcceptChanges method. If you want to rollback, then you need to use the RejectChanges method which will roll back to the previous state.The RejectChanges method will copies 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. Here, in this article, I try to explain important DataTable Methods in C# with examples. I hope this article will helps you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *