Run a script on existing database from sql file in c#

Question

I have created an empty database. I need to run script on this empty database. So far I have found code from a website:

public bool RunScript()
        {
            try
            {

                SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-PCEOPRM\SQLEXPRESS;Initial Catalog=" + DbName + ";Integrated Security=True");


                con.Open();
                string script = File.ReadAllText(@"C:\inetpub\wwwroot\MetisTemplateDBScript\MetisEmptyDBScript.sql");
                IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$",
                           RegexOptions.Multiline | RegexOptions.IgnoreCase);

                foreach (string commandString in commandStrings)
                {
                    if (commandString.Trim() != "")
                    {
                        using (var command = new SqlCommand(commandString, con))
                        {
                            command.ExecuteNonQuery();
                        }
                    }
                }
                con.Close();

                return true;
            }catch(Exception exc)
            {
                return false;
            }
        }

But when I run it, it throws some exception due to '\n','\r' or simple slashes etc. Is there some proper method of running a large script from sql file? My file also contains GO statements.

It's not a duplicate question as it has two things 1) Go command and 2) escape characters.

Thanks is advance!

instead of posting this code, showing SQL Query would help I think. because there ain't any "\n,\r" in this code. — Рахул Маквана, Aug 18 '16 at 13:31
it shows syntax errors in strings.. My string either has escape characters or \n,\r,\t. I also replaced them with simple space but it did not work. The problem is reading from file. How can i get rid of these escape things? — CodeGenius, Aug 18 '16 at 13:33

score 1 · Answer 1 · answered Aug 18 '16 at 13:31

1

SQL Server ships with a command line SQL execution utility called SqlCmd (https://msdn.microsoft.com/en-us/library/ms162773.aspx) . You can use it to execute any valid script. If you really need to run it from C# you can use the System.Diagnostics.Process namespace classes to start a process which executes SqlCmd.

answered Aug 18 '16 at 13:31

PhillipH

6,182
1
15
25

can you please show some code of doing so? – CodeGenius Aug 18 '16 at 13:34
The Process class is well documented, you just need to look up the relevant online docs. Heres a link to the correct variant of the Process.Start method with a working example. https://msdn.microsoft.com/en-us/library/h6ak8zt5(v=vs.110).aspx – PhillipH Aug 19 '16 at 07:20

Run a script on existing database from sql file in c#

1 Answers1

Linked