creating good classes to connect to data sources

[fguihen]

ive had to write a coupple of classes to connect to data sources like databases in the last few weeks and ive found myself repeating a lot of code. im wondering, what methods/properties do you put in your abstract datasource connection class, and what do you put in the class that implements this abstract class? how do you structure a class that connects to a datasource so as to make it as reusable as possible

[HJB417]

I wrote an abstract dataprovider. It implements the common ado.net interfaces (idbconnection, dbdataadapter, idbcommand, etc.)

In the app config, I specify the 'provider' to use and the assemblies of the provider (if it's odbc, sql, oracle, or ole db, the assemblies don't need to be listed since they're builtin).

 

So say my mysql db is down, I can simply switch to sqlserver by changing the app config file. Or better yet, say I want to switch from using mysqlodbc to mysql.net connector or my own mysql provider, it can all be done in the app.config without having to perform a recompile.

 

e.x.:

 

<Data.ConnectionInfo>
<!-- ace api -->
<add
	name="iCode"
	provider="Advantage"
	debugMode="false"
	connectionstring="Data Source=path;ServerType=ADS_REMOTE_SERVER|ADS_LOCAL_SERVER;TableType=ADS_CDX;TrimTrailingSpaces=TRUE;"
	preFormatQuery="true"
	autoDetectParamDbType="true"
	maxConnectionPoolSize="25"
	namedParameterPrefix="@"
	deparameterizeQueries="false"
	logExceptions="true"
	connectionPoolQuery="SELECT COUNT(*) FROM PERSONAL"
	connectionPoolLifetime="1:00"
	quotePrefix="""
	quoteSuffix="""
/>

<!-- sql server -->
<addd
	name="iCode"
	provider="MSSQL"
	debugMode="false"
	connectionstring="Server=serverip;Database=icode;"
	namedParameterPrefix="@"
	logExceptions="true"
	quotePrefix="""
	quoteSuffix="""
/>

<add
	name="price engine poster"
	provider="HB MySql"
	debugMode="false"
	connectionstring="SERVER=serverip;PORT=3306;DATABASE=priceenginetemplates;OPTION=3;enable booleans=true"
	preFormatQuery="true"
	namedParameterPrefix="@"
	deparameterizeQueries="false"
	logExceptions="true"
	quotePrefix="`"
	quoteSuffix="`"
/>

<add
	name="cached retailers"
	provider="ODBC"
	debugMode="false"
	connectionstring="DRIVER={MySQL ODBC 3.51 Driver};SERVER=serverip;PORT=3306;DATABASE=pricespider;OPTION=3;enable booleans=true"
	preFormatQuery="true"
	namedParameterPrefix="@"
	deparameterizeQueries="false"
	logExceptions="true"
	quotePrefix="`"
	quoteSuffix="`"
/>
</Data.ConnectionInfo>

 

and in code, I can get the connection info by doing

 

_connInfo = DbConnectionInfoCollection.Get("cached retailers");

 

Here's a code snippet.

 

Settings.DatabaseConnection is a DbConnectionInfo object which is what DbConnectionInfoCollection.Get returns. I use it to create a 'GenericDbCommandHelper' with the method 'CreateCommandHelper'. A GenericDbCommandHelper is basically a IDbComamnd that doesn't need to be disposed and has a bunch of methods I find useful.

 

You'll also notice that I use Settings.DatabaseConnection.Quote to set the ComamndText. This is because I want to quote my database object names where an object is a database, table, etc.

 

E.x.: mysql uses `OBJECTNAME, access/mssql uses [OBJECTNAME], etc.

 

Doing this gives me the flexibility of changing databases and not have to do a major code review + recompile because I'm using reserved works in the queries.

 

/// <summary>
/// Determines if an approval code already exists in the approval codes
/// database.
/// </summary>
/// <param name="CreditCardInfo">The credit card info containing the approval code.</param>
/// <returns>True if and only if the approval code exists in the database.</returns>
/// <remarks>The approval codes database connection is locked until the method completes.</remarks>
public static bool Exists(CreditCardInfo ccInfo)
{
if(ccInfo == null)
	throw new ArgumentNullException("ccInfo");
if(ccInfo.ApprovalCode == null)
{
	string msg = "Credit card approval code cannot be null.";
	throw new ArgumentNullException("CreditCardInfo.ApprovalCode", msg);
}
GenericDbCommandHelper cmd = Settings.DatabaseConnection.CreateCommandHelper();

GenericDbParameter paramApprovalCode = cmd.Parameters.Add("@approvalcode", ccInfo.ApprovalCode);
GenericDbParameter paramAccount = cmd.Parameters.Add("@account", CreditCardCrypter.Encrypt(ccInfo));

cmd.CommandText = Settings.DatabaseConnection.Quote(@"
SELECT
COUNT(*)
FROM
{0}
WHERE
{1}=" + paramApprovalCode.ParameterName + @"
AND
{2}=" + paramAccount.ParameterName,
	/*{0}*/"approvalcodes",
	/*{1}*/"approvalcode",
	/*{2}*/"account");

Trace.WriteLine("Checking to see if approval code " + ccInfo.ApprovalCode + " exists.");
int value = (int) cmd.ExecuteScalar();
Trace.WriteLine(value + " matching approval codes were found.");
return (value > 0);
}

 

 

The bad side is I really can't use proprietary db stuff such as stored procs and sql statements such as LIMIT, TOP, etc. It all depends on what db and db versions you want your app to target. stored procs won't be so bad if you require mysql5+ or mssql server.

 

Anyways, you get the idea.