ADO.NET DataTable in C# with Examples

In this article, I am going to discuss ADO.NET DataTable in C# with Examples. Please read our previous article, where we discussed ADO.NET SqlDataAdapter in C# with Examples. At the end of this article, you are going to understand the following pointers in detail, which are related to C# DataTable.

1. What is ADO.NET DataTable?
2. Understanding Properties, Methods, and Constructors of DataTable class in C#.
3. How to 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 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. Once you define the schema (i.e., columns and constraints) for the DataTable, then only you can 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.

Constructors of ADO.NET DataTable class in C#:

The DataTable class provides the following four constructors.

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 is used to initialize a new instance of the System.Data.DataTable class with the specified table name. Here, the Parameters tableName is 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.
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 is used to get the collection of 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 DataSet 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 get the collection of 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 is used to return 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 schema of the table.
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 is used to merge the specified DataTable with the current DataTable.
12. NewRow(): It is used to create 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 to create a DataTable in C#?

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

Step1: Creating DataTable instance

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

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.

Step2: Adding DataColumn and Defining Schema

A DataTable is actually a collection of DataColumn objects that is 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 constraints of columns. 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 creating a data column using all the available properties.

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

The following image shows creating a Data Column with the default properties.

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 DataRow Objects in C#:

Once you created the DataColumns for the DataTable object, then you can populate the DataTable object 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.

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

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.

The complete code is given below:

Output:

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 used 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.

Output:

When to 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 you need to work 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 representation of data is needed.

Summary of ADO.NET DataTable Class in C#:

DataTable is a fundamental component of the ADO.NET framework provided by Microsoft. It represents an in-memory representation of tabular data, similar to a database table. DataTable is often used in conjunction with other ADO.NET components like SqlDataAdapter to create a disconnected data model, allowing you to work with and manipulate data without a continuous connection to the database.

Here are the key features and use cases of DataTable:

1. Tabular Representation: DataTable is designed to represent data in a tabular format, with rows and columns similar to a relational database table.
2. Columns and Data Types: You define the columns of the DataTable with their names and data types. Each column can represent a different field of your data.
3. Rows: DataTable contains a collection of rows, where each row corresponds to a record in your data. Each row has values in its columns, forming a complete dataset.
4. Data Manipulation: You can add, modify, and delete rows in a DataTable. This allows you to work with data locally before updating changes back to the database.
5. Data Binding: DataTable supports data binding, enabling you to bind controls in your user interface directly to the data stored in the DataTable.
6. Data Retrieval: You can manually populate a DataTable by adding rows and assigning values, or you can use components like SqlDataAdapter to fill it with data from a database.
7. Data Validation: You can enforce constraints, relationships, and data validation rules on a DataTable, ensuring data integrity.
8. Sorting and Filtering: DataTable provides methods to sort and filter data within the table, allowing you to work with subsets of the data.
9. Serialization: DataTable can be serialized to various formats, making it suitable for data interchange between different layers of an application.
10. Data Export: You can export the data stored in a DataTable to different formats like XML, JSON, CSV, etc.

In more complex scenarios, you might use DataTable in combination with other ADO.NET components like SqlDataAdapter to fill it with data from a database, perform updates, and synchronize changes. Additionally, DataTable is often used in data-driven applications, reporting systems, and scenarios where you need to manipulate and present tabular data.

In the next article, I am going to 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.