.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
satyapriyanayak
SP
Sambanthamoorthy
Ashutosh Jha
Imran Ghani

Home >> Articles >> .Net Framework >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Using DataSet To Retrieve Data From Database

Posted By:Abhisek Panda       Posted Date: March 15, 2010    Points: 25    Category: .Net Framework    URL:   

This article will explain what is a DataSet and how to use it to retrieve data from the database with a suitable example.
 

Use Of DataSet


          DataSet is the king of consumer objects.Consumer objects are defined for disconnected consumer site of ADO.NET. These objects leave within the "System.Data" namespace. The DataDet represents a set of related tables referenced as a single unit in our application. With this object you can get the data from all the related tables quickly, examine and change it while you are disconnected from the server and then you can update it when you are online. The DataSet has features that enables you to access lower level objects that represents individual tables and relationships.

        In more simplified form DataSet is a virtual table using which we can retrieve the value from the database and we can store it in that database. In a DataSet multiple tables and its rows and columns can be displayed. We can also use the DataSet in connected architecture. As a virtual table to retrieve data from the DataSet we have to make use of DataGrid object.


RETRIEVING DATA FROM DATASET:-


        The DataSet is the central object in ADO.NET. A DataSet contains a set of DataTableDataRow and DataColumn objects representing rows and column of thr database table. You can get to all the individual elements of the table, rows, columns through their objects as described below. objects representing the database tables. It can be only one DataTable. Each DataTable object has child

Filling THe DataSet with data:-


        We can fill the DataSet with data using the "Fill()" method of a DataAdapter object. The DataAdapter is a object that ties the DataSet with a perticular Database. Fill() method has many overloads. but we will use only two parameters, the first specifeies the DataSet you want to fill and the second specifies the name of the DataTable within the DataSet that will contain the data.


Accessing tables, Rows and Columns in the DataSet:-


       The Dataset object has a property named "Table" that is a collection of all the DataTable objects within the Database.Tables is of the type DataTableCollection & has an overloaded indexer, which means that you can access each individiual DataTable in one of two possible ways-

By Table Name-
thisDataSet.Tables["Customers"]
specifies the DataTable called customers.

By index(the index is zero-based)-
thisDataSet.Tables[0]
specifies the first DataTable in the DataSet.

Within each DataTable there is a Rows property that is a collection of the individual DataRow objects. Rows is of type "DataRowCollection" and is an ordered list, indexed by row number. Thus,

myDataSet.Tables["Customers"].Rows[n]
, specifies a row(n-1) in the 'Customer DataTable' in the thisDataSet.

You might expect DataRow to have a property of the type 'DataColumnCollection', but it is not as simple as that; because you want to take advantage the datatype of the individual columns in each row, so that a column containing character data become a String, a column containing an integer became an integer object and so on.

The DataRow object is itself has an indexer property that is overloaded, enabling you to access individual columns by name and by number. Thus,

thisDataSet.Tables["Customers"].Rows[n]["Company Name"]
specifies the company name column of row number n-1 in the 'Customers DataTable' of thisDataSet. The dataRow object here is
thisDataSet.Tables["Customers"].Rows[n]

Example

It is a console application which uses ADO.NET and SQL Server data provider(sqlClient). It uses Northwind database as the source for retrieving data.

To run this console application you need to have sqlserver installed and Northwind database also installed.

SEE THE FOLLOWING CODE
using System;
using System.Collections.Generic;
using System.Data; // Use ADO.NET namespace
using System.Data.SqlClient; // Use SQL Server data provider namespace 
using System.Linq;
using System.Text;

namespace DataSet_Demo
{
    class Program
    {
        static void Main(string[] args)

        {
            //1: Specify SQL Server connection string
          SqlConnection con = new SqlConnection(@"Data  Source
 
=.\SQLEXPRESS;"+@"AttachDbFilename=?C:\SQL Server 2000 Sample

   Databases\NORTHWIND.MDF?;" +@"Integrated Security=True;Connect Timeout=30;User Instance=true" );

          //2: Create DataAdapter object
          SqlDataAdapter da = new SqlDataAdapter("SELECT CustomerID, 

ContactName FROM  Customers", con);

          //3: Create DataSet object to contain  data tables, rows, and columns
          DataSet ds = new DataSet();
         //4: Fill DataSet using query defined for DataAdapter
         da.Fill(ds, "Customers Details");
         //5:Access data row wise using foreach loop
         foreach (DataRow dr in ds.Tables["Customers"].Rows)
         {
         Console.WriteLine(dr["CustomerID"] + "\t" +dr["ContactName"]);
         }
         //Closing the connection
         con.Close();
         Console.Write("Program finished, press Enter/Return to continue:");
         Console.ReadLine();
        }
    }
}

OUTPUT

Run the program. It will show you the following output depending on the data in your database. In my system I get the following output.

ALFKI Maria Anders
ANATR Ana Trujillo
ANTON Antonio Moreno
AROUT Thomas Hardy
BERGS Christina Berglund
BLAUS Hanna Moos
BLONP Frédérique Citeaux
BOLID Martín Sommer
BONAP Laurence Lebihan
BOTTM Elizabeth Lincoln
BSBEV Victoria Ashworth
CACTU Patricio Simpson
CENTC Francisco Chang
CHOPS Yang Wang
COMMI Pedro Afonso
CONSH Elizabeth Brown
DRACD Sven Ottlieb
DUMON Janine Labrune
EASTC Ann Devon
ERNSH Roland Mendel
FAMIA Aria Cruz
FISSA Diego Roel
FOLIG Martine Rancé
FOLKO Maria Larsson
FRANK Peter Franken
FRANR Carine Schmitt
FRANS Paolo Accorti
FURIB Lino Rodriguez
GALED Eduardo Saavedra
GODOS José Pedro Freyre
GOURL André Fonseca
GREAL Howard Snyder
GROSR Manuel Pereira
HANAR Mario Pontes
HILAA Carlos Hernández
HUNGC Yoshi Latimer
HUNGO Patricia McKenna
ISLAT Helen Bennett
KOENE Philip Cramer
LACOR Daniel Tonini
LAMAI Annette Roulet
LAUGB Yoshi Tannamuri
LAZYK John Steel
LEHMS Renate Messner
LETSS Jaime Yorres
LILAS Carlos González
LINOD Felipe Izquierdo
LONEP Fran Wilson
MAGAA Giovanni Rovelli
MAISD Catherine Dewey
MEREP Jean Fresnière
MORGK Alexander Feuer
NORTS Simon Crowther
OCEAN Yvonne Moncada
OLDWO Rene Phillips
OTTIK Henriette Pfalzheim
PARIS Marie Bertrand
PERIC Guillermo Fernández
PICCO Georg Pipps
PRINI Isabel de Castro
QUEDE Bernardo Batista
QUEEN Lúcia Carvalho
QUICK Horst Kloss
RANCH Sergio Gutiérrez
RATTC Paula Wilson
REGGC Maurizio Moroni
RICAR Janete Limeira
RICSU Michael Holz
ROMEY Alejandra Camino
SANTG Jonas Bergulfsen
SAVEA Jose Pavarotti
SEVES Hari Kumar
SIMOB Jytte Petersen
SPECD Dominique Perrier
SPLIR Art Braunschweiger
SUPRD Pascale Cartrain
THEBI Liz Nixon
THECR Liu Wong
TOMSP Karin Josephs
TORTU Miguel Angel Paolino
TRADH Anabela Domingues
TRAIH Helvetius Nagy
VAFFE Palle Ibsen
VICTE Mary Saveley
VINET Paul Henriot
WANDK Rita Müller
WARTH Pirkko Koskitalo
WELLI Paula Parente
WHITC Karl Jablonski
WILMK Matti Karttunen
WOLZA Zbyszek Piestrzeniewicz
Program finished, press Enter/Return to continue:

Description:-

1:First we create a connection and then use this connection to create DataAdapter object.

2:Create an DataAdapter object and pass the sql query using the con object.

3:Then we created a DataSet where the data will be filled.

4:Now we have to fill the DataSet. A DataTable named 'Customers Details' will be created in the DataSet not in the database.

5:After filling the DataSet now we need to retrieve individual rows and columns. We use a foreach loop for this purpose which will loop through all the rows. here the dr["CustomerID"] represents the CustomerID column and dr["ContactName"] represents ContactName column of dr DataRow.

6:Then finally we are closing the connection.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend