将项目转换为 SQL Server，设计思路？
目前，我正在使用一个用 Access 编写的丑陋的业务应用程序，该应用程序每天都会获取一个电子表格并将其导入到 MDB 中。我目前正在将一个主要项目转换为 SQL Server 和 .net，特别是 C#。
为了容纳此信息，有两个表（此处为别名），我将其称为 Master_Prod 和 Master_Sheet，它们连接在 Master_Prod 表 ProdID 的身份键父级上。还有两个表用于存储历史记录：History_Prod 和 History_Sheet。还有更多从 Master_Prod 扩展出来的表，但出于解释目的，将其限制为两个表。
由于这是用 Access 编写的，处理该文件的子例程中充斥着手动编码的触发器来处理历史记录，这些触发器过去和现在一直是一个持续的痛苦，这是我很高兴将其转移到数据库服务器的原因之一而不是 RAD 工具。我正在编写触发器来处理历史跟踪。
我的计划是创建一个对电子表格进行建模的对象，将数据解析到其中，并在将数据发送到服务器之前使用 LINQ 在客户端进行一些检查...基本上我需要将工作表中的数据与匹配的数据进行比较记录（除非不存在，否则是新的）。如果任何字段已更改，我想发送更新。
最初我希望将此过程放入某种接受 IEnumerable 列表的 CLR 程序集中，因为我已经有了这种形式的电子表格，但我最近了解到这将与我使用的一个相当重要的数据库服务器配对。我非常担心陷入困境。
这值得放入 CLR 存储过程吗？还有其他数据输入的入口点，如果我可以构建一个过程来处理它们（给定传入的对象），那么我可以从应用程序中删除大量业务规则，但会牺牲潜在的数据库性能。
基本上，我想将更新检查从客户端移开，并将其放在数据库上，以便数据系统管理是否应该更新表，以便可以触发历史触发器。
有没有想过更好的方法来沿着同一方向实施这一点？
Currently, I'm sitting on an ugly business application written in Access that takes a spreadsheet on a bi-daily basis and imports it into a MDB. I am currently converting a major project that includes this into SQL Server and .net, specifically in c#.
To house this information there are two tables (alias names here) that I will call Master_Prod and Master_Sheet joined on an identity key parent to the Master_Prod table, ProdID. There are also two more tables to store history, History_Prod and History_Sheet. There are more tables that extend off of Master_Prod but keeping this limited to two tables for explanation purposes.
Since this was written in Access, the subroutine to handle this file is littered with manually coded triggers to deal with history that were and have been a constant pain to keep up with, one reason why I'm glad this is moving to a database server rather than a RAD tool. I am writing triggers to handle history tracking.
My plan is/was to create an object modeling the spreadsheet, parse the data into it and use LINQ to do some checks client side before sending the data to the server... Basically I need to compare the data in the sheet to a matching record (Unless none exist, then its new). If any of the fields have been altered I want to send the update.
Originally I was hoping to put this procedure into some sort of CLR assembly that accepts an IEnumerable list since I'll have the spreadsheet in this form already but I've recently learned this is going to be paired with a rather important database server that I am very concerned with bogging down.
Is this worth putting a CLR stored procedure in for? There are other points of entry where data enters and if I could build a procedure to handle them given the objects passed in then I could take a lot of business rule away from the application at the expense of potential database performance.
Basically I want to take the update checking away from the client and put it on the database so the data system manages whether or not the table should be updated so the history trigger can fire off.
Thoughts on a better way to implement this along the same direction?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用SSIS。使用 Excel Source 读取电子表格，也许使用 查找转换 检测新项目，最后使用 SQL Server 目标，将丢失的项目流插入到 SQL 中。
SSIS更适合这些从头开始编写东西的工作，无论 linq 有多么有趣。 SSIS 包比某些忘记源的 dll 更容易调试、维护和重构。此外，您将无法匹配 SSIS 在管理高吞吐量缓冲区方面的改进 数据流。
Use SSIS. Use Excel Source to read the spreadsheets, perhaps use a Lookup Transformation to detect new items and finally use a SQL Server Destination to insert the stream of missing items into SQL.
SSIS is way better fit to these kind of jobs that writing something from scratch, no matter how much fun linq is. SSIS Packages are easier to debug, maintain and refactor than some dll with forgoten sources. Besides, you will not be able to match the refinements SSIS has in managing its buffers for high troughput Data Flows.
不起作用。 C# 编写的 CLR 过程的任何输入仍然必须遵循正常的 SQL 语义。所有可以改变的只是内部设置。与客户端的任何通信都必须使用 SQL 完成。这意味着执行/方法调用。无法直接传入可枚举的对象。
Does not work. Any input into a C# written CLR procedure STILL has to follow normal SQL semantics. All that can change is the internal setup. Any communication up with the client has to be done in SQL. Which means executions / method calls. No way to directly pass in an enumerable of objects.
您可能需要为您的方法选择一个“中心性” - 即以数据为中心或以对象为中心。
我可能会首先对数据进行适当的建模。这是因为关系数据库（甚至是关系数据库中表示的非规范化模型）通常比客户端工具/库应用程序寿命更长。我可能会开始尝试以正常形式进行建模，并考虑维护审计/历史记录的触发器，正如您在这段时间提到的那样。
然后我通常会想到传入的数据（实际上不是对象模型或实体）。因此，然后我专注于输入的格式和语义，看看我的数据模型中是否存在不合适的情况——也许我的数据模型中存在不正确的假设。是的，我并没有考虑创建一个验证电子表格的对象模型，尽管电子表格是出了名的多变的输入源。与 Remus 一样，我会简单地使用 SSIS 将其引入 - 可能引入到临时表中，然后进行更多验证，然后使用一些 T-SQL 将其应用到生产表。
然后我会考虑一个客户端工具，它具有基于我良好的实体数据模型的对象模型。
或者，对象方法意味着对电子表格进行建模，但也意味着需要将对象模型持久化到数据库 - 也许您现在有两个对象模型（电子表格和完整业务域）和数据库模型（存储持久性），如果电子表格对象模型不如系统的业务域对象模型完整。
我可以想到一个例子，其中我有一个类似这样的一次性外部对象模型。它读取一个“主文件”，这是一个描述输入文件的布局文件。该对象模型允许程序构建 SSIS 包（以及 BCP 和 SQL 脚本）以对这些文件进行导入/导出/执行其他操作。实际上，它是一个一次性对象模型 - 它不用作行中数据的实际模型或父行和子行之间的任何类型的导航等，而只是用于内部目的的内部表示 - 它不一定对应于“域”实体。
You probably need to pick a "centricity" for your approach - i.e. data-centric or object-centric.
I would probably model the data appropriately first. This is because relational databases (or even non-normalized models represented in relational databases) will often outlive client tools/libraries applications. I would probably start trying to model in a normal form and think about the triggers to maintain audit/history as you mention during this time also.
I would typically then think of the data coming in (not an object model or an entity, really). So then I focus on the format and semantics of the inputs and see if there is misfit in my data model - perhaps there were assumptions in my data model which were incorrect. Yes, I'm not thinking of making an object model which validates the spreadsheet even though spreadsheets are notoriously fickle input sources. Like Remus, I would simply use SSIS to bring it in - perhaps to a staging table and then some more validation before applying it to production tables with some T-SQL.
Then I would think about a client tool which had an object model based on my good solid data model.
Alternatively, the object approach would mean modeling the spreadsheet, but also an object model which needs to be persisted to the database - and perhaps you now have two object models (spreadsheet and full business domain) and database model (storage persistence), if the spreadsheet object model is not as complete as the system's business domain object model.
I can think of an example where I had a throwaway external object model kind of like this. It read a "master file" which was a layout file describing an input file. This object model allowed the program to build SSIS packages (and BCP and SQL scripts) to import/export/do other operations on these files. Effectively it was a throwaway object model - it was not used as the actual model for the data in the rows or any kind of navigation between parent and child rows, etc., but simply an internal representation for internal purposes - it didn't necessarily correspond to a "domain" entity.