Source Code: ADO.NET 2.0 The Series - Part One
ADO.NET 2.0 The Series - Part One
This is part one of a multi-part article about ADO.NET 2.0. Part one introduces the ADO.NET 2.0 Object Model and provides source code which implements the ADO.NET Connection, Command, and Reader objects using the System.Data.SqlClient data provider.
The source code .zip file included with part one contains:
Visual Basic 2005 Windows Forms Application
C# 2005 Windows Forms Application
Application Screen Shot
The application opens a connection to a Sql Server database (Northwind database), runs a select command on the Northwind database's Categories table, reads the results returned, and displays the results in a RichTextbox control.
ADO.NET Object Model
The ADO.NET object model below presents the high level components of ADO.NET. Spend some time studying the model but don't worry if it is not completely clear at this point. It will be covered in depth in other parts of this article.
Data Provider Components
.NET Data Provider Namespaces
System.Data.SqlClient - Sql Server Data Provider
System.Data.OleDB - OLE DB Data Provider
System.Data.Odbc - ODBC Data Provider
System.Data.OracleClient - Oracle Data Provider
A data provider includes Connection, Command, Reader, and Adapter objects.
Data provider components are specific to a data source.
.NET data providers contain the same objects, but their names and some of their properties and methods differ. Examples:
SqlClient Data Provider - SqlConnection, SqlCommand, SqlDataReader, SqlDataAdapter
OleDB Data Provider - OleDbConnection, OleDbCommand, OleDbDataReader, OleDbDataAdapter
Odbc Data Provider - OdbcDbConnection, OdbcDbCommand, OdbcDbDataReader, OdbcDbDataAdapter
Data Provider Component Objects
Connection Object
Physical connection to data source
Determines the data provider used
Determines the data source and database
|
|
|
| Adapter Object |
DataAdapter
Provides the bridge between a Connection object and a DataSet object
Contains four Command objects: Select, Insert, Update, and Delete |
|
| |
TableAdapter (new in ADO.NET 2.0)
Contains both a DataAdapter object and a Connection object.
Contains four Command objects: Select, Insert, Update, and Delete plus additional commands as defined by the programmer. |
|
Command Object
SQL statement or stored procedure to be executed at the data source.
Use standalone or with the DataAdapter or TableAdapter.
|
|
|
Reader Object
Provides a fast, forward-only, read-only stream of data from a data source.
Used with a Command object |
|
|
DataSet Object
Memory-resident
Always disconnected from the data source
Models tables and the relationships between them
Can contain tables from multiple data sources
Data Consumers
Data consumers are show with the ADO.NET objects in the ADO.NET Object Model to represent the applications, services, and other consumers that may call on the ADO.NET objects. In this article Windows Forms applications and ASP.NET applications will play the role of data consumers.
Implementing Connection, SqlCommand, and Reader Objects
The applications included in the .zip source code file demonstrate how to connect to a Sql Server, execute a Sql SELECT command, and read the results. Excerpts from the source code files are included below.
Visual Basic 2005 Code Excerpt
' Declare variable named theConnectionString of type String;
' Assign a connection string to theConnectionString variable.
' NOTE: To run this yourself replace the connection string with a connection string to a
' SQL Server with the Northwind database installed that you have permission to access.
Dim theConnectionString As String = "Data Source=az-4ffd1ee68bce;Initial Catalog=Northwind;Integrated Security=True"
' Declare a variable named theQueryString of type String;
' Assign a string containing a SQL SELECT command to theQueryString
Dim theQueryString As String = "SELECT CategoryID, CategoryName FROM dbo.Categories;"
' The Visual Basic 2005 'Using' keyword simplifies the use of Dispose methods by
' automatically calling dispose for an object.
' The keyword is used here to create a SqlConnection object.
Using theSqlConnection As New SqlConnection(theConnectionString)
' Declare variable named theSqlCommand of type SqlCommand
' calling theSqlConnection object's CreateCommand method
' and assigning the resulting SqlCommand object to theSqlCommand variable.
Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
' Assign theQueryString to theSqlCommand's CommandText property.
theSqlCommand.CommandText = theQueryString
Try
' Open theSqlConnection.
theSqlConnection.Open()
' Declare variable named theSqlDataReader of type SqlDataReader
' calling theSqlCommand object's ExecuteReader method
' and assigning the resulting cursor to theSqlDataReader varaiable.
Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
' Call theSqlDataReader object's Read method until then end
' of the cursor is reached.
Do While theSqlDataReader.Read()
' Append a formatted string containing column 0 and column 1 of the row read
' to the ResultsRichTextBox.
Me.ResultsRichTextBox.AppendText(String.Format("{0}" & vbTab & "{1}", _
theSqlDataReader(0), theSqlDataReader(1)) & vbCrLf)
Loop
' Close theSqlDataReader
theSqlDataReader.Close()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
C# 2005 Code Excerpt
// Declare variable named theConnectionString of type String;
// Assign a connection string to theConnectionString variable.
// NOTE: To run this yourself replace the connection string with a connection string to a
// SQL Server with the Northwind database installed that you have permission to access.
string theConnectionString = "Data Source=az-4ffd1ee68bce;Initial Catalog=Northwind;Integrated Security=True";
// Declare a variable named theQueryString of type String;
// Assign a string containing a SQL SELECT command to theQueryString variable.
string theQueryString = "SELECT CategoryID, CategoryName FROM dbo.Categories;";
// The C# 'Using' keyword simplifies the use of Dispose methods by
// automatically calling dispose for an object.
// The keyword is used here to create a SqlConnection object.
using (SqlConnection theSqlConnection = new SqlConnection(theConnectionString))
{
// Declare variable named theSqlCommand of type SqlCommand
// calling theSqlConnection object's CreateCommand method
// and assigning the resulting SqlCommand object to theSqlCommand variable.
SqlCommand theSqlCommand = theSqlConnection.CreateCommand();
// Assign theQueryString to theSqlCommand's CommandText property.
theSqlCommand.CommandText = theQueryString;
try
{
// Open theSqlConnection.
theSqlConnection.Open();
// Declare variable named theSqlDataReader of type SqlDataReader
// calling theSqlCommand object's ExecuteReader method
// and assigning the resulting cursor to theSqlDataReader varaiable.
SqlDataReader theSqlDataReader = theSqlCommand.ExecuteReader();
// Call theSqlDataReader object's Read method until then end
// of the cursor is reached.
while (theSqlDataReader.Read())
{
// Append a formatted string containing column 0 and column 1 of the row read
// to the ResultsRichTextBox.
this.resultsRichTextBox.AppendText(string.Format("{0}" + "\t" + "{1}", theSqlDataReader[0], theSqlDataReader[1]) + "\r");
}
// Close theSqlDataReader
theSqlDataReader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
For more information:
SqlConnection Class
SqlCommand Class
SqlDataReader Class
mike mcintyre http://www.getdotnetcode.com