creating good classes to connect to data sources

F

fguihen

Well-known member
Joined
Nov 10, 2003
Messages
248
Location
Eire
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
 
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 its odbc, sql, oracle, or ole db, the assemblies dont need to be listed since theyre 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.:

Code: 
<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="&quot;"
		quoteSuffix="&quot;"
	/>
	
	<!-- sql server -->
	<addd
		name="iCode"
		provider="MSSQL"
		debugMode="false"
		connectionstring="Server=serverip;Database=icode;"
		namedParameterPrefix="@"
		logExceptions="true"
		quotePrefix="&quot;"
		quoteSuffix="&quot;"
	/>
	
	<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

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

Heres 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 doesnt need to be disposed and has a bunch of methods I find useful.

Youll 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 Im using reserved works in the queries.

Code: 
/// <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 cant 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 wont be so bad if you require mysql5+ or mssql server.

Anyways, you get the idea.
 
Back
Top