Introduction:
OK, so there are a ton of C# data classes out there already, but this one is coded to be as reusable as possible.
Most of the code is pretty straight forward: the only bit that needs some explaining is the
TParams struct.
public struct TParams
{
public TParams(string inName,int inSize,System.Data.SqlDbType inDataType,object inValue)
{
this.dtDataType=inDataType;
this.Name=inName;
this.oValue=inValue;
this.Size=inSize;
}
public string Name;
public int Size;
public System.Data.SqlDbType dtDataType;
public object oValue;
}
Trying to create a generalised function that you can use to call parameterized stored procedures is a more difficult design goal. If it was solely a name-value paring it would be easy, but to properly prepare your stored procedure execution, there is a fair amount of information that you need to pass through to the DB (viz. parameter name, parameter datatype, parameter value, parameter direction). In ADO 2.5 and below there was the tendency to ignore most of this preparatory work and rather just rely on the defaults provided for by ADO. While this works, it is certainly not a best practice as ADO can only guess your exact needs.
In a non-typed environment (ASP), the solution to this would probably be to use arrays and in VB, perhaps using an array of type. This is the solution that is used here. An array of type
TParams is prepared and passed to the function, along with the stored procedure name.
System.Data.DataSet Rs = cDat.DIRunSPretDs
(
new DatTools.cDB.TParams[]
{
new DatTools.cDB.TParams("@country", 100,
System.Data.SqlDbType.VarChar,
"Germany")
}
,"sp_Test"
);
In this snippet, the DIRunSPretDs function is called, returning a Dataset. On line 3 an inline temporary array of type
TParams is created. Line 5-7 creates an inline temporary object of type TParams that represents a single parameter of this (sp_Test) stored procedure. Using a type over a paramarray construct gives the advantage of compile-time type safety as well as Intellisense. An overload for the
TParams struct has been provided to simplify the parameter passing.
Limitations:
While this mechanism is useful and offers good functionality, one of the things that it does not offer is OUTPUT parameters.
This was a design decision based on several facts:
- Returning a scalar from a stored procedure is promoted as the preferred way by
MicroSoft.
- Returning several OUTPUT values is not as efficient as returning a recordset with these values.
- SQL server has a good way of returning errors. This can be used in conjunction with the Transact-SQL:
sp_addmessage and RAISERROR.
Conclusion:
This is one way of black-boxing functionality and allowing other developers to reuse without knowledge of the implementation (fundamental OO principle).
While the data class is simple, it does allow for easy client code and fulfilled the goal of the project this was intended for, namely an OSDN codegen on SourceForge.
