什么是最好的做法或坑瀑布，我们需要知道在web服务中心的.NET应用程序使用Microsoft Oracle提供的时候？

What are the best practices or pit falls that we need to be aware of when using Microsoft Oracle provider in a web service centric .NET application?

推荐答案

我们采用基于我们的生产经验，有些做法：

Some practices we employ based on our production experience:

从连接池中获取它们验证连接时。 写您服务code不认为连接是有效的 - 如果不这样做会导致特别是在生产环境中相当多的悲伤 在任何可能的地方，明确地关闭和使用后丢弃的连接（使用（康涅狄格州）{} 块不错的选择） 在一个服务，你应该使用最短的时间内连接 - 特别是如果你正在寻找创建一个可扩展的解决方案。 在考虑使用显式timouts适当的请求的典型持续时间的请求。你想的最后一件事是，有一种类型的要求，即挂起到可能阻止你的整个系统。 尽可能地使用绑定变量，以避免硬解析在数据库（这可能是一个噩梦般的表现，如果你不开始与这种做法）。使用绑定变量也保护你从基本的SQL注入攻击。 确保您有足够的诊断支持内置到您的系统 - 考虑创建围绕甲骨文ADO的包装要求，让您可以仪器，记录，并找到他们的所有 在考虑使用存储过程或视图时，可能推查询语义和数据模型的认识到数据库中。这使得更容易profileing和查询优化。 另外，可以考虑使用一个很好的ORM库（EF，休眠等）封装数据访问 - 特别是如果你同时执行读取和写入操作 扩展以上 - 不要辣椒的code几十个单独写入SQL片段。这迅速成为一个维护的噩梦。 如果您正致力于甲骨文作为数据库，不要害怕使用Oracle特定的功能。该ODP库提供了访问大部分的功能​​ - 例如返回表游标，批量操作等 在甲骨文对待空字符串（）和空值等同 - .NET没有。规范化的字符串处理，适用于甲骨文。 在考虑使用，而不是VARCHAR2 NVARCHAR2如果你在数据库中直接存储统一code .NET字符串。否则，将所有单code字符串，以符合核心ASCII子集。如果不这样做可能会导致各种混乱和邪恶的数据损坏问题。 Validate connections when retrieving them from the connection pool. Write your service code to not assume that connections are valid - failure to do so can cause quite a bit of grief especially in production environments Wherever possible, explicitly close and dispose connections after using them (using(conn){} blocks work well) In a service, you should use connections for the shortest time possible - particularly if you are looking to create a scalable solution. Consider using explicit timouts on requests appropriate to the typical duration of a request. The last thing you want is to have one type of request that hangs to potentially block your whole system. Wherever possible use bind variables to avoid hard parses at the database (this can be a performance nightmare if you don't start out with this practice). Using bind variables also protect you from basic SQL-injection attacks. Make sure you have adequate diagnostic support built into your system - consider creating a wrapper around the Oracle ADO calls so that you can instrument, log, and locate all of them. Consider using stored procedures or views when possible to push query semantics and knowledge of the data model into the database. This allows easier profileing and query tuning. Alternatively, consider use a good ORM library (EF, Hibernate, etc) to encapsulate data access - particularly if you perform both read and write operations. Extending on the above - don't pepper your code with dozens of individually written SQL fragments. This quickly becomes a maintainability nightmare. If you are committed to Oracle as a database, don't be afraid to use Oracle-specific features. The ODP library provides access to most features - such as returning table cursors, batch operations, etc. Oracle treats empty strings ("") and NULLs as equivalent - .NET does not. Normalize your string treatment as appropriate for Oracle. Consider using NVARCHAR2 instead of VARCHAR2 if you will store Unicode .NET string directly in your database. Otherwise, convert all unicode strings to conform to the core ASCII subset. Failure to do so can cause all sorts of confusing and evil data corruption problems.