mysql - ASP.NET SQLDataSource SelectParameters 默认值不起作用

Question

我编写了一个简单的 ASP.NET 表单来管理我的 (MySQL) 数据库上的特定查询。此类查询由此存储过程处理:

CREATE DEFINER=`root`@`localhost` PROCEDURE `noleggio_conducenti`(IN _id_convenzionato INT(10))
BEGIN
IF _id_convenzionato IS NOT NULL THEN
    SELECT * FROM
    (
        SELECT 
            *
        FROM
            noleggi AS H
        WHERE
            id_convenzionato = _id_convenzionato 

        UNION 

        SELECT 
            *
        FROM
            noleggi AS H
        WHERE
            id_convenzionato IN (SELECT 
                    id_recipiente
                FROM
                    sosautomotive.transizioni_point
                WHERE
                    id_cedente = _id_convenzionato )
    ) H
    WHERE (H.uscita IS NOT NULL) AND (H.rientro IS NOT NULL);
ELSE
    SELECT 
        *
    FROM
        noleggi
    WHERE (uscita IS NOT NULL) AND (rientro IS NOT NULL);
END IF;
END

如您所见，唯一的参数可以是 NULL ...我希望使用此存储过程为以下 SQLDataSource 对象提供数据:

<asp:SqlDataSource 
    ID="_sdsConducenti" 
    runat="server"
    ConnectionString="<%$ ConnectionStrings:sos_db %>"
    ProviderName="<%$ ConnectionStrings:sos_db.ProviderName %>" 
    SelectCommandType="StoredProcedure"
    SelectCommand="noleggio_conducenti">
    <SelectParameters>
        <asp:Parameter Name="_id_convenzionato" Type="Int32" DefaultValue="" ConvertEmptyStringToNull="true" />
    </SelectParameters>
</asp:SqlDataSource>

向 GridView 提供数据:

<asp:GridView
    ID="_gvConducenti"
    runat="server"
    DataSourceID="_sdsConducenti"
    OnSorting="_gvConducenti_Sorting"
    OnPageIndexChanging="_gvConducenti_PageIndexChanging"
    OnRowDataBound="_gvConducenti_RowDataBound"
    AutoGenerateColumns="false"
    EmptyDataText="Nessun conducente presente."
    BorderStyle="None"
    CellSpacing="0"
    CellPadding="0"
    ShowHeader="true"
    ShowFooter="true"
    AllowSorting="true"
    AllowPaging="true"
    PageSize="10"
    GridLines="Horizontal"
    SelectedIndex="0"
    Style="width: 100%;" 
    HorizontalAlign="Center">
    <SelectedRowStyle CssClass="SelRow" />
    <HeaderStyle CssClass="GridHeader" />
    <AlternatingRowStyle BackColor="#F7F5E9" CssClass="AltRow" />
    <PagerStyle HorizontalAlign="Center" />
    <PagerSettings
        Visible="true"
        Mode="NumericFirstLast"
        PageButtonCount="3"
        Position="Bottom"
        NextPageText="Pagina successiva"
        PreviousPageText="Pagina precedente"
        FirstPageText="Prima pagina"
        LastPageText="Ultima pagina" />
    <Columns>
        <asp:TemplateField Visible="false">
            <HeaderTemplate>&nbsp;</HeaderTemplate>
            <ItemTemplate>
                <%#Eval("idnoleggio")%>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Numero" SortExpression="numero">
            <ItemTemplate>
                <asp:LinkButton 
                    runat="server" 
                    CommandArgument='<%# Eval("idnoleggio")%>' 
                    CommandName="Link" 
                    OnCommand="Link_Command">
                        <%#Eval("numero_completo")%>
                </asp:LinkButton>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField HeaderText="Conducente" DataField="conducente" />
        <asp:BoundField HeaderText="Via" DataField="conducente_via" />
        <asp:BoundField HeaderText="N° Civico" DataField="conducente_num_civico" />
        <asp:BoundField HeaderText="CAP" DataField="conducente_cap" />
        <asp:BoundField HeaderText="Città" DataField="conducente_residente_in" />
        <asp:BoundField HeaderText="Prov." DataField="conducente_residente_in_provincia" />
        <asp:BoundField HeaderText="Convenzionato" DataField="convenzionato" />
        <asp:BoundField HeaderText="Uscita" DataField="uscita" DataFormatString="{0:dd/MM/yyyy HH:mm}" />
        <asp:BoundField HeaderText="Rientro" DataField="rientro" DataFormatString="{0:dd/MM/yyyy HH:mm}" />
        <asp:BoundField HeaderText="Tipologia noleggio" DataField="modalita_noleggio" />
    </Columns>
</asp:GridView>

当然这个表单还有其他字段用于过滤查询，这些字段由这对函数处理:

private void Ricerca()
{
    if (!String.IsNullOrWhiteSpace(_txtConvenzionato.Text.Trim()))
    {
        int _nIdConvenzionato = CUtilita.RitornaIdConvenzionato(_txtConvenzionato.Text);

        if (_nIdConvenzionato != -1)
            _sdsConducenti.SelectParameters["_id_convenzionato"].DefaultValue = _nIdConvenzionato.ToString();
        else
            _sdsConducenti.SelectParameters["_id_convenzionato"].DefaultValue = null;
    }

    _sdsConducenti.FilterExpression = Filter();

}

private string Filter()
{
    StringBuilder _sbFilter = new StringBuilder();

    try
    {
        #region Data fine noleggio

        if (!string.IsNullOrWhiteSpace(_txtDallaDataFineNoleggio.Text.Trim()) &&
            !string.IsNullOrWhiteSpace(_txtAllaDataFineNoleggio.Text.Trim()))
        {
            DateTime _dtDallaDataFineNoleggio = DateTime.Parse(_txtDallaDataFineNoleggio.Text.Trim());
            DateTime _dtAllaDataFineNoleggio = DateTime.Parse(_txtAllaDataFineNoleggio.Text.Trim());

            _sbFilter.AppendFormat("((uscita>='{0:yyyy-MM-dd}') AND (uscita<='{1:yyyy-MM-dd}'))", _dtDallaDataFineNoleggio, _dtAllaDataFineNoleggio);
        }
        else if (string.IsNullOrWhiteSpace(_txtDallaDataFineNoleggio.Text.Trim()) &&
            !string.IsNullOrWhiteSpace(_txtAllaDataFineNoleggio.Text.Trim()))
        {
            DateTime _dtAllaDataFineNoleggio = DateTime.Parse(_txtAllaDataFineNoleggio.Text.Trim());
            _sbFilter.AppendFormat("(uscita<='{0:yyyy-MM-dd}')", _dtAllaDataFineNoleggio);
        }
        else if (!string.IsNullOrWhiteSpace(_txtDallaDataFineNoleggio.Text.Trim()) &&
            string.IsNullOrWhiteSpace(_txtAllaDataFineNoleggio.Text.Trim()))
        {
            DateTime _dtDallaDataFineNoleggio = DateTime.Parse(_txtDallaDataFineNoleggio.Text.Trim());
            _sbFilter.AppendFormat("(uscita>='{0:yyyy-MM-dd}')", _dtDallaDataFineNoleggio);
        }

        #endregion
    }
    catch (FormatException ex)
    {
        _lblStatus.Text = ex.Message;
        _lblStatus.ForeColor = System.Drawing.Color.Red;
    }

    try
    {
        #region Data esportazione

        if (!string.IsNullOrWhiteSpace(_txtDallaDataEstrazione.Text.Trim()) &&
            !string.IsNullOrWhiteSpace(_txtAllaDataEstrazione.Text.Trim()))
        {
            DateTime _dtDallaDataEstrazione = DateTime.Parse(_txtDallaDataEstrazione.Text.Trim());
            DateTime _dtAllaDataEstrazione = DateTime.Parse(_txtAllaDataEstrazione.Text.Trim());

            _sbFilter.AppendFormat("((data_esportazione_conducenti>='{0:yyyy-MM-dd}') AND (data_esportazione_conducenti<='{1:yyyy-MM-dd}'))", _dtDallaDataEstrazione, _dtAllaDataEstrazione);
        }
        else if (string.IsNullOrWhiteSpace(_txtDallaDataEstrazione.Text.Trim()) &&
            !string.IsNullOrWhiteSpace(_txtAllaDataEstrazione.Text.Trim()))
        {
            DateTime _dtAllaDataEstrazione = DateTime.Parse(_txtAllaDataEstrazione.Text.Trim());
            _sbFilter.AppendFormat("(data_esportazione_conducenti<='{0:yyyy-MM-dd}')", _dtAllaDataEstrazione);
        }
        else if (!string.IsNullOrWhiteSpace(_txtDallaDataEstrazione.Text.Trim()) &&
            string.IsNullOrWhiteSpace(_txtAllaDataEstrazione.Text.Trim()))
        {
            DateTime _dtDallaDataEstrazione = DateTime.Parse(_txtDallaDataEstrazione.Text.Trim());
            _sbFilter.AppendFormat("(data_esportazione_conducenti>='{0:yyyy-MM-dd}')", _dtDallaDataEstrazione);
        }

        #endregion
    }
    catch (FormatException ex)
    {
        _lblStatus.Text = ex.Message;
        _lblStatus.ForeColor = System.Drawing.Color.Red;
    }

    #region Tipo Noleggio

    if (!string.IsNullOrWhiteSpace(_ddlTipoNoleggio.SelectedValue) && !_ddlTipoNoleggio.SelectedValue.Equals("TUTTI"))
    {
        if (_sbFilter.Length > 0)
            _sbFilter.Append(" AND");

        _sbFilter.Append("(modalita_noleggio = '" + _ddlTipoNoleggio.SelectedValue + "')");
    }

    #endregion

    if (_sbFilter.Length == 0)
        return null;

    return _sbFilter.ToString();

}

我通过 MySQL Workbench 测试了存储过程，我确信它可以工作(使用有效参数和 NULL)。但是，当我尝试在表单中使用它时，它仅在提供参数值并且然后将值设置为 NULL (即 _sdsConducenti.SelectParameters["_id_convenzionato"].DefaultValue = null) 查询没有提供任何数据(虽然它应该检索大量记录)。我哪里失败了？如何在调用存储过程前检查NULL值是否正确设置为参数值？

完整代码可用here .

Answer 1

尝试在 SQLDataSource 定义中指定以下属性 -

CancelSelectOnNullParameter="false"

它被描述为

true if a data retrieval operation is canceled when a parameter contained in the SelectParameters collection evaluated to null; otherwise, false. The default is true

这里解释了类似的问题——

Set SqlDataSource parameter to null

有关更多信息，请参阅 here-