I have some SQL request:

go
Update dbo.Parameter set ValueAsStr = '{
 "CreateDepoUrl": "https://sandbox.sg...../",
 "CheckDepoStatusUrl": "https://sandbox.sg..../",
 "CreatePayoutUrl": "https://sandbox.sg....../",
 "CheckPayoutStatusUrl": "https://sandbox.sg..../",
 "PayoutTerminalIds": {
....
go

If I send this request in SSMS all is alright
My method for send SQL request from C# code:

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();
    }
}

When I send request in C# code I get an exception:

Incorrect syntax near '.'

If I delete "dbo." in the SQL request I get an exception:

Incorrect syntax near '='

Table and field names are correct. Without typos.
How I can solve this problem?
Thanks!

CodePudding user response：

I use the code bellow to do this :

...
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
}
...

The class GoSplitter (sorry comments are in french):

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
        }
    }
}

Enjoy!

CodePudding user response：

You can use the SQL Server Management Objects library to execute SQL commands with GO statements. We use this internally for executing database migration scripts to update our database schema.

Here's the library.

And some sample code demonstrating how to use it:

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");
}