为了实现报表，写了一个日期选择功能。可以参考下图：
下面内容是在2011年8月21日更新添加的：
实现这个功能，需要在数据库准备星期与月份的数据：
星期函数：
月份函数：
准备好数据源之后，当然是写存储过程了，Insus.NET会以星期与月份分开写出来,首先是星期：
usp_Week_GetByDate SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [ dbo ] . [ usp_Week_GetByDate ] ( @Date DATETIME ) AS DECLARE @Year INT = YEAR ( @Date ) SELECT * FROM udf_Week( @Year , @Year ) WHERE CONVERT ( NVARCHAR ( 100 ), @Date , 23 ) BETWEEN [ StartDate ] and [ EndDate ]
usp_Week_GetByYear SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [ dbo ] . [ usp_Week_GetByYear ] ( @StartYear INT , @EndYear INT ) AS SELECT * FROM [ dbo ] . [ udf_Week ] ( @StartYear , @EndYear )
usp_Week_GetByYearAndWeek SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [ dbo ] . [ usp_Week_GetByYearAndWeek ] ( @Year INT , @Week INT ) AS SELECT * FROM udf_Week( @Year , @Year ) WHERE [ Week ] = @Week
下面的是月份的存储过程：
usp_Month_GetByDate SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [ dbo ] . [ usp_Month_GetByDate ] ( @Date DATETIME ) AS DECLARE @Year INT = YEAR ( @Date ) SELECT * FROM udf_Month( @Year , @Year ) WHERE CONVERT ( NVARCHAR ( 100 ), @Date , 23 ) BETWEEN [ StartDate ] and [ EndDate ]
usp_Month_GetByYear SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [ dbo ] . [ usp_Month_GetByYear ] ( @StartYear INT , @EndYear INT ) AS SELECT * FROM [ dbo ] . [ udf_Month ] ( @StartYear , @EndYear )
usp_Moth_GetByYearAndMonth SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [ dbo ] . [ usp_Moth_GetByYearAndMonth ] ( @Year INT , @Month INT ) AS SELECT * FROM udf_Month( @Year , @Year ) WHERE [ Month ] = @Month
最后还再写一个存储过程，就是可浏览几年前的数据：
usp_HistoryYear ALTER PROCEDURE [ dbo ] . [ usp_HistoryYear ] ( @Years INT ) AS BEGIN IF OBJECT_ID ( ' dbo.#HistoryYear ' ) IS NOT NULL DROP TABLE dbo.#HistoryYear CREATE TABLE #HistoryYear ( YearId INT IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL , [ Year ] INT NOT NULL ) BEGIN DECLARE @EndYear INT = ( SELECT Year ( GETDATE ())) DECLARE @StartYear INT = ( @EndYear - ( @Years - 1 )) WHILE @StartYear <= @EndYear BEGIN INSERT INTO #HistoryYear( [ Year ] ) VALUES ( @StartYear ) SET @StartYear = @StartYear + 1 END END END SELECT * FROM #HistoryYear ORDER BY [ YearId ] DESC
下面是星期与月份以及年份三个类别的实现。
Week using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; /// <summary> /// Summary description for Week /// </summary> namespace Insus.NET{ public class Week { BusinessBase objBusinessBase = new BusinessBase(); public Week() { // // TODO: Add constructor logic here // } public DataTable GetWeek( string startYear, string endYear) { Parameter[] parameter = { new Parameter ( " @StartYear " ,SqlDbType.Int , 4 ,ConvertData.ToInt(startYear)), new Parameter ( " @EndYear " ,SqlDbType.Int , 4 ,ConvertData.ToInt(endYear)) }; return objBusinessBase.GetDataToDataSet( " usp_Week_GetByYear " , parameter).Tables[ 0 ]; } public DataTable GetWeekByYearAndWeek( string year, string week) { Parameter[] parameter = { new Parameter ( " @Year " ,SqlDbType.Int , 4 ,ConvertData.ToInt(year)), new Parameter ( " @Week " ,SqlDbType.Int , 4 ,ConvertData.ToInt(week)) }; return objBusinessBase.GetDataToDataSet( " usp_Week_GetByYearAndWeek " , parameter).Tables[ 0 ]; } public DataTable GetWeekByDate(DateTime date) { Parameter[] parameter = { new Parameter ( " @Date " ,SqlDbType.DateTime , 8 ,date) }; return objBusinessBase.GetDataToDataSet( " usp_Week_GetByDate " , parameter).Tables[ 0 ]; } }}
Month using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; /// <summary> /// Summary description for Month /// </summary> namespace Insus.NET{ public class Month { BusinessBase objBusinessBase = new BusinessBase(); public Month() { // // TODO: Add constructor logic here // } public DataTable GetMonth( string startYear, string endYear) { Parameter[] parameter = { new Parameter ( " @StartYear " ,SqlDbType.Int , 4 ,ConvertData.ToInt(startYear)), new Parameter ( " @EndYear " ,SqlDbType.Int , 4 ,ConvertData.ToInt(endYear)) }; return objBusinessBase.GetDataToDataSet( " usp_Month_GetByYear " , parameter).Tables[ 0 ]; } public DataTable GetMonthByYearAndMonth( string year, string month) { Parameter[] parameter = { new Parameter ( " @Year " ,SqlDbType.Int , 4 ,ConvertData.ToInt(year)), new Parameter ( " @Month " ,SqlDbType.Int , 4 ,ConvertData.ToInt(month)) }; return objBusinessBase.GetDataToDataSet( " usp_Moth_GetByYearAndMonth " , parameter).Tables[ 0 ]; } public DataTable GetMonthByDate(DateTime date) { Parameter[] parameter = { new Parameter ( " @Date " ,SqlDbType.DateTime , 8 ,date) }; return objBusinessBase.GetDataToDataSet( " usp_Month_GetByDate " , parameter).Tables[ 0 ]; } }}
Year using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; /// <summary> /// Summary description for Year /// </summary> namespace Insus.NET{ public class Year { BusinessBase objBusinessBase = new BusinessBase(); public Year() { // // TODO: Add constructor logic here // } public DataTable GetHistoryYear( int years) { Parameter[] parameter = { new Parameter ( " @Years " ,SqlDbType.Int , 4 ,years) }; return objBusinessBase.GetDataToDataSet( " usp_HistoryYear " , parameter).Tables[ 0 ]; } }}
接下来，我们可以写三个用户控件UserControl,这个页需要时，接进去就可以了。
DateSelector.ascx <% @ Control Language = " C# " AutoEventWireup = " true " CodeFile = " DateSelector.ascx.cs " Inherits = " System_SystemControl_DateSelector " %> < table id = " Table1 " runat = " server " cellpadding = " 0 " cellspacing = " 0 " border = " 0 " width = " 100% " > < tr > < td width = " 45px " > 日期： </ td > < td align = " left " > < asp:TextBox ID = " txtStartDate " runat = " server " CssClass = " textbox " Width = " 70px " ></ asp:TextBox > < img id = " img2 " src = ' <%= ResolveUrl("~/System/Image/Calendar.gif")%> ' border = " 0 " /> < ajaxToolkit:TextBoxWatermarkExtender ID = " TextBoxWatermarkExtender3 " runat = " server " TargetControlID = " txtStartDate " WatermarkText = " 开始日期 " WatermarkCssClass = " watermarked " > </ ajaxToolkit:TextBoxWatermarkExtender > < ajaxToolkit:CalendarExtender ID = " CalendarExtender2 " runat = " server " TargetControlID = " txtStartDate " PopupButtonID = " img2 " Format = " yyyy-MM-dd " > </ ajaxToolkit:CalendarExtender > < asp:CompareValidator ID = " CompareValidator2 " runat = " server " ControlToValidate = " txtStartDate " Operator = " DataTypeCheck " Type = " date " Display = " None " ErrorMessage = " 开始日期栏位，非有效日期！ " ></ asp:CompareValidator > < asp:RequiredFieldValidator ID = " RequiredFieldValidator2 " runat = " server " ControlToValidate = " txtStartDate " Display = " none " ErrorMessage = " 填写开始日期。 " ></ asp:RequiredFieldValidator > 至 < asp:TextBox ID = " txtEndDate " runat = " server " CssClass = " textbox " Width = " 70px " ></ asp:TextBox > < img id = " img3 " src = ' <%= ResolveUrl("~/System/Image/Calendar.gif")%> ' border = " 0 " /> < ajaxToolkit:TextBoxWatermarkExtender ID = " TextBoxWatermarkExtender4 " runat = " server " TargetControlID = " txtEndDate " WatermarkText = " 结束日期 " WatermarkCssClass = " watermarked " > </ ajaxToolkit:TextBoxWatermarkExtender > < ajaxToolkit:CalendarExtender ID = " CalendarExtender3 " runat = " server " TargetControlID = " txtEndDate " PopupButtonID = " img3 " Format = " yyyy-MM-dd " > </ ajaxToolkit:CalendarExtender > < asp:CompareValidator ID = " CompareValidator3 " runat = " server " ControlToValidate = " txtEndDate " Operator = " DataTypeCheck " Type = " date " Display = " None " ErrorMessage = " 结束日期栏位，非有效日期！ " ></ asp:CompareValidator > < asp:RequiredFieldValidator ID = " RequiredFieldValidator3 " runat = " server " ControlToValidate = " txtEndDate " Display = " none " ErrorMessage = " 填写结束日期。 " ></ asp:RequiredFieldValidator > < asp:CompareValidator ID = " CompareValidator1 " runat = " server " ControlToValidate = " txtEndDate " ControlToCompare = " txtStartDate " Display = " None " ErrorMessage = " 结束日期必须大于或等于开始日期！ " Operator = " GreaterThanEqual " Type = " Date " ></ asp:CompareValidator > </ td > </ tr > </ table >
DateSelector.ascx.cs using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using Insus.NET; public partial class System_SystemControl_DateSelector : System.Web.UI.UserControl;{ protected void Page_Load( object sender, EventArgs e) { } public TextBox StartDate { get { return this .txtStartDate; } } public TextBox EndDate { get { return this .txtEndDate; } }}
WeekSelector.ascx <% @ Control Language = " C# " AutoEventWireup = " true " CodeFile = " WeekSelector.ascx.cs " Inherits = " System_SystemControl_WeekSelector " %> < table id = " Table1 " runat = " server " cellpadding = " 0 " cellspacing = " 0 " border = " 0 " width = " 100% " > < tr > < td width = " 45px " > 年份： </ td > < td align = " left " > < asp:DropDownList ID = " ddlYear " runat = " server " AutoPostBack = " true " OnSelectedIndexChanged = " ddlYear_SelectedIndexChanged " > </ asp:DropDownList > & nbsp; & nbsp;当前第 < asp:Label ID = " lblCurrentWeek " runat = " server " Text = "" ForeColor = " Red " ></ asp:Label > 周( < asp:Label ID = " lblDate " runat = " server " Text = "" ></ asp:Label > ) </ td > </ tr > < tr > < td width = " 45px " valign = " top " > < div style = " height: 5px; " > </ div > 周别： </ td > < td align = " left " > < asp:DataList ID = " dlWeek " runat = " server " DataKeyField = " WeekId " RepeatColumns = " 13 " RepeatDirection = " Horizontal " > < ItemTemplate > < asp:CheckBox ID = " CheckBox1 " runat = " server " Text = ' <%# Eval("Week") %> ' ToolTip = ' <%# Eval("StartDate", "{0:yyyy-MM-dd}") + "～" + Eval("EndDate", "{0:yyyy-MM-dd}") %> ' /> </ ItemTemplate > </ asp:DataList > </ td > </ tr > </ table >
WeekSelector.ascx.cs using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using Insus.NET; public partial class System_SystemControl_WeekSelector : System.Web.UI.UserControl Week objWeek = new Week(); Year objYear = new Year(); protected void Page_Load( object sender, EventArgs e) { if ( ! IsPostBack) { Data_Binding(); } } private void Data_Binding() { objListControlUtility.DropDownListParse(ddlYear, objYear.GetHistoryYear( 5 ), " Year " , " YearId " ); DataRow dataRow = objWeek.GetWeekByDate(DateTime.Now.Date).Rows[ 0 ]; this .lblCurrentWeek.Text = dataRow[ " week " ].ToString(); this .lblDate.Text = objInsusDateTimeUtility.GetDateTime(dataRow[ " StartDate " ], " yyyy-MM-dd " ) + " ～ " + objInsusDateTimeUtility.GetDateTime(dataRow[ " EndDate " ], " yyyy-MM-dd " ); WeekBinding(); } public DropDownList DropDownListYear { get { return this .ddlYear; } } public DataList DataListWeek { get { return this .dlWeek; } } protected void ddlYear_SelectedIndexChanged( object sender, EventArgs e) { WeekBinding(); } private void WeekBinding() { string year = ddlYear.SelectedItem.Text; this .dlWeek.DataSource = objWeek.GetWeek(year, year); this .dlWeek.DataBind(); }}
MonthSelector.ascx <% @ Control Language = " C# " AutoEventWireup = " true " CodeFile = " MonthSelector.ascx.cs " Inherits = " System_SystemControl_MonthSelector " %> < table id = " Table1 " runat = " server " cellpadding = " 0 " cellspacing = " 0 " border = " 0 " width = " 100% " > < tr > < td width = " 45px " > 年份： </ td > < td align = " left " > < asp:DropDownList ID = " ddlYear " runat = " server " AutoPostBack = " true " OnSelectedIndexChanged = " ddlYear_SelectedIndexChanged " > </ asp:DropDownList > & nbsp; & nbsp;当前第 < asp:Label ID = " lblCurrentMonth " runat = " server " Text = "" ForeColor = " Red " ></ asp:Label > 月份( < asp:Label ID = " lblDate " runat = " server " Text = "" ></ asp:Label > ) </ td > </ tr > < tr > < td width = " 45px " valign = " top " > < div style = " height: 5px; " > </ div > 月份： </ td > < td align = " left " > < asp:DataList ID = " dlMonth " runat = " server " DataKeyField = " MonthId " RepeatColumns = " 12 " RepeatDirection = " Horizontal " > < ItemTemplate > < asp:CheckBox ID = " CheckBox2 " runat = " server " Text = ' <%# Eval("Month") %> ' ToolTip = ' <%# Eval("StartDate", "{0:yyyy-MM-dd}") + "～" + Eval("EndDate", "{0:yyyy-MM-dd}") %> ' /> </ ItemTemplate > </ asp:DataList > </ td > </ tr > </ table >
MonthSelector.ascx.cs using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using Insus.NET; public partial class System_SystemControl_MonthSelector : System.Web.UI.UserControl { Month objMonth = new Month(); Year objYear = new Year(); protected void Page_Load( object sender, EventArgs e) { if ( ! IsPostBack) { Data_Binding(); } } private void Data_Binding() { objListControlUtility.DropDownListParse(ddlYear, objYear.GetHistoryYear( 5 ), " Year " , " YearId " ); DataRow dataRow = objMonth.GetMonthByDate(DateTime.Now.Date).Rows[ 0 ]; this .lblCurrentMonth.Text = dataRow[ " Month " ].ToString(); this .lblDate.Text = objInsusDateTimeUtility.GetDateTime(dataRow[ " StartDate " ], " yyyy-MM-dd " ) + " ～ " + objInsusDateTimeUtility.GetDateTime(dataRow[ " EndDate " ], " yyyy-MM-dd " ); MonthBinding(); } public DropDownList DropDownListYear { get { return this .ddlYear; } } public DataList DataListMonth { get { return this .dlMonth; } } protected void ddlYear_SelectedIndexChanged( object sender, EventArgs e) { MonthBinding(); } private void MonthBinding() { string year = ddlYear.SelectedItem.Text; this .dlMonth.DataSource = objMonth.GetMonth(year, year); this .dlMonth.DataBind(); }}
终于把这几个用户控件代码帖完了。任务还没有完成，还要完成最终的功能效果，就是最开始的动画：
View Code < fieldset id ="fieldset3" runat ="server" style ="position: relative; margin: 5px; padding: 5px;" > < legend > 时间 </ legend > < asp:RadioButtonList ID ="rblDateType" runat ="server" RepeatDirection ="Horizontal" AutoPostBack ="true" OnSelectedIndexChanged ="rblDateType_SelectedIndexChanged" > < asp:ListItem Text ="日期" Value ="0" ></ asp:ListItem > < asp:ListItem Text ="周别" Value ="1" ></ asp:ListItem > < asp:ListItem Text ="月份" Value ="2" ></ asp:ListItem > </ asp:RadioButtonList > < asp:RequiredFieldValidator ID ="RequiredFieldValidator1" runat ="server" ControlToValidate ="rblDateType" Display ="none" ErrorMessage ="选择时间。" SetFocusOnError ="true" ></ asp:RequiredFieldValidator > < div id ="DivDay" runat ="server" visible ="false" > < insus:DateSelector ID ="DateSelector1" runat ="server" /> </ div > < div id ="DivWeek" runat ="server" visible ="false" > < insus:WeekSelector ID ="WeekSelector1" runat ="server" /> </ div > < div id ="DivMonth" runat ="server" visible ="false" > < insus:MonthSelector ID ="MonthSelector1" runat ="server" /> </ div > </ fieldset >
View Code protected void rblDateType_SelectedIndexChanged( object sender, EventArgs e) { if ( ! string .IsNullOrEmpty(rblDateType.SelectedItem.Value)) { switch (rblDateType.SelectedItem.Value) { case " 0 " : this .DivDay.Visible = true ; this .DivWeek.Visible = false ; this .DivMonth.Visible = false ; break ; case " 1 " : this .DivDay.Visible = false ; this .DivWeek.Visible = true ; this .DivMonth.Visible = false ; break ; case " 2 " : this .DivDay.Visible = false ; this .DivWeek.Visible = false ; this .DivMonth.Visible = true ; break ; } } }
上面代码，只作为参考，如果你也想使用，在实现过程中，如果遇上问题，可以联系Insus.NET，除了QQ，其它联系方式均可，QQ上班不可以使用，最好是发送邮件。