Greetings,
Currently we are replacing all of the data access code in the ASP.Net
Security Providers using Spring.Net and iBatis.Net. Basically instead of
implementing a custom provider each time security persistence needs
changed we just use one provider and change its data access component
(iBatisNet) as needed.
We did run into one operation that just won't return the expected
results no matter what we do short of modifying the actual stored
procedure. We don't have a problem changing the SPROC to make it work
but we were wondering if anyone could point out what might be happening
here.
Components:
1) SQL Server 2005
2) Visual Studio 2005
3) iBatisNet DataMapper 1.6.1.0
Original Stored Procedure:
ALTER PROCEDURE dbo.aspnet_Membership_GetNumberOfUsersOnline
@ApplicationName nvarchar(256),
@MinutesSinceLastInActive int,
@CurrentTimeUtc datetime
AS
BEGIN
DECLARE @DateActive datetime
SELECT @DateActive = DATEADD(minute,
-(@MinutesSinceLastInActive), @CurrentTimeUtc)
DECLARE @NumOnLine int
SELECT @NumOnLine = Count(*)
FROM aspnet_Users AS u WITH (NOLOCK) INNER JOIN
aspnet_Applications AS a WITH
(NOLOCK) ON u.ApplicationId = a.ApplicationId INNER JOIN
aspnet_Membership AS m WITH (NOLOCK)
ON u.UserId = m.UserId
WHERE (u.LastActivityDate > @DateActive) AND
(a.LoweredApplicationName = LOWER(@ApplicationName))
return @NumOnLine
END
Statement:
<procedure id="GetNumberOfUsersOnline"
resultClass="int"
parameterMap="GetNumberOfUsersOnline">
aspnet_Membership_GetNumberOfUsersOnline
</procedure>
Parameter Map:
<parameterMap id="GetNumberOfUsersOnline" class="Hashtable">
<parameter property="ApplicationName" column="ApplicationName"
dbType="string" size="256"/>
<parameter property="MinutesSinceLastInActive"
column="MinutesSinceLastInActive" dbType="int"/>
<parameter property="CurrentTimeUtc" column="CurrentTimeUtc"
dbType="DateTime"/>
</parameterMap>
DAO Code:
...snippet...
Hashtable parms = new Hashtable();
parms.Add("ApplicationName", applicationname);
parms.Add("MinutesSinceLastInactive", minutessincelastinactive);
parms.Add("CurrentTimeUtc", currenttimeutc);
int results = 0;
results = (int)
CustomMapper.Instance().QueryForObject("GetNumberOfUsersOnline", parms);
return results;
...snippet...
Testing:
Using the same parameters, expected results = 2 ...
1) Removed the return @NumOnLine line
1) Ran the SPROC in the SQL debugger - SQL output results were fine
2) Ran the original DAO code using SQL command objects - SQL command
results were fine
3) Ran our DAO code using iBatisNet - no results ever return
4) Change mapping file and DAO code to reflect using a Hashtable as
the return class - iBatisNet did not return any results
5) Changed @DateActive to anything else (date value) - iBatisNet
results were fine
Thanks,
Lee
