3

I've got a problem with SQL update statements when I pass null values (or better let's say DbNull values) to a DbParameter object. When I pass the DbNull value to the Value property, the DbType of the DbParameter object is still STRING.

If I try to write into a binary field ( varbinary(max) ), I got an exception that the conversion between varchar and varbinary is not possible. So in that case I have to set the DbType by my own. My question is now, how do I get the DbType from an .Net type. I want to be this generic, so I can use my methods with other databases. I couldn't find anything usefull in the MSDN documentation. If someone can get me some hints how to solve this, I would appreciate this. Or maybe I'm on the wrong path. I'm not sure for the moment.

msfanboy
  • 5,273
  • 13
  • 69
  • 120
  • Straightforward solution is to use switch(dbType) manually for type you're using – sll Aug 12 '11 at 22:12
  • When an objects value is null you can not get the type of the object thus I can not set the DbType. Its a dead end street... – msfanboy Aug 12 '11 at 22:15
  • @Dissy you speak of the default in the switch/case ? How would you use the switch case? I will show you bit of my code and I dont think my approach works with your suggestion... – msfanboy Aug 12 '11 at 22:23

1 Answers1

1

The best thing I can suggest there is to use a generic method to add the parameter, i.e.

... Foo<T>(T value, ...)

That way, you can check typeof(T) even if value is null. You would then need to switch on the type of T and hard-code the relationship. A switch on Type.GetTypeCode(...) makes things fairly easy.

Another approach is to pass the values in as typed members - for example, in dapper we pass the parameters in as a wrapper object (typically an anonymous type) - then we can inspect the types from the MemberInfos.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Thanks Marc just wanted to post a sample but I think its not needed. I also thought about making the CreateParameter(T value,String marker). I dont think nobody knows better than you in that area around this time ;-) – msfanboy Aug 12 '11 at 22:28