December 2017 : Online courses in ASP.NET MVC and Angular 4. Conducted by Bipin Joshi. Read more...
Registration for December 2017 batches of ASP.NET MVC / Core and Angular 4 online courses have already started. Conducted by Bipin Joshi. Book your seat today ! Click here for more details.

<%@ Page %>

ADO.NET Series - Populating DataSet

Introduction

In previous articles of the series we saw how to SELECT data from database into a DataReader and iterate through it. As we saw the DataReader is a connected, read-only and forward only cursor. In this article we will see how to populate DataSet from database data.

What is DataSet

DataSet is an in-memory replica of database. Just like database, a DataSet can contain tables, relations and constraints. In the simplest form it is used to hold data from various SELECT queries. The data is stored in DataSet in the form of one or more DataTables.

Example

using System;
using System.Data;
using System.Data.SqlClient;

//This sample shows how to populate dataset

namespace ADONETSamples
{
class Sample4
{
static void Main(string[] args)
{
//declare connection,datadapter and dataset
SqlConnection cnn;
SqlDataAdapter da;
DataSet ds;
//create connection
cnn=new SqlConnection
(Database.GetConnectionString());
da=new SqlDataAdapter();
ds=new DataSet();
//set selectcommand property
da.SelectCommand=new SqlCommand
("select * from employees",cnn);
//populate the dataset
da.Fill(ds,"employees");
//display values
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
{
Console.WriteLine(
ds.Tables[0].Rows[i]["firstname"]);
}
}
}
}

Explanation

  • We first imported System.Data and System.Data.SqlClient namespaces.
  • Inside the Main method we create connection, command objects as usual.
  • Next, we also create an instance of DataAdapter class. The DataAdapter is a bridge between your database and DataSet.
  • We then create an instance of DataSet class.
  • The DataAdapter class has a property called SelectCommand that is responsible for fetching the data.
  • This property needs to set to valid command object that actually has the SELECT command or stored procedure.
  • That is why we set SelectCommand property to the SqlCommand instance we created.
  • We then call the Fill() method of the DataAdapter that actually populates the DataSet with the records.
  • The second parameter of the Fill() method represents the DataTable name (employees in our case) that gets created after the filling operation.
  • To check whether data really got populated in the DataSet or not we simply output various field values to the console. The iteration is done using Rows collection of the DataTable.

Summary

In this article we saw how to populate DataSet with data from database table. DataSet is an in-memory replica of database which can contain one or more result sets of data in the form of DataTables.

Bipin Joshi is a software consultant, an author and a yoga mentor having 22+ years of experience in software development. He also conducts online courses in ASP.NET MVC / Core and Design Patterns. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches Meditation and Mindfulness to interested individuals. To know more about him click here.

Get connected : Twitter  Facebook  Google+  LinkedIn

Posted On : 19 May 2003


Tags : ADO.NET Data Access SQL Server