Tutorials » DotNet tutorials »

DataSet, DataTable, DataRow

Manipulating Data using DataSet and DataTable

DataSet and DataTable are the key components in ADO.NET programming. In simple words, DataSet represents an in memory representation of the database. We can load an entire database into a DataSet and manipulate the data in memory. If you aremore familiar with DataSet, you can Add, Edit and Update data in the dataset and then just call a single method 'AcceptChanges()' whichwill save all the changes back to the database.

  • A DataSet contains one or more DataTables
  • A DataTable contains DataRows.

    What is DataSet ?

    A DataSet is an in memory representation of data loaded from any data source. Even though the most common data sourceis database, we can use DataSet to load data from other data sources including XML files etc. In this article, we will talk about the role of DataSet in manipulating data from database.

    In .NET, a DataSet is a class provided by the .NET Framework. The DataSet class exposes several proeprties and methods that can be used to retrieve, manipulate and save data from various data sources.

    Just like any other classes in object oriented programming, we have to create an instance of DataSet class to work with data. Typically, we may create a new instance of a DataSet and use other classes provided by .NET Framework to populate the DataSet. See the following example:

    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";

    OleDbConnection myConnection = new OleDbConnection( connectionString );

    string query = "select * from EMPLOYEE_TABLE";

    OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );

    DataSet employeeData = new DataSet();

    myAdapter.Fill ( employeeData );

    Here we are creating a OleDbConnection object and we are just passing the object to the OleDbDataAdapterobject. Also, we pass the 'select ...' query to the OleDbDataAdapter. Next, we call the '.Fill()' method of the OleDbDataAdapter. This step will populate the dataset ( called 'employeeData' ) with the data retrieved for the sql statement 'select * from EMPLOYEE'.

    As you already know, a DataSet can contain a collection of tables. But in the above case, our sql statement will retrieve data from only one table. So, our DataSet will have only one table.

    Commonly used properties and methods of DataSet

    Property : Tables

    The Tables propertly allows us to retrieve the tables contained in the DataSet. This property returns a DataTableCollection object. The following sample code demonstrates iterating through the collection of tables in a data set and print the name of all the tables.

    DataSet employeeData = new DataSet();
    myAdapter.Fill( employeeData );

    // Repeat for each table in the DataSet collection.
    foreach ( DataTable table in employeeData.Tables )
    MessageBox.Show ( table.TableName );

    Or, you can use the indexer to access any specific table in the collection.

    DataSet employeeData = new DataSet();
    myAdapter.Fill( employeeData );

    // Repeat for each table in the DataSet collection.
    for ( int i = 0; i < employeeData.Tables.Count; i++ )
    DataTable table = employeeData.Tables[i];
    MessageBox.Show ( table.TableName );

    Method : GetXml()

    The GetXml() method returns the XML representation of the data from the DataSet.

    DataSet employeeData = new DataSet();
    myAdapter.Fill( employeeData );

    string xmlData = employeeData.GetXml();

    Method : WriteXml(...)

    The WriteXml() method allows to save XML representation of the data from the DataSet to an XML file. There are many overloaded method available, which takes various parameters. The example shown below takes a file name as parameter and saves the data in DataSet into xml format to the file name specified as parameter. We can optionally save only the data or both data and schema.

    DataSet employeeData = new DataSet();
    myAdapter.Fill( employeeData );

    employeeData.WriteXml( "c:\\MyData.xml" );

    Method : ReadXml(...)

    The ReadXml() method allows to load the DataSet from an XML representation of the data. There are many overloaded method available, which takes various parameters. The example shown below takes a file name as parameter and loads the data from XML file into the DataSet. This method can be used to load either the data only or both data and schema from the XML.

    DataSet employeeData = new DataSet();
    employeeData.ReadXml( "c:\\MyData.xml" );

    There is another method called 'ReadXmlSchema()', which can be used to load only the schema from a file.

    The methods WriteXml() and ReadXml() are useful to save the data from a database into some temporary files, transport to other places or keep it as a local file and load later. Many applications, including the SpiderAlerts tool available for download from this site, uses DataSet to manipulate data and saves/retrieves them from local disk using the WriteXml() and ReadXml() methods.

    The SpiderAlerts tool communicates with webservices in our site and retrieves the alerts in the form of a DataSet. Once the Alerts are retrieved, it is saved into local computer using the WriteXml method. (This implementation may be changed soon in the future versions of this tool. We are considering saving(serializing) the DataSet into Isolated Storage (IsolatedStorage is a new feature part of the .NET Framework - it is a kind of hidden file system).

    You can read more about Isolated Storage here:

  • http://www.dotnetspider.com/Article18.aspx
  • http://www.dotnetspider.com/kb/Article344.aspx

    The source code for the SpiderAlerts tool will be available in this site's projects section soon.

  • Next Chapter: More about DataTable and DataRow
    Previous Chapter: Create, Read, Update, Delete - ADO.NET sample
    More Chapters: .NET Tutorials
    More Tutorials: Tutorial Index

    Top Contributors
      Last 7 Daysmore...

      Awards & Gifts

      Online Members

      Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India