A simple .NET version of SQLCMD for executing an SQL Script against Microsoft SQL Server

program.cs

namespace uksqlcmd
{
    using System;
    using System.Data.SqlClient;
    using System.IO;
    using System.Text;

    internal static class Program
    {
        private static int Main(string[] args)
        {
            try
            {
                Console.WriteLine("Execute a given SQL script on a given connection string.");
                Console.WriteLine("");
                Console.WriteLine("This also works with very large files, as it reads the file line-by-line");
                Console.WriteLine("and not in one batch into memory.");
                Console.WriteLine("");
                Console.WriteLine("Syntax:");
                Console.WriteLine("    uksqlcmd.exe \"MyConnectionString\" \"C:\\Temp\\MySqlScript.sql\" <separator mode> <error handling kind>");
                Console.WriteLine("");
                Console.WriteLine("The first parameter is the full connection string,");
                Console.WriteLine("the second parameter is the full path to the sql script,");
                Console.WriteLine("the third, optional parameter is the separator mode,");
                Console.WriteLine("the fourth, optional parameter is the error handling kind.");
                Console.WriteLine("");
                Console.WriteLine("Possible separator modes:");
                Console.WriteLine("    \"go\"   - single lines that only contain \"GO\" split SQL statements");
                Console.WriteLine("               This is the default");
                Console.WriteLine("    \"line\" - each line is executed directly, independent of other lines");
                Console.WriteLine("");
                Console.WriteLine("Possible error handling kinds:");
                Console.WriteLine("    \"stop\" - Stop program upon SQL errors");
                Console.WriteLine("               This is the default");
                Console.WriteLine("    \"cont\" - Continue executing SQL statements even after errors occurred");
                Console.WriteLine("");
                Console.WriteLine("Returns 0 upon success, -1 upon failure.");
                Console.WriteLine("");
                Console.WriteLine("Example usage:");
                Console.WriteLine("");
                Console.WriteLine("    uksqlcmd.exe \"Server=MyServer\\SQLEXPRESS; Database=MyDB; Integrated Security=true\" \"C:\\Ablage\\my.sql\" \"line\" ");
                Console.WriteLine("");
                Console.WriteLine("");
                Console.WriteLine("August 2019, [email protected] https://uwe.co");
                Console.WriteLine("");

                // --

                if (args.Length < 2 || args.Length > 4)
                {
                    Console.Error.WriteLine($"ERROR: Expected 2 or 4 command line parameters but got {args.Length}.");
                    return -1;
                }

                var cs = args[0];
                var filePath = args[1];

                var lineMode = args.Length > 2 ? args[2]?.Trim() : null;
                if (string.IsNullOrWhiteSpace(lineMode)) lineMode = @"GO";
                lineMode = lineMode.ToLowerInvariant();

                var errorHandling = args.Length > 3 ? args[3]?.Trim() : null;
                if (string.IsNullOrWhiteSpace(errorHandling)) errorHandling = @"stop";
                errorHandling = errorHandling.ToLowerInvariant();

                Console.WriteLine($"Command line argument 1 (connection string)   : \"{cs}\"");
                Console.WriteLine($"Command line argument 2 (SQL script file path): \"{filePath}\"");
                Console.WriteLine($"Command line argument 3 (line mode)           : \"{lineMode}\"");
                Console.WriteLine($"Command line argument 4 (error handling kind) : \"{errorHandling}\"");
                Console.WriteLine("");

                if (!File.Exists(filePath))
                {
                    Console.Error.WriteLine($"ERROR: File '{filePath}' does not exist.");
                    return -1;
                }
                if (new FileInfo(filePath).Length <= 0)
                {
                    Console.Error.WriteLine($"ERROR: File '{filePath}' is empty.");
                    return -1;
                }

                var totalExecutedQueriesCount = 0;

                using (var conn = new SqlConnection(cs))
                {
                    conn.Open();

                    var sb = new StringBuilder();

                    using (var reader = new StreamReader(filePath))
                    {
                        while (reader.Peek() > 0)
                        {
                            var line = reader.ReadLine();
                            if (lineMode == @"line")
                            {
                                line = line?.Trim();
                                if (!string.IsNullOrEmpty(line)) executeSql(conn, line, errorHandling, ref totalExecutedQueriesCount);
                            }
                            else if (lineMode == @"go")
                            {
                                if (isStopLine(line) && sb.Length > 0)
                                {
                                    executeSql(conn, sb.ToString(), errorHandling, ref totalExecutedQueriesCount);
                                    sb.Clear();
                                }
                                else
                                {
                                    sb.AppendLine(line ?? string.Empty);
                                }
                            }
                            else
                            {
                                throw new Exception($"Unknown separator mode '{lineMode}'.");
                            }
                        }

                        if (sb.Length > 0)
                        {
                            executeSql(conn, sb.ToString(), errorHandling, ref totalExecutedQueriesCount);
                        }
                    }
                }

                Console.WriteLine("");
                Console.WriteLine($"Finished. Executed a total of {totalExecutedQueriesCount} SQL queries.");
                Console.WriteLine("");

                return 0;
            }
            catch (Exception x)
            {
                Console.Error.WriteLine($"ERROR: {x.Message}");
                Console.Error.WriteLine("");
                Console.Error.WriteLine(x.ToString());
                return -1;
            }
        }

        private static void executeSql(SqlConnection conn, string sql, string errorHandling, ref int totalExecutedQueriesCount)
        {
            Console.WriteLine("About to run SQL command:");
            Console.WriteLine("--------");
            Console.WriteLine(sql);
            Console.WriteLine("--------");
            Console.WriteLine("");

            using (var command = new SqlCommand(sql, conn))
            {
                try
                {
                    totalExecutedQueriesCount++;
                    command.ExecuteNonQuery();
                }
                catch (Exception x)
                {
                    switch (errorHandling)
                    {
                        case @"stop":
                            throw;
                        case @"cont":
                            Console.Error.WriteLine($"ERROR: Error '{x.Message}' executing SQL. Continuing.");
                            break;
                        default:
                            throw new Exception($"Unknown error handling kind '{errorHandling}'.");
                    }
                }
            }
        }

        private static bool isStopLine(string line)
        {
            if (string.IsNullOrWhiteSpace(line)) return false;
            line = line.Trim(' ', '\t', '\r', '\n', ';').ToLowerInvariant();

            return line == @"go";
        }
    }
}