Razor动态SQL查询

本文关键字：查询 SQL 动态 Razor | 更新日期: 2023-09-27 18:14:28

我有以下c#代码:

var var_db = Database.Open("Intranet") ;
var var_selectCommand = "SELECT Ticket_ID AS [Ticket], Logged_Date AS [Created], Ticket_Type AS [Ticket Type], Department, Priority, Forename, Surname, Ticket_Status AS [Status], Completed_Date AS [Completed], User_Assigned AS [User], Ticket_Subject AS [Subject] FROM Ticket_Data WHERE Ticket_Status = 'Open' ORDER BY Logged_Date DESC";
var var_searchTerm = "";
if(!Request.QueryString["Priority"].IsEmpty() ) {
    var_selectCommand = "SELECT Ticket_ID AS [Ticket], Logged_Date AS [Created], Ticket_Type AS [Ticket Type], Department, Priority, Forename, Surname, Ticket_Status AS [Status], Completed_Date AS [Completed], User_Assigned AS [User], Ticket_Subject AS [Subject] FROM Ticket_Data WHERE Priority = @0 ORDER BY Logged_Date DESC";
    var_searchTerm = Request.QueryString["Priority"];
}
if(!Request.QueryString["Department"].IsEmpty() ) {
    var_selectCommand = "SELECT Ticket_ID AS [Ticket], Logged_Date AS [Created], Ticket_Type AS [Ticket Type], Department, Priority, Forename, Surname, Ticket_Status AS [Status], Completed_Date AS [Completed], User_Assigned AS [User], Ticket_Subject AS [Subject] FROM Ticket_Data WHERE Department = @0 ORDER BY Logged_Date DESC";
    var_searchTerm = Request.QueryString["Department"];
}

我有以下标记:

            <select class="field" id="priority" name="Priority" >
                <option value="">Please choose...</option>
                <option value="Low">Low</option>
                <option value="Medium">Medium</option>
                <option value="High">High</option>
            </select>
            <select class="field" id="department" name="Department" >
                <option value="">Please choose...</option>
                <option value="Department 1">Department 1</option>
                <option value="Department 2">Department 2</option>
                <option value="Department 3">Department 3</option>
                <option value="Department 4">Department 4</option>
                <option value="Department 5">Department 5</option>
                <option value="Department 6">Department 6</option>
            </select>

如果我想通过'优先级'或'部门'搜索，那么这只工作，但我想做的是应用多个搜索条件，并让它动态更改SQL查询，这样我就可以只带回特定优先级和特定部门的结果。

谁能告诉我最好的方法来实现这一点?

Razor动态SQL查询

您可以使用一些字符串连接来构建查询。这应该可以让你开始。

var selectStatement = "SELECT Ticket_ID AS [Ticket], Logged_Date AS [Created], Ticket_Type AS [Ticket Type], Department, Priority, Forename, Surname, Ticket_Status AS [Status], Completed_Date AS [Completed], User_Assigned AS [User], Ticket_Subject AS [Subject] FROM Ticket_Data ";
List<string> whereConditions = new List<string>();
if(!Request.QueryString["Priority"].IsEmpty() ) {
    whereConditions.Add(" Priority = yourPriorityParameterHere ");
    //.....
}
if(!Request.QueryString["Department"].IsEmpty() ) {
    whereConditions.Add(" Department = yourDepartmentParameterHere ");
    //.....
}
var commandText = selectClause + " WHERE "+ string.Join(" AND ", whereConditions) + " ORDER BY LoggedDate DESC";
//......
//Fill in parameters, execute sql, live happy
相关文章：