.net – Intermittent Async SQLClient issue c#

I have this strange Intermittent Async SQLClient issue. We’ve recently migrated our .net core code from sync to async. I have an example of our code below.

                using (var con = new SqlConnection(_fimpConnectionString))
                using (var cmd = new SqlCommand("EMP2_GetPrositeDetails", con) { CommandType = CommandType.StoredProcedure })
                {
                    cmd.Parameters.Add(new SqlParameter("@SiteURL", site.Host));
                    await con.OpenAsync();
                    using (var dr = await cmd.ExecuteReaderAsync())
                    {
                        while (await dr.ReadAsync())
                        {
                            return MapToPrositeDetails(dr);
                        }
                    }
                }

private PrositeDetails MapToPrositeDetails(DbDataReader dr)
    {
        var prositeDetails = new PrositeDetails();

        prositeDetails.EntityId = dr.GetInt32("EntityId");
        prositeDetails.MemId = dr.GetInt32("MemId");
        prositeDetails.Address = new Address()
        {
            Building = dr.GetValueOrDefault<string>("address"),
            Street = dr.GetValueOrDefault<string>("streetName"),
            Town = dr.GetValueOrDefault<string>("town"),
            Postcode = dr.GetValueOrDefault<string>("postCode"),
            County = dr.GetValueOrDefault<string>("county"),
        };
        prositeDetails.Phone = dr.GetValueOrDefault<string>("phone");
        prositeDetails.IMPStatus = dr.GetInt32("IMPStatus");
        prositeDetails.GolfClub = dr.GetValueOrDefault<string>("GolfClub");
        prositeDetails.Url = dr.GetValueOrDefault<string>("url");
        prositeDetails.FirstName = dr.GetValueOrDefault<string>("firstName");
        prositeDetails.LastName = dr.GetValueOrDefault<string>("lastName");
        prositeDetails.JobTitle = dr.GetValueOrDefault<string>("JobTitle");
        prositeDetails.ClubUrl = dr.GetValueOrDefault<string>("clubURL");
        prositeDetails.GoogleAnalyticsId = dr.GetValueOrDefault<string>("googleAnalyticsID");
        prositeDetails.Email = dr.GetValueOrDefault<string>("Email");
        prositeDetails.CpEmail = dr.GetValueOrDefault<string>("CpEmail");
        prositeDetails.SocialMedia = new SocialMedia()
        {
            Twitter = dr.GetValueOrDefault<string>("twitter"),
            Facebook = dr.GetValueOrDefault<string>("facebook"),
            Youtube = dr.GetValueOrDefault<string>("youTube"),
            Instagram = dr.GetValueOrDefault<string>("flickr"),
            LinkedIn = dr.GetValueOrDefault<string>("linkedIn"),
        };
        prositeDetails.Coordinates = new Coordinates()
        {
            Latitude = float.Parse(dr.GetValueOrDefault<double>("latitude").ToString(), CultureInfo.InvariantCulture.NumberFormat),
            Longitude = float.Parse(dr.GetValueOrDefault<double>("longitude").ToString(), CultureInfo.InvariantCulture.NumberFormat)
        };
        prositeDetails.BannerText = dr.GetValueOrDefault<string>("BannerText");
        prositeDetails.reCAPTCHASecret = dr.GetValueOrDefault<string>("reCAPTCHASecret");
        prositeDetails.reCAPTCHASiteKey = dr.GetValueOrDefault<string>("reCAPTCHASiteKey");
        prositeDetails.NoFollowLinks = dr.GetBoolean("NoFollowLinks");
        prositeDetails.RequestTeeTime = dr.GetBoolean("RequestTeeTime");
        prositeDetails.PrivateEmail = dr.GetValueOrDefault<string>("privateEmail");
        prositeDetails.ProImageUrl = dr.GetValueOrDefault<string>("ProImageUrl");
        prositeDetails.HotJar = dr.GetValueOrDefault<string>("HotJar");
        prositeDetails.WebIntroHtml = dr.GetValueOrDefault<string>("WebIntroHtml");
        prositeDetails.ThemeId = dr.GetInt32("ThemeId");
        prositeDetails.HomepageHeader = dr.GetValueOrDefault<string>("HomepageHeader");
        prositeDetails.HomepageSubHeader = dr.GetValueOrDefault<string>("HomepageSubHeader");
        prositeDetails.HomePageBannerURL = dr.GetValueOrDefault<string>("ImageUrl");
        return prositeDetails;
    }

Frequently our sites will be hit by the following error. I can’t for the life of me work out what’s wrong with this code as it feels pretty standard. If anyone has any advice when using .net core and async code it would be greatly appreciated.

System.ArgumentNullException: SafeHandle cannot be null. (Parameter 'pHandle')
   at System.StubHelpers.StubHelpers.SafeHandleAddRef(SafeHandle pHandle, Boolean& success)
   at System.Data.SqlClient.SNINativeMethodWrapper.SNIPacketAllocateWrapper(SafeHandle pConn, IOType IOType)
   at System.Data.SqlClient.SNIPacket..ctor(SafeHandle sniHandle)
   at System.Data.SqlClient.TdsParserStateObjectNative.WritePacketCache.Take(SNIHandle sniHandle)
   at System.Data.SqlClient.TdsParserStateObjectNative.GetResetWritePacket(Int32 dataSize)
   at System.Data.SqlClient.TdsParserStateObject.WriteSni(Boolean canAccumulate)
   at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode, Boolean canAccumulate)
   at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.BeginExecuteReader(AsyncCallback callback, Object stateObject, CommandBehavior behavior)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl[TArg1](Func`4 beginMethod, Func`2 endFunction, Action`1 endAction, TArg1 arg1, Object state, TaskCreationOptions creationOptions)
   at System.Data.SqlClient.SqlCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at FgEmp.Services.MemberModule.MemberService.GetPrositeDetailsAsync(Uri site) in C:Windowssystem32configsystemprofileAppDataLocalJenkins.jenkinsworkspacePrositesCoreFgEmp.ServicesMemberModuleMemberService.cs:line 327

Occassionally we will also intermittently receive the following stacktrace. This sounds like a simple structure issue, but the s/p isn’t being changed, it just intermittently fails. I don’t know if these issues are related or not.

2022-06-23 12:58:57,425 FATAL Logger.MoveNext [331] - MESSAGE: EMP2_GetPrositeDetails Failed: 
 System.IndexOutOfRangeException: EntityId
   at System.Data.ProviderBase.BasicFieldNameLookup.GetOrdinal(String fieldName)
   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
   at FgEmp.Services.MemberModule.MemberService.MapToPrositeDetails(DbDataReader dr) in C:Windowssystem32configsystemprofileAppDataLocalJenkins.jenkinsworkspacePrositesCoreFgEmp.ServicesMemberModuleMemberService.cs:line 348
   at FgEmp.Services.MemberModule.MemberService.GetPrositeDetailsAsync(Uri site) in C:Windowssystem32configsystemprofileAppDataLocalJenkins.jenkinsworkspacePrositesCoreFgEmp.ServicesMemberModuleMemberService.cs:line 331

Leave a Comment