Cannot import data to SQL Server database with C#

Question

I have exported data from SQL Server express and I try to import them into another database.

I want to use it as "Repair Table" in which I will add more tables with data and I always get an error and I can't understand why.

The error is

Message - System.Data.SqlClient.SqlException (0x80131904): Unclosed quotation mark after the character string 'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ '. Incorrect syntax near 'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ '.

The descriptions are in Greek language, I don't know if this has something to do with the error.

My code so far

private void startBtn_Click(object sender, EventArgs e)
{
     string sqlQuery = DatabaseTables.KodikoiTaric;

     if(checkBox1.Checked) 
          InsertDefaultValues(sqlQuery);
}

void InsertDefaultValues(string tableName)
{
    RepairTable(tableName);
    // DataTable csvData = GetDataTabletFromCSVFile(DatabaseTables.taric2);
}

void RepairTable(string tableName)
{
    try
    {
        string sqlConnectionString = Parameters.Config.ConnectionString;
        string script = tableName;
        var sqlqueries = script.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);

        SqlConnection conn = new SqlConnection(sqlConnectionString);

        SqlCommand cmd = new SqlCommand("query", conn);
        Server server = new Server(new ServerConnection(conn));

        conn.Open();
        var progressBar = 10;

        foreach (var query in sqlqueries)
        {
            progressBar += 10;
            cmd.CommandText = query;
            cmd.ExecuteNonQuery();
            conn.InfoMessage += delegate (object sender, SqlInfoMessageEventArgs e)
                {
                    FileStream ostrm;
                    StreamWriter writer;
                    TextWriter oldout = Console.Out;
                    string _dbUpdateLogPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + @"\DBUpdate" + DateTime.Now.ToString("ddMMyy") + ".txt";
                    try
                    {
                        if (!File.Exists(_dbUpdateLogPath))
                        {
                            ostrm = new FileStream(_dbUpdateLogPath, FileMode.OpenOrCreate, FileAccess.Write);
                            writer = new StreamWriter(ostrm);
                            Console.SetOut(writer);
                            Console.WriteLine(e.Message);
                            Console.SetOut(oldout);
                            writer.Close();
                            ostrm.Close();
                        }
                        else if (File.Exists(_dbUpdateLogPath))
                        {
                            ostrm = new FileStream(_dbUpdateLogPath, FileMode.Append, FileAccess.Write);
                            writer = new StreamWriter(ostrm);
                            Console.SetOut(writer);
                            Console.WriteLine(e.Message);
                            Console.SetOut(oldout);
                            writer.Close();
                            ostrm.Close();
                        }
                    }
                    catch (Exception ex)
                    {
                        NLogger.NLogger.LibraryLogClass.Error(ex.ToString());
                        return;
                    }

                };
            }

            conn.Close();
    }
    catch (Exception ex)
    {
        NLogger.NLogger.LibraryLogClass.Error(ex.ToString());
    }
}

I have the file as resource file and the form is like this

SET NUMERIC_ROUNDABORT OFF
GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO

SET DATEFORMAT YMD
GO

SET XACT_ABORT ON
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

BEGIN TRANSACTION
GO

DELETE FROM [dbo].[KodikosTaric]
GO

SET IDENTITY_INSERT [dbo].[KodikosTaric] ON 

INSERT INTO [dbo].[KodikosTaric] ([Id], [KodikosTaric], [KoinotikonMetron_a], [KoinotikonMetron_b], [Perigrafi], [DasmosTritonXoron], [ProtimisiakosDasmos], [SimpliromatikesMonades], [YpologismosKila], [DiasafistisId]) 
VALUES (1, N'8701100000', NULL, NULL, N'ΕΛΚΥΣΤΗΡΕΣ ΧΕΙΡΟΔΗΓΟΥΜΕΝΟΙ', NULL, NULL, NULL, NULL, NULL),
       (2, N'8701201000', NULL, NULL, N'ΚΑΙΝΟΥΡΓΙΟΙ', NULL, NULL, NULL, NULL, NULL),
       (3, N'8701209000', NULL, NULL, N'ΜΕΤΑΧΕΙΡΙΣΜΕΝΟΙ', NULL, NULL, NULL, NULL, NULL)
      .
      .
      .

The line that always give me the error is this

INSERT INTO [dbo].[KodikosTaric] ([Id], [KodikosTaric], [KoinotikonMetron_a], [KoinotikonMetron_b], [Perigrafi], [DasmosTritonXoron], [ProtimisiakosDasmos], [SimpliromatikesMonades], [YpologismosKila], [DiasafistisId]) 
VALUES (782, N'2009809711', NULL, NULL, N'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ GOYAVES', NULL, NULL, NULL, NULL, NULL)

It doesn't matter in which line it is. I try putting it on first line of the file and it throw the same error.

The funny is that if I import that file from SQL Server Express as query it works great.

EDIT: Thanks to @Chris J I start understanding the problem. So the problem is that in the file there is the word

GOYAVES

which when I split the text it sees it as GO and not as GOYAVES. I tried to change the split command

var sqlqueries = script.Split(new[] { "\nGO", "\ngo" }, StringSplitOptions.RemoveEmptyEntries);

and I don't have the exception any more but the problem is even though it seems to working and splits correct the sql file it doesn't write anything to the table.

EDIT 2: I removed those lines from the sql script

SET NUMERIC_ROUNDABORT OFF
GO

    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
    GO

    SET DATEFORMAT YMD
    GO

    SET XACT_ABORT ON
    GO

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    GO

    BEGIN TRANSACTION
    GO

and everything worked fine. Thanks everyone for your help

Answer 1

The problem is most likely here:

N'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ GOYAVES'
                               ^^

Earlier in your C# code you're doing:

var sqlqueries = script.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);

So it's likely splitting your SQL query on that 'GO' that's in the substring. One other clue to this is the error itself:

Unclosed quotation mark after the character string 'ΧΥΜΟΙ ΑΧΛΑΔΙΩΝ ΤΗΣ ΠΟΙΚΙΛΙΑΣ '.

This string is a substring of that full string up to and including the space.

You probably need to change your C# to look only for GO at the start of a line.

As for the best way of doing that, it looks like your code reads the file in to memory whole as a string and then you split on that string. For large files, this could be slow and memory inefficient: it's something I generally try and avoid doing myself.

A more efficient method would be to split as you read the file - this saves having to read and process a large string in memory. Something like this would do the job:

private IEnumerable<string> GetStatement(string sqlFile)
{
    using (var sr = new StreamReader(sqlFile))
    {
        string s;
        var sb = new StringBuilder();
        while ((s = sr.ReadLine()) != null)
        {
            if (s.Trim().Equals("GO", StringComparison.InvariantCultureIgnoreCase))
            {
                yield return sb.ToString();
                sb.Clear();
                continue;
            }

            sb.AppendLine(s);
        }

        yield return sb.ToString();
    }
}

This method reads the file line by line, and when it encounters "GO" on a line by itself it returns that batch. It can be called in a simple for each:

foreach (var batch in GetStatement("Batch.sql"))
{
    Console.WriteLine(batch);
}

If you'd prefer to still read the file in full, and process a string, then you can swap the StreamReader out for a StringReader and apply the same method. But unless you really need to load the entire file into memory, it's simpler (and potentially faster for large files) to avoid doing that in the first instance and just process as you go.

Answer 2

How about this:

  var sqlqueries = script.Split(new[] { "\rGO", "\r\nGO" }, 
             StringSplitOptions.RemoveEmptyEntries);

The two strings will match the likely forms of line breaks.

See How to split strings on carriage return with C#?