使用Delphi中的ADO连接连接到SQL压缩文件（.sdf） [英] Connecting to a SQL Compact file (.sdf) using an ADO connection in Delphi

问题描述

如果主数据库不可达，我正在尝试使用本地.sdf文件作为临时存储的一种手段。我有.sdf文件，但是当我尝试将其设置为文件时，似乎完全不知道.sdf是否存在。目前的连接字符串目前是：

I'm attempting to use a local .sdf file as a means of temporary storage should the main database be unreachable. I have the .sdf file, but when I try to set it to the file it seems to not at all know if the .sdf exists. The current connection string I have currently is:

 Driver={SQL Native Client};Data Source=C::\users\username\desktop\file\MyData.sdf;Persist Security Info=False

和对于为我生成的提供者：

and for the Provider it generated for me:

 Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5

当我尝试使用连接时，我得到一个提供者找不到，可能没有正确的安装。 .sdf绝对是在文件夹中。我还有/有一个问题，它想要一个用户名和/或密码，我不必在创建数据库时指定。

When I try to use the connection, I get a "Provider cannot be found. It may not be properly installed." The .sdf is most definitely in the folder. I also had/have a problem with it wanting a username and/or password, neither of which I had to specify when creating the database.

问题：有什么东西我的连接字符串错了？使用ADO连接访问SQL Compact Databases是否合理？可能有一个更简单的方法来查询/从临时存储中检索数据（我宁愿使用SQL来做）？
大多数文档似乎是2003/2005年，这是无益的。

The Question: Is there something wrong with my connection string? Is it reasonable to use ADO connections to access SQL Compact Databases? Might there be an easier way to query/retrieve data from a temporary storage (I would prefer doing it with SQL though)? Most documentation seems to be from 2003/2005, which is unhelpful.

我使用connectionstrings.com来帮助制作字符串。任何建议都有帮助，谢谢

I used "connectionstrings.com" for help making the string. Any advice would be helpful, thanks

推荐答案

首先打开sdf文件，您必须使用与sdf版本兼容的提供程序文件。因为您在评论中提到3.5版本，您必须使用此提供程序 Microsoft.SQLSERVER.CE.OLEDB.3.5

First to open the sdf file you must use a provider compatible with the version of the sdf file. since you mention in your comments the version 3.5 you must use this provider Microsoft.SQLSERVER.CE.OLEDB.3.5

然后，您必须确保提供商安装哪个

Then you must ensure which the provider is installed

尝试此代码列出系统中安装的OLEDB提供程序

Try this code to list the OLEDB providers installed in your system

{$APPTYPE CONSOLE}

{$R *.res}

uses
  Windows,
  Registry,
  Classes,
  SysUtils;

procedure ListOLEDBProviders;
var
  LRegistry: TRegistry;
  LIndex: Integer;
  SubKeys,Values: TStrings;
  CurKey, CurSubKey: string;
begin
  LRegistry := TRegistry.Create;
  try
    LRegistry.RootKey := HKEY_CLASSES_ROOT;
    if LRegistry.OpenKeyReadOnly('CLSID') then
    begin
      SubKeys := TStringList.Create;
      try
        LRegistry.GetKeyNames(SubKeys);
        LRegistry.CloseKey;
        for LIndex := 0 to SubKeys.Count - 1 do
        begin
          CurKey := 'CLSID\' + SubKeys[LIndex];
          if LRegistry.KeyExists(CurKey)  then
          begin
            if LRegistry.OpenKeyReadOnly(CurKey) then
            begin
              Values:=TStringList.Create;
              try
                LRegistry.GetValueNames(Values);
                LRegistry.CloseKey;
                for CurSubKey in Values do
                 if SameText(CurSubKey, 'OLEDB_SERVICES') then
                   if LRegistry.OpenKeyReadOnly(CurKey+'\ProgID') then
                   begin
                     Writeln(LRegistry.ReadString(''));
                     LRegistry.CloseKey;
                     if LRegistry.OpenKeyReadOnly(CurKey+'\OLE DB Provider') then
                     begin
                      Writeln('    '+LRegistry.ReadString(''));
                      LRegistry.CloseKey;
                     end;
                   end;
              finally
                Values.Free;
              end;
            end;
          end;
        end;
      finally
        SubKeys.Free;
      end;
      LRegistry.CloseKey;
    end;
  finally
    LRegistry.Free;
  end;
end;


begin
 try
    ListOLEDBProviders;
 except
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.

现在这是连接到Sql Server压缩文件的基本示例。

Now this is a basic sample to connect to a Sql Server compact file.

{$APPTYPE CONSOLE}

{$R *.res}

uses
  ActiveX,
  ComObj,
  AdoDb,
  SysUtils;

procedure Test;
Var
  AdoQuery : TADOQuery;
begin
  AdoQuery:=TADOQuery.Create(nil);
  try
    AdoQuery.ConnectionString:='Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Datos\Northwind.sdf';
    AdoQuery.SQL.Text:='Select * from Customers';
    AdoQuery.Open;
    While not AdoQuery.eof do
    begin
      Writeln(Format('%s %s',[AdoQuery.FieldByName('Customer ID').AsString,AdoQuery.FieldByName('Company Name').AsString]));
      AdoQuery.Next;
    end;
  finally
    AdoQuery.Free;
  end;
end;

begin
 try
    CoInitialize(nil);
    try
      Test;
    finally
      CoUninitialize;
    end;
 except
    on E:EOleException do
        Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.

这篇关于使用Delphi中的ADO连接连接到SQL压缩文件（.sdf）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！