Share via

Creating a Database Object

  • Article

All data access methods are executed against a Database object. You can use the Enterprise Library container to create a Database object. The specific type of Database object it returns is determined by the application configuration information. By changing the default configuration, the unmodified application can be run against different databases. The connection string information for each database you define is stored in the <connectionStrings> section in the application configuration file.

This topic includes the following sections that describe how to create instances of different types of databases, and some things you must consider when working with specific types of databases:

  • Creating a Default Database Instance
  • Creating a Named Database Instance
  • Creating a Specific Database Type
  • Creating a Database Instance Directly
  • Creating a GenericDatabase Instance
  • Writing Code to Use the Database Classes
  • Using SQL Server CE
  • Using Oracle with the TransactionScope Class
  • Using the OracleDataReaderWrapper Class

Creating a Default Database Instance

You can use the configuration tools to specify a default database instance. The container creates the database specified as the default when you resolve an instance without specifying a database instance name. The following application code shows how you can resolve a default Database instance by specifying the Database type as a dependency of the class constructor.

public class ExampleScenario 
{
  private Database db;

  public ExampleScenario(Database theDatabase)
  {
    db = theDatabase;
  }
  ...
}

'Usage
Public Class ExampleScenario

  Private db As Database

  Public Sub New(ByVal theDatabase As Database)
    db = theDatabase
  End Sub
  ...
End Class

Then, to force injection to occur, you must resolve the ExampleScenario class through the container, or specify it as a dependency of a parent class that uses this class. For example, the following code demonstrates both the Resolve and the GetInstance methods to instantiate the ExampleScenario class with the injected database.

// Resolve the class through the Unity container.
var container = new UnityContainer()
                .AddNewExtension<EnterpriseLibraryCoreExtension>();
ExampleScenario myObject = container.Resolve<ExampleScenario>(); 

// Resolve the class through the default container. You must use this 
// approach if you are not using Unity with Enterprise Library. 
ExampleScenario myObject 
  = EnterpriseLibraryContainer.Current.GetInstance<ExampleScenario>(); 

'Usage
' Resolve the class through the Unity container.
Dim container = New UnityContainer() _
                .AddNewExtension<EnterpriseLibraryCoreExtension>()
Dim myObject As ExampleScenario = container.Resolve(Of ExampleScenario)() 

' Resolve the class through the default container. You must use this 
' approach if you are not using Unity with Enterprise Library. 
Dim myObject As ExampleScenario _
  = EnterpriseLibraryContainer.Current.GetInstance(Of ExampleScenario)()

Note

Some containers may require you to register the class you want to resolve. Unity allows you to resolve types that are not registered; it creates an instance of the type you specify and populates any dependencies specified in that type. For more information about resolving objects, see Creating Application Block Objects.

Creating a Named Database Instance

Alternatively, the application code can specify a named instance of a database. For example, if you used the configuration console to create an instance named Sales, the code to create a Database object for that specific instance would use a Dependency attribute on the constructor parameter, as shown here.

public class ExampleScenario 
{
  private Database db;

  public ExampleScenario([Dependency("Sales")] Database theDatabase)
  {
    db = theDatabase;
  }
  ...
}

'Usage
Public Class ExampleScenario

  Private db As Database

  Public Sub New(<Dependency("Sales")> ByVal theDatabase As Database)
    db = theDatabase
  End Sub
  ...
End Class

Then, to force injection to occur, you must resolve the ExampleScenario class through the container, or specify it as a dependency of a parent class that uses this class, as shown in the previous section.

Note

If the configuration file does not specify a default instance and the block attempts to resolve a default Database without providing the name, the block will throw an exception. For more information about resolving Enterprise Library objects in your applications, see Creating and Referencing Enterprise Library Objects.

Creating a Specific Database Type

Functionality specific to a particular database system is incorporated in the appropriate Database-derived class. For example, ExecuteXmlReader is only available in the SqlDatabase class. The client code must downcast to the correct type to use any methods that are unique to a particular database.

If you must use methods that are specific to a particular database type, such as asynchronous calls or the ExecuteXmlReader method, you can downcast the resolved database type. The following code resolves a SqlDatabase object.

public class ExampleScenario 
{
  private SqlDatabase db;

  public ExampleScenario(Database theDatabase)
  {
    db = theDatabase as SqlDatabase;
  }
  ...
}

'Usage
Public Class ExampleScenario

  Private db As SqlDatabase

  Public Sub New(ByVal theDatabase As Database)
    db = DirectCast(theDatabase, SqlDatabase)
  End Sub
  ...
End Class

Similarly, to create an Oracle database, use the OracleDatabase type. To create a SQL Server CE database, use the SqlCeDatabase type. To force injection to occur, you must resolve the ExampleScenario class through the container, or specify it as a dependency of a parent class that uses this class, as shown in the first scenario in this topic.

Creating a Database Instance Directly

If you know the connection string for the database you want to create, you can bypass the application's configuration information and use a constructor to directly create the Database object. Because the Database class is an abstract base class, you must construct one of its derived types. The derived Database type determines the ADO.NET data provider. For example, the SqlDatabase class uses the SqlClientFactory provider. It is your responsibility to construct the appropriate type of Database class for the connection string.

The following code creates a SqlDatabase object with the supplied connection string.

// Assume the method GetConnectionString exists in your application and 
// returns a valid connection string.
string myConnectionString = GetConnectionString();

SqlDatabase sqlDatabase = new SqlDatabase(myConnectionString);

'Usage
' Assume the method GetConnectionString exists in your application and 
' returns a valid connection string.
Dim myConnectionString As String = GetConnectionString()

Dim db As SqlDatabase = New SqlDatabase(myConnectionString)

If you use a connection string for a data provider other than the ADO.NET SQL data provider or Oracle data provider, you can construct a GenericDatabase object. When you create a GenericDatabase object, you must supply the DbProviderFactory object.

For more information about creating Enterprise Library objects without resolving them through the container, see Creating Application Block Objects Directly.

Creating a GenericDatabase Instance

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. 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.

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(myConnectionString, OdbcFactory.Instance);

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

Writing Code to Use the Database Classes

The Data Access Application Block creates the correct database class based on connection string information in the configuration file and returns to the client code an object of a subtype of the Database base class. If you do not need commands that are specific to a particular database type, such as SQL Server, you should keep your 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. However, if you need to use commands that are specific to one database or another, you must specify the database type you expect to be created by the block.

Each data access method has multiple overloads. The following descriptions and guidelines can help you to select the appropriate overload:

  • There are overloads that accept an ADO.NET DbCommand object. These overloads provide the most flexibility and control for each method.
  • There are overloads that accept a stored procedure name and a collection of values to be used as parameter values for the stored procedure. These overloads are convenient when your application calls stored procedures that have input-only parameters.
  • There are overloads that accept a System.Data.CommandType and a string that represents the command. These are convenient overloads to use when your application executes inline SQL statements or stored procedures without parameters.
  • Each of the preceding overloads includes an overload that accepts a transaction. This allows you to use the desired style of overload while executing the method in an existing transaction.
  • Most of the SqlDatabase methods also have equivalent Begin and End versions you can use to perform data access asynchronously.
  • The block also supports the Accessor class that exposes methods to execute data access operations both synchronously and asynchronously, and return the results as a series of objects suitable for client-side querying using technologies such as LINQ.

Each key scenario demonstrates one of the overloads available for a particular method. Many of the scenarios can be completed using other available overloads.

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 SQL Server Compact 3.5 on the Microsoft Web site. For information about the relevant APIs, see the System.Data.SqlServerCe Namespace page on MSDN.

Note

SQL Server CE can only operate in full-trust environments.

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.

Using the OracleDataReaderWrapper Class

Enterprise Library provides an OracleDataReaderWrapper class, instead of a database-specific Oracle data reader, for returning data from an Oracle database. The wrapper performs type conversions to enable the retrieval of values for types not natively supported by the OracleDataReader. If you require the ability to directly access the Oracle database reader wrapped in OracleDataReaderWrapper, you can access it by casting your reader using the OracleDataReaderWrapper class InnerReader property, as shown in the following example.

OracleDataReader innerReader = ((OracleDataReaderWrapper)reader).InnerReader;

'Usage
Dim innerReader As OracleDataReader = (CType(reader, OracleDataReaderWrapper)).InnerReader