0

I can't seem to figure out why I can't get my code below working. I confirmed that my DataTable has records when calling the stored procedure, however the actual table-valued parameter that arrives at my stored procedure has no records. (this was confirmed by logging a rowcount in the proc). Any ideas as to what might be my issue? I'm using EntityFrameworkCore 6.

public class MyDbContext : DbContext, IMyDbContext
{

    public MyDbContext()
    {
    }

    public MyDbContext(DbContextOptions<MyDbContext> options)
        : base(options)
    {
    }

    public int InsertStagingData(DataTable data)
    {
        var parameter = new SqlParameter("@TVP_StagingData", data)
        {
            TypeName = "dbo.dataType",
            SqlDbType = SqlDbType.Structured
        };

        var response = this.Database.ExecuteSqlRaw("EXEC [dbo].[sp_InsertStagingData]", parameter);
        return response;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
    }
}
kdubbers
  • 33
  • 1
  • 6
  • I am guessing the trailing `}` is a typo here from copy/paste? – Mark Schultheiss Oct 13 '22 at 22:40
  • Add the SQL `CREATE TYPE` code for `@TVP_StagingData` i.e. the perhaps not as clear name of the type as `dbo.dataType` with the columns as input to the stored procedure whatever this is (used in `sp_InsertStagingData`) to match to the rows of `data` - which perhaps needs a "type" similar to the `List` simple one I put in my answer given I did not have those. i.e. c# class which maps the input to the TVP columns - which for simplicity say is then passed here as `(List myTypeModelList)`. Consider using Dapper, Automapper or some such to simplify things a bit. – Mark Schultheiss Oct 14 '22 at 21:21

1 Answers1

0

Cannot see your type in SQL but here I will go with a simplified example of a list of strings. Somewhat pseudo code here since I am just typing this...

  List<string> coolStrings = new List<string>{"one","two","three","redfish","bluefish"};
  var coolParam = new SqlParameter { ParameterName = "@coolStrings ", SqlDbType = SqlDbType.Structured, Direction = ParameterDirection.Input, Value = coolStrings, TypeName = "dbo.StringList" };
  var procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };

  this.Database.ExecuteSqlCommand("EXEC @procResult = [dbo].[sp_InsertStagingData] @coolStrings", coolParam , procResultParam);
  return (int) procResultParam.Value;

Just for clarity a simple type SQL

CREATE TYPE [dbo].[StringList] AS TABLE (
    [val] VARCHAR (1000) NOT NULL);
Mark Schultheiss
  • 32,614
  • 12
  • 69
  • 100
  • My sql data type is a table. The proc is successfully getting the DataTable, its just not getting the records within the DataTable. Its difficult to explain but hopefully that makes sense. – kdubbers Oct 14 '22 at 02:15