Properties, Methods And Events Of DataSet Class In ADO.NET

Introduction

The DataSet represents an in-memory collection of data retrieved from a data source. It can contain records of multiple tables. It is used for adding and manipulating records of a table in a disconnected environment.

The data in the DataSet can be edited programmatically. The developer can use an XML Schema to populate data in the DataSet. This DataSet schema can be created programmatically.

Properties and Methods of DataSet class

HasErrors

This property retrieves a value that indicates whether errors are present in any of the Data Table objects within a DataSet.

IsInitialized

This property retrieves a value that indicates whether the DataSet is Initialized.

Merge

Merges a DataSet, DataTable & DataRow objects into current DataSet or DataTable.

RejectChanges

This method rejects all changes made to the DataSet from the time it was created or since the last time the DataSet. AcceptChnages() method was called.

MergeFailed

Event occurs when the DataRow (target) and DataRow (source) have identical primary key value and also EnforceConstraints property is true.

The Source Code demonstrates how to merge two tables and display their records using the DataSet class.

SqlConnection sqlconMarketing = new SqlConnection(“Data Source = MRKTING; Initial Catalog = DigiMarket; Integrated Security = SSPI;”);
DataSet dsetProducts;
DataSet dsetNewProducts;
DataGridView dgvwProducts;
SqlCommand sqlcomMarketing = new SqlCommand(“SELECT * FROM Products”, sqlconMarketing);
SqlDataAdpater sqldaMarketing = new SqlDataAdapter(sqlcomMarketing);
dsetProducts = new DataSet();
sqldaMarketing.Fill(dsetProducts);
dgvwProducts = new DataGridView();
dgvwProducts.DataSource = dsetProducts.Tables[0];
Button btnMerge = new Button();
sqlcomMarketing = new SqlCommand(“SELECT * FROM NewProducts”, sqlconMarketing);
sqldaMarketing = new SqlDataAdapter(sqlcomMarketing);
dsetNewProducts = new DataSet();
sqldaMarketing.Fill(dsetNewProducts);
private void destProducts_MergeFailed(object sender, MergeFaildeEventArgs e) {
MessageBox.Show(“Error
while Merging…”);
}
private void btnMerge_Click(object sender, EventArgs e) {
if (dsetProducts.IsInitialized && !dsetProducts.HasErrors) {
dsetProducts.Merge(dsetNewProducts);
dgvwProducts.DataSource = dsetProducts.Tables[0];
}
}

In this source code, two DataSet objects are created namely, diet products and dsetNewProducts. These DataSets are filled with the records of the Products and NewProducts table respectively. Both these tables have a similar structure. When a user clicks the button, the NewProducts table is merged with the dsetProductsDataSet and the records of both the tables are displayed in the DataGridView control. The MergeFailed event occurs if the merging of the datasets fails.

Navigating through Records

DataSet objects allow the users to access rows using the index of the table collection. They also allow finding and deleting rows based on the primary key.

Source Code demonstrates how to retrieve the second record from the table specified in the DataSet.

// Code for database connectivity and filling the dataset.
DataColumn[] dcolPrimary=new DataColumn[1];
dcolPrimary[0]=dset.Tables[“Suppliers”].Columns[“SupplierID”];
dset.Tables[“Suppliers”].primarykey=dcolPrimary;
drowTerritories=dset.Tables[“Suppliers”].Rows.Find(“S001”);

In this code , first the primary key is set in the DataSet. This is done using the DataColumn object and the PrimaryKey property of the DataSet class. Now, the find() method of the DataSet class is used to retrieve the row whose suppler ID is S001.

The Code below demonstrates how to delete the row using the primary key.

// Code for database connectivity using the SqlCommandBuilder class and filling the dataset.
Dset.Tables[“Files”].Rows.Find(“F001”).Delete();
sqldaFiles.Update(dset,”Files”);

Summary

DataSet class contains records of multiple tables, and also represents an in-memory collection of data retrieved from a data source. The data in the DataSet can be edited programmatically. DataSet objects allow the users to access rows using the index of the table collection.