I need to execute a large set of SQL statements (creating a bunch of tables, views and stored procedures) from within a C# program.
These statements need to be separated by GO
statements, but SqlCommand.ExecuteNonQuery()
does not like GO
statements. My solution, which I suppose I'll post for reference, was to split the SQL string on GO
lines, and execute each batch separately.
Is there an easier/better way?
回答
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
回答1
protected void Page_Load(object sender, EventArgs e) { string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ccwebgrity;Data Source=SURAJIT\SQLEXPRESS"; string script = File.ReadAllText(@"E:\Project Docs\MX462-PD\MX756_ModMappings1.sql"); SqlConnection conn = new SqlConnection(sqlConnectionString); Server server = new Server(new ServerConnection(conn)); server.ConnectionContext.ExecuteNonQuery(script); }
回答2
I tried this solution with Microsoft.SqlServer.Management but it didn't work well with .NET 4.0 so I wrote another solution using .NET libs framework only.
string script = File.ReadAllText(@"E:\someSqlScript.sql"); // split script on GO command IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase); Connection.Open(); foreach (string commandString in commandStrings) { if (!string.IsNullOrWhiteSpace(commandString.Trim())) { using(var command = new SqlCommand(commandString, Connection)) { command.ExecuteNonQuery(); } } } Connection.Close();
需要注意的是using SqlConnection = Microsoft.Data.SqlClient.SqlConnection; 命令空间不一样
private void ExecuteSqlFile(string connectionString, string content) { using (Microsoft.Data.SqlClient.SqlConnection connection = new Microsoft.Data.SqlClient.SqlConnection(connectionString)) { Server server = new Server(new ServerConnection(connection)); server.ConnectionContext.ExecuteNonQuery(content); } }