ADO.Net
Making the Connection: Accessing Your Database using ADO.NET
Data Access Namespaces
Within the .NET framework class library (FCL) version 1.1, there are
five third-level namespaces that provide access to an underlying
database. These are System.Data.SqlClient, System.Data.SqlServerCE,
System.Data.OracleClient, System.Data.OleDb, and System.Data.Odbc. As
their names imply, these are used to connect to MS SQL Server, MS SQL
Server Compact Edition, Oracle, as well as data sources supported by
OLE DB Providers and ODBC (open database connectivity) drivers,
respectively.
While the classes and types in the System.Data.OleDb and
System.Data.Odbc namespaces permit you to connect to almost any data
source, they have one drawback. Specifically, they require that the
associated OLE DB Provider or ODBC driver be installed on each
workstation. For some developers this does not pose much of a problem.
For others, however, the additional installation requirements may make
these alternatives unattractive.
In addition to the namespaces in the FCL, many database vendors provide
their own ADO.NET drivers. For example, Oracle publishes the Oracle
Data Provider for .NET (ODP.NET) in the Oracle.DataAccess.Client
namespace, Advantage Database Server supplies a driver in the
Advantage.Data.Provider namespace, and IBM offers a .NET Data Provider
in the IBM.Data.DB2 namespace. In many cases, the vendor-specific
drivers offer additional features over the FCL native drivers. As a
result, it is often worthwhile to compare the performance of the
vendor-specific driver to the OLE DB or ODBC alternatives, so long as
deployment issues are not an issue.
The IDbConnection Interface
Regardless of which namespace you are using, the concrete classes that
you use to connect to a database implement the IDbConnection interface.
This interface, which is defined in the System.Data namespace, has few
methods and properties, all of which are self-explanatory.
There are five public methods in this interface. The Open and Close
methods are implemented to open and close a connection, respectively.
Unlike other data access options that you might have used before, with
ADO.NET it is important that each call to Open be associated with a
corresponding call to Close. For most ADO.NET connections, failure to
call Close may unnecessarily consume resources on your server.
BeginTransaction is implemented to initiate a transaction, while
ChangeDatabase is implemented to change the database the connection is
associated with. Finally, CreateCommand is implemented to return an
appropriate instance of a class that implements IDbCommand.
In addition, there are four public properties in the IDbConnection
interface. ConnectionString is used to get or set the parameters that
will be used to connect to a database. Most IDbConnection implementing
classes permit the connection string to also be set through a parameter
of the class constructor. ConnectionTimeout is used to set or get the
number of seconds after which an attempt to connect will be aborted,
and Database is used to read the name of the database that the
connection will use.
Finally, State is used to read the status of the connection. State is a
ConnectionState property, which is an enumeration. Valid
ConnectionState values include ConnectionState.Open,
ConnectionState.Closed, ConnectionState.Connecting,
ConnectionState.Executing, ConnectionState.Fetching, and
ConnectionState.Broken.
Connecting to Data
Connecting to a database is actually straightforward. Using an instance
of an IDbConnection-implementing class, you define the connection
string and then call the Open method. As mentioned in the preceding
section, the connection string can be set using the ConnectionString
property, but most developers prefer to pass the connection string as a
parameter to the connection constructor.
The connection string consists of a list of name/value pairs that
define how to connect to a database. When a connection string consists
of more than one name/value pair, and most require more than one, the
parameters are separated by semicolons. At a minimum, the connection
string will define on which server the database is running (for remote
database servers), which database to use, and often a user name and
password with data access privileges.
Each IDbConnection-implementing class defines its own connection string
parameters. If you are using one of the five connection-types defined
in the FCL version 1.1, you can find the connection string parameters
in the .NET Framework SDK Documentation. If you are using some other
namespace, you should refer to the documentation provided by the vendor
who publishes that namespace.
The following sections demonstrate how to connect to a variety of
databases using the IDbConnection-implementing classes in the
System.Data.SQLClient, System.Data.OleDb, and System.Data.Odbc
namespaces. In each of these examples a connection is used to populate
a DataTable in a DataSet, and that data is displayed in a DataGrid.
In addition to the connection object, each of these examples employ a
DataSet and a class that implements IDbDataAdapter. These classes will
be discussed in a later article in this series. Consequently, the
following discussions will not go into detail about what or how these
classes are being used.
Connecting to data using BDP.NET is demonstrated at the end of this
article.
Connecting to SQL Server
You connect to SQL server using a SqlConnection instance. The following
code segment shows the variable declarations of the SqlConnectin
object, as well as the supporting classes in C#.
public SqlConnection connection;
public SqlDataAdapter dataadapter;
public DataSet dataset;When calling the constructor of this class you
can pass the connection string as a parameter. This is demonstrated in
the following code segment.
connection = new SqlConnection("Persist Security Info=False;" +
"Integrated Security=SSPI;database=northwind;" +
"server=localhost;Connect Timeout=30");
connection.Open();
dataadapter = new SqlDataAdapter("select * from customers",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";Regardless of which connection class
you are using, you can typically can set the connection string using
the connection string property. An example of this is shown in the
following code segment.
connection = new SqlConnection();
connection.ConnectionString = "Persist Security Info=False;" +
"Integrated Security=SSPI;database=northwind;" +
"server=localhost;Connect Timeout=30";
connection.Open();
dataadapter = new SqlDataAdapter("select * from customers",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";The following Windows form shows the
contents of the customers table from the Northwind database displayed
in a DataGrid.
As you learned earlier, it is very important to close a connection once
you are done with it. In the preceding C# project, the following call
to close the connection can be found in the Closing event of the
Windows form to which this code is attached.
connection.Close();Using Delphi, the variable declarations look like
the following:
Connection: SqlConnection;
Adapter: SqlDataAdapter;
CustDataSet: DataSet;The following shows you how to establish the
connection, populate the DataTable, and display the data in the
DataGrid using Delphi:
Connection := SqlConnection.Create('Persist Security Info=False;' +
'Integrated Security=SSPI;database=northwind;' +
'server=localhost;Connect Timeout=30');
Connection.Open;
CustDataSet := DataSet.Create;
Adapter := SqlDataAdapter.Create('select * from customers',
Connection);
Adapter.Fill(CustDataSet, 'customers');
DataGrid1.DataSource := CustDataSet;
DataGrid1.DataMember := 'customers'For the remainder of this article,
the examples are shown using C#.
Connecting Using OLE DB Providers
OLE DB providers are COM-based classes that implement a standard set of
COM interfaces in order to connect to a data source. While non-database
data sources are supported by OLE DB providers, the following
discussion will only employ database-related OLE DB providers.
OLE DB providers are part of the Microsoft Data Access Component (MDAC)
framework. The .NET framework version 1.1 supports MDAC 2.6 or later.
However, MDAC 2.7 or later is recommended. And, at the time of this
writing, version 2.8 is the most recent.
The following code segment demonstrates how to create a connect to a
Microsoft Access database using an OleDbConnection.
connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=C:\\Program Files\\Common Files\\Borland Shared\\Data" +
"\\dbdemos.mdb;Persist Security Info=False");
connection.Open();
dataadapter = new OleDbDataAdapter("select * from customer",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";In this case, a simple connection
string identifies the driver (Microsoft's Jet engine OLE DB provider)
and the database (a MS Access database that is installed along with
Borland Delphi, Kylix, and C++Builder products). All other parameters
that are associated with this driver will assume their default values.
Instead of using a connection string that identifies the various
parameters to use to connect to an OLE DB provider's data source, you
can instead reference a data link (*.udl) file. Data link files permit
you to maintain database connection information outside your
application, much like the feature provided by BDE configuration files
(idapi32.cfg).
The following example demonstrates how to connect to the same database
as above. However, this time, the dbdemos.udl file located in the
c:\program files\common files\system\ole db\data links folder is used.
connection =
new OleDbConnection("FILE NAME=" +
"C:\\Program Files\\Common Files\\System\\" +
"OLE DB\\Data Links\\dbdemos.udl");
connection.Open();
dataadapter = new OleDbDataAdapter("select * from customer",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";Note that the data link file is parsed
each time a connection is opened. If you application must establish
many different connections, the use of data link files may reduce
application performance.
Connecting Using ODBC
ODBC (open database connectivity) is a broadly supported, SQL-based
standard for accessing databases using the Windows operating systems.
To connect to a database using an ODBC driver you use the
OdbcConnection class, located in the System.Data.Odbc third-level
namespace.
You can make a connection to an ODBC database using either an ODBC
connection string, or a reference to a configured ODBC data source.
The following example demonstrates connecting to an MS Access database
using an ODBC connection string. MS Access is used in this example
because its connection string is simple, considering that the database
does not require a user name or password.
connection =
new OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};"+
"DBQ=c:\\program files\\common files\\"+
"borland shared\\data\\dbdemos.mdb");
connection.Open();
dataadapter = new OdbcDataAdapter("select * from customer",
connection);
dataset = new DataSet();
dataadapter.Fill(dataset, "table1");
dataGrid1.DataSource = dataset;
dataGrid1.DataMember = "table1";As in the OLE DB provider example shown
earlier in this paper, the MS Access database that this connection
connects to is the one installed by recent Borland products, such as
C++Builder or Delphi. This database is pointed to by the DBQ parameter
of the connection string used to connect to the database.
So long as you have a configured user, file, or system data source name
(DSN), you can use the parameters of the data source name instead of a
lengthy connection string to connect to your database using an ODBC
driver. The following OdbcConnection constructor show how can connect
to Paradox and InterBase databases, given that you have the necessary
installed drivers, and have configured data source names 'Paradox
DataSource' and 'EasySoft IB ODBC,' respectively
connection = new OdbcConnection("DSN=Paradox DataSource");and
connection = new OdbcConnection("DSN=EasySoft IB DBC");