Solved

I need to capture what SQL datatypes are being returned.

Posted on 2011-03-14
9
260 Views
Last Modified: 2013-12-17
I am returning a dataset from a SQL 2005 stored procedure.

I need to capture what SQL datatypes are being returned.

For example. Column1 = Varchar(10), Column2 INT.

Is this possible?
0
Comment
Question by:Mr_Shaw
9 Comments
 
LVL 12

Expert Comment

by:enachemc
ID: 35127496
use the metadata of the returned resultset
0
 

Author Comment

by:Mr_Shaw
ID: 35127522
how what I do that?
0
 
LVL 11

Accepted Solution

by:
Kusala Wijayasena earned 200 total points
ID: 35127525

I don't think there is 1 to 1 map between SQL data types and CLR data type. But there is a standard mapping that you could use

http://msdn.microsoft.com/en-us/library/ms131092.aspx

-Kusala
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:Mr_Shaw
ID: 35127527
My dataset is populated from a stored procedure.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 35127610
The schema that comes with the DataTable will only give the the CLR type that your columns map to, it won't return you the underlying SQL data type.
0
 

Assisted Solution

by:BlueKarana
BlueKarana earned 150 total points
ID: 35127615
This is the SQL for querying metadata (data about the data) in SQL Server:

SELECT     TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, 
                      CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, 
                      CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, 
                      DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
FROM         INFORMATION_SCHEMA.COLUMNS
WHERE     (TABLE_NAME = '[your table name]')

Open in new window


You can see that one of these is DATA_TYPE, which is what you're after.
0
 
LVL 7

Assisted Solution

by:mkobrin
mkobrin earned 150 total points
ID: 35127629
if you have named your data reader "reader" then this will work for you:

                                    int counter = reader.FieldCount;
                                    for (int k= 0; k< counter; k++)
                                    {
                                        string type = reader[ki].GetType().ToString();
                                    }
0
 

Author Comment

by:Mr_Shaw
ID: 35128127
GetType() returns System.Data.DataColumn
0
 

Author Closing Comment

by:Mr_Shaw
ID: 35128278
thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question