无法让 sql server compact 3.5/4 与 ASP .NET MVC 2 一起使用 [英] Can't get sql server compact 3.5 / 4 to work with ASP .NET MVC 2

问题描述

我使用的是 Visual Studio 2008 Pro.

I'm using Visual Studio 2008 Pro.

我可能在这里遗漏了一些非常明显的东西，但我一直在尝试让 Sql Server compact 4 的 CTP 在我的 asp.net mvc 应用程序中工作.我几乎找不到关于如何设置它或工作示例应用程序的说明.我的目标是私人安装，因此我可以将它包含在我的 Web 应用程序中，而无需在我的域托管上进行 sql server 设置.这真的只是我在微风中拍摄并试图解决这个问题.我不打算举办市场或任何与此相关的活动.

I'm probably missing something very obvious here, but I've been trying to get the CTP for Sql Server compact 4 to work in my asp.net mvc application. I can find next to no instruction on how to set this up or a working example application. My goal is a private install so I can just include it in my web app without having to do sql server setup on my domain hosting. This is really just me shooting the breeze and trying to figure this out. I don't plan to host a market or anything with this.

因此，我已将在基本 4.0 方向(c:Program FilesSql Server compactv4.0)中安装的所有 dll 复制到我的应用程序中的 lib 文件夹中.我已将复制到输出方向选项设置为如果较新则复制".然后我引用 System.Data.SqlServerCE dll 并将复制本地"设置为 True.

So, I've copied all the dll's that install in the base 4.0 direction (c:Program FilesSql Server compactv4.0) to a lib folder in my application. I've set the copy to output direction option to 'Copy if Newer'. I then reference the System.Data.SqlServerCE dll and set 'Copy Local' to True.

我通过 Sql Studio Express 创建了一个 sdf 文件.一个重要的注意事项是我没有看到创建此文件的 CE 4.0 版本的选项，因此它是使用 CE 3.5 创建的.我创建了几个表，向这些表中添加了几行，将 *.sdf 文件复制到我的 App_Data 目录中.值得一提的是，在 VS 2008 中，这个文件从未出现在我的项目中，但它确实存在于 App_Data 目录的物理位置中.我不确定这是为什么.

I created an sdf file via Sql Studio Express. An important note is that I did not see an option for creating a CE 4.0 version of this file, so it was created using CE 3.5. I create a few tables, add a few rows to those tables, copy the *.sdf file to my App_Data directory. It's worth mentioning that, from inside VS 2008, this file never appears in my project, but it does exist in the physical location of the App_Data directory. I'm not sure why this is.

接下来，我只是尝试通过以下方式与我的 sdf 文件建立基本连接:

Next, I just try making a basic connection to my sdf file via:

SqlCeConnection conn = new SqlCeConnection("DataSource=rpg.sdf");

这会产生以下错误:

Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8402. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.

我想从这里开始，我只是尝试让 Sql CE 3.5 工作.我将本地安装的 Sql CE 3.5 升级到 sp2.我在基本位置 (c:Program FilesSql Server compactv3.5) 复制 dll，包括从我的项目引用中删除和读取 System.Data.SqlServerCE dll 的版本.

I figure from here, I'd just try getting Sql CE 3.5 to work. I upgrade my local installation of Sql CE 3.5 to sp2. I copy the dlls at the base location (c:Program FilesSql Server compactv3.5), including removing and readding the version of the System.Data.SqlServerCE dll from my project references.

这里奇怪的是，当我右键单击并查看引用的 SqlServerCE dll 的属性时，它总是说它的版本是 4.0.0.1.

The curious thing here is when I right click and look at the properties of the referenced SqlServerCE dll, it always says it's version 4.0.0.1.

伙计们，我真的可以在这里使用一些指导.我搜索了堆栈溢出、帮助文档、在线书籍和谷歌搜索.我真的没有找到任何可以从 CE 3.5 或 4.0 的最高层开始并告诉我要添加什么 dll、将它们放在哪里、如何引用它们、如何将 .sdf 文件添加到我的项目中的任何内容，连接到它，并从中查询.我确实遇到了一些关于应该使用 Sql CE 3.5 的 IBuySpy 门户示例应用程序的提及，但实际上无法导航 msdn 下载迷宫以获取它.理想情况下，我想为 CE 4.0 设置私有部署.

Guys, I really could use some direction here. I have searched stack overflow, the help docs, books online, and googled. I really haven't found anything that takes this from the very top for either CE 3.5 or 4.0 and tells me exactly what dll's to add, where to put them, how to reference them, how to add the .sdf file to my project, connect to it, and query from it. I did come across a few mentions of an IBuySpy portal sample app that was supposed to use Sql CE 3.5, but can't actually navigate the msdn download maze to get to it. Ideally, I want to setup a private deploy for CE 4.0.

我全神贯注.建议，要点，无论什么都会受到高度赞赏.谢谢！

I'm all ears. Suggestions, points, whatever would be highly appreciated. Thank you!

是的，我确实看到了知识库.它没有帮助

在此处查看:http://support.microsoft.com/kb/974247

CORFLAG 的结果

好的，试过了，这些是我的结果:C:DevelopmentMvc2MessingAroundMvc2MessingAroundinLib>corflags System.Data.SqlServerCe.dllMicrosoft (R) .NET Framework CorFlags 转换工具.版本 3.5.21022.8版权所有 (c) 微软公司.保留所有权利.

Okay, tried that and these are my results: C:DevelopmentMvc2MessingAroundMvc2MessingAroundinLib>corflags System.Data. SqlServerCe.dll Microsoft (R) .NET Framework CorFlags Conversion Tool. Version 3.5.21022.8 Copyright (c) Microsoft Corporation. All rights reserved.

Version   : v2.0.50727
CLR Header: 2.5
PE        : PE32
CorFlags  : 9
ILONLY    : 1
32BIT     : 0
Signed    : 1

我发誓我安装了两个版本的 Sql CE (3.5/4) 的 x86 版本.安装程序可能以某种方式感到困惑，因为我的处理器支持 64 位，但我运行的是 Windows xp sp 3 32 位.结果似乎表明它是 64 位.是这样吗?

I would have sworn I installed the x86 version of both versions of Sql CE (3.5/4). The installer might have gotten confused somehow because my processor is 64bit capable, but i'm running Windows xp sp 3 32 bit. The results seem to indicate it's 64 bit. Is that the case?

添加详细信息

迄今为止，以下配置已在 2 台机器上试用.两者都是具有 64 位处理器的 Windows xp sp3 32 位.两者的开发环境都是VS 2008 Pro.机器 2 上的结果是在全新安装 Sql CE 4 Ctp 之后得出的.

To date the configurations below have been tried on 2 machines. Both are Windows xp sp3 32 bit with a 64 bit capable processor. The development environment on both is VS 2008 Pro. The results on machine 2 come after a fresh install of the Sql CE 4 Ctp.

配置 #1

myappin
     System.Data.SqlServerCe.dll

myappinprivate
    amd64
    x86

myappinprivatex86
    sqlceca40.dll
    sqlcecompact40.dll
    sqlceer40EN.dll
    sqlceme40.dll
    sqlceqp40.dll
    sqlcese40.dll

myappinprivateamd64
    sqlceca40.dll
    sqlcecompact40.dll
    sqlceer40EN.dll
    sqlceme40.dll
    sqlceqp40.dll
    sqlcese40.dll

错误:

An exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.DLL but was not handled in user code

Additional information: Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8402. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.

代码:

SqlCeConnection conn = new SqlCeConnection();

配置 2

与#1 相同，但在 myappin 方向使用 System.Data.SqlServerCE.Entity.dll.

Same as #1, but with System.Data.SqlServerCE.Entity.dll at myappin direction.

点击上面代码之前的页面错误.这是消息:

The page errors before hitting the code above. This is the message:

无法加载文件或程序集System.Data.SqlServerCe.Entity"或其依赖项之一.此程序集由比当前加载的运行时更新的运行时构建，无法加载.

Could not load file or assembly 'System.Data.SqlServerCe.Entity' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

描述:在执行当前 Web 请求的过程中发生了未处理的异常.请查看堆栈跟踪以获取有关错误及其在代码中的来源的更多信息.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

异常详细信息:System.BadImageFormatException:无法加载文件或程序集System.Data.SqlServerCe.Entity"或其依赖项之一.此程序集由比当前加载的运行时更新的运行时构建，无法加载.

Exception Details: System.BadImageFormatException: Could not load file or assembly 'System.Data.SqlServerCe.Entity' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

我已经检查了 VS 2008 Pro 中的项目设置，并将 .Net 3.5 框架设置为目标.

I've checked the project settings in VS 2008 Pro and the .Net 3.5 framework is set as the target.

配置 3

与#1 相同，除了 System.Data.SqlServerCE.dll 是从 myappinprivate 文件夹中引用的.

Same as #1, except the System.Data.SqlServerCE.dll is referenced from the myappinprivate folder.

结果与 CONFIGURATION #1 相同(错误信息 100% 相同且错误发生在同一行代码上).

Results are the same as CONFIGURATION #1 (error message is 100% same and the error occurrs on the same line of code).

正确的配置

根据 Erik 的说明(如果我更仔细地遵循它们)，设置应该是

Per Erik's instructions (had I followed them more carefully), the setup should be

myappin
    x86
    amd64
    System.Data.SqlServerCE.dll

直接从代码的 bin 文件夹中引用 System.Data.SqlServerCE.dll.我愚蠢地认为需要包含 Private 文件夹，但事实并非如此.不要将 System.Data.SqlServerCE.Entity.dll 放在 bin 文件夹中，除非您使用的是 .net 4.0 解决方案.我认为 dll 不适用于 3.5.

Reference the System.Data.SqlServerCE.dll directly from the bin folder for the code. My folly was thinking the Private folder needed to be included, but it doesn't. Do not put the System.Data.SqlServerCE.Entity.dll in the bin folder unless you are using a .net 4.0 solution. I don't think that dll works w/ 3.5.

有用的链接:

http://blogs.msdn.com/b/sqlservercompact/archive/2010/07/07/introducing-sql-server-compact-4-0-the-next-gen-embedded-database-from-microsoft.aspx

推荐答案

SQL CE 3.5 不适用于 ASP.NET，您必须使用 4.0 CTP.

SQL CE 3.5 does not work with ASP.NET, you must use 4.0 CTP.

从此处下载.

安装运行时.

将以下目录内容(包括 x86 和 amd64 文件夹)复制到 ASP.NET 应用程序的 bin 文件夹中:C:Program FilesMicrosoft SQL Server Compact Editionv4.0Private

Copy the following directory contents (including the x86 and amd64 folders) to the bin folder of your ASP.NET app: C:Program FilesMicrosoft SQL Server Compact Editionv4.0Private

更新:使用桌面文件夹中的 System.Data.SqlServerCe.dll 以避免中等信任问题

myappin 
 System.Data.SqlServerCe.dll 

myappinx86 
 sqlceca40.dll 
 sqlcecompact40.dll 
 sqlceer40EN.dll 
 sqlceme40.dll 
 sqlceqp40.dll 
 sqlcese40.dll 

myappinamd64 
 sqlceca40.dll 
 sqlcecompact40.dll 
 sqlceer40EN.dll 
 sqlceme40.dll 
 sqlceqp40.dll 
 sqlcese40.dll 

添加对刚刚放在/bin 文件夹中的 System.Data.SqlServerCe.dll 文件的引用.

Add a reference to the System.Data.SqlServerCe.dll file you just put in your /bin folder.

将 SQL Compact sdf 文件放在您的 App_Data 文件夹中.

Place the SQL Compact sdf file in your App_Data folder.

添加连接字符串:

<connectionStrings>
   <add name ="NorthWind"
   connectionString="data source=|DataDirectory|Nw40.sdf" />
</connectionStrings>

连接！:-)

using System.Data.SqlServerCe;

    protected void Page_Load(object sender, EventArgs e)
    {
        using (SqlCeConnection conn = new SqlCeConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
            conn.Open();
            using (SqlCeCommand cmd = new SqlCeCommand("SELECT TOP (1) [Category Name] FROM Categories", conn))
            {
                string valueFromDb = (string)cmd.ExecuteScalar();
                Response.Write(string.Format("{0} Time {1}", valueFromDb, DateTime.Now.ToLongTimeString()));
            }
        }
    }

这篇关于无法让 sql server compact 3.5/4 与 ASP .NET MVC 2 一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！