SQL Stored Procedure with TableValuedParameters


I’m out of ideas for how to execute a stored procedure via ServiceStack.OrmLite and pass in a table valued parameter in our .net core service.

I can use SqlMapper’s QueryMultiple but was trying to use SqlList(). Is there a way to do this with SqlList instead?

    var accessibleSystemIdsDataTable = userAccess.Systems.Map(s => s.SystemId).ToListIntDataTable();
    var accessibleSystemIds = accessibleSystemIdsDataTable.AsTableValuedParameter("ListInts");

    var clientContactParameters = new DynamicParameters(new
        ClientId = clientDto.Id,
        AccessibleSystemIds = accessibleSystemIds
    using (var multiResultSet = Db.QueryMultiple("schema.sp_name", clientContactParameters, commandType: CommandType.StoredProcedure))
        var contacts = multiResultSet.Read<Fusion.ServiceModel.Database.Core.Composite.Contact>().ToList();

        var contactDtos = contacts.ConvertAll(c => c.ToDto());
        clientDto.Contacts = contactDtos;

Have not tried calling SPs with table valued params, but here’s an example using OrmLite to call an SP with SqlList:

IDbDataParameter pTotal = null;
var results = db.SqlList<LetterFrequency>("spSearchLetters", cmd => {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.AddParam("pLetter", "C");
        pTotal = cmd.AddParam("pTotal", direction: ParameterDirection.Output);
var total = pTotal.Value;

Thanks, yeah I’ve called SPs from ormlite lots on our old servicestack service.

We’re porting to netcore and I’m doing a code cleanup and refactor as we go. I was trying to move away from the MultiQuery approach we were using before so there’s less SQL ‘magic’ and the c# program flow reads better.

Do you tend to just write all your sql access in c# in the services and avoid stored procedures?

Yeah, I’d only use stored procedures when major efficiency gains are possible, otherwise I’d keep all logic in C#.

1 Like

Guess I’ll need to learn LINQ a bit better then :face_with_monocle:

1 Like

Do you have a good example of a reasonably complicated multi join select statement, selecting columns from multiple tables?

Here’s some SqlExpression with JOIN examples in the docs.

Whilst there’s a lot of join examples in OrmLite’s tests, e.g:

1 Like

one scenario stored procs (and views) come in handy is when you have to deal with a less than ideal legacy data model… the procs & views create the abstraction of the more preferred model and map it back into actual tables… allows you to avoid proliferating undesirable structure into C# logic and creates opportunities for a cleaner future while you go along without being disruptive to legacy dependencies on the existing schema… it’s the basis of a transitional change management strategy.