ADO.NET Sample Application
The following is a simple ADO.NET application that returns results from a data source and writes the output to the console or command prompt. Much of the sample code provided in Accessing Data with ADO.NET can be placed into a template made from this sample to see a working example of a particular ADO.NET feature.
The sample code in this topic shows the typical namespaces included in an ADO.NET application. There are samples for the .NET Framework Data Provider for SQL Server (System.Data.SqlClient), the .NET Framework Data Provider for OLE DB (System.Data.OleDb), the .NET Framework Data Provider for ODBC (System.Data.Odbc), and the .NET Framework Data Provider for Oracle (System.Data.OracleClient). All data providers can be used in a single application, if desired.
The following example connects to the Northwind database on Microsoft SQL Server 2000 and returns a list of Categories using a DataReader (for more information about the DataReader, see Retrieving Data Using the DataReader).
To compile and run the ADO.NET sample application
Using Notepad or another text editor, create a blank text file named sample.vb for Visual Basic sample code or named sample.cs for C# sample code.
Copy and paste the Visual Basic or C# sample code from this topic into the blank text file. Save the file.
Open a command prompt (Start, then Run, then enter "command").
In the command prompt, change the directory to the directory that contains the new text file. For example:
cd\SampleCode\ADONETSampleIn the command prompt, enter one of the following commands to compile the sample (the path to your file will likely differ).
For Visual Basic, use vbc.exe and use the following command to reference the system libraries needed to run your ADO.NET application.
vbc.exe sample.vb /r:System.dll /r:System.Data.dll /r:System.Data.OracleClient.dll /r:System.Xml.dllFor C#, use csc.exe and use the following command to reference the system libraries needed to run your ADO.NET application.
csc.exe sample.cs /r:System.dll /r:System.Data.dll /r:System.Data.OracleClient.dll /r:System.Xml.dll
The compiler will create an executable called sample.exe. Enter "sample.exe" at the command prompt to run the compiled sample.
SqlClient
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasic
Public Class Sample
Public Shared Sub Main()
Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
Dim catCMD As SqlCommand = nwindConn.CreateCommand()
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"
nwindConn.Open()
Dim myReader As SqlDataReader = catCMD.ExecuteReader()
Do While myReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
Loop
myReader.Close()
nwindConn.Close()
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.SqlClient;
class Sample
{
public static void Main()
{
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlCommand catCMD = nwindConn.CreateCommand();
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories";
nwindConn.Open();
SqlDataReader myReader = catCMD.ExecuteReader();
while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}
myReader.Close();
nwindConn.Close();
}
}
OleDb
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic
Public Class Sample
Public Shared Sub Main()
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
Dim catCMD As OleDbCommand = nwindConn.CreateCommand()
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"
nwindConn.Open()
Dim myReader As OleDbDataReader = catCMD.ExecuteReader()
Do While myReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
Loop
myReader.Close()
nwindConn.Close()
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.OleDb;
class Sample
{
public static void Main()
{
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
OleDbCommand catCMD = nwindConn.CreateCommand();
catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories";
nwindConn.Open();
OleDbDataReader myReader = catCMD.ExecuteReader();
while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}
myReader.Close();
nwindConn.Close();
}
}
Odbc
Imports System
Imports System.Data
Imports System.Data.Odbc
Imports Microsoft.VisualBasic
Public Class Sample
Public Shared Sub Main()
Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;" & _
"Trusted_Connection=yes;Database=northwind")
Dim catCMD As OdbcCommand = new OdbcCommand("SELECT CategoryID, CategoryName FROM Categories", nwindConn)
nwindConn.Open()
Dim myReader As OdbcDataReader = catCMD.ExecuteReader()
Do While myReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
Loop
myReader.Close()
nwindConn.Close()
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.Odbc;
class Sample
{
public static void Main()
{
OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;" +
"Trusted_Connection=yes;Database=northwind");
OdbcCommand catCMD = new OdbcCommand("SELECT CategoryID, CategoryName FROM Categories", nwindConn);
nwindConn.Open();
OdbcDataReader myReader = catCMD.ExecuteReader();
while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}
myReader.Close();
nwindConn.Close();
}
}
OracleClient
Imports System
Imports System.Data
Imports System.Data.OracleClient
Imports Microsoft.VisualBasic
Class Sample
Public Shared Sub Main()
Dim oraConn As OracleConnection = New OracleConnection("Data Source=MyOracleServer;Integrated Security=yes;")
Dim oraCMD As OracleCommand = New OracleCommand("SELECT CUSTOMER_ID, NAME FROM DEMO.CUSTOMER", oraConn)
oraConn.Open()
Dim myReader As OracleDataReader = oraCMD.ExecuteReader()
Do While (myReader.Read())
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
Loop
myReader.Close()
oraConn.Close()
End Sub
End Class
[C#]
using System;
using System.Data;
using System.Data.OracleClient;
class Sample
{
public static void Main()
{
OracleConnection oraConn = new OracleConnection("Data Source=MyOracleServer;Integrated Security=yes;");
OracleCommand oraCMD = new OracleCommand("SELECT CUSTOMER_ID, NAME FROM DEMO.CUSTOMER", oraConn);
oraConn.Open();
OracleDataReader myReader = oraCMD.ExecuteReader();
while (myReader.Read())
{
Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));
}
myReader.Close();
oraConn.Close();
}
}