我如何运行包含的关键字,包括&QUOT大型SQL脚本; GO"使用C#? [英] How do I run large SQL scripts that contain many keywords, including "GO" using C#?
问题描述
我创建作为前端做SQL复制一个Web应用程序。
I'm creating a web application that serves as a front end to do SQL Replication.
我有存储在所述程序的属性许多脚本。让我们用第一个,例如,第一个脚本是从发布服务器上创建发布拿到剧本。
I have many scripts stored in the properties of the program. Let's use the first one as an example. The first script is the script that you get from creating a publication on the publisher server.
USE [<<SOURCE_DATABASE_NAME>>]
EXEC sp_replicationdboption @dbname = N'<<SOURCE_DATABASE_NAME>>',
@optname = N'publish', @value = N'true'
GO
USE [<<SOURCE_DATABASE_NAME>>]
EXEC [<<SOURCE_DATABASE_NAME>>].sys.sp_addlogreader_agent @job_login = N'XXX\Admin',
@job_password = NULL, @publisher_security_mode = 0,
@publisher_login = N'Admin', @publisher_password = N'<<PASSWORD>>',
@job_name = NULL
GO
USE [<<SOURCE_DATABASE_NAME>>]
EXEC sp_addpublication @publication = N'<<SOURCE_DATABASE_NAME>>',
@description = N'Transactional publication of database ''<<SOURCE_DATABASE_NAME>>'' from Publisher ''<<SOURCE_SERVER_NAME>>''.',
@sync_method = N'concurrent', @retention = 0, @allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'false',
@enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21,
@allow_subscription_copy = N'false', @add_to_active_directory = N'false',
@repl_freq = N'continuous', @status = N'active',
@independent_agent = N'true', @immediate_sync = N'false',
@allow_sync_tran = N'false', @allow_queued_tran = N'false',
@allow_dts = N'false', @replicate_ddl = 1,
@allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false'
GO
EXEC sp_addpublication_snapshot @publication = N'<<SOURCE_DATABASE_NAME>>',
@frequency_type = 1, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday = 8, @frequency_subday_interval = 1,
@active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@active_start_date = 0, @active_end_date = 0,
@job_login = N'ICS\Admin', @job_password = NULL,
@publisher_security_mode = 0, @publisher_login = N'Admin',
@publisher_password = N'<<PASSWORD>>'
而不是运行在SQL Management Studio中这个剧本,我想用我的web应用程序来运行它。
Instead of running this script in SQL Management Studio, I want to use my web application to run it.
我试过:
public static void CreatePublication(string server, string query)
{
string finalConnString = Properties.Settings.Default.rawConnectionString.Replace("<<DATA_SOURCE>>", server).Replace("<<INITIAL_CATALOG>>", "tempdb");
using (SqlConnection conn = new SqlConnection(finalConnString))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
public static string ConstructCreatePublicationScript(string rawPublicationScript, string rawAddArticleScript,
string password, string sourceServerName, string sourceDatabaseName, List<string> selectedTables)
{
string createPublicationScript = "";
string addArticleScript = "";
createPublicationScript = rawPublicationScript.Replace("<<PASSWORD>>", password)
.Replace("<<SOURCE_SERVER_NAME>>", sourceServerName)
.Replace("<<SOURCE_DATABASE_NAME>>", sourceDatabaseName);
createPublicationScript = createPublicationScript + "\n\n";
foreach (string selectedTable in selectedTables)
{
addArticleScript = rawAddArticleScript.Replace("<<SOURCE_DATABASE_NAME>>", sourceDatabaseName)
.Replace("<<SOURCE_TABLE_NAME>>", selectedTable);
createPublicationScript = createPublicationScript + addArticleScript + "\n\n";
}
//write script to file
return createPublicationScript;
}
但遇到了这个错误:
But ran into this error:
的SQLException被抓获
SqlException was caught
附近有语法错误'GO'。结果
不正确的语法附近'GO'。结果
不正确的语法附近'GO'。结果
不正确的语法附近'GO'。结果
不正确的语法附近'GO'。结果
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
我的问题是,我怎么能在C#中运行这上面整个脚本?如果我只是摆脱GO的关键字?
My question is, how can I run this whole script above in C#? Should I just get rid of the "GO" keywords?
推荐答案
更改ConstructCreatePublicationScript返回列表&LT;串&GT;
其中每个字符串元素是一块你完整的脚本在GO语句分裂。这是必要的,因为GO是Management Studio中使用的分隔符,而不是一个SQL语句
Change your ConstructCreatePublicationScript to return a List<string>
where each string element is a piece of your complete script splitted at the GO statement. This is necessary because the GO is the separator used by Management Studio and not a SQL statement
public static List<string> ConstructCreatePublicationScript(string rawPublicationScript, string rawAddArticleScript)
{
.....
List<string> result = new List<string>();
result.AddRange(Regex.Split(createPublicationScript, "^GO$", RegexOptions.Multiline));
return result;
}
然后更改您的执行code接收清单和执行每个单个
then change your execution code to receive the list and execute each single string
public static void CreatePublication(string server, List<string> queries)
{
string finalConnString = Properties.Settings.Default.rawConnectionString.Replace("<<DATA_SOURCE>>", server).Replace("<<INITIAL_CATALOG>>", "tempdb");
using (SqlConnection conn = new SqlConnection(finalConnString))
{
conn.Open();
foreach(string query in queries)
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.ExecuteNonQuery();
}
}
}
}
这篇关于我如何运行包含的关键字,包括&QUOT大型SQL脚本; GO&QUOT;使用C#?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!