DataSet in .Net

February 9, 2010 muthu ADO .Net 0

What is DataSet:

A Dataset is a in memory representation of a collection of Database objects including tables of a relational database scheme. The dataset contains a collection of Tables, Relations, constraints etc.,

It can be used for manipulating the data remotely and finally updating the database with the modified data. This way it enables disconnected means of working with data. This improves performance in terms of reducing the number of times a database is accessed for data manipulations.

The dataset contains all the objects as a Collection of objects. For example it contains the Database Tables as DataTable objects. The data from the database is filled into the dataset by using the DataAdapter.

All the related classes like DataSet, DataAdapter, DataTable, DataRow etc are available inside the System.Data namespace. This namespace can be used as follows:

--Inside an asp.net page
<%@ Import Namespace="System.Data" %>

--Inside a C# page
using System.Data;

All Data Access related classes and namespaces come under this System.Data namespace. There are some more Database specific namespaces under System.Data such as System.Data.SqlClient,System.Data.XML etc., The SqlClient contains the classes required to work with the Sql Server database and XML namespaces gives classes tailored to suit the needs of XML data access.

Working with DataSet

: Working with dataset can be looked at from two angles. One is the ways of filling data into a Dataset and the second is how to render it on the screen. Presentation of data can be either on a GUI Client Server screen or on an ASP .Net web page.

Populating a DataSet:

A dataset can be populated in multiple number of ways.

  • Manually constructed Rows and Tables
  • Data from an XML File
  • Data from a database

Manually constructed Rows and Tables:

This is usually preferred when we need to process an existing set of data such as calculations on a database, reading from an unconventional data source etc., If we need data from such inconsistent data sources to be rendered into an application written on our .Net framework (including asp .net, c#, vb .net) etc., then we can go for manipulating the data and then creating the Dataset manually.

Before constructing a dataset in .net manually, it should be prepared by adding datacolumn types to it. The DataTable thus constructed should be added to the DataSet using DataSet.Tables.Add(DataTable variablename) function. This will add the DataTable with the specific definition of Column types into the DataSet. If needed more DataTables can be added to the DataSet as it is capable of holding multiple Database objects at a single point of time.

After preparing the DataSet and DataTable to contain the Correct Column Types, the following is the code snippet for adding DataRow into the Dataset.

ExampleRow = myDataTable.NewRow();
ExampleRow["Name"] = "testname";
ExampleRow["Age"] = 25;
myDataTable.Rows.Add(ExampleRow);

The above code snippet will add a row of data as DataRow to the DataTable. Any number of rows can be added in a similar fashion with either Processed or Unprocessed data. This can finally be rendered by a manual iteration or Data binding to a DataGrid.

Reading Data from XML File into a DataSet:

This is a very straight forward operation and the code involved is very simple. The following Code snippet can pull the data from an XML File into the Data set.

DataSet dsCategory = new DataSet();
dsCategory.ReadXml("XML Path in the drive");

This DataSet can then be used to manipulate data for rendering on the screen.

Reading Data from a Database into a DataSet:

This needs the SqlConnection object of .Net to connect to the database first.

using System.Data.SqlClient;
using System.Data;string strDBConnection = "server=(local);database=DatabaseName;user id=UserName;password=Pwd;connection reset=false;connection lifetime=5;Trusted_Connection=Yes;"
SqlConnection dbConnection;
dbConnection = new SqlConnection(strDBConnection);

string strSelectSql = "Select * from [DatabaseName].[OwnerName].[TableName] order by FieldName";

//Open the connection
dbConnection.Open();

//Create a command
SqlCommand selectSqlCommand = new SqlCommand(strSelectSql,dbConnection);
SqlDataAdapter sqlData = new SqlDataAdapter(selectSqlCommand);
DataSet dsSelectData = new DataSet();
sqlData.Fill(dsSelectData);

The above code snippet will connect to the specified database and fill the Data from the Table TableName into the DataSet. The data can then be used for either rendering on the screen or for further processing.