SubSonic Stored Procedure Issue - Data Generated at Stored Procedure is different from Data Received by Stored Procedure in Output Parameter

Question

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...

Answer 1

Unfortunately, I did not get the answer of my question from this forum. But I have found the root-cause of this problem in the following thread,

http://forums.subsonicproject.com/t/4241.aspx

Now anyone can help me to find the resolution of this problem.