Dapper/tests/Dapper.Tests/ParameterTests.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Dynamic;
using System.Globalization;
using System.Linq;
using System.Text.RegularExpressions;
using Xunit;

#if ENTITY_FRAMEWORK
using System.Data.Entity.Spatial;
using Microsoft.SqlServer.Types;
#endif

[assembly: CollectionBehavior(DisableTestParallelization = true)]

namespace Dapper.Tests
{
    [Collection(NonParallelDefinition.Name)] // because it creates SQL types that compete between the two providers
    public sealed class SystemSqlClientParameterTests : ParameterTests<SystemSqlClientProvider> { }
#if MSSQLCLIENT
    [Collection(NonParallelDefinition.Name)] // because it creates SQL types that compete between the two providers
    public sealed class MicrosoftSqlClientParameterTests : ParameterTests<MicrosoftSqlClientProvider> { }
#endif
    public abstract class ParameterTests<TProvider> : TestBase<TProvider> where TProvider : DatabaseProvider
    {
        public class DbDynamicParams : SqlMapper.IDynamicParameters, IEnumerable<IDbDataParameter>
        {
            private readonly List<IDbDataParameter> parameters = new List<IDbDataParameter>();
            public IEnumerator<IDbDataParameter> GetEnumerator() { return parameters.GetEnumerator(); }
            IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); }
            public void Add(IDbDataParameter value)
            {
                parameters.Add(value);
            }

            void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)
            {
                foreach (IDbDataParameter parameter in parameters)
                    command.Parameters.Add(parameter);
            }
        }
        
        public class DbCustomParam : SqlMapper.ICustomQueryParameter
        {
            private readonly IDbDataParameter _sqlParameter;

            public DbCustomParam(IDbDataParameter sqlParameter)
            {
                _sqlParameter = sqlParameter;
            }

            public void AddParameter(IDbCommand command, string name)
            {
                command.Parameters.Add(_sqlParameter);
            }
        }

        public readonly struct DbCustomParamStruct : SqlMapper.ICustomQueryParameter
        {
            private readonly IDbDataParameter _sqlParameter;

            public DbCustomParamStruct(IDbDataParameter sqlParameter)
            {
                _sqlParameter = sqlParameter;
            }

            public void AddParameter(IDbCommand command, string name)
            {
                command.Parameters.Add(_sqlParameter);
            }
        }

        private static IEnumerable<IDataRecord> CreateSqlDataRecordList(IDbCommand command, IEnumerable<int> numbers)
        {
#pragma warning disable CS0618 // Type or member is obsolete
            if (command is System.Data.SqlClient.SqlCommand) return CreateSqlDataRecordList_SD(numbers);
#pragma warning restore CS0618 // Type or member is obsolete
            if (command is Microsoft.Data.SqlClient.SqlCommand) return CreateSqlDataRecordList_MD(numbers);
            throw new ArgumentException(nameof(command));
        }
        private static IEnumerable<IDataRecord> CreateSqlDataRecordList(IDbConnection connection, IEnumerable<int> numbers)
        {
#pragma warning disable CS0618 // Type or member is obsolete
            if (connection is System.Data.SqlClient.SqlConnection) return CreateSqlDataRecordList_SD(numbers);
#pragma warning restore CS0618 // Type or member is obsolete
            if (connection is Microsoft.Data.SqlClient.SqlConnection) return CreateSqlDataRecordList_MD(numbers);
            throw new ArgumentException(nameof(connection));
        }

#pragma warning disable CS0618 // Type or member is obsolete
        private static List<Microsoft.SqlServer.Server.SqlDataRecord> CreateSqlDataRecordList_SD(IEnumerable<int> numbers)
        {
            var 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) };

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

            return number_list;
        }
#pragma warning restore CS0618 // Type or member is obsolete

        private static List<Microsoft.Data.SqlClient.Server.SqlDataRecord> CreateSqlDataRecordList_MD(IEnumerable<int> numbers)
        {
            var number_list = new List<Microsoft.Data.SqlClient.Server.SqlDataRecord>();

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

            foreach (int n in numbers)
            {
                // Create a new record, using the metadata array above.
                var rec = new Microsoft.Data.SqlClient.Server.SqlDataRecord(tvp_definition);
                rec.SetInt32(0, n);    // Set the value.
                number_list.Add(rec);      // Add it to the list.
            }

            return number_list;
        }


        private class IntDynamicParam : SqlMapper.IDynamicParameters
        {
            private readonly IEnumerable<int> numbers;
            public IntDynamicParam(IEnumerable<int> numbers)
            {
                this.numbers = numbers;
            }

            public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
            {
                command.CommandType = CommandType.StoredProcedure;

                var number_list = CreateSqlDataRecordList(command, numbers);

                AddStructured(command, number_list);
            }
        }

        private class IntCustomParam : SqlMapper.ICustomQueryParameter
        {
            private readonly IEnumerable<int> numbers;
            public IntCustomParam(IEnumerable<int> numbers)
            {
                this.numbers = numbers;
            }

            public void AddParameter(IDbCommand command, string name)
            {
                command.CommandType = CommandType.StoredProcedure;

                var number_list = CreateSqlDataRecordList(command, numbers);

                // Add the table parameter.
                AddStructured(command, number_list);
            }
        }

        private static IDbDataParameter AddStructured(IDbCommand command, object value)
        {
#pragma warning disable CS0618 // Type or member is obsolete
            if (command is System.Data.SqlClient.SqlCommand sdcmd)
            {
                var p = sdcmd.Parameters.Add("integers", SqlDbType.Structured);
                p.Direction = ParameterDirection.Input;
                p.TypeName = "int_list_type";
                p.Value = value;
                return p;
            }
#pragma warning restore CS0618 // Type or member is obsolete
            else if (command is Microsoft.Data.SqlClient.SqlCommand mdcmd)
            {
                var p = mdcmd.Parameters.Add("integers", SqlDbType.Structured);
                p.Direction = ParameterDirection.Input;
                p.TypeName = "int_list_type";
                p.Value = value;
                return p;
            }
            else
                throw new ArgumentException(nameof(command));
        }

        /* TODO:
         * 
        public void TestMagicParam()
        {
            // magic params allow you to pass in single params without using an anon class
            // this test fails for now, but I would like to support a single param by parsing the sql with regex and remapping. 

            var first = connection.Query("select @a as a", 1).First();
            Assert.Equal(first.a, 1);
        }
         * */

        [Fact]
        public void TestDoubleParam()
        {
            Assert.Equal(0.1d, connection.Query<double>("select @d", new { d = 0.1d }).First());
        }

        [Fact]
        public void TestBoolParam()
        {
            Assert.False(connection.Query<bool>("select @b", new { b = false }).First());
        }

        // http://code.google.com/p/dapper-dot-net/issues/detail?id=70
        // https://connect.microsoft.com/VisualStudio/feedback/details/381934/sqlparameter-dbtype-dbtype-time-sets-the-parameter-to-sqldbtype-datetime-instead-of-sqldbtype-time

        [Fact]
        public void TestTimeSpanParam()
        {
            Assert.Equal(connection.Query<TimeSpan>("select @ts", new { ts = TimeSpan.FromMinutes(42) }).First(), TimeSpan.FromMinutes(42));
        }

        [Fact]
        public void PassInIntArray()
        {
            Assert.Equal(
                new[] { 1, 2, 3 },
                connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 }.AsEnumerable() })
            );
        }

        [Fact]
        public void PassInEmptyIntArray()
        {
            Assert.Equal(
                Array.Empty<int>(),
                connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = Array.Empty<int>() })
            );
        }

        [Fact]
        public void TestExecuteCommandWithHybridParameters()
        {
            var p = new DynamicParameters(new { a = 1, b = 2 });
            p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.Output);
            connection.Execute("set @c = @a + @b", p);
            Assert.Equal(3, p.Get<int>("@c"));
        }

        [Fact]
        public void GuidIn_SO_24177902()
        {
            // invent and populate
            Guid a = Guid.NewGuid(), b = Guid.NewGuid(), c = Guid.NewGuid(), d = Guid.NewGuid();
            connection.Execute("create table #foo (i int, g uniqueidentifier)");
            connection.Execute("insert #foo(i,g) values(@i,@g)",
                new[] { new { i = 1, g = a }, new { i = 2, g = b },
                new { i = 3, g = c },new { i = 4, g = d }});

            // check that rows 2&3 yield guids b&c
            var guids = connection.Query<Guid>("select g from #foo where i in (2,3)").ToArray();
            Assert.Equal(2, guids.Length);
            Assert.DoesNotContain(a, guids);
            Assert.Contains(b, guids);
            Assert.Contains(c, guids);
            Assert.DoesNotContain(d, guids);

            // in query on the guids
            var rows = connection.Query("select * from #foo where g in @guids order by i", new { guids })
                .Select(row => new { i = (int)row.i, g = (Guid)row.g }).ToArray();
            Assert.Equal(2, rows.Length);
            Assert.Equal(2, rows[0].i);
            Assert.Equal(b, rows[0].g);
            Assert.Equal(3, rows[1].i);
            Assert.Equal(c, rows[1].g);
        }

        [FactUnlessCaseSensitiveDatabase]
        public void TestParameterInclusionNotSensitiveToCurrentCulture()
        {
            // note this might fail if your database server is case-sensitive
            CultureInfo current = ActiveCulture;
            try
            {
                ActiveCulture = new CultureInfo("tr-TR");

                connection.Query<int>("select @pid", new { PId = 1 }).Single();
            }
            finally
            {
                ActiveCulture = current;
            }
        }

        [Fact]
        public void TestMassiveStrings()
        {
            var str = new string('X', 20000);
            Assert.Equal(connection.Query<string>("select @a", new { a = str }).First(), str);
        }


        [Fact]
        public void TestTVPWithAnonymousObject()
        {
            try
            {
                connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
                connection.Execute("CREATE PROC get_ints @integers int_list_type READONLY AS select * from @integers");

                var nums = connection.Query<int>("get_ints", new { integers = new IntCustomParam(new int[] { 1, 2, 3 }) }, commandType: CommandType.StoredProcedure).ToList();
                Assert.Equal(1, nums[0]);
                Assert.Equal(2, nums[1]);
                Assert.Equal(3, nums[2]);
                Assert.Equal(3, nums.Count);
            }
            finally
            {
                try
                {
                    connection.Execute("DROP PROC get_ints");
                }
                finally
                {
                    connection.Execute("DROP TYPE int_list_type");
                }
            }
        }

        [Fact]
        public void TestTVPWithAnonymousEmptyObject()
        {
            try
            {
                connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
                connection.Execute("CREATE PROC get_ints @integers int_list_type READONLY AS select * from @integers");

                var nums = connection.Query<int>("get_ints", new { integers = new IntCustomParam(new int[] { }) }, commandType: CommandType.StoredProcedure).ToList();
                Assert.Equal(1, nums[0]);
                Assert.Equal(2, nums[1]);
                Assert.Equal(3, nums[2]);
                Assert.Equal(3, nums.Count);
            }
            catch (ArgumentException ex)
            {
                Assert.True(string.Compare(ex.Message, "There are no records in the SqlDataRecord enumeration. To send a table-valued parameter with no rows, use a null reference for the value instead.") == 0);
            }
            finally
            {
                try
                {
                    connection.Execute("DROP PROC get_ints");
                }
                finally
                {
                    connection.Execute("DROP TYPE int_list_type");
                }
            }
        }

        // SQL Server specific test to demonstrate TVP 
        [Fact]
        public void TestTVP()
        {
            try
            {
                connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
                connection.Execute("CREATE PROC get_ints @integers int_list_type READONLY AS select * from @integers");

                var nums = connection.Query<int>("get_ints", new IntDynamicParam(new int[] { 1, 2, 3 })).ToList();
                Assert.Equal(1, nums[0]);
                Assert.Equal(2, nums[1]);
                Assert.Equal(3, nums[2]);
                Assert.Equal(3, nums.Count);
            }
            finally
            {
                try
                {
                    try { connection.Execute("DROP PROC get_ints"); } catch { }
                }
                finally
                {
                    try { connection.Execute("DROP TYPE int_list_type"); } catch { }
                }
            }
        }

        private class DynamicParameterWithIntTVP : DynamicParameters, SqlMapper.IDynamicParameters
        {
            private readonly IEnumerable<int> numbers;
            public DynamicParameterWithIntTVP(IEnumerable<int> numbers)
            {
                this.numbers = numbers;
            }

            public new void AddParameters(IDbCommand command, SqlMapper.Identity identity)
            {
                base.AddParameters(command, identity);

                command.CommandType = CommandType.StoredProcedure;

                var number_list = CreateSqlDataRecordList(command, numbers);

                // Add the table parameter.
                AddStructured(command, number_list);
            }
        }

        [Fact]
        public void TestTVPWithAdditionalParams()
        {
            try
            {
                connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
                connection.Execute("CREATE PROC get_values @integers int_list_type READONLY, @stringParam varchar(20), @dateParam datetime AS select i.*, @stringParam as stringParam, @dateParam as dateParam from @integers i");

                var dynamicParameters = new DynamicParameterWithIntTVP(new int[] { 1, 2, 3 });
                dynamicParameters.AddDynamicParams(new { stringParam = "stringParam", dateParam = new DateTime(2012, 1, 1) });

                var results = connection.Query("get_values", dynamicParameters, commandType: CommandType.StoredProcedure).ToList();
                Assert.Equal(3, results.Count);
                for (int i = 0; i < results.Count; i++)
                {
                    var result = results[i];
                    Assert.Equal(i + 1, result.n);
                    Assert.Equal("stringParam", result.stringParam);
                    Assert.Equal(new DateTime(2012, 1, 1), result.dateParam);
                }
            }
            finally
            {
                try
                {
                    connection.Execute("DROP PROC get_values");
                }
                finally
                {
                    connection.Execute("DROP TYPE int_list_type");
                }
            }
        }

        [Fact]
        public void TestSqlDataRecordListParametersWithAsTableValuedParameter()
        {
            try
            {
                connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
                connection.Execute("CREATE PROC get_ints @integers int_list_type READONLY AS select * from @integers");

                var records = CreateSqlDataRecordList(connection, new int[] { 1, 2, 3 });

                var nums = connection.Query<int>("get_ints", new { integers = records.AsTableValuedParameter() }, commandType: CommandType.StoredProcedure).ToList();
                Assert.Equal(new int[] { 1, 2, 3 }, nums);

                nums = connection.Query<int>("select * from @integers", new { integers = records.AsTableValuedParameter("int_list_type") }).ToList();
                Assert.Equal(new int[] { 1, 2, 3 }, nums);

                try
                {
                    connection.Query<int>("select * from @integers", new { integers = records.AsTableValuedParameter() }).First();
                    throw new InvalidOperationException();
                }
                catch (Exception ex)
                {
                    ex.Message.Equals("The table type parameter 'ids' must have a valid type name.");
                }
            }
            finally
            {
                try
                {
                    connection.Execute("DROP PROC get_ints");
                }
                finally
                {
                    connection.Execute("DROP TYPE int_list_type");
                }
            }
        }

        [Fact]
        public void TestEmptySqlDataRecordListParametersWithAsTableValuedParameter()
        {
            try
            {
                connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
                connection.Execute("CREATE PROC get_ints @integers int_list_type READONLY AS select * from @integers");


                var emptyRecord = CreateSqlDataRecordList(connection, Enumerable.Empty<int>());

                var nums = connection.Query<int>("get_ints", new { integers = emptyRecord.AsTableValuedParameter() }, commandType: CommandType.StoredProcedure).ToList();
                Assert.True(nums.Count == 0);
            }
            finally
            {
                try
                {
                    connection.Execute("DROP PROC get_ints");
                }
                finally
                {
                    connection.Execute("DROP TYPE int_list_type");
                }
            }
        }

        [Fact]
        public void TestSqlDataRecordListParametersWithTypeHandlers()
        {
            try
            {
                connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
                connection.Execute("CREATE PROC get_ints @integers int_list_type READONLY AS select * from @integers");

                // Variable type has to be IEnumerable<SqlDataRecord> for TypeHandler to kick in.
                object args;
#pragma warning disable CS0618 // Type or member is obsolete
                if (connection is System.Data.SqlClient.SqlConnection)
                {
                    IEnumerable<Microsoft.SqlServer.Server.SqlDataRecord> records = CreateSqlDataRecordList_SD(new int[] { 1, 2, 3 });
                    args = new { integers = records };
                }
#pragma warning restore CS0618 // Type or member is obsolete
                else if (connection is Microsoft.Data.SqlClient.SqlConnection)
                {
                    IEnumerable<Microsoft.Data.SqlClient.Server.SqlDataRecord> records = CreateSqlDataRecordList_MD(new int[] { 1, 2, 3 });
                    args = new { integers = records };
                }
                else
                {
                    throw new ArgumentException(nameof(connection));
                }

                var nums = connection.Query<int>("get_ints", args, commandType: CommandType.StoredProcedure).ToList();
                Assert.Equal(new int[] { 1, 2, 3 }, nums);

                try
                {
                    connection.Query<int>("select * from @integers", args).First();
                    throw new InvalidOperationException();
                }
                catch (Exception ex)
                {
                    ex.Message.Equals("The table type parameter 'ids' must have a valid type name.");
                }
            }
            finally
            {
                try
                {
                    connection.Execute("DROP PROC get_ints");
                }
                finally
                {
                    connection.Execute("DROP TYPE int_list_type");
                }
            }
        }

        [Fact]
        public void DataTableParameters()
        {
            try { connection.Execute("drop proc #DataTableParameters"); }
            catch { /* don't care */ }
            try { connection.Execute("drop table #DataTableParameters"); }
            catch { /* don't care */ }
            try { connection.Execute("drop type MyTVPType"); }
            catch { /* don't care */ }
            connection.Execute("create type MyTVPType as table (id int)");
            connection.Execute("create proc #DataTableParameters @ids MyTVPType readonly as select count(1) from @ids");

            var table = new DataTable { Columns = { { "id", typeof(int) } }, Rows = { { 1 }, { 2 }, { 3 } } };

            int count = connection.Query<int>("#DataTableParameters", new { ids = table.AsTableValuedParameter() }, commandType: CommandType.StoredProcedure).First();
            Assert.Equal(3, count);

            count = connection.Query<int>("select count(1) from @ids", new { ids = table.AsTableValuedParameter("MyTVPType") }).First();
            Assert.Equal(3, count);

            try
            {
                connection.Query<int>("select count(1) from @ids", new { ids = table.AsTableValuedParameter() }).First();
                throw new InvalidOperationException();
            }
            catch (Exception ex)
            {
                ex.Message.Equals("The table type parameter 'ids' must have a valid type name.");
            }
        }

        [Fact]
        public void SO29533765_DataTableParametersViaDynamicParameters()
        {
            try { connection.Execute("drop proc #DataTableParameters"); } catch { /* don't care */ }
            try { connection.Execute("drop table #DataTableParameters"); } catch { /* don't care */ }
            try { connection.Execute("drop type MyTVPType"); } catch { /* don't care */ }
            connection.Execute("create type MyTVPType as table (id int)");
            connection.Execute("create proc #DataTableParameters @ids MyTVPType readonly as select count(1) from @ids");

            var table = new DataTable { TableName = "MyTVPType", Columns = { { "id", typeof(int) } }, Rows = { { 1 }, { 2 }, { 3 } } };
            table.SetTypeName(table.TableName); // per SO29533765
            IDictionary<string, object> args = new Dictionary<string, object>(1)
            {
                ["ids"] = table
            };
            int count = connection.Query<int>("#DataTableParameters", args, commandType: CommandType.StoredProcedure).First();
            Assert.Equal(3, count);

            count = connection.Query<int>("select count(1) from @ids", args).First();
            Assert.Equal(3, count);
        }

        [Fact]
        public void SO26468710_InWithTVPs()
        {
            // this is just to make it re-runnable; normally you only do this once
            try { connection.Execute("drop type MyIdList"); }
            catch { /* don't care */ }
            connection.Execute("create type MyIdList as table(id int);");

            var ids = new DataTable
            {
                Columns = { { "id", typeof(int) } },
                Rows = { { 1 }, { 3 }, { 5 } }
            };
            ids.SetTypeName("MyIdList");
            int sum = connection.Query<int>(@"
            declare @tmp table(id int not null);
            insert @tmp (id) values(1), (2), (3), (4), (5), (6), (7);
            select * from @tmp t inner join @ids i on i.id = t.id", new { ids }).Sum();
            Assert.Equal(9, sum);
        }

        [Fact]
        public void DataTableParametersWithExtendedProperty()
        {
            try { connection.Execute("drop proc #DataTableParameters"); }
            catch { /* don't care */ }
            try { connection.Execute("drop table #DataTableParameters"); }
            catch { /* don't care */ }
            try { connection.Execute("drop type MyTVPType"); }
            catch { /* don't care */ }
            connection.Execute("create type MyTVPType as table (id int)");
            connection.Execute("create proc #DataTableParameters @ids MyTVPType readonly as select count(1) from @ids");

            var table = new DataTable { Columns = { { "id", typeof(int) } }, Rows = { { 1 }, { 2 }, { 3 } } };
            table.SetTypeName("MyTVPType"); // <== extended metadata
            int count = connection.Query<int>("#DataTableParameters", new { ids = table }, commandType: CommandType.StoredProcedure).First();
            Assert.Equal(3, count);

            count = connection.Query<int>("select count(1) from @ids", new { ids = table }).First();
            Assert.Equal(3, count);

            try
            {
                connection.Query<int>("select count(1) from @ids", new { ids = table }).First();
                throw new InvalidOperationException();
            }
            catch (Exception ex)
            {
                ex.Message.Equals("The table type parameter 'ids' must have a valid type name.");
            }
        }

        [Fact]
        public void SupportInit()
        {
            var obj = connection.Query<WithInit>("select 'abc' as Value").Single();
            Assert.Equal("abc", obj.Value);
            Assert.Equal(31, obj.Flags);
        }

        public class WithInit : ISupportInitialize
        {
            public string? Value { get; set; }
            public int Flags { get; set; }

            void ISupportInitialize.BeginInit() => Flags++;

            void ISupportInitialize.EndInit() => Flags += 30;
        }

        [Fact]
        public void SO29596645_TvpProperty()
        {
            try { connection.Execute("CREATE TYPE SO29596645_ReminderRuleType AS TABLE (id int NOT NULL)"); }
            catch { /* don't care */ }
            connection.Execute(@"create proc #SO29596645_Proc (@Id int, @Rules SO29596645_ReminderRuleType READONLY)
                                as begin select @Id + ISNULL((select sum(id) from @Rules), 0); end");
            var obj = new SO29596645_OrganisationDTO();
            int val = connection.Query<int>("#SO29596645_Proc", obj.Rules, commandType: CommandType.StoredProcedure).Single();

            // 4 + 9 + 7 = 20
            Assert.Equal(20, val);
        }

        private class SO29596645_RuleTableValuedParameters : SqlMapper.IDynamicParameters
        {
            private readonly string parameterName;

            public SO29596645_RuleTableValuedParameters(string parameterName)
            {
                this.parameterName = parameterName;
            }

            public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
            {
                Debug.WriteLine("> AddParameters");
                var p = command.CreateParameter();
                p.ParameterName = "Id";
                p.Value = 7;
                command.Parameters.Add(p);
                var table = new DataTable
                {
                    Columns = { { "Id", typeof(int) } },
                    Rows = { { 4 }, { 9 } }
                };
                p = command.CreateParameter();
                p.ParameterName = "Rules";
                p.Value = table;
                command.Parameters.Add(p);
                Debug.WriteLine("< AddParameters");
            }
        }

        private class SO29596645_OrganisationDTO
        {
            public SO29596645_RuleTableValuedParameters Rules { get; }

            public SO29596645_OrganisationDTO()
            {
                Rules = new SO29596645_RuleTableValuedParameters("@Rules");
            }
        }

#if ENTITY_FRAMEWORK
        private class HazGeo
        {
            public int Id { get; set; }
            public DbGeography? Geo { get; set; }
            public DbGeometry? Geometry { get; set; }
        }

        private class HazSqlGeo
        {
            public int Id { get; set; }
            public SqlGeography? Geo { get; set; }
            public SqlGeometry? Geometry { get; set; }
        }

        [Fact]
        public void DBGeography_SO24405645_SO24402424()
        {
            SkipIfMsDataClient();

            EntityFramework.Handlers.Register();

            connection.Execute("create table #Geo (id int, geo geography, geometry geometry)");

            var obj = new HazGeo
            {
                Id = 1,
                Geo = DbGeography.LineFromText("LINESTRING(-122.360 47.656, -122.343 47.656 )", 4326),
                Geometry = DbGeometry.LineFromText("LINESTRING (100 100, 20 180, 180 180)", 0)
            };
            connection.Execute("insert #Geo(id, geo, geometry) values (@Id, @Geo, @Geometry)", obj);
            var row = connection.Query<HazGeo>("select * from #Geo where id=1").SingleOrDefault();
            Assert.NotNull(row);
            Assert.Equal(1, row.Id);
            Assert.NotNull(row.Geo);
            Assert.NotNull(row.Geometry);
        }

        [Fact]
        public void SqlGeography_SO25538154()
        {
            SkipIfMsDataClient();

            SqlMapper.ResetTypeHandlers();
            connection.Execute("create table #SqlGeo (id int, geo geography, geometry geometry)");

            var obj = new HazSqlGeo
            {
                Id = 1,
                Geo = SqlGeography.STLineFromText(new SqlChars(new SqlString("LINESTRING(-122.360 47.656, -122.343 47.656 )")), 4326),
                Geometry = SqlGeometry.STLineFromText(new SqlChars(new SqlString("LINESTRING (100 100, 20 180, 180 180)")), 0)
            };
            connection.Execute("insert #SqlGeo(id, geo, geometry) values (@Id, @Geo, @Geometry)", obj);
            var row = connection.Query<HazSqlGeo>("select * from #SqlGeo where id=1").SingleOrDefault();
            Assert.NotNull(row);
            Assert.Equal(1, row.Id);
            Assert.NotNull(row.Geo);
            Assert.NotNull(row.Geometry);
        }

        [Fact]
        public void NullableSqlGeometry()
        {
            SqlMapper.ResetTypeHandlers();
            connection.Execute("create table #SqlNullableGeo (id int, geometry geometry null)");

            var obj = new HazSqlGeo
            {
                Id = 1,
                Geometry = null
            };
            connection.Execute("insert #SqlNullableGeo(id, geometry) values (@Id, @Geometry)", obj);
            var row = connection.Query<HazSqlGeo>("select * from #SqlNullableGeo where id=1").SingleOrDefault();
            Assert.NotNull(row);
            Assert.Equal(1, row.Id);
            Assert.Null(row.Geometry);
        }

        [Fact]
        public void SqlHierarchyId_SO18888911()
        {
            SkipIfMsDataClient();

            SqlMapper.ResetTypeHandlers();
            var row = connection.Query<HazSqlHierarchy>("select 3 as [Id], hierarchyid::Parse('/1/2/3/') as [Path]").Single();
            Assert.Equal(3, row.Id);
            Assert.NotEqual(default(SqlHierarchyId), row.Path);

            var val = connection.Query<SqlHierarchyId>("select @Path", row).Single();
            Assert.NotEqual(default(SqlHierarchyId), val);
        }

        public class HazSqlHierarchy
        {
            public int Id { get; set; }
            public SqlHierarchyId Path { get; set; }
        }

#endif

        [Fact]
        public void TestDynamicParameters()
        {
            var args = new DbDynamicParams {
                Provider.CreateRawParameter("foo", 123),
                Provider.CreateRawParameter("bar", "abc")
            };
            var result = connection.Query("select Foo=@foo, Bar=@bar", args).Single();
            int foo = result.Foo;
            string bar = result.Bar;
            Assert.Equal(123, foo);
            Assert.Equal("abc", bar);
        }
        
        [Fact]
        public void TestDynamicParametersReuse()
        {
            var args = new DbDynamicParams {
                Provider.CreateRawParameter("foo", 123),
                Provider.CreateRawParameter("bar", "abc")
            };
            var result1 = connection.Query("select Foo=@foo, Bar=@bar", args).Single();
            var result2 = connection.Query("select Foo=@foo, Bar=@bar", args).Single();
            Assert.Equal(123, result1.Foo);
            Assert.Equal("abc", result1.Bar);
            Assert.Equal(123, result2.Foo);
            Assert.Equal("abc", result2.Bar);
        }

        
        [Fact]
        public void TestCustomParameter()
        {
            var args = new {
                foo = new DbCustomParam(Provider.CreateRawParameter("foo", 123)),
                bar = "abc"
            };
            var result = connection.Query("select Foo=@foo, Bar=@bar", args).Single();
            int foo = result.Foo;
            string bar = result.Bar;
            Assert.Equal(123, foo);
            Assert.Equal("abc", bar);
        }
        
        [Fact]
        public void TestCustomParameterReuse()
        {
            var args = new {
                foo = new DbCustomParam(Provider.CreateRawParameter("foo", 123)),
                bar = "abc"
            };
            var result1 = connection.Query("select Foo=@foo, Bar=@bar", args).Single();
            var result2 = connection.Query("select Foo=@foo, Bar=@bar", args).Single();
            Assert.Equal(123, result1.Foo);
            Assert.Equal("abc", result1.Bar);
            Assert.Equal(123, result2.Foo);
            Assert.Equal("abc", result2.Bar);
        }

        [Fact]
        public void TestCustomParameterValueType()
        {
            // Value type (struct) ICustomQueryParameter previously caused a segfault
            // because the IL emitted Callvirt on an unboxed struct (see #2189)
            var args = new {
                foo = new DbCustomParamStruct(Provider.CreateRawParameter("foo", 123)),
                bar = "abc"
            };
            var result = connection.Query("select Foo=@foo, Bar=@bar", args).Single();
            int foo = result.Foo;
            string bar = result.Bar;
            Assert.Equal(123, foo);
            Assert.Equal("abc", bar);
        }

        [Fact]
        public void TestDynamicParamNullSupport()
        {
            var p = new DynamicParameters();

            p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
            connection.Execute("select @b = null", p);

            Assert.Null(p.Get<int?>("@b"));
        }

        [Fact]
        public void AddParameters_Command_AppliesBagAndLiterals()
        {
            // the identity-free overload: external tooling (e.g. build-time code generators)
            // can apply a bag using the command's own text for query-specific handling
            using var connection = GetClosedConnection();
            using var command = connection.CreateCommand();
            command.CommandText = "select @a + {=b}";

            var args = new DynamicParameters();
            args.Add("a", 1);
            args.Add("b", 2);
            args.AddParameters(command);

            Assert.Equal("select @a + 2", command.CommandText); // literal replaced
            // (note the literal member is still attached as a parameter too - unused, but
            // that matches what the bag does under vanilla execution)
            var p = Assert.IsAssignableFrom<IDbDataParameter>(command.Parameters[0]);
            Assert.Equal("a", p.ParameterName);
            Assert.Equal(1, p.Value);
            Assert.Equal(2, command.Parameters.Count);
        }

        [Fact]
        public void AddParameters_Command_DispatchesViaInterface()
        {
            // a subclass that hides AddParameters and re-implements IDynamicParameters
            // (the DynamicParameterWithIntTVP pattern) must still get its version called
            using var connection = GetClosedConnection();
            using var command = connection.CreateCommand();
            command.CommandText = "select @a";

            var args = new HidingBag();
            args.Add("a", 1);
            args.AddParameters(command);

            Assert.True(args.CustomRan);
        }

        private class HidingBag : DynamicParameters, SqlMapper.IDynamicParameters
        {
            public bool CustomRan { get; private set; }
            public new void AddParameters(IDbCommand command, SqlMapper.Identity identity)
            {
                base.AddParameters(command, identity);
                CustomRan = true;
            }
        }

        [Fact]
        public void TestAppendingAnonClasses()
        {
            var p = new DynamicParameters();
            p.AddDynamicParams(new { A = 1, B = 2 });
            p.AddDynamicParams(new { C = 3, D = 4 });

            var result = connection.Query("select @A a,@B b,@C c,@D d", p).Single();

            Assert.Equal(1, (int)result.a);
            Assert.Equal(2, (int)result.b);
            Assert.Equal(3, (int)result.c);
            Assert.Equal(4, (int)result.d);
        }

        [Fact]
        public void TestAppendingADictionary()
        {
            var dictionary = new Dictionary<string, object>(2)
            {
                ["A"] = 1,
                ["B"] = "two"
            };

            var p = new DynamicParameters();
            p.AddDynamicParams(dictionary);

            var result = connection.Query("select @A a, @B b", p).Single();

            Assert.Equal(1, (int)result.a);
            Assert.Equal("two", (string)result.b);
        }

        [Fact]
        public void TestAppendingAnExpandoObject()
        {
            dynamic expando = new ExpandoObject();
            expando.A = 1;
            expando.B = "two";

            var p = new DynamicParameters();
            p.AddDynamicParams(expando);

            var result = connection.Query("select @A a, @B b", p).Single();

            Assert.Equal(1, (int)result.a);
            Assert.Equal("two", (string)result.b);
        }

        [Fact]
        public void TestAppendingAList()
        {
            var p = new DynamicParameters();
            var list = new int[] { 1, 2, 3 };
            p.AddDynamicParams(new { list });

            var result = connection.Query<int>("select * from (select 1 A union all select 2 union all select 3) X where A in @list", p).ToList();

            Assert.Equal(1, result[0]);
            Assert.Equal(2, result[1]);
            Assert.Equal(3, result[2]);
        }

        [Fact]
        public void TestAppendingAListAsDictionary()
        {
            var p = new DynamicParameters();
            var list = new int[] { 1, 2, 3 };
            var args = new Dictionary<string, object>(1) { ["ids"] = list };
            p.AddDynamicParams(args);

            var result = connection.Query<int>("select * from (select 1 A union all select 2 union all select 3) X where A in @ids", p).ToList();

            Assert.Equal(1, result[0]);
            Assert.Equal(2, result[1]);
            Assert.Equal(3, result[2]);
        }

        [Fact]
        public void TestAppendingAListByName()
        {
            DynamicParameters p = new DynamicParameters();
            var list = new int[] { 1, 2, 3 };
            p.Add("ids", list);

            var result = connection.Query<int>("select * from (select 1 A union all select 2 union all select 3) X where A in @ids", p).ToList();

            Assert.Equal(1, result[0]);
            Assert.Equal(2, result[1]);
            Assert.Equal(3, result[2]);
        }

        [Fact]
        public void ParameterizedInWithOptimizeHint()
        {
            const string sql = @"
select count(1)
from(
    select 1 as x
    union all select 2
    union all select 5) y
where y.x in @vals
option (optimize for (@vals unKnoWn))";
            int count = connection.Query<int>(sql, new { vals = new[] { 1, 2, 3, 4 } }).Single();
            Assert.Equal(2, count);

            count = connection.Query<int>(sql, new { vals = new[] { 1 } }).Single();
            Assert.Equal(1, count);

            count = connection.Query<int>(sql, new { vals = new int[0] }).Single();
            Assert.Equal(0, count);
        }

        [Fact]
        public void TestProcedureWithTimeParameter()
        {
            var p = new DynamicParameters();
            p.Add("a", TimeSpan.FromHours(10), dbType: DbType.Time);

            connection.Execute(@"CREATE PROCEDURE #TestProcWithTimeParameter
    @a TIME
    AS 
    BEGIN
    SELECT @a
    END");
            Assert.Equal(connection.Query<TimeSpan>("#TestProcWithTimeParameter", p, commandType: CommandType.StoredProcedure).First(), new TimeSpan(10, 0, 0));
        }

        [Fact]
        public void TestUniqueIdentifier()
        {
            var guid = Guid.NewGuid();
            var result = connection.Query<Guid>("declare @foo uniqueidentifier set @foo = @guid select @foo", new { guid }).Single();
            Assert.Equal(guid, result);
        }

        [Fact]
        public void TestNullableUniqueIdentifierNonNull()
        {
            Guid? guid = Guid.NewGuid();
            var result = connection.Query<Guid?>("declare @foo uniqueidentifier set @foo = @guid select @foo", new { guid }).Single();
            Assert.Equal(guid, result);
        }

        [Fact]
        public void TestNullableUniqueIdentifierNull()
        {
            Guid? guid = null;
            var result = connection.Query<Guid?>("declare @foo uniqueidentifier set @foo = @guid select @foo", new { guid }).Single();
            Assert.Equal(guid, result);
        }

        [Fact]
        public void TestSupportForDynamicParameters()
        {
            var p = new DynamicParameters();
            p.Add("name", "bob");
            p.Add("age", dbType: DbType.Int32, direction: ParameterDirection.Output);

            Assert.Equal("bob", connection.Query<string>("set @age = 11 select @name", p).First());
            Assert.Equal(11, p.Get<int>("age"));
        }

        [Fact]
        public void TestSupportForDynamicParametersOutputExpressions()
        {
            var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };

            var p = new DynamicParameters(bob);
            p.Output(bob, b => b.PersonId);
            p.Output(bob, b => b.Occupation);
            p.Output(bob, b => b.NumberOfLegs);
            p.Output(bob, b => b.Address!.Name);
            p.Output(bob, b => b.Address!.PersonId);

            connection.Execute(@"
SET @Occupation = 'grillmaster' 
SET @PersonId = @PersonId + 1 
SET @NumberOfLegs = @NumberOfLegs - 1
SET @AddressName = 'bobs burgers'
SET @AddressPersonId = @PersonId", p);

            Assert.Equal("grillmaster", bob.Occupation);
            Assert.Equal(2, bob.PersonId);
            Assert.Equal(1, bob.NumberOfLegs);
            Assert.Equal("bobs burgers", bob.Address.Name);
            Assert.Equal(2, bob.Address.PersonId);
        }

        [Fact]
        public void TestSupportForDynamicParametersOutputExpressions_Scalar()
        {
            using (var connection = GetOpenConnection())
            {
                var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };

                var p = new DynamicParameters(bob);
                p.Output(bob, b => b.PersonId);
                p.Output(bob, b => b.Occupation);
                p.Output(bob, b => b.NumberOfLegs);
                p.Output(bob, b => b.Address!.Name);
                p.Output(bob, b => b.Address!.PersonId);

                var result = (int)connection.ExecuteScalar(@"
SET @Occupation = 'grillmaster' 
SET @PersonId = @PersonId + 1 
SET @NumberOfLegs = @NumberOfLegs - 1
SET @AddressName = 'bobs burgers'
SET @AddressPersonId = @PersonId
select 42", p)!;

                Assert.Equal("grillmaster", bob.Occupation);
                Assert.Equal(2, bob.PersonId);
                Assert.Equal(1, bob.NumberOfLegs);
                Assert.Equal("bobs burgers", bob.Address.Name);
                Assert.Equal(2, bob.Address.PersonId);
                Assert.Equal(42, result);
            }
        }

        [Fact]
        public void TestSupportForDynamicParametersOutputExpressions_Query_Buffered()
        {
            using (var connection = GetOpenConnection())
            {
                var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };

                var p = new DynamicParameters(bob);
                p.Output(bob, b => b.PersonId);
                p.Output(bob, b => b.Occupation);
                p.Output(bob, b => b.NumberOfLegs);
                p.Output(bob, b => b.Address!.Name);
                p.Output(bob, b => b.Address!.PersonId);

                var result = connection.Query<int>(@"
SET @Occupation = 'grillmaster' 
SET @PersonId = @PersonId + 1 
SET @NumberOfLegs = @NumberOfLegs - 1
SET @AddressName = 'bobs burgers'
SET @AddressPersonId = @PersonId
select 42", p, buffered: true).Single();

                Assert.Equal("grillmaster", bob.Occupation);
                Assert.Equal(2, bob.PersonId);
                Assert.Equal(1, bob.NumberOfLegs);
                Assert.Equal("bobs burgers", bob.Address.Name);
                Assert.Equal(2, bob.Address.PersonId);
                Assert.Equal(42, result);
            }
        }

        [Fact]
        public void TestSupportForDynamicParametersOutputExpressions_Query_NonBuffered()
        {
            using (var connection = GetOpenConnection())
            {
                var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };

                var p = new DynamicParameters(bob);
                p.Output(bob, b => b.PersonId);
                p.Output(bob, b => b.Occupation);
                p.Output(bob, b => b.NumberOfLegs);
                p.Output(bob, b => b.Address!.Name);
                p.Output(bob, b => b.Address!.PersonId);

                var result = connection.Query<int>(@"
SET @Occupation = 'grillmaster' 
SET @PersonId = @PersonId + 1 
SET @NumberOfLegs = @NumberOfLegs - 1
SET @AddressName = 'bobs burgers'
SET @AddressPersonId = @PersonId
select 42", p, buffered: false).Single();

                Assert.Equal("grillmaster", bob.Occupation);
                Assert.Equal(2, bob.PersonId);
                Assert.Equal(1, bob.NumberOfLegs);
                Assert.Equal("bobs burgers", bob.Address.Name);
                Assert.Equal(2, bob.Address.PersonId);
                Assert.Equal(42, result);
            }
        }

        [Fact]
        public void TestSupportForDynamicParametersOutputExpressions_QueryMultiple()
        {
            using (var connection = GetOpenConnection())
            {
                var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } };

                var p = new DynamicParameters(bob);
                p.Output(bob, b => b.PersonId);
                p.Output(bob, b => b.Occupation);
                p.Output(bob, b => b.NumberOfLegs);
                p.Output(bob, b => b.Address!.Name);
                p.Output(bob, b => b.Address!.PersonId);

                int x, y;
                using (var multi = connection.QueryMultiple(@"
SET @Occupation = 'grillmaster' 
SET @PersonId = @PersonId + 1 
SET @NumberOfLegs = @NumberOfLegs - 1
SET @AddressName = 'bobs burgers'
select 42
select 17
SET @AddressPersonId = @PersonId", p))
                {
                    x = multi.Read<int>().Single();
                    y = multi.Read<int>().Single();
                }

                Assert.Equal("grillmaster", bob.Occupation);
                Assert.Equal(2, bob.PersonId);
                Assert.Equal(1, bob.NumberOfLegs);
                Assert.Equal("bobs burgers", bob.Address.Name);
                Assert.Equal(2, bob.Address.PersonId);
                Assert.Equal(42, x);
                Assert.Equal(17, y);
            }
        }

        [Fact]
        public void TestSupportForExpandoObjectParameters()
        {
            dynamic p = new ExpandoObject();
            p.name = "bob";
            object parameters = p;
            string result = connection.Query<string>("select @name", parameters).First();
            Assert.Equal("bob", result);
        }

        [Fact]
        public void SO25069578_DynamicParams_Procs()
        {
            var parameters = new DynamicParameters();
            parameters.Add("foo", "bar");
            // parameters = new DynamicParameters(parameters);
            try { connection.Execute("drop proc SO25069578"); }
            catch { /* don't care */ }
            connection.Execute("create proc SO25069578 @foo nvarchar(max) as select @foo as [X]");
            var tran = connection.BeginTransaction(); // gist used transaction; behaves the same either way, though
            var row = connection.Query<HazX>("SO25069578", parameters,
                commandType: CommandType.StoredProcedure, transaction: tran).Single();
            tran.Rollback();
            Assert.Equal("bar", row.X);
        }

        public class HazX
        {
            public string? X { get; set; }
        }

        [Fact]
        public void SO25297173_DynamicIn()
        {
            const string query = @"
declare @table table(value int not null);
insert @table values(1);
insert @table values(2);
insert @table values(3);
insert @table values(4);
insert @table values(5);
insert @table values(6);
insert @table values(7);
SELECT value FROM @table WHERE value IN @myIds";
            var queryParams = new Dictionary<string, object>(1)
            {
                ["myIds"] = new[] { 5, 6 }
            };

            var dynamicParams = new DynamicParameters(queryParams);
            List<int> result = connection.Query<int>(query, dynamicParams).ToList();
            Assert.Equal(2, result.Count);
            Assert.Contains(5, result);
            Assert.Contains(6, result);
        }

        [Fact]
        public void Test_AddDynamicParametersRepeatedShouldWork()
        {
            var args = new DynamicParameters();
            args.AddDynamicParams(new { Foo = 123 });
            args.AddDynamicParams(new { Foo = 123 });
            int i = connection.Query<int>("select @Foo", args).Single();
            Assert.Equal(123, i);
        }

        [Fact]
        public void Test_AddDynamicParametersRepeatedIfParamTypeIsDbStiringShouldWork()
        {
            var foo = new DbString() { Value = "123" };

            var args = new DynamicParameters();
            args.AddDynamicParams(new { Foo = foo });
            args.AddDynamicParams(new { Foo = foo });
            int i = connection.Query<int>("select @Foo", args).Single();
            Assert.Equal(123, i);
        }

        [Fact]
        public void AllowIDictionaryParameters()
        {
            var parameters = new Dictionary<string, object>(1)
            {
                ["param1"] = 0
            };

            connection.Query("SELECT @param1", parameters);
        }

        [Fact]
        public void TestParameterWithIndexer()
        {
            connection.Execute(@"create proc #TestProcWithIndexer 
	@A int
as 
begin
	select @A
end");
            var item = connection.Query<int>("#TestProcWithIndexer", new ParameterWithIndexer(), commandType: CommandType.StoredProcedure).Single();
        }

        public class ParameterWithIndexer
        {
            public int A { get; set; }
            public virtual string? this[string columnName]
            {
                get { return null; }
                set { }
            }
        }

        [Fact]
        public void TestMultipleParametersWithIndexer()
        {
            var order = connection.Query<MultipleParametersWithIndexer>("select 1 A,2 B").First();

            Assert.Equal(1, order.A);
            Assert.Equal(2, order.B);
        }

        public class MultipleParametersWithIndexer : MultipleParametersWithIndexerDeclaringType
        {
            public int A { get; set; }
        }

        public class MultipleParametersWithIndexerDeclaringType
        {
            public object? this[object field]
            {
                get { return null; }
                set { }
            }

            public object? this[object field, int index]
            {
                get { return null; }
                set { }
            }

            public int B { get; set; }
        }

        [Fact]
        public void Issue182_BindDynamicObjectParametersAndColumns()
        {
            connection.Execute("create table #Dyno ([Id] uniqueidentifier primary key, [Name] nvarchar(50) not null, [Foo] bigint not null);");

            var guid = Guid.NewGuid();
            var orig = new Dyno { Name = "T Rex", Id = guid, Foo = 123L };
            var result = connection.Execute("insert into #Dyno ([Id], [Name], [Foo]) values (@Id, @Name, @Foo);", orig);

            var fromDb = connection.Query<Dyno>("select * from #Dyno where Id=@Id", orig).Single();
            Assert.Equal((Guid)fromDb.Id, guid);
            Assert.Equal("T Rex", fromDb.Name);
            Assert.NotNull(fromDb.Foo);
            Assert.Equal(123L, (long)fromDb.Foo);
        }

        public class Dyno
        {
            public dynamic? Id { get; set; }
            public string? Name { get; set; }

            public object? Foo { get; set; }
        }

        [Fact]
        public void Issue151_ExpandoObjectArgsQuery()
        {
            dynamic args = new ExpandoObject();
            args.Id = 123;
            args.Name = "abc";

            var row = connection.Query("select @Id as [Id], @Name as [Name]", (object)args).Single();
            Assert.Equal(123, (int)row.Id);
            Assert.Equal("abc", (string)row.Name);
        }

        [Fact]
        public void Issue151_ExpandoObjectArgsExec()
        {
            dynamic args = new ExpandoObject();
            args.Id = 123;
            args.Name = "abc";
            connection.Execute("create table #issue151 (Id int not null, Name nvarchar(20) not null)");
            Assert.Equal(1, connection.Execute("insert #issue151 values(@Id, @Name)", (object)args));
            var row = connection.Query("select Id, Name from #issue151").Single();
            Assert.Equal(123, (int)row.Id);
            Assert.Equal("abc", (string)row.Name);
        }

        [Fact]
        public void Issue192_InParameterWorksWithSimilarNames()
        {
            var rows = connection.Query(@"
declare @Issue192 table (
    Field INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Field_1 INT NOT NULL);
insert @Issue192(Field_1) values (1), (2), (3);
SELECT * FROM @Issue192 WHERE Field IN @Field AND Field_1 IN @Field_1",
    new { Field = new[] { 1, 2 }, Field_1 = new[] { 2, 3 } }).Single();
            Assert.Equal(2, (int)rows.Field);
            Assert.Equal(2, (int)rows.Field_1);
        }

        [Fact]
        public void Issue192_InParameterWorksWithSimilarNamesWithUnicode()
        {
            var rows = connection.Query(@"
declare @Issue192 table (
    Field INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    Field_1 INT NOT NULL);
insert @Issue192(Field_1) values (1), (2), (3);
SELECT * FROM @Issue192 WHERE Field IN @µ AND Field_1 IN @µµ",
    new { µ = new[] { 1, 2 }, µµ = new[] { 2, 3 } }).Single();
            Assert.Equal(2, (int)rows.Field);
            Assert.Equal(2, (int)rows.Field_1);
        }

        [FactUnlessCaseSensitiveDatabase]
        public void Issue220_InParameterCanBeSpecifiedInAnyCase()
        {
            // note this might fail if your database server is case-sensitive
            Assert.Equal(
                new[] { 1 },
                connection.Query<int>("select * from (select 1 as Id) as X where Id in @ids", new { Ids = new[] { 1 } })
            );
        }

        [Fact]
        public void SO30156367_DynamicParamsWithoutExec()
        {
            var dbParams = new DynamicParameters();
            dbParams.Add("Field1", 1);
            var value = dbParams.Get<int>("Field1");
            Assert.Equal(1, value);
        }

        [Fact]
        public void RunAllStringSplitTestsDisabled()
        {
            RunAllStringSplitTests(-1, 1500);
        }

        [FactRequiredCompatibilityLevel(FactRequiredCompatibilityLevelAttribute.SqlServer2016)]
        public void RunAllStringSplitTestsEnabled()
        {
            RunAllStringSplitTests(10, 4500);
        }

        private void RunAllStringSplitTests(int stringSplit, int max = 150)
        {
            int oldVal = SqlMapper.Settings.InListStringSplitCount;
            try
            {
                SqlMapper.Settings.InListStringSplitCount = stringSplit;
                try { connection.Execute("drop table #splits"); } catch { /* don't care */ }
                int count = connection.QuerySingle<int>("create table #splits (i int not null);"
                    + string.Concat(Enumerable.Range(-max, max * 3).Select(i => $"insert #splits (i) values ({i});"))
                    + "select count(1) from #splits");
                Assert.Equal(count, 3 * max);

                for (int i = 0; i < max; Incr(ref i))
                {
                    try
                    {
                        var vals = Enumerable.Range(1, i);
                        var list = connection.Query<int>("select i from #splits where i in @vals", new { vals }).AsList();
                        Assert.Equal(list.Count, i);
                        Assert.Equal(list.Sum(), vals.Sum());
                    }
                    catch (Exception ex)
                    {
                        throw new InvalidOperationException($"Error when i={i}: {ex.Message}", ex);
                    }
                }
            }
            finally
            {
                SqlMapper.Settings.InListStringSplitCount = oldVal;
            }
        }

        private static void Incr(ref int i)
        {
            if (i <= 15) i++;
            else if (i <= 80) i += 5;
            else if (i <= 200) i += 10;
            else if (i <= 1000) i += 50;
            else i += 100;
        }

        [Fact]
        public void Issue601_InternationalParameterNamesWork()
        {
            // regular parameter
            var result = connection.QuerySingle<int>("select @æøå٦", new { æøå٦ = 42 });
            Assert.Equal(42, result);
        }

        [FactLongRunning]
        public void TestListExpansionPadding_Enabled() => TestListExpansionPadding(true);

        [FactLongRunning]
        public void TestListExpansionPadding_Disabled() => TestListExpansionPadding(false);

        [Theory]
        [InlineData(true)]
        [InlineData(false)]
        public void OleDbParamFilterFails(bool legacyParameterToken)
        {
            SqlMapper.PurgeQueryCache();
            var oldValue = SqlMapper.Settings.SupportLegacyParameterTokens;
            try
            {
                SqlMapper.Settings.SupportLegacyParameterTokens = legacyParameterToken;

                if (legacyParameterToken) // OLE DB parameter support enabled; can false-positive
                {
                    Assert.Throws<NotSupportedException>(() => GetValue(connection));
                }
                else // OLE DB parameter support disabled; more reliable
                {
                    Assert.Equal("this ? could be awkward", GetValue(connection));
                }
            }
            finally
            {
                SqlMapper.Settings.SupportLegacyParameterTokens = oldValue;
            }

            static string GetValue(DbConnection connection)
                => connection.QuerySingle<string>("select 'this ? could be awkward'",
                    new TypeWithDodgyProperties());
        }

        private void TestListExpansionPadding(bool enabled)
        {
            bool oldVal = SqlMapper.Settings.PadListExpansions;
            try
            {
                SqlMapper.Settings.PadListExpansions = enabled;
                Assert.Equal(4096, connection.ExecuteScalar<int>(@"
create table #ListExpansion(id int not null identity(1,1), value int null);
insert #ListExpansion (value) values (null);
declare @loop int = 0;
while (@loop < 12)
begin -- double it
	insert #ListExpansion (value) select value from #ListExpansion;
	set @loop = @loop + 1;
end

select count(1) as [Count] from #ListExpansion"));

                var list = new List<int>();
                int nextId = 1, batchCount;
                var rand = new Random(12345);
                const int SQL_SERVER_MAX_PARAMS = 2095;
                TestListForExpansion(list, enabled); // test while empty
                while (list.Count < SQL_SERVER_MAX_PARAMS)
                {
                    try
                    {
                        if (list.Count <= 20) batchCount = 1;
                        else if (list.Count <= 200) batchCount = rand.Next(1, 40);
                        else batchCount = rand.Next(1, 100);

                        for (int j = 0; j < batchCount && list.Count < SQL_SERVER_MAX_PARAMS; j++)
                            list.Add(nextId++);

                        TestListForExpansion(list, enabled);
                    }
                    catch (Exception ex)
                    {
                        throw new InvalidOperationException($"Failure with {list.Count} items: {ex.Message}", ex);
                    }
                }
            }
            finally
            {
                SqlMapper.Settings.PadListExpansions = oldVal;
            }
        }

        private void TestListForExpansion(List<int> list, bool enabled)
        {
            var row = connection.QuerySingle(@"
declare @hits int, @misses int, @count int;
select @count = count(1) from #ListExpansion;
select @hits = count(1) from #ListExpansion where id in @ids ;
select @misses = count(1) from #ListExpansion where not id in @ids ;
declare @query nvarchar(max) = N' in @ids '; -- ok, I confess to being pleased with this hack ;p
select @hits as [Hits], (@count - @misses) as [Misses], @query as [Query];
", new { ids = list });
            int hits = row.Hits, misses = row.Misses;
            string query = row.Query;
            int argCount = Regex.Matches(query, "@ids[0-9]").Count;
            int expectedCount = GetExpectedListExpansionCount(list.Count, enabled);
            Assert.Equal(hits, list.Count);
            Assert.Equal(misses, list.Count);
            Assert.Equal(argCount, expectedCount);
        }

        private static int GetExpectedListExpansionCount(int count, bool enabled)
        {
            if (!enabled) return count;

            if (count <= 5 || count > 2070) return count;

            int padFactor;
            if (count <= 150) padFactor = 10;
            else if (count <= 750) padFactor = 50;
            else if (count <= 2000) padFactor = 100;
            else if (count <= 2070) padFactor = 10;
            else padFactor = 200;

            int blocks = count / padFactor, delta = count % padFactor;
            if (delta != 0) blocks++;
            return blocks * padFactor;
        }

        [Fact]
        public void Issue1907_SqlDecimalPreciseValues()
        {
            bool close = false;
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                    close = true;
                }
                connection.Execute(@"
create table #Issue1907 (
    Id int not null primary key identity(1,1),
    Value numeric(30,15) not null);");

                const string PreciseValue = "999999999999999.999999999999999";
                SqlDecimal sentValue = SqlDecimal.Parse(PreciseValue), recvValue;
                connection.Execute("insert #Issue1907 (Value) values (@value)", new { value = sentValue });

                // access via vendor-specific API; if this fails, nothing else can work
                using (var wrappedReader = connection.ExecuteReader("select Id, Value from #Issue1907"))
                {
                    var reader = Assert.IsAssignableFrom<IWrappedDataReader>(wrappedReader).Reader;
                    Assert.True(reader.Read());
                    if (reader is Microsoft.Data.SqlClient.SqlDataReader msReader)
                    {
                        recvValue = msReader.GetSqlDecimal(1);
                    }
#pragma warning disable CS0618 // Type or member is obsolete
                    else if (reader is System.Data.SqlClient.SqlDataReader sdReader)
                    {
                        recvValue = sdReader.GetSqlDecimal(1);
                    }
#pragma warning restore CS0618 // Type or member is obsolete
                    else
                    {
                        throw new InvalidOperationException($"unexpected reader type: {reader.GetType().FullName}");
                    }
                    Assert.Equal(sentValue, recvValue);
                    Assert.Equal(PreciseValue, recvValue.ToString());

                    Assert.False(reader.Read());
                    Assert.False(reader.NextResult());
                }

                // access via generic API
                using (var wrappedReader = connection.ExecuteReader("select Id, Value from #Issue1907"))
                {
                    var reader = Assert.IsAssignableFrom<DbDataReader>(Assert.IsAssignableFrom<IWrappedDataReader>(wrappedReader).Reader);
                    Assert.True(reader.Read());
                    recvValue = reader.GetFieldValue<SqlDecimal>(1);
                    Assert.Equal(sentValue, recvValue);
                    Assert.Equal(PreciseValue, recvValue.ToString());

                    Assert.False(reader.Read());
                    Assert.False(reader.NextResult());
                }

                // prove that we **cannot** fix ExecuteScalar, because ADO.NET itself doesn't work for that
                Assert.Throws<OverflowException>(() =>
                {
                    using var cmd = connection.CreateCommand();
                    cmd.CommandText = "select Value from #Issue1907";
                    cmd.CommandType = CommandType.Text;
                    _ = cmd.ExecuteScalar();
                });

                // prove that simple read: works
                recvValue = connection.QuerySingle<SqlDecimal>("select Value from #Issue1907");
                Assert.Equal(sentValue, recvValue);
                Assert.Equal(PreciseValue, recvValue.ToString());

                recvValue = connection.QuerySingle<SqlDecimal?>("select Value from #Issue1907")!.Value;
                Assert.Equal(sentValue, recvValue);
                Assert.Equal(PreciseValue, recvValue.ToString());

                // prove that object read: works
                recvValue = connection.QuerySingle<HazSqlDecimal>("select Id, Value from #Issue1907").Value;
                Assert.Equal(sentValue, recvValue);
                Assert.Equal(PreciseValue, recvValue.ToString());

                recvValue = connection.QuerySingle<HazNullableSqlDecimal>("select Id, Value from #Issue1907").Value!.Value;
                Assert.Equal(sentValue, recvValue);
                Assert.Equal(PreciseValue, recvValue.ToString());

                // prove that value-tuple read: works
                recvValue = connection.QuerySingle<(int Id, SqlDecimal Value)>("select Id, Value from #Issue1907").Value;
                Assert.Equal(sentValue, recvValue);
                Assert.Equal(PreciseValue, recvValue.ToString());

                recvValue = connection.QuerySingle<(int Id, SqlDecimal? Value)>("select Id, Value from #Issue1907").Value!.Value;
                Assert.Equal(sentValue, recvValue);
                Assert.Equal(PreciseValue, recvValue.ToString());
            }
            finally
            {
                if (close) connection.Close();
            }
            
        }
        class HazSqlDecimal
        {
            public int Id { get; set; }
            public SqlDecimal Value { get; set; }
        }

        class HazNullableSqlDecimal
        {
            public int Id { get; set; }
            public SqlDecimal? Value { get; set; }
        }

        class TypeWithDodgyProperties
        {
            public string Name => throw new NotSupportedException();
        }
    }
}