convert string value to SqlDbType

Question

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;

Adam Valpied · Accepted Answer · 2014-02-18 05:02:56Z

3

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

edited Feb 18, 2014 at 5:02

answered Feb 18, 2014 at 2:56

Adam Valpied

1786 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wayfarer Over a year ago

Thanks, that works. SqlDBType was a typo when I built the question. Not sure what you meant by wrong way around, as the correct result in DDRow seems to be produced.

Adam Valpied Over a year ago

That's great. In your post you seemed to pass s1 as the first parameter - which was the name of the data type if I'm not mistaken, but it should have been the parameter name. I've edited my answer to include the "@" symbol as I forgot to put that in there. Either way, what you are doing now looks fine.

Collectives™ on Stack Overflow

convert string value to SqlDbType

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related