SQL Simple Utilities

• Download source - 1606 KB

Introduction

This project provides three 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.
3. Show references: The user may specify a record in the database. Based on the table relations (foreign keys), the utility finds all related records up to a certain distance, and displays the graph using svg.

This project also demonstrates how to handle toolbars in MDI applications, how to display svg in a WebBrowser control, how to make a WebBrowser control zoomable, how to fill a DataSet with an asynchronous call to the database, how to write recursive SQL queries with Common Table Expressions (CTE), how to write a depth-first graph search algorithm, and how to display a graph.

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.

The form provides two tabs:

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

  

  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:

• 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:

• 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:

   

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

   

   The following message box is displayed:

   

   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.

   

   The following message box is displayed:

   

   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.

   

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

Show References

When looking at a record in the database, it is difficult to see, to which records it is related. The record may have references to other records, or other records may reference the given record. To find the records of the first case, the foreign keys have to be retrieved, that have as parent table the table of the given record. The parent columns of these foreign keys, have to be retrieved from this record. To find the records of the second case, the foreign keys have to be retrieved that have as referenced table the table of the given record. For each of these foreign keys, the records of the foreign key's referenced table have to be retrieved that reference the given record. All these neighbouring records are at a distance 1 from the given record. These records might also be related to other records which are related at a distance 2 from the given record. The same procedure may run up to a given distance. This utility, shows in a graph all related records of a given record up to a given distance.

The form provides two tabs:

• Execution: Provides controls to specifiy the table name, primary key value and distance, and shows the graph of the records and their relations.
  • Table name: The record's table name.
  • PK Values: Comma separated list of the record's primary key values.
  • Distance: Up to which distance the related records shall be retrieved.
  • Table filter: SQL WHERE clause, to exclude certain tables. For example, certain base tables that are referenced by many tables, such as currencies, countries, users may here be excluded.
• Connection: As above, provides parameters to specify the connection to an SQL server database.

Toolbar buttons:

• Run: Retrieves all primary keys and foreign keys of the database except of the tables specified by the "Table filter". For the given record, a graph is built of all related records up to a given distance. The graph is written to the files Graph1.dot, and Graph1.svg in the executable folder. It is displayed in the form's WebBrowser control if the file is smaller than 200KB, otherwise it is opened in the default Webbrowser.
• Cancel: Cancels execution. The unfinished graph will be written as above to the files and will be displayed as stated above.

Example

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

   

2. In the Execution tab, select the Sales.Customer table, set the PK Values to 30040, set the Distance to 1 and press the button Run: The form displays all records directly related to the record in Sales.Customer table with CustomerID=30040:

   

   In every vertex, four elements are displayed:
   • Level: An "L" followed by the level of the vertex. The level of a parent is higher than all levels of its children. The level represents the order of creation. Lower level records have been created before higher level records. Subsequently higher level records must be deleted before lower level records. Records of the same level may be deleted simultaneously.
   • Distance: A "D" followed by the distance from the start record. All records directly related to the start record (either direct parents or direct children), have a distance of 1. All records directly related to the records of distance 1, have a distance of 2, etc.
   • Table name: The table name to which the record, represented by the vertex, belongs to.
   • Primary key values: A comma separated list of the record's primary key values.
   In the status bar, the following information is displayed:
   • Max distance: The maximum distance between the start record and the related records.
   • Nr vertices: The number of vertices found.
   • Nr vertices: The number of edges found.
   • File: The file path of the generated svg file. A corresponding dot file is also created at the same location.
   • Length: The length of the svg file in bytes.
3. Set the Distance to 2. This will take already much more time (typically 4 minutes) and will produce about 10000 vertices. This is due to the Sales.Territory table which is referenced by the Sales.Customer table. A Sales.Territory record is referenced by many Sales.Customer records, as shows a fragment of the generated graph:

   

4. Set the Table filter to WHERE a.name != 'SalesTerritory'. The graph gets much smaller with only 19 vertices:

   

5. Set the Distance to 10 and the Table filter to WHERE a.name NOT IN ('AddressType','ContactType','CountryRegion','CurrencyRate','PhoneNumberType','Product','SalesPerson','SalesStore','SalesTerritory','ShipMethod','SpecialOffer','SpecialOfferProduct','StateProvince'), to exclude all base tables, and press Run. Upon completion, the status bar shows Max distance = 9. Because the maximum distance was set to 10, that means, that all records directly or indirectly related to the given record were retrieved. There are no records with a distance greater than 9 related to the given record (except of the tables given in the Table filter).
   

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.

Show References

FormShowReferences.vb implements the Show References functionality. All the processing is done in the BackgroundWorker1_DoWork method. The following statements are executed:

• FillSqlStatements: This method retrieves the primary keys and the foreign keys of all tables (except of the ones specified in the Table filter) and fills the DataTables mDataTableIXs and mDataTableFKs.
  • mDataTableIXs contains the primary keys and has the following columns:
    • TableName: Table name to which this index belongs to
    • PKColumn: Comma separated list of column names belonging to this index
  • mDataTableFKs contains the foreign keys and has the following columns:
    • FKName: Foreign key name
    • ParentTable: Referenced table of the foreign key
    • ChildTable: Parent table of the foreign key
    • ParentColumn: Comma separated list of the foreign key's referenced columns
    • ChildColumn: Comma separated list of the foreign key's parent columns
    The naming of the columns in the sys.foreign_keys table is a little bit confusing when trying to map the relationship defined by the foreign key to a parent-child relationship. Throughout this project, the parent table will be the foreign key's referenced table, and the child table will be the foreign key's parent table.
  Both queries to fill the above DataTables are recursive queries using CTE (Common Table Expressions).
  For each table, this method constructs the SQL statements to be executed when retrieving the neighbours of a record. For example, the table Sales.Customer has 3 parent tables and one child table:

  

  To get the parent records, the following SQL script must be executed:
  SQL

  SELECT PersonID,TerritoryID,StoreID FROM Sales.Customer _
  WHERE CustomerID=@Sales.Customer_CustomerID@

  To get the child records, the records of each child table that reference the given record must be retrieved, like in the following SQL script:
  SQL

  SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE CustomerID=@Sales.Customer_CustomerID@

  The SQL statements for each table are saved in the DataTable DataTableSQLStatements.
• A new graph is created: mGraph = New AdjacencyGraph(New DbVertexProvider(), New DbEdgeProvider(), False). AdjacencyGraph is in the namespace QuickGraph.Representations which is defined in the QuickGraph.Algorithms.dll. AdjacencyGraph represents a graph with vertices and edges.
• The initial vertex is created: Dim vertex = GetStartVertex(mGraph). The record specified in the UI is retrieved, and a vertex is created and added to the graph. A database record's corresponding vertex is defined by the table name and the primary key value. Two vertices represent the same record, if their table names and primary key values are equal.
• The neighbours of the start vertex are retrieved, by calling the method FillNeighbours. This method employees a depth first search (DFS) algorithm to build the graph:
  • If the distance is smaller than the maximum distance and the vertex has not yet been visited
    • A SQL statement is constructed by filling-in the primary key values of the vertex in the previously generated SQL statements. The related records are retrieved by calling the GetDataset method. The GetDataSet method uses the asynchronous method SqlAdapter.BeginExecuteReader to fill the DataSet.
    • For each of the foreign keys where the child table is the vertex's table:
      • Fill the parent record by calling the FillParent method. The FillParent method gets the child vertex and creates or retrieves the parent vertex:
        • The parent vertex is created.
        • If such a vertex already exists
          • The level of the child vertex is set to the maximum of the level of the child vertex and the level of the existing parent vertex plus 1.
          • An edge is added from the existing parent vertex to the child vertex.
        • Else
          • The parent's level is set to the child's level minus 1.
          • The parent vertex is added to the graph.
          • An edge is added from the newly created parent vertex to the child vertex.
          • All neighbours of the newly created parent vertex are filled by calling recursively the method FillNeighbours.
    • For each of the foreign keys where the parent table is the vertex's table:
      • For each of the rows of the child table
        • Fill the child record by calling the FillChild method. The FillChild method gets the parent vertex and creates or retrieves the child vertex:
          • The child vertex is created.
          • If such a vertex already exists
            • The level of the parent vertex is set to the minimum of the level of the parent vertex and the level of the existing child vertex minus 1.
            • An edge is added from the parent vertex to the existing child vertex.
          • Else
            • The child's level is set to the parent's level plus 1.
            • The child vertex is added to the graph.
            • An edge is added from the parent vertex to the newly created child vertex.
            • All neighbours of the newly created child vertex are retrieved by calling recursively the method FillNeighbours.
• The generated graph is written out to two files in the FillGraphViz method:
  • A GraphvizAlgorithm object is created: Dim svg = New GraphvizAlgorithm(graph, ".\", GraphvizImageType.Svg)
  • The Graph1.svg file is created: mFileName = svg.Write("Graph1")
  • The Graph1.dot file is created: File.WriteAllText("Graph1.dot", svg.Output.ToString())

The method BackgroundWorker1_RunWorkerCompleted retrieves the file Graph1.svg, and if it is smaller than 200kB, it displays it in the WebBrowser control, otherwise, it opens the file in the default Webbrowser.

This project uses the QuickGraph libraries provided in the project Dependency Visualizer. Unfortunately, I could not find documentation for these libraries. I tried to use the official QuickGraph libraries, but the documentation is very spare, and no examples are provided.

History

• 21^st May, 2013: Initial post
• 22^nd Sep, 2016: Added FormReferences.vb to show relations of database records