Wrapping Stored Procedures in .NET Languages

by Jonathan Allen on Jun 03, 2009 |

Creating wrapper functions for pre-existing stored procedures is surprisingly difficult in .NET. Stored procedures have certain calling conventions that aren’t generally used in the .NET Framework. These include:

Pass by Reference Parameters
Output Parameters
Optional Parameters with default values
All parameters are nullable

Until recently, full mappings were not possible. C# doesn’t currently support optional parameters, making placeholders for missing parameters necessary. VB could handle optional parameters, but not if those parameters were also nullable. For example, you cannot write:

Function FindCustomer(Optional ByVal firstName As String = Nothing, _ 
                      Optional ByVal lastName As String = Nothing, _
                      Optional ByVal zipCode As Integer? = Nothing) As DataTable

This becomes a real concern when call search-style stored procedures that then to have numerous optional parameters. With C# 4 and VB 10, the last of these holes have been addressed. C# now supports optional values and both languages can use optional types in conjunction with nullable structures.

Feature	C#	Mapping	VB	Mapping	F#	Mapping
Pass by Reference	1	ref type [param]	7	ByRef [param] As [type]	4	[param] : [type] byref
Output	1	out type [param]	7	ByRef [param] As [type]	4	[param] : [type] byref
Optional Strings	4	string param = [default]	7	Optional [param] As String = [default]	N/A 4	Normal functions do not support optional parameters. Method functions use: ?[param] : [type]
Optional (other)	4	[type] param = [default]	7	Optional param As [type] = [default]	N/A 4
Nullable Strings	1	string param	7	param as String	4	[param] : string [param] : string option
Nullable (other)	2	[type]? param	8	param As Nullable(of [type])	4	[param] : Nullable<[type]> [param] : [type] option
Nullable (other)	2	[type]? param	9	param as [type]?	4	[param] : Nullable<[type]> [param] : [type] option
Optional Nullable (other)	4	[type]? param = [default]	10	Optional param As [type]? = [default]	4	?[param] : Nullable<[type]> ?[param] : [type] option

It should be noted that while F# does support returning multiple values, the support is very limited. There is no way to update more than one arbitrary mutable value without using the byref syntax.

Related Editorial

Application Performance Management Buyer's Guide

Infographic: Just How Big is the Nerd Economy

A Unified Mobile Architecture for the Modern Data Center

Faster PHP Through Java

Speed Matters: See why Nike, Groupon, and Comcast are using New Relic

Hello stranger!

You need to Register an InfoQ account or to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Community comments

Real .NET to Stored Procedure Implementation by Daniel Bullington Posted

Real .NET to Stored Procedure Implementation by Daniel Bullington

I have successfully created a framework for mapping interface methods to database stored prcoedure calls, as part of my blog code suite.

The framework is well engineered ans fully unit (in the true sense) tested.

The code is at: www.codeplex.com/SoftwareIsHardwork
Blog: blog.softwareishardwork.com

For example:

[DatabaseContract]
public interface IMockDatabaseContract : IDisposable
{
[CommandContract(CommandType.StoredProcedure, "usp_DoSomething", CommandBehavior.Default, -1, false)]
void DoSomething([ParameterContract(DbType.Int32, 0, "@ItemId")] int itemId);
}

would map to

exec usp_DoSomething @ItemId

but the .NET code ONLY sees the contract; a factory dishes out transparent prxoies which intercept these methods and do the real database work.

Take a look...

dpb

D. P. Bullington