Home how to pass key-value pair into a user-defined table type?
Reply: 1

how to pass key-value pair into a user-defined table type?

l--''''''---------''''''''''''
1#
l--''''''---------'''''''''''' Published in 2017-11-14 17:05:16Z

How do I pass a Dictionary or any other tuple type into a stored procedure?

The stored procedure is defined as:

CREATE PROCEDURE [dbo].[Components]
(
  @OrderTypeID INT = NULL,
  @OrderTypeKey VARCHAR(40) = NULL,
  @SegmentID INT = NULL,
  @SegmentKey VARCHAR(40) = NULL,
  @FilterPairs KeyValuePair READONLY
)
AS 
BEGIN
..

Where KeyValuePair is defined as:

This is how I am calling the sproc:

context.Database.SqlQuery<EntityType>(
    @"declare param1 int = --some logic
      declare param2 varchar = --some logic
      EXEC Components @param1=@param1,@param2=@param2"
);

How do I pass a Dictionary or any other tuple type into a stored procedure for a user-defined table-type parameter?

Please note that I do not want to use SqlParameter for this implementation if possible.

Harald Coppoolse
2#
Harald Coppoolse Reply to 2017-11-15 09:04:55Z

As you tag the question Entity Framework, I assume you have a DbContext.

This DbContext represents (the access to) your database. It has DbSets for all tables in the database. It knows the relations between the tables, and it should also know how to call the stored procedures.

After all, should a user of the dbContext need to do something very different when calling a stored procedure than when handling a table?

Therefore the call to your stored procedure ought to be a function in your DbCntext

class MyDbContext : DbContext
{
    public DbSet<...> Table1 {get; set;}
    public DbSet<...> Table2 {get; set;}

    #region stored procedures
    public void Components(int orderTypeId, string orderTypeKey, ...
         KeyValuePair<string, string> pair, ...)
    {
        // TODO: fill code
    }
}

Change your stored procedure. @FilterPairs should be two parameters:

@FilterKey,
@FilterValue,

Now it will be easy to implement procedure Components:

public void Components(int orderTypeId, string orderTypeKey, ...
    KeyValuePair<string, string> pair, ...)
{
    object[] functionParameters = new object[]
    {
        new SqlParameter(@"OrderTypeId", orderTypeId),
        new SqlParameter(@"OrderTypeKey", orderTypeKey),
        ...
        // The KeyValuePair: in your store procedure two parameters:
        new SqlParameter(@"FilterKey", pair.Key),
        new SqlParameter(@"FilterValue", pair.Value),
    };
    const string sqlCommans = @"Exec ...
        @OrderTypeId, @OrderTypeKey, ...
        @FilterKey, @FilterValue, ...";
    this.Database.ExecuteSqlCommand(sqlComman, functionParameters);
}

You'll know what to do if you want a function that takes a Dictionary, or any other class that implements IEnumerable<KeyValuePair<string, string>>

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.477493 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO