1

I am working with SQL Compact 4.0 for the first time and trying to insert data into an IMAGE column and most of the data is around 50kbytes.

The problem I am getting is although the image column is supposed to be big enough the data is being truncated to 8000 bytes.

I am using a SqlCeParameter to insert the data. The size has been set to the match the length of the byte[] (43402) and the SqlDbType is SqlDbType.Image.

In the database which you can see all the rows are 8000 bytes (using datalength function).

I suspect this is something in the database schema itself, perhaps a default size set for IMAGE columns which I can hopefully override. Interesting to note the parameter has the base DbType.Binary - which according to Microsoft is limited to 8000 bytes - could be red herring!

====

As I couldn't add any images, grrrr. Here is some more information:

Here is the code where I build the command. _params is just a Dictionary which contains each of the parameter names and data:

var cmd = connection.CreateCommand();
cmd.CommandText = _command;

foreach(var p in _params)
{
  var param = SqlHelper.CreateSqlCeParameter(p);
  cmd.Parameters.Add(param);
  if(param.SqlDbType == System.Data.SqlDbType.Image)
  {
     param.Size = (p.Value as byte[]).Length;
  }
}
cmd.ExecuteNonQuery();

Using this helper class to create the parameters:

public class SqlHelper
    {
        public static SqlDbType MapToSqlCeType(object data)
        {
            if(data.GetType() == typeof(string))
            {
                return SqlDbType.NVarChar; 
            }

            if(data.GetType() == typeof(byte[]))
            {
                return SqlDbType.Image;
            }

            return SqlDbType.NVarChar;
        }

        internal static SqlCeParameter CreateSqlCeParameter(KeyValuePair<string, object> data)
        {
            return new SqlCeParameter(data.Key, SqlHelper.MapToSqlCeType(data.Value))
            {
                Value = data.Value
            };
        }
    }

SQL I am using to check: SELECT top 10 datalength([Data]) FROM [Datamodule]; GO

Results: Column1 8000 8000 8000 8000 8000 8000 8000 8000 8000 8000

Andrew Larkin
  • 11
  • 4
  • Image columns can be up to 1 gb. Could you show how the parameters are built, maybe there is a bug there? It is important that you explicitly set the SqlDbType – ErikEJ Aug 07 '15 at 17:22
  • I have added the code above to demonstrate how the parameter is being created. I can confirm the parameter on the command all looks okay at the point the command is executed. – Andrew Larkin Aug 10 '15 at 08:18

1 Answers1

0

I figured this out and it was an issue with the way I was creating the SqlCeParameter.

If I just build the parameter with the name and value then SqlDbType is set to VarBinary (by default) and this inserts all the data into the IMAGE type column.

So using the following code works fine:

 var cmd = connection.CreateCommand();
                    cmd.CommandText = _command;

                    foreach (var p in _params)
                    {
                        cmd.Parameters.Add(new SqlCeParameter(p.Key, p.Value));
                    }

                    cmd.ExecuteNonQuery();

Rather than explicitly setting the SqlDbType to Image, VarBinary appears to do the job. I had previously tried upgrading the database via script and converting/casting the data as VARBINARY had truncated it to 4000 bytes, which I why I went down this route in the first place.

Thanks for the help.

Andrew Larkin
  • 11
  • 4