Non English characters not inserting from file to SQL Server in C# application-CodePudding

We have this table

CREATE TABLE [dbo].[PackageStatus] 
(
    [ID]                   INT            IDENTITY (1, 1) NOT NULL,
    [PackageStatusName]    NVARCHAR (200) NOT NULL,
    [PackageStatusName_FR] NVARCHAR (200) NULL,
    [IsActive]             BIT            
        CONSTRAINT [DF_PackageStatus_IsActive] DEFAULT ((0)) NOT NULL,
    [DisplayName]          NVARCHAR (200) 
        CONSTRAINT [DF_PackageStatus_DisplayName] DEFAULT ('') NOT NULL,
    [DisplayName_FR]       NVARCHAR (200) 
        CONSTRAINT [DF_PackageStatus_DisplayName_FR] DEFAULT ('') NOT NULL,
    CONSTRAINT [PK_PackageStatus] PRIMARY KEY CLUSTERED ([ID] ASC)
);

PackageStatus.sql merge script which contains some french values

SET IDENTITY_INSERT [dbo].[PackageStatus] ON 

MERGE [dbo].[PackageStatus] AS T
USING ( VALUES 
    (1, N'New', N'En transit', 1, N'In Transit', N'En transit'),
    (2, N'Received', N'Prêt pour le ramassage', 1, N'Ready for Pickup', N'Prêt pour le ramassage'),
    (3, N'Received Damaged', N'Prêt pour le ramassage', 1, N'Ready for Pickup', N'Prêt pour le ramassage'),
    (4, N'Scheduled', N'Programmé', 1, N'Scheduled', N'Programmé')
) AS S ([ID], [PackageStatusName], [PackageStatusName_FR], [IsActive], [DisplayName], [DisplayName_FR])
ON T.[ID] = S.[ID]
WHEN MATCHED
    THEN UPDATE 
        SET 
            T.[PackageStatusName] = S.[PackageStatusName],
            T.[PackageStatusName_FR] =  S.[PackageStatusName_FR],
            T.[IsActive] = S.[IsActive],
            T.[DisplayName] =  S.[DisplayName],
            T.[DisplayName_FR] = S.[DisplayName_FR]         
WHEN NOT MATCHED BY TARGET
    THEN INSERT ([ID], [PackageStatusName], [PackageStatusName_FR], [IsActive], [DisplayName], [DisplayName_FR])
        VALUES ([ID], [PackageStatusName], [PackageStatusName_FR], [IsActive], [DisplayName], [DisplayName_FR])
WHEN NOT MATCHED BY SOURCE
    THEN DELETE;

SET IDENTITY_INSERT [dbo].[PackageStatus] OFF

If we run that script from SQL Server Management Studio, it works as expected.

But when we read that file content using C# and run SQL using Entity Framework Core from the application end

 string sql = File.ReadAllText(sourceFilePath);
 DbContext.Database.ExecuteSqlRaw(sql);

we see unwanted characters

Again things are as expected if we use a hardcoded query

string sql = @"
    SET IDENTITY_INSERT [dbo].[PackageStatus] ON 
    MERGE [dbo].[PackageStatus] AS T
    USING ( VALUES 
        (1, N'New', N'En transit', 1, N'In Transit', N'En transit'),
        (2, N'Received', N'Prêt pour le ramassage', 1, N'Ready for Pickup', N'Prêt pour le ramassage'),
        (3, N'Received Damaged', N'Prêt pour le ramassage', 1, N'Ready for Pickup', N'Prêt pour le ramassage'),
        (4, N'Scheduled', N'Programmé', 1, N'Scheduled', N'Programmé')
    ) AS S ([ID], [PackageStatusName], [PackageStatusName_FR], [IsActive], [DisplayName], [DisplayName_FR])
    ON T.[ID] = S.[ID]
    WHEN MATCHED
        THEN UPDATE 
            SET 
                T.[PackageStatusName] = S.[PackageStatusName],
                T.[PackageStatusName_FR] =  S.[PackageStatusName_FR],
                T.[IsActive] = S.[IsActive],
                T.[DisplayName] =  S.[DisplayName],
                T.[DisplayName_FR] = S.[DisplayName_FR]         
    WHEN NOT MATCHED BY TARGET
        THEN INSERT ([ID], [PackageStatusName], [PackageStatusName_FR], [IsActive], [DisplayName], [DisplayName_FR])
            VALUES ([ID], [PackageStatusName], [PackageStatusName_FR], [IsActive], [DisplayName], [DisplayName_FR])
    WHEN NOT MATCHED BY SOURCE
        THEN DELETE;
    SET IDENTITY_INSERT [dbo].[PackageStatus] OFF
";
DbContext.Database.ExecuteSqlRaw(sql);

We have tried encoding, but nothing works

 string sql = File.ReadAllText(sourceFilePath, Encoding.UTF8);

Is there any way to resolve that issue?

CodePudding user response：

Try

string sql = File.ReadAllText(sourceFilePath, Encoding.GetEncoding("ISO-8859-1"));