danielwertheim

danielwertheim


notes from a passionate developer

Developer that lives by the mantra "code is meant to be shared".

Share


Tags


Disclaimer

This is a personal blog. The opinions expressed here represent my own and not those of my employer, nor current or previous. All content is published "as is", without warranty of any kind and I don't take any responsibility and can't be liable for any claims, damages or other liabilities that might be caused by the content.

Truly amazing! SQLCE truncates parameters

Daniel WertheimDaniel Wertheim

Recently I got a bug report in SisoDb where a document got truncated hence when getting deserialized back, the deserialization process blow up. After a short investigation I found this truly amazing bug in SQLCE4. If you have a field being nvarchar(4000) and you use a command parameters that has a value being 4001 chars where you set the dbParam.Size property to 4001 chars; the size will be 4000 and then it just truncates the value upon insert. SQL Server on the other hand, behaves correctly. It throws an exception warning about truncation.

Short sample

I've put together a small sample showing the behaviour in this Gist: https://gist.github.com/2784218

I really do hope it's me missing something.

class Program  
{
  static void Main(string[] args)
  {
    FatSql(); //Fails
    Console.WriteLine("***** ***** ***** ***** *****");
    Ce(); //Gives 4000 chars and not 4001, hence, truncation

    Console.ReadLine();
  }

  static void FatSql()
  {
    try
    {
      using (var cn = new SqlConnection(
        "data source=.;initial catalog=;integrated security=true;"))
      {
        cn.Open();
        cn.ExecuteSql(
          "if DB_ID('TrulyAmazing') is null begin create database TrulyAmazing; end");
        cn.ExecuteSql(
          "if OBJECT_ID(N'TrulyAmazing.dbo.Foo', N'U') is null begin create table TrulyAmazing.dbo.Foo(SomeValue nvarchar(4000));end");
        cn.ExecuteSql(
          "truncate table TrulyAmazing.dbo.Foo;");
        cn.ExecuteSql(
          "insert into TrulyAmazing.dbo.Foo values (@p0);", new string('a', 4001));
        Console.WriteLine(cn.ExecuteScalarString(
            "select top 1 SomeValue from TrulyAmazing.dbo.Foo;").Length);
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
    }
  }

  static void Ce()
  {
    const string dbPath = @"c:\TempTrulyAmazing.sdf";

    if (!File.Exists(dbPath))
      using (var e = new SqlCeEngine(string.Concat("data source=", dbPath)))
        e.CreateDatabase();

    using (var cn = new SqlCeConnection(
      string.Concat("data source=", dbPath)))
    {
      cn.Open();
      if(string.IsNullOrWhiteSpace(cn.ExecuteScalarString(
        "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME='Foo';")))
        cn.ExecuteSql("create table Foo(SomeValue nvarchar(4000))");

      cn.ExecuteSql("insert into Foo values (@p0);", new string('a', 4001));
      Console.WriteLine(cn.ExecuteScalarString(
        "select top 1 SomeValue from Foo;").Length);
    }
  }
}

public static class AdoExtensions  
{
  public static void ExecuteSql(
    this IDbConnection cn,
    string sql,
    params object[] parameters)
  {
    using(var cmd = cn.CreateCommand())
    {
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = sql;

      for (var i = 0; i < parameters.Length; i++)
      {
        var parameter = parameters[i];
        var dbParam = cmd.CreateParameter();
        dbParam.ParameterName = string.Concat("@p", i);

        if (parameter is string)
        {
          dbParam.DbType = DbType.String;
          dbParam.Size = parameter.ToString().Length;
        }

        dbParam.Value = parameter;

        cmd.Parameters.Add(dbParam);
      }

      cmd.ExecuteNonQuery();
    }
  }

  public static string ExecuteScalarString(this IDbConnection cn, string sql)
  {
    using (var cmd = cn.CreateCommand())
    {
      cmd.CommandType = CommandType.Text;
      cmd.CommandText = sql;

      var value = cmd.ExecuteScalar();

      if(value == null || value == DBNull.Value)
        return null;

      return value.ToString();
    }
  }
}

//Daniel

Developer that lives by the mantra "code is meant to be shared".

Comments