vba 从 C# dll 使用 ADODB 连接到 SQL 服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17573544/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connecting to a SQL server using ADODB from a C# dll
提问by
I am writing a custom Connectionclass in C# for Excel to be able to connect to a SQL Server.
When I use SQLConnectionfrom System.Data.SqlClientlibrary I am able to establish a connection. The working code I've got:
我正在Connection用 C# 为 Excel编写一个自定义类，以便能够连接到 SQL Server。当我SQLConnection从System.Data.SqlClient图书馆使用时，我能够建立连接。我得到的工作代码：
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:
添加对 Excel 的引用后，我可以使用如下简单的 VBA 代码测试连接：
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 Recordsetclass in my C# library so I decided to use an ADODBlibrary and its RecordSetinstead of SqlDataReaderas I am planning to work with some big chunks of data. So, I have modified my code to this:
所以一切都很好。现在我想Recordset在我的 C# 库中创建自定义类，所以我决定使用一个ADODB库和它RecordSet而不是SqlDataReader因为我计划使用一些大数据块。因此，我已将代码修改为：
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.
我还添加了对Microsoft ActiveX Data Objects 6.1 Library.
Now, when I am executing the VBA code it outputs:
现在，当我执行 VBA 代码时，它会输出：
0
0
0
0
But I was expecting 0and 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 ).
但我期待0和1。在我看来，我没有正确连接到服务器（凭据与我刚刚从该代码中删除实际数据的凭据相同）。
I have tried to use different variations of the connection string, however it always returns 0and 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.
我尝试使用连接字符串的不同变体，但它总是返回0和0。我尝试使用新的 GUID 创建一个新项目，并尝试重命名项目、类等，但没有任何效果。我怀疑它建立了连接，但我不确定如何调试 dll。
I have used link1, link2, link3, link4for reference
我使用了link1, link2, link3, link4作为参考
Update:
I have wrote the exception to the file as TheKingDave suggested. This is the exception error message
更新：
我已按照 TheKingDave 的建议将例外写入文件。这是异常错误信息
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
System.Runtime.InteropServices.COMException (0x80004005): [Microsoft][ODBC 驱动程序管理器] 未找到数据源名称且未在 TestADODB 的 ADODB._Connection.Open(String ConnectionString, String UserID, String Password, Int32 Options) 中指定默认驱动程序.Connection.Open() 在 c:\Users\administrator\Documents\Visual Studio 2012\Projects\Test\Test\Connection.cs:line 49
采纳答案by Brad Rem
The connection string is missing Provider=SQLOLEDB.
缺少连接字符串Provider=SQLOLEDB。
The ADODB.Connection needs to know what type of database it is connecting to.
ADODB.Connection 需要知道它连接到什么类型的数据库。
