Hi,

I am struggling to find a way to read the column names and values from a Data Reader that I populated from an excel file and would appreciate very much is someone could help me.

As you can see, I have produced a very simple excel file. The first row contains the column headers and the other rows contain the values.

Company Audit JARScan
test1 Y
test2 Y

Once I read the excel file into a data reader, I thought I would have access to its header information as well.

I can easily get the values from the cells by doing this:

dr("Audit")

But what about getting the headers and values dynamically?

I just need a way to dynamically create a name / value pair. It could even be a XML file like this:

<Permissions>
<perm type="Company" value="test1"/>
<perm type="Audit" value=""/>
<perm type="JARScan" value"Y"/>
<Permissions>

So the type attribute would contain the name of the column/heading and the value would contain the value of the cell for that row.

I would apprediate very much if could shed some light.

I have used the code below but to no avail. When it comes to read the
column names it does not work

dt.Columns[i].ColumnName;
string cn = dr.GetSchemaTable().Columns[i].ColumnName;
string val = dr.GetSchemaTable().Columns[i].DefaultValue.ToString();

THE CODE
===========


using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;

using (DbCommand command = connection.CreateCommand())
{
// sheet$ comes from the name of the worksheet
string sheet = ddlSheets.SelectedItem.Text;
command.CommandText = "SELECT * FROM [" + sheet + "]";

connection.Open();

using (DbDataReader dr = command.ExecuteReader())
{

DataTable columns;

//columns = connection.GetSchema("Columns");

DataTable dt = dr.GetSchemaTable();
for (int i=0;i<dt.Columns.Count;i++)
{
string cl = dt.Columns[i].ColumnName;
}



while (dr.Read())
{

for (int i = 0; i < dr.GetSchemaTable().Columns.Count; i++)
{
string cn = dr.GetSchemaTable().Columns[i].ColumnName;
string val = dr.GetSchemaTable().Columns[i].DefaultValue.ToString();


}

}
}
}
}
}


Cheers

C