Accessing an excel resource in another c# project

Question

Hi,

I am using c# (visual studio 2008) to try and access business logic in an excel spreadsheet.

I have the following class.. (feel free to criticise if I am doing this wrong - I am java developer normally - this is my first c# application.)

public class SpreadSheetClass
{
    // apologies for any typo's code written in place, not copied from an IDE...
    //
    public DataTable DoIt()
    {
        DataTable result;
        String sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MySpreadsheet.xls;Extended Properties=Excel 8.0;";

        OleDbConnection connection = new OleDbConnection(sConnection);
        try
        {
            connection.Open();
            OleDbCommand oleDbcCommand = new OleDbCommand("SELECT * FROM SELECTION", connection); 
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
            dataAdapter.SelectCommand = oleDbcCommand;    
            result = new DataTable();
            dataAdapter.Fill(result);
        }
        finally
        {
            connection.Close();
        }
        return result;
    }
}

I added the spreadsheet to the project using Add -> Existing Item -> then selecting the .xls file. I then edited the properties of the file to say it was an embedded resource, and to copy it always to the output directory.

I can access the spreadsheet fine from within the project.

However, I want to run the DoIt method from outside of the project, eg from a Test Project, I get the following exception:

"Test method TestProject1.UnitTest1.TestMethod1 threw exception: System.Data.OleDb.OleDbException: The Microsoft Jet database engine could not find the object 'SELECTION'. Make sure the object exists and that you spell its name and the path name correctly.."

I am sure that my problem is to do with calling a class that accesses a resource from another project.

Help!

Answer 1

In .NET (specifically in C#) it's better to "wrap" classes which implement IDisposable in using statements:

using(OleDbConnection connection = new OleDbConnection(sConnection))
{
    // ...
}

What you basically have to do is to extract the resource from an assembly (see Assembly.GetManifestResourceStream()), persist it onto disk and then alter the connection string appropriately.

Answer 2

I added the spreadsheet to the project using Add -> Existing Item -> then selecting the .xls file. I then edited the properties of the file to say it was an embedded resource, and to copy it always to the output directory.

I can access the spreadsheet fine from within the project.

You open the other program as an assembly, and then load resources from it.

Something like:

Assembly other = Assembly.Load(name);
Stream xlsData = other.GetManifestResourceStream(resourceName);