Execute a large SQL script (with GO commands)
Use SQL Server Management Objects (SMO) which understands GO separators. See my blog post here: http://weblogs.asp.net/jongalloway/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-2D00-the-easy-way
Sample code:
public static void Main()
{
string scriptDirectory = "c:\\temp\\sqltest\\";
string sqlConnectionString = "Integrated Security=SSPI;" +
"Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";
DirectoryInfo di = new DirectoryInfo(scriptDirectory);
FileInfo[] rgFiles = di.GetFiles("*.sql");
foreach (FileInfo fi in rgFiles)
{
FileInfo fileInfo = new FileInfo(fi.FullName);
string script = fileInfo.OpenText().ReadToEnd();
using (SqlConnection connection = new SqlConnection(sqlConnectionString))
{
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(script);
}
}
}
If that won't work for you, see Phil Haack's library which handles that: http://haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators-and.aspx
How do I execute a large SQL Script with many GO statements from within Delphi?
Loading the script into a TStringList
would work. looking for the word GO
on a line by itself (which avoids embedded text containing GO
) as the end of each block or the script itself.
You use the GO
line's index - 1 to mark the end of the prior block, and the GO
line index + 1 as the start of the next block. If the GO
index is equal to the StringList.Count - 1, you've reached then end of the script. You can start a transaction before processing the blocks, and roll that transaction back if an exception is raised.
Executing SQL batch containing GO statements in C#
ScriptDom
The easiest solution (and the most robust) is to use a T-SQL parser. The good news is that you don't have to write it, just add reference to:
Microsoft.Data.Schema.ScriptDom
Microsoft.Data.Schema.ScriptDom.Sql
Then use the code:
static void Main(string[] args)
{
string sql = @"
/*
GO
*/
SELECT * FROM [table]
GO
SELECT * FROM [table]
SELECT * FROM [table]
GO
SELECT * FROM [table]";
string[] errors;
var scriptFragment = Parse(sql, SqlVersion.Sql100, true, out errors);
if (errors != null)
{
foreach (string error in errors)
{
Console.WriteLine(error);
return;
}
}
TSqlScript tsqlScriptFragment = scriptFragment as TSqlScript;
if (tsqlScriptFragment == null)
return;
var options = new SqlScriptGeneratorOptions { SqlVersion = SqlVersion.Sql100, KeywordCasing = KeywordCasing.PascalCase };
foreach (TSqlBatch batch in tsqlScriptFragment.Batches)
{
Console.WriteLine("--");
string batchText = ToScript(batch, options);
Console.WriteLine(batchText);
}
}
public static TSqlParser GetParser(SqlVersion level, bool quotedIdentifiers)
{
switch (level)
{
case SqlVersion.Sql80:
return new TSql80Parser(quotedIdentifiers);
case SqlVersion.Sql90:
return new TSql90Parser(quotedIdentifiers);
case SqlVersion.Sql100:
return new TSql100Parser(quotedIdentifiers);
case SqlVersion.SqlAzure:
return new TSqlAzureParser(quotedIdentifiers);
default:
throw new ArgumentOutOfRangeException("level");
}
}
public static IScriptFragment Parse(string sql, SqlVersion level, bool quotedIndentifiers, out string[] errors)
{
errors = null;
if (string.IsNullOrWhiteSpace(sql)) return null;
sql = sql.Trim();
IScriptFragment scriptFragment;
IList<ParseError> errorlist;
using (var sr = new StringReader(sql))
{
scriptFragment = GetParser(level, quotedIndentifiers).Parse(sr, out errorlist);
}
if (errorlist != null && errorlist.Count > 0)
{
errors = errorlist.Select(e => string.Format("Column {0}, Identifier {1}, Line {2}, Offset {3}",
e.Column, e.Identifier, e.Line, e.Offset) +
Environment.NewLine + e.Message).ToArray();
return null;
}
return scriptFragment;
}
public static SqlScriptGenerator GetScripter(SqlScriptGeneratorOptions options)
{
if (options == null) return null;
SqlScriptGenerator generator;
switch (options.SqlVersion)
{
case SqlVersion.Sql80:
generator = new Sql80ScriptGenerator(options);
break;
case SqlVersion.Sql90:
generator = new Sql90ScriptGenerator(options);
break;
case SqlVersion.Sql100:
generator = new Sql100ScriptGenerator(options);
break;
case SqlVersion.SqlAzure:
generator = new SqlAzureScriptGenerator(options);
break;
default:
throw new ArgumentOutOfRangeException();
}
return generator;
}
public static string ToScript(IScriptFragment scriptFragment, SqlScriptGeneratorOptions options)
{
var scripter = GetScripter(options);
if (scripter == null) return string.Empty;
string script;
scripter.GenerateScript(scriptFragment, out script);
return script;
}
SQL Server Management Objects
Add references to:
Microsoft.SqlServer.Smo
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
You can then use this code:
using (SqlConnection connection = new SqlConnection("Server=(local);Database=Sample;Trusted_Connection=True;"))
{
ServerConnection svrConnection = new ServerConnection(connection);
Server server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(script);
}
CodeFluent Runtime
CodeFluent Runtime Database has a small sql file parser. It does not handle complex cases but for instance comments are supported.
using (StatementReader statementReader = new CodeFluent.Runtime.Database.Management.StatementReader("GO", Environment.NewLine, inputStream))
{
Statement statement;
while ((statement = statementReader.Read(StatementReaderOptions.Default)) != null)
{
Console.WriteLine("-- ");
Console.WriteLine(statement.Command);
}
}
Or much simplier
new CodeFluent.Runtime.Database.Management.SqlServer.Database("connection string")
.RunScript("path", StatementReaderOptions.Default);
How to execute SQL with comments and GO statements using SqlConnection?
You need to use the SQL management classes instead of the normal SqlCommand. This page shows you how to do it. If you try to parse the SQL yourself then there will always be edge cases that you miss. For example, what if a string within the code contains the word "GO" with leading and trailing carriage returns?
Add these references:
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Management.Sdk.Sfc (Edit: This reference isn't needed)
Then you can use this code:
string connectionString, scriptText;
SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(scriptText);
Dapper. Execute Query with GOs
Dapper cannot help you here. Instead, I would suggest you try SQL Server Management Objects to execute large SQL commands like the one you mentioned. And it does supports GO statements.
You can check out this blog post for step-by-step instructions.
Run SQL script via command line to remote server
-d - Specifies the database name - docs.microsoft.com/en-us/sql/tools/sqlcmd-utility – Ctznkane525
sqlcmd -S myServer\instanceName -i C:\myScript.sql -d DatabaseName
Related Topics
Conditionally Change CSS Class in Razor View
My Algorithm to Calculate Position of Smartphone - Gps and Sensors
Make an Installation Program For C# Applications and Include .Net Framework Installer into the Setup
How to Clone a Range of Array Elements to a New Array
Cross-Platform File Name Handling in .Net Core
Cannot Run ASP.NET 5 from Docker
How to Have an Auto Incrementing Version Number (Visual Studio)
How to Build Splash Screen in Windows Forms Application
Translucent Circular Control With Text
Why Do We Need Boxing and Unboxing in C#
Reliable Method to Get Machine'S MAC Address in C#
How to Convert a Datatable into a Generic List
Why Does C# Forbid Generic Attribute Types
How to Migrate a .Net Windows Service Application to Linux Using Mono
What Is the Best Choice For .Net Inter-Process Communication