Acessing data from SQL server using Dataset

Posted by Abhisek under C# category on | Views : 2821
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.


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
Console.Write("Program finished, press Enter/Return to continue:");


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:


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.

Comments or Responses

Login to post response