Cannot convert type 'System.Type' to 'System.Data.SqlDbType'

I encounter error, "Cannot convert type 'System.Type' to 'System.Data.SqlDbType'" when attempting the following code:

  // Create a secondary DataSet in order to research the unknown data types of the destination table columns.
  SqlDataAdapter sqlResearchAdapter = new SqlDataAdapter();
  SqlCommand sqlResearchCommand = sqlSelectCommand.Clone();
  DataTable sqlResearchTable;
  DataSet sqlResearchSet;
  sqlResearchAdapter.TableMappings.Add("Table", sqlTableName);
  sqlResearchAdapter.SelectCommand = sqlResearchCommand;
  sqlResearchSet = new DataSet(sqlTableName + "0");
  sqlResearchAdapter.Fill(sqlResearchSet);
  sqlResearchTable = sqlResearchSet.Tables[0];

  // Prepare the SQL UPDATE statement to write changes back to the table.
  sqlUpdateCommand.Connection = sqlConnection;
  sqlUpdateCommand.CommandType = CommandType.Text;
  sqlUpdate = "UPDATE [" + sqlTableName + "] SET "
  + "[" + sqlSTColName + "] = @" + sqlSTColName;
  foreach (string aceColumn in destColumns.Keys) {
  sqlColName = (string)destColumns[aceColumn];
  sqlParmName = "@" + sqlColName;
  sqlUpdate += ", [" + sqlColName + "] = " + sqlParmName;
// *** This next line causes error, "Cannot convert type 'System.Type' to 'System.Data.SqlDbType'"
  sqlUpdateCommand.Parameters.Add(sqlParmName, (SqlDbType)sqlResearchTable.Columns[sqlColName].DataType);
  sqlUpdateCommand.Parameters[sqlParmName].SourceColumn = sqlColName;
  }
  sqlUpdate += " WHERE [" + sqlPKColName + "] = @old" + sqlPKColName
  + jobMessage.SQLServerSettings.GetWhereCondition(true);
  sqlUpdateCommand.Parameters.Add("@" + sqlSTColName, SqlDbType.TinyInt);
  sqlUpdateCommand.Parameters["@" + sqlSTColName].SourceColumn = sqlSTColName;
  sqlUpdateCommand.Parameters.Add("@old" + sqlPKColName, SqlDbType.Int, 32, sqlPKColName);
  sqlUpdateCommand.Parameters["@old" + sqlPKColName].SourceVersion = DataRowVersion.Original;
  sqlUpdateCommand.CommandText = sqlUpdate;

It is impossible for my code to know the SqlDbType of all the columns in the destination table, with the exception only of the primary key (PK) and the standardization flag (ST) columns. However, my code is responsible for manipulating the data in all columns, regardless of data type. Generally speaking, I anticipate encountering only varchar, char, bit, bigint, int, smallint, and tinyint fields.

Ultimate question: How do I determine the SqlDbType of any arbitrary DataColumn object which is a member of the DataColumnCollection of a DataTable (implying that both its SQL and .NET data types are known to ADO.NET) .DataType returns the System.Type equivalent, which is NOT what I need because the C# compiler throws this error message. .DataType.UnderlyingSystemType also does NOT return a SqlDbType value for the same reason. I've searched the documentation and the web and I cannot find any technique for returning a SqlDbType value from a DataColumn. Such a techinque must exist because, obviously, the SqlParameter class requires such a mapping.

Answer this question

Cannot convert type 'System.Type' to 'System.Data.SqlDbType'

Bharg

You have two options:

1- use the overload of Add that just takes a parameter name and an object for the value. We'll infer the type for you. If you're using .NET 2.0, you should use AddWithValue for this instead of Add(string, object).

2- Create a small function that checks for the known types for your app and maps them to the appropriate SqlDbType.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation
Michael He
As far as the dilemma with DBNull values, I don't create a parameter for a column that contains a Null. If you think about what Nulls are supposed to represent, this makes sense.
Magicle

For option 1: I see, I didn't realize you have a bunch of rows and you're trying to create a command that can handle each and any of them. You're right, you should not rely on this method because a value could be null or something like that.

Option 2 should work for the general case. Unless you have very specific needs for sql types, you can do default mappings; for example, for string just pick nvarchar.

Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corporation
dear_vvr

I am using .NET 2.0 with Visual Studio 2005.

In reference to option 1: To determine whether I understand correctly, you are advising that I return a record from the table (obviously using the SELECT SqlCommand), then create parameters for the UPDATE SqlCommand by pushing the value of each column into Parameters.AddWithValue(string Name, object Value) What happens if any of the columns in the initial query contain <NULL> values; will the correct SqlDbType be inferred Will the correct maximum width be determined from n/varchar fields that are returned with only some of the buffer space in use (say, only 2 characters are used in a 25 width column on the first record)

In reference to option 2: I tried this conceptually, but ran into the snag of having the String type mapping to many SqlDbType enumeration values (varchar, nvarchar, char, etc). I don't see a reliable way to resolve this competition for the single String data type.

Thank you.
knutejoh

BonnieB wrote:
As far as the dilemma with DBNull values, I don't create a parameter for a column that contains a Null. If you think about what Nulls are supposed to represent, this makes sense.

I'm sorry, but this doesn't make sense and I am very familiar with the use and application of null values. Not all records will have null values in the same column; some are populated, some aren't. This is typical across myriad applications. Therefore, it is necessary to have a Parameter for every column which my code may be called upon to update.

Without going into too much detail, you must understand that my code is extremely dynamic -- it has limited knowledge of the structure of the tables from which it will receive, process, and submit data. Processing is guided by an incoming instruction file which lists only database, table, and column names (along with flags which indicate what kind of processing to perform), without any data type specification. This won't change, and shouldn't have to considering that I should be able to determine that correct data types (and column widths) from the metadata which I believe ADO.NET has access to, or should.

Daliah

pablo-ms wrote:

For option 1: I see, I didn't realize you have a bunch of rows and you're trying to create a command that can handle each and any of them. You're right, you should not rely on this method because a value could be null or something like that.

Option 2 should work for the general case. Unless you have very specific needs for sql types, you can do default mappings; for example, for string just pick nvarchar.

I didn't realize that I could pick from a generic set of SqlDbType enumeration values as default mappings for System.Type values. With this knowledge, I will attempt to write a generic mapping function (receives a System.Type value and returns a "best-guess" SqlDbType value) and see what happens.

Thank you for your time.

Nishantha

BonnieB wrote:

As far as the dilemma with DBNull values, I don't create a parameter for a column that contains a Null. If you think about what Nulls are supposed to represent, this makes sense.

ASTECH wrote:
I'm sorry, but this doesn't make sense and I am very familiar with the use and application of null values.

Of course it makes sense, but maybe I should have said that all the parameters in my Stored Procs default to NULL if the parameter isn't passed. I guess I left out that important little tidbit. Sorry!!

Jerry Hung

Afterthough as a feature request for .NET:

Please give us a built-in way to accurately map the data types between System.Type (as returned in the sample code above) and System.Data.SqlDbType (as is necessary to write the kind of code that is provided above). .NET has intrinsic knowledge of the metadata accessed during the query and should be able to resolve this relationship for us intuitively.

Thanks!
wateyz
Okay, I know this is way old...but I came here because of a similar issue. One thing that may help others, however, is that the entire slew of if/else-if lowered string comparisons solution could have been consolidated to a single line.

retval = (System.Data.SqlDbType)System.Enum.Parse(typeof(System.Data.SqlDbType), dtype, true);
S Burris
i have created on function to read the data type from the table using information schema and return the 'System.Data.SqlDbType' for the coresponding fields< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

is there is any other option or can any body suggest a better way

public System.Data.SqlDbType funsqldbtype(string tablename,string columnname,string Connstring)
  {

   string StrQuery,dtype;
   System.Data.SqlDbType retval;
   SqlConnection Sqconn=new SqlConnection(Connstring);
   SqlCommand Sqc;
   SqlDataReader Sqdr;
   dtype="";
   StrQuery="SELECT DATA_TYPE FROM INFORMATION_SCHEMA.columns WHERE (TABLE_NAME = '"+tablename.ToString()+"') AND (COLUMN_NAME = '"+columnname.ToString()+"')";
   Sqconn.Open();
   Sqc=new SqlCommand(StrQuery,Sqconn);
   Sqdr=Sqc.ExecuteReader();
   while(Sqdr.Read())
   {
   dtype=Sqdr[0].ToString();
   }

Sqconn.Close();


   //Bigint
   if(dtype.ToLower()== "Bigint".ToLower())
   {
    retval=System.Data.SqlDbType.BigInt;
   }
   else if(dtype.ToLower()=="bit".ToLower())
   {//bit
    retval=System.Data.SqlDbType.Bit;
   }
   else if(dtype.ToLower()=="datetime".ToLower())
   {//datetime
    retval= System.Data.SqlDbType.DateTime;
   }
   else if(dtype.ToLower()=="decimal".ToLower())
   {//decimal
    retval=System.Data.SqlDbType.Decimal;
   }
   else if(dtype.ToLower()=="Float".ToLower())
   {//Float
    retval=System.Data.SqlDbType.Float;
   }
   else if(dtype.ToLower() =="image".ToLower())
   {//image
    retval=System.Data.SqlDbType.Image;
   }
   else if(dtype.ToLower()=="Int".ToLower())
   {//Int
    retval=System.Data.SqlDbType.Int;
   }
   else if(dtype.ToLower()=="Ntext".ToLower())
   {//Ntext
    retval=System.Data.SqlDbType.NText;
   }
   else if(dtype.ToLower()=="Real".ToLower())
   {//Real
    retval=System.Data.SqlDbType.Real;
   }
   else if(dtype.ToLower()=="smallint".ToLower())
   {//smallint
    retval=System.Data.SqlDbType.SmallInt;
   }
   else if(dtype.ToLower()=="tinyint".ToLower())
   {//tinyint
    retval=System.Data.SqlDbType.TinyInt;
   }
   else if(dtype.ToLower()=="smallmoney".ToLower())
   {//
    return System.Data.SqlDbType.SmallMoney;
   }else if(dtype.ToLower()=="text".ToLower())
   {//text
    return System.Data.SqlDbType.Text;
   }
   else if(dtype.ToLower()=="timestamp".ToLower())
   {//
    return System.Data.SqlDbType.Timestamp;
   }
   else if(dtype.ToLower()=="binary".ToLower())
   {//binary
    return System.Data.SqlDbType.Binary;
   }
   else if(dtype.ToLower()=="uniqueidentifier".ToLower())
   {//uniqueidentifier
    return System.Data.SqlDbType.UniqueIdentifier;
   }
   else if(dtype.ToLower()=="varbinary".ToLower())
   {//varbinary
    return System.Data.SqlDbType.VarBinary;
   }
   else if(dtype.ToLower()=="varchar".ToLower())
   {//varchar
    return System.Data.SqlDbType.VarChar;
   }
   else if(dtype.ToLower()=="sql_variant".ToLower())
   {//sql_variant
    return System.Data.SqlDbType.Variant;
   }
   else if(dtype.ToLower()=="Money".ToLower())
   {//Money
    return System.Data.SqlDbType.Money;
   }
   else if(dtype.ToLower()=="nchar".ToLower())
   {//nchar
    return System.Data.SqlDbType.NChar;
   }
   else if(dtype.ToLower()=="nvarchar".ToLower())
   {//nvarchar
    return System.Data.SqlDbType.NVarChar;
   }
   else if(dtype.ToLower()=="smalldatetime".ToLower())
   {//smalldatetime
    return System.Data.SqlDbType.SmallDateTime;
   }
   else if(dtype.ToLower()=="Char".ToLower())
   {//Char
    return System.Data.SqlDbType.Char;
   }
   else
   {//sql_variant
     retval=System.Data.SqlDbType.Variant;
   }
   return retval;

   //Numeric sysname
  }
yoyop
Please explain. In order to use SqlCommand.Parameters.Add(string ParameterName, SqlDbType Type), we are required to provide one of the SqlDbType enumeration values. Which .Name property will map to a SqlDbType value
MatTavis
This recommendation appears to be working after some limited testing. This thread can be closed. Thanks again!
dpuza
This is becase SqlDbType is not a type but enumerator and you cannot do this conversion. You could use Name property of the returned Type to define what is your actual type
Cannot convert type 'System.Type' to 'System.Data.SqlDbType'

Answer this question