SQL Stored Procedure with TableValuedParameters

#1

Hi,

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,
        clientDto.SystemId,
        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;
    }
#2

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;
#3

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?

#4

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

1 Like
#5

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

1 Like
#6

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

#7

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
#8

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.

3 Likes
#9

Not sure if this is still an issue, but here is an example:

public async Task<(int, List<Guid>)> Search(string searchString,
  IEnumerable<KeyValuePair<string, string>> filterParams, int skip, int take, CancellationToken ct = default)
{
  var dataTableTmp = new DataTable();

  dataTableTmp.Columns.Add("searchKey", typeof(string));
  dataTableTmp.Columns.Add("searchValue", typeof(string));
  if (filterParams != null)
  {
    foreach (var item in filterParams)
    {
      dataTableTmp.Rows.Add(item.Key, item.Value);
    }
  }
  var result = new List<Guid>();
  var totalCount = 0;
  using (var db = await dbFactory.OpenAsync(ct).ConfigureAwait(false))
  {
    ct.ThrowIfCancellationRequested();

    result.AddRange(db.Exec(dbCmd =>
    {
      dbCmd.CommandType = CommandType.StoredProcedure;
      dbCmd.Parameters.Add(new SqlParameter("@filterParams", dataTableTmp));
      dbCmd.Parameters.Add(new SqlParameter("@searchString", searchString ?? ""));
      dbCmd.Parameters.Add(new SqlParameter("@skip", skip));
      dbCmd.Parameters.Add(new SqlParameter("@take", take));

      var outputParam = new SqlParameter("@totalCount", DbType.Int32);
      outputParam.Direction = ParameterDirection.Output;
      dbCmd.Parameters.Add(outputParam);

      dbCmd.CommandText = "spSearch";

      var result = dbCmd.ConvertToList<Guid>();
      totalCount = (int)outputParam.Value;
      return result;
    }));

  }
  return (totalCount, result);
}

This will return a list of GUIDs (which in out case is a list of profile IDs we are looking for)
Keep in mind that you need to create the custom data type in SQL as well and it needs to match the signature of the DataTable.
Also, this particular SP gives the total number of matched items in the @totalCount ouput param. Might be handy for skip/take functionality.

2 Likes