Linked Articles
Introduction
Some of us use Visual Studio to connect to a database and wizards or drag/drop methods to create classes from the tables. Others buy a third-party application that builds those classes for you which you can extend depending on your own needs. And there are some who build all that stuff themselves, like me.
This article is the first in a series of how to build a library that is easy to use and can be reused for other projects. (The series will depend a bit on the popularity and "deemed" quality of this article.)
The goal of this library is to make database access easy. You don't want to create Connection, Command or DataAdapter objects and the respective business logic all the time.
Has this been done before? Probably, the article is meant to give you some insight and provide an easy to use library.
Pick what best suits you.
Background
Whatever you may think of the code. The base of this component was originally written in C# 1.0 about a decade ago, when I was just a junior developer learning C#. That is also the reason why I wanted to write an article about it, because it is a good level of understanding about many concepts about databases and n-tier development. In fact, developing this separately from a main project enforces you to start thinking in modules or tiers.
In that decade, the code was improved (Result class instead of just returning a status, added transaction handling, ...), but the core idea mainly remained the same. In addition, this library can also be rewritten in any language (Java, Python, ...)
This library was also improved to be able to handle multiple database (Oracle, Microsoft Access, MySQL, ...) because at the time of that improvement, I was in need of a tool that could switch databases easily. Right now, this library is attached to a tool I wrote called "DbTackler" that can tackle (or switch) between databases with the press of a button. Comes in handy when you want to execute queries against development and production databases for example.
A Quick Word on Design
This is a very basic design for a small to middle sized desktop or web application. In larger applications, layers can be split like a Business Object layer and a Business Logic layer or multiple DAL components where one part talks to database, the other to web services and yet another reads and writes files. Other applications like interfaces or services on the other hand don't have a GUI layer.
For this article, it is important to note that it belongs to the DAL layer and that a DAL layer could contain more than just database access.
Utilities is a vertical layer touching every part of the code like logging modules or settings and options.
Putting the layers in different assemblies from the start greatly facilitates the re-usability of features, but also prevents spaghetti code, redundant code (copy/paste) and divides the big problem in smaller ones. Since small applications or even prototypes often end up large, it is good practice to be rigorous about your initial design. (Also see my article Programming vs Software Development.)
Providers
Each database has what they call a "provider". In most cases, at least one is installed with the client, if the provider is not yet known in this library, you can add it similar to the existing ones. If the client didn't install a provider, chances are you can download and install one, though almost all suppliers at least support ODBC. It is important to note that many databases support multiple providers. Oracle has ODBC, OleDb and ODP.NET eg. Each with slightly different properties.
One piece of advice: If you have control over your database, make sure to avoid table/column/sequence/... names that could be potentially a key word. "Name", "Key", "From", "To", ... I've had the rare occasion where this messed up the expected result. After changing the column to example, Fname (first name) the query worked as expected. Very fun to debug something like that (as the copy/pasted SQL in the database client worked).
This also means that if the library is not immediately working, chances are you don't have the correct provider installed or configured. It might be that you need the x86 or x64 DLL of the provider depending on the machine. Make sure to compile to "Any cpu".
Using the Code
The entire DAL component of this library is based on the IDatabaseConnector interface. The point here being that each provider works exactly the same way when using the library even when it works differently under the hood.
All implementations are derived from this class which makes it easy for you to add a new provider if necessary. Add a class and implement the interface with the correct provider objects.
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
namespace Framework.Dal
{
public enum TRANSACTION{
Commit,
Rollback
};
public interface IDataBaseConnector
{
Result ExecuteSelect(string SQL);
Result ExecuteSelect(StringBuilder SQL);
Result ExecuteSelectSafe(string SQL, string[] paramnames, object[] paramvals);
Result ExecuteSelectSafe(StringBuilder SQL, string[] paramnames, object[] paramvals);
Result ExecuteUpdate(string SQL);
Result ExecuteUpdate(StringBuilder SQL);
Result ExecuteUpdateSafe(string SQL, string[] paramnames, object[] paramvals);
Result ExecuteUpdateSafe(StringBuilder SQL, string[] paramnames, object[] paramvals);
Result ExecuteInsert(string SQL);
Result ExecuteInsert(StringBuilder SQL);
Result ExecuteInsertSafe(string SQL, string [] paramnames, object [] paramvals);
Result ExecuteInsertSafe(StringBuilder SQL, string [] paramnames, object [] paramvals);
Result ExecuteDelete(string SQL);
Result ExecuteDelete(StringBuilder SQL);
Result ExecuteDeleteSafe(string SQL, string[] paramnames, object[] paramvals);
Result ExecuteDeleteSafe(StringBuilder SQL, string[] paramnames, object[] paramvals);
Result ExecuteStoredProc(string ProcName, string [] paramnames, object [] paramvals);
Result ExecuteNonQuery(string SQL);
Result ExecuteNonQuerySafe(string SQL, string[] paramnames, object[] paramvals);
string[] GetTables();
DataColumnCollection GetColumnInformation(string tablename);
}
} The triple / comments insert intellisense information about the members, so they are important. (I believe you need to check the "XML documentation file" option in the build tab on the project properties in Visual Studio). Also, I made StringBuilder overloads so you don't have to call the "ToString()" method each time you use the StringBuilder object for building a query.
So basically, you have support for:
- (C) -
Insert statements (string or StringBuilder, with or without parameter) - (R) -
Select statements (string or StringBuilder, with or without parameter) - (U) -
Update statements (string or StringBuilder, with or without parameter) - (D) -
Delete Statements (string or StringBuilder, with or without parameter) - Stored procedures
Create, alter, ... statements (string with or without parameter)
Note that most methods return a Result object.
This is the Result class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Framework.Dal {
public class Result {
public enum STATUS{
UNDEFINED = 0,
CONNECTION_FAILED = 1,
CONNECTION_OK = 2,
EXECUTE_FAILED = 3,
EXECUTE_OK = 4,
};
private STATUS status = STATUS.UNDEFINED;
private System.Data.DataSet ds = new System.Data.DataSet();
private Exception exception = null;
private string sql;
private string [] paramnames;
private object [] paramvals;
public STATUS Status {
get {
return status;
}
internal set{
status = value;
}
}
public int NumberOfRowsAffected{ get; internal set; }
public Exception ResultException{
get{
return exception;
}
internal set{
exception = value;
}
}
public System.Data.DataSet ResultDataSet{
get{
return ds;
}
set{
ds = value;
}
}
public string SQLConstructed{
get{
StringBuilder builder;
try{
builder = new StringBuilder(sql);
if(paramnames != null && paramvals != null && paramnames.Length == paramvals.Length){
for(int i = 0; i < paramnames.Length; i++){
builder.Replace(Convert.ToString(paramnames[i]), Convert.ToString(paramvals[i]));
}
}
}
catch(Exception ex){
builder = new StringBuilder("Error constructing SQL: " + ex.Message);
}
return builder.ToString();
}
}
public string SQL{
get{
return sql;
}
internal set{
sql = value;
}
}
public string [] ParameterNames{
get{
return paramnames;
}
internal set{
paramnames = value;
}
}
public object [] ParameterValues{
get{
return paramvals;
}
internal set{
paramvals = value;
}
}
}
}The Result class comes in handy particularly when trying to debug, because it allows you to recreate the SQL you sent to the database by filling in the parameters for you so you can copy/paste the SQL in the database's query window (SQL plus, Toad, SQL Server Management Studio, MySQL workbench, ...). It also holds the exception object that often holds an error code of the database which is easily Googled.
The Result also holds a success/failure status and a DataSet object (in case of select) or NumberOfRowsAffected (in case of insert/update/delete).
Using the code is as simple as this:
string connectionstring = "[Connectionstring here]";
IDataBaseConnector databaseconnector = new OleDbDataBaseConnector(connectionstring);
Result result = databaseconnector.ExecuteSelect("[Select Statement here]");
if(result.Status == Result.STATUS.EXECUTE_OK){
}
else{
}
Whenever possible, make sure to use the ExecuteXXXSafe methods that execute parametrized queries. There are numerous articles about SQL injection on the internet or here on CodeProject. I would recommend to read one and also try it out!
Points of Interest
- There are many features you can use with the base
Connector/DataAdapter /Command classes of each provider, but this library should cover more then 90% of your needs. The whole core idea is to simplify the thinking process and lose everything you don't need. (and nothing prevents you from adding or removing features you deem important) - The
Insert/Update/Delete functions are redundant (They all call ExecuteNonQuery), but do help further down the road as it is immediately clear what the statement is.
Eg. if you call ExecuteUpdate(MyStatement); you'll know, without debugging or looking elsewhere that this will be an update statement. You can remove those and use ExecuteNonQuery instead if you like. - The
NonQuery functions can be used in cases of Create Table, Alter Table, .... statements. - I cleaned the code before posting, so if you do spot an error, feel free to let me know.
- Currently successfully connected and used for:
- Oracle (OleDb and ODP.Net)
- Sql-Server
- MySql
- PostgreSQL
- Microsoft Access
- (And even DBase)
- Further articles in the series will probably be "logging" and "settings".
- You could just leave the compilation option as is and add the assembly through the "Add reference" option, or you can strong name it, recompile and add to the GAC.
- One of my previous bosses believed that "re-usability" was copy/pasting classes from one project to the other reasoning that one project could never be dependent on another. Don't. The framework you're writing will be the most tested feature of your development and end up pretty robust, in addition, there are other options to ensure independency.
- You could make this stuff more dynamic by using reflection and what not. I wouldn't do that because I see it as a performance killer, but you could if you wanted to.
Guidelines
- Often not 100% possible, but frameworks should be as independent of other assemblies/projects/modules as possible.
- Making your code re-usable is not sufficient, you need to make it clear how anyone can use it.
- This will be your bottleneck, take time to test it over and over again and be rigorous on new features. Do you really need it?
Some Links that Can Come in Handy
Notes
- To be very honest, I never, ever used the
ExecuteStoredProcedure methods. Apologies if they should contain errors.
History
- Version 1.0 (February 2014)
- Version 1.1 (April 2014) - Added linked article
