capture output parameter from stored proc in LINQ To SQL ExecuteQuery

Question

Is it possible to get an output parameter back from the LINQ To SQL DataContext when you execute a stored procedure?

IEnumerable<Address> result = 
    ExecuteQuery<Address>(((MethodInfo)(MethodInfo.GetCurrentMethod())), 
       address, pageIndex, pageSize, totalCount);

where address, pageIndex and pageSize are input parameters, and TotalCount is an output parameter.

How can you capture the output param?

here is another attempt at doing it but again cannot get the parameter value:


    [Function(Name = "Telecom.AddressSearch")]
        private IEnumerable SearchAddress([Parameter(Name = "address", DbType = "varchar")] string address,
                                             [Parameter(Name = "pageIndex", DbType = "int")] int pageIndex,
                                             [Parameter(Name = "pageSize", DbType = "int")] int pageSize,
                                             [Parameter(Name = "totalCount", DbType = "int")] ref int totalCount)
        {            
            IEnumerable result = ExecuteQuery(((MethodInfo)(MethodInfo.GetCurrentMethod())), address, pageIndex, pageSize, totalCount);

            return result;
        }

sgriffinusa · Accepted Answer · 2010-08-19 11:22:13Z

6

Scott Guthrie has a blog post that describes how to get LINQ to SQL to work with stored procedures. While his post does not directly answer your question, it provides a clue to something that may work. When defining a method in a .dbml class, "LINQ to SQL maps 'out' parameters in SPROCs as reference parameters (ref keyword)." You might try and use the ref keyword and see if the totalCount gets updated.

   IEnumerable r = ExecuteQuery(((MethodInfo)(MethodInfo.GetCurrentMethod())),
   address, pageIndex, pageSize, ref totalCount);

Update:

I did some more research, and have found a way that should work for you. This is from an MSDN article. You would need to extend your DataContext, but that shouldn't be too big of an issue (it appears you may already be doing this). Check out the article for more information, but the basic piece is this:

[Function(Name="dbo.CustOrderTotal")]
[return: Parameter(DbType="Int")]
public int CustOrderTotal([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID, [Parameter(Name="TotalSales", DbType="Money")] ref System.Nullable<decimal> totalSales)
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID, totalSales);
    totalSales = ((System.Nullable<decimal>)(result.GetParameterValue(1)));
    return ((int)(result.ReturnValue));
}

Where 'this' is your DataContext.

Update 2:

The IExecuteResult has a ReturnValue property. It is of type Object, so you will have to cast it to get the results, but it should allow you to get the Enumerable of the results. In your case, something like this should work:

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), address, pageIndex, pageSize, totalCount);
totalCount = ((System.Nullable<decimal>)(result.GetParameterValue(3)));
return (IEnumerable<Address>)result.ReturnValue;

edited Aug 19, 2010 at 11:22

answered Aug 18, 2010 at 14:10

sgriffinusa

4,2211 gold badge27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BBurke Over a year ago

I had tried that, but its says the params[] object has invalid parameters

BBurke Over a year ago

yeah i've had a look at that, but i didnt think it will work. i dont see how the IExecuteResult will return the Enumerable data

sgriffinusa Over a year ago

Updated answer again on how to get the return data.

Collectives™ on Stack Overflow

capture output parameter from stored proc in LINQ To SQL ExecuteQuery

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related