Singleton pattern and server connection using excel-DNA

Question

I have an SQL database.

Then in one class I have an ExcelFunction:

[ExcelFunction(Description = "fonction de recherche")]
public static double id(string _isin)
{
   double res;
   res =DBSQL.Instance.getID(_isin);
   return res;
}

Then in anoher class I have my connection and the creation of the singleton pattern (in order to be safe in case of multi-threading). The idea might not be clear, just ask me and I will try to explain. The point is to open a connection (using the singleton pattern), then do the request and then delete the singleton to close the connection.

Here is my code :

public class DBSQL : iAccesDB1
{

    private SqlConnection MaConn = new SqlConnection("sefhgoefhouzeyf");

    private static volatile DBSQL instance;

    private static object syncRoot = new Object();

    private DBSQL() {}

    public static DBSQL Instance
    {   
        get 
        {
           if (instance == null)   
           {
               lock (syncRoot)
               {
                    if (instance == null)
                        instance = new DBSQL();
                }
            }
            return instance;
        }
    }

    public void Connection()    
    {
        MaConn.Open();
    }

    public void CloseConnection()
    {
        MaConn.Close();
    }

    public double getID(String _isin)
    {
        SqlDataReader rd;

        double res = -9999;

        SqlCommand cd = new SqlCommand("select cpn from tD where isin='" + _isin + "'", MaConn);
        try
        {
            rd = cd.ExecuteReader();
            if (rd.HasRows)
            {
                while (rd.Read())
                    res =double.Parse(rd["cpn"].ToString());
            }
        }
        catch (Exception ex)
        {
            throw new Exception("1000: " + ex.Message);
        }
        return res;
    }
}

The problem is that it does not work - in my excel cell I have the following: VALUE?

score 2 · Answer 1 · answered Jul 11 '14 at 10:30

When your Excel-DNA function returns #VALUE to Excel, it probably means there was an unhandled exception.

I suggest you change your top-level function to return an 'object' which returns an error string if there is an exception, like this:

[ExcelFunction(Description = "fonction de recherche")]
public static object id(string _isin)
{ 
    try 
    {
        double res;
        res = DBSQL.Instance.getID(_isin);
        return res; 
    }
    catch (Exception ex)
    {
        return "!!! ERROR: " + ex.ToString();
    }
}

Singleton pattern and server connection using excel-DNA

1 Answers1