SQL Server - how to get data type from table schema through c# code?

Asked By Michael Parker on 03-Jan-07 01:00 PM

Hi --

I have a TableSchema object in my C# code which is pointing to  a table in sqlserver 2005.

I can access the Columns Property of the schema object, and get the name of the column, but now I want to get the data type of the object. (drilled down to the point so that if I get a varchar, i can know that it is a varchar (250) vs varchar(35)....

I am using the SourceTable. Column.DataType, but that is giving me C# datatypes, and I would like to have the SqlServer datatypes.

Okay, an update is that SourceTable.Columns[i].NativeType gives me the native type. Now I just need to find the drilled down part, where I can know what the lenth is of the datatype where that is applicable.




Thank you.

    if (i != SourceTable.Columns.Count - 1)
    {
             strOutput += @"@" + SourceTable.Columns[i].Name + " ";
                strOutput += SourceTable.Columns[i].DataType + "(";

Get Column data type and name from DataColumn - K Pravin Kumar Reddy replied to Michael Parker on 03-Jan-07 01:14 PM

Get Column data type and name from DataColumn

using System;
using System.Data;
using System.Data.OleDb;

public class DatabaseInfo {    
 public static void Main () { 
   String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.\\Employee.mdb";
   OleDbConnection con = new OleDbConnection(connect);
   con.Open();  
   Console.WriteLine("Made the connection to the database");

   String cmd = "SELECT * FROM Employee"
   OleDbDataAdapter adapter = new OleDbDataAdapter();
   adapter.SelectCommand = new OleDbCommand(cmd, con);
   DataSet ds = new DataSet();
   adapter.Fill(ds, "Employee");
   DataTable item = ds.Tables[0];
   Console.WriteLine("Table name: {0}", item.TableName);
   Console.WriteLine("Its columns are:");
   foreach (DataColumn col in item.Columns)
     Console.WriteLine("{0}\t{1}", col.ColumnName, col.DataType);

   con.Close();
 }
}

reference

http://www.java2s.com/Code/CSharp/Database-ADO.net/GetColumndatatypeandnamefromDataColumn.htm

http://www.csharp-home.com/index/tiki-read_article.php?articleId=46

I had tried that, but it doesn't give me the SqlServer DataType - Michael Parker replied to K Pravin Kumar Reddy on 04-Jan-07 09:30 AM

I am able to get the SqlServer DataType by using Columns.NativeType, however, it only gives me the general type (such as nvarchar) and not the specific type, which i am looking for (such as nvarchar(50) ).

how to get data type from table schema through c# code? - Rajeev Kumar replied to Michael Parker on 25-Sep-08 06:10 AM

Thank you.

    if (i != SourceTable.Columns.Count - 1)
    { 
             strOutput += @"@" + SourceTable.Columns[i].Name + " ";
                strOutput += SourceTable.Columns[i].DataType + "(";