Inserting a string larger then 4000 characters using Sql CE 4.0

[GoogleCodeExporter]

What steps will reproduce the problem?

Try to insert a string into a sql ce 4.0 database that has a column type of 
ntext.

Example:

var entity = new { LargeString = "Large string over 4000 characters, actually 
it has to be over 4029 don't know why..." };

var sql = INSERT INTO TestTable(NTextColumn) VALUES (@LargeString);

connection.Execute(sql, entity);

Dapper determines the size of the string is greater then 4000 characters and 
sets the size of the command parameter to -1. Sql Compact CE 4.0 evidently does 
not support this as it throws an exception when you try to set a size parameter 
to -1. I set it to Int32.MaxValue instead which works for me although I'm not 
sure that's the best fix.

Original issue reported on code.google.com by casey.en...@gmail.com on 23 Aug 2012 at 8:29

[GoogleCodeExporter]

I ran into the same problem. My workaround was to use DynamicParameters to 
force the size:
var parameters = new DynamicParameters();
parameters.Add("Value", myLongValueHere, System.Data.DbType.String, 
System.Data.ParameterDirection.Input, Int32.MaxValue);
dbc.Execute(sql, parameters, trans);

How did you go about setting the size parameter in your code? Is there an 
easier way than what I did?

Is this something that can be addressed in Dapper?

Original comment by j...@cybertechnical.com on 17 Mar 2013 at 11:40

[GoogleCodeExporter]

I modified the dapper code and just hard coded it to a blob or something, don't 
remember the specifics, but I know I had a separate fork of dapper going.

Original comment by casey.en...@gmail.com on 17 Mar 2013 at 11:56

[GoogleCodeExporter]

Thanks for the explanation. I was considering doing the same until I noticed 
the overload when adding dynamic parameters. Did you test your solution with 
SQL server as well? I'm curious if this change can be made in the source 
without having a negative impact on other DB engines.

Original comment by j...@cybertechnical.com on 18 Mar 2013 at 2:17

[GoogleCodeExporter]

I did not check it with sql server, that probably would have not worked
correctly but we were only using sql compact and so I didn't care :)

Original comment by casey.en...@gmail.com on 18 Mar 2013 at 2:19

[GoogleCodeExporter]

I know this is an old issue, but I'm posting again because I realized my 
solution above did not work as I had thought. I believe the problem actually 
lies in the SqlCe provider itself. When I use the following command, the query 
executes, but the data is actually truncated internally:
parameters.Add("Value", myLongValueHere, System.Data.DbType.String, 
System.Data.ParameterDirection.Input, Int32.MaxValue);

So instead of throwing up an error, it is truncating the string to 4000 
characters and storing the string in the DB. This is definitely not what I 
intended.

The underlying problem is that when using a System.Data.IDbCommand with a 
System.Data.DbType.String parameter and setting the size to greater than 4000, 
the SqlCe provider does not assume it is an NText field - rather, it converts 
it to an NVarChar and truncates the string to 4000 characters. There is no 
NText equivalent in System.Data.DbType, so there is no easy solution here.

I am considering looking into creating a fork of Dapper to handle this. If I 
can implement a fix for this, would the Dapper admins consider merging this 
into the code base? Or would this be frowned upon because it is specific to 
SqlCe and Dapper needs to stay DB independent?

Original comment by j...@cybertechnical.com on 19 Jul 2013 at 5:47

[GoogleCodeExporter]

It occured to me to look and see if PetaPoco has a workaround for this, and it 
was actually easy to find:
https://github.com/cyotek/PetaPoco/commit/ea13add473be3899ebb73b463d2aff98f8d6d0
6e

I was able to use this as a workaround on a standard IDbCommand parameter:
var pValue = cmd.CreateParameter();
pValue.ParameterName = "Value";
pValue.DbType = System.Data.DbType.String;
pValue.Value = p.Value;
if (p.Value.Length + 1 > 4000 && pValue.GetType().Name == "SqlCeParameter")
     pValue.GetType().GetProperty("SqlDbType").SetValue(pValue, System.Data.SqlDbType.NText, null);
pValue.Size = Math.Max(p.Value.Length + 1, 4000);
cmd.Parameters.Add(pValue);

I'll take a look at Dapper when I get a chance to see if this is practical to 
implement...

Original comment by j...@cybertechnical.com on 19 Jul 2013 at 6:07