Click here to Skip to main content
12,830,941 members (40,830 online)
Click here to Skip to main content
Add your own
alternative version


38 bookmarked
Posted 27 Jan 2002

C# Data Class

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


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)
    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,

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.


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.


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.


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


About the Author

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
Email : simon AT
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.

You may also be interested in...


Comments and Discussions

QuestionIs this still useful, and what is cDat? Pin
neil_b2-Sep-07 2:16
memberneil_b2-Sep-07 2:16 
AnswerRe: Is this still useful, and what is cDat? Pin
SimonS2-Sep-07 2:26
memberSimonS2-Sep-07 2:26 
Internally we still use parts of this code as a general abstraction from SQL.

Project has been superceded by :

cDat is a variable within this sample project.


> company:: Broken Keyboards Software
> blog:: brokenkeyboards

> skype :: SimonMStewart
> CV :: PDF

GeneralKick DataSet to out of memory Pin
Anonymous6-Nov-02 23:05
sussAnonymous6-Nov-02 23:05 
GeneralRe: Kick DataSet to out of memory Pin
erick12219-Jun-03 18:39
membererick12219-Jun-03 18:39 
GeneralRe: Kick DataSet to out of memory Pin
SimonS21-Jun-03 8:05
memberSimonS21-Jun-03 8:05 
GeneralRe: Kick DataSet to out of memory Pin
erick12221-Jun-03 15:16
membererick12221-Jun-03 15:16 
GeneralRe: Kick DataSet to out of memory Pin
erick12221-Jun-03 15:22
membererick12221-Jun-03 15:22 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170326.1 | Last Updated 28 Jan 2002
Article Copyright 2002 by SimonS
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid