CODEWITHSUNDEEP
c#.netlinq-to-sql
user576510
user576510

Reputation: 5915

how I can skip passing optional parameter to stored procedure in Linq to SQL

I am using Linq to SQL with stored procedures.

I have to pass parameters to stored procedures even if they are optional (where I have set the default value for parameters in stored procedures). Is there any way that I may skip passing default parameters.

Thanks

Upvotes: 4

Views: 5717

Answers (2)

Mehdi Dehghani
Mehdi Dehghani

Reputation: 11611

As mentioned link in marked answer is dead, I put the idea here for future readers (of course I didn't read the mentioned article, but below way will work, I tested before posting here)

You need to overload auto-generated method of your SP (in .designer.cs class) and cut your optional parameter(s) (as you know, By default, when you drop a stored procedure onto DBMLclass file, it only auto-generates the default function)

For ex. with this SP:

CREATE PROCEDURE sp_Test
    @optionalParam INT = 0
AS
    -- your logic gose here

In .designer.cs you will got something like below by default

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.sp_Test")]
public ISingleResult<sp_TestResult> sp_Test([global::System.Data.Linq.Mapping.ParameterAttribute(Name="optionalParam", DbType="Int")] System.Nullable<int> optionalParam)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), optionalParam);
    return ((ISingleResult<sp_TestResult>)(result.ReturnValue));
}

So you need to overload this, here is the code:

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.sp_Test")]
public ISingleResult<sp_sp_TestResult> sp_Test()
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
    return ((ISingleResult<sp_sp_TestResult>)(result.ReturnValue));
}

Remember

  • The DBML designer file will be re-generated every time you add/remove any SP or table to it, so make sure to keep your methods before you do save on it, or alternatively move all overloaded methods to a new partial class.
  • If you have SPs with many optional parameters, you will have to generate overloaded methods for all possible combinations, and that can get annoying fast. Also, in this case you will have to call the SP with named parameters

Upvotes: 4

Priyank
Priyank

Reputation: 10623

You can do this: http://challadotnetfaq.blogspot.com/2009/05/stored-procedure-optional-parameters.html

you can if you map the sp to a method with optional parameters

Upvotes: 2

Related Questions