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


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Acessing data from SQL server

Posted By:Abhisek Panda       Posted Date: October 26, 2009    Points: 10    Category: ADO.NET    URL: http://www.dotnetspark.com  
 

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.


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

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