Execute a large SQL script (with GO commands)
我需要从C#程序中执行一大套SQL语句(创建一堆表,视图和存储过程)。
这些语句需要用GO语句分隔,但是SqlCommand.ExecuteNonQuery()不喜欢GO语句。 我想提供参考的解决方案是在GO行上拆分SQL字符串,并分别执行每个批处理。
有没有更简单/更好的方法?
使用了解GO分隔符的SQL Server管理对象(SMO)。在这里查看我的博客文章:http://weblogs.asp.net/jongalloway/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-2D00-the-easy-way
样例代码:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
| 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);
}
}
} |
如果那对您不起作用,请参阅Phil Haack的库来处理该问题:http://haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators -and.aspx
这就是我共同解决当前问题的方法。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| private void ExecuteBatchNonQuery(string sql, SqlConnection conn) {
string sqlBatch = string.Empty;
SqlCommand cmd = new SqlCommand(string.Empty, conn);
conn.Open();
sql +="
GO"; // make sure last batch is executed.
try {
foreach (string line in sql.Split(new string[2] {"
","
" }, StringSplitOptions.RemoveEmptyEntries)) {
if (line.ToUpperInvariant().Trim() =="GO") {
cmd.CommandText = sqlBatch;
cmd.ExecuteNonQuery();
sqlBatch = string.Empty;
} else {
sqlBatch += line +"
";
}
}
} finally {
conn.Close();
}
} |
它要求GO命令位于其自己的行上,并且不会检测到块注释,因此这种事情将被拆分,并导致错误:
1 2 3 4
| ExecuteBatchNonQuery(@"
/*
GO
*/", conn); |
您可以使用SQL管理对象来执行此操作。这些是Management Studio用于执行查询的对象。我相信Server.ConnectionContext.ExecuteNonQuery()将执行您需要的操作。
SQL Management Studio本身实际上使用了" GO"批处理分隔符关键字,因此它知道在何处终止要发送给服务器的批处理,并且不会传递给SQL Server。如果需要,您甚至可以在Management Studio中更改关键字。
基于Blorgbeard的解决方案。
1 2 3 4 5
| foreach (var sqlBatch in commandText.Split(new[] {"GO" }, StringSplitOptions.RemoveEmptyEntries))
{
sqlCommand.CommandText = sqlBatch;
sqlCommand.ExecuteNonQuery();
} |
我最后几次看这是由EF实施决定的
对SqlConnection进行了一些修改
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
| public static void ExecuteSqlScript(this SqlConnection sqlConnection, string sqlBatch)
{
// Handle backslash utility statement (see http://technet.microsoft.com/en-us/library/dd207007.aspx)
sqlBatch = Regex.Replace(sqlBatch, @"\\(
|
|
)", string.Empty);
// Handle batch splitting utility statement (see http://technet.microsoft.com/en-us/library/ms188037.aspx)
var batches = Regex.Split(
sqlBatch,
string.Format(CultureInfo.InvariantCulture, @"^\s*({0}[ \t]+[0-9]+|{0})(?:\s+|$)", BatchTerminator),
RegexOptions.IgnoreCase | RegexOptions.Multiline);
for (int i = 0; i < batches.Length; ++i)
{
// Skip batches that merely contain the batch terminator
if (batches[i].StartsWith(BatchTerminator, StringComparison.OrdinalIgnoreCase) ||
(i == batches.Length - 1 && string.IsNullOrWhiteSpace(batches[i])))
{
continue;
}
// Include batch terminator if the next element is a batch terminator
if (batches.Length > i + 1 &&
batches[i + 1].StartsWith(BatchTerminator, StringComparison.OrdinalIgnoreCase))
{
int repeatCount = 1;
// Handle count parameter on the batch splitting utility statement
if (!string.Equals(batches[i + 1], BatchTerminator, StringComparison.OrdinalIgnoreCase))
{
repeatCount = int.Parse(Regex.Match(batches[i + 1], @"([0-9]+)").Value, CultureInfo.InvariantCulture);
}
for (int j = 0; j < repeatCount; ++j)
{
var command = sqlConnection.CreateCommand();
command.CommandText = batches[i];
command.ExecuteNonQuery();
}
}
else
{
var command = sqlConnection.CreateCommand();
command.CommandText = batches[i];
command.ExecuteNonQuery();
}
}
} |
例如,如果您不想使用SMO,因为您需要跨平台,则还可以使用SubText中的ScriptSplitter类。
这是C#和VB.NET中的实现
用法:
1 2 3 4 5 6 7 8 9 10
| string strSQL = @"
SELECT * FROM INFORMATION_SCHEMA.columns
GO
SELECT * FROM INFORMATION_SCHEMA.views
";
foreach(string Script in new Subtext.Scripting.ScriptSplitter(strSQL ))
{
Console.WriteLine(Script);
} |
如果您对多行c样式注释有疑问,请使用正则表达式删除注释:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
| static string RemoveCstyleComments(string strInput)
{
string strPattern = @"/[*][\w\d\s]+[*]/";
//strPattern = @"/\*.*?\*/"; // Doesn't work
//strPattern ="/\\*.*?\\*/"; // Doesn't work
//strPattern = @"/\*([^*]|[
]|(\*+([^*/]|[
])))*\*+/"; // Doesn't work
//strPattern = @"/\*([^*]|[
]|(\*+([^*/]|[
])))*\*+/"; // Doesn't work
// http://stackoverflow.com/questions/462843/improving-fixing-a-regex-for-c-style-block-comments
strPattern = @"/\*(?>(?:(?>[^*]+)|\*(?!/))*)\*/"; // Works !
string strOutput = System.Text.RegularExpressions.Regex.Replace(strInput, strPattern, string.Empty, System.Text.RegularExpressions.RegexOptions.Multiline);
Console.WriteLine(strOutput);
return strOutput;
} // End Function RemoveCstyleComments |
在此处删除单行注释:
1
| https://stackoverflow.com/questions/9842991/regex-to-remove-single-line-sql-comments |
如果您不想安装SMO对象,则可以使用gplex工具(请参阅此答案)
如果您不想走SMO路线,可以搜索并将" GO"替换为";"。和查询一样。请注意,最后将返回最后一个结果集。
我也面临同样的问题,除了将单个SQL操作拆分为单独的文件,然后依次执行所有其他操作外,我找不到其他任何方法。
显然问题不在于DML命令列表,它们之间可以在没有GO的情况下执行;与DDL不同的故事(创建,更改,删除...)
今天,我通过将SQL从文本文件加载到一个字符串中来完成此任务。然后,我使用字符串分割功能将字符串分成单独的命令,然后分别发送到服务器。简单:)
刚意识到您需要在 nGO上拆分,以防万一字母GO出现在您的任何表名中,等等。
如果您不想使用SMO(比下面的解决方案更好,但我想提供一个替代方案...),则可以使用此函数拆分查询。
它是:
-
注释证明(例如--GO或/ * GO * /)
-
只能在新行中使用,就像在SSMS中一样(示例/ *测试/ * GO有效,而选择1则不行
-
字符串证明(示例打印" no go")
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
| private List<string> SplitScriptGo(string script)
{
var result = new List<string>();
int pos1 = 0;
int pos2 = 0;
bool whiteSpace = true;
bool emptyLine = true;
bool inStr = false;
bool inComment1 = false;
bool inComment2 = false;
while (true)
{
while (pos2 < script.Length && Char.IsWhiteSpace(script[pos2]))
{
if (script[pos2] == '
' || script[pos2] == '
')
{
emptyLine = true;
inComment1 = false;
}
pos2++;
}
if (pos2 == script.Length)
break;
bool min2 = (pos2 + 1) < script.Length;
bool min3 = (pos2 + 2) < script.Length;
if (!inStr && !inComment2 && min2 && script.Substring(pos2, 2) =="--")
inComment1 = true;
if (!inStr && !inComment1 && min2 && script.Substring(pos2, 2) =="/*")
inComment2 = true;
if (!inComment1 && !inComment2 && script[pos2] == '\'')
inStr = !inStr;
if (!inStr && !inComment1 && !inComment2 && emptyLine
&& (min2 && script.Substring(pos2, 2).ToLower() =="go")
&& (!min3 || char.IsWhiteSpace(script[pos2 + 2]) || script.Substring(pos2 + 2, 2) =="--" || script.Substring(pos2 + 2, 2) =="/*"))
{
if (!whiteSpace)
result.Add(script.Substring(pos1, pos2 - pos1));
whiteSpace = true;
emptyLine = false;
pos2 += 2;
pos1 = pos2;
}
else
{
pos2++;
whiteSpace = false;
if (!inComment2)
emptyLine = false;
}
if (!inStr && inComment2 && pos2 > 1 && script.Substring(pos2 - 2, 2) =="*/")
inComment2 = false;
}
if (!whiteSpace)
result.Add(script.Substring(pos1));
return result;
} |
使用以下方法拆分字符串并逐批执行
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
| using System;
using System.IO;
using System.Text.RegularExpressions;
namespace RegExTrial
{
class Program
{
static void Main(string[] args)
{
string sql = String.Empty;
string path=@"D:\temp\sample.sql";
using (StreamReader reader = new StreamReader(path)) {
sql = reader.ReadToEnd();
}
//Select any GO (ignore case) that starts with at least
//one white space such as tab, space,new line, verticle tab etc
string pattern="[\\s](?i)GO(?-i)";
Regex matcher = new Regex(pattern, RegexOptions.Compiled);
int start = 0;
int end = 0;
Match batch=matcher.Match(sql);
while (batch.Success) {
end = batch.Index;
string batchQuery = sql.Substring(start, end - start).Trim();
//execute the batch
ExecuteBatch(batchQuery);
start = end + batch.Length;
batch = matcher.Match(sql,start);
}
}
private static void ExecuteBatch(string command)
{
//execute your query here
}
}
} |
我在Java中遇到了相同的问题,并通过一些逻辑和正则表达式解决了它。我相信可以应用相同的逻辑。首先,我从slq文件读取到内存中。然后,我应用以下逻辑。几乎已经说过了,但是我相信使用正则表达式单词绑定比期待换行符更安全。
1 2 3 4 5 6
| String pattern ="\\bGO\\b|\\bgo\\b";
String[] splitedSql = sql.split(pattern);
for (String chunk : splitedSql) {
getJdbcTemplate().update(chunk);
} |
这基本上将sql字符串拆分为sql字符串数组。正则表达式基本上是检测小写或大写的完整"开始"单词。然后,您将依次执行不同的查询。
我遇到了同样的问题,最终只是通过简单的字符串替换将其解决,用分号(;)替换了GO字样
在执行带有内联注释,块注释和GO命令的脚本时,一切似乎都工作正常
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
| public static bool ExecuteExternalScript(string filePath)
{
using (StreamReader file = new StreamReader(filePath))
using (SqlConnection conn = new SqlConnection(dbConnStr))
{
StringBuilder sql = new StringBuilder();
string line;
while ((line = file.ReadLine()) != null)
{
// replace GO with semi-colon
if (line =="GO")
sql.Append(";");
// remove inline comments
else if (line.IndexOf("--") > -1)
sql.AppendFormat(" {0}", line.Split(new string[] {"--" }, StringSplitOptions.None)[0]);
// just the line as it is
else
sql.AppendFormat(" {0}", line);
}
conn.Open();
SqlCommand cmd = new SqlCommand(sql.ToString(), conn);
cmd.ExecuteNonQuery();
}
return true;
} |
为了避免第三方,正则表达式,内存开销以及大型脚本的快速工作,我创建了自己的基于流的解析器。 它
-
之前检查语法
-
可以使用-或/ ** /识别注释
1 2 3 4 5
| -- some commented text
/*
drop table Users;
GO
*/ |
-
可以识别带有'或'的字符串文字
1 2 3 4
| set @s =
'create table foo(...);
GO
create index ...'; |
-
保留LF和CR格式
-
将注释块保留在对象主体(存储过程,视图等)中
-
和其他结构,例如
如何使用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| try
{
using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DATABASE-NAME;Data Source=SERVER-NAME"))
{
connection.Open();
int rowsAffected = SqlStatementReader.ExecuteSqlFile(
"C:\\target-sql-script.sql",
connection,
// Don't forget to use the correct file encoding!!!
Encoding.Default,
// Indefinitely (sec)
0
);
}
}
// implement your handlers
catch (SqlStatementReader.SqlBadSyntaxException) { }
catch (SqlException) { }
catch (Exception) { } |
基于流的SQL脚本阅读器
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480
| class SqlStatementReader
{
public class SqlBadSyntaxException : Exception
{
public SqlBadSyntaxException(string description) : base(description) { }
public SqlBadSyntaxException(string description, int line) : base(OnBase(description, line, null)) { }
public SqlBadSyntaxException(string description, int line, string filePath) : base(OnBase(description, line, filePath)) { }
private static string OnBase(string description, int line, string filePath)
{
if (filePath == null)
return string.Format("Line: {0}. {1}", line, description);
else
return string.Format("File: {0}
Line: {1}. {2}", filePath, line, description);
}
}
enum SqlScriptChunkTypes
{
InstructionOrUnquotedIdentifier = 0,
BracketIdentifier = 1,
QuotIdentifierOrLiteral = 2,
DblQuotIdentifierOrLiteral = 3,
CommentLine = 4,
CommentMultiline = 5,
}
StreamReader _sr = null;
string _filePath = null;
int _lineStart = 1;
int _lineEnd = 1;
bool _isNextChar = false;
char _nextChar = '\0';
public SqlStatementReader(StreamReader sr)
{
if (sr == null)
throw new ArgumentNullException("StreamReader can't be null.");
if (sr.BaseStream is FileStream)
_filePath = ((FileStream)sr.BaseStream).Name;
_sr = sr;
}
public SqlStatementReader(StreamReader sr, string filePath)
{
if (sr == null)
throw new ArgumentNullException("StreamReader can't be null.");
_sr = sr;
_filePath = filePath;
}
public int LineStart { get { return _lineStart; } }
public int LineEnd { get { return _lineEnd == 1 ? _lineEnd : _lineEnd - 1; } }
public void LightSyntaxCheck()
{
while (ReadStatementInternal(true) != null) ;
}
public string ReadStatement()
{
for (string s = ReadStatementInternal(false); s != null; s = ReadStatementInternal(false))
{
// skip empty
for (int i = 0; i < s.Length; i++)
{
switch (s[i])
{
case ' ': continue;
case '\t': continue;
case '
': continue;
case '
': continue;
default:
return s;
}
}
}
return null;
}
string ReadStatementInternal(bool syntaxCheck)
{
if (_isNextChar == false && _sr.EndOfStream)
return null;
StringBuilder allLines = new StringBuilder();
StringBuilder line = new StringBuilder();
SqlScriptChunkTypes nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
SqlScriptChunkTypes currentChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
char ch = '\0';
int lineCounter = 0;
int nextLine = 0;
int currentLine = 0;
bool nextCharHandled = false;
bool foundGO;
int go = 1;
while (ReadChar(out ch))
{
if (nextCharHandled == false)
{
currentChunk = nextChunk;
currentLine = nextLine;
switch (currentChunk)
{
case SqlScriptChunkTypes.InstructionOrUnquotedIdentifier:
if (ch == '[')
{
currentChunk = nextChunk = SqlScriptChunkTypes.BracketIdentifier;
currentLine = nextLine = lineCounter;
}
else if (ch == '"')
{
currentChunk = nextChunk = SqlScriptChunkTypes.DblQuotIdentifierOrLiteral;
currentLine = nextLine = lineCounter;
}
else if (ch == '\'')
{
currentChunk = nextChunk = SqlScriptChunkTypes.QuotIdentifierOrLiteral;
currentLine = nextLine = lineCounter;
}
else if (ch == '-' && (_isNextChar && _nextChar == '-'))
{
nextCharHandled = true;
currentChunk = nextChunk = SqlScriptChunkTypes.CommentLine;
currentLine = nextLine = lineCounter;
}
else if (ch == '/' && (_isNextChar && _nextChar == '*'))
{
nextCharHandled = true;
currentChunk = nextChunk = SqlScriptChunkTypes.CommentMultiline;
currentLine = nextLine = lineCounter;
}
else if (ch == ']')
{
throw new SqlBadSyntaxException("Incorrect syntax near ']'.", _lineEnd + lineCounter, _filePath);
}
else if (ch == '*' && (_isNextChar && _nextChar == '/'))
{
throw new SqlBadSyntaxException("Incorrect syntax near '*'.", _lineEnd + lineCounter, _filePath);
}
break;
case SqlScriptChunkTypes.CommentLine:
if (ch == '
' && (_isNextChar && _nextChar == '
'))
{
nextCharHandled = true;
currentChunk = nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
currentLine = nextLine = lineCounter;
}
else if (ch == '
' || ch == '
')
{
currentChunk = nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
currentLine = nextLine = lineCounter;
}
break;
case SqlScriptChunkTypes.CommentMultiline:
if (ch == '*' && (_isNextChar && _nextChar == '/'))
{
nextCharHandled = true;
nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
nextLine = lineCounter;
}
else if (ch == '/' && (_isNextChar && _nextChar == '*'))
{
throw new SqlBadSyntaxException("Missing end comment mark '*/'.", _lineEnd + currentLine, _filePath);
}
break;
case SqlScriptChunkTypes.BracketIdentifier:
if (ch == ']')
{
nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
nextLine = lineCounter;
}
break;
case SqlScriptChunkTypes.DblQuotIdentifierOrLiteral:
if (ch == '"')
{
if (_isNextChar && _nextChar == '"')
{
nextCharHandled = true;
}
else
{
nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
nextLine = lineCounter;
}
}
break;
case SqlScriptChunkTypes.QuotIdentifierOrLiteral:
if (ch == '\'')
{
if (_isNextChar && _nextChar == '\'')
{
nextCharHandled = true;
}
else
{
nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
nextLine = lineCounter;
}
}
break;
}
}
else
nextCharHandled = false;
foundGO = false;
if (currentChunk == SqlScriptChunkTypes.InstructionOrUnquotedIdentifier || go >= 5 || (go == 4 && currentChunk == SqlScriptChunkTypes.CommentLine))
{
// go = 0 - break, 1 - begin of the string, 2 - spaces after begin of the string, 3 - G or g, 4 - O or o, 5 - spaces after GO, 6 - line comment after valid GO
switch (go)
{
case 0:
if (ch == '
' || ch == '
')
go = 1;
break;
case 1:
if (ch == ' ' || ch == '\t')
go = 2;
else if (ch == 'G' || ch == 'g')
go = 3;
else if (ch != '
' && ch != '
')
go = 0;
break;
case 2:
if (ch == 'G' || ch == 'g')
go = 3;
else if (ch == '
' || ch == '
')
go = 1;
else if (ch != ' ' && ch != '\t')
go = 0;
break;
case 3:
if (ch == 'O' || ch == 'o')
go = 4;
else if (ch == '
' || ch == '
')
go = 1;
else
go = 0;
break;
case 4:
if (ch == '
' && (_isNextChar && _nextChar == '
'))
go = 5;
else if (ch == '
' || ch == '
')
foundGO = true;
else if (ch == ' ' || ch == '\t')
go = 5;
else if (ch == '-' && (_isNextChar && _nextChar == '-'))
go = 6;
else
go = 0;
break;
case 5:
if (ch == '
' && (_isNextChar && _nextChar == '
'))
go = 5;
else if (ch == '
' || ch == '
')
foundGO = true;
else if (ch == '-' && (_isNextChar && _nextChar == '-'))
go = 6;
else if (ch != ' ' && ch != '\t')
throw new SqlBadSyntaxException("Incorrect syntax was encountered while parsing go.", _lineEnd + lineCounter, _filePath);
break;
case 6:
if (ch == '
' && (_isNextChar && _nextChar == '
'))
go = 6;
else if (ch == '
' || ch == '
')
foundGO = true;
break;
default:
go = 0;
break;
}
}
else
go = 0;
if (foundGO)
{
if (ch == '
' || ch == '
')
{
++lineCounter;
}
// clear GO
string s = line.Append(ch).ToString();
for (int i = 0; i < s.Length; i++)
{
switch (s[i])
{
case ' ': continue;
case '\t': continue;
case '
': continue;
case '
': continue;
default:
_lineStart = _lineEnd;
_lineEnd += lineCounter;
return allLines.Append(s.Substring(0, i)).ToString();
}
}
return string.Empty;
}
// accumulate by string
if (ch == '
' && (_isNextChar == false || _nextChar != '
'))
{
++lineCounter;
if (syntaxCheck == false)
allLines.Append(line.Append('
').ToString());
line.Clear();
}
else if (ch == '
')
{
++lineCounter;
if (syntaxCheck == false)
allLines.Append(line.Append('
').ToString());
line.Clear();
}
else
{
if (syntaxCheck == false)
line.Append(ch);
}
}
// this is the end of the stream, return it without GO, if GO exists
switch (currentChunk)
{
case SqlScriptChunkTypes.InstructionOrUnquotedIdentifier:
case SqlScriptChunkTypes.CommentLine:
break;
case SqlScriptChunkTypes.CommentMultiline:
if (nextChunk != SqlScriptChunkTypes.InstructionOrUnquotedIdentifier)
throw new SqlBadSyntaxException("Missing end comment mark '*/'.", _lineEnd + currentLine, _filePath);
break;
case SqlScriptChunkTypes.BracketIdentifier:
if (nextChunk != SqlScriptChunkTypes.InstructionOrUnquotedIdentifier)
throw new SqlBadSyntaxException("Unclosed quotation mark [.", _lineEnd + currentLine, _filePath);
break;
case SqlScriptChunkTypes.DblQuotIdentifierOrLiteral:
if (nextChunk != SqlScriptChunkTypes.InstructionOrUnquotedIdentifier)
throw new SqlBadSyntaxException("Unclosed quotation mark ".", _lineEnd + currentLine, _filePath);
break;
case SqlScriptChunkTypes.QuotIdentifierOrLiteral:
if (nextChunk != SqlScriptChunkTypes.InstructionOrUnquotedIdentifier)
throw new SqlBadSyntaxException("Unclosed quotation mark '.", _lineEnd + currentLine, _filePath);
break;
}
if (go >= 4)
{
string s = line.ToString();
for (int i = 0; i < s.Length; i++)
{
switch (s[i])
{
case ' ': continue;
case '\t': continue;
case '
': continue;
case '
': continue;
default:
_lineStart = _lineEnd;
_lineEnd += lineCounter + 1;
return allLines.Append(s.Substring(0, i)).ToString();
}
}
}
_lineStart = _lineEnd;
_lineEnd += lineCounter + 1;
return allLines.Append(line.ToString()).ToString();
}
bool ReadChar(out char ch)
{
if (_isNextChar)
{
ch = _nextChar;
if (_sr.EndOfStream)
_isNextChar = false;
else
_nextChar = Convert.ToChar(_sr.Read());
return true;
}
else if (_sr.EndOfStream == false)
{
ch = Convert.ToChar(_sr.Read());
if (_sr.EndOfStream == false)
{
_isNextChar = true;
_nextChar = Convert.ToChar(_sr.Read());
}
return true;
}
else
{
ch = '\0';
return false;
}
}
public static int ExecuteSqlFile(string filePath, SqlConnection connection, Encoding fileEncoding, int commandTimeout)
{
int rowsAffected = 0;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
// Simple syntax check (you can comment out these two lines below)
new SqlStatementReader(new StreamReader(fs, fileEncoding)).LightSyntaxCheck();
fs.Seek(0L, SeekOrigin.Begin);
// Read statements without GO
SqlStatementReader rd = new SqlStatementReader(new StreamReader(fs, fileEncoding));
string stmt;
while ((stmt = rd.ReadStatement()) != null)
{
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = stmt;
cmd.CommandTimeout = commandTimeout;
int i = cmd.ExecuteNonQuery();
if (i > 0)
rowsAffected += i;
}
}
}
return rowsAffected;
}
} |
对于仍然有问题的任何人。 您可以使用官方的Microsoft SMO
https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/overview-smo?view=sql-server-2017
1 2 3 4 5
| using (var connection = new SqlConnection(connectionString))
{
var server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(sql);
} |
太难了 :)
创建字符串数组str []将GO替换为",@":
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152
| string[] str ={
@"
USE master;
",@"
CREATE DATABASE" +con_str_initdir+ @";
",@"
-- Verify the database files and sizes
--SELECT name, size, size*1.0/128 AS [Size in MBs]
--SELECT name
--FROM sys.master_files
--WHERE name = N'" + con_str_initdir + @"';
--GO
USE" + con_str_initdir + @";
",@"
SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Customers](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [nvarchar](50) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
",@"
SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GOODS]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[GOODS](
[GoodsID] [int] IDENTITY(1,1) NOT NULL,
[GoodsName] [nvarchar](50) NOT NULL,
[GoodsPrice] [float] NOT NULL,
CONSTRAINT [PK_GOODS] PRIMARY KEY CLUSTERED
(
[GoodsID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
",@"
SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[Date] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
",@"
SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderDetails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[GoodsID] [int] NOT NULL,
[Qty] [int] NOT NULL,
[Price] [float] NOT NULL,
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[OrderID] ASC,
[GoodsID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
",@"
SET ANSI_NULLS ON
",@"
SET QUOTED_IDENTIFIER ON
",@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InsertCustomers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE [dbo].[InsertCustomers]
@CustomerName nvarchar(50),
@Identity int OUT
AS
INSERT INTO Customers (CustomerName) VALUES(@CustomerName)
SET @Identity = SCOPE_IDENTITY()
'
END
",@"
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Orders_Customers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Orders]'))
ALTER TABLE [dbo].[Orders] WITH CHECK ADD CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
ON UPDATE CASCADE
",@"
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
",@"
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OrderDetails_GOODS]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderDetails]'))
ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [FK_OrderDetails_GOODS] FOREIGN KEY([GoodsID])
REFERENCES [dbo].[GOODS] ([GoodsID])
ON UPDATE CASCADE
",@"
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_GOODS]
",@"
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OrderDetails_Orders]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderDetails]'))
ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
ON UPDATE CASCADE
ON DELETE CASCADE
",@"
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Orders]
"};
for(int i =0; i<str.Length;i++)
{
myCommand.CommandText=str[i];
try
{
myCommand.ExecuteNonQuery();
}
catch (SystemException ee)
{
MessageBox.Show("Error "+ee.ToString());
}
} |
就这样,享受。
|