ADO.NET DataTable

ADO.NET DataTable

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 with Examples. At the end of this article, you are going to understand the following pointers in detail which are related 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#?

Like a table in SQL, the DataTable is also going to represents the relational data in tabular form and this data is going to be store in memory. When you an create 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 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 which can be use 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 is used initializes 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 initializes 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 initializes 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 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 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, and 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 look at the following image. Here, we are using 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 Data Table is created, the next important step is to add data column and define the schema for the columns.

Step2: Adding DataColumn and Defining Schema

A DataTable is actually a collection of DataColumn objects which 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 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.

Adding DataColumn and Defining Schema in C#

The following image shows adding Data Column using few properties.

ADO.NET DataTable

The following image shows creating 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 is 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 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 it’s 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 a data into the Student DataTable object.

Creating DataRow Objects in C#

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

ADO.NET DataTable Example in C#

Iterating the DataTable in C#:

You can use a foreach loop to loop through the rows and columns of a data table. The following images 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 use some of the properties of DataColumn. Following is the list of all available of DataColumn class.

  1. AllowDBNull: This property is used to gets or sets a value that indicates whether the column will accept null values or not.
  2. Autoincrement: This property is used when you want to increment the column values automatically.
  3. AutoincrementSeed: This property is used gets or sets the starting value for the auto incremented column.
  4. AutoincrementStep: This property is used gets or sets the increment used by a column with its Autoincrement property set to true.
  5. Caption: his property is used gets or sets the caption for the column.
  6. ColumnName: This property is used gets or sets the name of the column.
  7. Expression: This property is used gets or sets the expression used to filter rows, calculate the values in a column, or create an aggregate column.
  8. MaxLength: This property is used gets or sets the maximum length of a text column.
  9. Unique: This property is used gets or sets 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 the following example, we set 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

In the next article, I am going to discuss important Methods of DataTable class in detail. Here, in this article, I try to explain the ADO.NET DataTable in C# with examples. I hope this C# DataTable 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 *