SQLCmdHelper

using System;
using System.Diagnostics;
using System.IO;
using System.Text;

 namespace  Core.Data {
    public  class  SQLCmdHelper {

         #region  Excecute sqlcmd.exe
         string  GetSqlcmdPath() {
             //might be different on other boxes
             return  @"C:\\Program Files\\Microsoft SQL Server\\100\\Tools\\Binn\\SQLCMD.EXE" ;
         }
         /// <summary>
         /// Gets a temp file in C:\\Users\\{USER}\\AppData\\Local\\Temp\\_SQLTEMP directory
         /// </summary>
         /// <param name="filename"></param>
         /// <returns></returns>
         string  GetTempFilePath(string  filename = null ) {
             string  tempDirectory = Path .Combine(Path .GetTempPath(),"_SQLTEMP" );

             if (!Directory .Exists(tempDirectory))
                 Directory .CreateDirectory(tempDirectory);

             if (filename == null )
                 filename = Path .GetFileName(Path .GetTempFileName());
             return  Path .Combine(tempDirectory,filename);
         }
         bool  ExcecuteSQLScript(string  sqlbatch) {

            string  sqlServerInstance = ".\\\\SQLExpress" ;

            return ExcecuteSQLScript(sqlbatch,sqlServerInstance);
         }
         /// <summary>
         /// Executes the sql code
         /// </summary>
         /// <param name="sqlbatch"></param>
         /// <returns></returns>
         bool  ExcecuteSQLScript(string  sqlbatch,string sqlServerInstance) {

             if (string .IsNullOrWhiteSpace(sqlbatch))
                 throw  new  ArgumentNullException ("sqlbatch" );


             bool  deleteTempFiles = true ;

             string  sqlcmdPath = GetSqlcmdPath();
             string  cmdline = string .Empty;

             string  scriptPath = GetTempFilePath("DBSCRIPT.sql" );
             string  outputPath = GetTempFilePath("DBSCRIPT_OUTPUT.sql" );

             if (File .Exists(scriptPath))
                 File .Delete(scriptPath);
             if (File .Exists(outputPath))
                 File .Delete(outputPath);

             try  {

                 //write the dropall text to temp file
                 using (FileStream  file = new  FileStream (scriptPath,FileMode .CreateNew,FileAccess .Write)) {

                     file.Write(
                         Encoding .Default.GetBytes(sqlbatch),
                         0,
                         sqlbatch.Length);

                     file.Flush();
                     file.Close();
                 }

                 cmdline = " -E " ;   // trusted connection
                 cmdline += " -S "  + sqlServerInstance;  // server name/instance
                 cmdline += " -i "  + scriptPath;   //sql script file path
                 cmdline += " -o "  + outputPath;   //output file path

                 Status(Environment .NewLine);
                 Status(sqlcmdPath,true );
                 Status(cmdline,true );
                 Status(Environment .NewLine);
                 Status("Running Scripts..." );

                 //create process with arguments
                 ProcessStartInfo procInfo = new  ProcessStartInfo(sqlcmdPath);
                 procInfo.UseShellExecute = false ;
                 procInfo.CreateNoWindow = true ;
                 procInfo.Arguments = cmdline;
                 Process sqlproc = Process.Start(procInfo);

                 //run sqlproc and wait for 60 seconds
                 if (sqlproc.WaitForExit(60000)) {
                     if (File .Exists(outputPath)) {
                         Status(Environment .NewLine);
                         Status("Completed sqlcmd.exe" );

                         Status("Results:" );

                         Status(File .ReadAllText(outputPath));

                         if (deleteTempFiles) {
                             if (File .Exists(scriptPath))
                                 File .Delete(scriptPath);

                             if (File .Exists(outputPath))
                                 File .Delete(outputPath);
                         }
                         return  true ;
                     }
                     else  {
                         Status("No output for returned" );
                         return  false ;
                     }
                 }
                 else  {
                     Status("Scripts timeout" );
                     return  false ;
                 }
             }
             catch (Exception  error) {
                 Status(error.ToString());
                 return  false ;
             }
         }
         #endregion

    }
 }