C#: How to import SQL-script into database programmatically?

Question

Do I have to parse the SQL-script manually and execute each statement separately or are there better ways? Iam looking for a programmatically solution, I know there are tools which are already able to do this. It would be good if the solution would work for all database systems, not just sqlite.

Answer 1

Using the DbCommand.ExecuteNonQuery method you are able execute any SQL against the database.

Sample with a SqlCommand:

var command = new SqlCommand("CREATE TABLE foo ...", connection);
command.ExecuteNonQuery();

As suggested in another answer you may need to split the command if it contains 'GO', since that will split the command in multiple batches.

Answer 2

I'm not sure how this applies to SqlLite, but I've used code like the following with SqlServer:

protected virtual void ExecuteScript(SqlConnection connection, string script)
{
    string[] commandTextArray = script.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries); // See EDIT below!
    connection.Open();
    foreach (string commandText in commandTextArray)
    {
        if (commandText.Trim() == string.Empty) continue;
        SqlCommand command = new SqlCommand(commandText, connection);
        command.ExecuteNonQuery();
    }
    connection.Close();
}

(Warning: I had to modify my original code a little bit so this is untested code.)

I found that the word "GO" was a problem when running scripts via ADO.NET, so the code does a Split() operation on the overall script with "GO" as the delimiter, then loops through the array of commands and executes them one at a time.

EDIT:

@Mark's comment below that "GO" might appear in a script as part of another word is definitely a valid concern. I remember when I wrote the above code, I did a search of my scripts to ensure that "GO" only appeared as a batch separator. Another way to go (no pun intended) would be to edit all your scripts so that "GO" is always followed by the same comment e.g., " -- SPLIT HERE!" and change your delimiter to "GO -- SPLIT HERE!". This requires you to edit your scripts, however. Yet another option is Regex.Split(), which would allow you to check for white space before GO. In my scripts, my batch separators always go on their own line. If the same rule applies to your script, something like the following ought to work:

string[] commandTextArray = System.Text.RegularExpressions.Regex.Split(script, "\r\n[\t ]*GO");

The bottom line is that some formatting consistency for batch separators is required to accurately split up your script.

Answer 3

I'm not familiar with SqlLite, but most RDBMS' will let you send multiple statements if you end them with semicolons. Some, like MSSQL, will also accept newline as a statement terminator. Semicolon + newline makes most happy, and leads to well formatted code as well.

If you have batch separators - in MSSQL, it's usually "GO" - then you have to do a bit more work, as that's parsed by the client. There's been a couple of solutions to handle MSSQL - but I don't know how portable (if at all) they are. I'd suspect Haack's would be the best starting point.

Answer 4

In the following code Config.Database.CreateCommandText is a collection of semicolon separated statements for creating tables and indexes in a database. The following creates a DB from nothing using System.Data.SQLite.

private SQLiteCommand command;
private SQLiteConnection connection;

connection = new SQLiteConnection(Config.Database.ConnectionString.ToString());
connection.Open();

command = connection.CreateCommand();

SQLiteConnection.CreateFile(Config.Database.Path);

command.CommandText = Config.Database.CreateCommandText;

command.ExecuteNonQuery();