Knowledgebase

If you have a folder with (*.sql) TSQL table scripts and you want to deploy these scripts, you can manually add the file names by adding numbers to set the order of installation.

001. Cars.sql
002. Customers.sql
etc.

Or you can use a C# function to automatically determine the installation order of the TSQL table scripts:

public void ExecuteTableTSQLScriptFiles(string server, string database, string userId, string password, bool trustedConnection, string folder)
    {
      // Validate parameters
      if (string.IsNullOrEmpty(server)) { throw new ArgumentNullException("server"); }
      if (string.IsNullOrEmpty(database)) { throw new ArgumentNullException("database"); }
      if (string.IsNullOrEmpty(userId)) { throw new ArgumentNullException("userId"); }
      if (string.IsNullOrEmpty(password)) { throw new ArgumentNullException("password"); }
      if (string.IsNullOrEmpty(folder)) { throw new ArgumentNullException("folder"); }
      if (!Directory.Exists(folder)) { new ApplicationException(string.Format("Folder [{0]] does not exist", folder)); }

      // Get *.sql files in folder
      List<string> filesToProcess = new List<string>();
      filesToProcess.AddRange(Directory.GetFiles(folder, "*.sql"));

      // First loop will install all tables with no foreignkeys
      // Second loop will install all tables with foreignkeys that are not part of a foreignkey
      // Preceding loops will install all tables with foreignkeys that are part of a foreignekey
      // Use loopCounter to prevent endless loop
      int loopCounter = 0;
      while (filesToProcess.Count() > 0 && loopCounter <= 10)
      {
        List<string> reProcessFiles = new List<string>();
        foreach (string file in filesToProcess)
        {
          try
          {
              // Get script file contents
            string scriptContents = File.ReadAllText(file);

            // Make sure alle GO statements are capatilized
            scriptContents = scriptContents.Replace("\r\ngo\r\n", "\r\nGO\r\n");

            // Remove leading and trailing spaces
            scriptContents = scriptContents.Trim();

            // Determine if file contents ends with go statement
            if (scriptContents.EndsWith("go") || scriptContents.EndsWith("GO"))
            {
              // Remvove last go statement
              scriptContents = scriptContents.Remove(scriptContents.Length - 2);
            }

            //split the script on "GO" commands
            string[] splitter = new string[] { "\r\nGO\r\n" };
            string[] commandTexts = scriptContents.Split(splitter, StringSplitOptions.RemoveEmptyEntries);

            // Determine connection string
            string connectionString = string.Format(@"Server=""{0}"";Database=""{1}"";User ID=""{2}"";Password=""{3}"";Trusted_Connection={4};", server, database, userId, password, trustedConnection);

            // Run each sql command in the sql script file
            foreach (string commandText in commandTexts)
            {
              using (SqlConnection sqlConnection = new SqlConnection(connectionString))
              {
                using (SqlCommand command = new SqlCommand(commandText, sqlConnection))
                {
                  command.Connection.Open();
                  command.ExecuteNonQuery();
                }
              }
            }

          }
          catch (Exception ex)
          {
            // Add as first file to reProcessFiles, when exception starts with [Foreign key]
            if (!string.IsNullOrEmpty(ex.Message) && ex.Message.StartsWith("Foreign key"))
            {
              reProcessFiles.Insert(0, file);
            }
          }
        }

        filesToProcess = reProcessFiles;
        loopCounter++;
      }
    }

It’s a dirty function but somebody had to make it 😉