IDatabase Interface

Application extensions can use this class in a variety of situations, such as reading data for display, passing data through application layers, and submitting changed data back to the database system. The class includes support for both stored procedures and in-line SQL.

Common housekeeping tasks, such as managing connections, are encapsulated in the class' methods. In other words, the Database class provides access to the most often used features of ADO.NET in simple-to-use classes; this boosts developer productivity.

ADO.NET 2.0 provides classes such as the DbCommand class and the DbConnection class; these classes help to abstract the data provider from any particular database implementation. The Database class takes advantage of these classes and provides a model that further supports encapsulation of database type-specific features, such as type conversions. Because of this, applications can be ported from one database type to another without modifying the client code.

The Database class includes an abstract base class that defines a common interface and that provides much of the implementation needed by the data access methods available in ADO.NET 2.0.

The Database class provides the following benefits:

• It uses the functionality provided by ADO.NET 2.0 and with it, you can use ADO.NET functionality along with the Databases functionality.
• It reduces the need to write boilerplate code to perform standard tasks.
• It helps maintain consistent data access practices.
• It reduces difficulties in changing the database type.
• It relieves developers from learning different programming models for different types of databases.
• It reduces the amount of code that developers must write when they port applications to different types of databases.
• It supports automatic resolution of embedded functions via the EmbeddedParser.
• It provides extensive logging to the DevLog including all SQL executed and its timing.

IDatabase provides a set of lookup-like utility functions, each with several overloads:

• DBLookup(String, String, String) - Performs a lookup of a single value.
• DBCount(String, String, String) - Performs a COUNT(*) query.
• DBExists(String, String) - Efficiently queries for the existence of a record.

IDatabase provides a set of generalized database query functions, each with several overloads:

• FillStringCollection(String, DataFuncOptions, String) - Fill a StringCollection with the contents of the first column in the provided query.
• OpenCachedTable(String, String, String) - Opens a new DataSet and returns the first DataTable, with caching.
• OpenDataReader(String, DataFuncOptions, String) - Opens a recordset using the internal connection.
• OpenDataset(String, String) - Opens one or more tables or views.
• OpenHashtable(String) - Opens a 2 column statement on the encapsulated connection and returns the results as a hashtable.
• OpenTable(String, String) - Opens a new DataSet and returns the first DataTable.

IDatabase supports infrequently used, extended functionality via calls to Exec(DatabaseExecCommands, Int32, Object) with a DatabaseExecCommands value.

The following helper classes may be useful when rolling your own statements:

• SQLDeleter - Class to manage building a SQL DELETE statement generically across all DBMS types.
• SQLInserter - Class to manage building a SQL INSERT statement generically across all DBMS types.
• SQLSelectInto - Class to manage building a SQL "INSERT INTO ... SELECT ... FROM" statement generically across all DBMS types.
• SQLUpdater - Class to manage building a SQL UPDATE statement generically across all DBMS types.
• SQLUpserter - Class to manage building a SQL MERGE statement to insert or update as appropriate (SQL-Server 2008+ only).

Examples

IDatabase db = appContext.Databases["Studio"];
System.Collections.Generic.Dictionary<string, string> props = new System.Collections.Generic.Dictionary<string, string>();
IDataReader dr = null;

try
{
    using (dr = db.OpenDataReader("SELECT prop_id, prop_value FROM xmt_properties WHERE prop_value is not null"))
    {
        while (dr.Read())
        {
            props.Add(dr.GetString(0), dr.GetString(1));
        }
        dr.Close();
    }
    dr = null;
}
catch (Exception ex)
{
    DevLog.LogMessage("OpenProperties", "dataReadError", ex);
    throw;
}
finally
{
    if (dr != null)
        dr.Close();
}

Reference