Support for SqlDbType.Image?

Topics: General Discussion Forum, July and December Releases Forum
May 8, 2007 at 10:51 AM
Hi,

I have been using an unmodified version of the second release of the Service Factory in one of my projects. When I generate the Data Access layer I run into a problem for one of my database tables that contains an Image type column. The generated code looks similar to this:
        public DbCommand ConstructUpdateCommand(Database db, CustomData customData)
        {
            DbCommand command = db.GetStoredProcCommand("dbo.UpdateCustomData");
 
            db.AddInParameter(command, "Id", DbType.Int32, customData.Id);
            db.AddInParameter(command, "Data", DbType.Object, customData.Data);
            db.AddInParameter(command, "TS", DbType.Binary, customData.TS);
 
            return command;
        }

When I run this code from a unit test, I get an error caused by the second parameter, Data. The error output looks like this:

TestCase 'Service.DataAccess.Tests.CustomDataDataAccessFixture.UpdateCustomData'
failed: Service.DataAccess.RepositoryFailureException : Unexpected error occurred in the database server
----> System.Data.SqlClient.SqlException : Operand type clash: sql_variant is incompatible with image
...
...

If I alter the generated code to look like this, it works fine:
        public DbCommand ConstructUpdateCommand(Database db, CustomData customData)
        {
            SqlCommand command = (SqlCommand)db.GetStoredProcCommand("dbo.UpdateCustomData");
 
            db.AddInParameter(command, "Id", DbType.Int32, customData.Id);
            db.AddInParameter(command, "Data", DbType.Object, customData.Data);
            db.AddInParameter(command, "TS", DbType.Binary, customData.TS);
 
            command.Parameters["@Data"].SqlDbType = SqlDbType.Image;
 
            return command;
        }

Am I doing something peculiar, or have I misunderstood something, or is this really something you have to do in order to make it work? Does the guidance package not work out of the box for all Sql Server databases? In such case, has anyone modified the guidance package to counter this error?

Best regards
Linus
Developer
May 9, 2007 at 2:31 AM
Linus,

I've never seen this problem before but considering your suggested fix, you may update the t4 template that is used to generate this code without even compiling/registering the built in package.
Just open "Data Access Guidance Package\Templates\T4\DAC\EntityUpdateFactory.t4" file and follow the logic to create the code that you have in your sample (that is very simple code logic) and you shouldn't have trouble in adding your fix with a simple check for the column type (SqlDbType.Image). For example you can replace the function "GetGenericDBType()" with your own and include the Image type check inside that funcion.
Then save the file and try running the recipe. That should generate the code that you suggested.
Let me know if that is a bit beyond your level/time and I will try to assist you further.

Charly
May 11, 2007 at 1:52 PM
Edited May 11, 2007 at 1:56 PM
I have had the same problem and have been hand-editing the generated output. This will be a useful fix for me as well.
John

P.S. I have simply modified the line:

db.AddInParameter(command, "Data", DbType.Object, customData.Data);

to

db.AddInParameter(command, "Data", DbType.Binary, customData.Data);

and that seems to do the trick.
May 11, 2007 at 3:10 PM
Hi,
I have modified the Data Access Guidance Package. The simplest thing is to change Data Access Guidance Package\Templates\T4\DAC\EntityUpdateFactory.t4 template and change it to generate code as you wish. You have to know when you extract code for Data Access Guidance Package you will have an different name and guide for this guidance and because of that enable them throygh Guidance Manager in your project. Please let me know if I will have a problem with this issue, I will try help.
Regards,
Lukasz
May 15, 2007 at 2:17 PM
Hi,

I solved the problem by altering DbTypesHelper.t4 in the Data Access Guidance Package. I changed the GetGenericDBType function so that DatabaseType.Blob types generates DbType.Binary instead of DbType.Object:
        case DatabaseType.Blob:
            return "DbType.Binary";

To me this looks like a bug in the original Guidance Package. An Image data type should be binary! Don't you agree?

Thank you all for your support!
May 15, 2007 at 2:38 PM
Hi,
It is good to hear that you've solved this issue. Frankly, I think this is a bug in the original factory. I think that will be good to share your fix and add this issue to the Dec2006Cs as work item and fix this or just share with community.
Regards,
Lukasz
Developer
May 15, 2007 at 9:54 PM
You may also post this to Issue Tracker.
Good catch and workarround!!!

Charly