C# DataTable

Creating a DataTable in “C# DataTable”

• We first need to create an instance of a “DataTable class” for creating a data table in “C# DataTable”.
• Then we will add DataColumn objects that define the type of data we will insert.
• And then add DataRow objects which contain the data.

Example: To create a DataTable named “myOrder”

//Creating a DataTable named as "myOrder"

DataTable order = new DataTable ("myOrder");

Now let’s talk about some commonly used DataTable class properties in “C# DataTable”.

Data Table Class Properties

Let’s discuss some commonly used DataTable methods.

Learn From The Best Mentors in the Industry!

Automation Testing Masters ProgramExplore Program

Data Table Class Methods

There are many other “C# DataTable” properties and methods, but the properties and methods mentioned above are the most commonly used ones.

Examples

We will be discussing and writing a code below. In this code, we will create two data table objects, “purchaser” and purchaser’s “orders”, and set or establish a relationship between them.

Then, we will add our data to DataTable and bind the DataTable to DataGridView control, enabling it to load and display the data in the control. 

Learn 15+ In-Demand Tools and Skills!

Automation Testing Masters ProgramExplore Program

Now let us discuss the steps that we are going to follow.

Steps:

• Open Visual Studio’s latest version to create windows form application using C#.
• Add a DataGridView control to our form and resize our control as you see fit.
• After calling the initializeComponent() on the Form’s constructor, call following three methods called CreatePurchasersTable(), CreateOrdersTable, and BindData() method.
• The listing-1 shown below represents the form constructor. 

Listing-1: Form's constructor calling CreatePurchasersTable, CreateOrdersTable, and BindData.

public Form1()

{

    InitializeComponent();

    CreatePurchasersTable();

    CreateOrdersTable();

    BindData();

}

You also need to add a dataset variable dSet to the beginning of our form. See code below:

public class Form1 : System.Windows.Forms.Form

{

    private DataSet dtaSet;

}

Now we are going to create listing-2. Here, the method CreatePurchsersTable() will create a Purchasers data table with id, name, and address and add the three data rows. The CreateOrdersTable() will create the Orders data table with all the order id, cost id, name, and description columns and add data, respectively.

Then, using the DataBind method, we will establish a relationship between the purchasers and the orders and bind the data to a DataGrid control using DataSet. The following listing method (listing-2) will implement all three CreatePurchasersTable, CreateOrdersTable, and BindData methods.

Listing-2: Purchasers - Orders relationship

// Creating the purchasers table

private void CreatePurchasersTable()

{

    // Creating a new DataTable.

    DataTable custTable = new DataTable("purchasers");

    DataColumn dtaColumn;

    DataRow myDataRow;

    // Create id column

    dtaColumn = new DataColumn();

    dtaColumn.DataType = typeof(Int32);

    dtaColumn.ColumnName = "id";

    dtaColumn.Caption = "Cust ID";

    dtaColumn.ReadOnly = false;

    dtaColumn.Unique = true;

    // Add column to the DataColumnCollection.

    custTable.Columns.Add(dtaColumn);

   // Create Name column.

    dtaColumn = new DataColumn();

    dtaColumn.DataType = typeof(String);

    dtaColumn.ColumnName = "Name";

    dtaColumn.Caption = "Cust Name";

    dtaColumn.AutoIncrement = false;

    dtaColumn.ReadOnly = false;

    dtaColumn.Unique = false;

    /// Add column to the DataColumnCollection.

    custTable.Columns.Add(dtaColumn); 

    // Create Address column.

    dtaColumn = new DataColumn();

    dtaColumn.DataType = typeof(String);

    dtaColumn.ColumnName = "Address";

    dtaColumn.Caption = "Address";

    dtaColumn.ReadOnly = false;

    dtaColumn.Unique = false;

    // Add column to the DataColumnCollection.

    custTable.Columns.Add(dtaColumn); 

    // Make id column the primary key column.

    DataColumn[] PrimaryKeyColumns = new DataColumn[1];

    PrimaryKeyColumns[0] = custTable.Columns["id"];

    custTable.PrimaryKey = PrimaryKeyColumns;

    // Create a new DataSet

    dtaSet = new DataSet();

    // Add custTable to the DataSet.

    dtaSet.Tables.Add(custTable);

    // Add data rows to the custTable using NewRow method

    // I add three Purchasers with their addresses, names and ids

    myDataRow = custTable.NewRow();

    myDataRow["id"] = 1001;

    myDataRow["Name"] = "Mum Paul";

    myDataRow["Address"] = "Shukhobrishti, Shapoorji Pallonji, Kolkata";

    custTable.Rows.Add(myDataRow);

    myDataRow = custTable.NewRow();

    myDataRow["id"] = 1002;

    myDataRow["name"] = "Rocky Jaiswal";

    myDataRow["Address"] = " Goregaon East, Mumbai";

    custTable.Rows.Add(myDataRow);

    myDataRow = custTable.NewRow();

    myDataRow["id"] = 1003;

    myDataRow["Name"] = "Shuvam Banerjee";

    myDataRow["Address"] = "Saltlake, Kolkata";

    custTable.Rows.Add(myDataRow);

} 

// Create Orders table

private void CreateOrdersTable()

{

    // Create a DataTable

    DataTable ordersTable = new DataTable("Orders");

    DataColumn dtaColumn;

    DataRow dtRow; 

    // Create OrderId column

    dtaColumn = new DataColumn();

    dtaColumn.DataType = Type.GetType("System.Int32");

    dtaColumn.ColumnName = "OrderId";

    dtaColumn.AutoIncrement = true;

    dtaColumn.Caption = "Order ID";

    dtaColumn.ReadOnly = true;

    dtaColumn.Unique = true;

    ordersTable.Columns.Add(dtaColumn);

    // Create Name column.

    dtaColumn = new DataColumn();

    dtaColumn.DataType = Type.GetType("System.String");

    dtaColumn.ColumnName = "Name";

    dtaColumn.Caption = "Item Name";

    ordersTable.Columns.Add(dtaColumn);

    // Create CustId column which Reprence Cust Id from

    // The cust Table

    dtaColumn = new DataColumn();

    dtaColumn.DataType = Type.GetType("System.Int32");

    dtaColumn.ColumnName = "CustId";

    dtaColumn.AutoIncrement = false;

    dtaColumn.Caption = "CustId";

    dtaColumn.ReadOnly = false;

    dtaColumn.Unique = false;

    ordersTable.Columns.Add(dtaColumn); 

    // Create Description column.

    dtaColumn = new DataColumn();

    dtaColumn.DataType = Type.GetType("System.String");

    dtaColumn.ColumnName = "Description";

    dtaColumn.Caption = "DescriptionName";

    ordersTable.Columns.Add(dtaColumn); 

    // Add ordersTable to DataSet

    dtaSet.Tables.Add(ordersTable);

    // ADD two rows to the purchaser Id 1001

    dtRow = ordersTable.NewRow();

    dtRow["OrderId"] = 0;

    dtRow["Name"] = "Python Book";

    dtRow["custId"] = 1001;

    dtRow["Description"] = "Beginner to advance level";

    ordersTable.Rows.Add(dtRow);

    dtRow = ordersTable.NewRow();

    dtRow["OrderId"] = 1;

    dtRow["Name"] = "Java Book";

    dtRow["custId"] = 1001;

    dtRow["description"] = "JAVA oops";

    ordersTable.Rows.Add(dtRow);

    // Add two rows to purchaser id 1002

    dtRow = ordersTable.NewRow();

    dtRow["OrderId"] = 2;

    dtRow["Name"] = "Data Quest";

    dtRow["Description"] = "Monthly magazine";

    dtRow["CustId"] = 1002;

    ordersTable.Rows.Add(dtRow);

    dtRow = ordersTable.NewRow();

    dtRow["OrderId"] = 3;

    dtRow["Name"] = "PC Magazine";

    dtRow["Description"] = "Monthly Magazine";

    dtRow["CustId"] = 1003;

    ordersTable.Rows.Add(dtRow);

    // Add two rows to Customer id 1003

    dtRow = ordersTable.NewRow();

    dtRow["OrderId"] = 4;

    dtRow["Name"] = "PCMagazine";

    dtRow["Description"] = "Monthly Magazine";

    dtRow["custId"] = 1003;

    ordersTable.Rows.Add(dtRow);

    dtRow = ordersTable.NewRow();

    dtRow["OrderId"] = 5;

    dtRow["Name"] = "Cloud computation Book";

    dtRow["CustId"] = 1003;

    dtRow["Description"] = "2 Day Air ";

    ordersTable.Rows.Add(dtRow);

}

// This method creates a customer order relationship and data tables

// Also displays Purchasers table data in a DataGridView control

private void BindData()

{

    DataRelation dtRelation;

    DataColumn custCol = dtaSet.Tables["Purchasers"].Columns["id"];

    DataColumn orderCol = dtaSet.Tables["orders"].Columns["custId"];

    dtRelation = new DataRelation("CustOrderRelation ", custCol, orderCol);

    dtaSet.Tables["orders"].ParentRelations.Add(dtRelation);

    // Create a BindingSource

    BindingSource bs = new BindingSource();

    bs.DataSource = dtaSet.Tables["Purchasers"];

    // Bind data to DataGridView.DataSource

    dataGridView1.DataSource = bs;

}

From the code above, we can see that for creating the Purchasers table,

• First, we create the Purchasers table using the DataTable
• Then we insert their id, name, and address as columns using DataColumn.
• As the id must be unique, so it has two properties such as readOnly and unique.
• We have to use the DataColumn object to add the column to the data table using the DataTable.Columns.Add method.
• This way, we will create two other columns for address and name.
• Then we will set our id column as our primary key using DataTable.PrimaryKey. 

PrimaryKeyColumns[0] = custTable.Columns["id"];

custTable.PrimaryKey = PrimaryKeyColumns;

• After creating the datatable in “C# DataTable”, we have to add it to the dataset using the DataSet.Tables.Add method. This method takes one argument of type DataTable:

dtaSet = new DataSet("purchasers");

dataSet.Tables.Add(custTable);

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Now, we have to add the data to the data table using DataRow in “C# DataTable”.

• First, we create a DataRow object using DataTable’s NewRow method.
• Then, we add DataRow to the DataTable using DataTable.Rows.Add method.
• We will follow the same method for creating the Order table with OrderId, Name, description, and custId.
• Using DataRelation, the BindData will establish a relation between the id column of the Purchasers data table and custId of Order table.
• The relationship CustOrderRelation is established. After that, we bind DataTable to the DataGrid using the SetDataBinding method.

Conclusion

“C# DataTable” plays a significant role in storing different data types with its efficient properties and methods. It, therefore, has a vital role in our programming journey. C# DataTable helps us work with the backend with utmost flexibility and efficiency. It helps us keep all the data/information organized, i.e., if we receive data from somewhere and store it in C# DataTable, it will make it easier for us to look up later. 

If you wish to learn more such concepts, Simplilearn provides an excellent course on Full Stack Web Development, which not only covers fundamentals but will also train you on an industry level which will help you find better job prospects.

To further sharpen your skills and advance your career, you can visit SkillUp. This Simplilearn initiative offers several free online courses that can help you enhance your knowledge and skills and improve your career prospects.