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.

Answer 2

The problem is that the DbConnection has already been opened. So when you try retrieve the connection string, the password has been wiped.

You could add persist security info=true to the string, but I would advise against it.

Instead, rewrite your GetSqlBulkCopy function, which is anyway fundamentally broken in the way it handles transactions, so that it now reuses the connection object it receives, instead of just the connection string.

private SqlBulkCopy GetSqlBulkCopy(SqlConnection connection, DbContext dbContext)
{
    var sqlTransaction = _dbContext.Database.CurrentTransaction.GetDbTransaction() as SqlTransaction;
    return new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.CheckConstraints, sqltransaction);
}

And then

var sqlConnection = (SqlConnection)dbContext.Database.GetDbConnection();
var wasOpen = sqlConnection.ConnectionState == ConnectionState.Open
try
{
    using (var sqlBulkCopy = GetSqlBulkCopy(sqlConnection, this._dbContext))
    {
        //etc
        if (!wasOpen)
            await sqlConnection.OpenAsync();
        // etc
    }
}
finally
{
    if (!wasOpen)
        sqlConnection.Close();
}