Click here to Skip to main content
Click here to Skip to main content
Go to top

SQL Simple Utilities

, 21 May 2013
Rate this:
Please Sign up or sign in to vote.
This project provides utilities for SQL server, such as executing a list of SQL scripts, and exporting data to an SQL script.
SqlSimpleUtilities main form

Introduction

This project provides two utilities for SQL server:

  1. Execute SQL scripts: The user may select a folder with SQL scripts, or a file containing a list of SQL scripts. The order of the scripts may be specified, and the SQL scripts may be executed.
  2. Export to SQL script: The user may specify a list of tables to be exported. Based on the table relations (foreign keys), the utility calculates the order (parent tables before child tables). The tables' data may then be exported to an SQL script, by preserving the relations.

This project also demonstrates how to handle toolbars in MDI applications.

Background

Execute SQL Scripts

There is often the need to execute several SQL scripts, one after another. The usual way to do this is to load the SQL scripts one by one into the SQL Server Management Studio, and execute them. This is cumbersome, time-consuming and error-prone, as easily an SQL script might be forgotten. The provided tool shown in the following image provides the functionality to execute many SQL scripts one after another.

Execute scripts form

The form provides two tabs:

  • Execution: Displays the list of SQL scripts and the execution progress.
  • Connection:

    Connection tab

    Provides parameters to specify the connection to an SQL server database.

    By pressing the button "Get", the list of SQL databases is loaded to the combo-box.

Toolbar buttons:Toolbar buttons
  • New: Clears the list.
  • Open: Prompts the user to select a file containing a list of SQL scripts. Upon selecting the file, the form's list is filled with the file names listed in the selected file.
  • Import: Prompts the user to select a folder containing SQL scripts. Upon selecting the folder, the form's list is filled alphabetically with the file names of .sql files of the selected folder and sub-folders. It is also possible to drag and drop files into the form's list.
  • Save: The form's list may be saved to file. This can later be opened by the Open button.
  • Run: Starts execution of the SQL scripts.
  • Pause: Pauses execution. When pressing Run, execution will resume from the script where the execution was paused.
  • Cancel: Cancels execution. When pressing Run, execution will start from the beginning.
  • Up: Moves the selected file name in the list one position up.
  • Down: Moves the selected file name in the list one position down.

Example

  1. Create a folder, for example D:\temp\SQLscripts and put some SQL scripts inside.
  2. Press the button import, and select that folder. The list is filled with the .sql files.
  3. Re-order the files with the up and down buttons.
  4. Press the button Run to start execution.
  5. If error messages occur, these will be written in the text-box at the bottom of the form.
  6. Press the button Save, to save the list of files for example to D:\temp\List1.txt.

Export to SQL Script

Parameterization in database tables must often be copied from development to UAT and production databases. This is often done by writing the appropriate SQL scripts. This tool exports data from selected database tables to an SQL script. The difficulty is in finding the correct order in which to export the tables, because there may be foreign keys between the tables. The foreign keys represent a graph. The graph is traversed to find the order in which to export the tables, because parent tables are exported before child tables. It is possible to specify the link with a parent table through a unique column.

The form provides two tabs:

  • Execution: Displays the list of tables, the foreign keys and the export progress.
  • Connection: As above, provides parameters to specify the connection to an SQL server database.
Toolbar buttons:Toolbar buttons
  • Prepare: Finds the foreign keys of the selected tables. If the Ids are different in the source and destination database, a unique column may be specified foreign keys grid, through which the Id of the parent table will be retrieved. If cyclic chains are found in the foreign keys graph, these are marked with a grey background and have to be removed. In the above case, one cyclic chain was found on table HumanResources.Employee which references itself through the ManagerID column. It was removed, so that export could be started. If no cycle chains are found, the order of the tables is calculated.
  • Run: Starts exporting data to an SQL script. The filename must be specified in the "SQL script" text-box.
  • Pause: Pauses execution. When pressing Run, execution will resume from the table where the execution was paused.
  • Cancel: Cancels execution. When pressing Run, execution will start from the beginning.

Example

  1. In the Connection tab, set the connection to the AdventureWorks database:

    Connection tab

  2. In the Execution tab, select the HumanResources tables and press the button Prepare: The grid is filled with the foreign keys:

    After first prepare

    The following message box is displayed:

    Message box cyclic chain

    The cyclic chain is the first row in the grid with grey background. The table Employees has a reference to itself.
  3. The tool does not allow cyclic references. Select the first row, and with right-click delete it. This does not remove the foreign key from the database. The ManagerID value will be used as is in the SQL script.
  4. Press again the button Prepare: The grid is filled with the foreign keys, and the order of the tables is displayed in the list on the right side.

    After second prepare

    The following message box is displayed:

    Message box foreign keys to other tables

    The two foreign keys that reference tables that are not exported have grey background.
  5. In the last foreign key, change the selected field in the Unique column to EmailAddress as shown in the picture before. This means that the ContactId field will be retrieved from the Person.Contact table by searching with the field EmailAddress, which is unique.
  6. Set the SQL script text-box to a valid file-name. This is the file where the script will be written to.
  7. Press the button Run. The exporting of the tables starts.

    During Run

  8. Upon completion, you may open the script by double-clicking the SQL script text-box.

Using the Code

Execute SQL Scripts

FormExecScripts.vb implements the execution of SQL scripts. An SQL script may hold GO statements, it is therefore not possible to execute these scripts with SqlCommand.ExecuteNonQuery. The method ModSql.ExecuteSql splits the SQL script at the GO statements. GO statements are at the beginning of the line, and no other statement is on the same line. The resulting SQL scripts are then executed one-by-one with SqlCommand.ExecuteNonQuery.

Export to SQL Script

FormExportToScript.vb implements the export to SQL script. The export logic is in the class ExportByTable. The method FKsComputeLevel computes the level (i.e. the order) of the foreign keys. The algorithm is described in the comments of the method. The SQL code is produced by the method DataTableExport.

History

  • 21st May, 2013: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Alexandros Pappas
Software Developer (Senior)
Greece Greece
No Biography provided

Comments and Discussions

 
GeneralMy vote of 4 PinmemberBeeWayDev22-May-13 3:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140921.1 | Last Updated 21 May 2013
Article Copyright 2013 by Alexandros Pappas
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid