Connections when reading excel into dataset

[tshad]

If I have a connection:

OleDbConnection xlsConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
AppSettings.InputFilePath + ";Extended Properties=\"Excel
8.0;HDR=Yes;IMEX=1\"");

And I open many files in a loop:

foreach (string strFile in strFiles)
{
da = new OleDbDataAdapter("SELECT * FROM Query1",
xlsConnection);
da.Fill(ds);

.... Some code
}

Do I need to close my connect for each file or does the Adapter do it at the
da.Fill?

Thanks,

Tom

 

[tshad]

Actually, I just realized this won't work anyway since I am not adding the
filename in.

This is how I would do it with a csv file.

I would do a similar connection string:

OleDbConnection csvConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
AppSettings.InputFilePath + ";Extended
Properties=\"Text;HDR=No;FMT=Delimited\"");

and then loop through the files as below:

foreach (string strFile in strFiles)
{
da = new OleDbDataAdapter("SELECT * FROM " + strFile,
csvConnection);
da.Fill(ds);

.... Some code
}

Same question - do I have to close the Connection after each da.Fill().

2nd question that just came up:

In an excel sheet it appears I have to put the file name into the Connection
string explicitly. So instead of setting up the connection outside of my
loop where I only enter the Path into the Datasource and just keep chaning
my Select statement by adding in a new filename, I would have to do it like
so:

OleDbConnection xlsConnection;

foreach (string strFile in strFiles)
{
xlsConnection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
AppSettings.InputFilePath + strFiles + ";Extended
Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"");
da = new OleDbDataAdapter("SELECT * FROM Query1",
csvConnection);
da.Fill(ds);

.... Some code
}

Is there a way to use the same connection for xls like I do for csv?

Thanks,

Tom