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.

score 0 · Answer 1 · answered Mar 10 '14 at 06:17

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.

But i want to know the result of above one i am not getting any thing as result — Anjali, Mar 10 '14 at 06:55
result in what? in the variable? in the database? I suggest you make a breakpoint and dump out the `argument` variable. Now go into a command prompt (Start/Run/CMD) and type `SQLCMD.EXE` + the value of argument (i.e. exactly what your program is doing), and you will probably get more information. You should do some more research on SQLCMD. Note your output goes to a temp file which is not very helpful - why not write the output to a file that you can find afterwards? — Nick.Mc, Mar 10 '14 at 21:41
If i done the above thing in command prompt i am getting timed out — Anjali, Mar 11 '14 at 04:42
Please post the exact error. Based on those parameters, you need a local SQL Server installed with and instance name of SQLEXPRESS. Are you able to test a connection to your locally installed SQLEXPRESS (using ODBC, UDL, SQL Server Management Studio?), because this is what it is trying to connect to. Observe that you need to improve your code to pick up these errors and report them. — Nick.Mc, Mar 11 '14 at 05:44

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

1 Answers1