SQLCLR 中的数据访问模式
我在项目中使用 SQLCLR，郑重声明，我对它非常满意。但是，我找不到任何良好的数据访问模式的良好信息来源。
通常我使用存储过程在 .net 和 SQL 之间进行通信，因为我总是需要一个适用于我的数据库的 API。然而，在这种情况下，.net 代码是 API 的一部分，因此 SP 看起来很糟糕。
SQL Server 中不存在 Linq2SQL（尽管可以通过 DBA 不喜欢的方式来安装它），因此它不是一个选项。
我目前拥有的是我的代码，其中混杂着标准的 ADO.NET 代码
using (SqlCommand cmd = c.CreateCommand()) {
cmd.CommandText = "SELECT ... FROM ...";
using (SqlDataReader rdr = cmd.ExecuteReader()) {
DoSomething(rdr);
}
}
，尽管它可以工作，但它看起来非常像错误的方法。
其他人是怎么做的？
I'm using SQLCLR in my project and, for the record, I'm very pleased with it. However, I can't find any good sources of information for good data access patterns.
Usually I do communication between .net and SQL using stored procedures because I always want an API for my database. However, in this case the .net code is part of the API, so SPs seem bad.
Linq2SQL does not exist in SQL server (although it can be installed by doing things DBAs won't like), so it's not an option.
What I currently have is my code cluttered with standard ADO.NET code like
using (SqlCommand cmd = c.CreateCommand()) {
cmd.CommandText = "SELECT ... FROM ...";
using (SqlDataReader rdr = cmd.ExecuteReader()) {
DoSomething(rdr);
}
}
and, although, it works, it just seems very much like the wrong way to do it.
How do other people do it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我使用 XSLT 为 DAL 生成 C# 代码。我通常从数据库定义本身加载 XML（例如某种形式的 SELECT name, type, length, ... FROM sys.columns JOIN sys.tables JOIN sys.types FOR XML PATH）我及时开发了用于代码生成的自定义 XSLT 转换。我将它们添加为 Visual Studio 构建过程本身的一部分，类似于此博客： http://rusanu.com/2009/04/11/using-xslt-to-generate-performance-counters-code/ （该博客是关于性能计数器生成的，但它也同样适用于 DAL 代码）。我还使用 XSLT 代码生成为结果集构建类型。
虽然在重型客户端上，这种方法与 ORM 和 ADO 数据集功能重叠（我仍然在这些客户端上使用它，但这是我的问题......），但由于 SQLCLR 的特定限制，在 SQLCR 中是最适合的。
这种方法首先非常灵活，允许我快速进行影响每个 DAL 入口点的全局更改，同时保持对代码库的绝对控制（没有外部依赖项，没有外部引入的错误）。与 ADO 数据集相比，它极其轻量。
我只会为了 LINQ 而放弃这种方法，因为传递 IQueryable 对象会带来附加值。但如您所知，在 SQLCLR 中，这还不是一个可行的选择。
I use XSLT to generate C# code for DAL. I usualy load the XML from the database definition itself (eg. some form of
SELECT name, type, length, ... FROM sys.columns JOIN sys.tables JOIN sys.types FOR XML PATH) and I have developed in time custom XSLT transformation for code generation. I have them added as part of the Visual studio build process itself, similar to this blog: http://rusanu.com/2009/04/11/using-xslt-to-generate-performance-counters-code/ (the blog is about performance counters generaiton, but it applies just as well to DAL code). I also build types for the result sets using XSLT code gen.While on heavy clients this approach overlaps with ORMs and ADO datasets functionality (I still use it on those clients, but that is my problem...), in SQLCR is imho the best fit, due to the SQLCLR specific limitations.
This approach is first and foremost very flexible, allowing me to quickly do global changes that affect every DAL entry point, while I keep absolute control over the code base (no external dependencies, no external introduced bugs). It is extreamly lightweight compared to ADO datasets.
I would only trade off this approach for LINQ, because of the added value of passing IQueryable objects around. But in SQLCLR, as you know, this is not yet a viable option.
对于 SQLCLR 实际上比基于适当集的 TSQL 提供性能增益的有限位置，我完全按照上面显示的方式进行数据访问。您必须执行一些相当繁重的基于循环的处理，而这些处理无法基于集合、XML 解析或极其复杂的数学来完成，才真正需要使用 SQLCLR。如果您仅使用 SQLCLR 进行数据访问，则会以性能为代价。如果您想要一些演示，请告诉我，我将从去年的演示文稿中提取 AdventureWorks 的示例。
For the limited places that SQLCLR actually provides a performance gain over proper set based TSQL, I do data access exactly how you have it shown above. You'd have to be doing some pretty heavy loop based processing that can't be done set based, XML parsing, or extremely complex math to really need to use SQLCLR. If you are using SQLCLR just for data-access, you are doing so at the cost of performance. If you'd like some demonstrations of this let me know and I'll pull my examples for AdventureWorks out of my Presentations from last year.