ADO.NET Code Examples
The code listings in this topic demonstrate how to retrieve data from a database by using the following ADO.NET technologies:
ADO.NET data providers:
.NET Framework Data Provider for SQL Server (System.Data.SqlClient)
.NET Framework Data Provider for OLE DB (System.Data.OleDb)
.NET Framework Data Provider for ODBC (System.Data.Odbc)
.NET Framework Data Provider for Oracle (System.Data.OracleClient)
ADO.NET Entity Framework:
LINQ to Entities
Typed ObjectQuery
EntityClient data provider (System.Data.EntityClient)
LINQ to SQL
ADO.NET Data Provider Examples
The following code listings demonstrate how to retrieve data from a database using ADO.NET data providers. The data is returned in a DataReader. For more information, see Retrieving Data Using a DataReader (ADO.NET).
SqlClient
The code in this example assumes that you can connect to the Northwind sample database on Microsoft SQL Server 7.0 or a later version. The code creates a SqlCommand to select rows from the Products table, adding a SqlParameter to restrict the results to rows with a UnitPrice greater than the specified parameter value, in this case 5. The SqlConnection is opened inside of a using block, which ensures that resources are closed and disposed when the code exits. The code executes the command by using a SqlDataReader, and displays the results in the console window.
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 = _
"Data Source=(local);Initial Catalog=Northwind;" _
& "Integrated Security=true"
' Provide the query string with a parameter placeholder.
Dim queryString As String = _
"SELECT ProductID, UnitPrice, ProductName from dbo.Products " _
& "WHERE UnitPrice > @pricePoint " _
& "ORDER BY UnitPrice DESC;"
' Specify the parameter value.
Dim paramValue As Integer = 5
' Create and open the connection in a using block. This
' ensures that all resources will be closed and disposed
' when the code exits.
Using connection As New SqlConnection(connectionString)
' Create the Command and Parameter objects.
Dim command As New SqlCommand(queryString, connection)
command.Parameters.AddWithValue("@pricePoint", paramValue)
' Open the connection in a try/catch block.
' Create and execute the DataReader, writing the result
' set to the console window.
Try
connection.Open()
Dim dataReader As SqlDataReader = _
command.ExecuteReader()
Do While dataReader.Read()
Console.WriteLine( _
vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", _
dataReader(0), dataReader(1), dataReader(2))
Loop
dataReader.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Console.ReadLine()
End Using
End Sub
End Class
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString =
"Data Source=(local);Initial Catalog=Northwind;"
+ "Integrated Security=true";
// Provide the query string with a parameter placeholder.
string queryString =
"SELECT ProductID, UnitPrice, ProductName from dbo.products "
+ "WHERE UnitPrice > @pricePoint "
+ "ORDER BY UnitPrice DESC;";
// Specify the parameter value.
int paramValue = 5;
// Create and open the connection in a using block. This
// ensures that all resources will be closed and disposed
// when the code exits.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
// Create the Command and Parameter objects.
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@pricePoint", paramValue);
// Open the connection in a try/catch block.
// Create and execute the DataReader, writing the result
// set to the console window.
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}\t{2}",
reader[0], reader[1], reader[2]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
[Top]
OleDb
The code in this example assumes that you can connect to the Microsoft Access Northwind sample database. The code creates a OleDbCommand to select rows from the Products table, adding a OleDbParameter to restrict the results to rows with a UnitPrice greater than the specified parameter value, in this case 5. The OleDbConnection is opened inside of a using block, which ensures that resources are closed and disposed when the code exits. The code executes the command by using a OleDbDataReader, and displays the results in the console window.
Option Explicit On
Option Strict On
Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class Program
Public Shared Sub Main()
' The connection string assumes that the Access
' Northwind.mdb is located in the c:\Data folder.
Dim connectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& "c:\Data\Northwind.mdb;User Id=admin;Password=;"
' Provide the query string with a parameter placeholder.
Dim queryString As String = _
"SELECT ProductID, UnitPrice, ProductName from Products " _
& "WHERE UnitPrice > ? " _
& "ORDER BY UnitPrice DESC;"
' Specify the parameter value.
Dim paramValue As Integer = 5
' Create and open the connection in a using block. This
' ensures that all resources will be closed and disposed
' when the code exits.
Using connection As New OleDbConnection(connectionString)
' Create the Command and Parameter objects.
Dim command As New OleDbCommand(queryString, connection)
command.Parameters.AddWithValue("@pricePoint", paramValue)
' Open the connection in a try/catch block.
' Create and execute the DataReader, writing the result
' set to the console window.
Try
connection.Open()
Dim dataReader As OleDbDataReader = _
command.ExecuteReader()
Do While dataReader.Read()
Console.WriteLine( _
vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", _
dataReader(0), dataReader(1), dataReader(2))
Loop
dataReader.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Console.ReadLine()
End Using
End Sub
End Class
using System;
using System.Data;
using System.Data.OleDb;
class Program
{
static void Main()
{
// The connection string assumes that the Access
// Northwind.mdb is located in the c:\Data folder.
string connectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ "c:\\Data\\Northwind.mdb;User Id=admin;Password=;";
// Provide the query string with a parameter placeholder.
string queryString =
"SELECT ProductID, UnitPrice, ProductName from products "
+ "WHERE UnitPrice > ? "
+ "ORDER BY UnitPrice DESC;";
// Specify the parameter value.
int paramValue = 5;
// Create and open the connection in a using block. This
// ensures that all resources will be closed and disposed
// when the code exits.
using (OleDbConnection connection =
new OleDbConnection(connectionString))
{
// Create the Command and Parameter objects.
OleDbCommand command = new OleDbCommand(queryString, connection);
command.Parameters.AddWithValue("@pricePoint", paramValue);
// Open the connection in a try/catch block.
// Create and execute the DataReader, writing the result
// set to the console window.
try
{
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}\t{2}",
reader[0], reader[1], reader[2]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
[Top]
Odbc
The code in this example assumes that you can connect to the Microsoft Access Northwind sample database. The code creates a OdbcCommand to select rows from the Products table, adding a OdbcParameter to restrict the results to rows with a UnitPrice greater than the specified parameter value, in this case 5. The OdbcConnection is opened inside of a using block, which ensures that resources are closed and disposed when the code exits. The executes the command by using a OdbcDataReader, and displays the results in the console window.
Option Explicit On
Option Strict On
Imports System
Imports System.Data
Imports System.Data.Odbc
Public Class Program
Public Shared Sub Main()
' The connection string assumes that the Access
' Northwind.mdb is located in the c:\Data folder.
Dim connectionString As String = _
"Driver={Microsoft Access Driver (*.mdb)};" _
& "Dbq=c:\Data\Northwind.mdb;Uid=Admin;Pwd=;"
' Provide the query string with a parameter placeholder.
Dim queryString As String = _
"SELECT ProductID, UnitPrice, ProductName from Products " _
& "WHERE UnitPrice > ? " _
& "ORDER BY UnitPrice DESC;"
' Specify the parameter value.
Dim paramValue As Integer = 5
' Create and open the connection in a using block. This
' ensures that all resources will be closed and disposed
' when the code exits.
Using connection As New OdbcConnection(connectionString)
' Create the Command and Parameter objects.
Dim command As New OdbcCommand(queryString, connection)
command.Parameters.AddWithValue("@pricePoint", paramValue)
' Open the connection in a try/catch block.
' Create and execute the DataReader, writing the result
' set to the console window.
Try
connection.Open()
Dim dataReader As OdbcDataReader = _
command.ExecuteReader()
Do While dataReader.Read()
Console.WriteLine( _
vbTab & "{0}" & vbTab & "{1}" & vbTab & "{2}", _
dataReader(0), dataReader(1), dataReader(2))
Loop
dataReader.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Console.ReadLine()
End Using
End Sub
End Class
using System;
using System.Data;
using System.Data.Odbc;
class Program
{
static void Main()
{
// The connection string assumes that the Access
// Northwind.mdb is located in the c:\Data folder.
string connectionString =
"Driver={Microsoft Access Driver (*.mdb)};"
+ "Dbq=c:\\Data\\Northwind.mdb;Uid=Admin;Pwd=;";
// Provide the query string with a parameter placeholder.
string queryString =
"SELECT ProductID, UnitPrice, ProductName from products "
+ "WHERE UnitPrice > ? "
+ "ORDER BY UnitPrice DESC;";
// Specify the parameter value.
int paramValue = 5;
// Create and open the connection in a using block. This
// ensures that all resources will be closed and disposed
// when the code exits.
using (OdbcConnection connection =
new OdbcConnection(connectionString))
{
// Create the Command and Parameter objects.
OdbcCommand command = new OdbcCommand(queryString, connection);
command.Parameters.AddWithValue("@pricePoint", paramValue);
// Open the connection in a try/catch block.
// Create and execute the DataReader, writing the result
// set to the console window.
try
{
connection.Open();
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}\t{2}",
reader[0], reader[1], reader[2]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
[Top]
OracleClient
The code in this example assumes a connection to DEMO.CUSTOMER on an Oracle server. You must also add a reference to the System.Data.OracleClient.dll. The code returns the data in an OracleDataReader.
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 = _
"Data Source=ThisOracleServer;Integrated Security=yes;"
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
End Class
using System;
using System.Data;
using System.Data.OracleClient;
class Program
{
static void Main()
{
string connectionString =
"Data Source=ThisOracleServer;Integrated Security=yes;";
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);
}
}
}
}
[Top]
Entity Framework Examples
The following code listings demonstrate how to retrieve data from a data source by querying entities in an Entity Data Model (EDM). These examples use the Northwind model. For more information, see Introducing the Entity Framework.
LINQ to Entities
The code in this example uses a LINQ query to return data as Categories objects, which are projected as an anonymous type that contains only the CategoryID and CategoryName properties. For more information, see LINQ to Entities Overview.
Option Explicit On
Option Strict On
Imports System
Imports System.Linq
Imports System.Data.Objects
Imports NorthwindModel
Class LinqSample
Public Shared Sub ExecuteQuery()
Using context As NorthwindEntities = New NorthwindEntities()
Try
Dim query = From category In context.Categories _
Select New With _
{ _
.categoryID = category.CategoryID, _
.categoryName = category.CategoryName _
}
For Each categoryInfo In query
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
categoryInfo.categoryID, categoryInfo.categoryName)
Next
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Sub
End Class
using System;
using System.Linq;
using System.Data.Objects;
using NorthwindModel;
class LinqSample
{
public static void ExecuteQuery()
{
using (NorthwindEntities context = new NorthwindEntities())
{
try
{
var query = from category in context.Categories
select new
{
categoryID = category.CategoryID,
categoryName = category.CategoryName
};
foreach (var categoryInfo in query)
{
Console.WriteLine("\t{0}\t{1}",
categoryInfo.categoryID, categoryInfo.categoryName);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
[Top]
Typed ObjectQuery
The code in this example uses an ObjectQuery<T> to return data as Categories objects. For more information, see Object Queries (Entity Framework).
Option Explicit On
Option Strict On
Imports System
Imports System.Data.Objects
Imports NorthwindModel
Class ObjectQuerySample
Public Shared Sub ExecuteQuery()
Using context As NorthwindEntities = New NorthwindEntities()
Dim categoryQuery As ObjectQuery(Of Categories) = context.Categories
For Each category As Categories In _
categoryQuery.Execute(MergeOption.AppendOnly)
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
category.CategoryID, category.CategoryName)
Next
End Using
End Sub
End Class
using System;
using System.Data.Objects;
using NorthwindModel;
class ObjectQuerySample
{
public static void ExecuteQuery()
{
using (NorthwindEntities context = new NorthwindEntities())
{
ObjectQuery<Categories> categoryQuery = context.Categories;
foreach (Categories category in
categoryQuery.Execute(MergeOption.AppendOnly))
{
Console.WriteLine("\t{0}\t{1}",
category.CategoryID, category.CategoryName);
}
}
}
}
[Top]
EntityClient
The code in this example uses an EntityCommand to execute an Entity SQL query. This query returns a list of records that represent instances of the Categories entity type. An EntityDataReader is used to access data records in the result set. For more information, see EntityClient Provider for Entity Framework.
Option Explicit On
Option Strict On
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.EntityClient
Imports NorthwindModel
Class EntityClientSample
Public Shared Sub ExecuteQuery()
Dim queryString As String = _
"SELECT c.CategoryID, c.CategoryName " & _
"FROM NorthwindEntities.Categories AS c"
Using conn As EntityConnection = _
New EntityConnection("name=NorthwindEntities")
Try
conn.Open()
Using query As EntityCommand = _
New EntityCommand(queryString, conn)
Using rdr As DbDataReader = _
query.ExecuteReader(CommandBehavior.SequentialAccess)
While rdr.Read()
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
rdr(0), rdr(1))
End While
End Using
End Using
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Sub
End Class
using System;
using System.Data;
using System.Data.Common;
using System.Data.EntityClient;
using NorthwindModel;
class EntityClientSample
{
public static void ExecuteQuery()
{
string queryString =
@"SELECT c.CategoryID, c.CategoryName
FROM NorthwindEntities.Categories AS c";
using (EntityConnection conn =
new EntityConnection("name=NorthwindEntities"))
{
try
{
conn.Open();
using (EntityCommand query = new EntityCommand(queryString, conn))
{
using (DbDataReader rdr =
query.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (rdr.Read())
{
Console.WriteLine("\t{0}\t{1}", rdr[0], rdr[1]);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
[Top]
LINQ to SQL
The code in this example uses a LINQ query to return data as Categories objects, which are projected as an anonymous type that contains only the CategoryID and CategoryName properties. This example is based on the Northwind data context. For more information, see Getting Started (LINQ to SQL).
Option Explicit On
Option Strict On
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports Northwind
Class LinqSqlSample
Public Shared Sub ExecuteQuery()
Using db As NorthwindDataContext = New NorthwindDataContext()
Try
Dim query = From category In db.Categories _
Select New With _
{ _
.categoryID = category.CategoryID, _
.categoryName = category.CategoryName _
}
For Each categoryInfo In query
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
categoryInfo.categoryID, categoryInfo.categoryName)
Next
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Sub
End Class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Northwind;
class LinqSqlSample
{
public static void ExecuteQuery()
{
using (NorthwindDataContext db = new NorthwindDataContext())
{
try
{
var query = from category in db.Categories
select new
{
categoryID = category.CategoryID,
categoryName = category.CategoryName
};
foreach (var categoryInfo in query)
{
Console.WriteLine("vbTab {0} vbTab {1}",
categoryInfo.categoryID, categoryInfo.categoryName);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
[Top]
See Also
Other Resources
Retrieving and Modifying Data in ADO.NET
Creating Client Data Applications
Querying an Entity Data Model (Entity Framework Tasks)
How to: Execute a Query that Returns an Anonymous Type (Entity Framework)