Setting MaxValue for Migrations

12 03 2013

I’ve just been using the MigratorDotNet framework for the first time. I was creating a migration that would create a new table with various new fields. One of the fields I wanted to create was to store files so therefore I wanted a varbinary(max) field. If you leave the size undeclared, it will create a varbinary(8000). The way I got around this was to create my column as follows:

Column file = new Column("BinaryFile", System.Data.DbType.Binary, int.MaxValue, ColumnProperty.NotNull);

Using the int.MaxValue gives the same result as varbinary(max). However, this doesn’t create a varbinary(max) field (at least not in SQL Server 2008 R2 which is what I am using). It creates an image field instead. This isn’t necessarily a problem, it still gives me the space I require. Importantly though, Microsoft are now advising you do not use this type in new development as it will be removed in a future version of SQL Server (see MSDN article here). We’ve weighed this up and have decided that this is not a problem for us at this time because we are developing improvements to a current system with no plans for any SQL upgrade in the future. There are also uses of the image type in other parts of the database. We have instead logged it in our technical backlog to go through and change all uses of this type at a later date. Personally, in a new project, I would not use it, just in case!

I did some searching in the hope I would find a more elegant solution (something akin to the EF Code First data annotations would have been nice) but I didn’t find anything. If anybody comes across this post, and you’ve found a nicer way to do this, please comment on here to let me know!

Advertisements




Casting SqlParameter/Object as a Byte array

2 03 2010

I’ve just spent ages working this out, and all the time I was just making a silly mistake! So hopefully this may save someone else the pain! For anyone that doesn’t want to read the rest of the post, the code to do it is:

byte[] fileContents = (byte[])sqlParameters[3].Value;

I was saving a PDF file as a byte array and then inserting that to my database as a varbinary. I needed to be able to then get that varbinary back out of the database as a byte array, and open it without corrupting the information.

So casting is the obvious candidate. But I made a silly mistake and had the following:

byte[] fileContents = (byte)sqlParamters[3].Value;

Which caused a problem as I was trying to cast a single byte to a byte array. I then got all caught up in using the ToString() method on the parameter value and then converting it from a string to a byte array. It just kept getting corrupted!

When I actually sat back and thought about it, a SqlParameter is just an object. So a simple cast is all that’s required. When I took a step back, I realised my code should have been:

byte[] fileContents = (byte[])sqlParameters[3].Value;

See what I mean about it being a stupid mistake?! All I needed was the square brackets in the casting and now it works perfectly, giving me back my lovely PDF, uncorrupted!

Sometime problems aren’t as complicated as you first think and the easy solution is the right one 🙂