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.
The samples in this topic show how to connect to and retrieve data using 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 SqlClient example below assumes that you can connect to the Northwind sample database on Microsoft SQL Server 7.0 or later, and returns a list of records from the Categories table using a SqlDataReader. The OleDb and Odbc examples assume a connection to the Microsoft Access Northwind sample database. The OracleClient example assumes a connection to DEMO.CUSTOMER on an Oracle server. You must also add a reference to the System.Data.OracleClient.dll.
For more information about the DataReader, see Retrieving Data Using a DataReader).
SqlClient
Option Explicit On
Option Strict On
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class Program
Public Shared Sub Main()
Dim connectionString As String = GetConnectionString()
Dim queryString As String = _
"SELECT CategoryID, CategoryName FROM dbo.Categories;"
Using connection As New SqlConnection(connectionString)
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText = queryString
Try
connection.Open()
Dim dataReader As SqlDataReader = _
command.ExecuteReader()
Do While dataReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
dataReader(0), dataReader(1))
Loop
dataReader.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Sub
Private Shared Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=(local);Initial Catalog=Northwind;" _
& "Integrated Security=SSPI;"
End Function
End Class
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
string queryString =
"SELECT CategoryID, CategoryName FROM dbo.Categories;";
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = queryString;
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}",
reader[0], reader[1]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "Data Source=(local);Initial Catalog=Northwind;"
+ "Integrated Security=SSPI";
}
}
OleDb
Option Explicit On
Option Strict On
Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class Program
Public Shared Sub Main()
Dim connectionString As String = GetConnectionString()
Dim queryString As String = _
"SELECT CategoryID, CategoryName FROM Categories;"
Using connection As New OleDbConnection(connectionString)
Dim command As OleDbCommand = connection.CreateCommand()
command.CommandText = queryString
Try
connection.Open()
Dim dataReader As OleDbDataReader = _
command.ExecuteReader()
Do While dataReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
dataReader(0), dataReader(1))
Loop
dataReader.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Sub
Private Shared Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
' Assumes Northwind.mdb is located in c:\Data folder.
Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& "c:\Data\Northwind.mdb;User Id=admin;Password=;"
End Function
End Class
using System;
using System.Data;
using System.Data.OleDb;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
string queryString =
"SELECT CategoryID, CategoryName FROM Categories;";
using (OleDbConnection connection =
new OleDbConnection(connectionString))
{
OleDbCommand command = connection.CreateCommand();
command.CommandText = queryString;
try
{
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}",
reader[0], reader[1]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
// Assumes Northwind.mdb is located in the c:\Data folder.
return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ "c:\\Data\\Northwind.mdb;User Id=admin;Password=;";
}
}
Odbc
Option Explicit On
Option Strict On
Imports System
Imports System.Data
Imports System.Data.Odbc
Public Class Program
Public Shared Sub Main()
Dim connectionString As String = GetConnectionString()
Dim queryString As String = _
"SELECT CategoryID, CategoryName FROM Categories;"
Using connection As New OdbcConnection(connectionString)
Dim command As OdbcCommand = connection.CreateCommand()
command.CommandText = queryString
Try
connection.Open()
Dim dataReader As OdbcDataReader = _
command.ExecuteReader()
Do While dataReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
dataReader(0), dataReader(1))
Loop
dataReader.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Sub
Private Shared Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
' Assumes Northwind.mdb is located in c:\Data folder.
Return "Driver={Microsoft Access Driver (*.mdb)};" _
& "Dbq=c:\Data\Northwind.mdb;Uid=Admin;Pwd=;"
End Function
End Class
using System;
using System.Data;
using System.Data.Odbc;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
string queryString =
"SELECT CategoryID, CategoryName FROM Categories;";
using (OdbcConnection connection =
new OdbcConnection(connectionString))
{
OdbcCommand command = connection.CreateCommand();
command.CommandText = queryString;
try
{
connection.Open();
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}",
reader[0], reader[1]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
// Assumes Northwind.mdb is located in the c:\Data folder.
return "Driver={Microsoft Access Driver (*.mdb)};"
+ "Dbq=c:\\Data\\Northwind.mdb;Uid=Admin;Pwd=;";
}
}
OracleClient
Option Explicit On
Option Strict On
Imports System
Imports System.Data
Imports System.Data.OracleClient
Public Class Program
Public Shared Sub Main()
Dim connectionString As String = GetConnectionString()
Dim queryString As String = _
"SELECT CUSTOMER_ID, NAME FROM DEMO.CUSTOMER"
Using connection As New OracleConnection(connectionString)
Dim command As OracleCommand = connection.CreateCommand()
command.CommandText = queryString
Try
connection.Open()
Dim dataReader As OracleDataReader = _
command.ExecuteReader()
Do While dataReader.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
dataReader(0), dataReader(1))
Loop
dataReader.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Sub
Private Shared Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=ThisOracleServer;Integrated Security=yes;"
End Function
End Class
using System;
using System.Data;
using System.Data.OracleClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
string queryString =
"SELECT CUSTOMER_ID, NAME FROM DEMO.CUSTOMER";
using (OracleConnection connection =
new OracleConnection(connectionString))
{
OracleCommand command = connection.CreateCommand();
command.CommandText = queryString;
try
{
connection.Open();
OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}",
reader[0], reader[1]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
// Assumes Northwind.mdb is located in the c:\Data folder.
return "Data Source=ThisOracleServer;Integrated Security=yes;";
}
}