Solved

I need to capture what SQL datatypes are being returned.

Posted on 2011-03-14
9
261 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

860 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