Linq to SQL SP parameters

Question

I have a SP which I want to call from linq. The SP have 5 parameters but i only want to pass/need to 2 of them.

how would I call the SP as when i go to add the parameters in code it wont build as it want all 5.

p.campbell · Accepted Answer · 2009-07-30T22:24:04.307

3

You'd have something like this:

MyDbDataContext db = new MyDbDataContext();

db.MyStoredProc(customerID, "sometext", null, null, null);

Success/failure would depend on the SQL statements inside your sproc dealing with nulls on those last 3 parameters.

edited Jul 30 '09 at 22:24

answered Jul 30 '09 at 22:18

p.campbell

does LinqToSql make the arguments nullable types automatically? I can't remember. If not, this wont work. – Allen Rice Jul 30 '09 at 23:31
Yes, LinqToSql will make them nullable types (assuming the field is nullable). – Jacob Proffitt Jul 31 '09 at 00:06

score 2 · Answer 2 · answered Jul 30 '09 at 22:21

2

Another option is to drag the stored proc into your DBML a second time and delete the parameters you don't want to pass in.

answered Jul 30 '09 at 22:21

Same as my approach but sexier since the UI does it for you, not bad. – Allen Rice Jul 30 '09 at 23:43
There is an important behavior difference. In the case of non-nullable values, passing in 0s as you suggest would override any default values set within the stored proc declaration. – Frank Schwieterman Jul 31 '09 at 00:42

score 1 · Answer 3 · answered Jul 30 '09 at 22:17

overload the sproc call.

I dont have it in front of me but you'd goto your DataAccessName.cs file and create a method name with the same signature

auto gen'd sproc method signature:

void sp_sproc(int a, int b, int c, int d, int e);

you'd make this in your DataAccessName.cs file

void sp_sproc(int a, int b)
{
    this.sp_sproc(a,b,0,0,0);
}

if the params are nullable, like so

void sp_sproc(int? a, int? b, int? c, int? d, int? e);

then you could do

void sp_sproc(int a, int b)
{
    this.sp_sproc(a,b,null,null,null);
}

3 Answers3