DataAccess for sql ce image type can not exceed 8000 bytes

Sep 4, 2009 at 8:13 AM

Hi,

I encounter a problem , I use the DataAccess block to connect to a sql ce database  and insert a image into database image column, raise a error :" Byte array truncation to a length of 8000." under the code snap , the product photo column is image data type,

public class SqlCeEntLibDataAccess : DataAccessBase
 {
        private SqlCeDatabase database = null;

        public SqlCeEntLibDataAccess()
        {
            database = new SqlCeDatabase(this.ConnectionString);
        }

public override bool UpdateProducts(Products product)
        {
            // Validate Parameters
            if (product == null)
                throw new ArgumentNullException("product");

            if (product.ProductID <= 0)
                throw (new ArgumentOutOfRangeException("product.ProductID"));

            // Execute SQL Command
            string cmdText = @"UPDATE Products SET
        ProductName = @ProductName,
        SupplierID = @SupplierID,
        CategoryID = @CategoryID,
        QuantityPerUnit = @QuantityPerUnit,
        UnitPrice = @UnitPrice,
        UnitsInStock = @UnitsInStock,
        UnitsOnOrder = @UnitsOnOrder,
        ReorderLevel = @ReorderLevel,
        Discontinued = @Discontinued,
        Photo = @Photo
          WHERE ProductID = @ProductID";

           DbParameter[] parameters = {
        database.CreateParameter("@ProductID", DbType.Int32,0, product.ProductID),
        database.CreateParameter("@ProductName", DbType.String,40,product.ProductName),
        database.CreateParameter("@SupplierID", DbType.Int32,0,product.SupplierID),
        database.CreateParameter("@CategoryID", DbType.Int32,0,product.CategoryID),
        database.CreateParameter("@QuantityPerUnit", DbType.String,20,product.QuantityPerUnit),
        database.CreateParameter("@UnitPrice", DbType.Currency,0,product.UnitPrice),
        database.CreateParameter("@UnitsInStock", DbType.Int32,0,product.UnitsInStock),
        database.CreateParameter("@UnitsOnOrder", DbType.Int32,0,product.UnitsOnOrder),
        database.CreateParameter("@ReorderLevel", DbType.Int32,0,product.ReorderLevel),
        database.CreateParameter("@Discontinued", DbType.Boolean,0,product.Discontinued),
        database.CreateParameter("@Photo", DbType.Binary ,0,product.Photo)
                                 };
            int rowAccount = database.ExecuteNonQuery(cmdText, parameters);
            return (rowAccount > 0 ? true : false);
        }

use the same image , I insert it into access database ,but have no error , code segment:

 public class AccessEntLibDataAccess : DataAccessBase
 {

     private Database database = null;

        public AccessEntLibDataAccess()
        {
            database = DatabaseFactory.CreateDatabase();
        }

public override bool UpdateProducts(Products product)
        {
            // Validate Parameters
            if (product == null)
                throw new ArgumentNullException("product");

            if (product.ProductID <= 0)
                throw (new ArgumentOutOfRangeException("product.ProductID"));

            string cmdText = @"UPDATE Products SET
             CategoryID = @CategoryID ,
              Discontinued = @Discontinued ,
              Photo = @Photo ,
              ProductName = @ProductName ,
              QuantityPerUnit = @QuantityPerUnit ,
              ReorderLevel = @ReorderLevel ,
              SupplierID = @SupplierID ,
              UnitPrice = @UnitPrice ,
              UnitsInStock = @UnitsInStock ,
              UnitsOnOrder = @UnitsOnOrder
         WHERE ProductID = @ProductID";

            // Execute SQL Command
            using (DbCommand cmd = database.GetSqlStringCommand(cmdText))
            {
                database.AddInParameter(cmd, "@CategoryID", DbType.Int32, product.CategoryID);
                database.AddInParameter(cmd, "@Discontinued", DbType.Int16, product.Discontinued);
                database.AddInParameter(cmd, "@Photo", DbType.Binary, product.Photo);
                database.AddInParameter(cmd, "@ProductName", DbType.String, product.ProductName);
                database.AddInParameter(cmd, "@QuantityPerUnit", DbType.String, product.QuantityPerUnit);
                database.AddInParameter(cmd, "@ReorderLevel", DbType.Int16, product.ReorderLevel);
                database.AddInParameter(cmd, "@SupplierID", DbType.Int32, product.SupplierID);
                database.AddInParameter(cmd, "@UnitPrice", DbType.Currency, product.UnitPrice);
                database.AddInParameter(cmd, "@UnitsInStock", DbType.Int16, product.UnitsInStock);
                database.AddInParameter(cmd, "@UnitsOnOrder", DbType.Int16, product.UnitsOnOrder);
                database.AddInParameter(cmd, "@ProductID", DbType.Int32, product.ProductID);

                int returnValue = database.ExecuteNonQuery(cmd);
                return (returnValue > 0 ? true : false);
            }

        }

the DbType.Binary is range between 1 and 8000 bytes , but why the access database have no error ?  Could anyone help me , I want to use Ent Lib to sql ce database ?

Thanks very much!

Sep 4, 2009 at 9:34 AM

Hi,

Can you please try, to explicitly create a SqlCeParameter for the Photo parameter with a SqlDbType of Image.

            SqlCeParameter param = new SqlCeParameter();
            param.SqlDbType = SqlDbType.Image;
            param.ParameterName = "@Photo";
            param.Value = product.Photo;

 

then add it to your parameters array.

Please see this:

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Sep 4, 2009 at 9:59 AM

Hi, AvanadeSupport

Thanks your quicky response!

I have tried this before , It have no error indeed,

But , when I use Ent Lib data access block connect to sql ce , it raise error . because I want to use Ent Lib as a data access to my sql ce database , If can not figure it out,

I have to abandon Ent Lib and explicitly create a SqlCeParameter array .

further more , I wonder , why it have no error when connect to access database.

Thanks !

Sep 4, 2009 at 10:13 AM

Hi, AvanadeSupport

I use this way , It can work under Ent Lib , Thank you !

 SqlCeParameter param = new SqlCeParameter();
            param.SqlDbType = SqlDbType.Image;
            param.ParameterName = "@Photo";
            param.Value = product.Photo;


            DbParameter[] parameters = {
                                     database.CreateParameter("@ProductID", DbType.String,40,product.ProductID),
         database.CreateParameter("@ProductName", DbType.String,40,product.ProductName),
         database.CreateParameter("@SupplierID", DbType.Int32,0,product.SupplierID),
         database.CreateParameter("@CategoryID", DbType.Int32,0,product.CategoryID),
         database.CreateParameter("@QuantityPerUnit", DbType.String,20,product.QuantityPerUnit),
         database.CreateParameter("@UnitPrice", DbType.Currency,0,product.UnitPrice),
         database.CreateParameter("@UnitsInStock", DbType.Int32,0,product.UnitsInStock),
         database.CreateParameter("@UnitsOnOrder", DbType.Int32,0,product.UnitsOnOrder),
         database.CreateParameter("@ReorderLevel", DbType.Int32,0,product.ReorderLevel),
         database.CreateParameter("@Discontinued", DbType.Boolean,0,product.Discontinued),
         param
                                     };