c#/.net项目如何保存/组织数据库查询
在我的第一个 C# 项目中，我需要连接到数据库服务器以进行多个只读查询。有人会分享如何将查询组织到项目中的经验吗？目前，我只是在需要时在 C# 源文件中硬编码查询字符串。但它很难维护，一旦数据库服务器端发生变化，我就会遇到麻烦。或者我应该使用 appsettings 将所有查询字符串放入 .config 文件中？还有更好的方法吗？我无权在服务器上保存存储过程。谢谢。
In my first c# project, I need to connect to a database server for multiple read only queries. Would anyone share experiences on how to organize the queries into the project? currently I just hardcoded query strings in the c# source files whenever needed. but it is hard to maintain and once something changes on the database server side I am in trouble. Or should I put all query strings in the .config file using appsettings? Are there better ways? I do not have rights to save stored procedures on the server. thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
根据您的需求，有不同复杂程度的不同答案。除了最小的项目之外，我创建了两个用于数据库访问的类库项目：一个包含数据模型和查询，另一个测试项目则执行第一个项目的查询。在简单的解决方案中，您可以在 ASP.NET 或其他项目中使用此库。
您应该强烈考虑学习 ORM，例如 NHibernate 或 VS 2008/.NET 3.5 的 Linq-To-SQL 或 实体框架。至少，您必须记住使用
在更复杂的解决方案中，您将把数据库完全封装到它的自己的服务或层中。根据我的经验，我有一个数据访问层，它作为 Windows 服务在它自己的 Windows Communication Foundation 服务中运行，并且它是唯一可以直接与数据库通信或了解数据库的数据模型的服务。它将完成与数据库的所有交互，然后将数据转换为其他层读取的不同数据模型。我通常创建一个名为“Contracts”的项目，其中包含从数据层通信到系统其余部分的所有接口和数据模型。这样做的原因是为了避免您提到的痛苦：您可以更新底层数据库、ORM 层和“通用数据模型”，然后根本不更改其他层。
There are different answers with varying levels of sophistication based on your needs. Except in the very smallest of projects, I create two class library projects for database access: one that contains the data model and queries and another test project that exercises the first project's queries. In simple solutions, you use this library in an ASP.NET or other project.
You should strongly consider learning an ORM like NHibernate or VS 2008/.NET 3.5's Linq-To-SQL or Entity Framework. Minimally, you MUST remember to use parameterized queries if you have a web-facing app.
In more sophisticated solutions you will completely encapsulate the database into it's own service, or tier. In my experience I had a data access tier that ran in it's own Windows Communication Foundation service, as a Windows Service, and it was the only service that could talk directly to the database or knew the database's data model. It would do all the interaction with the database, and then transform the data into different data models that are read by the other tiers. I typically create a project called "Contracts" that contains all the interfaces and data models that are communicated from the data tier to the rest of the system. The reason you do this is so that you avoid the pain you have mentioned: you can update the underlying database, ORM layer, and "common data models" and then not change the other tiers at all.
如果这是您的第一个项目，请尽量保持简单的想法。如果您添加太多变量，您可能会更多地考虑技术而不是解决方案。
也就是说，如果您的查询不希望更改其参数，则可以使用存储过程。这种方法还有助于提高查询速度，因为执行计划将保存在数据库中。
If this is your first project, try to keep thinks simple. If you add too much variables probably you'll end thinking more in technology than in solutions.
That said, if your queries don't expect to change it's parameters, you can use stored procedures. This approach also will help boost your queries as the execution plan will be kept in the database.