ADO.NET DataTable in C#

ADO.NET DataTable in C# with Examples

In this article, I will discuss ADO.NET DataTable in C# with Examples. Please read our previous article discussing ADO.NET SqlDataAdapter in C# with Examples. At the end of this article, you will understand the following detail pointers related to C# DataTable.

  1. What is ADO.NET DataTable?
  2. Understanding Properties, Methods, and Constructors of DataTable class in C#.
  3. How Do We Create a DataTable in C#?
What is ADO.NET DataTable in C#?

The DataTable in C# is similar to the Tables in SQL. That means the DataTable will also represent the relational data in tabular form, i.e., rows and columns, and this data will be stored in memory. When we create an instance of DataTable, by default, it does not have a table schema, i.e., it does not have any columns or constraints by default. You can create the table schema by adding columns and constraints to the table. Only once you define the schema (i.e., columns and constraints) for the DataTable can you add rows to the data table. In order to use DataTable, you must have to include the System.Data namespace.

Note: The ADO.NET DataTable is a central object that can be used independently or can be used by other objects such as DataSet and the DataView. 

Signature of DataTable in C#:

The signature of the DataTable class is shown in the below image.

Signature of DataTable in C#

Constructors of ADO.NET DataTable class in C#:

The DataTable class provides the following four constructors.

Constructors of ADO.NET DataTable class

Let us discuss each of these constructors.

  1. DataTable(): This constructor is used to initialize a new instance of the System.Data.DataTable class with no arguments.
  2. DataTable(string tableName): It initializes a new instance of the System.Data.DataTable class with the specified table name. Here, the Parameters tableName is the name given to the table. If tableName is null or an empty string, a default name is given when added to the System.Data.DataTableCollection.
  3. DataTable(SerializationInfo info, StreamingContext context): This constructor is used to initialize a new instance of the System.Data.DataTable class with the System.Runtime.Serialization.SerializationInfo and the System.Runtime.Serialization.StreamingContext. Here, the parameter info specifies the data needed to serialize or deserialize an object, and the parameter context specifies the source and destination of a given serialized stream.
  4. DataTable(string tableName, string tableNamespace): It is used to initialize a new instance of the System.Data.DataTable class using the specified table name and namespace. Here, the parameter tableName specifies the name to give the table. If tableName is null or an empty string, a default name is given when added to the System.Data.DataTableCollection. The second parameter, i.e., tableNamespace, specifies the namespace for the XML representation of the data stored in the DataTable.
Properties of ADO.NET DataTable in C#:

The ADO.NET DataTable class in C# provides the following properties.

  1. Columns: It collects the columns that belong to this table.
  2. Constraints: t is used to get the collection of constraints maintained by this table.
  3. DataSet: It is used to get the Data Set to which this table belongs.
  4. DefaultView: It is used to get a customized view of the table that may include a filtered view.
  5. HasErrors: It is used to get a value indicating whether there are errors in any of the rows in the table of the DataSet.
  6. MinimumCapacity: It is used to get or set the initial starting size for this table.
  7. PrimaryKey: It is used to get or set an array of columns that function as primary keys for the data table.
  8. Rows: It is used to collect the rows that belong to this table.
  9. TableName: It is used to get or set the name of the DataTable.
Methods of C# DataTable in ADO.NET:

The C# DataTable class provides the following methods

  1. AcceptChanges(): It is used to commit all the changes made to this table.
  2. Clear(): It is used to clear the DataTable of all data.
  3. Clone(): It is used to clone the structure of the DataTable.
  4. Copy(): It is used to copy both the structure and data of the DataTable.
  5. CreateDataReader(): It returns a DataTableReader corresponding to the data within this DataTable.
  6. CreateInstance(): It is used to create a new instance of DataTable.
  7. GetRowType(): It is used to get the row type.
  8. GetSchema(): It is used to get the table schema.
  9. ImportRow(DataRow): It is used to copy a DataRow into a DataTable.
  10. Load(IDataReader): It is used to fill a DataTable with values from a data source using the supplied IDataReader.
  11. Merge(DataTable, Boolean): It merges the specified DataTable with the current DataTable.
  12. NewRow(): It creates a new DataRow with the same schema as the table.
  13. Select(): It is used to get an array of all DataRow objects.
  14. WriteXml(String): It is used to write the current contents of the DataTable as XML using the specified file.
How Do We Create a DataTable in C#?

In order to create a DataTable in C#, we first need to create an instance of the DataTable class. Then, we need to add DataColumn objects that define the data type to be held and insert DataRow objects that contain the data. Let us discuss this step by step.

Step 1: Creating DataTable instance

Please have a look at the following image. Here, we use the constructor, which takes the table name as a parameter.

How to create a DataTable in C#?

The above code will create an empty data table for which the TableName property is set to Student. Later, you can use this property to access this data table from a DataTableCollection. Once the DataTable is created, the next important step is to add the data columns and define the schema for the columns.

Step 2: Adding DataColumn and Defining Schema

A DataTable is a collection of DataColumn objects referenced by the Columns property of the data table. A DataTable object is useless until it has a schema. You can create the schema by adding DataColumn objects and setting the column constraints. As we already know from SQL’s point of view, Constraints are basically used to maintain data integrity. Let us see how to Create DataColumn and set the schema. The following image shows a data column that is created using all the available properties.

Adding DataColumn and Defining Schema in C#

The following image shows adding a Data Column using a few properties.

ADO.NET DataTable

The following image shows how to create a data column with the default properties.

What is ADO.NET DataTable?

Creating Primary Key Column in Datatable:

Like SQL, the primary key of a DataTable object also consists of a column or columns that make up a unique identity for each data row. The following image shows how to set the PrimaryKey property on the Id column of the Student DataTable object.

Creating Primary Key Column in Datatable

Creating DataRow Objects in C#:

Once you have created the DataColumns for the DataTable object, you can populate it by adding DataRow objects. You need to use the DataRow object and its properties and methods to retrieve, insert, update, and delete the values in the DataTable. The DataRowCollection represents the actual DataRow objects in the DataTable, and it has an Add method that accepts a DataRow object. The Add method is also overloaded to accept an array of objects instead of a DataRow object. The following image shows how to create and add data to the Student DataTable object.

Creating DataRow Objects in C#

You can also add a new DataRow by adding the values in the image below.

ADO.NET DataTable Example in C#

Iterating the DataTable in C#:

You can use a for each loop to loop through the rows and columns of a data table. The following image shows how to enumerate through the rows and columns of a data table.

Iterating the DataTable in C#

The complete code is given below:
using System;
using System.Data;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Creating data table instance
                DataTable dataTable = new DataTable("Student");

                //Add the DataColumn using all properties
                DataColumn Id = new DataColumn("ID");
                Id.DataType = typeof(int);
                Id.Unique = true;
                Id.AllowDBNull = false;
                Id.Caption = "Student ID";
                dataTable.Columns.Add(Id);
                
                //Add the DataColumn few properties
                DataColumn Name = new DataColumn("Name");
                Name.MaxLength = 50;
                Name.AllowDBNull = false;
                dataTable.Columns.Add(Name);
                
                //Add the DataColumn using defaults
                DataColumn Email = new DataColumn("Email");
                dataTable.Columns.Add(Email);
                
                //Setting the Primary Key
                dataTable.PrimaryKey = new DataColumn[] { Id };
                
                //Add New DataRow by creating the DataRow object
                DataRow row1 = dataTable.NewRow();
                row1["Id"] = 101;
                row1["Name"] = "Anurag";
                row1["Email"] = "Anurag@dotnettutorials.net";
                dataTable.Rows.Add(row1);

                //Adding new DataRow by simply adding the values
                dataTable.Rows.Add(102, "Mohanty", "Mohanty@dotnettutorials.net");

                foreach (DataRow row in dataTable.Rows)
                {
                    Console.WriteLine(row["Id"] + ",  " + row["Name"] + ",  " + row["Email"]);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }

            Console.ReadKey();
        }
    }
}
Output:

DataTable in C#

DataColumn Properties

In the above example, we have used some of the properties of DataColumn. Following is the list of all available DataColumn classes.

  1. AllowDBNull: This property is used to get or set a value indicating whether the column will accept null values.
  2. Autoincrement: This property is used when you want to increment the column values automatically.
  3. AutoincrementSeed: This property is used to get or set the starting value for the auto-incremented column.
  4. AutoincrementStep: This property is used to get or set the increment used by a column with its Autoincrement property set to true.
  5. Caption: his property is used to get or set the caption for the column.
  6. ColumnName: This property is used to get or set the name of the column.
  7. Expression: This property is used to get or set the expression to filter rows, calculate the values in a column, or create an aggregate column.
  8. MaxLength: This property is used to get or set the maximum length of a text column.
  9. Unique: This property is used to get or set a value that indicates whether the values in each row of the column must be unique.

In our example, we discussed most of the above properties. Let us understand one important property, i.e., Autoincrement.

Example to Understand Autoincrement Column in C#:

In the following example, we set the Id column of the data table as auto-increment.

using System;
using System.Data;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Creating data table instance
                DataTable dataTable = new DataTable("Student");
                
                DataColumn Id = new DataColumn
                {
                    ColumnName = "Id",
                    DataType = System.Type.GetType("System.Int32"),
                    AutoIncrement = true,
                    AutoIncrementSeed = 1000,
                    AutoIncrementStep = 10
                };

                dataTable.Columns.Add(Id);
                

                //Add the DataColumn few properties
                DataColumn Name = new DataColumn("Name");
                Name.MaxLength = 50;
                Name.AllowDBNull = false;
                dataTable.Columns.Add(Name);
                
                //Add the DataColumn using defaults
                DataColumn Email = new DataColumn("Email");
                dataTable.Columns.Add(Email);
                
                //Add New DataRow by creating the DataRow object
                DataRow row1 = dataTable.NewRow();
                
                row1["Name"] = "Anurag";
                row1["Email"] = "Anurag@dotnettutorials.net";
                dataTable.Rows.Add(row1);

               
                //Adding new DataRow by simply adding the values
                //Supply null for auto increment column
                dataTable.Rows.Add(null, "Mohanty", "Mohanty@dotnettutorials.net");

                foreach (DataRow row in dataTable.Rows)
                {
                    Console.WriteLine(row["Id"] + ",  " + row["Name"] + ",  " + row["Email"]);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }

            Console.ReadKey();
        }
    }
}
Output:

Example to understand Autoincrement Column

When Should We Use ADO.NET DataTable in C#?

DataTable from ADO.NET is a versatile tool that serves as an in-memory representation of tabular data. It’s useful in various scenarios where you need to work with data in a structured format. Here are some situations where you might consider using DataTable:

  1. Disconnected Data Manipulation: When you need to work with data in a disconnected manner (i.e., without a continuous connection to the database), DataTable provides a way to load and manipulate data locally before synchronizing changes back to the database.
  2. Data Binding: If you’re building data-driven user interfaces, you can use DataTable as a data source for controls like grids, lists, and combo boxes. This allows you to display and interact with data in a user-friendly way.
  3. Complex Data Manipulation: In scenarios where you need to perform data manipulation operations like sorting, filtering, and grouping, DataTable provides built-in methods to achieve these tasks.
  4. Interchange Format: DataTable can serve as a container for data during data exchange between different layers of an application or even between different applications. You can serialize it to formats like XML or JSON for this purpose.
  5. Local Caching: If your application frequently accesses the same data, you can use DataTable to store a local copy of the data, reducing the need for frequent database queries.
  6. Small to Moderate Data Sizes: DataTable is suitable for managing data of moderate size. It’s not optimized for very large datasets, where other techniques like streaming and pagination might be more appropriate.
  7. Data Transformation: You can use DataTable to transform data from one format to another before sending it to another layer of your application or storing it in a different format.
  8. Batch Updates: If your application requires updating multiple records in a single transaction, you can accumulate changes in a DataTable and then perform a batch update to the database.
  9. Offline Access: In cases where users might need to work with data while disconnected from the network (e.g., in mobile applications), DataTable can store the required data for local usage.
  10. Data Validation: You can apply constraints and validation rules to columns within a Data Table to ensure data integrity before persisting changes to the database.

However, it’s important to note that while DataTable offers these advantages, it’s not always the best choice for every scenario:

  • For large datasets, a more memory-efficient approach like streaming with SqlDataReader or pagination might be better.
  • For read-only scenarios, where you’re primarily retrieving data, using SqlDataReader or similar approaches can be more efficient.
  • If your application is built around modern data-binding frameworks, they might offer more advanced data manipulation capabilities.

So, use DataTable when working with data in a structured tabular format, perform various data manipulation operations, create data-bound user interfaces, and manage changes in a disconnected environment. It’s a versatile tool that fits well in scenarios where a lightweight, in-memory data representation is needed.

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

Leave a Reply

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