Suport for custom DB specific query parameters.

[GoogleCodeExporter]

Next case:

"SELECT * FROM table1 t1
    INNER JOIN @enum AS t2 ON t1.id = t2.t1_id
WHERE t1.some_field between (@param1 AND @param2)"

So, this query uses MsSql 2008 new feature of passing table-valued parameter 
@enum to the query(IEnumerable<SqlDataRecord> exactly). Suggested way with 
implementing IDynamicParameters is not fit my needs, because query have another 
two parameters, this means that I must create new IDynamicParameters inheritor 
every time when query requires another parameters set.

I looked for solution in Dapper source code and find that dapper already have 
implemented DbString class, from which i extracted interface 
ICustomQueryParameter and replaced DbString usages in SqlMapper to 
ICustomParameter. Patch file attached. 

    public interface ICustomQueryParameter
    {
        void AddParameter(IDbCommand command, string name);
    }

So, now it will look like

var rows = EnumerateRows();
var sql = @"SELECT * FROM table1 t1
            INNER JOIN @table_2 AS t2 ON t1.id = t2.t1_id
            WHERE t1.some_field between (@param1 AND @param2)";
var result = connection.Query(sql, 
    new 
    { 
        table_2 = new CustomQueryParameter(rows), 
        param1 = 1, 
        param2 = 10 
    }); 

Original issue reported on code.google.com by AdvanT...@gmail.com on 26 Oct 2011 at 4:37

Attachments:

• SqlMapper.patch

[GoogleCodeExporter]

I would really love to see Table valued parameters - specifically the 
SqlDbType.Structured supported

Original comment by chunkbuf...@gmail.com on 17 Nov 2011 at 2:48

[GoogleCodeExporter]

You can use my attached patch to modify dapper sources. There are just few 
lines of changes.

Original comment by AdvanT...@gmail.com on 17 Nov 2011 at 9:48

[GoogleCodeExporter]

I don't mind this, will run it by Marc

Original comment by sam.saff...@gmail.com on 19 Dec 2011 at 1:41

[GoogleCodeExporter]

We are find with the change, however can you submit it as a pull request (just 
clone the repo here or on github) 

Also we need a few test cases added. 

Original comment by sam.saff...@gmail.com on 22 Jan 2012 at 11:34

• Changed state: Accepted

[GoogleCodeExporter]

was anything ever done to add this functionality - using reflector on the 
latest Dapper I don't see that this was ever added

Original comment by chunkbuf...@gmail.com on 9 Aug 2012 at 8:33

[GoogleCodeExporter]

You can apply my attached patch to get this functionality. Sorry but i have no 
time to create pull request. Sam, please deliver this task to somebody of 
involved developers.

Original comment by AdvanT...@gmail.com on 10 Aug 2012 at 9:17

[GoogleCodeExporter]

ICustomQueryParameter was implemented.  Can't this be closed?

Original comment by richard....@gmail.com on 21 Aug 2013 at 1:49

[GoogleCodeExporter]

ICustomerQueryParameter is a step in the right direction, but why can't a 
DataTable variable be passed in directly?

Original comment by smdra...@gmail.com on 21 Aug 2013 at 3:39

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

This is great! howcome it hasn't been added to the original source?

Original comment by abramson...@gmail.com on 20 Feb 2014 at 4:01

[GoogleCodeExporter]

Using your code, I built a DataTable parameter and a IEnumerable<T> parameter:

    /// <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(null)
        {
            var t = typeof(T);
            var propertyByName = new Dictionary<string, PropertyInfo>();
            _table = new System.Data.DataTable();

            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);
            }
        }
    }

Original comment by abramson...@gmail.com on 20 Feb 2014 at 6:07