Migration DbType Bug?

I've been using SubSonic for about 8 months and am just now getting started with migrations.  They are truly righteous, but I'm having a problem.  I need to create a table with a binary data field.  In SQL Server terms, I need the data type to be "image".  The likeliest candidate of the System.Data.DbType enum appears to be Binary.  However, when using DbType.Binary, I end up with a tinyint.  Here is some example code:

TableSchema.Table table = CreateTableWithKey("some_table");
table.AddColumn("binary_field", System.Data.DbType.Binary, 8000, false);

What I end up with in the database, again as I mentioned, is "binary_field" being a tinyint.  This is not at all expected.

I took a look in the SubSonic source code and discovered what I think is the problem.  There is a switch statement in ANSISqlGenerator.GetNativeType() that returns "tinyint" for DbType.Binary.  Should this be changed and/or overridden in a subclass?

What does the communal wisdom suggest?

It scares me when I see a conversation start up about a DB and then image comes into play.  Are you planning on storing an image in the database?

DiverKas:

It scares me when I see a conversation start up about a DB and then image comes into play.  Are you planning on storing an image in the database?

Yes.  Image data is one of many forms of binary data that I do plan to store in the database.  Is this a bad idea?  What am I missing?  It's just data, isn't it?

Does anyone know how to use migrations to create binary data fields?

Thanks.

Databases are not the best storage mechanism for images.  You are better off storing on drive as a file, and store the path in the table.  This avoids your DB files from growing at an alarming rate, as well as paging issues when storing and querying those records. 

While a database CAN hold image data, the better question is, SHOULD it... and it is generally considered bad practice to do so.

Well I think it depends if you are using SqlServer 2008 then it might not be so bad but the price of SqlServer hosting is a lot more then File Hosting but you should always make sure that you only get the images when you really need them and not on every select to that table

yitzchok:

Well I think it depends if you are using SqlServer 2008 then it might not be so bad but the price of SqlServer hosting is a lot more then File Hosting but you should always make sure that you only get the images when you really need them and not on every select to that table

There is never a good reason to store images or BLOB objects in a database.  Not one database manufacturer recommends it.  In fact, most go out of their way to say, "Yes you can, but you really shouldnt".  For the sake of all that is good and right with the world, they should remove the capability.   Storing BLOB data seems like a good idea, but it never is.  The problem lies in how the database engine will store the data.  Because of row paging, a 1.8 meg file (typical for a jpeg these days) really takes up almost 2.8 because of the overhead of the 8k blocks and header info repeated on each page.  Unlike row data, which at most will take 4 pages, a typical image will take 237500 pages, which are not guaranteed to be contiguous.  Its a huge impact on performance to read the data as well as write it.  And lets not forget the resizing impact everytime the storage file has to grow to handle all that BLOB data.  SQL Server backups become a HUGE performance nightmare as the length of time required to execute the backup grows increasingly unmanageable.

Files are best stored as such, it makes it simple to link them, or embed them without have to go through the hoops of mangling it back into a format that is usable on the web.  File system storage is exactly that, and is much more efficient than the DB.

SQL Server 2008 offers the ability store filestream objects...  but this is just a fancy way to talk about BLOB fields and have the same basic constraints.  

I see two sides to the story check out http://tech.groups.yahoo.com/group/altdotnet/messages/13860
I would go with blobs out of the DB but its good to know both sides of the fence