无法从c#代码向存储过程传递sql Date类型的参数

本文关键字:sql Date 类型 参数 存储过程 代码 | 更新日期: 2023-09-27 18:14:51

我有一个通过比较两个日期来选择记录的存储过程。从c#代码,我传递参数到存储过程,但得到错误

将参数值从字符串转换为日期时间失败。

我的SQL Server存储过程是:

ALTER PROCEDURE Select  
    @fromDt date = null,
    @toDt date = null,
    @flag int
AS
BEGIN   
    SET NOCOUNT ON;
    if @flag = 1
    begin   
        SELECT xxx,xxx,xxx from xxx where Status <> 'Complete'
    end
    else
    begin
        SELECT xxx,x,xxx from xxx where Status <> 'Complete' and Date >= @fromDt and Date < @toDt
    end
END
GO

我的代码查询表是:

 protected void btnFilter_Click(object sender, EventArgs e)
 {
    DoProcess(Convert.ToString(dtFrom.SelectedDate.ToString("yyyyMMdd")), Convert.ToString(dtTo.SelectedDate.ToString("yyyyMMdd")), 0);
 }
 private void DoProcess(string fromDt,string toDt, int flag)
 {
    SqlConnection connection = GeneralMethods.GetConnection();
    SqlCommand selectCommand = new SqlCommand();
    DataTable dtVendors = new DataTable();
    try 
    {
        // set command object properties
        selectCommand.Connection = connection;
        selectCommand.CommandType = System.Data.CommandType.StoredProcedure;
        selectCommand.CommandText = "Select";
        // check if from date and to dates are not null then set parameters to stored procedure.
        if (!string.IsNullOrEmpty(fromDt))
        {                        
            selectCommand.Parameters.Add("@fromDt", SqlDbType.Date).Value = fromDt;
            selectCommand.Parameters.Add("@toDt", SqlDbType.Date).Value = toDt;
        }
        // @flag is 1 then do not compare dates if 0 then compare to and from dates.
        selectCommand.Parameters.Add("@flag", SqlDbType.Int).Value = flag;
        // open the connection execute the select command.
        connection.Open();
        // load data into data table.
        dtVendors.Load(selectCommand.ExecuteReader());
        connection.Close();
        if (dtVendors.Rows.Count > 0)
        {       
            grdVendors.DataSource = dtVendors;
            grdVendors.DataBind();                    
        }
    }
    catch (Exception ex)
    { }
    finally
    {
        // finally close the connection if open and release the resources
        if (connection.State == System.Data.ConnectionState.Open)
            connection.Close();
        connection.Dispose();
        selectCommand.Dispose();
        dtVendors.Dispose();
    }
}

我该如何处理这个错误?

无法从c#代码向存储过程传递sql Date类型的参数

为什么将日期参数作为字符串传递?

selectCommand.Parameters.Add("@fromDt", SqlDbType.Date).Value = DateTime.Parse(fromDt);

或更好

selectCommand.Parameters.Add("@fromDt", SqlDbType.Date, DateTime.Parse(fromDt));