none
Run a .sql script file in C#

    Question

  • Hello!

     

    I need to run an sql script file from code.
      I have a folder with lots of .sql files, which the app will use if it decides the database is out of date.

    I can't work out how to run the whole file?

     

    I am expecting there is be something like cmd.executeFile("file.sql") somewhere but I can't find anything like it.

     

    Is it possible do this or do I need to separate it out the file some how?

     

    Thanks

     

    Bex

    Wednesday, July 18, 2007 2:05 PM

Answers

  • You can't run files directly, with just specifing file name. You must first read it's content in a string and then execute CommandText command.
    But this will work as long you don't have GO statement in your sql script. From your post it looks like that SqlCommand is not usefull because scripts that include DDL command are completed with batch finalizer GO command. Luckily, since SQL Server 2005, you can use smo library to do this operation and not use batch command and osql connection as previously. Here is how to do it:

    using System.Data.SqlClient;

    using System.IO;

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo;

     

    namespace ConsoleApplication1

    {

        class Program

        {

            static void Main(string[] args)

            {

                string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

                FileInfo file = new FileInfo("C:\\myscript.sql");

                string script = file.OpenText().ReadToEnd();

                SqlConnection conn = new SqlConnection(sqlConnectionString);

                Server server = new Server(new ServerConnection(conn));

                server.ConnectionContext.ExecuteNonQuery(script);

            }

        }

    }

    Wednesday, July 18, 2007 2:42 PM

All replies

  • You can't run files directly, with just specifing file name. You must first read it's content in a string and then execute CommandText command.
    But this will work as long you don't have GO statement in your sql script. From your post it looks like that SqlCommand is not usefull because scripts that include DDL command are completed with batch finalizer GO command. Luckily, since SQL Server 2005, you can use smo library to do this operation and not use batch command and osql connection as previously. Here is how to do it:

    using System.Data.SqlClient;

    using System.IO;

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo;

     

    namespace ConsoleApplication1

    {

        class Program

        {

            static void Main(string[] args)

            {

                string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

                FileInfo file = new FileInfo("C:\\myscript.sql");

                string script = file.OpenText().ReadToEnd();

                SqlConnection conn = new SqlConnection(sqlConnectionString);

                Server server = new Server(new ServerConnection(conn));

                server.ConnectionContext.ExecuteNonQuery(script);

            }

        }

    }

    Wednesday, July 18, 2007 2:42 PM
  • Thanks for the info!
    I will try that!
    Wednesday, July 18, 2007 3:03 PM
  •  

    Hi i m getting errors while executing because of \n \t \r in the text file while reading. Even the 'GO' statement is giving errors
    Friday, August 31, 2007 5:23 AM
  • Start a transaction, read and execute line by line, commit the transaction or rollback.
    Friday, August 31, 2007 5:49 AM
  •  

    Can u help me with that how can i achieve that. Thank You
    Friday, August 31, 2007 5:51 AM
  • Which part? If i type "c# start transaction" and "c# read line by line" in my favorite search-engine i get many valuable answers...

    While doing this, didn't take 5 minutes, i stumbled upon http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdedepl.asp which even contains the code you're looking for (but it's in vb)..
    Friday, August 31, 2007 6:16 AM
  • Code I've posted is working fine. This thread targets the problem of running script files with DDL commands, which means they have '\r\n' new lines and especially 'GO' statemens. In fact SqlCommand object can also execute command text with 'r\n' in the string, but can't execute GO statement.
    So you have some other problem.

     

    Friday, August 31, 2007 6:49 AM
  • I'm sorry. I would like to run sql script on VS2003.  VS2003 can't use Microsoft.SqlServer file.

    Do you have other way for help me?

    Wednesday, September 05, 2007 8:02 AM
  •  

    Great tip (post nr.2)! Helped me through a large .sql file  Smile

     

    The files you need to reference are:

    • Microsoft.SqlServer.ConnectionInfo
    • Microsoft.SqlServer.Smo

    Took me a few minutes to find at first. If you can't find them in the .NET tab, you might not have it installed. The paths on my computer to these files are:

    • c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll

    • c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

    Hope it saves some time looking!

     

    Mats

    Wednesday, October 10, 2007 11:27 AM
  • If I want to pass variables to the sql file,how to do that ?

     

    Thursday, June 12, 2008 7:49 PM
  •  

    Should'n we be using 'using' statement for SQLConnection? It implements Dispose() interface.

     

    using(SqlConnection conn = new SqlConnection(sqlConnectionString))

    {

                Server server = new Server(new ServerConnection(conn));

                server.ConnectionContext.ExecuteNonQuery(script);

    }

    Tuesday, August 19, 2008 9:33 AM
  • it works~

    thanks~

    Saturday, September 27, 2008 6:53 AM
  • HI!!

     

    I have a sql file and I want to give some parameters to it and then run it. My guess is, how could I do it  with the code given by boban.s? it's possible to pass some parameters to the sql file?

     

    Thanks!!

     

    I'm venezuelan so forgive me for my english.

    Wednesday, October 01, 2008 2:51 PM
  •  

    Hi Boban,

    I really love your trick, for the people how had some issue with special characters  “\n”, “\r” or “\t” they can use

    string script = file.OpenText().ReadToEnd();

    from Boban code follow by

    script = script.Replace(“\t”,”  “).Rplace(“\n”,” “);

    I think the “\r” works fine, otherwise replace it too.

    and if you have some parameter to change like Param1 try to use

    script = script.Replace(“Param1”,” ‘Test value’ “);

    Thanks

    • Proposed as answer by Waleed A.K. _ Tuesday, March 24, 2009 7:22 PM
    Tuesday, March 24, 2009 7:20 PM
  • Hi All:

    Thanks for this solution it help me so much, but it's not work when I tried it for a file contains more than one stored procedure, it always give me an error says "Incorrect syntax near the keyword 'PROCEDURE'. Must declare the scalar variable '@Parameter' ", any body have an idea about this problem.

    thanks in advance
    Monday, April 13, 2009 1:25 PM
  • From the error message, it looks that your script is not correct. Try the same script in management studio in order to debug and find the problem.
    There is no limation in executing script on the provided solution. What is required is that script must be correct.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/
    Monday, April 13, 2009 2:04 PM
  • Hi boban:

    I tried the procedures in the management studioa and it's return the same error because there are no GO at the end of each procedure so when I add GO at the end of each one it's going right in the management studio but not in the code because of GO.

    here is a sample of stored procedures which i use
    /****** Object:  StoredProcedure [dbo].[UserEmployee_SelectEmployee]    Script Date: 04/13/2009 15:00:09 ******/
    CREATE PROCEDURE [UserEmployee_SelectEmployee]
    	@lang varchar(10)
    AS
    BEGIN
    	SELECT	
    		EmployeeID ,
    	case @lang
    		when 'Arabic' then 
    			EmployeeNameA
    		else
    			EmployeeNameE
    		end AS EmployeeName               
    	FROM
    		Employee
    		ORDER BY EmployeeName;
    END
    
    /****** Object:  StoredProcedure [dbo].[DisplayContract_InsertDisplayContract]    Script Date: 04/13/2009 14:58:14 ******/
    CREATE PROCEDURE [DisplayContract_InsertDisplayContract](
    	@P_ClientID                     int,
    	@P_ContractDate                 datetime,
    	@P_ReceiveDate                  datetime,
    	@P_StartDate                    datetime,
    	@P_EndDate                      datetime,
    	@P_Comments                     nvarchar (512),
    	@P_CreatedBy                    nvarchar (30))
    AS
    BEGIN
    
    	INSERT INTO DisplayContract(		
    		ClientID,
    		ContractDate,
    		ReceiveDate,
    		StartDate,
    		EndDate,
    		Comments,
    		CreationDate,
    		CreatedBy)
                                    
    	VALUES (
    		@P_ClientID,
    		@P_ContractDate,
    		@P_ReceiveDate,
    		@P_StartDate,
    		@P_EndDate,
    		@P_Comments,
    		getdate(),
    		@P_CreatedBy);	
    END
    

    Tuesday, April 14, 2009 6:16 AM
  • Put the batch terminator GO after each procedure, and the script should work from code.
    MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/
    Tuesday, April 14, 2009 8:24 AM
  • thank for your quick reply, I tried it before but still the same problem
    Tuesday, April 14, 2009 9:08 AM
  • My problem was solved thank you so much, the problem was in the code because I remove new lines from the procedures
    Wednesday, April 15, 2009 6:55 AM
  • If you would like to run a script generated by sql server try to remove all “GO”. See the code below


    FileInfo

    file = new FileInfo(“Put The File Name Here”);

    string SQLscript = file.OpenText().ReadToEnd();

    SQLscript = SQLscript.Replace("GO", "");

    //Optional to Replace Comments with empty string

    SQLscript = Regex.Replace(SQLscript, "([/*][*]).*([*][/])", "");

    //Optional to Replace Chain of spaces with one Space

    SQLscript = Regex.Replace(SQLscript, "\\s{2,}", " ");

    SqlCommand sqlCommand = new SqlCommand(SQLscript, “Put your Connection Here”);

    sqlCommand.Connection.Open();

    sqlCommand.ExecuteNonQuery();

    sqlCommand.Connection.Close();

     

    Friday, April 17, 2009 9:17 PM
  • Hi,

    Just today i have use the following code:

    /// <summary>
            /// Crea una tabla en la base de datos ZAION
            /// </summary>
            /// <param name="script">El script SQL generado por Zaion</param>
            /// <param name="conexion">La cadena de conexión a usar</param>
            public static void CreaTablaZaion(string script, string conexion)
            {
                Microsoft.Practices.EnterpriseLibrary.Data.Database _db = DatabaseFactory.CreateDatabase(conexion);
                DbConnection _cmd = _db.CreateConnection();
                using (SqlConnection conn = new SqlConnection(_cmd.ConnectionString))
                {

                    try
                    {
                        Server server = new Server(new ServerConnection(conn));
                        server.ConnectionContext.ExecuteNonQuery(script);
                    }
                    catch (Exception ex)
                    {
                        _lasterror = ex.Message;
                        throw new ApplicationException(String.Format("Ocurrió un error al intentar crear el esquema de plantilla.\r\nMás detalles:\r\n{0}", _lasterror));
                    }
                }
            }

    You will need to add the following references:

    using System.Data.SqlClient;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System.Data.Common;

    Regards
    TESTED
    • Proposed as answer by Tabas Monday, May 25, 2009 9:40 PM
    Monday, May 25, 2009 9:40 PM
  • Thanks for this piece of code, I'm using and its working fine but...

    - When a create procedure is in the script:
      - if "GO" not is in the script--> Error "Create Procedure must be the first instruction..."
      - if "GO" is in the script --> Syntax Error

    Any idea to solve this problem ?

    Finally, I think the regular expression to replace comments doesn't work. I test in the notepad++.

    Quim
    Wednesday, August 05, 2009 4:34 PM
  • Hello Quim,

    Did you ever tried  sp_executesql ?

    -Waleed A.K

    • Proposed as answer by Waleed A.K. _ Tuesday, August 11, 2009 10:25 PM
    Tuesday, August 11, 2009 10:21 PM
  • Thanks Waleed

    I solved it following the TIMVW's proposal:


    - Start a transaction, read and execute line by line, commit the transaction or rollback: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdedepl.asp

    Quim Chalé

    Wednesday, August 12, 2009 7:24 AM
  • Hello,

    At the line:

    server.ConnectionContext.ExecuteNonQuery(script);
    I'm getting this error:

     

    Test method IsItScienceFiction.Tests.UserTest.TestMethod1 threw exception:  System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->  System.TypeInitializationException: The type initializer for '<Module>' threw an exception. --->  <CrtImplementationDetails>.ModuleLoadException: The C++ module failed to load during appdomain initialization.
     --->  System.DllNotFoundException: Unable to load DLL 'MSVCR80.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E).

     

    with this stack trace:

     

    _encode_pointer(Void* )
    _initatexit_app_domain()
    LanguageSupport.InitializePerAppDomain(LanguageSupport* )
    LanguageSupport._Initialize(LanguageSupport* )
    LanguageSupport.Initialize(LanguageSupport* )
    ThrowModuleLoadException(String errorMessage, Exception innerException)
    ThrowModuleLoadException(String , Exception )
    LanguageSupport.Initialize(LanguageSupport* )
    cctor()
    Microsoft.SqlServer.Management.Common.ExecuteBatch.GetStatements(String sqlCommand)
    System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
    System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
    System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
    System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
    Microsoft.SqlServer.Management.Common.ServerConnection.GetStatements(String query, ExecutionTypes executionType, Int32& statementsToReverse)
    Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
    Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)
    IsItScienceFiction.Tests.UserTest.TestMethod1() in C:\Users\J. Pablo Fernández\Documents\Visual Studio 2008\Projects\IsItScienceFiction\IsItScienceFiction.Tests\UserTest.cs: line 75

     

    I am running Visual Studio 2008 with SQL Server 2008. What am I doing wrong?

    • Proposed as answer by 205ger Monday, September 21, 2009 1:29 PM
    Saturday, September 19, 2009 9:49 PM
  • Hi See this solution: Console


            static void Main(string[] args)
            {
                Console.WriteLine("1-Hola");
                Console.WriteLine("2-Genera Conextion String");
                string sqlConnectionString = @"...."
                SqlConnection cn = new SqlConnection(sqlConnectionString);

                Console.WriteLine("3-Verificando esta de conexion");

                      if (cn.State==ConnectionState.Closed)

                      {
                          Console.WriteLine("4-Por abrir conexion");
                        cn.Open();
                        Console.WriteLine("5-Conxion Abierta");

                      }

                 SqlCommand cmd;

                 FileInfo file = new FileInfo("C:\\apolo2.sql");
                 Console.WriteLine("6-Por leer archivo");
                 string sql = file.OpenText().ReadToEnd();
                 Console.WriteLine("7-archivo ya leido y paso a reemplazo GO");
            
                 string scriptText = sql;

                 //split the script on "GO" commands
                 string[] splitter = new string[] { "\r\nGO\r\n" };
                 string[] commandTexts = scriptText.Split(splitter,
                   StringSplitOptions.RemoveEmptyEntries);

                 int i=0;
                    Console.WriteLine("8-Actualizando");
                 foreach (string commandText in commandTexts)
                 {
                     if (cn.State == ConnectionState.Closed)
                     {
                        
                         cn.Open();

                     }
                     i++;
                     string x = commandText.Replace("GO", "");
                     cmd = new SqlCommand(x, cn);
                     try
                     {
                         Console.Write(i.ToString() + "-");
                        
                         cmd.ExecuteNonQuery();
                      
                     }
                     catch (SqlException sqlexception)
                     {
                         Console.WriteLine(sqlexception.Message);
                     }
                     catch (Exception ex)
                     {
                         Console.WriteLine(ex.Message);
                     }
                     finally
                     {
                         cn.Close();

                     }
                    
                    
                    
                     //execute commandText
                 }
                   

     

          Console.WriteLine("10-Cerrada la base OK");
          Console.ReadKey();
              
            }

    • Proposed as answer by 205ger Monday, September 21, 2009 1:45 PM
    Monday, September 21, 2009 1:30 PM
  • Cool ! But how to carry out this on production server ?

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo;

     

    are not supported by webforms..

     


    Saturday, October 03, 2009 7:44 AM
  • Hi,

    I also have the same requirement i.e. I have to create database and all table using the C# application. I have all command in a

    single .sql file. Currently I am using sqlCommand.ExecuteNonQuery() to perform execute sql commands. But as you mention another

    way to do the same. Can you please help me which one is better in context of Application performance etc ?
    Wednesday, October 07, 2009 12:07 PM
  • Hello
    I have similar issues.

    SQL Server 2008

    Using all the correct references I dare say:
    System.Data.SqlClient;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Sdk.Sfc;

    All compiles with no errors.

    I have stripped code down to almost zero for easy debugging.

    Connecting to server alright and so on.

    Excuting following code:
    SqlConnection connection = new SqlConnection(sqlConnectionString);
    Server server = new Server(new ServerConnection(connection));
    server.ConnectionContext.ExecuteNonQuery(sqlDBQuery);

    Where "sqlDBQuery" is a string: "USE [master]  GO  ALTER DATABASE [Cassiopeia] SET ANSI_NULL_DEFAULT OFF  GO  ALTER DATABASE [Cassiopeia] SET ANSI_NULLS OFF  GO"

    But it doesn't matter what "sqlDBQuery" is, I always get the same error, like "incorrect syntax near GO".

    I was in belief that SMO would take care of this, when I look at my ConnectionContext is says Batchseparator = "GO"

    If I remove GO it's a go... so to speak but I really need to know why my SMO doesn't work.

    Hope I didn't post something not applicable to this thread, but everywhere I look it just says "use smo like this and you're off fine". Well... doesn't work for me.

    Regards/

    • Proposed as answer by Malakin Monday, November 16, 2009 6:35 PM
    Tuesday, October 20, 2009 4:16 PM
  • I don't know if this will help any, but i find this way much more reliable.
    Have fun and take what you need.

    /// <summary>
    /// Run an .sql script trough sqlcmd.
    /// </summary>
    /// <param name="fileName">the .sql script</param>
    /// <param name="machineName">The name of the server.</param>
    /// <param name="databaseName">The name of the database to connect to.</param>
    /// <param name="trustedConnection">Use a trusted connection.</param>
    /// <param name="args">The arguments passed to the sql script.</param>
    public void RunSqlScript(string fileName, string machineName, string databaseName, bool trustedConnection, string[] args)
    {
        // simple checks
        if (!Path.GetExtension(fileName).Equals(".sql", StringComparison.InvariantCulture))
            throw new Exception("The file doesn't end with .sql.");
    
        // check for used arguments
        foreach (var shortArg in new[] { "S", "d", "E", "i" })
        {
            var tmpArg = args.SingleOrDefault(a => a.StartsWith(string.Format("-{0}", shortArg), StringComparison.InvariantCulture));
            if (tmpArg != null)
                throw new ArgumentException(string.Format("Cannot pass -{0} argument to sqlcmd for a second time.", shortArg));
        }
    
        // check the params for trusted connection.
        var userArg = args.SingleOrDefault(a => a.StartsWith("-U", StringComparison.InvariantCulture));
        var passwordArg = args.SingleOrDefault(a => a.StartsWith("-P", StringComparison.InvariantCulture));
        if (trustedConnection)
        {
            if (userArg != null)
                throw new ArgumentException("Cannot pass -H argument when trustedConnection is used.");
            if (passwordArg != null)
                throw new ArgumentException("Cannot pass -P argument when trustedConnection is used.");
        }
        else
        {
            if (userArg == null)
                throw new ArgumentException("Exspecting username(-H) argument when trustedConnection is not used.");
            if (passwordArg == null)
                throw new ArgumentException("Exspecting password(-P) argument when trustedConnection is not used.");
        }
    
    
        // set the working directory. (can be needed with ouputfile)
        // TODO: Test if the above statement is correct
        var tmpDirectory = Directory.GetCurrentDirectory();
        var directory = Path.IsPathRooted(fileName) ? Path.GetDirectoryName(fileName) : Path.Combine(this.ProjectRoot, fileName);
        var file = Path.GetFileName(fileName);
        Directory.SetCurrentDirectory(directory);
    
        // create cmd line
        var cmd = string.Format(string.Format("SQLCMD -S {0} -d {1} -i \"{2}\"", machineName, databaseName, file));
        foreach (var argument in args.Where(a => a.StartsWith("-", StringComparison.InvariantCultureIgnoreCase)))
            cmd += " " + argument;
        if (trustedConnection)
            cmd += " -E";
    
        // create the process
        var process = new System.Diagnostics.Process();
        process.StartInfo.FileName = "cmd";
        process.StartInfo.CreateNoWindow = true;
        process.StartInfo.UseShellExecute = false;
        process.StartInfo.RedirectStandardOutput = true;
        process.StartInfo.RedirectStandardInput = true;
    
        // start the application
        process.Start();
        process.StandardInput.WriteLine("@ECHO OFF");
        process.StandardInput.WriteLine(string.Format("cd {0}", directory));
        process.StandardInput.WriteLine(cmd);
        process.StandardInput.WriteLine("EXIT");
        process.StandardInput.Flush();
        process.WaitForExit();
        
        // write the output to my debug folder and restore the current directory
        Debug.Write(process.StandardOutput.ReadToEnd());
        Directory.SetCurrentDirectory(tmpDirectory);
    }
    • Proposed as answer by Malakin Monday, November 16, 2009 6:39 PM
    • Edited by Malakin Monday, November 16, 2009 7:18 PM Added 'EXIT' Command. (this will end the process when its finished)
    Monday, November 16, 2009 6:38 PM
  • What a great yeoman service from a geek!!!!!!!

    Malakin what does this.ProjectRoot to be replaced with when testing on local cassini

    Error    1    'RunSql' does not contain a definition for 'ProjectRoot' and no extension method 'ProjectRoot' accepting a first argument of type 'RunSql' could be found (are you missing a using directive or an assembly reference?)    C:\Documents and Settings\hsj\Desktop\TestSQL\RunSql.aspx.cs  
    Monday, November 16, 2009 8:51 PM
  • I don't know if this will help any, but i find this way much more reliable.
    Have fun and take what you need.

    /// <summary>
    /// Run an .sql script trough sqlcmd.
    /// </summary>
    /// <param name="fileName">the .sql script</param>
    /// <param name="machineName">The name of the server.</param>
    /// <param name="databaseName">The name of the database to connect to.</param>
    /// <param name="trustedConnection">Use a trusted connection.</param>
    /// <param name="args">The arguments passed to the sql script.</param>
    public void RunSqlScript(string fileName, string machineName, string databaseName, bool trustedConnection, string[] args)
    {
        // simple checks
        if (!Path.GetExtension(fileName).Equals(".sql", StringComparison.InvariantCulture))
            throw new Exception("The file doesn't end with .sql.");
    
        // check for used arguments
        foreach (var shortArg in new[] { "S", "d", "E", "i" })
        {
            var tmpArg = args.SingleOrDefault(a => a.StartsWith(string.Format("-{0}", shortArg), StringComparison.InvariantCulture));
            if (tmpArg != null)
                throw new ArgumentException(string.Format("Cannot pass -{0} argument to sqlcmd for a second time.", shortArg));
        }
    
        // check the params for trusted connection.
        var userArg = args.SingleOrDefault(a => a.StartsWith("-U", StringComparison.InvariantCulture));
        var passwordArg = args.SingleOrDefault(a => a.StartsWith("-P", StringComparison.InvariantCulture));
        if (trustedConnection)
        {
            if (userArg != null)
                throw new ArgumentException("Cannot pass -H argument when trustedConnection is used.");
            if (passwordArg != null)
                throw new ArgumentException("Cannot pass -P argument when trustedConnection is used.");
        }
        else
        {
            if (userArg == null)
                throw new ArgumentException("Exspecting username(-H) argument when trustedConnection is not used.");
            if (passwordArg == null)
                throw new ArgumentException("Exspecting password(-P) argument when trustedConnection is not used.");
        }
    
    
        // set the working directory. (can be needed with ouputfile)
        // TODO: Test if the above statement is correct
        var tmpDirectory = Directory.GetCurrentDirectory();
        var directory = Path.IsPathRooted(fileName) ? Path.GetDirectoryName(fileName) : Path.Combine(this.ProjectRoot, fileName);
        var file = Path.GetFileName(fileName);
        Directory.SetCurrentDirectory(directory);
    
        // create cmd line
        var cmd = string.Format(string.Format("SQLCMD -S {0} -d {1} -i \"{2}\"", machineName, databaseName, file));
        foreach (var argument in args.Where(a => a.StartsWith("-", StringComparison.InvariantCultureIgnoreCase)))
            cmd += " " + argument;
        if (trustedConnection)
            cmd += " -E";
    
        // create the process
        var process = new System.Diagnostics.Process();
        process.StartInfo.FileName = "cmd";
        process.StartInfo.CreateNoWindow = true;
        process.StartInfo.UseShellExecute = false;
        process.StartInfo.RedirectStandardOutput = true;
        process.StartInfo.RedirectStandardInput = true;
    
        // start the application
        process.Start();
        process.StandardInput.WriteLine("@ECHO OFF");
        process.StandardInput.WriteLine(string.Format("cd {0}", directory));
        process.StandardInput.WriteLine(cmd);
        process.StandardInput.WriteLine("EXIT");
        process.StandardInput.Flush();
        process.WaitForExit();
        
        // write the output to my debug folder and restore the current directory
        Debug.Write(process.StandardOutput.ReadToEnd());
        Directory.SetCurrentDirectory(tmpDirectory);
    }
    
    You have a deadlock when reading the console output after the WaitForExit call as described here:

    I have changed your code to suite my needs (Client is using OSQL, so I couldn't use -v option)
    class Program
        {
            static void Main(string[] args)
            {
                string fileName = @"CreateDatabase-params.sql";
                SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder();
                connStringBuilder.UserID = "sa";
                connStringBuilder.Password = "Test123";
                connStringBuilder.DataSource = @".\SQLEXPRESS";
                Dictionary<string, string> variables = new Dictionary<string, string>(4);
                variables.Add("AppLogin", "ADMTLogin");
                variables.Add("AppPassword", "Test123");
                variables.Add("DBName", "ADMT");
                variables.Add("DBPath", @"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA");
    
                using(
                    TextWriter standard = new StreamWriter("output.txt"),
                    error = new StreamWriter("errors.txt"))
                {
                    SqlHelper.StandardOutput = standard;
                    SqlHelper.ErrorOutput = error;
                    Thread th = new Thread(new ThreadStart(delegate 
                        {
                            SqlHelper.RunSqlScript(fileName,connStringBuilder,variables);
                        }));
                    Console.WriteLine("Creating Database");
                    th.Start();
                    while (th.IsAlive)
                    {
                        Console.Write(".");
                        Thread.Sleep(1000);
                    }
                    Console.WriteLine(" Done");
                    th.Join();
                    standard.Flush();
                    error.Flush();
                }
    
                Console.WriteLine("Database Created");
                Console.ReadKey();
    
            }
    
    class SqlHelper
        {
            public static TextWriter StandardOutput;
            public static TextWriter ErrorOutput;
    
            /// <summary>
            /// Parse Parameters then Run a .sql script trough osql.
            /// </summary>
            /// <param name="fileName">the .sql script</param>
            /// <param name="connStringBuilder">object that holds all connection information.</param>
            /// <param name="args">The arguments passed to the sql script.</param>
            public static void RunSqlScript(string fileName, SqlConnectionStringBuilder connStringBuilder, Dictionary<string,string> variables)
            {
                // simple checks
                if (!Path.GetExtension(fileName).Equals(".sql", StringComparison.InvariantCulture))
                    throw new Exception("The file doesn't end with .sql.");
    
                FileInfo file = ProcessArgs(fileName, variables);
    
                // create cmd line
                StringBuilder cmd = new StringBuilder(string.Format(
                    "OSQL -S \"{0}\" -i \"{1}\" -n", 
                    /*0*/connStringBuilder.DataSource, 
                    /*1*/file.ToString()));
                if (connStringBuilder.IntegratedSecurity)
                    cmd.Append(" -E");
                else
                    cmd.AppendFormat(" -U {0} -P {1}",
                        /*0*/connStringBuilder.UserID,
                        /*1*/connStringBuilder.Password);
    
                // create the process
                var process = new System.Diagnostics.Process();
                process.StartInfo.WorkingDirectory = Environment.CurrentDirectory;
                process.StartInfo.FileName = "cmd";
                process.StartInfo.CreateNoWindow = false;
                process.StartInfo.UseShellExecute = false;
                process.StartInfo.RedirectStandardOutput = true;
                process.StartInfo.RedirectStandardInput = true;
                process.StartInfo.RedirectStandardError = true;
                process.ErrorDataReceived += new DataReceivedEventHandler(process_ErrorDataReceived);
                process.OutputDataReceived += new DataReceivedEventHandler(process_OutputDataReceived);
    
                // start the application
                process.Start();
                process.BeginErrorReadLine();
                process.BeginOutputReadLine();
                process.StandardInput.WriteLine("@ECHO OFF");
                process.StandardInput.WriteLine(cmd.ToString());
                process.StandardInput.WriteLine("EXIT");
                process.StandardInput.Flush();
                process.WaitForExit();
                
                //delete temporary file
                file.Delete();
                
                //Reading output after waitforexit creates deadlock as available output buffer is fully filled
                //output.Write(process.StandardOutput.ReadToEnd());
                
            }
            private static void process_OutputDataReceived(object sender, DataReceivedEventArgs e)
            {
                if (e.Data != string.Empty)
                    if (StandardOutput == null)
                        Console.WriteLine(e.Data);
                    else
                        StandardOutput.WriteLine(e.Data);
            }
    
            private static void process_ErrorDataReceived(object sender, DataReceivedEventArgs e) {
                if (e.Data != string.Empty)
                    if (ErrorOutput == null)
                        Console.WriteLine(e.Data);
                    else
                        ErrorOutput.WriteLine(e.Data);
            }
    
            private static FileInfo ProcessArgs(string fileName, Dictionary<string, string> variables)
            {
                FileInfo f = new FileInfo(fileName);
                string SqlScript = f.OpenText().ReadToEnd();
                foreach (KeyValuePair<string,string> kvp in variables)
                {
                    SqlScript = SqlScript.Replace(String.Format("$({0})", kvp.Key), kvp.Value);
                }
    
                FileInfo newFile = new FileInfo("tmpSql.sql");
                using (var w = newFile.CreateText())
                {
                    w.Write(SqlScript);
                    w.Flush();
                }
                return newFile;
            }
        }

    Wednesday, January 20, 2010 10:47 AM
  • Just wanted to say thanks.  You suggestion saved me an enormous amount of trouble and time.  Can't believe it worked so easily!
    Thursday, April 01, 2010 9:25 AM
  • Hi,

    Here is a simple solution and it works!

    Adds 2 tables to test database

    Example SQL Script: BUILDECM.sql

    Works with Express 2005 and 2008

    ------------------------------------------START---------------------------------------------
    CREATE TABLE [tbl_archive_years](
     [No] [int] IDENTITY(1,1) NOT NULL,
     [Year] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
     [UserId] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
     [Domain] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
     CONSTRAINT [PK_tbl_archive_years] PRIMARY KEY CLUSTERED
    (
     [No] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
    )
    CREATE TABLE [tbl_enquiry_form](
     [No] [int] IDENTITY(1,1) NOT NULL,
     [ItemNo] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
     [ProductName] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
     [RefNumber] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
     [Date] [datetime] NULL,
     [UserId] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
     [Domain] [varchar](max) COLLATE Latin1_General_CI_AS NULL,
     CONSTRAINT [PK_tbl_enquiry_form] PRIMARY KEY CLUSTERED
    (
     [No] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
    )
    -------------------------------------------END--------------------------------------------

     

    protected void Button1_Click(object sender, EventArgs e)
    {
    //SqlConnection con = new SqlConnection(@"Server=.\SQLEXPRESS;Database=" + txtSqlName.Value + ";User ID=" + txtSqlLogin.Value + ";Password=" + txtSqlPassword.Text + ";Trusted_Connection=False");
    SqlConnection con = new SqlConnection(@"Server=.\SQLEXPRESS;Database=test;UserID=yourusername;Password=yourpassword;Trusted_Connection=False");
    con.Open();
    string FilePath;
    FilePath =
    @"C:\Users\Philip\Desktop\BUILDECM.sql";
    //FilePath = @"C:\Inetpub\vhosts\" + dsAdministration.Tables[0].Rows[0]["Domain"].ToString() + @"\httpdocs\users\" + Session["UserId"].ToString() + @"\" + Session["Domain"].ToString() + @"\css\" + lnkName.Text;
    if (File.Exists(FilePath))
    {
    StringBuilder strStatement = new StringBuilder(File.ReadAllText(FilePath).ToString());
    SqlCommand command = new SqlCommand(strStatement.ToString(), con);
    command.ExecuteNonQuery();
    con.Close();
    }
    }

    cheers!!!

    Thursday, April 22, 2010 1:40 PM
  • Hi Frnds U can use built in Installer Class to run ur Script

    U will find the Details along with the source code download in Below link

    http://www.codeguru.com/Csharp/.NET/net_security/encryption/article.php/c9601/

     

     

    Monday, May 10, 2010 7:32 AM
  • I was getting the same msvcr80.dll errors on some machines.

    It only occurred if the code was executed as part of a unit test.

    The same code ran fine in non-test assemblies.

    Installing the Visual Studio 2008 SP1 service pack resolved the errors.

    Thursday, May 20, 2010 4:58 PM
  • Hi Friend,

    i have used the same namespaces you have mentioned but now on production server it is not working. (SMO is installed on production server already)  .

    Did you get any solution ? Please help me.

    Thanks & Regards,

    MEgha

    Thursday, June 10, 2010 12:54 PM
  • Hello,

    At the line:

    server.ConnectionContext.ExecuteNonQuery(script);
    
    I'm getting this error:

     

     

    Test method IsItScienceFiction.Tests.UserTest.TestMethod1 threw exception:  System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->  System.TypeInitializationException: The type initializer for '<Module>' threw an exception. --->  <CrtImplementationDetails>.ModuleLoadException: The C++ module failed to load during appdomain initialization.
     --->  System.DllNotFoundException: Unable to load DLL 'MSVCR80.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E).

     

    with this stack trace:

     

    _encode_pointer(Void* )
    _initatexit_app_domain()
    LanguageSupport.InitializePerAppDomain(LanguageSupport* )
    LanguageSupport._Initialize(LanguageSupport* )
    LanguageSupport.Initialize(LanguageSupport* )
    ThrowModuleLoadException(String errorMessage, Exception innerException)
    ThrowModuleLoadException(String , Exception )
    LanguageSupport.Initialize(LanguageSupport* )
    cctor()
    Microsoft.SqlServer.Management.Common.ExecuteBatch.GetStatements(String sqlCommand)
    System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
    System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
    System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
    System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
    Microsoft.SqlServer.Management.Common.ServerConnection.GetStatements(String query, ExecutionTypes executionType, Int32& statementsToReverse)
    Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
    Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)
    IsItScienceFiction.Tests.UserTest.TestMethod1() in C:\Users\J. Pablo Fernández\Documents\Visual Studio 2008\Projects\IsItScienceFiction\IsItScienceFiction.Tests\UserTest.cs: line 75

     

    I am running Visual Studio 2008 with SQL Server 2008. What am I doing wrong?

    Hi Pablo,

     

    I experience the same issue. Did you ever find a solution?

     

    Yoann

    Monday, September 20, 2010 9:19 AM
  • SQLscript = SQLscript.Replace("GO", "");

     

    Warning, replacing systematically all GO in the text could break the Script, Imagine that you have to SELECT CATEGORY FROM WHATEVER

    John

    Friday, December 03, 2010 7:26 PM
  • Hi, one question: How do I find out the output generated by a command (e.g. a select command) in this example?
    Sunday, December 12, 2010 10:06 PM
  • Here to work with GO:

     To split the GO (in assumtion that GO syntax in a line):

    List<string> cmds = new List<string>();
    if (File.Exists(qfile))
    {
      TextReader tr = new StreamReader(qfile);
      string line = "";
      string cmd = "";
      while ((line = tr.ReadLine()) != null)
      {
        if (line.Trim().ToUpper() == "GO")
        {
          cmds.Add(cmd);
          cmd = "";
        }
        else
        {
          cmd += line + "\r\n";
        }
      }
      if (cmd.Length > 0)
      {
        cmds.Add(cmd);
        cmd = "";
      }
      tr.Close();
    }

    Then execute them in a connection to SQL:

    if (cmds.Count > 0)
    {
      SqlCommand command = new SqlCommand();
      command.Connection = new SqlConnection(cs);
      command.CommandType = System.Data.CommandType.Text;
      command.Connection.Open();
      for (int i = 0; i < cmds.Count; i++)
      {
        command.CommandText = cmds[i];
        command.ExecuteNonQuery(); 
      }
      
      
    }
    Saturday, December 18, 2010 11:20 PM
  • Great works perfectly :)
    Tuesday, March 29, 2011 7:55 AM
  • Hello Bex,

    It is very simple, you can start from C# any SP.

    you have to instance a command for your database provider

    YourProviderCommand cmd = new YourProviderCommand("file.SQL");
    to add the connectionstring to that one
    cmd.ConnectionString = "myConnectionsTring";
    to tell that it is an SP
    cmd.CommandType = CommandType.StoredProcedure;

    And run the command with

    cmd.ExecuteNonQuery();

    That is all, quite simple


    Success
    Cor
    Monday, May 09, 2011 8:10 AM
  • hi boban,

    thanks for the simple solution. it works perfectly when my machine and DB server are in the same domain. my problem now is, i'm executing this console application from a machine (name: X, user id: abc, domain D1). the SQL server is in another machine (name: Y, domain: D2). I use VPN to connect to machine Y with user id 'abc' and work on SQL in remote desktop. how can i run sql scripts across domains? I use this in a custom activity in my TFS build template as the last step to run the scripts in the target database which is in domain D2.

     

    i'm getting this error: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

     

    is it possible to connect to the DB using the admin user ID of machine Y in domain D2?

    Sunday, November 20, 2011 1:26 AM
  • Hi,

    To add the below assembly/interface

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo;

     

    which COM object do i need to reference.

    I am using VS 2010 Express.

     

     


    sami
    Wednesday, December 21, 2011 10:47 AM
  • Thanks for Post above code....I want to ask that can i apply same code to run .sqlce file in .Net C#..i am using SqlCe 3.5 and Visual Studio 2008..plz reply..thanks in advance..

    Friday, May 18, 2012 5:13 AM
  • Monday, June 18, 2012 11:01 AM
  • You can't run files directly, with just specifing file name. You must first read it's content in a string and then execute CommandText command.
    But this will work as long you don't have GO statement in your sql script. From your post it looks like that SqlCommand is not usefull because scripts that include DDL command are completed with batch finalizer GO command. Luckily, since SQL Server 2005, you can use smo library to do this operation and not use batch command and osql connection as previously. Here is how to do it:

    using System.Data.SqlClient;

    using System.IO;

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo;

    namespace ConsoleApplication1

    {

        class Program

        {

            static void Main(string[] args)

            {

                string sqlConnectionString = "Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True";

                FileInfo file = new FileInfo("C:\\myscript.sql");

                string script = file.OpenText().ReadToEnd();

                SqlConnection conn = new SqlConnection(sqlConnectionString);

                Server server = new Server(new ServerConnection(conn));

                server.ConnectionContext.ExecuteNonQuery(script);

            }

        }

    }

    Very useful code...

    • Proposed as answer by Vignesh Raja M Friday, November 08, 2013 10:11 AM
    Monday, August 27, 2012 8:59 AM
  • Where do I find the references to add?  I am working in Visual Studio 2012 and cannot find the references for

    using Microsoft.SqlServer.Management.Common;

    using Microsoft.SqlServer.Management.Smo;

    via the Framework or the Extensions of the Add References dialog.  Without them it has a build problem with:

                Server server = new Server( etc.  ....


    WFB

    Tuesday, February 04, 2014 8:02 PM