从MS Access迁移的复杂数据密集型，数据库不可知系统中的业务/数据层？ [英] Business/Data Layers in complex data-intensive, DB-agnostic system being migrated from MS Access?

问题描述

在尝试迁移一个非常复杂且数据密集的生命科学应用程序（并添加实质性功能）时，我正在努力解决处理业务层和数据持久性的正确方法的问题。沿途的改进）。自Access 1.0发布以来，这个巨头一直在Access平台上发展。这使得设计人员可以非常轻松地做一些很酷的事情，比如与SQL Server和Oracle相当好地讨论，这在任何其他没有内置到数据库引擎中的ODBC抽象的平台中都不是那么简单。这也让他们很容易做出像制作非常平坦的桌子这样的东西，因为当你把所有东西扔进一张桌子时，Access形式更容易出现。

快进十年（我只是这里过去几年和这次迁移已有一年多了一段时间）现在这个怪物有200张桌子，如果不接近100列，其中很多都有几十张。处理迁移这个问题的第一个主要项目只涉及少数特定于某个子系统的表。我想以一种避免仅使用无类型数据集和读取器的默认方式来解决这个问题，因为我觉得它会使最终结果看起来很像原始的Access VBA代码，这是一个理解，调试和维护的噩梦。你在VBA代码中所做的一切显然都依赖于对底层模式的深刻理解，大量的DAO记录集访问数据（使用列名并迭代记录直到EOF）。
在我的。网络代码我试图利用强类型和intellisense（喘息！）之类的东西来帮助我和我的初级开发人员完成工作，而不必经常回顾模式，这样我们就可以从数据库中获取一些数据，做一些事情，然后更新那些或其他一些记录......

管理层的目标是在我们将一个子系统一次迁移到.Net Web架构时保持互操作性。

我通过创建一些直接（大部分）映射到一两个表的业务对象，在一个非常基础的层面上完成了一些自制O / R映射，这样在业务层中我们可以使用这些对象而不是无类型数据集和读取器。问题是，我觉得在最佳实践方面应用我学到的一些东西是非常困难的，我想我想要保证我能用我所得到的最好的方法是"OK"例如，在我们自己开发的文档管理系统（再次来自遗留访问系统）中，我有一个代表这些非规范化表之一的类。它有95个属性，实际上并不比单表数据传输对象多。这个类本身并没有做太多工作，而是依赖于"经理"这个类。同班同学做重担。我最终以这种方式做事，因为我需要一种方法将具有70个数据输入字段+隐藏系统字段的表单绑定到业务层对象，我可以传递并以易于使用的方式执行操作业务逻辑必须与一堆其他支持表交谈以做任何有用的事情。

由于基础表结构无法修改，因此很难以真正的OOD方式处理这个问题。很多，整个系统的设计都是从一个平面的表格视角完成的。

我发现自己感觉有一种冲动，就是每个表创建一个类，也许还有一些包装类来组织"对象" ;.我很想使用像nHibernate这样的O / R映射器工具来消除痛苦，但似乎通过简单的配置让它与SQL服务器和Oracle很好地协作将会有很多工作。相反，我开发了一个实用程序类，它允许我做简单的1表的东西，而不用担心我要反对的数据库。我最终必须编写许多小函数来更新一个或两个无关表中的一个或两个字段，这些表将这个卡片组子系统保持在一起。对于任何开始处理更复杂查询的事情，我最终都要手工编写动态SQL ...有时两次处理SQL / Oracle差异。我真的没有花太多时间来完成诸如分离关注点或信息隐藏等事情。
我真的很喜欢一些可以帮助我在做事之间找到平衡的指导。右"以及不破坏与传统VBA / Access代码兼容的主要指令。有时我觉得我在穿着铅泳裤时试图踩水。管理层不了解或关心任何这一点，因为在他们看来，就像VBA / Access一样，这应该就像在表格中读取和写入数据一样简单，这有什么复杂之处呢？ （没关系，遗留的代码库超过200,000行意大利面代码！）

感谢您阅读我的呼救声，哈哈！我觉得自己已经做得很好，但战场上的一些提示和一些鼓励现在还有很长的路要走。

戴夫

I'm having some struggles trying to settle on the correct approach to handle working with and handling the business layer and persistence of data in my attempt to migrate a very complex and data-intensive life-sciences application (and add substantial functional improvements along the way).  This behemoth which has been evolving in an Access platform ever since Access 1.0 was released.  This allowed the designers to do some cool things very easily like talk to SQL Server and Oracle fairly equally well that is not so trivial in any other platform that doesn't have this ODBC abstraction built into the database engine.  It also made it very easy for them to do things like make extremely flat tables because Access forms are easier to crank out when you just throw everything into one table.

Fast forward a decade (I have only been here for the last couple of years and on this migration for a little more than a year) and now this monster has 200 tables, many of which have dozens if not approaching 100 columns.  The first major project to deal with migrating this thing has involved only a handful of the tables that are quite specific to a certain subsystem.  I wanted to approach this in a way that avoids just defaulting to using untyped datasets and readers for everything because I feel that it will cause the end result to look a lot like the original Access VBA code which is a nightmare to understand, debug and maintain.  Everything you do in the VBA code is obviously dependant on a deep understanding of the underlying schema, with tons of accessing data by DAO recordsets (using column names and iterating through records until EOF).

In my .Net code I am trying to take advantage of things like strong typing and intellisense (gasp!) to help me and my junior developers accomplish things without having to constantly refer back to the schema so we can get a piece of data out of the DB, do some things and then update that or some other records...

Management's goal is to maintain interoperability as we migrate one subsystem at a time to a .Net web architecture.

I've done some homebrew O/R mapping on a very basic level by creating some business objects that map directly (mostly) to a table or two so that in the business layer we can work with those objects instead of untyped datasets and readers.  The thing is that I feel that it is very difficult to apply some of the things I have learned in terms of best practices and I guess I want assurance that my method of doing the best I can with what I've got is "OK".

For instance in our homegrown document management system (again from the legacy access system), I have an class that represents one of these un-normalized table.  It has like 95 properties and really isn't much more than a single-table data transfer object.  This class doesn't do much on its own and relies on a "Manager" co-class to do the heavy lifting.  I ended up doing things this way because I needed a way to bind a form that has 70 data entry fields + hidden system fields to a business layer object that I could pass around and do things in an easy-to-work-with manner but the business logic has to talk to a bunch of other supporting tables to do anything useful.

It's proven to be very difficult to approach this in a truly OOD manner given that the underlying table structure cannot be modified very much and the whole system design was done very much from a flat table-oriented perspective...

I find myself feeling the urge to just create 1 class per table and perhaps some wrapper classes to organize things into "objects".  I would love to use a O/R mapper tool like nHibernate to take away the pain but it seems like it would be a ton of work to get it to play nicely with both SQL server and Oracle via simple configuration.  Instead I've developed a utility class that allows me to do simple 1-table stuff without worrying about which database I'm going against.  I end up having to write a lot of little functions to go update one or two fields in that one or two extraneous tables that hold this deck-of-cards subsystem together.  For anything that starts to deal with more complex queries, I end up having to write dynamic SQL by hand...sometimes twice to deal with SQL/Oracle differences.  I don't really get to spend much time trying to accomplish things like separation of concerns or information hiding, etc.

I would really love some guidance that could help me find a balance between doing things "right" and the primary directive of not breaking compatibility with the legacy VBA/Access code.  Sometimes I feel like I'm trying to tread water while wearing lead swimming trunks.  Management doesn't understand or care about any of this because in their view, just like VBA/Access, this should all be as simple as reading and writing data in and out of tables and what is so complicated about that? (nevermind that the legacy codebase is over 200,000 lines of spaghetti-code!)

Thanks for reading my cry for help, lol!  I feel like I've done a good job with what I've been given, but some tips from the battlefield and some encouragement would go a long way right now.

Dave

推荐答案

嗨Dave，
由于OR映射工具的学习曲线有点高，我们的许多朋友都面临着你所面临的问题。他们也会落地你在后期阶段出现性能问题。

我建议你使用DAL生成工具，如TierDeveloper，Codesmith，Codus等来生成DAL层。
从那以后，你将使用实体由数据库创建的对象，因此你的强类型和智能感知目标将会实现。
无论如何自动生成的代码将减少你的开发时间。你在这里要学习的唯一东西是DAO模式，我们创建1个实体类表示1个数据库表，1个工厂类用于对该实体进行操作，1个集合类用于创建实体集合。

我是mys elf是Codus开发团队的一员，也使用过Tierdeveloper。无论大多数都可以免费使用，它们都能很好地工作。

如果你想拥有一个相同的DAL层来访问各种数据库，我建议你先考虑三次，因为它是非常罕见的机会在需要时。如果您确实发现它是必需的，或者使用DAL生成工具生成单独的DAL层，或者使用带有帮助类的Microsoft Enterprise库数据访问应用程序块用于eah类型的数据库。我自己创建了一个项目，它要求我们创建DAL层以支持多种类型的数据库。然后我们创建了一个接口类，它调用了一个工厂类，它最终会在读取所需的数据库连接字符串之后为不同的数据库调用所需的helper classess。配置文件。

希望这会有所帮助。
干杯

Bhaskar
------------- -----------------------
如果这对您有帮助，请将此标记为答案

Hi Dave,

The problem faced by you is faced by many of our friends as the learning curve for OR mapping tools is somewhat high.Also they land you up in performance issues at later stages.

I would recommend you to use DAL generation tools like TierDeveloper,Codesmith,Codus etc to generate DAL layer.
There onwards, you would be using the entity objects created out of database, hence your strong typing and intellisense objectives will be achieved.
Moreoever automated generated code will cut your time of develomement.The only thing which you have to learn here is DAO pattern where we create 1 entity class to represent 1 database table, 1 factory class for operations on this entity and 1 collection class to create collection of entity.

I myself was a party of Codus developement team and have used Tierdeveloper as well. They work nicely moreoever most of them are freely available.


Now if you want to have a same DAL layer to access variety of databases, i would recommend you to first think twice as it is very rare chances when it is required.If you really find it to be required, either
generate seperate DAL layers using DAL generation tools or use Microsoft Enterprise library data access application block with helper classes for eah type of database.
I myself worked on an project which required us to create DAL layer to support multiple type of databases.Then we created an interface class which called a factory class which will ultimately call required helper classess for different databases after reading required database connection string from configuration file.


Hope this helps.
Cheers

Bhaskar
------------------------------------
Please mark this as answer if this helps you

这篇关于从MS Access迁移的复杂数据密集型，数据库不可知系统中的业务/数据层？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！