net - leer archivo sql desde c#

Ejecutar un script SQL grande(con comandos GO)

Question

Necesito ejecutar un gran conjunto de sentencias SQL (creando un conjunto de tablas, vistas y procedimientos almacenados) desde dentro de un programa C #.

Estas declaraciones deben estar separadas por declaraciones GO , pero a SqlCommand.ExecuteNonQuery() no le gustan las declaraciones GO . Mi solución, que supongo que publicaré para referencia, fue dividir la cadena SQL en líneas GO y ejecutar cada lote por separado.

¿Hay una manera más fácil / mejor?

Answer 1

Basado en la solución de Blorgbeard.

foreach (var sqlBatch in commandText.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries))
{
   sqlCommand.CommandText = sqlBatch;
   sqlCommand.ExecuteNonQuery();
}

Answer 2

Demasiado difícil :)

Crear una matriz de cadenas str [] reemplazando GO con ", @":

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

            }

Eso es todo, disfruta.

Answer 3

Esto es lo que llamé juntos para resolver mi problema inmediato.

private void ExecuteBatchNonQuery(string sql, SqlConnection conn) {
    string sqlBatch = string.Empty;
    SqlCommand cmd = new SqlCommand(string.Empty, conn);
    conn.Open();
    sql += "/nGO";   // make sure last batch is executed.
    try {
        foreach (string line in sql.Split(new string[2] { "/n", "/r" }, StringSplitOptions.RemoveEmptyEntries)) {
            if (line.ToUpperInvariant().Trim() == "GO") {
                cmd.CommandText = sqlBatch;
                cmd.ExecuteNonQuery();
                sqlBatch = string.Empty;
            } else {
                sqlBatch += line + "/n";
            }
        }            
    } finally {
        conn.Close();
    }
}

Requiere comandos GO para estar en su propia línea, y no detectará comentarios de bloque, por lo que este tipo de cosas se dividirán y provocarán un error:

ExecuteBatchNonQuery(@"
    /*
    GO
    */", conn);

Answer 4

La palabra clave del separador por lotes "GO" la usa realmente SQL Management Studio, para que sepa dónde finalizar los lotes que envía al servidor y no se pasa al servidor SQL. Incluso puede cambiar la palabra clave en Management Studio, si así lo desea.

Answer 5

Llegué a este mismo problema y eventualmente lo resolví simplemente reemplazando una cadena, reemplazando la palabra IR con un punto y coma (;)

Todo parece funcionar bien al ejecutar scripts con comentarios en línea, comentarios de bloque y comandos GO

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

Answer 6

Lo logré hoy al cargar mi SQL de un archivo de texto en una cadena. Luego utilicé la función de división de cuerda para separar la cadena en comandos individuales que luego se enviaron al servidor individualmente. Simples :)

Me acabo de dar cuenta de que necesitas dividir en / nGO solo en caso de que aparezcan las letras GO en cualquiera de tus nombres de tabla, etc. ¡Supongo que tuve suerte allí!

Answer 7

Para evitar terceros, expresiones regulares, gastos generales de memoria y trabajo rápido con grandes scripts, creé mi propio analizador basado en secuencias. Eso

• comprueba la sintaxis antes

• puede reconocer comentarios con - o / ** /

  -- some commented text
   /*
  drop table Users;
  GO
     */

• puede reconocer cadenas literales con ''o''

  set @s =
      ''create table foo(...);
      GO
      create index ...'';

• conserva el formato LF y CR
• conserva el bloque de comentarios en cuerpos de objetos (procedimientos almacenados, vistas, etc.)

• y otras construcciones tales como

        gO -- commented text

Cómo utilizar

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

Lector de scripts SQL basado en secuencias

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}/r/nLine: {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 ''/r'': continue;
                    case ''/n'': 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 == ''/r'' && (_isNextChar && _nextChar == ''/n''))
                        {
                            nextCharHandled = true;
                            currentChunk = nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
                            currentLine = nextLine = lineCounter;
                        }
                        else if (ch == ''/n'' || ch == ''/r'')
                        {
                            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 == ''/r'' || ch == ''/n'')
                            go = 1;
                        break;
                    case 1:
                        if (ch == '' '' || ch == ''/t'')
                            go = 2;
                        else if (ch == ''G'' || ch == ''g'')
                            go = 3;
                        else if (ch != ''/n'' && ch != ''/r'')
                            go = 0;
                        break;
                    case 2:
                        if (ch == ''G'' || ch == ''g'')
                            go = 3;
                        else if (ch == ''/n'' || ch == ''/r'')
                            go = 1;
                        else if (ch != '' '' && ch != ''/t'')
                            go = 0;
                        break;
                    case 3:
                        if (ch == ''O'' || ch == ''o'')
                            go = 4;
                        else if (ch == ''/n'' || ch == ''/r'')
                            go = 1;
                        else
                            go = 0;
                        break;
                    case 4:
                        if (ch == ''/r'' && (_isNextChar && _nextChar == ''/n''))
                            go = 5;
                        else if (ch == ''/n'' || ch == ''/r'')
                            foundGO = true;
                        else if (ch == '' '' || ch == ''/t'')
                            go = 5;
                        else if (ch == ''-'' && (_isNextChar && _nextChar == ''-''))
                            go = 6;
                        else
                            go = 0;
                        break;
                    case 5:
                        if (ch == ''/r'' && (_isNextChar && _nextChar == ''/n''))
                            go = 5;
                        else if (ch == ''/n'' || ch == ''/r'')
                            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 == ''/r'' && (_isNextChar && _nextChar == ''/n''))
                            go = 6;
                        else if (ch == ''/n'' || ch == ''/r'')
                            foundGO = true;
                        break;
                    default:
                        go = 0;
                        break;
                }
            }
            else
                go = 0;

            if (foundGO)
            {
                if (ch == ''/r'' || ch == ''/n'')
                {
                    ++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 ''/r'': continue;
                        case ''/n'': continue;
                        default:
                            _lineStart = _lineEnd;
                            _lineEnd += lineCounter;
                            return allLines.Append(s.Substring(0, i)).ToString();
                    }
                }
                return string.Empty;
            }

            // accumulate by string
            if (ch == ''/r'' && (_isNextChar == false || _nextChar != ''/n''))
            {
                ++lineCounter;
                if (syntaxCheck == false)
                    allLines.Append(line.Append(''/r'').ToString());
                line.Clear();
            }
            else if (ch == ''/n'')
            {
                ++lineCounter;
                if (syntaxCheck == false)
                    allLines.Append(line.Append(''/n'').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 ''/r'': continue;
                    case ''/n'': 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;
    }
}

Answer 8

Puede usar objetos de administración de SQL para realizar esto. Estos son los mismos objetos que Management Studio usa para ejecutar consultas. Creo que Server.ConnectionContext.ExecuteNonQuery() realizará lo que necesite.

Answer 9

Si no desea instalar objetos SMO, puede usar la herramienta gplex (vea esta respuesta )

Answer 10

Si no desea ir a la ruta SMO, puede buscar y reemplazar "IR" para ";" y la consulta como lo harías. Tenga en cuenta que solo se devolverá el último conjunto de resultados.

Answer 11

Si no desea usar SMO, por ejemplo, porque necesita ser multiplataforma, también puede usar la clase ScriptSplitter de SubText.

Here''s la implementación en C # & VB.NET

Uso:

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

Si tiene problemas con los comentarios de estilo c multilínea, elimine los comentarios con expresiones regulares:

static string RemoveCstyleComments(string strInput)
{
    string strPattern = @"/[*][/w/d/s]+[*]/";
    //strPattern = @"//*.*?/*/"; // Doesn''t work
    //strPattern = "///*.*?//*/"; // Doesn''t work
    //strPattern = @"//*([^*]|[/r/n]|(/*+([^*/]|[/r/n])))*/*+/ "; // Doesn''t work
    //strPattern = @"//*([^*]|[/r/n]|(/*+([^*/]|[/r/n])))*/*+/ "; // Doesn''t work

    // http://.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

La eliminación de comentarios de una sola línea está aquí:

https://.com/questions/9842991/regex-to-remove-single-line-sql-comments

Answer 12

Si no desea utilizar SMO (que es mejor que la solución a continuación, pero quiero dar una alternativa ...) puede dividir su consulta con esta función.

Es:

• Prueba de comentarios (ejemplo --GO o / * GO * /)
• Solo funciona en una nueva línea, al igual que en SSMS (ejemplo / * prueba / * GO funciona y selecciona 1 como ir no

• A prueba de cadenas (ejemplo: ''no ir'')

  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] == ''/r'' || script[pos2] == ''/n'')
              {
                  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;
  }

Answer 13

También me enfrenté al mismo problema, y ​​no pude encontrar otra forma más que dividir la operación de SQL en archivos separados, y luego ejecutarlos en secuencia.

Obviamente, el problema no está en las listas de comandos DML, sino que se pueden ejecutar sin IR entre ellos; historia diferente con DDL (crear, alterar, soltar ...)

Answer 14

Tuve el mismo problema en Java y lo resolví con un poco de lógica y regex. Creo que se puede aplicar la misma lógica. Primero, leo del archivo slq en la memoria. Luego aplico la siguiente lógica. Es más o menos lo que se ha dicho antes, sin embargo, creo que usar palabras regex es más seguro que esperar una nueva línea de caracteres.

String pattern = "//bGO//b|//bgo//b";

String[] splitedSql = sql.split(pattern);
for (String chunk : splitedSql) {
  getJdbcTemplate().update(chunk);
}

Esto básicamente divide la cadena sql en una matriz de cadenas sql. La expresión regular es básicamente para detectar palabras completas ''ir'' en minúsculas o mayúsculas. Luego ejecutas las diferentes consultas secuencialmente.

Answer 15

Use objetos de administración de SQL Server (SMO) que comprende los separadores GO. Vea la publicación de mi blog aquí: http://weblogs.asp.net/jongalloway/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-2D00-the-easy-way

Código de muestra:

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

Si eso no funciona para usted, consulte la biblioteca de Phil Haack que maneja eso: http://haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators-and.aspx

Answer 16

utilice el siguiente método para dividir la cadena y ejecutar el lote por lote

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
        }

    }
}