R – SubSonic Return ExecuteSingle for Stored Procedure

stored-proceduressubsonic

I wish to return a single ScPollOption item using a Stored Procedure via the code below:

public ScPollOption FetchPollOptionByID(int optionID)
    {
        StoredProcedure sp = SPs.ScPollOptionGetOptionByID(optionID);
        return sp;
    }

When working with a query I would use:

ExecuteSingle<ScPollOption>()

but SubSonic only allows for sp.ExecuteTypedList<> and sp.ExecuteScalar<>.

How can I return a single ScPollOption item?

Thanks

Dan

Best Answer

I know it's not terribly attractive, but this would work if you're able to use the LINQ extensions:

sp.ExecuteTypedList<ScPollOption>().FirstOrDefault();

You could also execute an IDataReader and inflate the ScPollOption object manually:

ScPollOption item;

using (IDataReader reader = sp.ExecuteReader())
{
    if (reader.Read())
    {
        item = new ScPollOption();
        item.SomeProperty = reader.GetValue(0);
        // Set additional properties
    }
}

return item;

Related Solutions

R – SubSonic and Stored Procedures

If the results of the stored procedure has the same schema as one of your tables, you can build a collection using this code (SubSonic 2.1):

ProductCollection coll = new ProductCollection();
coll.LoadAndCloseReader(SPs.GetProducts(1).GetReader());

Sql-server – SQL Server: Query fast, but slow from procedure

I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure.

I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g.

Slow way:

CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))
AS
BEGIN
    SELECT * 
    FROM orders
    WHERE customerid = @CustID
END

Fast way:

CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))
AS
BEGIN
    DECLARE @LocCustID varchar(20)
    SET @LocCustID = @CustID

    SELECT * 
    FROM orders
    WHERE customerid = @LocCustID
END

Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.

Best Answer

Related Solutions

R – SubSonic and Stored Procedures

Sql-server – SQL Server: Query fast, but slow from procedure

Related Topic