title: Connecting to a SQL server using ADODB from a C# dll
body: <p>I am writing a custom <code>Connection</code> class in C# for Excel to be able to connect to a SQL Server. 
  When I use <code>SQLConnection</code> from <code>System.Data.SqlClient</code> library I am able to establish a connection. The working code I've got:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  using System.Data.SqlClient;
  using System.Runtime.InteropServices;
  
  namespace Test
  {
  
      [InterfaceType(ComInterfaceType.InterfaceIsDual),
      Guid("6E8B9F68-FB6C-422F-9619-3BA6D5C24E84")]
      public interface IConnection
      {
          bool Status { get; }
          bool Open();
      }
  
      [ClassInterface(ClassInterfaceType.None)]
      [Guid("B280EAA4-CE11-43AD-BACD-723783BB3CF2")]
      [ProgId("Test.Connection")]
      public class Connection : IConnection
      {
          private bool status;
          private SqlConnection conn;
          private string connString = "Data Source=[server]; Initial Catalog=[initial]; User ID=[username]; Password=[password]";
  
          public Connection()
          {
          }
  
          public bool Status
          {
              get
              {
                  return status;
              }
          }
  
          public bool Open()
          {
              try
              {
                  conn = new SqlConnection(connString);
                  conn.Open();
                  status = true;
                  return true;
              }
              catch(Exception e)
              {
                  e.ToString();
                  return false;
              }
          }
      }
  }
  </code></pre>
  
  <p>And after adding the reference to Excel I am able to test the connection using a simple VBA code like this:</p>
  
  <pre><code>Sub TestConnection()
  
      Dim conn As Test.Connection
      Set conn = New Test.Connection
  
      Debug.Print conn.Status
      conn.Open
      Debug.Print conn.Status
  
  End Sub
  </code></pre>
  
  <p>It outputs:</p>
  
  <blockquote>
    <p>False <br>
    True <br></p>
  </blockquote>
  
  <p>So everything is fine. Now I would like to create custom <code>Recordset</code> class in my C# library so I decided to use an <code>ADODB</code> library and its <code>RecordSet</code>instead of <code>SqlDataReader</code> as I am planning to work with some big chunks of data. So, I have modified my code to this:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  using System.Data.SqlClient;
  using System.Runtime.InteropServices;
  
  namespace Test
  {
  
      [InterfaceType(ComInterfaceType.InterfaceIsDual),
      Guid("6E8B9F68-FB6C-422F-9619-3BA6D5C24E84")]
      public interface IConnection
      {
          bool Status { get; }
          bool Open();
      }
  
      [ClassInterface(ClassInterfaceType.None)]
      [Guid("B280EAA4-CE11-43AD-BACD-723783BB3CF2")]
      [ProgId("Test.Connection")]
      public class Connection : IConnection
      {
  
          private bool status;
          private ADODB.Connection conn = new ADODB.Connection();
          private string connString = "Data Source=[server]; Initial Catalog=[initial]; User ID=[username]; Password=[password]";
  
          public Connection()
          {
          }
  
          public bool Status
          {
              get
              {
                  return status;
              }
          }
  
          public bool Open()
          {
              try
              {
                  conn.ConnectionString = connString;
                  conn.Open();
                  // conn.Open(connString, ["username"], ["password"], 0)
                  // what else can I try? is this where it actually fails?
                  status = true;
                  return true;
              }
              catch (Exception e)
              {
                  e.ToString();
                  return false;
              }
          }
  
      }
  }
  </code></pre>
  
  <p>I also have added references to <code>Microsoft ActiveX Data Objects 6.1 Library</code>.</p>
  
  <p>Now, when I am executing the VBA code it outputs:</p>
  
  <blockquote>
    <p>0 <br>
    0 <br></p>
  </blockquote>
  
  <p>But I was expecting <code>0</code> and <code>1</code>. It seems to me like I am not properly connecting to the server <em>( credentials are the same i have just removed actual data from this code )</em>.
  <br></p>
  
  <p>I have tried to use different variations of the connection string, however it always returns <code>0</code> and <code>0</code>. I have tried creating a new project with new GUIDs and also tried renaming the projects, classes, etc. nothing has worked. I am suspecting its the establishment of the connection but I am unsure how to debug a dll.</p>
  
  <p>I have used <a href="http://support.microsoft.com/kb/308611" rel="nofollow">link1</a>, <a href="http://msdn.microsoft.com/en-us/library/ms807027.aspx" rel="nofollow">link2</a>, <a href="http://msdn.microsoft.com/en-us/library/ms130978.aspx" rel="nofollow">link3</a>, <a href="http://homeofcox-cs.blogspot.co.uk/2009/07/use-adodbrecordset-in-c.html" rel="nofollow">link4</a> for reference<br></p>
  
  <p><strong>Update:</strong><br>
  I have wrote the exception to the file as TheKingDave suggested. This is the exception error message</p>
  
  <blockquote>
    <p>System.Runtime.InteropServices.COMException (0x80004005):
    [Microsoft][ODBC Driver Manager] Data source name not found and no
    default driver specified    at ADODB._Connection.Open(String
    ConnectionString, String UserID, String Password, Int32 Options)    at
    TestADODB.Connection.Open() in c:\Users\administrator\Documents\Visual
    Studio 2012\Projects\Test\Test\Connection.cs:line 49</p>
  </blockquote>