Application database connections
In general, the best practice to access the database to execute queries, updates or stored procedure calls is to go through the Mongoose framework using IDO requests. However, there might be times when it is necessary or more efficient to access the database directly (for example, when building and executing dynamic SQL or iterating through records from an extremely large query result set). Direct database access is accomplished using the standard .NET framework database interfaces: IDbConnection and IDbCommand. For more information on these classes, see the Microsoft .NET Framework documentation.
Opening a connection to the application database
The ApplicationDB.Connection property provides an opened SqlConnection instance pointing to the application database. By going through the ApplicationDB.Connection property, the framework is able to open and initialize the connection based on the calling user's session information.
Executing database commands
The IDbCommand class is used to execute stored procedures, queries, updates, or any other T-SQL command. The IDbCommand must be created by using the ApplicationDB.CreateCommand( ) method, setting the CommandType and CommandText properties, and adding any parameters to the Parameters property. The parameters are added by using the ApplicationDB.AddCommandParameterWithValue or ApplicationDB.AddCommandParameter methods. When the command is executed, use the ApplicationDB.ExecuteScalar, ApplicationDB.ExecuteNonQuery, or ApplicationDB.ExecuteReader methods. Using any of these three methods allows you to leverage the framework infrastructure for executing SQL commands including logging and exception-message translation.