我什么时候应该使用 Odbc、OleDb、SQLClient？ 有哪些权衡
我从 SQLServer 数据库开始。 所以看来我应该使用 System.Data.SqlClient 命名空间。 但是，我们有可能关闭 SqlServer 数据库并转到 MySql 或 Oracle。 因此，我提出了一套有关 .Net 应用程序如何与数据库通信的标准，以便将来在需要时更容易迁移到不同的数据库系统。
所以这里是标准：
- 如果可能的话使用 ORM（例如 NHibernate）（没有 LINQ，因为它只是 支持SqlServer，但是呢 实体框架及其支持 Oracle 和 MySql？）
- 如果 ORM 太过分了，那么就使用参数化 SQL 查询。
- 仅将存储过程用于长时间运行或复杂的操作 需要执行在 数据库。
这让我想到了我的手头的主要问题。 我应该使用哪个命名空间来编码我的 DAL？
在我看来，选择是在 System.Data.ODBC 和 之间System.Data.OleDB：
- 有哪些权衡？
- 其中一个比另一个更受青睐吗？
- 您对前 3 个标准有何看法？
I am starting off with a SQLServer database. So it would seem that I should use System.Data.SqlClient namespace. But, there is a chance that we might shut down our SqlServer database and go to MySql or Oracle. For this reason, I am coming up with a set of standards on how our .Net apps will communicate with the database, so as to make it easier to migrate to a different database system in the future if we needed to do so.
So here are the standards:
- Use an ORM if possible (eg
NHibernate) (No LINQ as it only
supports SqlServer, but what about
Entity framework and its support for
Oracle and MySql?) - If ORM is an over-kill, then use parameterized SQL queries.
- Use stored procedures only for long running or complex actions that
need to be performed on the
database.
Which brings me to my main question at hand.
Which namespace should I be using to code my DAL?
It looks to me that the choice is between System.Data.ODBC and System.Data.OleDB:
- What are the trade-offs?
- Is one preferred over the other?
- What are your thoughts about the first 3 standards?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
System.Data.SQLClient
仅连接到 SQL Server 2000 及更高版本，但在连接到这些数据库时您将获得最佳性能。
System.Data.OledbClient
连接到 SQL 6.5
OLEDBClient 使您能够连接到其他数据库，例如 ORACLE 或 Access。 但是对于使用 SQL Server，您将使用 SQLClient 获得更好的性能。
注意：为了连接ORACLE，微软也有ORACLEClient。
System.Data.ODBCClient
使用 ODBC 驱动程序仅连接到旧数据库。 （例如 MS Access 97。）
原始来源
System.Data.SQLClient
Connects to SQL Server 2000 and later only, but you will get optimal performance when connecting to those databases.
System.Data.OledbClient
Connects to SQL 6.5
OLEDBClient gives you ability to connect to other database like ORACLE or Access. But for working with SQL Server you will get better performance using SQLClient.
Note: For connecting to ORACLE, Microsoft also has ORACLEClient.
System.Data.ODBCClient
Connects to legacy databases only, using ODBC drivers. (E.g. MS Access 97.)
Original source
您想要使用 SQL Server 驱动程序。 我理解您想要做什么，但是实现支持多个数据库的方法是插入另一层抽象。 您可以通过多种方式做到这一点。 但是您将数据库特定的代码放在类层次结构的边缘。 因此，每个类都可以获得数据库特定功能的好处，但更高级别的调用者不知道或关心底层正在使用什么数据库。 就 ORM 而言，我更喜欢 LLBLGen，但这只是我的偏好。
另外，需要澄清的是，LINQ 并非特定于 SQL Server。 这就是 LINQ 到 SQL。 LINQ 是一种查询技术，可以在 LINQ-to-SQL、LINQ-to-Entities、LINQ-to-objects 中使用，甚至 LLBLGen 也支持 LINQ。
You want to use the SQL Server driver. I understand what you are trying to do but the way you would accomplish supporting multiple databases is by inserting another layer of abstraction. You can do this many ways. But you put the database specific code at the edge of your class hierarchy. Therefore, each class can get the benefits of database specific functionality but the higher level callers don't know or care what database is being used underneath. As far as ORMs, I prefer LLBLGen, but this is just my preference.
Also, just to clarify, LINQ is not specific to SQL Server. That is LINQ-to-SQL. LINQ is a querying technology that you can use in LINQ-to-SQL, LINQ-to-Entities, LINQ-to-objects, and even LLBLGen supports LINQ.
无论您现在使用 SQLClient 还是 Odbc，如果您使用存储过程或其他特定于数据库的功能，则在更改数据库引擎时都必须重写这些功能。
No matter whether you use SQLClient or Odbc for now, if you use stored procedures or other database-specific features, you'll have to rewrite those if you change database engines.
我只是使用 SqlClient 并重新编写/重新生成 DAL（如果它发生了变化）。
除非您现在要在多个平台上实施和测试，否则我不确定现在的额外工作是否重要，或者是否比重做 DAL 的工作少，而且您已经获得了 DAL无论如何，这意味着您已将所有内容集中在一处，以便以后进行更改。
I'd just use SqlClient and re-write/re-generate the DAL if it changed.
Unless you're going to implement and test on multiple platforms right now, I'm not sure the extra effort right now is a big deal or any less than the effort to redo the DAL, and the fact that you've got a DAL at all means you've got everything in one place for a later change anyway.
如果您有任何迹象表明您将交换数据库（或支持多个后端），那么 ORM 就是您的最佳选择。 否则，您仍然需要重构/重写大量 DAL 才能支持更改。 如果你的应用程序很小，那也不错，但是任何实质性的应用程序都会让你受伤。
If you've got any inkling that you'll be swapping databases (or supporting multiple backends) then an ORM is the way to go. Otherwise, you'll still have to refactor/rewrite a lot of your DAL in order to support the change. If your app is small, it won't be bad, but anything substantial and you'll be hurting.
您会发现使用 SQL Server SqlClient 比 OleDB 和 ODBC 更快更容易开发 - 除非您极有可能需要支持多个平台，否则您会发现好处超过了您需要重写的风险你的达尔。
此外，使用 OleDB / ODBC 只是维护平台独立性的一种方法 - 您可能会发现拥有多个 DAL 实现更有效，每个实现都使用所用平台本机的客户端。
You will find that using SQL Server the SqlClient is far quicker and easier to develop with than OleDB and ODBC - unless it is extremely likely that you will need to support multiple platforms you will find that the benefits outweigh the risks that you will need to rewrite your DAL.
Additionally, using OleDB / ODBC is only 1 way of maintaining platform independence - you may find it more effective to have multiple implementations of your DAL, each using a client native to the platform being used.
我听说，除非它是一个关键功能，否则您不应该太担心保持平台独立性。 也就是说，
SQLClient 将为您提供本机访问并且应该具有更高的性能（它不必执行任何抽象/翻译）。
与 ODBC 相比，要使 OLEDB 正常工作，您唯一需要更改的是连接字符串。 OLEDB 具有更多的客户端智能，因此可以提供更好的性能。
I've heard it said that unless it's a key feature, you shouldn't worry too much about maintaining platform independence. That said,
SQLClient will give you native access and should be more performant (it doesn't have to do any abstractions/translations).
The only thing you have to change to get OLEDB working vs. ODBC is your connection string. OLEDB has a little bit more client-side intelligence so it offers better performance.