Select columns with no specific name

Question

Select columns with no specific name

Navigation

#1 by (4 votes)

2

I'm developing a C # application that consumes data from a spreadsheet, and at some point I need a query string that will feed a variable.

For example:

strComando1 = "SELECT TOP 2 ColunaGenerica1, ColunaGenerica2 FROM ["+ planilha +"]";

I wanted to know if it is possible and how can I do to consume data from the first two columns, for example, without specifying their names, since the column names may vary according to the user import. >

Provider and connection:

    private static OleDbConnection conexao = new OleDbConnection();

    strConexao = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=0\"", caminhoArquivo);
    conexao.ConnectionString = strConexao;

    private static DataTable tabelaDeSaida;

sql c# excel oledb

asked by anonymous 09.02.2018 / 13:21

1 answer

Is it possible to query the "INSERT INTO table SET field = 'value'" in PostgreSQL? Is it possible to find darker / lighter tones from a hexadecimal?

score 4 · Accepted Answer

You can use the command:

DataTable dtCols = conexao.GetSchema("Columns");

or

DataTable dtCols = conexao.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] {null, null, "NomeDaTabela", null});

It will return a DataTable , which has the following columns (among others):

TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION

From there, just grab the column you want, from the table you want and mount the query.

Example:

conexao.Open();

DataTable dtCols = conexao.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, "tabela$", null });

DataRow[] rs = dtCols.Select("ORDINAL_POSITION <= 2");

string query = "Select ";
foreach (DataRow r in rs)
{
    query+= r["COLUMN_NAME"].ToString()+ ",";
}

query = query.Remove(query.Length-1);

query += " From tabela$ where 1=1;";

MessageBox.Show(query);

Result:

Select coluna1,coluna2 From tabela$ where 1=1;