locked
How to get column names of a result set RRS feed

  • Question

  •  

    How do I get the column names of result set returned by "Select * FROM t1" ? The sql statement is passed dynamically and I need know the column names within this kind of select statement.

      

    Thanks,

    Ricky.

    Tuesday, May 15, 2007 9:49 AM

Answers

  •  

    One way to do it is using GetSchemaTable() function. The other way to do it is using FillSchema() method which requires a DataAdapter object. Read this very useful article too: C# - http://support.microsoft.com/default.aspx/kb/310107, Visual Basic - http://support.microsoft.com/kb/310108.

     

     

    Code Snippet - Retrieve column schema using GetSchemaTable() function
    private DataTable GetSchemaSample()
    {
        string connectionString = "Data Source=(local); Integrated Security=SSPI; Initial Catalog=Northwind;";
        string selectCommandText = "SELECT * FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(selectCommandText, connection))
            {
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                     return reader.GetSchemaTable();
                }
            }
        }
    }

     

     

     

    Code Snippet - Retrieve column schema using FillSchema() function
    private DataTable GetSchemaSample()
    {
        string connectionString = "Data Source=(local); Integrated Security=SSPI; Initial Catalog=Northwind;";
        string selectCommandText = "SELECT * FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(selectCommandText, connection))
            {
                connection.Open();

                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataTable schemaTable = new DataTable("SchemaInformation");
                    adapter.FillSchema(schemaTable, SchemaType.Source);
                    return schemaTable;
                }
            }
        }
    }

     

    Tuesday, May 15, 2007 10:43 AM

All replies

  •  

    One way to do it is using GetSchemaTable() function. The other way to do it is using FillSchema() method which requires a DataAdapter object. Read this very useful article too: C# - http://support.microsoft.com/default.aspx/kb/310107, Visual Basic - http://support.microsoft.com/kb/310108.

     

     

    Code Snippet - Retrieve column schema using GetSchemaTable() function
    private DataTable GetSchemaSample()
    {
        string connectionString = "Data Source=(local); Integrated Security=SSPI; Initial Catalog=Northwind;";
        string selectCommandText = "SELECT * FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(selectCommandText, connection))
            {
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
                {
                     return reader.GetSchemaTable();
                }
            }
        }
    }

     

     

     

    Code Snippet - Retrieve column schema using FillSchema() function
    private DataTable GetSchemaSample()
    {
        string connectionString = "Data Source=(local); Integrated Security=SSPI; Initial Catalog=Northwind;";
        string selectCommandText = "SELECT * FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(selectCommandText, connection))
            {
                connection.Open();

                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataTable schemaTable = new DataTable("SchemaInformation");
                    adapter.FillSchema(schemaTable, SchemaType.Source);
                    return schemaTable;
                }
            }
        }
    }

     

    Tuesday, May 15, 2007 10:43 AM
  • Thanks,  Cristian_t.
    Wednesday, May 16, 2007 1:47 AM