存储过程 查看基于筛选器的所有记录不接受空值

Question

我有一个存储客户记录的表，我想过滤这些客户，但我在将空值放入 sproc 时遇到问题。这些值有时可能是空的，我在 sql 中对其进行了测试并且它可以工作，但它不会接受来自后面的 c# 的空值。我尝试了if语句，结果是一样的。

任何帮助表示赞赏

您可以查看下面的代码

.SQL

WHERE ( ProcessStatus.ProcessStatusKey = @ProcessStatusKey OR @ProcessStatusKey IS NULL )
    AND
    ( PriorityLevels.PriorityLevelKey = @PriorityLevelKey OR @PriorityLevelKey IS NULL )
    AND
    ( SystemUsers.SystemUserKey = @SystemUserKey OR @SystemUserKey IS NULL )
    AND
    ( CaseHoganData.LoanAccountNumber = @AccountNumber OR @AccountNumber IS NULL )
    AND
    ( ( LTRIM(RTRIM(( ISNULL(Customers.FirstName, '') + ' ' + ISNULL(Customers.LastName, '') ))) ) LIKE '%'
    + @CustomerName + '%'OR @CustomerName IS NULL )

ASP.NET

    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("sp_Cases_ViewAll", con))
        {
            //if (prioritylev == "")
            //    cmd.Parameters.AddWithValue("@PriorityLevelKey", DBNull.Value);
            //else

            cmd.Parameters.AddWithValue("@PriorityLevelKey", (string.IsNullOrEmpty(prioritylev) ? (Guid?)null : new Guid(prioritylev)));
            cmd.Parameters.AddWithValue("@ProcessStatusKey", (string.IsNullOrEmpty(processkey) ? (Guid?)null : new Guid(processkey)));
            cmd.Parameters.AddWithValue("@SystemUserKey", (string.IsNullOrEmpty(systemuser) ? (Guid?)null : new Guid(systemuser)));
            cmd.Parameters.AddWithValue("@AccountNumber", accnumber);
            cmd.Parameters.AddWithValue("@CustomerName", cusname);
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                sda.Fill(dt);
                grid_Cases.DataSource = dt;
                grid_Cases.DataBind();
            }
        }
    }

编辑这对我有用

cmd.Parameters.AddWithValue("@PriorityLevelKey", (string.IsNullOrEmpty(prioritylev) ? (object)DBNull.Value : new Guid(prioritylev)));

Answer 1

您在注释代码"DBNull.value"上走在正确的道路上，这是您更改的代码：

            using (SqlCommand cmd = new SqlCommand("sp_Cases_ViewAll", con))
            {
                //if (prioritylev == "")
                //    cmd.Parameters.AddWithValue("@PriorityLevelKey", DBNull.Value);
                //else

                if (string.IsNullOrEmpty(prioritylev))
                {
                    cmd.Parameters.AddWithValue("@PriorityLevelKey", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@PriorityLevelKey", new Guid(prioritylev));
                }

                if(string.IsNullOrEmpty(processkey))
                {
                    cmd.Parameters.AddWithValue("@ProcessStatusKey", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@ProcessStatusKey", new Guid(processkey));
                }

                if (string.IsNullOrEmpty(systemuser))
                {
                    cmd.Parameters.AddWithValue("@SystemUserKey", DBNull.Value);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@SystemUserKey",  new Guid(systemuser));
                }

                cmd.Parameters.AddWithValue("@AccountNumber", accnumber);
                cmd.Parameters.AddWithValue("@CustomerName", cusname);
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    DataTable dt = new DataTable();
                    sda.Fill(dt);
                    grid_Cases.DataSource = dt;
                    grid_Cases.DataBind();
                }
            }
        }

Answer 2

NULL 由 DBNull 类表示：

https://msdn.microsoft.com/en-us/library/system.dbnull(v=vs.110(.aspx

当不存在任何值时，请使用DBNull.Value。