在运行时重写Sql

本文关键字：Sql 重写 运行时 | 更新日期: 2023-09-27 18:07:26

我有一个带有参数的SQL语句，例如

SELECT * 
FROM persons 
WHERE id = @id 
  AND firstName = @firstname
   OR surname = @surname

我想使用c#根据运行时提供的参数重写语句。

例如，如果在运行时没有提供@firstname，那么我的SQL应该重新写入

SELECT * 
FROM persons 
WHERE id = @id OR surname = @surname

这就是我到目前为止所拥有的。

public class SqlRewriter
{
    public string RewriteSql(List<string> passedRuntimeParameter )
    {
        var sql = @"SELECT * FROM persons 
                 WHERE id=@id AND firstName=@firstname
                 or surname=@surname";
        /**
         * todo parse the sql and compare with passedRuntimeParameter.
         * todo reconstruct the sql based on parsed parameters
         */
        return sql;
    }
}

在运行时重写Sql

IF @firstname = Null {
    SELECT * FROM persons WHERE id=@id or surname=@surname
} else {
    SELECT * FROM persons WHERE id=@id AND firstName=@firstname or surname=@surname
}

你可以改为写：

SELECT * FROM persons WHERE
    (id=@id OR @id is null) AND
    (
        (firstName=@firstname OR @firstname is null) OR
        (surname=@surname OR @surname is null)
    )

当你没有指定的值时发送null

或者，如果你真的想省略这个参数，你可以像mikey或Patrick回答的那样做。

但请记住，如果您真的想根据您拥有的参数进行查询(id和姓氏，或只是firstname或firstname+姓氏

if FirstName==NULL
{
 sqlQuery="ConstructYourQueryfor This condition";
}
else
{
 sqlQuery="Another Construction for else Query";
}

USE SQLCommand参数

using(var connection = new SqlConnection("your connectionstring goes here"))
{
    using(var command = new SqlCommand(sqlQuery,                                                                          connection))
    {
        command.Parameters.AddWithValue("@id ", yourIDvalue);
        command.Parameters.AddWithValue("@firstname ", FirstName);    
        command.Parameters.AddWithValue("@surname", surname);    
        var reader = command.ExecuteReader();
    }
}

您使用的是参数化查询，这很好。你可以使用if块来做你想做的事情，甚至可以使用内联if.

string query = "SELECT * FROM persons WHERE id=@id" + ((firstname!=null&&firstname!=string.Empty) ? " AND firstname=@firstname OR surname=@surname" : " AND surname=@surname");
// setup command with query
// setup parameters (remember to check if you need to provide a firstname parameter or not)

祝好运

相关文章：