Is It Possible To Use Output Parameters With Executequery?

Normally, when you want to call a stored procedure directly through Linq to Sql, you can use the ExecuteQuery method: result = dc.ExecuteQuery('Exec myStoredProcedur

Solution 1:

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) , @AddressIDintout  )
ASSELECT TOP 10*FROM Person.Address where City =@Cityselect  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;

namespaceTest
{
    classProgram
    {
        staticvoidMain(string[] args)
        {


            DataClasses1DataContextdc = newDataClasses1DataContext("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 SandPointeLane91 :7166 BrockLane92 :7126 EndingCt.93 :4598 ManilaAvenue94 :5666 HazelnutLane95 :1220 BradfordWay96 :5375 ClearlandCircle97 :2639 AnchorCourt98 :502AlexanderPl.99 :5802 AmpersandDrive13079

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

Solution 2:

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 integerstring 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"

Solution 3:

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.