Connecting to a SQL server using ADODB from a C# dll
The connection string is missing Provider=SQLOLEDB
.
The ADODB.Connection needs to know what type of database it is connecting to.
Admin
Updated on June 29, 2022Comments
-
Admin almost 2 years
I am writing a custom
Connection
class in C# for Excel to be able to connect to a SQL Server. When I useSQLConnection
fromSystem.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
TrueSo everything is fine. Now I would like to create custom
Recordset
class in my C# library so I decided to use anADODB
library and itsRecordSet
instead ofSqlDataReader
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
0But I was expecting
0
and1
. 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
and0
. 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 messageSystem.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