none
Identifying every field type in a table programmably RRS feed

  • Question

  • I want to retrieve every field type in a table in a program.

    We tried this by select top 0 * from Table with ADO recordset and found that many types can be identified by reading Fields[].Type value.
    However, some field types show incorrect results, for example, Date type in SQL Server is shown as VarWChar in ADO Fields[].Type value.
    How can I retrieve correct field type list of a table?

    Thanks in advance.
    Wednesday, November 18, 2009 7:05 AM

Answers

  • Hi Hyun,

     

    Welcome to MSDN Forums!

     

    As you said, not every type in SQL Server is mapped to certain ADO date type.  The date type in SQL Server is an example.   For detail, please see http://msdn.microsoft.com/en-us/library/ms714373(VS.85).aspx and http://msdn.microsoft.com/en-us/library/ms675318(VS.85).aspx.  

     

    Are you trying to retrieve the data type at the SQL Server side via ADO?   If so, you can refer to the following codes to get the column type information by SQL Server system tables:

    =====================================================================================
                ADODB.Connection conn = null;

                ADODB.Recordset rs = null;

     

                try

                {

                    ////////////////////////////////////////////////////////////////////////////////

                    // Connect to the data source.

                    //

     

                    Console.WriteLine("Connecting to the database ...");

     

                    // Get the connection string from App.config. (The data source is created in the

                    // sample SQLServer2005DB)

                    string connStr = @"Provider=SQLOLEDB;Data Source=(local);Initial Catalog=TestDB;Trusted_Connection=yes";

     

                    // Open the connection

                    conn = new ADODB.Connection();

                    conn.Open(connStr, null, null, 0);

     

     

                    ////////////////////////////////////////////////////////////////////////////////

                    // Use the Recordset Object.

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

                    // Recordset represents the entire set of records from a base table or the 

                    // results of an executed command. At any time, the Recordset object refers to 

                    // only a single record within the set as the current record.

                    //

     

                    //Console.WriteLine("Enumerating the records in the Person table");

     

                    // 1. Create a Recordset object

                    rs = new ADODB.Recordset();

     

                    // 2. Open the Recordset object

                    string strSelectCmd = "select sys.columns.name as ColumnName, sys.types.name as ColumnTypeName " +

                                            "from sys.columns, sys.tables, sys.types, sys.schemas where " +

                                            "sys.columns.object_id = sys.tables.object_id and " +

                                            "sys.columns.user_type_id=sys.types.user_type_id and " +

                                            "sys.tables.schema_id=sys.schemas.schema_id and " +

                                            "sys.tables.name='TableName' and sys.schemas.name = 'SchemaName'";

                    rs.Open(strSelectCmd,                       // SQL statement / table,view name /

                        // stored procedure call / file name

                        conn,                                   // Connection / connection string

                        ADODB.CursorTypeEnum.adOpenForwardOnly, // Cursor type. (forward-only cursor)

                        ADODB.LockTypeEnum.adLockOptimistic,        // Lock type. (locking records only

                        // when you call the Update method.

                        (int)ADODB.CommandTypeEnum.adCmdText);      // Evaluate the first parameter as

                    // a SQL command or stored procedure.

     

                    // 3. Enumerate the records by moving the cursor forward

                    rs.MoveFirst();  // Move to the first record in the Recordset

                    while (!rs.EOF)

                    {

                        var columnName = rs.Fields["ColumnName"].Value;

                        var columnType = rs.Fields["ColumnTypeName"].Value;

     

                        Console.WriteLine("Column: {0} in type of {1}", columnName, columnType);

     

                        rs.MoveNext();   // Move to the next record

                    }

                }

                catch (Exception ex)

                {

                    Console.WriteLine("The application throws the error: {0}", ex.Message);

                    if (ex.InnerException != null)

                        Console.WriteLine("Description: {0}", ex.InnerException.Message);

                }

                finally

                {

                    ////////////////////////////////////////////////////////////////////////////////

                    // Clean up objects before exit.

                    //

     

                    Console.WriteLine("Closing the connections ...");

     

                    // Close the record set if it is open

                    if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)

                        rs.Close();

     

                    // Close the connection to the database if it is open

                    if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)

                        conn.Close();

                }
    =====================================================================================

     

    The original code sample is from the project CSUseADO of All-In-One Code Framework, which is an open-source project owned by MSDN Community Support Team.  

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, November 18, 2009 9:28 AM
    Moderator

All replies

  • Hi Hyun,

     

    Welcome to MSDN Forums!

     

    As you said, not every type in SQL Server is mapped to certain ADO date type.  The date type in SQL Server is an example.   For detail, please see http://msdn.microsoft.com/en-us/library/ms714373(VS.85).aspx and http://msdn.microsoft.com/en-us/library/ms675318(VS.85).aspx.  

     

    Are you trying to retrieve the data type at the SQL Server side via ADO?   If so, you can refer to the following codes to get the column type information by SQL Server system tables:

    =====================================================================================
                ADODB.Connection conn = null;

                ADODB.Recordset rs = null;

     

                try

                {

                    ////////////////////////////////////////////////////////////////////////////////

                    // Connect to the data source.

                    //

     

                    Console.WriteLine("Connecting to the database ...");

     

                    // Get the connection string from App.config. (The data source is created in the

                    // sample SQLServer2005DB)

                    string connStr = @"Provider=SQLOLEDB;Data Source=(local);Initial Catalog=TestDB;Trusted_Connection=yes";

     

                    // Open the connection

                    conn = new ADODB.Connection();

                    conn.Open(connStr, null, null, 0);

     

     

                    ////////////////////////////////////////////////////////////////////////////////

                    // Use the Recordset Object.

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

                    // Recordset represents the entire set of records from a base table or the 

                    // results of an executed command. At any time, the Recordset object refers to 

                    // only a single record within the set as the current record.

                    //

     

                    //Console.WriteLine("Enumerating the records in the Person table");

     

                    // 1. Create a Recordset object

                    rs = new ADODB.Recordset();

     

                    // 2. Open the Recordset object

                    string strSelectCmd = "select sys.columns.name as ColumnName, sys.types.name as ColumnTypeName " +

                                            "from sys.columns, sys.tables, sys.types, sys.schemas where " +

                                            "sys.columns.object_id = sys.tables.object_id and " +

                                            "sys.columns.user_type_id=sys.types.user_type_id and " +

                                            "sys.tables.schema_id=sys.schemas.schema_id and " +

                                            "sys.tables.name='TableName' and sys.schemas.name = 'SchemaName'";

                    rs.Open(strSelectCmd,                       // SQL statement / table,view name /

                        // stored procedure call / file name

                        conn,                                   // Connection / connection string

                        ADODB.CursorTypeEnum.adOpenForwardOnly, // Cursor type. (forward-only cursor)

                        ADODB.LockTypeEnum.adLockOptimistic,        // Lock type. (locking records only

                        // when you call the Update method.

                        (int)ADODB.CommandTypeEnum.adCmdText);      // Evaluate the first parameter as

                    // a SQL command or stored procedure.

     

                    // 3. Enumerate the records by moving the cursor forward

                    rs.MoveFirst();  // Move to the first record in the Recordset

                    while (!rs.EOF)

                    {

                        var columnName = rs.Fields["ColumnName"].Value;

                        var columnType = rs.Fields["ColumnTypeName"].Value;

     

                        Console.WriteLine("Column: {0} in type of {1}", columnName, columnType);

     

                        rs.MoveNext();   // Move to the next record

                    }

                }

                catch (Exception ex)

                {

                    Console.WriteLine("The application throws the error: {0}", ex.Message);

                    if (ex.InnerException != null)

                        Console.WriteLine("Description: {0}", ex.InnerException.Message);

                }

                finally

                {

                    ////////////////////////////////////////////////////////////////////////////////

                    // Clean up objects before exit.

                    //

     

                    Console.WriteLine("Closing the connections ...");

     

                    // Close the record set if it is open

                    if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)

                        rs.Close();

     

                    // Close the connection to the database if it is open

                    if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)

                        conn.Close();

                }
    =====================================================================================

     

    The original code sample is from the project CSUseADO of All-In-One Code Framework, which is an open-source project owned by MSDN Community Support Team.  

     

    If you have any questions, please feel free to let me know.

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, November 18, 2009 9:28 AM
    Moderator
  • Hi Hyun,


    Do you need any further assistance?  If so, please feel free to let me know. 

     

    Have a great day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, November 23, 2009 12:39 AM
    Moderator