Friday, October 21, 2011
Database Connection in ado.net
Abstract: This series on ADO.NET continues with a look at connecting to your database. In particular, a number of IDbConnection-implementing classes are discussed, including that found in BDP.NET, the Borland Data Provider.
As you learned in the first article in this series, classes that implement the IDbConnection interface are used to establish a connection to a data source. In most cases, this will be a database associated with a particular database server. However, since both OLE DB, the COM (component object model) layer associated with ActiveX Data Objects (ADO), and ODBC (open database connectivity) are supported, it is possible to connect to any data source for which there are OLE DB providers or ODBC drivers, such as Paradox, dBase, and MS Access.
Although this article is focused on connecting to databases, it is worth noting that ADO.NET can also work with data stored in properly formatted XML (extensible markup language) files. Working with data in XML files with ADO.NET is discussed in depth in a latter article in this series.
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.
Borland offers something a little different in its C#Builder product, and it is anticipated that this will also be available in Octane, Borland's Delphi for the Microsoft .NET Framework. The classes of BDP.NET are not designed to work with a particular database. Instead, similar to dbExpress, they are designed to use a database-specific driver to connect to one of a variety of databases. In C#Builder, BDP.NET shipped with four drivers, but the driver interface is open, permitting database vendors to create their own BDP.NET drivers. The primary advantage of BDP.NET is that, in addition to supporting ADO.NET data access, they are supported by special editors that greatly simplify the process of connecting to your data, and also offer live, design-time views of your connected data.
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.
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.
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.
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.
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.
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.
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
In addition to BDP.NET's component designers, the BdpDataAdapter, the BDP.NET implementation of the IDbDataAdapter interface, provides you with a live, design-time view of your connected data. This also means that the BdpConnection component can be activated at design time. While Delphi, C++Builder, and Kylix developers are accustom to this feature, it is one that is not supported natively by the FCL version 1.1 ADO.NET components.
The following figure shows a C# application project begin configured in C#Builder. A DataGrid has been placed in the WinForm that appears in the designer, and a BdpConnection, BdpCommand, and BdpDataAdapter have been added to the project, as well as a DataSet.
You use the Connections Editor to easily configure which of the BDP.NET drivers you want to use to connect to your database. To display the Connections Editor, right-click a BdpConnection and select Connection Editor. The following figure shows the Connections Editor being used to configure the BdpConnection to connected to InterBase.
Once the BdpConnection has been configured, you set the BdpCommand's Connection property to point to the BdpConnection, and its CommandText property to hold the SQL SELECT statement that defines the data you want to access. Both of these can easily be configured using the Command Text Editor. The Command Text Editor is shown in the following figure, where it has been configured to select all fields from the customer table in the employee.gdb database that BdpConnection1 points to. To display the Command Text Editor, select the BdpCommand's CommandText property and click the ellipsis that appears.
Once the BdpCommand has been configured, there are a few properties that you need to set on the BdpDataAdapter. Specifically, set the BdpDataAdapter's SelectCommand property to the BdpCommand that contains your SQL SELECT statement, the DataSet property to a DataSet, and the Active property to True. Once that is done, you can set the DataGrid's DataSource property to the DataSet, and the DataMember property to a DataTable in the DataSet (the default names for DataTables use the following pattern: Table1, Table2, and so on).
The following figure shows a DataGrid in C#Builder displaying the data from a live, design-time connection.
Although this article is focused on connecting to databases, it is worth noting that ADO.NET can also work with data stored in properly formatted XML (extensible markup language) files. Working with data in XML files with ADO.NET is discussed in depth in a latter article in this series.
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.
Borland offers something a little different in its C#Builder product, and it is anticipated that this will also be available in Octane, Borland's Delphi for the Microsoft .NET Framework. The classes of BDP.NET are not designed to work with a particular database. Instead, similar to dbExpress, they are designed to use a database-specific driver to connect to one of a variety of databases. In C#Builder, BDP.NET shipped with four drivers, but the driver interface is open, permitting database vendors to create their own BDP.NET drivers. The primary advantage of BDP.NET is that, in addition to supporting ADO.NET data access, they are supported by special editors that greatly simplify the process of connecting to your data, and also offer live, design-time views of your connected data.
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;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";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";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();Connection: SqlConnection;
Adapter: SqlDataAdapter;
CustDataSet: DataSet;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'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";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";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";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");connection = new OdbcConnection("DSN=EasySoft IB DBC");Connecting to Data Using BDP.NET
The underlying process of connecting to data using BDP.NET is no different than for other ADO.NET mechanisms. Before you can open a connection, you must supply a connection string with the parameters that the underlying driver requires to access the data. What is different is the support that you get at design time. Specifically, BDP.NET supplies a rich collection of component designers, including the Connection Editor, the Command Text Editor, the Data Adapter Configuration dialog box, and the Data Explorer.In addition to BDP.NET's component designers, the BdpDataAdapter, the BDP.NET implementation of the IDbDataAdapter interface, provides you with a live, design-time view of your connected data. This also means that the BdpConnection component can be activated at design time. While Delphi, C++Builder, and Kylix developers are accustom to this feature, it is one that is not supported natively by the FCL version 1.1 ADO.NET components.
The following figure shows a C# application project begin configured in C#Builder. A DataGrid has been placed in the WinForm that appears in the designer, and a BdpConnection, BdpCommand, and BdpDataAdapter have been added to the project, as well as a DataSet.
You use the Connections Editor to easily configure which of the BDP.NET drivers you want to use to connect to your database. To display the Connections Editor, right-click a BdpConnection and select Connection Editor. The following figure shows the Connections Editor being used to configure the BdpConnection to connected to InterBase.
Once the BdpConnection has been configured, you set the BdpCommand's Connection property to point to the BdpConnection, and its CommandText property to hold the SQL SELECT statement that defines the data you want to access. Both of these can easily be configured using the Command Text Editor. The Command Text Editor is shown in the following figure, where it has been configured to select all fields from the customer table in the employee.gdb database that BdpConnection1 points to. To display the Command Text Editor, select the BdpCommand's CommandText property and click the ellipsis that appears.
Once the BdpCommand has been configured, there are a few properties that you need to set on the BdpDataAdapter. Specifically, set the BdpDataAdapter's SelectCommand property to the BdpCommand that contains your SQL SELECT statement, the DataSet property to a DataSet, and the Active property to True. Once that is done, you can set the DataGrid's DataSource property to the DataSet, and the DataMember property to a DataTable in the DataSet (the default names for DataTables use the following pattern: Table1, Table2, and so on).
The following figure shows a DataGrid in C#Builder displaying the data from a live, design-time connection.
