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.
在我的第一个c#项目中,我需要连接到数据库服务器以进行多个只读查询。有没有人分享如何将查询组织到项目中的经验?目前我只需要在c#源文件中硬编码查询字符串。但是很难维护,一旦数据库服务器端发生了变化,我就遇到了麻烦。或者我应该使用appsettings将所有查询字符串放在.config文件中?还有更好的方法吗?我无权在服务器上保存存储过程。谢谢。
2 个解决方案
#1
4
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.
根据您的需求,有不同的答案和不同的复杂程度。除了最小的项目之外,我创建了两个用于数据库访问的类库项目:一个包含数据模型和查询,另一个测试项目用于执行第一个项目的查询。在简单的解决方案中,您可以在ASP.NET或其他项目中使用此库。
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.
您应该强烈考虑学习像NHibernate或VS 2008 / .NET 3.5的Linq-To-SQL或Entity Framework这样的ORM。最低限度,如果您有面向Web的应用程序,则必须记住使用参数化查询。
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.
在更复杂的解决方案中,您将完全将数据库封装到自己的服务或层中。根据我的经验,我有一个数据访问层,它作为Windows服务运行在自己的Windows Communication Foundation服务中,它是唯一可以直接与数据库通信或了解数据库数据模型的服务。它将完成与数据库的所有交互,然后将数据转换为其他层读取的不同数据模型。我通常会创建一个名为“Contracts”的项目,其中包含从数据层传递到系统其余部分的所有接口和数据模型。这样做的原因是为了避免您提到的痛苦:您可以更新底层数据库,ORM层和“通用数据模型”,然后根本不更改其他层。
#2
2
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.
也就是说,如果您的查询不希望更改它的参数,则可以使用存储过程。这种方法还有助于提升您的查询,因为执行计划将保留在数据库中。
#1
4
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.
根据您的需求,有不同的答案和不同的复杂程度。除了最小的项目之外,我创建了两个用于数据库访问的类库项目:一个包含数据模型和查询,另一个测试项目用于执行第一个项目的查询。在简单的解决方案中,您可以在ASP.NET或其他项目中使用此库。
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.
您应该强烈考虑学习像NHibernate或VS 2008 / .NET 3.5的Linq-To-SQL或Entity Framework这样的ORM。最低限度,如果您有面向Web的应用程序,则必须记住使用参数化查询。
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.
在更复杂的解决方案中,您将完全将数据库封装到自己的服务或层中。根据我的经验,我有一个数据访问层,它作为Windows服务运行在自己的Windows Communication Foundation服务中,它是唯一可以直接与数据库通信或了解数据库数据模型的服务。它将完成与数据库的所有交互,然后将数据转换为其他层读取的不同数据模型。我通常会创建一个名为“Contracts”的项目,其中包含从数据层传递到系统其余部分的所有接口和数据模型。这样做的原因是为了避免您提到的痛苦:您可以更新底层数据库,ORM层和“通用数据模型”,然后根本不更改其他层。
#2
2
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.
也就是说,如果您的查询不希望更改它的参数,则可以使用存储过程。这种方法还有助于提升您的查询,因为执行计划将保留在数据库中。