title: Singleton pattern and server connection using excel-DNA
body: <p>I have an <code>SQL</code> database.</p>
  
  <p>Then in one class I have an <code>ExcelFunction</code>:</p>
  
  <pre><code>[ExcelFunction(Description = "fonction de recherche")]
  public static double id(string _isin)
  {
     double res;
     res =DBSQL.Instance.getID(_isin);
     return res;
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>Here is my code : </p>
  
  <pre><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;
      }
  }
  </code></pre>
  
  <p>The problem is that it does not work - in my excel cell I have the following: VALUE?</p>