Candidate to support MS SQL TVPs

[GoogleCodeExporter]

*** THIS IS NOT AN ISSUE ***

Since I needed to support universal TVPs, I started playing around with your 
original test code and created a generic equivalent. It would probably be best 
if I created a separate NuGet package for this, like Dapper.Microsoft.Sql. Just 
wanted to share with you and if you had any comments regarding it.

    public class DynamicParam<T> : SqlMapper.IDynamicParameters
    {
        #region Fields

        private readonly Action<SqlDataRecord, object> actionMapping;

        private static readonly Dictionary<SqlDbType, Action<SqlDataRecord, object>> actionMappings = new Dictionary<SqlDbType, Action<SqlDataRecord, object>>
                                                                                               {
                                                                                                   { SqlDbType.Bit, (record, o) => record.SetBoolean(0, Convert.ToBoolean(o)) },
                                                                                                   { SqlDbType.TinyInt, (record, o) => record.SetByte(0, Convert.ToByte(o)) },
                                                                                                   { SqlDbType.SmallInt, (record, o) => record.SetInt16(0, Convert.ToInt16(o)) },
                                                                                                   { SqlDbType.Int, (record, o) => record.SetInt32(0, Convert.ToInt32(o)) },
                                                                                                   { SqlDbType.BigInt, (record, o) => record.SetInt64(0, Convert.ToInt64(o)) },
                                                                                                   { SqlDbType.NChar, (record, o) => record.SetChar(0, Convert.ToChar(o)) },
                                                                                                   { SqlDbType.NVarChar, (record, o) => record.SetString(0, Convert.ToString(o)) },
                                                                                               };

        private readonly string name;

        private readonly SqlMetaData[] tvpDefinition;

        private static readonly Dictionary<Type, SqlDbType> typeMappings = new Dictionary<Type, SqlDbType>
                                                                    {
                                                                        { typeof(bool), SqlDbType.Bit },
                                                                        { typeof(byte), SqlDbType.TinyInt },
                                                                        { typeof(Int16), SqlDbType.SmallInt },
                                                                        { typeof(Int32), SqlDbType.Int },
                                                                        { typeof(Int64), SqlDbType.BigInt },
                                                                        { typeof(char), SqlDbType.NChar },
                                                                        { typeof(string), SqlDbType.NVarChar },
                                                                    };

        private readonly string typeName;

        private readonly IEnumerable<T> values;

        #endregion

        #region Constructors and Destructors

        public DynamicParam(string name, string typeName, IEnumerable<T> values)
        {
            this.name = name;
            this.typeName = typeName;
            this.values = values;
            this.tvpDefinition = new[] { new SqlMetaData("n", typeMappings[typeof(T)]) };
            this.actionMapping = actionMappings[typeMappings[typeof(T)]];
        }

        #endregion

        #region Public Methods and Operators

        public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
        {
            var sqlCommand = (SqlCommand)command;
            sqlCommand.CommandType = CommandType.StoredProcedure;
            var listOfValues = this.values.Select(s =>
            {
                var rec = new SqlDataRecord(this.tvpDefinition);
                this.actionMapping.Invoke(rec, s);
                return rec;
            }).ToList();

            SqlParameter p = sqlCommand.Parameters.Add(this.name, SqlDbType.Structured);
            p.Direction = ParameterDirection.Input;
            p.TypeName = this.typeName;
            p.Value = listOfValues;
        }

        #endregion
    }

Sample use

List<Invoice> e =
  con.Query<Invoice>("dbo.[GetInvoices]",
    new DynamicParam<long>("@InvoiceIdSet", "TBIGINT", Enumerable.Range(i, 17).Select(Convert.ToInt64)),
    commandType: CommandType.StoredProcedure).ToList();

Original issue reported on code.google.com by ddaniele...@gmail.com on 31 Oct 2013 at 6:50

[GoogleCodeExporter]

Dapper.Microsoft.Sql posted to NuGet in a slightly modified version from the 
above.

Original comment by ddaniele...@gmail.com on 31 Oct 2013 at 8:09

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I would love to see a DynamicParam<T> where T is a class with more than 1 
property

Original comment by abramson...@gmail.com on 20 Feb 2014 at 3:41

[GoogleCodeExporter]

TVPs are handled directly in the library now

Original comment by marc.gravell on 5 Aug 2014 at 2:06

• Changed state: Fixed