Excel Interop COM Object with OleDbDataAdapter

[ksedran]

Hi Experts,

I am having an issue with an Excel COM Object not being released.
I have narrowed it down to a function with an OleDbDataAdapter.


When I run this way:
//Open spreadsheet
//do work
//Release COM Objects
//Close spreadsheet
All the COM object are released and the EXCEL.exe process closes in the task
manager.

But if I run this way:
//Open spreadsheet
DataTable table = GetSheetData(fileName, "Sheet1");
//do work
//Release COM Objects
//Close spreadsheet
There is COM Object not released and the EXCEL.exe process doesn't close in
the task manager.
I have spent the day making sure all the other code in the app is releasing
the COM Objects. As soon as I add the DataTable table =
GetSheetData(fileName, "Sheet1"); the EXCEL.exe process doesn't close. I have
all the code commented out that would use that table so there is no reference
to it in the code. So it must be with the OleDbDataAdapter.

Any advice????
Thanks in advance.

private DataTable GetSheetData(string fileName, string agencyName)
{
using (OleDbConnection connection =
CreateExcelConnection(fileName))
{
OleDbDataAdapter adapter = new
OleDbDataAdapter(string.Format("SELECT * FROM [" + agencyName + "$]"),
connection);
DataSet data = new DataSet();
adapter.Fill(data);
connection.Close();
adapter.Dispose();
return data.Tables[0];
}
}

private OleDbConnection CreateExcelConnection(string fileName)
{
StringBuilder connectionStringBuilder = new
StringBuilder(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0}", fileName));
const string EXCEL_EXTENDED_CONNECTION_PROPERTIES = @";Extended
Properties=""Excel 8.0;HDR=Yes""";

connectionStringBuilder.Append(EXCEL_EXTENDED_CONNECTION_PROPERTIES);
return new OleDbConnection(connectionStringBuilder.ToString());
}


--

 

[ksedran]

I made a small app to re-create the problem.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Runtime.InteropServices;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace ExcelTest
{
public partial class Form1 : Form
{

public Form1()
{
InitializeComponent();
}

private void button1_Click(object sender, EventArgs e)
{
string masterSpreadsheet = "C:\\Data\\TestSheet.xls";

Microsoft.Office.Interop.Excel.Application masterExcelApp = new
Microsoft.Office.Interop.Excel.ApplicationClass();
masterExcelApp.DisplayAlerts = false;
masterExcelApp.Visible = true;
masterExcelApp.EnableEvents = false;
Microsoft.Office.Interop.Excel.Workbook masterWorkbook =
masterExcelApp.Workbooks.Open(masterSpreadsheet, false, false, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet masterws =
(Microsoft.Office.Interop.Excel.Worksheet)masterExcelApp.ActiveSheet;

DataSet data = new DataSet();
System.Data.DataTable table = GetSheetData(masterSpreadsheet,
"Sheet1", data);

masterWorkbook.Save();
masterWorkbook.Close(true, System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
masterExcelApp.Workbooks.Close();
masterExcelApp.Quit();
Marshal.ReleaseComObject(masterws);
Marshal.ReleaseComObject(masterWorkbook);
Marshal.ReleaseComObject(masterExcelApp);

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}

private System.Data.DataTable GetSheetData(string fileName, string
agencyName, DataSet data)
{
OleDbConnection connection = new OleDbConnection();

using (connection = CreateExcelConnection(fileName))
{
OleDbDataAdapter adapter = new
OleDbDataAdapter(string.Format("SELECT * FROM [" + agencyName + "$]"),
connection);
adapter.Fill(data);
adapter.SelectCommand.Dispose();
adapter.Dispose();
adapter = null;
connection.ConnectionString = null;
connection.Close();
connection.Dispose();
connection = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
return data.Tables[0];
}
}

private OleDbConnection CreateExcelConnection(string fileName)
{
StringBuilder connectionStringBuilder = new
StringBuilder(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0}", fileName));
const string EXCEL_EXTENDED_CONNECTION_PROPERTIES = @";Extended
Properties=""Excel 8.0;HDR=Yes""";

connectionStringBuilder.Append(EXCEL_EXTENDED_CONNECTION_PROPERTIES);
return new OleDbConnection(connectionStringBuilder.ToString());
}
}
}

When the System.Data.DataTable table = GetSheetData(masterSpreadsheet, "AP
South", data); statement is commented out excel closes. Leave it in and excel
is left open. Open the task manager and you'll see the excel.exe process
doesn't close.



--
Kevin

Hi Experts,

I am having an issue with an Excel COM Object not being released.
I have narrowed it down to a function with an OleDbDataAdapter.


When I run this way:
//Open spreadsheet
//do work
//Release COM Objects
//Close spreadsheet
All the COM object are released and the EXCEL.exe process closes in the task
manager.

But if I run this way:
//Open spreadsheet
DataTable table = GetSheetData(fileName, "Sheet1");
//do work
//Release COM Objects
//Close spreadsheet
There is COM Object not released and the EXCEL.exe process doesn't close in
the task manager.
I have spent the day making sure all the other code in the app is releasing
the COM Objects. As soon as I add the DataTable table =
GetSheetData(fileName, "Sheet1"); the EXCEL.exe process doesn't close. I have
all the code commented out that would use that table so there is no reference
to it in the code. So it must be with the OleDbDataAdapter.

Any advice????
Thanks in advance.

private DataTable GetSheetData(string fileName, string agencyName)
{
using (OleDbConnection connection =
CreateExcelConnection(fileName))
{
OleDbDataAdapter adapter = new
OleDbDataAdapter(string.Format("SELECT * FROM [" + agencyName + "$]"),
connection);
DataSet data = new DataSet();
adapter.Fill(data);
connection.Close();
adapter.Dispose();
return data.Tables[0];
}
}

private OleDbConnection CreateExcelConnection(string fileName)
{
StringBuilder connectionStringBuilder = new
StringBuilder(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source={0}", fileName));
const string EXCEL_EXTENDED_CONNECTION_PROPERTIES = @";Extended
Properties=""Excel 8.0;HDR=Yes""";

connectionStringBuilder.Append(EXCEL_EXTENDED_CONNECTION_PROPERTIES);
return new OleDbConnection(connectionStringBuilder.ToString());
}