The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Vuyiswa Maseko 351 Reputation points
2021-10-10T21:10:23.103+00:00

Good Day All

I have the following SP

CREATE PROCEDURE [dbo].[spx_Add_Update_USERS]                        
(                          

@USER_HANDLE nvarchar(max),                      
@TELEPHONE varchar(max),                         
@LANGUAGES nvarchar(max) = NULL,                     
@COUNTRY_CODE nvarchar(max) = NULL,                        
@AGE INT = NULL,                        
@GENDER nvarchar(max) = NULL,                         
@COUNTRY nvarchar(max),                          
@STATE_PROVICE nvarchar(max) = NULL,             
@MY_RACE nvarchar(max) = NULL,       
@HIS_RACE nvarchar(max) = NULL,                     
@LU_LOOKING_FOR  nvarchar(max) = NULL,                         
@BIO nvarchar(max) = NULL,       
@CITY_SURBURBS nvarchar(max) = NULL   ,
@LU_RELATIONSHIP_LOOKING_FOR nvarchar(max) = NULL ,
@TOKEN UniqueIdentifier

)                        
AS                      


declare @USER_ID INT  

DECLARE @USER_ID_TOK   INT  =(SELECT  TOP 1 [USER_ID] FROM [dbo].[USERS] WHERE  TOKEN=  @TOKEN    )

DECLARE @USER_ID_TEL   INT  =(SELECT TOP 1  [USER_ID] FROM [dbo].[USERS] WHERE [TELEPHONE] = @TELEPHONE) 



 IF(@USER_ID_TOK  IS NULL AND @USER_ID_TEL IS NOT NULL  )
 BEGIN 
 SET @USER_ID = @USER_ID_TEL
 END 
 ELSE 
 BEGIN 
 SET @USER_ID = @USER_ID_TOK
 END 


DECLARE  @LU_COUNTRIES_ID  INT  =(  SELECT  top 1  [Id] FROM [dbo].[Countries] WHERE [Name] =@COUNTRY )                    

DECLARE @LU_LANGUAGES_ID  INT =  (  SELECT   top 1  [LU_LANGUAGES_ID] FROM [dbo].[LU_LANGUAGES] WHERE [DESCRIPTION] =@LANGUAGES )                    

DECLARE @STATE_PROVICE_ID INT  =  (  SELECT  top 1  Id FROM [dbo].[Regions] WHERE [Name] =  @STATE_PROVICE )                    

--DECLARE @LU_LOOKING_FOR_ID  INT   =  (  SELECT  LU_LOOKING_FOR_ID FROM [dbo].[LU_LOOKING_FOR] WHERE [DESCRIPTION] =@LU_LOOKING_FOR )                     
--DECLARE @LU_RACE_ID  INT   =  (  SELECT  LU_RACE_ID FROM [dbo].[LU_RACE] WHERE [DESCRIPTION] =@RACE )              

DECLARE @CITY_SURBURB_ID INT  =  (  SELECT  top 1  Id FROM [dbo].[Cities] WHERE [Name] =@CITY_SURBURBS )                    


IF ( ( SELECT   COUNT(*) FROM     [dbo].[USERS] WHERE                          
USER_ID = @USER_ID  ) > 0 )                        

BEGIN UPDATE [dbo].[USERS]                        
SET [USER_HANDLE] =@USER_HANDLE ,[TELEPHONE] =@TELEPHONE                     
 , [LU_LANGUAGES_ID] =@LU_LANGUAGES_ID,                    
 [COUNTRY_CODE]= @COUNTRY_CODE,[GENDER] =@GENDER,                    
[LU_COUNTRIES_ID] =@LU_COUNTRIES_ID,[STATE_PROVICE_ID] =@STATE_PROVICE_ID,[LU_TOWNSHIP_ID] = @CITY_SURBURB_ID 
,MY_RACE =@MY_RACE,HIS_RACE=@HIS_RACE,
LU_RELATIONSHIP_LOOKING_FOR = @LU_RELATIONSHIP_LOOKING_FOR  
 ,IS_VERIFIED = 1 ,  AGE = @AGE  ,
  DATE_GENDER  = @LU_LOOKING_FOR , TOKEN = @TOKEN       
WHERE [USER_ID] =  @USER_ID                


END                        
ELSE                         
BEGIN                         

INSERT INTO [dbo].[USERS]([USER_HANDLE] , [LU_USER_STATUS_ID] ,                         
[TELEPHONE]      ,                         
[LU_LANGUAGES_ID]  , [COUNTRY_CODE] ,                        
AGE , [GENDER] , [LU_COUNTRIES_ID] , [STATE_PROVICE_ID] ,[LU_TOWNSHIP_ID] ,                         
MY_RACE,HIS_RACE , LU_RELATIONSHIP_LOOKING_FOR    ,IS_VERIFIED , DATE_GENDER,TOKEN)                        
VALUES(@USER_HANDLE , 1 --ACTIVE                    
, @TELEPHONE     , @LU_LANGUAGES_ID ,                         
  @COUNTRY_CODE , @AGE , @GENDER , @LU_COUNTRIES_ID ,                         
@STATE_PROVICE_ID,@CITY_SURBURB_ID , @MY_RACE,@HIS_RACE , @LU_RELATIONSHIP_LOOKING_FOR ,1  , @LU_LOOKING_FOR,@TOKEN)                        
END 

and i use it like this

 public async Task<USERS_Model> Verify_code(USERS_Model model)
        {
            con = new SqlConnection(strCon);
            cmd = new SqlCommand();
            cmd.CommandText = "[dbo].[spx_Verify_code]";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con; 
            cmd.CommandTimeout = 0;

            cmd.Parameters.Add("@PHONE_NUMBER", SqlDbType.NVarChar).Value = GenericMethods.RemoveSpacesIn_String(model.TELEPHONE);

            cmd.Parameters.Add("@VERIFICATION_CODE", SqlDbType.NVarChar).Value = GenericMethods.RemoveSpacesIn_String(model.VERIFICATION_CODE);


            if (model.COUNTRY != null)
                cmd.Parameters.Add("@COUNTRY", SqlDbType.NVarChar).Value = model.COUNTRY;

            if (model.USER_HANDLE != null)
                cmd.Parameters.Add("@USER_HANDLE", SqlDbType.NVarChar).Value = model.USER_HANDLE;


            if (model.COUNTRY_CODE != null)
                cmd.Parameters.Add("@COUNTRY_CODE", SqlDbType.NVarChar).Value = model.COUNTRY_CODE.Replace('+', ' ').Replace('+', ' ');

            if (model.AGE != 0)
                cmd.Parameters.Add("@AGE", SqlDbType.Int).Value = model.AGE;

            if (model.GENDER != null)
                cmd.Parameters.Add("@GENDER", SqlDbType.NVarChar).Value = model.GENDER;

            if (model.STATE_PROVICE != null)
                cmd.Parameters.Add("@STATE_PROVICE", SqlDbType.NVarChar).Value = model.STATE_PROVINCE;

            if (model.HIS_RACE != null)
                cmd.Parameters.Add("@HIS_RACE", SqlDbType.NVarChar).Value = model.HIS_RACE;

            if (model.MY_RACE != null)
                cmd.Parameters.Add("@MY_RACE", SqlDbType.NVarChar).Value = model.MY_RACE;

            if (model.LOOKING_FOR != null)
                cmd.Parameters.Add("@LOOKING_FOR", SqlDbType.NVarChar).Value = model.LOOKING_FOR;

                   if (model.CITY != null)
                cmd.Parameters.Add("@CITY_SURBURBS", SqlDbType.NVarChar).Value = model.CITY;


            if (model.RELATIONSHIP_LOOKING_FOR != null)
                cmd.Parameters.Add("@LU_RELATIONSHIP_LOOKING_FOR", SqlDbType.NVarChar).Value = model.RELATIONSHIP_LOOKING_FOR;

            if (model.TOKEN != null)
                cmd.Parameters.Add("@TOKEN", SqlDbType.UniqueIdentifier).Value = model.TOKEN;

            da = new SqlDataAdapter();

            da.SelectCommand = cmd;

            DataTable dt = new DataTable();

            string message = string.Empty;

            USERS_Model m = new USERS_Model();
            try
            {
                con.Open();

                da.Fill(dt);

                if (dt.Rows.Count > 0)
                {
                    if (dt.Rows[0]["USER_ID"] != DBNull.Value)
                        m.USER_ID = Convert.ToInt32(dt.Rows[0]["USER_ID"]);

                    if (dt.Rows[0]["TOKEN"] != DBNull.Value)
                        m.TOKEN = Guid.Parse(dt.Rows[0]["TOKEN"].ToString());

                }

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            return m;
        }

for some reason im getting this error

Datalayer.Verify_code (Datalayer.USERS_Model model)
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Thanks

Xamarin
Xamarin
A Microsoft open-source app platform for building Android and iOS apps with .NET and C#.
5,325 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,344 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
{count} vote

3 answers

Sort by: Most helpful
  1. Dan Guzman 9,226 Reputation points
    2021-10-11T21:51:28.557+00:00

    Use local variables and wrap the objects in a using block to ensure managed objects (SqlConnection and SqlCommand) are closed and disposed automatically. This also eliminates the need to add explict code to do so.

    using(var con = new SqlConnection(strCon))
    using(var cmd = new SqlCommand())
    {
        //the rest of your method code here
    }
    
    2 people found this answer helpful.

  2. AmeliaGu-MSFT 13,971 Reputation points Microsoft Vendor
    2021-10-11T06:49:38.92+00:00

    Hi VuyiswaiMaseko-8882,

    It may be caused by not closing database connections correctly and consistently.
    Please check if all connections are normally opened and closed.
    And we can try to modify the connection string to increase "MaxPoolSize".
    Please refer to this thread which might be helpful.

    Best Regards,
    Amelia


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  3. Tom Phillips 17,721 Reputation points
    2021-10-11T11:18:19.407+00:00

    This is not a SQL Server error, but a C# errors.

    Please see:
    https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling#adding-connections

    Note
    Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Garbage Collection.

    1 person found this answer helpful.
    0 comments No comments