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

Answer 1

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);

        }

    }

}

Answer 2

Thanks for the info!
I will try that!

Answer 3

 

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

Answer 4

Start a transaction, read and execute line by line, commit the transaction or rollback.

Answer 5

 

Can u help me with that how can i achieve that. Thank You

Answer 6

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)..

Answer 7

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.

 

Answer 8

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?

Answer 9

use System.Diagnostics to start sqlcmd

http://msdn2.microsoft.com/en-us/library/ms165702.aspx

http://msdn2.microsoft.com/en-us/library/system.diagnostics.process.aspx

Answer 10

 

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

 

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

Answer 11

If I want to pass variables to the sql file,how to do that ?

 

Answer 12

I use http://www.databasejournal.com/features/mssql/article.php/3566401

and

use System.Diagnostics to start sqlcmd

http://msdn2.microsoft.com/en-us/library/ms165702.aspx

http://msdn2.microsoft.com/en-us/library/system.diagnostics.process.aspx

Answer 13

 

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);

}

Answer 14

it works~

thanks~

Answer 15

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.

Answer 16

 

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

Answer 17

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

Answer 18

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/

Answer 19

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

Answer 20

Put the batch terminator GO after each procedure, and the script should work from code.

---

MCDBA, MCSD, MCITP DD&DA http://sharpsource.blogspot.com/

Answer 21

thank for your quick reply, I tried it before but still the same problem

Answer 22

My problem was solved thank you so much, the problem was in the code because I remove new lines from the procedures

Answer 23

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();

 

Answer 24

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

Answer 25

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

Answer 26

Hello Quim,

Did you ever tried  sp_executesql ?

-Waleed A.K

Answer 27

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é

Answer 28

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?

Answer 29

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();
          
        }

Answer 30

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..

 

Answer 31

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 ?

Answer 32

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/

Answer 33

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);
}

Answer 34

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  

Answer 35

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:

http://msdn.microsoft.com/en-us/library/system.diagnostics.process.standardoutput.aspx

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;
        }
    }

Answer 36

Just wanted to say thanks.  You suggestion saved me an enormous amount of trouble and time.  Can't believe it worked so easily!

Answer 37

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!!!

Answer 38

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/

 

 

Answer 39

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.

Answer 40

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

Answer 41

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

Answer 42

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

Answer 43

Hi, one question: How do I find out the output generated by a command (e.g. a select command) in this example?

Answer 44

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(); 
  }
  
  
}

Answer 45

Great works perfectly :)

Answer 46

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

Answer 47

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?

Answer 48

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

Answer 49

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..

Answer 50

Try this article: SQL Connection in C# using SqlConnection

Answer 51

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...