calling methods from within sql select statement

[Guest]

Hi. I have a web form with a datagrid that displays employees who are on holiday. One of the datagrid columns shows the date of their last day of holiday, but what I really require is the Start Back at work date, i.e. if their last day of holiday is a Friday, then the Start Back date should be the following Monday (ignoring any weekends and public holiday dates).

I have a table which holds the national holiday dates (tblUKHolidays), but how do I build the datagrid to include a Start Back column. I have a c# function which calculates the Start Back date from the EndDate but I don’t know how to include this in the datagrid.

The DataColumn.Expression will not allow me to call the function.

Is there a way to call the function from within the SQL select string?

Here is the datagrid source code.

private DataView CreateSource(string SortBy) {
SqlDataAdapter daGrid = new SqlDataAdapter(String.Format(@"SELECT qryFullName.FullName AS FullName, tblAbsence.dtmAbsStart AS StartDate, tblAbsence.dtmAbsEnd AS EndDate, tblAbsence.blnContactable AS Contactable, " +
"CASE " +
"WHEN tblAbsence.dtmAbsStart = '{0}' THEN tblAbsence.strAMPMStart " +
"WHEN tblAbsence.dtmAbsEnd = '{0}' AND tblAbsence.strAMPMEnd IS NOT NULL THEN tblAbsence.strAMPMEnd " +
"ELSE '' " +
"END AS AMPM " +
"FROM qryFullName INNER JOIN tblAbsence ON qryFullName.strLogonName = tblAbsence.strLogonName " +
"WHERE (tblAbsence.dtmAbsStart <= '{0}') AND (tblAbsence.dtmAbsEnd >= '{0}') AND tblAbsence.blnDeleted = 0", ActiveDate), HolData.TimeOffConn);
DataSet dsGrid = new DataSet();
daGrid.Fill(dsGrid,"Absence");
DataView vwBookings = dsGrid.Tables["Absence"].DefaultView;
vwBookings.Sort=SortBy;
return vwBookings;
}

Here is the function that I want call.

public DateTime StartBackDate(DateTime dtEndDate)
{
DateTime MyDate;
int MyDays = 0;

MyDate = dtEndDate.AddDays(1);
SqlDataAdapter daUKHolidays = new SqlDataAdapter("Select * from tblUKHolidays", HolData.TimeOffConn);
DataSet dsUKHolidays = new DataSet();
daUKHolidays.Fill(dsUKHolidays, "tblUKHolidays");
DataView dvUKHolidays = new DataView(dsUKHolidays.Tables["tblUKHolidays"]);
dvUKHolidays.Sort = "dtmDate";
while (MyDays < 1)
{
if ((MyDate.DayOfWeek.ToString() == "Monday")|(MyDate.DayOfWeek.ToString() == "Tuesday")|(MyDate.DayOfWeek.ToString() == "Wednesday")|(MyDate.DayOfWeek.ToString() == "Thursday")|(MyDate.DayOfWeek.ToString() == "Friday"))
{
int rowIndex = dvUKHolidays.Find(MyDate);
if (rowIndex == -1)
{
MyDays = MyDays + 1;
}
}
if (MyDays >= 1)
break;
MyDate = MyDate.AddDays(1);;
}
return MyDate;
}

 

[Mohamoss]

Hi
What you but inside an SQL query string is considered by c# compiler as
string so it is taken as is. It is your DBMS that try to analyze this
string to be executed against your database. So in other words, whatever
you but inside the SOL query string is bypassed by the C# compiler and
therefore, you can not call a function within such string.
What you can do however, is to have a calculated column in your datagrid
that is not bound to your datatable . write a function that do the
calculation and use it to fill this column .
hope that helps
Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

You can call a function when you bind your grid, the code below may help
you, it's from a web page with a similar situation then yours.


<asp:datagrid id=recordgrid runat="server" DataSource="<%#
GetDataGridSource()%>"
showfooter="False" visible="true" OnEditCommand="RecordEditCommand"
OnDeleteCommand="RecordDeleteCommand" Width="100%" CellPadding="0">
<columns>
<asp:templatecolumn ItemStyle-VerticalAlign="Top"
ItemStyle-Width="65" ItemStyle-HorizontalAlign="left" >
<itemtemplate>
<span ><%# RecordStatusToString(
((CtpRecord)Container.DataItem).Status )%></span>
</itemtemplate>
</asp:templatecolumn>


You see how I call a method in the bind expression, you could do a similar
thing.


Cheers,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

Hi. I have a web form with a datagrid that displays employees who are on

holiday. One of the datagrid columns shows the date of their last day of
holiday, but what I really require is the Start Back at work date, i.e. if
their last day of holiday is a Friday, then the Start Back date should be
the following Monday (ignoring any weekends and public holiday dates).

I have a table which holds the national holiday dates (tblUKHolidays), but

how do I build the datagrid to include a Start Back column. I have a c#
function which calculates the Start Back date from the EndDate but I don't
know how to include this in the datagrid.

The DataColumn.Expression will not allow me to call the function.

Is there a way to call the function from within the SQL select string?

Here is the datagrid source code.

private DataView CreateSource(string SortBy) {
SqlDataAdapter daGrid = new SqlDataAdapter(String.Format(@"SELECT

qryFullName.FullName AS FullName, tblAbsence.dtmAbsStart AS StartDate,
tblAbsence.dtmAbsEnd AS EndDate, tblAbsence.blnContactable AS Contactable, "
+

"CASE " +
"WHEN tblAbsence.dtmAbsStart = '{0}' THEN tblAbsence.strAMPMStart " +
"WHEN tblAbsence.dtmAbsEnd = '{0}' AND tblAbsence.strAMPMEnd IS NOT NULL THEN tblAbsence.strAMPMEnd " +
"ELSE '' " +
"END AS AMPM " +
"FROM qryFullName INNER JOIN tblAbsence ON qryFullName.strLogonName = tblAbsence.strLogonName " +
"WHERE (tblAbsence.dtmAbsStart <= '{0}') AND (tblAbsence.dtmAbsEnd >=

'{0}') AND tblAbsence.blnDeleted = 0", ActiveDate), HolData.TimeOffConn);

DataSet dsGrid = new DataSet();
daGrid.Fill(dsGrid,"Absence");
DataView vwBookings = dsGrid.Tables["Absence"].DefaultView;
vwBookings.Sort=SortBy;
return vwBookings;
}

Here is the function that I want call.

public DateTime StartBackDate(DateTime dtEndDate)
{
DateTime MyDate;
int MyDays = 0;

MyDate = dtEndDate.AddDays(1);
SqlDataAdapter daUKHolidays = new SqlDataAdapter("Select * from

tblUKHolidays", HolData.TimeOffConn);

DataSet dsUKHolidays = new DataSet();
daUKHolidays.Fill(dsUKHolidays, "tblUKHolidays");
DataView dvUKHolidays = new DataView(dsUKHolidays.Tables["tblUKHolidays"]);
dvUKHolidays.Sort = "dtmDate";
while (MyDays < 1)
{
if ((MyDate.DayOfWeek.ToString() == "Monday")|(MyDate.DayOfWeek.ToString()

== "Tuesday")|(MyDate.DayOfWeek.ToString() ==
"Wednesday")|(MyDate.DayOfWeek.ToString() ==
"Thursday")|(MyDate.DayOfWeek.ToString() == "Friday"))