A colleague has put together some nice Linq type extensions for JavaScript.
Allows you to do things like:-
var peopleWhoLikeBananas = people.Where(function(p) {
return (p.Likes == "Bananas"); });
See his blog entry here.
Javascript Linq Extensions
See his blog entry here.
Data Layer Design - Part 1
Part 2 - redesigned to use Assembly.LoadFrom (coming soon)
Part 3 - redesigned using MEF (coming soon)
For a project I worked on several years ago I had to design a data layer that was flexible enough to allow multiple databases to be used and controlled via a configuration setting. As I was recently asked to do this again I thought I'd blog about the original version and blog the other modified versions.
Ok straight into the coding. So the first piece of work was to write an interface
namespace DataAccess {
using System.Data;
public interface IDataAccess {
Result ExecuteStoredProcedure(string ProcedureName,
params Parameter[] Parameters);
}
}
Only a single method was required that allowed stored procedures to be called - it was a requirement of the system that all communication with the database was done via stored procedures to improve security and maintainability.
The parameter class defines what can be passed to the stored procedure. It’s a fairly simple class that contains properties to hold the information needed to pass a parameter to the stored procedure.
namespace DataAccess
{
public class Parameter {
private string _msName;
private object _moValue;
private ParameterDirection _mpdDirection;
private ParameterType _mptDbType;
private int _miSize;
public ParameterType DbType {
get { return _mptDbType; }
set { _mptDbType = value; }
}
public ParameterDirection Direction {
get { return _mpdDirection; }
set { _mpdDirection = value; }
}
public string Name {
get { return _msName; }
set { _msName = value; }
}
public int Size {
get { return _miSize; }
set { _miSize = value; }
}
public object Value {
get { return _moValue; }
set { _moValue = value; }
}
public Parameter(ParameterType dbType,
ParameterDirection direction,
string name,
int size,
object value) {
_msName = parameterName;
_moValue = value;
_mpdDirection = direction;
_mptDbType = dbType;
_miSize = size;
}
public Parameter(ParameterType dbType,
ParameterDirection direction,
string parameterName,
object value)
: this(dbType, direction, parameterName, 0, value) {
}
public Parameter() {
}
}
}
it also needs the following enums...
namespace DataAccess
{
public enum ParameterDirection {
Input = 0,
Output = 1
}
}
namespace DataAccess
{
public enum ParameterType {
String = 0,
LongString = 1,
Date = 2,
Cursor = 3,
Number = 4
}
}
We also need a Result class that is used to return information from the stored procedure. This contains a DataSet and a property called ‘Outputs’. The idea with this is to allow multiple cursors to be returned along with individual values. The individual values will all be put into the ‘Outputs’ table using the parameter names as column names and the DataSet will contain all the other cursors that have been returned.
namespace DataAccess
{
using System.Data;
public class Result {
private DataSet mData;
public DataRow Outputs {
get { return mData.Tables["Outputs"].Rows[0]; }
}
public DataSet Data {
get { return mData; }
}
public Result(DataSet NewDataSet) {
mData = NewDataSet;
}
}
}
To make it easier to work with I’ll add a DataAccessHelper class. This is a static class that makes it easier to work with the database layer.
namespace DataAccess {
using System;
using System.Data;
public static class DataAccessHelper {
public static DataAccess.IDataAccess GetDataAccess() {
string lsDataType = "DataAccessLayerType";
string dataAccessStringType = System.Configuration.ConfigurationSettings.AppSettings[lsDataType];
String strPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);
if (string.IsNullOrEmpty(dataAccessStringType)) {
throw new DataException("Data access layer type is missing from web.config");
}
Type dataAccessType = Type.GetType(dataAccessStringType);
if (dataAccessType == null) {
throw new DataException("DataAccessType can not be found");
}
Type ti = dataAccessType.GetInterface("DataAccess.IDataAccess");
if (ti == null) {
throw (new ArgumentException("DataAccessType " + dataAccessStringType + " does not implement Data.IDataAccess"));
}
IDataAccess da = (DataAccess.IDataAccess)Activator.CreateInstance(dataAccessType);
return da;
}
public static Result ExecuteStoredProcedure(string procedureName, params Parameter[] parameters) {
IDataAccess lda = GetDataAccess();
var lResult = lda.ExecuteStoredProcedure(procedureName, parameters);
return lResult;
}
}
}
Ok so that’s the plumbing of the data access layer now we need to create an implementation of it that can be used.
namespace DataAccess {
using System;
public class OracleDataAccess : IDataAccess {
public Result ExecuteStoredProcedure(string ProcedureName, params Parameter[] Parameters) {
throw new NotImplementedException();
}
}
}
and to setup the configuration to use the Oracle data access you set the DataAccessLayerType setting to DataAccess.OracleDataAccess and the code will take care of the rest.
Now in your code you add a reference to DataAccess and use the following code :-
I'll try to make this code more realistic when I get a spare 5 mins...
var da = DataAccessHelper.GetDataAccess();
Result r = da.ExecuteStoredProcedure("stored procedure name",
new Parameter(ParameterType.String,
ParameterDirection.Input,
"paramname",
"paramvalue"),
new Parameter(ParameterType.String,
ParameterDirection.Input,
"paramname",
"paramvalue"),
new Parameter(ParameterType.String,
ParameterDirection.Output,
"paramname",
255),
new Parameter(ParameterType.String,
ParameterDirection.Output,
"paramname",
255),
new Parameter(ParameterType.Cursor,
ParameterDirection.Output,
"paramname",
0));
// Access either the r.Outputs for the individual params
r.Outputs["paramname"];
// or r.Data to get at any returned cursors
r.Data.Tables["cursorname"];
If you need to access a different type of database you can create a new class that implements IDataAccess, code up the ExecuteStoredProcedure method and then change the config to point to your new Data Access class.
In the next part I'll show how the project was changed to allow the DataAccess classes to be separate assemblies and loaded as required.
