我有一些 SQL 請求：
go
Update dbo.Parameter set ValueAsStr = '{
"CreateDepoUrl": "https://sandbox.sg...../",
"CheckDepoStatusUrl": "https://sandbox.sg..../",
"CreatePayoutUrl": "https://sandbox.sg....../",
"CheckPayoutStatusUrl": "https://sandbox.sg..../",
"PayoutTerminalIds": {
....
go
如果我在 SSMS 中發送此請求一切正常
我從 C# 代碼發送 SQL 請求的方法：
public static void SendToMainSqlRequest(MainDbContext mainDbContext, string queryString)
{
using (var conn = mainDbContext.Database.GetDbConnection())
{
conn.Open();
var command = mainDbContext.Database.GetDbConnection().CreateCommand();
command.CommandText = queryString;
command.CommandType = CommandType.Text;
int number = command.ExecuteNonQuery();
Console.WriteLine("count of updates: {0}", number);
conn.Close();
}
}
當我在 C# 代碼中發送請求時，出現例外：
'.' 附近的語法不正確
如果我洗掉“dbo”。在 SQL 請求中，我得到一個例外：
'=' 附近的語法不正確
表名和欄位名是正確的。沒有錯別字。
我該如何解決這個問題？
謝謝！
uj5u.com熱心網友回復：
我使用下面的代碼來做到這一點：
...
var lines = GoSplitter.Split(queryString);
foreach(var line in lines)
{
command.CommandText = line;
command.CommandType = CommandType.Text;
int number = command.ExecuteNonQuery();
// process number if needed
}
...
GoSplitter 類（抱歉，評論是法語）：
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text.RegularExpressions;
namespace DatabaseAndLogLibrary.DataBase
{
/// <summary>
/// Coupe un texte SQL en fonction des GO
/// Prend en compte les Go dans les chaines ou les commentaires SQL qui eux doivent être ignorés
/// Retire aussi les instruction SQL : USE
/// </summary>
internal class GoSplitter
{
/// <summary>
/// Pour détection des instruction USE
/// </summary>
private static Regex regUse = new Regex(@"^\s*USE\s");
/// <summary>
/// Renvoie la liste des instructions SQL séparé en fonction des GO dans le script d'origine
/// Prend en compte les Go dans les chaines ou les commentaires SQL qui eux doivent être ignorés
/// </summary>
/// <param name="fileContent"></param>
/// <returns></returns>
public static IEnumerable<string> Split(string fileContent)
{
if (string.IsNullOrWhiteSpace(fileContent))
{
yield break;
}
string res;
var currentState = EState.Normal;
List<Marker> markers = LoadMarker(fileContent).OrderBy(x => x.Index).ToList();
int index0 = 0;
for (int i = 0; i < markers.Count; i )
{
switch (currentState)
{
case EState.Normal:
switch (markers[i].Event)
{
case EMarker.Go:
res = fileContent.Substring(index0, markers[i].Index - index0).Trim();
res = ReplaceUse(res);
if (!string.IsNullOrWhiteSpace(res))
{
yield return res;
}
index0 = markers[i].Index 2; // 2 lettres dans go
break;
case EMarker.Quote:
currentState = EState.InText;
break;
case EMarker.Comment:
currentState = EState.InComment;
break;
}
break;
case EState.InText:
if (markers[i].Event == EMarker.Quote)
{
currentState = EState.Normal;
}
break;
case EState.InComment:
if (markers[i].Event == EMarker.EndComment)
{
currentState = EState.Normal;
}
break;
}
}
res = fileContent.Substring(index0, fileContent.Length - index0).Trim();
res = ReplaceUse(res);
if (!string.IsNullOrWhiteSpace(res))
{
yield return res;
}
}
/// <summary>
/// Charge les points clés du script
/// </summary>
/// <param name="fileContent"></param>
/// <returns></returns>
private static IEnumerable<Marker> LoadMarker(string fileContent)
{
var regGo = new Regex(@"\bgo\b", RegexOptions.Multiline | RegexOptions.IgnoreCase);
foreach(var m in regGo.Matches(fileContent).Where(x => x.Success).Select(x => new Marker() { Index = x.Index, Event = EMarker.Go }))
{
yield return m;
}
var regQuote = new Regex(@"'", RegexOptions.Multiline);
foreach (var m in regQuote.Matches(fileContent).Where(x => x.Success).Select(x => new Marker() { Index = x.Index, Event = EMarker.Quote }))
{
yield return m;
}
var regComment1 = new Regex(@"-(-) [\s\S]*?$", RegexOptions.Multiline);
foreach (Match m in regComment1.Matches(fileContent).Where(x => x.Success))
{
yield return new Marker() { Index = m.Index, Event = EMarker.Comment };
yield return new Marker() { Index = m.Index m.Length, Event = EMarker.EndComment };
}
var regComment2 = new Regex(@"/\*[\s\S]*?\*/", RegexOptions.Multiline);
foreach (Match m in regComment2.Matches(fileContent).Where(x => x.Success))
{
yield return new Marker() { Index = m.Index, Event = EMarker.Comment };
yield return new Marker() { Index = m.Index m.Length, Event = EMarker.EndComment };
}
}
/// <summary>
/// Remplace les instructions using
/// </summary>
/// <param name="sqlLine"></param>
/// <returns></returns>
private static string ReplaceUse(string sqlLine)
=> regUse.Replace(sqlLine, string.Empty); // .Replace("USE", "---");
[DebuggerDisplay("{Index} - {Event}")]
private class Marker
{
public int Index {get; set;}
public EMarker Event { get; set; }
}
/// <summary>
/// les types de détection qui aggissent sur l'automate
/// </summary>
private enum EMarker
{
Go,
Quote,
Comment,
EndComment
}
/// <summary>
/// Les états de l'automate
/// </summary>
private enum EState
{
Normal,
InComment,
InText
}
}
}
享受！
uj5u.com熱心網友回復：
您可以使用 SQL Server 管理物件庫來執行帶有 GO 陳述句的 SQL 命令。我們在內部使用它來執行資料庫遷移腳本來更新我們的資料庫架構。
這是圖書館。
以及一些演示如何使用它的示例代碼：
using System;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.Data.SqlClient;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var server = new Server(new ServerConnection(connection));
//this is to get script output (if you have any)
server.ConnectionContext.ServerMessage = (sender, eventArgs) =>
{
Console.WriteLine(eventArgs.Error.Message);
};
server.ConnectionContext.ExecuteNonQuery("some SQL with GO statements");
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/335662.html
標籤：C＃ sql-server 实体框架