WriteToServerAsync Not working as expected when Upgrade to .net 6

Question

My current project was in .net 5 which I have upgraded to .net 6 version. Earlier the code was working fine but after upgrade this code seems to be not working.

        public async Task AddBulkAsync(List<T> entityList)
        {
            try
            {
                if (entityList.Count > 0)
                {
                    using (var sqlBulkCopy = GetSqlBulkCopy(this._dbContext, this._dbContext.Database.CurrentTransaction))
                    {
                        sqlBulkCopy.BatchSize = 10000;
                        sqlBulkCopy.BulkCopyTimeout = 1800;
                        var dataTable = GetDataTable(entityList, sqlBulkCopy);
                        await sqlBulkCopy.WriteToServerAsync(dataTable);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        internal DataTable GetDataTable<T>(IList<T> entities, SqlBulkCopy sqlBulkCopy)
        {
            var dataTable = new DataTable();
            var columnsDict = new Dictionary<string, object>();
            var ownedEntitiesMappedProperties = new HashSet<string>();

            var type = entities[0].GetType();
            var entityType = this._dbContext.Model.FindEntityType(type);
            //var entityPropertiesDict = entityType.GetProperties().Where(a => tableInfo.PropertyColumnNamesDict.ContainsKey(a.Name)).ToDictionary(a => a.Name, a => a);
            var entityPropertiesDict = entityType.GetProperties().ToDictionary(a => a.Name, a => a);
            var entityNavigationOwnedDict = entityType.GetNavigations().Where(a => a.GetTargetType().IsOwned()).ToDictionary(a => a.Name, a => a);
            var properties = type.GetProperties();
            // var discriminatorColumn = tableInfo.ShadowProperties.Count == 0 ? null : tableInfo.ShadowProperties.ElementAt(0);

            foreach (var property in properties)
            {
                if (entityPropertiesDict.ContainsKey(property.Name))
                {
                    var propertyEntityType = entityPropertiesDict[property.Name];
                    string columnName = propertyEntityType.GetColumnName();

                    // var isConvertible = tableInfo.ConvertibleProperties.ContainsKey(columnName);
                    var propertyType = property.PropertyType;

                    var underlyingType = Nullable.GetUnderlyingType(propertyType);
                    if (underlyingType != null)
                    {
                        propertyType = underlyingType;
                    }

                    dataTable.Columns.Add(columnName, propertyType);
                    columnsDict.Add(property.Name, null);
                }
            }


            foreach (var entity in entities)
            {
                foreach (var property in properties)
                {
                    if (entityPropertiesDict.ContainsKey(property.Name))
                    {
                        var propertyValue = property.GetValue(entity, null);
                        if (property.PropertyType == typeof(Guid) && (Guid)propertyValue == default(Guid))
                        {
                            propertyValue = Guid.NewGuid();
                        }

                        columnsDict[property.Name] = propertyValue;
                    }
                }
                var record = columnsDict.Values.ToArray();
                dataTable.Rows.Add(record);
            }

            foreach (DataColumn item in dataTable.Columns)  //Add mapping
            {
                sqlBulkCopy.ColumnMappings.Add(item.ColumnName, item.ColumnName);
            }
            string schema = entityType.GetSchema() != null ? entityType.GetSchema() : "dbo";
            dataTable.TableName = schema + "." + entityType.GetTableName();
            sqlBulkCopy.DestinationTableName = dataTable.TableName;
            return dataTable;
        }

        private SqlBulkCopy GetSqlBulkCopy(DbContext dbContext, IDbContextTransaction transaction)
        {
            var sqlConnection = dbContext.Database.GetDbConnection().ConnectionString;
            if (transaction == null)
            {
                //return new SqlBulkCopy(sqlConnection, sqlBulkCopyOptions, null);
                return new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints);
            }
            else
            {
                var sqlTransaction = (SqlTransaction)transaction.GetDbTransaction();
                //return new SqlBulkCopy(sqlConnection, sqlBulkCopyOptions, sqlTransaction);
                return new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints);
            }
        }

This seems not working as expected await sqlBulkCopy.WriteToServerAsync(dataTable); I have tried to implement with non-async method sqlBulkCopy.WriteToServer(dataTable); But still, the same issue is occurring It returns an error

"One or more errors occurred. (Login failed for user 'Devadmin'.)" I have tried Task.Run method It will not return any error but will data will not be inserted into the table.

Task.Run(async () => await sqlBulkCopy.WriteToServerAsync(dataTable));

Please some one help me on this issue.

Answer 1

Instead of adding persist security info=true to the string. we can use following code.

public async Task AddBulkAsync(List<T> entityList)
    {
                var sqlConnectionString = SqlConnectionToConnectionString((SqlConnection)this._dbContext.Database.GetDbConnection());
                var sqlConnection = new SqlConnection(sqlConnectionString);
                var wasOpen = sqlConnection.State == ConnectionState.Open;
                try
                {
                    using (var sqlBulkCopy = new SqlBulkCopy(sqlConnectionString, SqlBulkCopyOptions.CheckConstraints))
                    {
                        if (!wasOpen)
                            await sqlConnection.OpenAsync();
                        
                        sqlBulkCopy.BatchSize = 10000;
                        sqlBulkCopy.BulkCopyTimeout = 1800;
                        var dataTable = GetDataTable(entityList, sqlBulkCopy);
                        await sqlBulkCopy.WriteToServerAsync(dataTable);
                    }
                }
                finally
                {
                    if (!wasOpen)
                        sqlConnection.Close();
                }
}
    private static string SqlConnectionToConnectionString(SqlConnection conn)
    {
        System.Reflection.PropertyInfo property = conn.GetType().GetProperty("ConnectionOptions", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic);
        object optionsObject = property.GetValue(conn, null);
        System.Reflection.MethodInfo method = optionsObject.GetType().GetMethod("UsersConnectionString");
        string connStr = method.Invoke(optionsObject, new object[] { false }) as string; // argument is "hidePassword" so we set it to false
        return connStr;
    }

This has helped me to solve the issue. The connection string was blocking password and cannot able to do the connection string established.