Basics of DataSet:
A Dataset is an 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 and so on.
It can be used for manipulating the data remotely and finally updating the database with the modified data. This way it provides a disconnected way to work 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 using a DataAdapter.
All the related classes, like DataSet, DataAdapter, DataTable, DataRow and so on 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 and so on., 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 a dataset can be looked at from two angles. One is the technique of fetching 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 many ways including:
- 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 and so on. 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 and so on) 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 since 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 code snippet above will add a row of data as a 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 dataset.
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 code snippet above 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.