使用 SQL 查询 Web 服务
最近，当我考虑将面向服务的架构与出色的 UI 相结合时，我感到有点震惊，该 UI 利用 SQL 来优化查询数据时的性能。
例如，ASP.NET 的 DevExpress 网格视图非常酷，它将所有过滤、排序和分页逻辑委托给数据库服务器。但这假定数据是从支持 SQL 的数据库服务器检索的。
如果我想在数据库层和 UI 层之间引入 Web 服务层，并让 UI 使用 Web 服务来查询数据，该怎么办？
- 如何设计 Web 服务和 UI，以便可以通过 Web 服务将过滤请求从 UI 传递到数据库？
- 我是否需要提供
List QueryData(string sqlQuery)风格的 Web 服务，并且必须自己解析 SQL 字符串以保证安全/访问限制？ - 或者有什么好的框架或设计指南可以减轻我的负担吗？
这肯定是一个很常见的问题，而且我相信这个问题已经得到了比较充分的解决，不是吗？
我主要对基于 .NET/C# 或兼容的解决方案感兴趣。
编辑：我找到了 OData 和 Microsoft WCF 数据服务。如果我猜对了，基于 OData 的应用程序可能如下所示：
- User ---/Give me Page 1 (records 1..10)/---> ASP.NET Server Control（当然，通过HTTP）
- ASP.NET Server Control ---/LINQ Query/---> 数据服务客户端
- 数据服务客户端 ---/OData查询/---> WCF数据服务
- WCF数据服务 ---/LINQ查询/---> 实体框架
- 实体框架 ---/SQL 查询/---> 数据库
如果我做对了，我的 DevExpress 服务器控件应该能够通过所有这些层将过滤请求（例如只给我前 10 个）委托给数据库，然后应用其索引等。为了执行该查询。
是这样吗？
编辑：很高兴看到这条线索变得栩栩如生:-)很难决定接受什么答案，因为所有的答案对我来说似乎都一样好......
I had a bit of a shock recently when thinking about combining a service oriented architecture with a brilliant UI which leverages SQL to optimize performance when querying data.
The DevExpress grid view for ASP.NET, for example, is so cool that it delegates all filtering, sorting and paging logic to the database server. But this presumes that the data is retrieved from a SQL-able database server.
What if I want to introduce a web service layer between the database and UI layers, and to have the UI use the web services to query the data?
- How can I design the web services and the UI such that I can pass filtering requests from the UI via the web services to the database?
- Do I need to provide a
List QueryData(string sqlQuery)style web service and have to parse the SQL string on my own to guarantee security/access restriction? - Or is there any good framework or design guideline that takes this burden from me?
This must be a very common problem, and I am sure that it has been solved relatively adequately already, has it?
I am mainly interested in a .NET/C#-based or -compatible solution.
Edit: I've found OData and Microsoft WCF Data Services. If I got it right, an OData-based application could look as follows:
- User ---/Give me Page 1 (records 1..10)/---> ASP.NET Server Control (of course, via HTTP)
- ASP.NET Server Control ---/LINQ Query/---> Data service client
- Data service client ---/OData Query/---> WCF Data Service
- WCF Data Service ---/LINQ Query/---> Entity Framework
- Entity Framework ---/SQL Query/---> Database
If I got this right, my DevExpress server control should be able to delegate a filtering request (e.g. give me the top 10 only) through all these layers down to the database which then applies its indexes etc. in order to perform that query.
Is that right?
Edit: It is a joy to see this thread coming to life :-) It is hard to decide on what answer to accept because all seem equally good to me...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
非常有趣的问题！我不认为有正确或错误的答案，但我认为你可以建立一些架构原则。
首先，“面向服务的架构”是一种架构风格，要求您公开业务服务以供其他应用程序使用。运行数据库查询不是一项服务——至少在我看来。事实上，提供 Web 服务来执行任意 SQL 可能是一种反模式 - 您将绕过大多数数据库服务器提供的安全模型，您无法控制查询 - 编写语法正确的“选择”相对容易查询会削弱你的数据库（笛卡尔连接是我最喜欢的），并且 Web 服务协议的开销会使这种方法比通过正常访问路由（LINQ 或其他）查询数据库慢几倍。
那么，假设您接受这个观点——问题的解决方案是什么？
首先，如果您想要使用 DevExpress 网格的生产力，您可能应该按照 DevExpress 希望您的工作方式工作 - 如果这意味着直接查询数据库，那么这是迄今为止最好的方法。如果您想迁移到 SOA，而 DevExpress 网格不支持，那么是时候寻找新的网格控件，而不是将整个企业架构定制为相对较小的组件。
其次，从结构上来说，你应该在哪里进行排序、过滤等？这是 SQL 中的一个简单概念，但在尝试将其转换为 Web 服务规范时却相当令人不快 - 您很快就会得到一个难以理解的方法签名（“getAccountDataForUser(userID, bool sortByDate, bool sortByValue, bool filterZeros, bool filterTransfers)”） ）。
另一方面，在客户端执行过滤和排序是混乱且缓慢的。
我的建议是查看 规范模式 - 这允许您拥有干净的方法签名，但是以一致的方式指定所需的排序和顺序。
Really interesting question! I don't think there's a right or wrong answer, but I think you can establish some architectural principles.
Firstly, "Service Oriented Architecture" is an architectural style that requires you to expose business services for consumption by other applications. Running a database query is not a service - in my opinion at least. In fact, providing a web service to execute arbitrary SQL is probably an anti-pattern - you would bypass the security model most database servers provide, you'd have no control over the queries - it's relatively easy to write a syntactically correct "select" query which cripples your database (Cartesian joins are my favourite), and the overhead of the web service protocol would make this approach several times slower than just querying the database through normal access routes - LINQ or whatever.
So, let's assume you accept that point of view - what is the solution to the problem?
Firstly, if you want the productivity of using the DevExpress grid, you probably should work in the way DevExpress want you to work - if that means querying the database directly, that's by far the best way to go. If you want to move to a SOA, and the DevExpress grid doesn't support that, it's time to find a new grid control, rather than tailor your entire enterprise architecture to a relatively minor component.
Secondly - structurally, where should you do your sorting, filtering etc? This is an easy concept in SQL, but rather unpleasant when trying to translate it to a web service specification - you quickly end up with an incomprehensible method signature ("getAccountDataForUser(userID, bool sortByDate, bool sortByValue, bool filterZeros, bool filterTransfers)").
On the other hand, performing the filtering and sorting on the client is messy and slow.
My recommendation would be to look at the Specification Pattern - this allows you to have clean method signatures, but specify the desired sorting and ordering in a consistent way.
实现
List QueryData(string sqlQuery)将使您面临近乎无限数量的安全问题。如果您需要基于安全访问进行过滤，那么 OData 实现也将非常重要，您需要在 WCF 服务上设置适当的授权/身份验证，以便您可以根据经过身份验证的用户数据进一步过滤 OData 查询。
从 WCF 服务检索数据时实现服务器端数据操作的最简单方法是在后面的代码中拦截 Grid 的排序/过滤操作，然后根据用户正在执行的操作调用 WCF 服务上的专用方法。
Implementing the
List QueryData(string sqlQuery)will open you up to a near infinite number of security problems.If you need to filter based on security access, then the OData implementation will not be trivial either, you need to setup proper authorization/authentication on the WCF service so that you could further filter the OData query based on the authenticated user data.
The easiest way to implement server side data operations when the data is retrieved from a WCF service would be to intercept the Grid's sort/filter operations in the code behind, and then call a specialized method on the WCF service based on what the user is doing.
考虑到开发人员世界中的剥皮猫数量，我不得不说不。WCF
数据服务提供了迄今为止我发现的最佳解决方案，但可以进行身份验证和授权有一篇不错的文章介绍了与此相关的服务器端问题 http://blogs.msdn.com/b/astoriateam/archive/2010/07/19/odata-and-authentication-part-4-server -side-hooks.aspx 设置起来并不容易，但效果很好。
Given the number of skinned cats laying around the developer world, I'd have to say no.
WCF Data Services offers the best solution I've found so far, but there authentication and authorization can be tricky. There is a decent post covering the server-side issues around this at http://blogs.msdn.com/b/astoriateam/archive/2010/07/19/odata-and-authentication-part-4-server-side-hooks.aspx. Setting this up isn't easy, but it does work well.