Updated March 29, 2023
Introduction to Dataset to Datatable C#
The following article provides an outline for Dataset to Datatable C#. Whenever data must be collected from the database, the dataset does the work without being in a continuous connection with the database where virtual databases are created that look like the original database and data is stored in the same place. Therefore, we can say that the dataset is a representation of the database structure. Datatable is the representation of one table in the database where rows and columns are properly named in the database. It also represents the cache of rows, columns, constraints of both rows and columns in the structure. Datatable is the base in DotNet, where dataset and data view makes use of Datatable.
What is Dataset to Datatable C#?
- Dataset: Dataset provides a consistent programmable model irrespective of the data source, and this helps in making a memory representation of data in the database. The tables in the dataset can be created with the help of DataTables, DataViews, or even with data alone. We can also create a dataset with the help of a data adapter.
- Datatable: A single table inside a dataset is represented with the help of Datatable, where it can be created either alone or in collaboration with the dataset. The structure is represented by columns, rows, and constraints in the database that has ForeignKeyConstraint and Unique constraint in the table.
Convert Dataset to Datatable C#
It is easy to convert the dataset to datatable as it has tables already.
The query to be written is:
DataTable myTable = dataSet.Tables[0]We can also call the table by using names.
DataTable employeesTable = dataSet.Tables["Employees"]Difference Between Dataset to Datatable C#
- Dataset is a collection of tables, and hence it is datatables itself.
- Datatable is a collection of rows and columns to form a table.
- Dataset defines the relationship between the tables and the constraints of having them in the dataset; since there is only one table represented in Datatable, the relationships need not be defined in the Datatable.
- Dataset is heavier than datatable as datatable will have only one table, but the dataset is a collection of datatables.
- Only one row can be fetched at a time in datatable, while multiple rows and columns can be displayed in a dataset.
Creating a Table C#
We can create the datatables either directly or through datasets. Datatable constructor is one way of creating the table, and the other way is using add method to the table property of the dataset. Data adapter object has fillschema methods which can be used for the schema in the datatables, or xml schema can be used as they are predefined.
Once datatables are present in a dataset, we cannot add the same tables in any other dataset. Column collection of the table is where we add data schema derived either from xml or fillschema methods. The primary column is necessary for the table, along with table constraints for the columns. Datarow objects can be added to the rows of the table after schema and columns definition. Tablename property is not necessary to be defined in the early stage as it can be left empty or can be named later. It is better to give a name to the table if it is to be added in the dataset. An exception will happen if the table name is repeated.
We can create a table of employees with the below script.
DataTable currentTable = new DataTable("Employees")We are adding the table to a dataset.
DataSet employeeDS = new DataSet();
DataTable empTable = customerDS.Tables.Add("EmpTable");We are creating a table in the example where all the relevant conditions are met and setting up the primary key in the table.
// Create the Table
DataTable ProjectsTable = new DataTable("Projects");
// Build the Projects schema
projectsTable.Columns.Add("ID"
Type.GetType("System.Int32"));
projectsTable.Columns.Add("Name"
Type.GetType("System.String"));
projectsTable.Columns.Add("Estimate"
Type.GetType("System.Int32"));
// Set up the ID column as the PrimaryKey
DataColumn[] prmk = new DataColumn[1];
prmk[0] = ordersTable.Columns["ID"];
ordersTable.PrimaryKey = prmk;
ordersTable.Columns["ID"].AutoIncrement = true;
ordersTable.Columns["ID"].AutoIncrementSeed = 1;
ordersTable.Columns["ID"].ReadOnly = true;Datatable constructor is used to create projects and increment, incrementseed, readonlyproperty is set in the above example. We can also set datatable objects and add them to a dataset. Constraints should be set in the scripts as the primary key, and datacolumn objects must be added to the columns collection in the table.
DataSet employeeDS = new DataSet("EmpProject");
DataTable projectsTable = employeeDS.Tables.Add("Projects");
DataColumn pkCol = projectsTable.Columns.Add("ProjectID", typeof(Int32));
projectsTable.Columns.Add("ProjectType", typeof(Int32));
projectsTable.Columns.Add("CompanyName", typeof(string));
projectsTable.PrimaryKey = new DataColumn[] { pkCol };
New rows can be created with the below example in datatable.
DataRow workRow = currentTable.NewRow();
workRow["ProjectDuration"] = "4 weeks";
workRow[1] = "4 weeks";
currentTable.Rows.Add(workRow);
currentTable.Rows.Add(new Object[] { 1, "4 weeks" });
If we need to add 5 rows to the datatable, following code can be used.
DataRow workRow;
for (int i = 0; i <= 4; i++) {
workRow = currentTable.NewRow();
workRow[0] = i;
workRow[1] = "Duration" + i.ToString();
currentTable.Rows.Add(workRow);
}Rows and columns can be used to access the contents inside the table. We can also set some criteria such as state, order to check the data inside the datatable. The find method also works for the row checking inside datatable.
Example of Dataset to Datatable C#
Given below are the example of the dataset to datatable c# :
Dataset:
This is an example of a dataset. We have two datatables students and departments inside the dataset college.
Code:
using System;
using System. Data;
class Program
{
static void Main()
{
// Create 2 DataTable instances.
DataTable table1 = new DataTable("students");
table1.Columns.Add("name");
table1.Columns.Add("id");
table1.Rows.Add("mary", 1);
table1.Rows.Add("amir", 2);
DataTable table2 = new DataTable("department");
table2.Columns.Add("id");
table2.Columns.Add("physics");
table2.Rows.Add(1, "chemistry");
table2.Rows.Add(2, "maths");
// Create a DataSet and put both tables in it.
DataSet set = new DataSet("college");
set.Tables.Add(table1);
set.Tables.Add(table2);
// Visualize DataSet.
Console.WriteLine(set.GetXml());
}
}We can create datatables using the above code where students and departments are created. Then, we can insert data based on our requirements inside the table and use it in the dataset.
Conclusion
Dataset can be filled with the help of SqlDataAdapter that helps manipulate data and update the data based on the user’s requirement. This helps in working with data even if we are not connected with the dataflow of the data. Hence, the database need not be accessed each time while doing the manipulations.
Recommended Articles
This is a guide to Dataset to Datatable C#. Here we discuss introduction, convert dataset to datatable C#, difference, creating a table C# & example. You may also have a look at the following articles to learn more –
