Click here to Skip to main content
Click here to Skip to main content
Go to top

C# Data Class

, 27 Jan 2002
Rate this:
Please Sign up or sign in to vote.
A generalised method for calling parameterized stored procedures

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

SimonS
Web Developer
South Africa South Africa
* Visual C# MVP 2004, 2005 - South Africa
* SADeveloper.NET User Group co-founder and lead 2003, 2004, 2005
 
MSN : simon_stewart AT hotmail.com
Email : simon AT brokenkeyboards.com
Skype: brokenkeyboards
CEO of Broken Keyboards Software



Founder of these startups:


Browse This For Me


Monitor My URL



My full CV can be download here in PDF format.

Comments and Discussions

 
QuestionIs this still useful, and what is cDat? Pinmemberneil_b2-Sep-07 1:16 
AnswerRe: Is this still useful, and what is cDat? PinmemberSimonS2-Sep-07 1:26 
Internally we still use parts of this code as a general abstraction from SQL.
 
Project has been superceded by : http://www.brokenkeyboards.com/bks/products_bks_orm_developer_tools.htm
 
cDat is a variable within this sample project.
 
Cheers,
Simon
 

> company:: Broken Keyboards Software
> blog:: brokenkeyboards
 
> skype :: SimonMStewart
> CV :: PDF

GeneralKick DataSet to out of memory PinsussAnonymous6-Nov-02 22:05 
GeneralRe: Kick DataSet to out of memory Pinmembererick12219-Jun-03 17:39 
GeneralRe: Kick DataSet to out of memory PinmemberSimonS21-Jun-03 7:05 
GeneralRe: Kick DataSet to out of memory Pinmembererick12221-Jun-03 14:16 
GeneralRe: Kick DataSet to out of memory Pinmembererick12221-Jun-03 14:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 28 Jan 2002
Article Copyright 2002 by SimonS
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid