SQL ParameterDirection.InputOutput / SqlCommand.ExecuteNonQuery() problem

See more:

C#

SQL

ADO.NET

stored-procedure

Help!

I have a very strange problem that only occurs when the code in question is in a high load situations. My C# code calls a T-SQL stored procedure that has an OUTPUT parameter.

Under high loads, the data being returned does not make it back to the calling C# code. I have extracted all the relevant code into the example below;

Stored Procedure:

CREATE PROCEDURE GetLoginName
	@LoginId BIGINT,
	@LoginName VARCHAR(50) OUTPUT
AS

SET NOCOUNT ON

SELECT	@LoginName = LoginName
FROM	Logins
WHERE	Id = @LoginId

SET NOCOUNT OFF

GO

Database base class:

public class DatabaseContextBase : IDisposable
	{
		private SqlConnection _connection;
		private string _connectionString;
		private SqlInt32 _returnValue;
		private int _commandTimeout;
        private static int _maxDatabaseExecuteAttempts = 3;   
		
		private static int s_commandTimeout = 30;
		
		protected DBContextBase()
		{
			// try and get the connection string off the identity first...
			string connectionString = GetConnectionStringFromIdentity();

			if( connectionString != null )
			{
				ConstructionHelper( connectionString, s_commandTimeout );
			}
			else
			{
				// use the initialised static connection string, and call the other overload
				// of the constructor
			
				ConstructionHelper( s_connectionString, s_commandTimeout );
			}	
		}
	
		private void ConstructionHelper( string connectionString, int commandTimeout ) 
		{
			// store the connection string in a member var.
			_connectionString = connectionString;

			// store the timout in a member var.
			_commandTimeout = commandTimeout;
		}
	
		public static string GetConnectionStringFromIdentity()
		{
			IIdentity identity = Thread.CurrentPrincipal.Identity as wxyz.Security.wxyzIdentityBase;
			string connectionString = null;

			if( identity != null )
			{
				connectionString = ( (wxyz.Security.wxyzIdentityBase) identity ).ConnectionString ;
			}

			return connectionString;
		}
	
		public void Dispose()
		{			
			if (_connection.State != ConnectionState.Closed)
			{
				_connection.Close();
			}

			_connection.Dispose();
			_connection = null;
		}

		protected void ExecuteNonQuery(SqlCommand command)
		{
            SqlConnection con = this.Connection;
            lock (con)
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }

                // don't need a try catch as this is only ever called from another method in this 
                // class which will wrap it.
                command.Connection = con;
                command.Transaction = _transaction;
                command.CommandTimeout = _commandTimeout;

                for (int currentAttempt = 1; currentAttempt <= _maxDatabaseExecuteAttempts; currentAttempt++)
                {
                    try
                    {
                        // do it
                        command.ExecuteNonQuery();

                        // done, exit loop
                        break;
                    }
                    catch (SqlException sex)
                    {
                        HandleDatabaseExceptions(currentAttempt, sex, command.CommandText);
                    }
                }
            }	
		}
		

		/// <summary>
		/// get the SqlConnection object owned by this database (already connected to db) 
		/// </summary>
		public SqlConnection Connection
		{
			get
			{
				// check whether we've got a connection string (from either identity or static initialise)
				if ( _connectionString == null )
				{
					throw new ArgumentNullException( "connectionString", "Connection string not set" );
				}

				if ( _connection != null )
				{
					return _connection;
				}
				else
				{
					_connection = new SqlConnection( _connectionString );
					return _connection;
				}
			}	
		}

		/// <summary>
		/// Return value from executed stored procedure
		/// </summary>
		public SqlInt32 ReturnValue
		{
			get
			{
				return _returnValue;
			}
			set
			{
				_returnValue = value;
			}
		}
	}

Database Access Class

/// <summary>Contains raw database commands for the Database</summary>
	public class AuthenticationDBCommands
	{
		/// <summary>Returns command object for spiAuthenticateLogin stored procedure in the Database</summary>
		public static SqlCommand GetLoginName()
		{
			System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("GetLoginName");
			cmd.CommandType = CommandType.StoredProcedure;
			
cmd.Parameters.Add(new SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.ReturnValue, false, 0, 0, "RETURN_VALUE", System.Data.DataRowVersion.Current, SqlInt32.Null));
                        
cmd.Parameters.Add(new SqlParameter("@LoginId", SqlDbType.BigInt, 8, ParameterDirection.Input, false, 0, 0, "LoginId", DataRowVersion.Current, SqlInt64.Null));
                        
cmd.Parameters.Add(new SqlParameter("@LoginName", SqlDbType.VarChar, 50, ParameterDirection.InputOutput,false, 0, 0, "LoginName", DataRowVersion.Current, SqlString.Null));

			return cmd;
		}
 }


public class AuthenticationDBContext : DatabaseContextBase
{
		public AuthenticationDBContext() : base()
		{
		} 

		public void GetLoginName(SqlInt64 LoginId, ref SqlString LoginName)
		{
			SqlCommand cmd = AuthenticationDBCommands.GetLoginName();
			cmd.Parameters[1].Value = LoginId;
			cmd.Parameters[2].Value = LoginName;

			base.ExecuteNonQuery(cmd);
			base.ReturnValue = (SqlInt32) cmd.Parameters[0].Value; 
			LoginName= (SqlString)(cmd.Parameters[2].Value); 
		}
}

Usage:

protected string GetLoginName(long loginId)
{
     SqlString loginName = SqlString.Null;

     using (AuthenticationDBContext dbc = new AuthenticationDBContext())
     {
          dbc.GetLogin(loginId, ref loginName);
     }

     return loginName.Value;
}

As you can see this is fairly standard stuff. But when the dbContext.GetLoginName() method is called by many different users in quick succession the loginName object is sometimes null.

I have tested the SQL and it always finds a value (I've inserted @LoginName into a table and it's never null). So the problem is happening after or in SqlCommand.ExecuteNonQuery();

I'm using .NET 4.5 and my database is on SQL 2008.

Has anyone seen anything like this before? Can anyone recommend any changes?

Thanks in advance,
Matt