CODEWITHSUNDEEP
c#sql-server
wayfarer
wayfarer

Reputation: 790

convert string value to SqlDbType

I'm building an in-memory data dictionary out of these class objects

public class DDRow
{
    public string ColumnName { get; set; }
    public SqlDBType DataType { get; set; }
    public int ColumnSize { get; set; }
    public bool IsIdentity { get; set; }
    public bool AllowDBNull { get; set; }
 }

...in a subroutine, populating it from a SqlReader.GetSchema call

//Retrieve column schema into a DataTable.
     schemaTable = myReader.GetSchemaTable();
     DDRow wrkRow = null;
     //For each field in the table...
      foreach (DataRow myField in schemaTable.Rows)
       {
            wrkRow = new DDRow(); 
            wrkRow.ColumnName = Convert.ToString(myField["ColumnName"]);  // string
            wrkRow.DataType = myField["DataType"];   //SqlDbType

...which all seems to work except I can't figure out how to convert 

myField["DataType"]

which is an object, into a SqlDBType value to put into DDRow.DataType

I can convert it into a string:

   string s1 = Convert.ToString(myField["DataType"]);

But 

   SqlParameter p = new SqlParameter(s1, o);
   SqlDbType q = p.SqlDbType;  // <-- error here.

throws an error as indicated above, so the string is not enough.

I recognize I might be able to run the string value through a conversion table routine to produce the SqlDbType value; is there any shorter way?

EDIT ------- - in light of adam's answer below, this is the correct way to code it, and the correct way to test that the right result was produced (replaces DataType with ProviderType in the DDRow definition)

  wrkRow.ProviderType = (SqlDbType)(int)myField["ProviderType"];

  SqlParameter wrkS = new SqlParameter("@ABC",wrkRow.ProviderType,8);

  SqlDbType wrkT = wrkS.SqlDbType;

Upvotes: 0

Views: 7724

Answers (1)

Adam Valpied
Adam Valpied

Reputation: 178

The DataType field is the CLR type - not the SqlDbType. The ProviderType field is the one you are after. It is an int that represents the SQL Server data type. You can cast that to a SqlDbType easily. Below is an example that should fit in with your code:

var reader = cmd.ExecuteReader();
var schemaTable = reader.GetSchemaTable();
var fields = new List<DDRow>();

foreach (DataRow myField in schemaTable.Rows)
{
    fields.Add(new DDRow()
    {
        ColumnName = Convert.ToString(myField["ColumnName"]),
        DataType = (SqlDbType)(int)myField["ProviderType"]
    });
}

Then you can do this to create your SqlParameter (assuming that your variable 'o' is the column name).

var field = fields.First();

SqlParameter p = new SqlParameter("@" + field.ColumnName, field.DataType);
SqlDbType q = p.SqlDbType;

In your example so seem to be passing the parameters the wrong way around too. Also in your class example, the DataType field should be SqlDbType rather than SqlDBType

Upvotes: 3

Related Questions