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.
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.
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();
}
}
}
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).
Using standard ADO would "work" -- SQL DDL can be sent using SqlCommand.ExecuteNonQuery, for instance.
...but I'd recommend using a Database Project (with a remote staging deploy or VSDBCMD) or one of the tools such as SQLCMD (perhaps in conjunction with a PowerShell script :-) or SQL Management Studio, etc. They are designed for this sort of stuff.
You could read in the commands using a TextReader, then use ExecuteNonQuery with the TextReader results as the command.
