How to run a SQL Server 2008 R2 Database Script using sqlcmd in C#?

Question

i am new to run SQL Scripts using sqlcmd in C#. i saw some code in Internet but i am not understanding how it Works.

string path = string.Empty;
            OpenFileDialog opd = new OpenFileDialog();
            opd.Filter = "sql files|*.sql";
            if (opd.ShowDialog() == DialogResult.OK)
            {
                path = opd.FileName;//Here i am taking Database sqlScript
            }
        string tmpFile = Path.GetTempFileName();
            SqlConnectionStringBuilder connection=new SqlConnectionStringBuilder(@"Data Source=LPTP2\LPTP2;Initial Catalog=Database;Integrated Security=True");
        string argument = string.Format(@" -S {0} -d {1} -i ""{2}"" -o ""{3}""",
            @".\SQLEXPRESS", "Database", path, tmpFile);

        // append user/password if not use integrated security
        if (!connection.IntegratedSecurity)
            argument += string.Format(" -U {0} -P {1}", "sa", "abc@123");

        var process = Process.Start("sqlcmd.exe", argument);
        process.StartInfo.UseShellExecute = false;
        process.StartInfo.CreateNoWindow = true;
        process.Start();
        while (true)
        {
            // wait for the process exits. The WaitForExit() method doesn't work
            if (process.HasExited)
                break;
            Thread.Sleep(500);
        }

i am not understanding how these three lines are working

 string tmpFile = Path.GetTempFileName();
            SqlConnectionStringBuilder connection=new SqlConnectionStringBuilder(@"Data Source=LPTP2\LPTP2;Initial Catalog=HemoTrace;Integrated Security=True");
        string argument = string.Format(@" -S {0} -d {1} -i ""{2}"" -o ""{3}""",
            @".\SQLEXPRESS", "HemoTrace", path, tmpFile);

        // append user/password if not use integrated security
        if (!connection.IntegratedSecurity)
            argument += string.Format(" -U {0} -P {1}", "sa", "abc@123");

Why i am doing this means i want to run a SQL SCRIPT the script which execute to create a database. but i want to do using sqlcmd. In Client Place if i execute my .exe file it finish my work(to attach database to Server).

Please help me regarding this.

Answer 1

string tmpFile = Path.GetTempFileName();

Declare a string variable called tmpFile and use Path.GetTempFileName() to generate a unique temp file name and store it in the variable

SqlConnectionStringBuilder connection=new 
SqlConnectionStringBuilder(@"Data Source=LPTP2\LPTP2;Initial Catalog=HemoTrace;
Integrated Security=True");

Use the SqlConnectionStringBuilder class to build a SQL Server connection string. This doesn't actually connect to anything, it just generates a connection string.

string argument = string.Format(@" -S {0} -d {1} -i ""{2}"" -o ""{3}""",
@".\SQLEXPRESS", "HemoTrace", path, tmpFile);

declare a string called argument and set it to a a bunch of characters, including the path to the temp file that was generated earlier. This bunch of characters is suitable to use as arguments to the SQLCMD command line.

// append user/password if not use integrated security
if (!connection.IntegratedSecurity)
argument += string.Format(" -U {0} -P {1}", "sa", "abc@123");

Use a property of the SqlConnectionStringBuilder class to work out if we should add the command line switch to indicate trusted security.

After all of this you run:

var process = Process.Start("sqlcmd.exe", argument);

If you dump this fill string out you'll find something that can be run on the command line.

SQLCMD is a command line program, which incidentally needs to be installed on your client machine.

The command line program takes a bunch of arguments which you have built in previous lines of your code.

There are some issues in this code:

• You need to have SQLCMD.EXE istalled for it to work.
• You hard code trusted security in a string, load this into a special class then use that class to work out if you're using trusted security... you've already hard coded it!
• You also hard code a certain server in the connection string but then hard code a different server in the arguments for SQLCMD
• It appears the connection string (the middle line) is totally redundant in this case.