CODEWITHSUNDEEP
servicestackormlite-servicestack
BoKDamgaard
BoKDamgaard

Reputation: 398

Issue with SqlScalar<T> and SqlList<T> when calling stored procedure with parameters

The new API for Servicestack.OrmLite dictates that when calling fx a stored procedure you should use either SqlScalar or SqlList like this:

List<Poco> results = db.SqlList<Poco>("EXEC GetAnalyticsForWeek 1");
List<Poco> results = db.SqlList<Poco>("EXEC GetAnalyticsForWeek @weekNo", new { weekNo = 1 });

List<int> results = db.SqlList<int>("EXEC GetTotalsForWeek 1");
List<int> results = db.SqlList<int>("EXEC GetTotalsForWeek @weekNo", new { weekNo = 1 });

However the named parameters doesn't work. You HAVE to respect the order of the parameters in the SP. I think it is because the SP is executed using CommandType=CommandType.Text instead of CommandType.StoredProcedure, and the parameters are added as dbCmd.Parameters.Add(). It seems that because the CommandType is Text it expects the parameters to be added in the SQL querystring, and not as Parameters.Add(), because it ignores the naming. An example:

CREATE PROCEDURE [dbo].[sproc_WS_SelectScanFeedScanRecords] 
    @JobNo int = 0
   ,@SyncStatus int = -1
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
      FSR.ScanId
    , FSR.JobNo
    , FSR.BatchNo
    , FSR.BagNo
    , FSR.ScanType
    , FSR.ScanDate
    , FSR.ScanTime
    , FSR.ScanStatus
    , FSR.SyncStatus
    , FSR.JobId
    FROM dbo.SCAN_FeedScanRecords FSR
    WHERE ((FSR.JobNo = @JobNo) OR (@JobNo = 0) OR (ISNULL(@JobNo,1) = 1))
    AND   ((FSR.SyncStatus = @SyncStatus) OR (@SyncStatus = -1) OR (ISNULL(@SyncStatus,-1) = -1))
END

When calling this SP as this:

db.SqlList<ScanRecord>("EXEC sproc_WS_SelectScanFeedScanRecords @SyncStatus",new {SyncStatus = 1});

It returns all records with JobNo = 1 instead of SyncStatus=1 because it ignores the named parameter and add by the order in which they are defined in the SP. I have to call it like this:

db.SqlList<ScanRecord>("EXEC sproc_WS_SelectScanFeedScanRecords @SyncStatus=1");

Is this expected behavior? I think it defeats the anonymous type parameters if I can't trust it

TIA

Bo

Upvotes: 3

Views: 1037

Answers (1)

BoKDamgaard
BoKDamgaard

Reputation: 398

My solution was to roll my own methods for stored procedures. If people finds them handy, I could add them to the project

    public static void StoredProcedure(this IDbConnection dbConn, string storedprocedure, object anonType = null)
    {
        dbConn.Exec(dbCmd =>
        {
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.CommandText = storedprocedure;
            dbCmd.SetParameters(anonType, true);
            dbCmd.ExecuteNonQuery();
        });
    }
    public static T StoredProcedureScalar<T>(this IDbConnection dbConn, string storedprocedure, object anonType = null)
    {
        return dbConn.Exec(dbCmd =>
        {
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.CommandText = storedprocedure;
            dbCmd.SetParameters(anonType, true);
            using (IDataReader reader = dbCmd.ExecuteReader())
                return GetScalar<T>(reader);
        });
    }
    public static List<T> StoredProcedureList<T>(this IDbConnection dbConn, string storedprocedure, object anonType = null)
    {
        return dbConn.Exec(dbCmd =>
        {
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.CommandText = storedprocedure;
            dbCmd.SetParameters(anonType, true);
            using (var dbReader = dbCmd.ExecuteReader())
                return IsScalar<T>()
                    ? dbReader.GetFirstColumn<T>()
                    : dbReader.ConvertToList<T>();
        });
    }

They are just modified versions of the SqlScalar and SqlList plus the ExecuteNonQuery

Upvotes: 3

Related Questions