C＃中的本地数据库和SQL Server Management Studio创建的数据库之间有什么区别？ [英] What is the difference between a Local Database in C# and a SQL Server Management Studio created database?

问题描述

我正在使用MS Visual C＃2010 Express创建一个需要数据库的应用程序。

我知道有两种方法可以创建/使用与此应用程序的SQL数据库。

第一个似乎是从C＃中，通过右键单击我的应用程序在解决方案资源管理器中，并单击添加创建一个本地数据库 >新建项目 - >本地数据库。然后它显示在数据库浏览器中，我可以使用它。

另一种方式是我使用SQL Server Management Studio创建数据库，然后从C＃代码中打开一个连接（SQLConnection ... yada yada yada）并使用它。

我很难理解选择一种方式或其他方式之间有什么技术原因...

p>

有人能描述差异，什么标准将用于选择一种方式与另一种方式？ （或指向网站引用...）

谢谢！

-Adeena

附加信息...
现在，这是一个爱好项目，因为我得到了一些事情解决了。

1. 我是唯一的开发人员，并且在单个机器上工作


2. 应用程序是独立运行的 - 不是
   浏览器或通过网络以任何方式。我知道这不是方向
   宇宙正在前进，但如上所述，这是一个爱好
   项目，我需要完成以解决一些其他问题。


3. 我不相信我有任何需要或意图多个应用程序
   在这个数据库上工作。

解决方案

实际上您有三个选项。您没有描述的选项是您使用SSMS创建数据库，然后设置连接到文件并选择由SSMS创建的MDB文件（您可能需要首先使用SSMS卸载数据库以使SQL Express释放其文件锁定）。当您创建此文件的连接时，系统会提示您是否要连接到该文件，或将其添加到您的项目中。

数据库可以采取两种形式，具体取决于如何创建它。有关详细信息，请参阅如何：管理项目中的本地数据文件 。

客户端服务器SQL Express

如果您设置了数据库与SSMS并通过SQL Express连接到它，那么你没有一个本地数据库是你的项目的一部分，你有一个数据库，服务器恰好在你的工作站本地。

本地数据库，SQL Express

如果使用SSMS设置数据库，请卸载数据库并将文件添加到

本地数据库，精简版
$ b

如果您使用Visual Studio菜单创建一个新数据库，则您有一个本地Compact Edition数据库。

SQL Express

当Visual Studio启动调试时，SQL Server Express的私有命名实例启动，应用程序使用共享内存而不是网络协议与此通信。

但是，绝对没有什么阻止您安装作为服务运行的SQL Express实例。您可以装载相同的数据库文件（或其副本），并使其可用于网络。你甚至可以将它挂载到SQL标准的实例上，甚至可以挂载到SQL Enterprise的实例上。

为什么你会对本地实例感到厌烦？它对多开发人员团队有好处，因为开发人员可以改变他们的模式而不中断其他人。它允许开发桌面（而不是网络）软件，虽然在这个日子和时代对这种能力的需求正在减少。

根据你在你的硬件开发环境，个人我不会使用本地数据库。

有些事情要注意

• TSQL对于所有版本的MSSQL是完全相同的，除了Compact和Micro版本。


• 在环境上，SQL Express将数据库大小限制为4G，但我相信R2的数据库大小已经达到8G。这对于开发不太可能是重要的，但可能影响测试者。


版本



这方面的信息相当薄。 Microsoft的版本比较不考虑Compact或Micro版本。紧凑版网页上的一些模糊声明了完全的TSQL兼容性。 SDF是一个一体化文件;没有单独的日志文件。从SDF到客户端服务器的路径肯定比SQL Express更直接，但它似乎是一个支持的选项，因为在这个主题的msdn有文章。





复制工具可用于压缩版，因此它可以用作偶尔连接的系统（也称为公文包模型）中的本地数据库缓存。公文包模型需要更仔细的整体系统设计，但它有很多优点：单个用户独立系统的所有性能和简单性，具有客户端 - 服务器系统的大部分优势。





结论





为了您的目的，我将使用Compact Edition选项。其他解决方案的开销和复杂性是解决问题，你不会和不会有。它们旨在解决在具有正式发布周期的网络化，大规模环境中团队开发的问题。





您处于幸运的位置，您可以保持简单。 Visual Studio中的工具更好。




I'm creating an application with MS Visual C# 2010 Express that requires a database.

I've learned that there seem to be two ways to create/use a SQL database with this application.

The first seems to be where from within C#, I can create a "local database" by right-clicking on my application in the Solution Explorer and clicking "Add"->"New Item"->"Local Database". Then it's shown in the Database Explorer and I can use it.

The other way is where I create a database with SQL Server Management Studio and then from within the C# code, I open a connection to it (SQLConnection... yada yada yada) and use it.

I'm having a hard time understanding what technical reasons there are between choosing one way or the other way to do this...

Can someone describe the differences and what criteria would be used to choose one way vs. the other? (or point to a website reference...)

Thanks!

-Adeena

Additional Info... Right now, this is really a hobby project as I get a few things worked out.

1. I'm the only developer and working on a single machine
2. The application is intended to be one that runs standalone - not in a browser or over the web in any way. I know that that's not the direction the universe is heading, but as mentioned above, this is a hobby project that I need to complete to work out a few other issues.
3. I don't believe I have any need or intent for multiple applications to work on this database.

解决方案

Actually you have three options. The option you didn't describe is the one where you create a database with SSMS and then set up a connection to a file and select the MDB file that was created by SSMS (you will probably need to first dismount the database using SSMS to get SQL Express to release its file locks). When you created this connection to a file, you will be prompted as to whether you want to connect to it where it is, or add it to your project.

A local database can take two forms, depending on how you create it. For detailed information, consult How to: Manage Local Data Files in Your Project.

Client-server, SQL Express

If you set up a database with SSMS and connect to it via SQL Express then you don't have a local database that's part of your project, you have a database for which the server happens to be local to your workstation.

Local database, SQL Express

If you set up a database with SSMS, dismount the database and add the file to your project, then you have a local database that uses a private instance of SQL Express.

Local database, Compact Edition

If you create a new database with the Visual Studio menus, you have a local Compact Edition database .

SQL Express

When Visual Studio launches debugging, a private named instance of SQL Server Express is started, and the application communicates with this using shared memory rather than a network protocol.

However, there is absolutely nothing preventing you from installing an instance of SQL Express that runs as a service. You can mount the same database file (or a copy of it) and make it available to the network. You can even mount it on an instance of SQL Standard, or even SQL Enterprise.

Why then would you muck about with a local instance? It has advantages for multi-developer teams because developers can alter their schema without disrupting others. It allows development of desktop (as opposed to network) software, although in this day and age demand for that capability is diminishing.

Depending on how much hardware you have in your development environment, personally I wouldn't use a local database. SQL Server is a memory pig, and I'd much rather it ran on a completely separate box.

Some things to note

• TSQL is absolutely identical for all editions of MSSQL except for the Compact and Micro editions.
• Environmentally, SQL Express limits database size to 4G although I believe this went up to 8G for R2. This is unlikely to be significant for development but may impact testers.
• Some SQL Server Reporting Services features are not available in cheaper editions.

SQL Server Compact Edition

Information on this is pretty thin. Microsoft's version comparison doesn't consider Compact or Micro editions. Some of the blurb on the Compact edition web page claims full TSQL compatibility. SDF is an all-in-one file; there is no separate log file. The path from SDF to client-server is certainly less direct than for SQL Express, but it does appear to be a supported option since there are articles in msdn on this topic.

Replication tools are available for Compact edition so that it can be used as a local database cache in an occasionally connected system (aka briefcase model). Briefcase model requires more careful total system design, but it has a lot going for it: all the performance and simplicity of a single user standalone system with most of the advantages of a client-server system.

Conclusion

For your purposes I'd go with the Compact Edition option. The overheads and complexity of the other solutions are pitched at solving problems you don't and won't have. They are intended to solve the problems of team development in a networked, large scale environment with a formal release cycle.

You are in the fortunate position that you can keep it simple. The tools in Visual Studio are nicer anyway.

这篇关于C＃中的本地数据库和SQL Server Management Studio创建的数据库之间有什么区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！