Creating a Database Object

You can create a Database object by using a factory or by directly constructing one. The factory uses configuration information to determine the connection string, ADO.NET data provider, and the appropriate Data Access Application Block Database-derived object to construct. Alternatively, you can directly construct a Database object by passing any required information to the object's constructor.

You can also use the Unity Integration approach to create instances of the objects in the Data Access Application Block. For more details of the integration of Enterprise Library and the Unity Application Block, see Creating Objects Using the Unity Application Block.

Use a factory or the Unity Integration approach when you want to use the configuration information stored in a location supported by Enterprise Library or when you want to use connection strings managed by ADO.NET. For example, use a factory to create a Database object constructed with connection string information stored in the <connectionStrings> section in the application configuration file. You can also use the factory to construct a Database object with a connection string that is stored in an alternative configuration source. You must configure your application to use a different default configuration source to allow the factory to create objects with connection strings stored in that source. Use a constructor when you are retrieving connection string information from some source other than your default configuration source.

The CreateDatabase method is a static method of the DatabaseFactory class. The factory creates the correct database class based on information in the configuration file and returns an object of a subtype of the base class type, Database, to the client code. If you do not need commands that are specific to a particular database type, such as SQL Server, you should keep the application database-agnostic by using only the methods available on the base Database class. The specific Database-derived type that is created is transparent to the application code; because of this, it remains the same regardless of the type of database being used.

You can use the CreateDatabase method to specify that the type of database to be created is based on the default configuration. By changing the default configuration, the unmodified application can be run against different databases.

You can also use a named instance of a database, such as "Customers," within the application. The factory uses the ADO.NET connection string information in the configuration file to look up the information associated with that particular named instance to create the appropriate database type.

If you need to use commands that are specific to one database or another, you must specify, through downcasting, the database type you expect to be created by the factory.

Finally, you can bypass the application's configuration information and directly create a Database object of a Database object subtype. To do this, you must know the type of database you want to create, as well as the connection string and any other information required by the subtype.

Creating a Default Database

A call to the CreateDatabase method with no parameters indicates that the factory should create the default database. The configuration file determines which of the named instances is the default instance. You can use the Configuration Console to change the default instance.

The following code shows how to create a database object that has been marked as the default instance.

Database dbSvc = DatabaseFactory.CreateDatabase();

'Usage
Dim dbSvc As Database = DatabaseFactory.CreateDatabase()

Using Instances

Using instances, you can refer to a database in your application code by a logical name and change the database configuration information (such as location or connection string information) without recompiling your code.

The following example shows how to create a database with the name "Sales."

// Use a named database instance that refers to an arbitrary database type, 
// which is determined by configuration information.
Database myDb = DatabaseFactory.CreateDatabase("Sales");

'Usage
' Use a named database instance that refers to an arbitrary database type, 
' which is determined by configuration information.
Dim myDb As Database = DatabaseFactory.CreateDatabase("Sales")

Creating a Specific Database Type

If you must use methods that are specific to a particular database type, specify the database type when you create the database. The following code requests that the factory create a SqlDatabase object.

// Create a SQL database.
SqlDatabase dbSQL = DatabaseFactory.CreateDatabase("Sales") as SqlDatabase;

'Usage
' Create a SQL database.
Dim dbSQL As SqlDatabase = DirectCast(DatabaseFactory.CreateDatabase("Sales"), SqlDatabase)

Similarly, to create an Oracle database, use the OracleDatabase type. To create a SQL Server CE database, use the SqlCeDatabase type.

Creating a Database without Configuration

You can create a database object without using configuration data by supplying a connection string to the database class constructor. The following code shows how to create a SqlDatabase object.

// Assume your application contains the routine GetConnectionString. 
string myConnectionString = GetConnectionString();

SqlDatabase sqlDatabase = new SqlDatabase(myConnectionString);

'Usage
' Assume your application contains the routine GetConnectionString. 
Dim myConnectionString As String = GetConnectionString()

Dim db As SqlDatabase = New SqlDatabase(myConnectionString)  

The following code shows how to create a GenericDatabase object. You must supply the connection string and a DbProviderFactory object. In this case, the DbProviderFactory object is the OdbcFactory.

GenericDatabase db = new GenericDatabase(connectionString, OdbcFactory.Instance);

'Usage
Dim db As GenericDatabase = New GenericDatabase(connectionstring, OdbcFactory.Instance)  

Using SQL Server CE

SQL Server CE is a small, in-process database that provides the essential features of a relational database and is intended for desktop and mobile applications that need a local data store but do not require the full functionality of SQL Server. Each database is stored in a file that, by default, has an .sdf file name extension. To create a new, empty database use the CreateFile method. This method uses the file name from the connection string.

For SQL Server CE, opening a connection opens the database file. As a result, creating and releasing connections for each request would be quite slow. To avoid these performance problems, an application that uses SQL Server CE typically keeps a connection open for as long as it uses the database.

The first time you call a Database class method, the provider creates an additional "keep alive" connection that keeps the database engine in memory. The application creates and closes other connections for each Database class method call, but closing these connections does not close the "keep alive" connection.

To open a database, open a connection to it with the CreateConnection method. This method creates the "keep alive" connection. When you are finished using the database, you must explicitly close the "keep alive" connection to the database with the CloseSharedConnection method. There is only one "keep alive" connection for each connection string, although you can have multiple connections open for the same connection string.

Because SQL Server CE is an in-process database, making multiple calls to the database is fast and efficient. Consequently, SQL Server CE does not support stored procedures. If you try to use any of the Execute methods, such as ExecuteScalar and ExecuteNonQuery, that include a stored procedure as a parameter, the application block throws an exception. Instead of stored procedures, you can use in-line SQL statements. There are Execute method overloads that accept a SQL statement as a parameter. For the same reason that stored procedures are unsupported, you can only send one SQL statement in a request.

SQL Server CE has a special result set named SqlCeResultSet. This is the type of the result set that queries should return. It supports seeking, moving both forwards and backwards in the database, and also changing the data.

For general information about SQL Server CE, see Microsoft SQL Server: SQL Server 2005 Compact Edition on the Microsoft Web site. For information about the relevant APIs, see the System.Data.SqlServerCe Namespace page on MSDN.

Using Oracle with the TransactionScope Class

Although you can use the TransactionScope class with the Oracle client, transactions are always treated as distributed transactions rather than lightweight transactions. Distributed transactions have a higher performance overhead.

The .NET Framework managed provider for Oracle requires a file named oramts.dll in order to use the TransactionScope class. For more information, see The Microsoft .NET Managed Provider for Oracle depends on the Oramts.dll file to enlist the Oracle connections on the Microsoft Help and Support Web site.

If you are using Oracle with the Microsoft Transaction Server, see Oracle Services for MTS on the Oracle Web site for the appropriate downloads.

Usage Notes

The DatabaseFactory object determines which Database object to create based on the ADO.NET DbProviderFactory object that is associated with the connection string. The connection string is located in the configuration file, in the <connectionStrings> section. By default, the application block creates Database objects of type SqlDatabase for data providers of type System.Data.SqlClient, objects of type SqlCeDatabase for data providers of type System.Data.SqlServerCe, objects of type OracleDatabase for data providers of type System.Data.OracleClient, and objects of type GenericDatabase for all other data provider types.

The GenericDatabase class supports only the database provider functionality provided by ADO.NET. In particular, the data access overloads that support parameter discovery do not work. A GenericDatabase object can be used with any .NET managed provider, including the ODBC and OLE-DB providers that ship with the .NET Framework 2.0. You can override the mappings between data provider types and Database object types through the configuration settings in the configuration file. For more information, see Design of the Data Access Application Block.