从c#方法可选参数构建的SQL查询

本文关键字：构建 SQL 查询 参数 方法 | 更新日期: 2023-09-27 18:03:50

我有一个应用程序，我需要允许用户选择一个月/年/用户的能力，然后用这些结果做一个SQL查询。难倒我的是，我想让每个参数都是可选的。

如果我选择了一个用户，但没有日期信息，我希望看到该用户所有时间的所有结果。如果我只选择一个月，我希望显示该月的当前年份，等等。我用这个代码让它与month/user一起工作:

    protected void btnView_Click(object sender, EventArgs e)
    {
        gvbind(Convert.ToInt32(ddlMonth.SelectedValue), ddlWebmasters.SelectedValue.ToString(), Convert.ToInt32(ddlYear.SelectedValue));
    }
    protected void gvbind(int month = 0, string user = "")
    {
        string query = "Select * from WebLogs w inner join(select max(ID) as id, username from WebLogs group by Username) w2 on w.id = w2.id and w.Username = w2.username where Year(LogEntryDate) = year(getDate())";
        if (user.Length > 0 && month > 0)
        {
            query = "SELECT * FROM [WebLogs] WHERE ([Username] = '" + user + "') AND month(LogEntryDate) =" + month + " AND year(LogEntryDate) = year(getdate()) ORDER BY [ID]";
        }
        else if (user.Length > 0 && month == 0)
        {
            query = "SELECT * FROM [WebLogs] WHERE ([Username] = '" + user + "') AND year(LogEntryDate) = year(getdate()) ORDER BY [ID]";
        }
        else if (user.Length == 0 && month > 0)
        {
            query = "SELECT * FROM [WebLogs] WHERE month(LogEntryDate) =" + month + "AND year(LogEntryDate) = year(getdate())  ORDER BY [ID]";
        }

将查询传递到SqlCommand，但现在我添加了year，这真的很低效，我知道必须有一个简单的方法来处理这个

从c#方法可选参数构建的SQL查询

protected void gvbind(int month = 0, string user = "")
    {
        //string query = "Select * from WebLogs w inner join(select max(ID) as id, username from WebLogs group by Username) w2 on w.id = w2.id and w.Username = w2.username where Year(LogEntryDate) = year(getDate())";
        string query = string.Empty;
        query = "SELECT * FROM [WebLogs] WHERE "+((user.Length>0)?"([Username] = '" + user + "') AND ":"") +   ((month>0)?" month(LogEntryDate) =" + month+ "AND ":"") + " year(LogEntryDate) = year(getdate()) ORDER BY [ID]";
    }
相关文章：