Connecting to a SQL server using ADODB from a C# dll

Question

I am writing a custom Connection class in C# for Excel to be able to connect to a SQL Server. When I use SQLConnection from System.Data.SqlClient library I am able to establish a connection. The working code I've got:

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;
            }
        }
    }
}

And after adding the reference to Excel I am able to test the connection using a simple VBA code like this:

Sub TestConnection()

    Dim conn As Test.Connection
    Set conn = New Test.Connection

    Debug.Print conn.Status
    conn.Open
    Debug.Print conn.Status

End Sub

It outputs:

False
True

So everything is fine. Now I would like to create custom Recordset class in my C# library so I decided to use an ADODB library and its RecordSetinstead of SqlDataReader as I am planning to work with some big chunks of data. So, I have modified my code to this:

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;
            }
        }

    }
}

I also have added references to Microsoft ActiveX Data Objects 6.1 Library.

Now, when I am executing the VBA code it outputs:

0
0

But I was expecting 0 and 1. It seems to me like I am not properly connecting to the server ( credentials are the same i have just removed actual data from this code ).

I have tried to use different variations of the connection string, however it always returns 0 and 0. 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.

I have used link1, link2, link3, link4 for reference

Update:
I have wrote the exception to the file as TheKingDave suggested. This is the exception error message

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

Answer 1

The connection string is missing Provider=SQLOLEDB.

The ADODB.Connection needs to know what type of database it is connecting to.