Execute sql file on SQL Server using C#

Question

I have many files for procedures, views, functions, etc.

I want to execute these files (creating components) in appropriate database on SQL Server 2005/2008.

Also the point is I want to execute them using C#.

Another point to mention, I want the application to be such that I can execute this files on a remote SQL Server too. Also client machine may not have osql,sqlcmd command tool.

Can someone please guide me on this.

Solution 1

This depends on what sort of files they are. If, for example, they only contain actual T-SQL commands (and aren't batch files that you'd run in, say, SSMS, which would contain a batch separator like GO), then you just need to create a connection, a command, then read the contents of the file and use that to populate the CommandText property of the command.

For example:

void ExecuteFile(string connectionString, string fileName)
{
    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        string data = System.IO.File.ReadAllText(fileName);

        conn.Open();

        using(SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = data;
            cmd.ExecuteNonQuery();
        }
    }
}

If it's a batch file, you'll need to split the file into individual batches and process those individually. The simplest method is just to use string.Split, but bear in mind that it won't respect SQL parsing rules when it splits (for example, if GO appears within a SQL statement, it's going to split the command up into two batches, which will obviously fail).

More generally, you can see what you'd need to do here by modifying the code in this way:

string[] batches = SplitBatches(System.IO.File.ReadAllText(fileName));

conn.Open();

using(SqlCommand cmd = conn.CreateCommand())
{
    foreach(string batch in batches)
    {
        cmd.CommandText = batch;
        cmd.ExecuteNonQuery();
    }
}

The implementation of a function called SplitBatches is up to you.

Solution 2

Typically, the simplest way is to split the script on the "GO" statement and execute each item separately. This solution will not work if the script contains a GO statement within a comment.

private readonly Regex _sqlScriptSplitRegEx = new Regex( @"^\s*GO\s*$", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Compiled );
public void ExecuteSqlScript( string scriptText )
{
    if ( string.IsNullOrEmpty( scriptText ) )
        return;

    var scripts = _sqlScriptSplitRegEx.Split( scriptText );
    using ( var conn = new SqlConnection( "connection string" ) )
    {
        using ( var ts = new TransactionScope( TransactionScopeOption.Required, new TimeSpan( 0, 10, 0 ) ) )
        {
            foreach ( var scriptLet in scripts )
            {
                if ( scriptLet.Trim().Length == 0 )
                    continue;

                using ( var cmd = new SqlCommand( scriptLet, conn ) )
                {
                    cmd.CommandTimeout = this.CommandTimeout;
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
            }

            ts.Complete();
        }
    }
}

Solution 3

Normally all you have to do is just execute them with SqlCommand.ExecuteNonQuery, one batch at a time. That leaves you the task of splitting the scripts into batches, using the currently set batch delimiter (usually GO). The free library dbutilsqlcmd can be used to handle SQL scripts, as it processes not only the delimiter, but SQLCMD extensions as well (:setvar, :connect etc).

Comments

[Sunil Agarwal]

Thanks for the answer. But I still have some doubt. In .sql files I am having the code like this >if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_CheckID]') and > OBJECTPROPERTY(id, N'IsProcedure') = 1) >drop procedure [dbo].[usp_CheckID] >GO //Code to create procedure

[Sunil Agarwal]

Hence while executing the file I am getting error Incorrect syntax near 'GO'. 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Incorrect syntax near the keyword 'else'. Incorrect syntax near 'GO'.

[Adam Robinson]

@Sunil: The you have a batch file; see the second part of my answer on how to handle that. The GO command is not a SQL command, it's just something that is a standard string used to separate SQL batches in things like SSMS or Visual Studio. You have to split the batches up yourself if you want to process them in this way.