I'm working on an existing ASP.NET application. The current application uses a lot of inline queries. Now they want to rewrite all the queries into stored procedures only.
我正在开发一个现有的ASP。网络应用程序。当前应用程序使用许多内联查询。现在，他们希望将所有查询重写为存储过程。
My problem is, these queries are very "dynamic" and the queries are concatenated based on different if...else conditions, for example:
我的问题是，这些查询是非常“动态的”，查询是基于不同的if…其他条件,例如:
string query = "Select * from EmpTable WHERE EmpType ='ACTIVE'";
if (conditionA == true)
query += "AND ID = 12345 ";
if (conditionB == true)
query += "AND Dept = 'Finance' ";
else
query += "AND Dept <> 'Finance' ";
if (conditionC == true)
query += "Order by EmpID";
else if (ConditionD == true)
query += "Order by Dept";
They also want to avoid using dynamic query. What are my options?
他们还希望避免使用动态查询。我的选择是什么?
Edited: I know I can also build dynamic query using stored procedures, I am just wondering what are some other "less pain" options out there.
编辑:我知道我也可以使用存储过程构建动态查询，我只是想知道还有哪些“更省事”的选项。
1 个解决方案
#1
3
Pass in all the parameters you might need, and use the IsNull and/or When clauses to create the same query as in the original program.
传递您可能需要的所有参数，并使用IsNull和/或When子句创建与原始程序相同的查询。
Alternatively you can simply build the query dynamically in the sproc itself or simply create a query for each permutation. Not necessarily fun, or clever but works and makes things easier to maintain in the future - especially when you can use it as a stepping stone for future refactoring of the sprocs.
或者，您可以在sproc本身中动态构建查询，或者为每个置换创建查询。不一定很有趣，也不一定很聪明，但可以让事情在将来更容易维护——尤其是当您可以将它用作sprocs未来重构的垫脚石时。
Edit: there is one more reason to simply convert them all to sprocs - when future devs come along and want to add some SQL, they'll follow the convention and create a new sproc themselves. I imagine one reason your code is littered with dynamic SQL is because it is already littered with dynamic SQL. Maybe over time you can improve them (slap legacy, must fix at the top of each convert) and you'll get them to fix up the design themselves too.
编辑:还有一个简单地将它们转换为sproc的理由——当将来的开发人员想要添加一些SQL时，他们将按照约定创建一个新的sproc。我想，您的代码中充斥着动态SQL的一个原因是，它已经充斥了动态SQL。也许随着时间的推移，你可以改进它们(一巴掌，必须在每个转换的顶部固定)，你也可以让他们自己来修复设计。
#1
3
Pass in all the parameters you might need, and use the IsNull and/or When clauses to create the same query as in the original program.
传递您可能需要的所有参数，并使用IsNull和/或When子句创建与原始程序相同的查询。
Alternatively you can simply build the query dynamically in the sproc itself or simply create a query for each permutation. Not necessarily fun, or clever but works and makes things easier to maintain in the future - especially when you can use it as a stepping stone for future refactoring of the sprocs.
或者，您可以在sproc本身中动态构建查询，或者为每个置换创建查询。不一定很有趣，也不一定很聪明，但可以让事情在将来更容易维护——尤其是当您可以将它用作sprocs未来重构的垫脚石时。
Edit: there is one more reason to simply convert them all to sprocs - when future devs come along and want to add some SQL, they'll follow the convention and create a new sproc themselves. I imagine one reason your code is littered with dynamic SQL is because it is already littered with dynamic SQL. Maybe over time you can improve them (slap legacy, must fix at the top of each convert) and you'll get them to fix up the design themselves too.
编辑:还有一个简单地将它们转换为sproc的理由——当将来的开发人员想要添加一些SQL时，他们将按照约定创建一个新的sproc。我想，您的代码中充斥着动态SQL的一个原因是，它已经充斥了动态SQL。也许随着时间的推移，你可以改进它们(一巴掌，必须在每个转换的顶部固定)，你也可以让他们自己来修复设计。