How do I get the SqlDbType from a DataTable?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to know what the native SQL data type is. How can I figure this out
once I have filled a dataset?

Thanks in advance.
 
That doesn't get me the actual SqlDbType as far as I know. I need to be able
to retrieve the data type from the SqlDbType enumeration.
 
AFAIK there's not direct support b/c the Datatable isn't coupled with any
SqlClient components. You could however write a function to do the
conversion - I started it, the rest of the conversions are on the link
below:

private void button3_Click(object sender, System.EventArgs e)
{
DataColumn dc = new DataColumn("MyColumn", typeof(String));
SqlDbType sqlType = ConvertType(dc.DataType);
MessageBox.Show(sqlType.ToString());
}

private SqlDbType ConvertType(Type type)
{
switch(type.Name)
{
case "Int64":
return SqlDbType.BigInt;
case "Byte[]":
return SqlDbType.Binary ;
case "Boolean":
return SqlDbType.Bit;
case "String":
return SqlDbType.Char;
default:
return SqlDbType.Variant;//I just did this so it'd compile
//You need to actually specify a default type or make sure
//that all paths return a value.
}
}

http://msdn.microsoft.com/library/d...f/html/frlrfSystemDataSqlDbTypeClassTopic.asp
 
I already created that type of functionality, the problem I am running into
is when I get to the numerics I need to know what their specified size is
(precision). I also had to fake a TEXT datatype by checking the MaxLength
column property to check if it was greater than 8000, which is the cut-off
for varchar/char data types I believe. So far this conversion thing sucks.
I had built a form designer which would display the fields into an
appropriate HTML element type depending on their SQL datatypes, now with .NET
I am getting into problems converting it. I would be a nice feature for MS
to add to ADO.Net.
 
You can grab the schema table from the db object and map the fields over
from there. Remember that a Datatable is totally decoupled from a DB so it
doesn't know or care where the data came from. It doesn't even know if it's
going to be used for a database scenario so doing this would be next to
impossible. For isntance, I could fill a datatable from an array, a web
service, a Sql Server database, an oracle databse and just about anythign
else, so reverse engineering this from the table in respect to precision
would be really rough considering that the same columns could be filled from
columns with different precisons/lenghts etc.. Enough about why it won't
work though (sorry if I got a little longwinded on that). can you query the
system catalog for this info? I think you can get there pretty easy and
find out the info you want.
 
It seems odd that MS couldn't just add Extended property attributes while
creating the DataTable via SqlDataAdapter.Fill.

I am ending up creating my own SqlDataAdapter in order to achieve this.
 
Back
Top