I am not generally a proponent of creating tables with columns of datatype sql_variant in them. Often times these tables seem to be a really great idea at first, but then slowly begin to become problemattic. However, if you do happen to use the sql_variant columns in your database eventually you may need to use those columns in your application code. Microsoft provides a datatype mapping table between the SQL native datatypes the CLR datatypes for SQL Server, and the CLR native datatypes in the Books Online. If you look at the table, you will notice that there is no SQL Server CLR datatype for the sql_variant datatype, and the CLR native type is Object, the base for all types in the CLR runtime.
So how exactly do you use a sql_variant in CLR?
Well first, lets look at how you would use one in SQL Server. To do this, we'll create a table called SpecialData and we'll load it with a few rows of differing datatypes:
CREATE TABLE SpecialData
(RowID INT IDENTITY PRIMARY KEY,
Data sql_variant)
DECLARE @varchar VARCHAR(20)
SET @varchar = 'some data'
INSERT INTO SpecialData VALUES (@varchar)
DECLARE @datetime datetime
SET @datetime = GETDATE()
INSERT INTO SpecialData VALUES (@datetime)
DECLARE @decimal decimal(10,2)
SET @decimal = 12.22
INSERT INTO SpecialData VALUES (@decimal)
DECLARE @int INT
SET @int = 100
INSERT INTO SpecialData VALUES (@int)
DECLARE @float float
SET @float = 1.23
INSERT INTO SpecialData VALUES (@float)
DECLARE @tinyint tinyint
SET @tinyint = 4
INSERT INTO SpecialData VALUES (@tinyint)
DECLARE @bigint bigint
SET @bigint = 100
INSERT INTO SpecialData VALUES (@bigint)
INSERT INTO SpecialData VALUES (NULL)
SELECT *
FROM SpecialData
Just looking at the output information from the select statement, it looks like we have a couple of decimal values, some integer values, a datetime value and a string value in our data. One of the problems that people encounter when using sql_variant columns is datatype conversion problems either through incorrect filtering of the data in the column, or simply because they don't know how to find out what datatype the value for a specific row has.
Included in SQL Server is the SQL_VARIANT_PROPERTY() function which will provide the definition of the data that is stored in a sql_variant column:
SELECT *,
SQL_VARIANT_PROPERTY(data,'BaseType') AS basetype,
SQL_VARIANT_PROPERTY(data,'Precision') AS PRECISION,
SQL_VARIANT_PROPERTY(data,'Scale') AS scale,
SQL_VARIANT_PROPERTY(data,'BaseType') AS basetype,
SQL_VARIANT_PROPERTY(data,'TotalBytes') AS totalbytes,
SQL_VARIANT_PROPERTY(data,'Collation') AS collation,
SQL_VARIANT_PROPERTY(data,'MaxLength') AS maxlength
FROM SpecialData Using this function, we can now see easily what the datatype for each row's column data is. Now how do we do something similar in CLR, especially when the only type that we can map the column to is a .NET object? Well for those familiar with programing in CLR and .NET, the answer is that while sql_variant is mapped to an Object, it is actually a boxed SQL Server CLR datatype. For those not familiar with programming in .NET, essentially all datatypes, classes, structs are Objects, so any of them can be "boxed" as an Object datatype. To get more indepth coverage of this subject I'd recommend that you read one of the following articles:
Since our sql_variant data can be typed in CLR as an Object, we can use the .GetType() method to determine what the actual datatype is for the data being stored in a particular row. This in turn has a .Name Property that can be used to get the actual name of the SQL Server CLR Datatype that the Object can be cast into to get the value back out of the Object:
using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace Sql_VariantExample
{
class Program
{
static void Main(string[] args)
{
string line = "RowID: {0}\tDataType: {1}\tValue: {2}";
using (SqlConnection conn = new SqlConnection("Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=SSPI;"))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("select * from SpecialData", conn))
{
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
object obj = dr.GetSqlValue(1);
if(obj is SqlInt16)
{
Console.WriteLine(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlInt16)obj).Value.ToString()));
}
else if(obj is SqlInt32)
{
Console.WriteLine(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlInt32)obj).Value.ToString()));
}
else if (obj is SqlInt64)
{
Console.WriteLine(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlInt64)obj).Value.ToString()));
}
else if (obj is SqlDecimal)
{
Console.WriteLine(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlDecimal)obj).Value.ToString()));
}
else if (obj is SqlDateTime)
{
Console.WriteLine(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlDateTime)obj).Value.ToString()));
}
else if (obj is SqlString)
{
Console.WriteLine(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlString)obj).Value.ToString()));
}
else if (obj is SqlDouble)
{
Console.WriteLine(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlDouble)obj).Value.ToString()));
}
else if (obj is SqlChars)
{
Console.WriteLine(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlChars)obj).Value.ToString()));
}
else if (obj is SqlByte)
{
Console.WriteLine(string.Format(line, dr.GetInt32(0).ToString(), obj.GetType().Name, ((SqlByte)obj).Value.ToString()));
}
}
}
conn.Close();
}
}
}
}
Now to be perfectly honest, some .NET guru is probably laughing at the above example, but hey, I am a DBA not a C# developer full time. I am sure that there is probably a much better way to do this in .NET using Reflection or some of the other features that I haven't learned yet. It should however provide enough of a general idea to demonstrate how to use a sql_variant column in CLR.