Stored Procedure Optional Parameters using LINQ to SQL

By default using the LINQ to SQL DataContext design surface (or the SQL Metal command line tool), all parameters are created for a Stored Procedure signature. This essentially means that when coding against the stored procedure method in code, NO PARAMETER argument is optional.

For example; Consider the following stored procedure signature -


CREATE PROCEDURE [dbo].[ChangingInputParameters]
(
@p1 int, /* mandatory */
@p2 int = 2, /* optional integer */
@p3 nvarchar(15), /* mandatory nvarchar */
@p4 nvarchar(25) output /* input/output nvarchar */
)


Even though @p2 has a default value specified and is optional. The LINQ to SQL tools create a C# signature that always expects a value to be passed in. Whilst the designer tools support only the full signature, LINQ to SQL supports the mapping of any method signature to the Stored Procedure call. You just need to define it yourself.

Steps to update the DataContext within VS 2008 -

1. From within Visual Studio 2008, drag the StoredProcedure onto the DataContext (LINQ to SQL Data Class) designer window. This creates the following function definition in the data context code behind file.



[Function(Name="dbo.ChangingInputParameters")]
public ISingleResult ChangingInputParameters(
[Parameter(DbType="Int")] System.Nullable p1,
[Parameter(DbType="Int")] System.Nullable p2,
[Parameter(DbType="NVarChar(15)")] string p3,
[Parameter(DbType="NVarChar(25)")] ref string p4)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), p1, p2, p3, p4);
p4 = ((string)(result.GetParameterValue(3)));
return ((ISingleResult)(result.ReturnValue));
}


2. Create a new partial class for the data context (TIP: Cut and past the using clause, down to the DataContext class definition; Remove the attribute from the class and fix the curly-parenthesis). You should end up with a class looking like this -



namespace [... will vary ...]
{
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Data;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;
using System.Linq.Expressions;
using System.ComponentModel;
using System;

public partial class ChangingInputParametersDataContext : System.Data.Linq.DataContext
{
}


3. Copy the original function definition in the code-behind file (e.g. ChangingInputParameters.designer.cs) to your new partial class file.


4. Remove the input arguments, including the in-line attribute declaring each argument as a Parameter.


5. Return output parameters (those marked INPUT/OUTPUT in SQL, and those marked with a 'ref' prefix in C# are accessed via their index position in this method declaration. Update 'x' in any lines getting the output parameter results, so that the correct parameter is mapped to the correct C# instance variable.


result.GetParameterValue(x)


You should have a method that looks like this -



// Modifying the parameter list by dropping the arguments from this list, and updating any OUT parameters...
[Function(Name = "dbo.ChangingInputParameters")]
public ISingleResult ChangingInputParameters(
[Parameter(DbType = "Int")] System.Nullable p1, // NOTICE: NO P2 Declaration! It is optional in the Stored Proc
[Parameter(DbType = "NVarChar(15)")] string p3,
[Parameter(DbType = "NVarChar(25)")] ref string p4)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), p1, p3, p4);
p4 = ((string)(result.GetParameterValue(2))); // NOTICE: We had to update the index position because the methods arguments changed.
return ((ISingleResult)(result.ReturnValue));
}