here's my attempt at a stored procedure gateway to facilitate/simplify multi developer scenarios and standardizing the coding as much as possible.
The gateway is developed in C# and uses generics, reflection, interfaces and an abstract base class. following is the list of requirements that i wanted to fulfil
- place most if not all of the core logic into the base class
- allow fast and straightforward implementation for each and every subsequent stored procedure
- expose only supported methods on each stored procedure class
I'll start off with the base class which holds most of the logic.
spbase (base class)
the base class is defined abstract with a <tfields> generic type parameter, it also implements the IDisposable interface so we can clean up after use.
public abstract class spbase<tfields> : IDisposable
3 properties are defined. StoredProcedureName is defined protected to give access only to the child class.
private SqlParameter[] sqlparams { get; set; }
private SqlConnection SQLConnection { get; set; }
protected abstract string StoredProcedureName { get; }
The
constructor does a check on the generic type parameter to ensure the developer has defined the child class generic type parameter as an enum; and initializes the SQLConnection.
public spbase()
{
if (!typeof(tfields).IsEnum)
{
throw new ApplicationException("Invalid class definition!");
}
try
{
this.SQLConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ToString());
}
catch (Exception e)
{
this.Dispose();
throw e;
}
}
The following code describes the methods of the base class
The setParameters method takes in the parameters required by the stored procedure; the keyword params is used so as to allow for a variable number of parameters to be passed to the method, as each stored procedure will have a variable number of parameter. The method uses reflection to access the properties defined in the child class. NOTE: (params tfields[] fields) will be explained further in the article.
private SqlParameter[] setParameters(params tfields[] fields)
{
if (fields.Length <= 0) return null;
SqlParameter[] sqlparams = new SqlParameter[fields.Length];
for (int i = 0; i < fields.Length; i++)
{
PropertyInfo pi = this.GetType().GetProperty(fields[i].ToString());
sqlparams[i] = new SqlParameter(fields[i].ToString(), pi.GetGetMethod().Invoke(this, null));
}
return sqlparams;
}
checkFields gives the option of overriding and defining the logic for checking the fields.
public virtual bool checkFields(params tfields[] fieldstocheck) { return true; }
The following 4 Execute methods uses the MS SqlHelper class, parameters are set using the setParamters function by passing in fields. Each are marked protected so as to expose methods to only the child class.
protected virtual string ExecuteXML(params tfields[] fields)
{
return ExecuteDataSet(fields).GetXml();
}
protected virtual DataSet ExecuteDataSet(params tfields[] fields)
{
sqlparams = this.setParameters(fields);
return SqlHelper.ExecuteDataset(this.SQLConnection, CommandType.StoredProcedure, this.StoredProcedureName, sqlparams);
}
protected virtual void ExecuteNonQuery(params tfields[] fields)
{
sqlparams = this.setParameters(fields);
SqlHelper.ExecuteNonQuery(this.SQLConnection, CommandType.StoredProcedure, this.StoredProcedureName, sqlparams);
}
protected virtual object ExecuteScalar(params tfields[] fields)
{
sqlparams = this.setParameters(fields);
return SqlHelper.ExecuteScalar(this.SQLConnection, CommandType.StoredProcedure, this.StoredProcedureName, sqlparams);
}
Implementation of the IDisposable interface
public void Dispose()
{
this.SQLConnection.Close();
this.SQLConnection = null;
this.SQLConnection.Dispose();
}
Interfaces
4 interfaces corresponding to the 4 Execute methods are required. the interfaces simply define the methods to be made available for each stored procedure class.
interface IExecuteDataSet
{
System.Data.DataSet ExecuteDataSet();
}
interface IExecuteNonQuery
{
void ExecuteNonQuery();
}
interface IExecuteXML
{
string ExecuteXML();
}
interface IExecuteScalar
{
object ExecuteScalar();
}
Stored Procedure Class
Now to put everything together ...
Start off by defining the stored procedure class, i always prefix mine with "sp_" so the class will follow suit.
public class sp_addnewuser: spbase<>
The class inherits from spbase which requires a generic type parameter, we will need to define that like so:
public class sp_addnewuser: spbase<sp_addnewuser.eFields>
{
public enum eFields { userid = 0, username, useremail }
public int userid { get; set; }
public string username { get; set; }
public string useremail { get; set; }
protected override string StoredProcedureName
{
get { return "sp_addnewuser"; }
}
}
We pass in the sp_addnewuser.eFields which is the enum defined in the child class so that our base class knows the enum and can use the values to perform it's tasks. This is the same enum that we will use as parameters in (params tfields[] fields). the eFields enum will contain all the stored procedure parameters (named exactly as in the stored procedure); for procedures with no parameters, simply define an empty eFields.
public enum eFields {}
On top of the enums, we would require corresponding properties (NOTE: must be exactly as the values in the enum). We would also be required to implement the abstract method StoredProcedureName, which we have done so as above.
Next we would need to implement the interfaces to expose the execute methods for this stored procedure.
public class sp_addnewuser: spbase<sp_addnewuser.eFields>, IExecuteNonQuery
{
public enum eFields { userid = 0, username, useremail }
public int userid { get; set; }
public string username { get; set; }
public string useremail { get; set; }
public void ExecuteNonQuery
{
base.ExecuteNonQuery(eFields.userid, eFields.username, eFields.useremail);
}
'this is the actual stored procedure name
protected override string StoredProcedureName
{
get { return "sp_addnewuser"; }
}
}
The base class base.ExecuteNonQuery takes in the parameters eFields.userid, eFields.username, eFields.useremail. this allows the base class to form the SqlParameters required for the execution of the stored procedure. All subsequent stored procedure classes will be defined in a similar way.
here's another example of the implementation
class sp_getcountry: spbase<sp_getcountry.eFields>, IExecuteDataSet, IExecuteXML
{
public enum eFields
{
countrycode = 0
}
public string countrycode { get; set; }
public string ExecuteXML()
{
return base.ExecuteDataSet(eFields.countrycode).GetXml();
}
public System.Data.DataSet ExecuteDataSet()
{
return base.ExecuteDataSet(eFields.countrycode);
}
protected override string StoredProcedureName
{
get { return "sp_getcountry"; }
}
}
The sp_getcountry class implements the IExecuteDataSet and IExecuteXML interfaces, which will expose the Execute methods supported by this stored procedure.
You may want to add a constructor to initialize all the properties (NOTE: remember to call base() on the constructor).
