Is it possible to use output parameters with ExecuteQuery<T>?

Question

Normally, when you want to call a stored procedure directly through Linq to Sql, you can use the ExecuteQuery method:

result = dc.ExecuteQuery<MyTable>("Exec myStoredProcedure");

And if you need to call it with parameters, you can add them through string substitution:

string query = "Exec myStoredProcedure ";
for (int i = 0; i < parameters.Count - 1; i++) {
  query += " {" + i + "},";
}
query = query.TrimEnd(',');
result = dc.ExecuteQuery<MyTable>(query, parameters);

But what if one of the parameters is an output variable? Is it possible to get the value back after the procedure has been run?

Conrad Frix · Accepted Answer · 2010-12-10 17:43:55Z

Alper Ozcetin's is right you can map StoredProcedures in *.dbml and you can use StoredProcedures as Method.

Below is demo doing this with the AdventureWorks DB and works for both vs2008 and vs2010

Wtih AdventureWorks I created the following Proc

CREATE PROC sp_test (@City  Nvarchar(60) , @AddressID int out  )
AS
SELECT TOP 10 * FROM Person.Address where City = @City
select  top 1  @AddressID  = AddressID FROM Person.Address where City = @City

I then added sp_test to a dbml and wrote the following program

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {


            DataClasses1DataContext dc = new DataClasses1DataContext("SomeSQLConnection);

            int? AddressID = null;
            ISingleResult<sp_testResult> result = dc.sp_test("Seattle", ref AddressID);

            foreach (sp_testResult addr in result)
            {
                Console.WriteLine("{0} : {1}", addr.AddressID, addr.AddressLine1);
            }
            Console.WriteLine(AddressID);


        }
    }
}

This results in the following ouput

23 : 6657 Sand Pointe Lane
91 : 7166 Brock Lane
92 : 7126 Ending Ct.
93 : 4598 Manila Avenue
94 : 5666 Hazelnut Lane
95 : 1220 Bradford Way
96 : 5375 Clearland Circle
97 : 2639 Anchor Court
98 : 502 Alexander Pl.
99 : 5802 Ampersand Drive
13079

You'll notice that the input into the sp_test method is a ref

Maksym Gontar · Accepted Answer · 2009-02-18 11:23:25Z

0

I'm not sure, but you can try to declare variable in query, pass it as an output parameter and then select it:

//assuming you out parameter is integer
string query = "DECLARE @OUT INT ";
query += " Exec myStoredProcedure ";
for (int i = 0; i < parameters.Count - 1; i++) {
  query += " {" + i + "},";
}
//assuming the output parameter is the last in the list
query += " @OUT OUT ";
//select value from out param after sp execution
query += " SELECT @OUT"

answered Feb 18, 2009 at 11:23

Maksym Gontar

22.8k10 gold badges80 silver badges115 bronze badges

Comments

Alper · Accepted Answer · 2009-04-23 10:52:32Z

0

You don't need to write raw SQL for StoredProcedures in ExecuteQuery. You can map StoredProcedures in *.dbml and you can use StoredProcedures as Methods.

answered Apr 23, 2009 at 10:52

Alper

1,40314 silver badges16 bronze badges

1 Comment

Adam Lassek Over a year ago

dbml-based stored procedures don't support output parameters, as far as I can tell.

Collectives™ on Stack Overflow

Is it possible to use output parameters with ExecuteQuery<T>?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related