I have written a specialized class to wrap up the enterprise library database functionality for easier usage. The reasoning for using the Enterprise Library is because my applications commonly connect to both oracle and sql server database systems. My wrapper handles both creating connection strings on the fly, connecting, and executing queries allowing my main code to only have to write a few lines of code to do database stuff and deal with error handling. As an example my ExecuteNonQuery method has the following declaration:
/// <summary>
/// Executes a query that
returns no results (e.g. insert or update statements)
/// </summary>
/// <param name="sqlQuery"></param>
/// <param name="parameters">Hashtable containing all the parameters for the query</param>
/// <returns>The total number of records modified, -1 if an error occurred </returns>
public int ExecuteNonQuery(string sqlQuery, Hashtable parameters)
{
// Make sure we are connected to the database
if (!IsConnected)
{
ErrorHandler("Attempted to run a query without being connected to a database.", ErrorSeverity.Critical);
return -1;
}
// Form the command
DbCommand dbCommand = _database.GetSqlStringCommand(sqlQuery);
// Add all the paramters
foreach (string key in parameters.Keys)
{
if (parameters[key] == null)
_database.AddInParameter(dbCommand, key, DbType.Object, null);
else
_database.AddInParameter(dbCommand, key, DbType.Object, parameters[key].ToString());
}
return _database.ExecuteNonQuery(dbCommand);
}
_database is defined as private Database _database;.
Hashtable parameters are created via code similar to p.Add("@param", value);.
the issue I am having is that it seems that with enterprise library database framework you must declare the dbType of each parameter. This isn't an issue when you are calling the database code directly when forming the paramters but doesn't work for creating a generic abstraction class such as I have. In order to try and get around that I thought I could just use DbType.Object and figure the DB will figure it out based on the columns the sql is working with. Unfortunately, this is not the case as I get the following error:
Implicit conversion from data type sql_variant to varchar is not allowed. Use the CONVERT function to run this query
Is there any way to use generic parameters in a wrapper class or am I just going to have to move all my DB code into my main classes?
