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

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. — Рахул Маквана
– Рахул Маквана, Commented Aug 18, 2016 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
– CodeGenius, Commented Aug 18, 2016 at 13:33

PhillipH · Accepted Answer · 2016-08-18 13:31:35Z

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, 2016 at 13:31

PhillipH

6,2221 gold badge17 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CodeGenius Over a year ago

can you please show some code of doing so?

PhillipH Over a year ago

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. msdn.microsoft.com/en-us/library/h6ak8zt5(v=vs.110).aspx

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related