calling methods from within sql select statement

[jez123456]

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.Expr ession 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(st ring SortBy) {
SqlDataAdapter daGrid = new SqlDataAdapter( String.Format(@ "SELECT qryFullName.Ful lName AS FullName, tblAbsence.dtmA bsStart AS StartDate, tblAbsence.dtmA bsEnd AS EndDate, tblAbsence.blnC ontactable AS Contactable, " +
"CASE " +
"WHEN tblAbsence.dtmA bsStart = '{0}' THEN tblAbsence.strA MPMStart " +
"WHEN tblAbsence.dtmA bsEnd = '{0}' AND tblAbsence.strA MPMEnd IS NOT NULL THEN tblAbsence.strA MPMEnd " +
"ELSE '' " +
"END AS AMPM " +
"FROM qryFullName INNER JOIN tblAbsence ON qryFullName.str LogonName = tblAbsence.strL ogonName " +
"WHERE (tblAbsence.dtm AbsStart <= '{0}') AND (tblAbsence.dtm AbsEnd >= '{0}') AND tblAbsence.blnD eleted = 0", ActiveDate), HolData.TimeOff Conn);
DataSet dsGrid = new DataSet();
daGrid.Fill(dsG rid,"Absence");
DataView vwBookings = dsGrid.Tables["Absence"].DefaultView;
vwBookings.Sort =SortBy;
return vwBookings;
}

Here is the function that I want call.

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

MyDate = dtEndDate.AddDa ys(1);
SqlDataAdapter daUKHolidays = new SqlDataAdapter( "Select * from tblUKHolidays", HolData.TimeOff Conn);
DataSet dsUKHolidays = new DataSet();
daUKHolidays.Fi ll(dsUKHolidays , "tblUKHolidays" );
DataView dvUKHolidays = new DataView(dsUKHo lidays.Tables["tblUKHolid ays"]);
dvUKHolidays.So rt = "dtmDate";
while (MyDays < 1)
{
if ((MyDate.DayOfW eek.ToString() == "Monday")|(MyDa te.DayOfWeek.To String() == "Tuesday")|(MyD ate.DayOfWeek.T oString() == "Wednesday")|(M yDate.DayOfWeek .ToString() == "Thursday")|(My Date.DayOfWeek. ToString() == "Friday"))
{
int rowIndex = dvUKHolidays.Fi nd(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="<%#
GetDataGridSour ce()%>"
showfooter="Fal se" visible="true" OnEditCommand=" RecordEditComma nd"
OnDeleteCommand ="RecordDeleteC ommand" Width="100%" CellPadding="0" >
<columns>
<asp:templateco lumn ItemStyle-VerticalAlign=" Top"
ItemStyle-Width="65" ItemStyle-HorizontalAlign ="left" >
<itemtemplate >
<span ><%# RecordStatusToS tring(
((CtpRecord)Con tainer.DataItem ).Status )%></span>
</itemtemplate>
</asp:templatecol umn>
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

"jez123456" <je*******@disc ussions.microso ft.com> wrote in message
news:EA******** *************** ***********@mic rosoft.com...

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.Expr ession 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(st ring SortBy) {
SqlDataAdapter daGrid = new SqlDataAdapter( String.Format(@ "SELECT qryFullName.Ful lName AS FullName, tblAbsence.dtmA bsStart AS StartDate,
tblAbsence.dtmA bsEnd AS EndDate, tblAbsence.blnC ontactable AS Contactable, "
+ "CASE " +
"WHEN tblAbsence.dtmA bsStart = '{0}' THEN tblAbsence.strA MPMStart " +
"WHEN tblAbsence.dtmA bsEnd = '{0}' AND tblAbsence.strA MPMEnd IS NOT NULL THEN tblAbsence.strA MPMEnd " + "ELSE '' " +
"END AS AMPM " +
"FROM qryFullName INNER JOIN tblAbsence ON qryFullName.str LogonName = tblAbsence.strL ogonName " + "WHERE (tblAbsence.dtm AbsStart <= '{0}') AND (tblAbsence.dtm AbsEnd >= '{0}') AND tblAbsence.blnD eleted = 0", ActiveDate), HolData.TimeOff Conn); DataSet dsGrid = new DataSet();
daGrid.Fill(dsG rid,"Absence");
DataView vwBookings = dsGrid.Tables["Absence"].DefaultView;
vwBookings.Sort =SortBy;
return vwBookings;
}

Here is the function that I want call.

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

MyDate = dtEndDate.AddDa ys(1);
SqlDataAdapter daUKHolidays = new SqlDataAdapter( "Select * from tblUKHolidays", HolData.TimeOff Conn); DataSet dsUKHolidays = new DataSet();
daUKHolidays.Fi ll(dsUKHolidays , "tblUKHolidays" );
DataView dvUKHolidays = new DataView(dsUKHo lidays.Tables["tblUKHolid ays"]); dvUKHolidays.So rt = "dtmDate";
while (MyDays < 1)
{
if ((MyDate.DayOfW eek.ToString() == "Monday")|(MyDa te.DayOfWeek.To String() == "Tuesday")|(MyD ate.DayOfWeek.T oString() ==
"Wednesday")|(M yDate.DayOfWeek .ToString() ==
"Thursday")|(My Date.DayOfWeek. ToString() == "Friday")) {
int rowIndex = dvUKHolidays.Fi nd(MyDate);
if (rowIndex == -1)
{
MyDays = MyDays + 1;
}
}
if (MyDays >= 1)
break;
MyDate = MyDate.AddDays( 1);;
}
return MyDate;
}