Hi All,
I am facing a unknown problem while using stored procedure with SubSonic. I have written a stored procedure & application code that takes first name & last name as input parameter and return last login id as ouput parameter. It creates login id as first character of first name & complete last name for no-existing login id otherwise it adds 1 in the last login id e.g.
First Name - Mark,
Last Name - Waugh,
First Login Id - MWaugh,
Second Login Id - MWaugh1,
Third Login Id - MWaugh2 etc.
Stored Procedure
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Users_FetchLoginId]
(
@FirstName nvarchar(64),
@LastName nvarchar(64),
@LoginId nvarchar(256) OUTPUT
)
AS
DECLARE @UserId nvarchar(256);
SET @UserId = NULL;
SET @LoginId = NULL;
SELECT @UserId = LoweredUserName FROM aspnet_Users WHERE LoweredUserName LIKE (LOWER(SUBSTRING(@FirstName,1,1) + @LastName))
IF @@rowcount = 0 OR @UserId IS NULL
BEGIN
SET @LoginId = (SUBSTRING(@FirstName, 1, 1) + @LastName);
print @LoginId
RETURN 1;
END
ELSE
BEGIN
SELECT TOP 1 LoweredUserName FROM aspnet_Users WHERE LoweredUserName LIKE (LOWER(SUBSTRING(@FirstName,1,1) + @LastName + '%')) ORDER BY LoweredUserName DESC
RETURN 2;
END
Application Code
public string FetchLoginId(string firstName, string lastName)
{
SubSonic.StoredProcedure sp = SPs.UsersFetchLoginId(
firstName,
lastName,
null
);
sp.Command.AddReturnParameter();
sp.Execute();
if (sp.Command.Parameters.Find(delegate(QueryParameter queryParameter)
{
return queryParameter.Mode == ParameterDirection.ReturnValue;
}).ParameterValue != System.DBNull.Value)
{
int returnCode = Convert.ToInt32(sp.Command.Parameters.Find(delegate(QueryParameter queryParameter)
{
return queryParameter.Mode == ParameterDirection.ReturnValue;
}).ParameterValue, CultureInfo.InvariantCulture);
if (returnCode == 1)
{
// UserName as First Character of First Name & Full Last Name
return sp.Command.Parameters[2].ParameterValue.ToString();
}
if (returnCode == 2)
{
DataSet ds = sp.GetDataSet();
if (null == ds || null == ds.Tables[0] || 0 == ds.Tables[0].Rows.Count)
return "";
string maxLoginId = ds.Tables[0].Rows[0]["LoweredUserName"].ToString();
string initialLoginId = firstName.Substring(0, 1) + lastName;
int maxLoginIdIndex = 0;
int initialLoginIdLength = initialLoginId.Length;
if (maxLoginId.Substring(initialLoginIdLength).Length == 0)
{
maxLoginIdIndex++;
// UserName as Max Lowered User Name Found & Incrementer as Suffix (Here, First Incrementer i.e. 1)
return (initialLoginId + maxLoginIdIndex);
}
if (int.TryParse(maxLoginId.Substring(initialLoginIdLength), out maxLoginIdIndex))
{
if (maxLoginIdIndex > 0)
{
maxLoginIdIndex++;
// UserName as Max Lowered User Name Found & Incrementer as Suffix
return (initialLoginId + maxLoginIdIndex);
}
}
}
}
Now the problem is for some input (see test data below), the login id created at sql server end correctly but at application subsonic dal side, it truncates some characters.
First Name - Jenelia and Last Name - Kanupatikenalaalayampentyalavelugoplansubhramanayam
[dbo].[Users_FetchLoginId] - Execute Stored Procedure Separately - Login Id Is Correct
JKanupatikenalaalayampentyalavelugoplansubhramanayam
public string FetchLoginId(string firstName, string lastName) - Application Code DAL Side - LginId Is Wrongly Received From Stored Procedure
JKanupatikenalaalayampentyalavelugoplansubhramanay
You can easily see that 2 charactes are removed. If the data is correctly generated by stored procedure then why the characters are removed when data is received in output parameter of stored procedure? Is it due to any internal known or unknown bug of SubSonic?
Your help is significant. Thanks in advance...
