What is ADO.NET
DOTNETPRO
programming classes (System.Data namespace) that render the data-access
services of the .NET Framework. ADO.NET is an advancement of ADO and is
built around disconnected and n-tier programming environments. XML is
an integral part of ADO.NET and the XML class framework in .NET and
ADO.NET are components of the same architecture. ADO.NET supports XML
from underpinning. XML in ADO.NET eases the process of sharing the
disconnected data between components. With the hand-in-hand, integrated
model of XML and ADO.NET Framework, you can access both XML and
relational data.
The ADO.NET object model consists of two key components:
-----------------------------------------------------------------------------------------------
Connected model: .NET Data Provider-a set of components including the
Connection, Command, DataReader, and DataAdapter objects.
Disconnected model (DataSet).
The .NET Data Provider enables you to execute commands directly against
the data source. With the .NET Data Provider, you can connect to a data
source, execute commands against the data source, and read data
directly from the data store or into a DataSet.
There are two primary .NET Data Providers for use with databases:
---------------------------------------------------------------------------------------------------
The OLE DB .NET Data Provider is suitable for use with any data source
that is not Microsoft's SQL Server 7.0 or higher. OLE DB .NET Data
Provider classes are located in the System.Data.OleDb namespace. To use
the OLE DB .NET Data Provider, you must import the System.Data.OleDb
namespace.
The SQL Server .NET Data Provider is intended to work directly with
Microsoft SQL Server 7.0 or later. To use the SQL Server .NET Data
Provider, you must import the System.Data.SqlClient namespace.
Following are the four core objects that constitute a .NET Data
Provider:
-------------------------------------------------------------------------------------------------------------
Connection (a class that implements IDbConnection) and Command (a class
that implements IDbCommand) are used to open a connection to a data
source and execute a command against it.
The DataReader (a class that implements the IDataReader interface) is
the read-only, forward cursor over a rowset of ADO.NET data. It's
highly optimized, nonbuffering, and analogous to a firehose cursor in
SQL Server.
The DataAdapter (a class derived from DbDataAdapter that implements
IDbDataAdapter) provides the association between the .NET Data
Providers and the DataSet. The DataAdapter is an integral part of
ADO.NET managed providers, the set of objects used to communicate
between a data source and a DataSet. It represents a set of Commands
and a Connection that are used to retrieve results from a data source
and fill a DataSet. The DataAdapter reads the data from a database into
a DataSet. It also writes the changed data from the DataSet to the
database.
The DataSet is a collection of disconnected recordsets. The DataSet is
the core component of the disconnected architecture of ADO.NET.
Datasets store data in a disconnected cache. It represents an in-memory
cache of data, and is explicitly designed such that it can access data
independent of any data source. Therefore, it can be used with multiple
and differing data sources and XML data. The DataSet consists of a
collection of DataTable objects that you can relate to each other with
DataRelation objects. A DataSet reads and writes data and schema as XML
documents.
Connecting to a Database
-------------------------------------------------
To access data in a database, you must first establish a connection
using an ADO.NET connection object. Two connection objects are included
in the .NET Framework: the OleDbConnection object, for working with the
same OLE DB data providers you would access through traditional ADO,
and the SqlConnection object, for optimized access to Microsoft SQL
Server. Because these examples connect to the Microsoft Jet Database,
you'll be using the OleDbConnection object. To create an object
variable of type OleDbConnection and initialize the variable to a new
connection, you could use a statement such as the following:
OleDbConnection con = new OleDbConnection();
To use ADO.NET, the first step that you need to take is to add the
proper Namespace to your project. Double-click the form now to access
its events. Scroll to the top of the class and add the following using
statement on the line below the other using statements:
using System.Data.OleDb;
Tto create a module-level variable to hold the connection, so place the
cursor below the left bracket ({) that follows the statement public
class fclsMain : System.Windows.Forms.Form and press Enter to create a
new line. Enter the following statement:
OleDbConnection con = new OleDbConnection();
Before using this connection, you must specify the data source to which
you want to connect. This is done through the ConnectionString property
of the ADO.NET connection object. The ConnectionString contains
connection information such as the name of the provider, username, and
password. The ConnectionString might contain many connection
parameters; the set of parameters available varies depending on the
source of data that you are connecting to.
Some of the parameters used in the OLE DB ConnectionString are:
---------------------------------------------------------------------------------------------------
Provider= The name of the data provider (Jet, SQL, and so on) to use.
Data Source= The name of the data source (database) to connect to.
UID= A valid username to use when connecting to the data source.
PWD= A password to use when connecting to the data source.
DRIVER= The name of the database driver to use. This isn't required if
a DSN is specified.
SERVER= The network name of the data source server
To specify the ConnectionString property of your ADO.NET connection,
place the following statement in the Load event of your form:
con.ConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\project\mydb.mdb";
After the connection string is defined, a connection to a data source
is established by using the Open() method of the connection object.
con.Open();
Closing a Connection to a Data Source
------------------------------------------------------------
You should always explicitly close a connection to a data source. This
means that you should not rely on a variable going out of scope to
close a connection, but you should force an explicit disconnect via
code. This is accomplished by calling the Close() method of the
connection object.
con.Close();
Manipulating Data
---------------------------------------------------
The easiest way to manipulate data using ADO.NET is to create a
DataTable object containing the resultset of a table, query, or stored
procedure. Using a DataTable, you can add, edit, delete, find, and
navigate records. The following sections explain how to use DataTables.
Understanding DataTables
DataTables contain a snapshot of the data in the data source. You
generally start by filling a DataTable, and then you manipulate the
results of the DataTable before finally sending the changes back to the
data source. The DataTable is populated using the Fill() method of a
DataAdapter object, and changes are sent back to the database using the
Update() method of a DataAdapter. Any changes made to the DataTable
appear only in the local copy of the data until you call the Update()
method. Having a local copy of the data reduces contention by
preventing users from blocking others from reading the data while it is
being viewed. This is similar to the Optimistic Batch Client Cursor in
ADO.
Creating a DataAdapter
To populate a DataTable, you need to create a DataAdapter, an object
that provides a set of properties and methods to retrieve and save data
between a DataSet and its source data. The DataAdapter you're going to
create will use the connection you've already defined to connect to the
data source and will then execute a query you'll provide. The results
of that query will be pushed into a DataTable.
Just as two ADO.NET connection objects are in the .NET Framework, there
are two ADO.NET DataAdapter Objects as well: the OleDbDataAdapter and
the SqlDataAdapter. Again, you'll be using the OleDbDataAdapter because
you aren't connecting to Microsoft SQL Server.
The constructor for the DataAdapter optionally takes the command to
execute when filling a DataTable or DataSet, as well as a connection
specifying the data source. (You could have multiple connections open
in a single project.) This constructor has the following syntax:
OleDbDataAdapter da = new OleDbDataAdapter([CommandText],[Connection]);
To add the DataAdapter to your project, first add the following
statement immediately below the statement you entered to declare the
"con" object.
OleDbDataAdapter da= new OleDbDataAdapter();
Next, add the following statement to the Load event of the form,
immediately following the statement that creates the connection:
da = new OleDbDataAdapter("Select * From customer",con);
Creating and Populating DataTables
------------------------------------------------------------------
You're going to create a module-level DataTable in your project. First,
create the DataTable variable by adding the following statement on the
line below the statement you entered previously to declare a new
module-level "da" object:
DataTable dtCustomer = new DataTable();
You are going to use an integer variable to keep track of the user's
current position within the DataTable. To do this, add the following
statement immediately below the statement you just entered to declare
the new DataTable object:
int m_rowPosition = 0;
Next, add the following statement to the Load event of the form,
immediately following the statement that creates the CommandBuilder:
da.Fill(dtCustomer);
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Rest in Next Topic>>>