DataSet Class

The System.Data.DataSet class holds data that are retrieved from the database. The DataSet class allows you to hold disconnected data. It means you can work with the contents of the database while disconnected to the actual database. This is because the contents that you will be working on already exists inside the DataSet. You can consider the DataSet class as a mini database management system inside the memory.

The DataSet class has a Tables property which is a collection of System.Data.DataTable objects. A typical database can contain multiple tables. Just like a database, a DataSet can contain multiple tables as well. Records retrieved from the actual database are stored as a DataTable and added to the Tables property of the DataSet. The DataTable also has properties named Columns and Rows which are collections of DataColumn and DataRow respectively. These properties contain the individual columns and rows of the table from the database. The good thing about the DataTable and DataColumn is that they can be assigned with names. This is done by using the TableName and ColumnName properties or using their constructors.

Let’s create a simple DataTable and add it to the DataSet. Create a new Windows Forms Application and name it DataSetDemo. Add a DataGridView from the toolbar and set its Dock property to Fill.

Double-click the title bar of the form to generate an event handler for the form’s Load event. First, be sure to import the System.Data namespace. The following code for the Load event handler.

private void Form1_Load(object sender, EventArgs e)
{
    //Initialize components
    DataSet personsDataSet = new DataSet();
    DataTable personsTable = new DataTable("Persons");
    DataColumn firstNameColumn = new DataColumn("FirstName");
    DataColumn lastNameColumn = new DataColumn("LastName");
    DataColumn ageColumn = new DataColumn("Age");
    DataRow firstPerson;
    DataRow secondPerson;
    DataRow thirdPerson;
 
    //Construct the table's column
    personsTable.Columns.AddRange(new DataColumn[] {
        firstNameColumn,
        lastNameColumn,
        ageColumn });
 
    //Create three rows/records
    firstPerson = personsTable.NewRow();
    secondPerson = personsTable.NewRow();
    thirdPerson = personsTable.NewRow();
 
    //Assign values to rows
    firstPerson["FirstName"] = "Mark";
    firstPerson["LastName"] = "Davidson";
    firstPerson["Age"] = 18;
 
    secondPerson["FirstName"] = "John";
    secondPerson["LastName"] = "Fredrich";
    secondPerson["Age"] = 22;
 
    thirdPerson["FirstName"] = "Chelsey";
    thirdPerson["LastName"] = "Bells";
    thirdPerson["Age"] = 19;
 
    //Add the rows to the table
    personsTable.Rows.Add(firstPerson);
    personsTable.Rows.Add(secondPerson);
    personsTable.Rows.Add(thirdPerson);
 
    //Add the table to the DataSet
    personsDataSet.Tables.Add(personsTable);
 
    dataGridView1.DataSource = personsDataSet.Tables["Persons"];
}

Example 1

Lines 4-11 declares the different classes for creating a complete DataTable and DataSet. Notice the passed names inside the constructor for the DataTable and each DataColumn. This will make them easier to reference when we call them using the DataSet. Lines 14-17 adds the created DataColumn object to the Columns property of the DataTable. It is important that you do this first before specifying the rows. Lines 20-22 initializes each DataRow object using the DataTable.NewRow() method which returns a DataRow object that already has the proper number of values it can have. Lines 25-35 assigns values to each field of the DataRow objects. Note that each field was accessed via indexers that accept that name of the corresponding DataColumn. To complete the DataTable, lines 38-40 adds all the rows to the DataTable.Rows property. Finally, we added the DataTable object to the Tables property of the DataSet. To access the table that we have added, we can use an indexer for the Tables property and pass the name of the table. We assign the table to the DataSource property of the DataGridView so it will show the contents of the DataTable that we have created. Run the program and you will see the records of the table inside the DataGridView control.