1

I have a EF code first database, to populate the initial tables, I am using sql scripts (are far easier to handle and update that the seed methods).

The problem is, that the scripts are inserting wihtout special characters....

The database collation is: SQL_Latin1_General_CP1_CI_AS

The seed is reading the script like this:

context.Database.ExecuteSqlCommand(File.ReadAllText(baseDir + @"..\..\Scripts\Oficina.sql"));

And the script looks like this:

INSERT [dbo].[Oficina] ([IdOficina], [Nombre], [SISTEMA], [ORDEN]) VALUES (20, N'Comisión     Admisión', 1, 5)

The problem is, that its being saved in the database as:

Comisi�n Admisi�n

I have no clue what the problem could be.....any ideas?

Fede
  • 27
  • 4
  • 1
    You may have to use an overload of ReadAllText() to specify an encoding that matches the encoding of the text file (really, the encoding of the tool that created the text file). See http://msdn.microsoft.com/en-us/library/ms143369%28v=vs.110%29.aspx. – Keith Payne Dec 19 '14 at 15:32
  • I tried with encoding....this is not working either: context.Database.ExecuteSqlCommand(File.ReadAllText(baseDir + @"..\..\Scripts\Calidad.sql", Encoding.UTF8)); it still inserts weird characters – Fede Dec 19 '14 at 16:32

2 Answers2

0

Check the table definition to see if the data type is varchar or nvarchar. The question has been asked on the site before. Here is a good explanation:

Which special characters are allowed?

Community
  • 1
  • 1
rwking
  • 1,032
  • 6
  • 18
  • Its not a DB issue....if I execute the SQL script manually on SQL manager, data is inserted without any problem. – Fede Dec 19 '14 at 16:06
0

I faced the same problem few time ago

public static void ExecuteBatchFromFile(this DataContext dc, String fileName, String batchSeparator,
                                        Encoding enc = null) {
    if (enc == null)
        enc = Encoding.UTF8;
    String stSql = File.ReadAllLines(fileName, enc);
    /* ... */
}

I solved it by adding the enc parameter to my function.

The problem is to correctly read the source. The collation is not necessarily the storage encoding, but the encoding used for comparison.

tschmit007
  • 7,559
  • 2
  • 35
  • 43
  • I tried your method...... I changed the seed to this: context.Database.ExecuteSqlCommand(string.Join(Environment.NewLine, File.ReadAllLines(baseDir + @"..\..\Scripts\Calidad.sql", Encoding.UTF8))); Still not working, it inserts weird characters – Fede Dec 19 '14 at 16:25
  • your source encoding is not necessarily UTF-8, it may be another one as CP1252 for windows. Give a try with `Encoding.Default` use a text editor that can convert encoding (Notepad++ for example) – tschmit007 Dec 21 '14 at 12:20