在加载时计算T-SQL查询参数

I'm trying to SELECT records out of an Events Database (with an EventDate field as Date) that have a EventDate that is in the current month.

我正在尝试从事件数据库中选择记录(事件日期为EventDate字段),该事件数据库具有当前月份的EventDate。

I have functions for calculating the first day of the month:

我有计算每月第一天的函数:

public string GetFirstofMonth(DateTime dt)
{
 int thisMonth = dt.Month;
 int thisYear = dt.Year;
 string firstOfMonth = thisMonth.ToString() + "/1/" + thisYear.ToString();
 return firstOfMonth;
}

and the last day of the month:

和本月的最后一天:

public string GetLastofMonth(DateTime dt)
{
  DateTime Date2Obj = new DateTime(dt.Year, dt.Month, 1);
  int thisMonth = dt.Month;
  int thisYear = dt.Year;
  Date2Obj.AddMonths(1);
  Date2Obj.AddDays(-1);
  int lastDay = Date2Obj.Day;
  string LastOfMonth = thisMonth + "/" + lastDay + "/" + thisYear;
  return LastOfMonth;
}

So in the MasterPage (or the instance, doesn't matter) I have this SQLDataSource:

所以在MasterPage(或实例中,没关系)我有这个SQLDataSource:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:MeetingsConnectionString %>" 
 SelectCommand="SELECT * FROM [Events] WHERE (([EventDate] &gt;= @EventDate) AND ([EventDate] &lt;= @EventDate2))">
 <SelectParameters>
   <asp:Parameter DbType="Date" Name="EventDate" />
   <asp:Parameter DbType="Date" Name="EventDate2" />
 </SelectParameters>
</asp:SqlDataSource>

How do I modify the EventDate and EventDate2 parameters in the SQLDataSource to use GetFirstofMonth(DateTime.Today); and GetLastofMonth(DateTime.Today), respectively, as their values? Or is this not possible?

如何修改SQLDataSource中的EventDate和EventDate2参数以使用GetFirstofMonth(DateTime.Today);和GetLastofMonth(DateTime.Today)分别作为它们的值?或者这不可能吗?

3 个解决方案

#1

This can be done by handling the SqlDataSource's "Selecting" event as follows:

这可以通过处理SqlDataSource的“Selecting”事件来完成,如下所示:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" OnSelecting="SqlDataSource1_Selecting"
 ConnectionString="<%$ ConnectionStrings:MeetingsConnectionString %>" 
 SelectCommand="SELECT * FROM [Events] WHERE [EventDate] BETWEEN @EventDate AND @EventDate2">
 <SelectParameters>
   <asp:Parameter Name="EventDate" />
   <asp:Parameter Name="EventDate2" />
 </SelectParameters>
</asp:SqlDataSource>

In your codebehind or in your inline code (where ever you have your functions defined) add this method:

在您的代码隐藏或内联代码中(无论您何时定义了函数),请添加以下方法:

protected void SqlDataSource1_Selecting(object sender, SqlDataSourceEventArgs e)
{
  e.Command.Parameters["@EventDate"].Value 
           = GetFirstofMonth(DateTime.Now); //replace with your date param
  e.Command.Parameters["@EventDate2"].Value 
              = GetLastofMonth(DateTime.Now); //replace with your date param
}

#2

One way to do this is to put hidden fields on your page, set the value of those fields in code-behind with the dates using your methods, then tie the data source parameters to the hidden fields.

一种方法是在页面上放置隐藏字段,使用您的方法在代码隐藏中设置这些字段的值,然后将数据源参数绑定到隐藏字段。

Codebehind

public void Page_Load( ... )
{
     ...
     EventDate.Value = GetFirstOfMonth( DateTime.Today );
     EventDate2.Value = GetLastOfMonth( DateTime.Today );
     ...
}

Mark up

<asp:HiddenField runat="server" id="EventDate" />
<asp:HiddenField runat="server" id="EventDate2" />

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
 ConnectionString="<%$ ConnectionStrings:MeetingsConnectionString %>" 
 SelectCommand="SELECT * FROM [Events] WHERE (([EventDate] &gt;= @EventDate) AND ([EventDate] &lt;= @EventDate2))">
 <SelectParameters>
   <asp:ControlParameter DbType="Date" Name="EventDate" ControlID="EventDate" PropertyName="Value" />
   <asp:ControlParameter DbType="Date" Name="EventDate2" ControlID="EventDate2" PropertyName="Value" />
 </SelectParameters>
</asp:SqlDataSource>

#3

I usually find it simpler to do this:

我通常觉得这样做更简单:

select * from Events where Month(EventDate) = Month(getdate())

If the month can be selected by the user, then you just substitute a parameter for the month number (returned from a selection list).

如果用户可以选择月份,则只需将参数替换为月份编号(从选择列表返回)。

P.S. - If you want to do more complex summary analysis of your event data (counts per hour, day etc.) and you are using SQL Server 2005+ then the function described in this post might be useful.

附: - 如果您想对事件数据(每小时,每天等)进行更复杂的摘要分析,并且您正在使用SQL Server 2005+,那么本文中描述的功能可能会有用。

#1