Geek Answers Handbook

Display CLR Parameter Data

I am writing a stored procedure generator, and I need to map CLR types to their SQL Server types.

MSDN lists type mappings at: http://msdn.microsoft.com/en-us/library/ms131092.aspx , but I don’t want to use the large switch statement to handle mappings.

Is there an easy way to get the SQL Server type as a string using any process used by System.Data.SqlTypes?

I need a method signature like this:

static string GetSqlType(Type clrType) { ... return sqlType; }

So, we get the following call:

 string sqlType = GetSqlType(1.GetType());

sqlType should contain: "int".

0
source share
1 answer

This is made more complicated because it depends on the version of sql server you are ready to target.

  • For the purpose of 2008, you will probably need DateTime instances to map to improved datetime2 rather than datetime.
    • TimeSpan is even worse since your fallback is using DateTime or varchar.

Here is a guide to how sql server will work with data types in replication scripts

SQL Server Compact Edition is even more confused because it does not support the column types varchar (max), varbinary (max) (you are limited to explicit columns ~ 4KB in length).

At some point, you will also need to make a heuristic decision on areas with several options.

  • textual information may be varchar (n) or varchar (max).
    • or XML since 2005
    • or TEXT but it's out of date
  • the decimal map will display well for entering numbers, but is risky at the output, since the numerical value has a larger scale.
  • should bitmaps be figurative or varbinary?
    • the image is out of date again, but it can annoy people who are still using it.
  • Unsigned integers
    • uint can be safely inserted into bigint
    • ulong is more problematic
    • bigints will be even more fun.

Considering that all this happens with the help of a simple simple switch statement in the utility function, life will become much easier than trying to rely on some opaque BCL library, intended only for type conversion, and not for creating text sql. It also allows you to clear the default throw or the default varchar (max), which will be your "undefined" behavior, which will remain under your control.

Returning a simple immutable class from a form method:

 public sealed class SqlServerTypeDescription { // for sql text construction public readonly string SqlServerName; // for code construction public readonly SqlDbType SqlDbType; // for code construction public readonly Type ClrType; // constructor etc. }

You might want to add an optional precision / size value, although this may be what you decide to leave to the user.

+2
source

More articles:


All Articles