c# - 如何在 asp.net c# 中通过下拉列表获取最近 3 年的数据？

Question

如何在 asp.net c# 中通过下拉列表获取最近 3 年的数据:

SQL Server 存储过程中的代码:

ALTER PROCEDURE [dbo].[GetAllTransactionsTime]
    (@Time VARCHAR(100),
     @div_id INT)
AS
BEGIN
    DECLARE @SUCCESS SMALLINT,
            @FAILED SMALLINT,
            @ERROR_SEVERITY SMALLINT,
            @ERROR_STATE1 SMALLINT,
            @theErrorMsg NVARCHAR(4000),    
            @theErrorState INT

    BEGIN TRY
    BEGIN TRANSACTION 
        IF (@Time = 'Today') 
        BEGIN
            SELECT *
            FROM [dbo].CandidatesDetail AS c
            WHERE c.is_deleted = 0  
              AND CAST(c.CreatedDate AS DATE) = CAST(GETDATE() AS DATE) 
              AND c.DeptId = @div_id
            ORDER BY CandidateId DESC
        END
        ELSE IF (@Time = 'This Year') 
        BEGIN
            SELECT *
            FROM [dbo].CandidatesDetail AS c
            WHERE c.is_deleted = 0  
              AND YEAR(c.CreatedDate) = YEAR(GETDATE()) 
              AND c.DeptId = @div_id
            ORDER BY CandidateId DESC
        END
        ELSE IF (@Time = 'Last Year') 
        BEGIN
            SELECT *
            FROM [dbo].CandidatesDetail AS c
            WHERE c.is_deleted = 0  
              AND CAST(c.CreatedDate AS DATE) >= DATEADD(YEAR, -1, CASTGETDATE() AS DATE) 
              AND c.DeptId = @div_id
            ORDER BY CandidateId DESC
        END
        ELSE IF (@Time = 'Last 2nd Year') 
        BEGIN
            SELECT *
            FROM [dbo].CandidatesDetail AS c
            WHERE c.is_deleted = 0  
              AND CAST(c.CreatedDate AS DATE) >= DATEADD(YEAR, -2, CASTGETDATE() AS DATE) 
              AND c.DeptId = @div_id
            ORDER BY CandidateId DESC
        END
        ELSE IF (@Time = 'Last 3rd Year') 
        BEGIN
            SELECT *
            FROM [dbo].CandidatesDetail AS c
            WHERE c.is_deleted = 0  
              AND CAST(c.CreatedDate AS DATE) >= DATEADD(YEAR, -3, CASTGETDATE() AS DATE) 
              AND c.DeptId = @div_id
            ORDER BY CandidateId DESC
        END

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH    
        SET @theErrorMsg = error_message()    
        SET @theErrorState = error_state()    

        ROLLBACK TRANSACTION    

        RAISERROR (@theErrorMsg, @ERROR_SEVERITY, @theErrorState)    
        RETURN (@FAILED)    
    END CATCH   
END

C# 后端代码

protected void Dtime_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Dtime.SelectedIndex != 0)
        {

            BindTime(Dtime.SelectedItem.Value);
        }
        else
        {
            rptCandidateDetails.DataSource = null;
            rptCandidateDetails.DataBind();
            rptCandidateDetails.Visible = false;
        }
    }


    private void BindTime(string Time)
    {
        DataSet ds = null;
        CandidateDetails objCandidateDetails = null;
        try
        {
            objCandidateDetails = new CandidateDetails();


            ds = new DataSet();

            if (Session["DeptId"] != null)
            {
                int div_id = Convert.ToInt32(Session["DeptId"].ToString());



                ds = objCandidateDetails.GetAlltransactionsTime(Time, div_id);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    rptCandidateDetails.DataSource = ds;
                    rptCandidateDetails.DataBind();
                    rptCandidateDetails.Visible = true;
                }
                else
                {
                    rptCandidateDetails.DataSource = null;
                    rptCandidateDetails.DataBind();
                    rptCandidateDetails.Visible = false;
                }
            }
        }


        catch (Exception ex)
        {
            ErrorLog.AddErrorLog(ex.Message, ex.Source, ex.StackTrace, ex.TargetSite.ToString());
            throw;
        }
        finally
        {
            if (ds != null)
            {
                ds.Dispose();
                ds = null;
            }
        }
    }

前端代码c#

<asp:DropDownList CssClass="input-large chosen-select" ID="Dtime" OnSelectedIndexChanged="Dtime_SelectedIndexChanged" runat="server" ClientIDMode="Static" AutoPostBack="true" InitialValue="-1">
                                    <asp:ListItem Value="Today" Text="Today"></asp:ListItem>

                                    <asp:ListItem Value="This Year" Text="This Year"></asp:ListItem>
                                    <asp:ListItem Value="Last Year" Text="Last Year"></asp:ListItem> 
                               <asp:ListItem Value="Last 2nd Year" Text="Last 2nd Year"></asp:ListItem>
                                    <asp:ListItem Value="Last 3rd Year" Text="Last 3rd Year"></asp:ListItem> 




</asp:DropDownList>

应用代码

public DataSet GetAlltransactionsTime(string Time, int div_id)
{
     DataSet ds = null;
     try
     {
          SqlParameter[] arParams = new SqlParameter[2];
          m_objconn = new SqlConnection(m_strcon);

          arParams[0] = new SqlParameter("@Time", Time);
          arParams[1] = new SqlParameter("@div_id", div_id);
          ds = SqlHelper.ExecuteDataset(m_objconn, CommandType.StoredProcedure, "GetAlltransactionsTime", arParams);
          return ds;

      }

      catch (Exception ex)
      {
          throw ex;
      }
      finally
      {
          if (ds != null)
          {
              ds.Dispose();
              ds = null;
          }
      }

}

我试过了，但我的输出不正确。帮助我得到我的结果，比如在下拉列表中显示年份的值，通过选择年份，它会显示该特定年份的数据。需要根据年份进行提前搜索。

谢谢

Answer 1

需要对代码进行几处更改。第一件事是去年、第二年和第三年的 near cast 语法不正确，缺少左括号。

请在下面找到更正后的代码:

ALTER PROCEDURE [dbo].[GetAllTransactionsTime]
    (@Time VARCHAR(100),
     @div_id INT)
AS
BEGIN
DECLARE @SUCCESS SMALLINT,
        @FAILED SMALLINT,
        @ERROR_SEVERITY SMALLINT,
        @ERROR_STATE1 SMALLINT,
        @theErrorMsg NVARCHAR(4000),    
        @theErrorState INT

BEGIN TRY
BEGIN TRANSACTION 
    IF (@Time = 'Today') 
    BEGIN
        SELECT *
        FROM [dbo].CandidatesDetail AS c
        WHERE c.is_deleted = 0  
          AND CAST(c.CreatedDate AS DATE) = CAST(GETDATE() AS DATE) 
          AND c.DeptId = @div_id
        ORDER BY CandidateId DESC
    END
    ELSE IF (@Time = 'This Year') 
    BEGIN
        SELECT *
        FROM [dbo].CandidatesDetail AS c
        WHERE c.is_deleted = 0  
          AND YEAR(c.CreatedDate) = YEAR(GETDATE()) 
          AND c.DeptId = @div_id
        ORDER BY CandidateId DESC
    END
    ELSE IF (@Time = 'Last Year') 
    BEGIN
        SELECT *
        FROM [dbo].CandidatesDetail AS c
        WHERE c.is_deleted = 0  
          AND CAST(c.CreatedDate AS DATE) >= DATEADD(YEAR, -1, CAST(GETDATE() AS DATE))
          AND c.DeptId = @div_id
        ORDER BY CandidateId DESC
    END
    ELSE IF (@Time = 'Last 2nd Year') 
    BEGIN
        SELECT *
        FROM [dbo].CandidatesDetail AS c
        WHERE c.is_deleted = 0  
          AND CAST(c.CreatedDate AS DATE) >= DATEADD(YEAR, -2, CAST(GETDATE() AS DATE))
          AND c.DeptId = @div_id
        ORDER BY CandidateId DESC
    END
    ELSE IF (@Time = 'Last 3rd Year') 
    BEGIN
        SELECT *
        FROM [dbo].CandidatesDetail AS c
        WHERE c.is_deleted = 0  
          AND CAST(c.CreatedDate AS DATE) >= DATEADD(YEAR, -3, CAST(GETDATE() AS DATE))
          AND c.DeptId = @div_id
        ORDER BY CandidateId DESC
    END

    COMMIT TRANSACTION
END TRY
BEGIN CATCH    
    SET @theErrorMsg = error_message()    
    SET @theErrorState = error_state()    

    ROLLBACK TRANSACTION    

    RAISERROR (@theErrorMsg, @ERROR_SEVERITY, @theErrorState)    
    RETURN (@FAILED)    
END CATCH   
END