How to know/check whether a value is SqlDbType compatible or not?

Question

I'm assigning objects to this list of SqlParameter and then trying to execute the SqlCommand, but it throws an exception saying that one of the objects could not be converted into SqlDbType. Preferably I want to handle such objects before adding them to the parameter collection list. So, how would I check whether a value being added to parameter list is a good/proper one or not? What property should I check for?

Here's is my code :

bool Submit(Progs progs, CommandType commandType, string commandText)
{   
    try
    {
        List<SqlParameter> paramCollection = new List<SqlParameter>();
        foreach(Prog p in progs)
        {
            SqlParameter spTemp = new SqlParameter { ParameterName = p.Name , Value = p.Value};
            paramCollection.Add(spTemp);
            using (SqlConnection con = GetConnection())
            {
                SqlCommand cmd = new SqlCommand { CommandType = commandType, CommandText = commandText, Connection = con };  
                con.Open();
                cmd.Parameters.AddRange(paramCollection ); // Exception is thrown from this line
                cmd.ExecuteNonQuery();
            }
            return true;
        }
        catch(Exception exc)
        {
            return false;
        }
    }

The exception thown says : No mapping exists from object type sol2.CodeBase.BL.Letter[] to a known managed provider native type.

PS : There is a property for of SqlParameter called ParamaterIsSqlType(yes, it's paramAter and not paramEter), which appears only during runtime(i.e. when I inspect spTemp with a breakpoint on the next line) and which is always set to false? What kind of property is this, so that it appears only during runtime??? Also, what value this "ParamaterIsSqlType" indicates?

This property is non-public, hence you neither can see nor use it and yes, it's misspelled but [won't be fixed](http://connect.microsoft.com/VisualStudio/feedback/details/90483/the-word-parameter-is-misspelled-in-the-paramaterissqltype-flag-in-the-non-public-members-of-system-data-sqlclient-sqlparameter). — Tim Schmelter, Feb 25 '12 at 20:48
What is `param`? Where is it declared? You are also opening the connection twice, that should raise an [InvalidOperationException](http://msdn.microsoft.com/en-us/library/system.invalidoperationexception.aspx). — Tim Schmelter, Feb 25 '12 at 20:54
ya, that was a typing mistake, fixed now. So now, how would you suggest to check if the conversion is possible or not??? Also, what does this paramaterIsSqlType indicate(why is it always false OR when it will be true)? — MrClan, Feb 25 '12 at 21:06
What is class `Prog` and it's properties `Name` and `Value`? Why are you adding SqlParameters to `paramCollection` for every prog, so that the list will increase but still holds the old values? Actually i have no clue what you're doing here. — Tim Schmelter, Feb 25 '12 at 21:11
Prog is a simple class where Prog.Name is always a string, but Prog.Value returns an object which can be integer, string, datetime or array depending upon conditions. — MrClan, Feb 25 '12 at 21:22
The internal property `parameterSqlType` checks just if a given parameter-value implements [INullable](http://msdn.microsoft.com/en-us/library/system.data.sqltypes.inullable.aspx). http://reflector.webtropy.com/default.aspx/4@0/4@0/untmp/DEVDIV_TFS/Dev10/Releases/RTMRel/ndp/fx/src/Data/System/Data/SqlClient/SqlParameter@cs/1305376/SqlParameter@cs But even if your class would implement INullable, you cannot expect that the dbms could use it anyway. A SqlParameter's value must be of a type that can be mapped to a known managed provider native type(f.e. String to varchar, Int32 to int etc). — Tim Schmelter, Feb 25 '12 at 21:26
So i assume that you're trying to add an Array as SqlParameter. That won't work since sql-server has no arrays(but tables). What do you really want to do with this array? — Tim Schmelter, Feb 25 '12 at 21:31
What I want to do is, if Prog.Value has an incompatible type of data, I'll call Prog.Value.ToSqlType() method to make it compatible. So, how would I check if know if Prog.Value can be converted into sqldbtype or not, before there is an exception? — MrClan, Feb 25 '12 at 21:40

Tim Schmelter · Accepted Answer · 2012-02-25T22:59:20.840

Do what SqlParameter would do to infer conversion from Type to SqlDbType if it's not set explicitely. So, no, there's (yet) no property or method available in the framework.

System.Type type = p.Value.GetType();
var isConvertible = IsConvertibleToSqlDbType(type);
if(!isConvertible){
    //call your custom ToSqlType-method
}

Following method is derived directly from SqlParemeter's private void InferSqlType (object value):

public static bool IsConvertibleToSqlDbType(Type type)
{
    switch(type.FullName) {
        case "System.Int64":
        case "System.Data.SqlTypes.SqlInt64":
            //SetSqlDbType (SqlDbType.BigInt);
            return true;
        case "System.Boolean":
        case "System.Data.SqlTypes.SqlBoolean":
            //SetSqlDbType (SqlDbType.Bit);
            return true;
        case "System.String":
        case "System.Data.SqlTypes.SqlString":
            //SetSqlDbType (SqlDbType.NVarChar);
            return true;
        case "System.DateTime":
        case "System.Data.SqlTypes.SqlDateTime":
            //SetSqlDbType (SqlDbType.DateTime);
            return true;
        case "System.Decimal":
        case "System.Data.SqlTypes.SqlDecimal":
            //SetSqlDbType (SqlDbType.Decimal);
            return true;
        case "System.Double":
        case "System.Data.SqlTypes.SqlDouble":
            //SetSqlDbType (SqlDbType.Float);
            return true;
        case "System.Byte[]":
        case "System.Data.SqlTypes.SqlBinary":
            //SetSqlDbType (SqlDbType.VarBinary);
            return true;
        case "System.Byte":
        case "System.Data.SqlTypes.SqlByte":
            //SetSqlDbType (SqlDbType.TinyInt);
            return true;
        case "System.Int32":
        case "System.Data.SqlTypes.SqlInt32":
            //SetSqlDbType (SqlDbType.Int);
            return true;
        case "System.Single":
        case "System.Data.SqlTypes.Single":
            //SetSqlDbType (SqlDbType.Real);
            return true;
        case "System.Int16":
        case "System.Data.SqlTypes.SqlInt16":
            //SetSqlDbType (SqlDbType.SmallInt);
            return true;
        case "System.Guid":
        case "System.Data.SqlTypes.SqlGuid":
            //SetSqlDbType (SqlDbType.UniqueIdentifier);
            return true;
        case "System.Money":
        case "System.SmallMoney":
        case "System.Data.SqlTypes.SqlMoney":
            //SetSqlDbType (SqlDbType.Money);
            return true;
        case "System.Object":
            //SetSqlDbType (SqlDbType.Variant); 
            return true;
        default:
            return false;
    }
}

how about this? ...*modified code* : SqlParameter spTemp = new SqlParameter { ParameterName = p.Name , Value = p.Value}; try { SqlDbType sqlType = spTemp.SqlDbType; } catch { spTemp.Value = spTemp.Value.ToSqlType(); } paramCollection.Add(spTemp); — MrClan, Feb 26 '12 at 05:43

How to know/check whether a value is SqlDbType compatible or not?

1 Answers1