Is there a way to get the column names from a SQL statement?

Question

I'm using Dapper and I want to get all the column names from a SQL statement that is provided at runtime. It should not matter if the resulting relation, returned from the database, is empty or not.

public async Task<List<string>> GetColumnsFromSQLStatement(string sqlStatement)
{
    List<string> Columns = new List<string>();

    using (var con = SourceDatabaseConnectionFactory.GetConnection())
    {
        using (var dbTrans = SourceTransactionFactory.CreateTransaction(con, TransactionOptions.ReadOnly))
        {
            DynamicParameters para = new DynamicParameters();

            var tmp = con.QueryAsync(sqlStatement, para, dbTrans,  100,  CommandType.Text);
            /*build the column string list?*/

            dbTrans.Commit();
        }
    }

    return Columns;
}

I expect that I can provide a string list with the column names.

Note: passing in just a `string` (without args) here makes me worry that you're exposing yourself to SQL injection risks. — Marc Gravell, Aug 16 '19 at 08:49

score 0 · Answer 1 · answered Aug 16 '19 at 08:49

0

When using the non-generic Query[Async] API, each row can be cast to IDictionary<string, object> which provides access to the names. Alternatively, use the API that returns a data-reader.

answered Aug 16 '19 at 08:49

Marc Gravell

1,026,079
266
2,566
2,900

doesn't work with my firebird connection: var tmp = con.ExecuteReader(sqlStatement, para, dbTrans, 100, CommandType.Text); var tmp2 = tmp.GetSchemaTable().Columns; foreach (DataColumn dc in tmp2) { Columns.Add(dc.ColumnName); } – RHofmann Aug 16 '19 at 09:16
Do you have maybe a example? – RHofmann Aug 16 '19 at 09:17
@RHofmann Data reader has a direct API to get column names - something like GetName(int) (I'm not at a PC to check) – Marc Gravell Aug 16 '19 at 10:17

score 0 · Answer 2 · answered Aug 16 '19 at 09:40

I found the solution to get the column names without having any record in the resulting relation from the sql statment:

 public async Task<List<string>> GetColumnsFromSQLStatement(string sqlStatement)
    {
        List<string> Columns = new List<string>();

        using (var con = SourceDatabaseConnectionFactory.GetConnection())
        {
            using (var dbTrans = SourceTransactionFactory.CreateTransaction(con, TransactionOptions.ReadOnly))
            {
                DynamicParameters para = new DynamicParameters();

                var tmp = con.ExecuteReader(sqlStatement, para, dbTrans, 100, CommandType.Text);
                var schema = tmp.GetSchemaTable();
                for (var i = 0; i < schema.Rows.Count; i++)
                {
                    Columns.Add(schema.Rows[i]["columnName"].ToString());
                }
                dbTrans.Commit();
            }
        }

        return Columns;
    }

So you it is possible to build complex statements with e.g. stored produceders and functions.

Is there a way to get the column names from a SQL statement?

2 Answers2