Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does Dapper support SQL 2008 Table-Valued Parameters 2?

I know that dapper can support TVF, but how do you send extra parameters along with TVF (without adding it to the IntDynamicParam class)? See the below example from Tests.cs, i have modified to add the extra parameter:

connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
connection.Execute("CREATE PROC get_ints @x int, @ints int_list_type READONLY AS select * from @ints");

I tried the following but got errors (No mapping exists from object type SqlMapper.Tests+IntDynamicParam to a known managed provider native type.):

var p = new DynamicParameters();
p.Add("x", 4);
p.Add("ints",new IntDynamicParam(new int[] { 1, 2, 3 }));

var nums = connection.Query<int>("get_ints", p).ToList();

Thank you for the reply Sam, but the question was a little different. I want to know how to pass in another variable along with the tuple. See the modified SP below:

CREATE TYPE int_tuple_list_type AS TABLE (n int NOT NULL PRIMARY KEY, n2 int)

CREATE PROC get_int_tuples 
  @someVar varchar(10),
  @ints int_tuple_list_type READONLY
AS select * from @ints
like image 631
mcpunjabi Avatar asked Aug 02 '11 14:08

mcpunjabi


People also ask

How to send table-valued parameter in SQL Server?

Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

How to use table-valued parameter in c#?

You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL.

How to update table-valued parameters in stored procedure?

You can't change the column values in the rows of a parameter with a table value, and you can't insert or subtract rows. You must inject the data into a temporary table or into a table variable to change the data that is transferred to a stored procedure or parameterized expression in a table-valued parameter.

What is a TVP in SQL?

Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.


2 Answers

There is very little magic about IDynamicParameters all you need to worry about is implementing AddParameters on the ready to run open IDbCommand.

Say you wanted a tuple of ints, you could implement the following:

CREATE TYPE int_tuple_list_type 
     AS TABLE (n int NOT NULL PRIMARY KEY, n2 int)
CREATE PROC get_int_tuples @ints 
     int_tuple_list_type READONLY AS select * from @ints

Followed by:

class TupleIntDynamicParam : Dapper.SqlMapper.IDynamicParameters
{
    IEnumerable<int> tuples;
    public IntDynamicParam(IEnumerable<Tuple<int,int>> tuples)
    {
        this.tuples= tuples;
    }

    public void AddParameters(IDbCommand command)
    {
        var sqlCommand = (SqlCommand)command;
        sqlCommand.CommandType = CommandType.StoredProcedure;

        List<Microsoft.SqlServer.Server.SqlDataRecord> number_list = 
           new List<Microsoft.SqlServer.Server.SqlDataRecord>();

        // Create an SqlMetaData object that describes our table type.
        Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { 
          new Microsoft.SqlServer.Server.SqlMetaData("n", SqlDbType.Int), 
          new Microsoft.SqlServer.Server.SqlMetaData("n2", SqlDbType.Int) };

        foreach (int n in tuples)
        {
            // Create a new record, using the metadata array above.
            Microsoft.SqlServer.Server.SqlDataRecord rec = 
                new Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition);
            rec.SetInt32(0, n.Item1);
            rec.SetInt32(1, n.Item2);
            number_list.Add(rec);      // Add it to the list.
        }

        // Add the table parameter.
        var p = sqlCommand.Parameters.Add("ints", SqlDbType.Structured);
        p.Direction = ParameterDirection.Input;
        p.TypeName = "int_tuple_list_type";
        p.Value = number_list;

    }
}

Then you can pass in tuples with:

var nums = connection.Query("get_int_tuples", 
      new TupleIntDynamicParam (new Tuple<int,int>[] 
      { 
           Tuple.Create(1,2), Tuple.Create(2,3) 
      })).ToList();
like image 133
Sam Saffron Avatar answered Sep 28 '22 15:09

Sam Saffron


Here's a complete solution using the ICustomQueryParameter interface and some code I wrote (in the bottom of attached page) to let you send any IEnumerable to a storedprocedure using dapper, even when T has multiple properties:

http://code.google.com/p/dapper-dot-net/issues/detail?can=2&start=0&num=100&q=ICustomQueryParameter&colspec=ID%20Type%20Status%20Priority%20Milestone%20Owner%20Summary&groupby=&sort=&id=69

Here's the code from the link:

  /// <summary>
    /// Send DataTable as dapper parameter
    /// </summary>
    public class DapperTableParameter : ICustomQueryParameter
    {
        protected DataTable _table = null;

        public DapperTableParameter(DataTable table)
        {
            _table = table;
        }

        public void AddParameter(System.Data.IDbCommand command, string name)
        {
            // This is SqlConnection specific
            ((SqlCommand)command).Parameters.Add("@" + name, SqlDbType.Structured).Value = _table;
        }
    }



public class DapperTVP<T> : DapperTableParameter
    {
        public DapperTVP(IEnumerable<T> list) : base(new System.Data.DataTable())
        {
            var t = typeof(T);
            var propertyByName = new Dictionary<string, PropertyInfo>();

            foreach (var p in t.GetProperties())
            {
                propertyByName.Add(p.Name, p);
                _table.Columns.Add(p.Name, p.PropertyType);
            }

            foreach (var i in list)
            {
                var row = _table.NewRow();
                foreach (var p in propertyByName)
                {
                    row[p.Key] = p.Value.GetValue(i, null);
                }

                _table.Rows.Add(row);
            }
        }
    }

You're still gonna have to merge ICustomQueryParameter like this: http://code.google.com/p/dapper-dot-net/issues/attachmentText?id=69&aid=690000000&name=SqlMapper.patch&token=wFLdWLM4LPamcAwcDaGqcITaAmg%3A1392913796708

like image 39
Uri Abramson Avatar answered Sep 28 '22 13:09

Uri Abramson