C# 最佳实践：直接 SQL 访问与 Web 服务

Best Practice: Direct SQL Access vs. Web Service

提问by Dan McClain

With respect to an application that has both a web and desktop client version:

对于同时具有 Web 和桌面客户端版本的应用程序：

1. What is the best practice for the desktop client which needs access to a SQL Server?
2. What are the benefits of connecting to the database from the application vs using a web service?
3. Which one provides better security?
4. What type of scope would call for one vs the other (enterprise intranet vs. web app, etc)
5. Are there any other considerations that are necessary when choosing on platform?

1. 需要访问 SQL Server 的桌面客户端的最佳实践是什么？
2. 从应用程序连接到数据库与使用 Web 服务相比有什么好处？
3. 哪一种提供更好的安全性？
4. 哪种类型的范围需要一个与另一个（企业内部网与 Web 应用程序等）
5. 在选择平台时是否还有其他需要考虑的因素？

采纳答案by Wim ten Brink

What is the best practice for the desktop client which needs access to a SQL Server?

需要访问 SQL Server 的桌面客户端的最佳实践是什么？

If you're using a local SQL Server then access the database directly. If the client has to use an SQL database on another system, the use of a web service is preferred for an additional protection and the added advantage of having a business layer that should be able to handle multiple users.

如果您使用的是本地 SQL Server，则直接访问数据库。如果客户端必须在另一个系统上使用 SQL 数据库，则首选使用 Web 服务以提供额外保护，并增加业务层应能够处理多个用户的优势。

What are the benefits of connecting to the database from the application vs using a web service?

从应用程序连接到数据库与使用 Web 服务相比有什么好处？

Connecting through a web service will always be a bit slower and modifications to the database will be a bit more difficult to add to the whole system. (Basically, that would mean that you need to create a newer version of the web service while maintaining the older web service for backwards compatibility.)

通过 Web 服务连接总是会慢一点，并且对数据库的修改将更难以添加到整个系统中。（基本上，这意味着您需要创建新版本的 Web 服务，同时维护旧的 Web 服务以实现向后兼容性。）

Which one provides better security?

哪一种提供更好的安全性？

The use of web services tends to be safer, although security is often more a peopleissue than software issue. But with the web service between the user and the database, the connection to the database is more secure since the user cannot directly access it. (Except for the functionality you provide through the web service.) This point is moot when client and database are on the same system because then the user can get full access.

Web 服务的使用往往更安全，尽管安全性通常更多是人的问题而不是软件问题。但是通过用户和数据库之间的 Web 服务，与数据库的连接更加安全，因为用户无法直接访问它。（除了您通过 Web 服务提供的功能。）当客户端和数据库位于同一系统上时，这一点没有意义，因为这样用户就可以获得完全访问权限。

What type of scope would call for one vs the other (enterprise intranet vs. web app, etc)

哪种类型的范围需要一个与另一个（企业内部网与 Web 应用程序等）

Web services are better for client-server applications, where users should not have direct access to the database. Otherwise, a direct database connection would just improve performance. When creating a web service, start by writing a generic (class) library which will provide the functionality for the web service. Create a web service around this (business) library, exposing the important methods to the outside world. Any web site could call this library directly without using the web service, although you can always opt to even let the web site code access the data through the web service. Even if you create just a desktop application with a local database, writing a business library with logic to access the database is just a very good thing to do. Your client could call this business library directly or through a web service, depending on your needs.

Web 服务更适用于客户端-服务器应用程序，用户不应直接访问数据库。否则，直接数据库连接只会提高性能。创建 Web 服务时，首先编写一个通用（类）库，该库将为 Web 服务提供功能。围绕这个（业务）库创建一个 Web 服务，将重要的方法暴露给外界。任何网站都可以在不使用网络服务的情况下直接调用该库，尽管您始终可以选择甚至让网站代码通过网络服务访问数据。即使您只创建一个带有本地数据库的桌面应用程序，编写一个带有访问数据库的逻辑的业务库也是一件非常好的事情。您的客户可以直接或通过网络服务调用此业务库，

Are there any other considerations that are necessary when choosing on platform?

在选择平台时是否还有其他需要考虑的因素？

Mostly just the amount of hardware that you're willing to use to set things up. If you can afford to set up a database server, a separate web service for the services and a third for your web site, with a dozen or so client systems, then you can opt for the most layered version, where both client and web site call upon the web service, which calls the database. But if everything needs to run on a single system then just stick to the application and the business layer/library instead.

主要是您愿意用于设置的硬件数量。如果你有能力设置一个数据库服务器，一个单独的 Web 服务用于服务，第三个用于你的网站，有十几个客户端系统，那么你可以选择最分层的版本，其中客户端和网站调用调用数据库的 Web 服务。但是如果一切都需要在单个系统上运行，那么只需坚持应用程序和业务层/库。

Adding layers will reduce performance from the view of a single user, though. However, working with multiple layers can improve the overall performance because resources get divided better amongst multiple users.

但是，从单个用户的角度来看，添加层会降低性能。但是，使用多个层可以提高整体性能，因为资源在多个用户之间得到更好的分配。

回答by Darin Dimitrov

The general rule of thumb is the following:

一般的经验法则如下：

1. Write an independent data access assembly that will talk to the database.
2. If you are looking for interoperability between different platforms/clients then expose this assembly as a SOAP web service.
3. If you are looking for performance use the assembly directly in your client .NET applications.

1. 编写将与数据库对话的独立数据访问程序集。
2. 如果您正在寻找不同平台/客户端之间的互操作性，则将此程序集公开为 SOAP Web 服务。
3. 如果您正在寻找性能，请直接在客户端 .NET 应用程序中使用该程序集。

回答by Mathias F

If you can acces the DB from the desktop then you should do that.

如果您可以从桌面访问数据库，那么您应该这样做。

You have multiple kinds of clients. That means your application should have mulitple layers. It does not mean you need multiple tiers.

您有多种类型的客户。这意味着您的应用程序应该有多个层。这并不意味着您需要多层。

Multiple tiers can be necessary if your layers must transfer data over firewalls or if you have diverse technolgies.

如果您的层必须通过防火墙传输数据或者您拥有不同的技术，则可能需要多层。

回答by Andomar

I'd keep it simple and minimize the amount of layers. Layers cost performance, introduce complexity, and require changes to be made in more locations.

我会保持简单并尽量减少层数。分层成本性能，引入复杂性，并需要在更多位置进行更改。

So, if the netwerk connection between the application and Sql Server is open (typically tcp port 1433), I'd use Sql connectivity.

因此，如果应用程序和 Sql Server 之间的网络连接是打开的（通常是 tcp 端口 1433），我会使用 Sql 连接。

回答by Marty Nelson

Given the context, there can be a big security concern with client access to databases. It requires either giving users access to the db, or creating a service account. Giving users direct access to the db poses risks. Both approaches open the door to exploiting desktop dll's to connect to db outside of application context (Multiple times I've seen cases where there is a common data access class that all functional operations use. And of course, this components initializes all the connection information. Reflection based access makes it is easy to get to protected or private methods, unless you assert Security Privileges).

鉴于上下文，客户端对数据库的访问可能存在很大的安全问题。它需要授予用户访问数据库的权限，或者创建一个服务帐户。让用户直接访问数据库会带来风险。这两种方法都为利用桌面 dll 连接到应用程序上下文之外的数据库打开了大门（我多次看到所有功能操作都使用通用数据访问类的情况。当然，这个组件初始化所有连接信息. 基于反射的访问使得访问受保护或私有方法变得容易，除非您断言安全特权）。

Web services expose functional operations that don't expose any sql based operations. Not only is this more secure, it abstracts your client away from your data storage implementation.

Web 服务公开不公开任何基于 sql 的操作的功能性操作。这不仅更安全，而且将您的客户端从您的数据存储实现中抽象出来。

Again, it depends on your context. In the Enterprise/ISV realm though, it is generally a big no-no.

同样，这取决于您的上下文。不过，在企业/ISV 领域，这通常是一个很大的禁忌。

回答by Mike

I do a hybrid. Direct database access with limited user who can perform read only from the tables. Webservice with a high privileged database user who can perform write functions. The business rules are built in the webservice (audit trials, permission checks etc)

我做一个混合动力车。与只能从表中读取的有限用户的直接数据库访问。具有可以执行写入功能的高特权数据库用户的 Web 服务。业务规则建立在网络服务中（审计试验、权限检查等）

The direct db access makes it easier for me to develop reports, access lookup values from the client app.

直接数据库访问使我可以更轻松地开发报告，从客户端应用程序访问查找值。