Attempting to Modify Dapper to Support SQL Server CE's ntext type

dapper sql-server-ce

Question

I have a need to support SQL Server CE's ntext type in Dapper. There is a thread here describing the issue: https://code.google.com/p/dapper-dot-net/issues/detail?can=2&start=0&num=100&q=&colspec=ID%20Type%20Status%20Priority%20Milestone%20Owner%20Summary&groupby=&sort=&id=110

I've attempted to modify Dapper to use a solution similar to the approach used in PetaPoco: https://github.com/cyotek/PetaPoco/commit/ea13add473be3899ebb73b463d2aff98f8d6d06e

After taking a look through Dapper's source, I decided a good way to attempt this might be to modify the DbString class. I changed the AddParameter(IDbCommand command, string name) method (starting at line 3120) to the following:

public void AddParameter(IDbCommand command, string name)
{
            if (IsFixedLength && Length == -1)
            {
                throw new InvalidOperationException("If specifying IsFixedLength,  a Length must also be specified");
            }
            var param = command.CreateParameter();
            param.ParameterName = name;
            param.Value = (object)Value ?? DBNull.Value;
            if (Length == -1 && Value != null && Value.Length <= 4000)
            {
                param.Size = 4000;
            }
            else
            {
                param.Size = Length;
            }
            param.DbType = IsAnsi ? (IsFixedLength ? DbType.AnsiStringFixedLength : DbType.AnsiString) : (IsFixedLength ? DbType.StringFixedLength : DbType.String);

            if (param.Value != null)
            {
                if (param.Value.ToString().Length + 1 > 4000 && param.GetType().Name == "SqlCeParameter")
                {
                    param.GetType().GetProperty("SqlDbType").SetValue(param, System.Data.SqlDbType.NText, null);  
                }
            }

            command.Parameters.Add(param);
}

My edit is the part towards the bottom that checks the type and attempts to change it to ntext. When I run this code, however, IIS Express crashes and asp.net does not give me any useful debug info. I tried to run it in a debugger and I am getting a heap corruption related error.

Am I on the wrong track here? Is there a better way to attempt something like this in Dapper? Or is this something that just is not going to work because of the way the cache and/or IL is generated? I was hoping to create a pull request for it if I could get it work, but I am striking out.


Update - I have found one potential solution. I added some code to the AddParameters(IDbCommand command, SqlMapper.Identity identity) method on the DynamicParameters class. The final lines of the method now read:

if (s != null)
{
    if (s.Length + 1 > 4000 && p.GetType().Name == "SqlCeParameter")    
    {
        p.GetType().GetProperty("SqlDbType").SetValue(p, SqlDbType.NText, null);
        p.Size = s.Length;
    }
}
if (add)
{
    command.Parameters.Add(p);
}
param.AttachedParam = p;

In order to use this solution, I have to add my parameters as DynamicParameters. So it works, but not in the most useful way. I am still searching for a better solution.

Is this the kind of thing that the Dapper devs would consider including as a patch if I create a pull request?

Popular Answer

Try this extension method to any property bag:

    public static class DynamicParametersExtensions
{
    public static DynamicParameters AsDynamic(this object propertyBag) {
        var parameters = new DynamicParameters();
        foreach (var property in propertyBag.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public)) {
            var propertyValue = property.GetValue(propertyBag);
            var propertyValueAsString = propertyValue as string;
            if (propertyValueAsString != null && propertyValueAsString.Length >= 4000) {
                parameters.Add(property.Name, new DbString {
                    Value = propertyValueAsString,
                    Length = propertyValueAsString.Length
                });
            } else {
                parameters.Add(property.Name, propertyValue);
            }
        }
        return parameters;
    }
}

Example use:

connection.Execute(@"UPDATE MyTable SET NTextColumn=@NTextColumn", new { value.NTextColumn }.AsDynamic()); 



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why