AzurePoolCrossDbGenerator/GenerateSqlCmdBatch.cs
/// <summary>
/// Generate a .bat file for executing all SQL scripts in the current directory. 
/// </summary>
/// <param name="configFile"></param>
Generate a .bat file for executing all SQL scripts in the current directory.
Generate a . bat file for executing all SQL scripts in the current directory .
public static void GenerateSqlCmdBatch(Configs.InitialConfig config, string targetDirectory, string oParam)
        {
            bool runOnAz = string.Equals(oParam, "az", StringComparison.OrdinalIgnoreCase);
            if (!string.IsNullOrEmpty(oParam) && !runOnAz)
            {
                Program.WriteLine();
                Program.WriteLine("Param -o can be set to AZ (run on Azure) or omitted (run locally).", ConsoleColor.Red);
                Program.ExitApp();
            }
            string serverName = (runOnAz)? config.serverName + ".database.windows.net" : config.localServer;
            if (string.IsNullOrEmpty(serverName))
            {
                string serverParamName = (runOnAz) ? "serverName" : "localServer";
                Program.WriteLine();
                Program.WriteLine($"Missing `{serverParamName}` param in `/config/config.json`", ConsoleColor.Red);
                Program.ExitApp();
            }
            string userName = (runOnAz) ? $"-U '{config.identity}'" : "";
            if (!Path.IsPathRooted(targetDirectory))
            {
                targetDirectory = targetDirectory.TrimStart(('\\'));
                if (!targetDirectory.ToLower().StartsWith("scripts\\")) targetDirectory = "scripts\\" + targetDirectory;
                targetDirectory = Path.Combine(Directory.GetCurrentDirectory(), targetDirectory);
            }
            string[] fileNames = Directory.GetFiles(targetDirectory);
            if (fileNames.Length == 0)
            {
                Program.WriteLine($"Empty folder: {targetDirectory}", ConsoleColor.Yellow);
                Program.ExitApp(2);
            }
            var sb = new System.Text.StringBuilder();
            foreach (string fileName in fileNames)
            {
                if (!fileName.EndsWith(fileExtSQL)) continue;
                string fileNameOnly = Path.GetFileName(fileName);
                var match = Regex.Match(fileNameOnly, @"^.*__([\w\d]*)__[\w\d]*__[\w\d]*", regexOptions_im);
                if (!match.Success || match.Groups.Count != 2)
                {
                    Program.WriteLine();
                    Program.WriteLine($"Cannot extract semantic parts from {fileNameOnly}", ConsoleColor.Red);
                    Program.ExitApp();
                }
                string dbName = match.Groups[1]?.Value;
                sb.AppendLine($"sqlcmd -b -S \"{serverName}\" {userName} -d {dbName} -i \"{fileNameOnly}\"");
                sb.AppendLine($"if ($LASTEXITCODE -eq 0) {{git add \"{fileNameOnly}\"}}");
            }
            sb.AppendLine(); 
            string batFileName = Path.Combine(targetDirectory, "apply.ps1");
            if (File.Exists(batFileName))
            {
                Program.WriteLine($"#{batFileName} already exists.", ConsoleColor.Yellow);
                Program.ExitApp(2);
            }
            Program.WriteLine($"Saving to {batFileName}");
            try
            {
                File.WriteAllText(batFileName, sb.ToString(), System.Text.Encoding.ASCII);
            }
            catch (Exception ex)
            {
                Program.WriteLine(ex.Message);
            }
        }
public static void GenerateSqlCmdBatch ( Configs . InitialConfig config , string targetDirectory , string oParam ) { bool runOnAz = string . Equals ( oParam , " " , StringComparison . OrdinalIgnoreCase ) ; if ( ! string . IsNullOrEmpty ( oParam ) && ! runOnAz ) { Program . WriteLine ( ) ; Program . WriteLine ( " " , ConsoleColor . Red ) ; Program . ExitApp ( ) ; } string serverName = ( runOnAz ) ? config . serverName + " " : config . localServer ; if ( string . IsNullOrEmpty ( serverName ) ) { string serverParamName = ( runOnAz ) ? " " : " " ; Program . WriteLine ( ) ; Program . WriteLine ( $" Missing ` { serverParamName } ` param in `/config/config.json` " , ConsoleColor . Red ) ; Program . ExitApp ( ) ; } string userName = ( runOnAz ) ? $" -U ' { config . identity } ' " : " " ; if ( ! Path . IsPathRooted ( targetDirectory ) ) { targetDirectory = targetDirectory . TrimStart ( ( ' \\ ' ) ) ; if ( ! targetDirectory . ToLower ( ) . StartsWith ( " \\ " ) ) targetDirectory = " \\ " + targetDirectory ; targetDirectory = Path . Combine ( Directory . GetCurrentDirectory ( ) , targetDirectory ) ; } string [ ] fileNames = Directory . GetFiles ( targetDirectory ) ; if ( fileNames . Length == 0 ) { Program . WriteLine ( $" Empty folder:  { targetDirectory } " , ConsoleColor . Yellow ) ; Program . ExitApp ( 2 ) ; } var sb = new System . Text . StringBuilder ( ) ; foreach ( string fileName in fileNames ) { if ( ! fileName . EndsWith ( fileExtSQL ) ) continue ; string fileNameOnly = Path . GetFileName ( fileName ) ; var match = Regex . Match ( fileNameOnly , @"^.*__([\w\d]*)__[\w\d]*__[\w\d]*" , regexOptions_im ) ; if ( ! match . Success || match . Groups . Count != 2 ) { Program . WriteLine ( ) ; Program . WriteLine ( $" Cannot extract semantic parts from  { fileNameOnly } " , ConsoleColor . Red ) ; Program . ExitApp ( ) ; } string dbName = match . Groups [ 1 ] ? . Value ; sb . AppendLine ( $" sqlcmd -b -S  \" { serverName } \" { userName }  -d  { dbName }  -i  \" { fileNameOnly } \" " ) ; sb . AppendLine ( $" if ($LASTEXITCODE -eq 0)  {{ git add  \" { fileNameOnly } \" }} " ) ; } sb . AppendLine ( ) ; string batFileName = Path . Combine ( targetDirectory , " " ) ; if ( File . Exists ( batFileName ) ) { Program . WriteLine ( $" # { batFileName }  already exists. " , ConsoleColor . Yellow ) ; Program . ExitApp ( 2 ) ; } Program . WriteLine ( $" Saving to  { batFileName } " ) ; try { File . WriteAllText ( batFileName , sb . ToString ( ) , System . Text . Encoding . ASCII ) ; } catch ( Exception ex ) { Program . WriteLine ( ex . Message ) ; } }
Generate a .bat file for executing all SQL scripts in the current directory.
Generate a . bat file for executing all SQL scripts in the current directory .
// check if we have the server name // check if we have the server name // prepare identity (user name) for Azure // the target directory can be relative or absolute // get all files in the folder // do not write out an empty file // loop thru the files // skip non-.sql files // extract the DB to run the script in from the file name // an empty line at the end to execute the last statement // output file name // do not overwrite files for consistency // save
config
Configs.InitialConfig
targetDirectory
string
oParam
string
configFile
None