Features
SqlBuilder is a very useful, powerful and intelligent C# tool for all developers and teams, which works with databases.
- SqlBuilder makes working on SQL servers much easier.
- One of the multiple intentions of SqlBuilder is to completely replace the user unfriendly Microsoft SQL server tools Query Analyzer and Enterprise Manager.
- SqlBuilder has a complicated Syntax Parser, which does not only display SQL commands in color but also automatically converts badly written SQL code into readable code. Long SQL commands like
SELECT are wrapped automatically in an intelligent way onto multiple lines and are indented. The parsing happens while entering text. Normally you have to pay a lot of money for this functionality. - SqlBuilder reads automatically stored procedures, views, functions and triggers from the server and allows to edit and store them.
- SqlBuilder offers a complex Table Editor which allows to change values in table cells as easy as modifying an Excel sheet.
- The Table Editor allows to add / remove columns and to add / delete rows.
- The comfortable Table Designer shows Foreign Keys and Identity values, allows to add / remove Primary and Unique keys, Indexes, modify a column's data type, NULL, Default value.
- SQL commands can be executed and the query result is displayed as a scalar value or as one or multiple tables.
- With CTRL+E you can execute the selected part of the SQL code or, if nothing is selected, the entire code.
- Each file or system object / file has its own Undo buffer which maintains its content even if you edited another file meanwhile.
- All procedures, views, functions, triggers and table definitions can be saved in seconds as *.sql files into one folder. (DataBase Backup)
- You can search for a text string in all procedures, views, functions, triggers and table columns directly on the server. (DataBase Search)
- SqlBuilder was designed for developer teams to store in Subversion or CVS, the procedures, functions etc., that have been changed on the server since the last release and then automatically create ONE huge Compound SQL script for the next release, which is given to the client to execute to update his server. With one click, you create a file which stores all your work since the last release.
- SqlBuilder measures the time of execution of an SQL command with an exactness of 1 ms. So you can compare the speed of different SQL commands / procedures to optimize your code.
- Additionally with CTRL+B, you can first clear the server cache (
DBCC FREEPROCCACHE ...) and then execute the SQL command. So you can measure the time an SQL command needs when it is first executed before the server has cached query results. The difference may be a factor of 1:10 or more! - You can execute two
SELECT or EXECUTE queries at the same time which return a dataset with 2 tables and then compare the 2 query results for exact equality. SqlBuilder will tell you if the two results differ even if the lines in the tables appear in a different order. If you wish, SqlBuilder can remove all equal lines from the tables and leave only the different ones. So you can check after a modification you have made on a procedure if it still returns the same data as before. - You can export query results and tables into an HTML file or an Excel sheet or as an XML file.
- You can export table data to a XML file and import table data from a XML file.
- If you have not yet assigned a program for *.sql files, in the future SQL files will open in
SqlEditor. If you have already assigned a default program for SQL files, this will not be changed but in the context menu of Explorer (right click on a file) you will find a new entry "Open with SqlBuilder". - The very first time you start SqlBuilder, it will add a shortcut to itself in the startmenu and in the quicklaunch bar. If you don't want that, simply delete the shortcut: this will not happen again.
- The SQL editor and Table editor can be controled completely via keyboard. (See button "Keyboard Help")
- The entire SQL execution runs in a separate (abortable) thread so the GUI does not "hang" while you execute lengthy commands.
- This is a living project. The Internet Update Check will show you a messagebox if a new SqlBuilder version is available on my homepage.
Limitations
- .NET Framework 1.1 must be installed.
- I have not tried to compile SqlBuilder on Visual Studio 2005. As Microsoft has never cared about compatibility with older versions of their own software, it may be possible that you have to change some code? It is your own fault if you don't compile on Visual Studio 2003 where it runs perfectly!
- The program was written for Microsoft SQL server. For Oracle or other SQL servers you will have to adapt three *.cs files of the source code which are the abstraction layer which contains the server specific code.
Source Code
Although the source code is VERY complex, you will find a VERY clean and well structured code with plenty of comments written by a very experienced programmer.
(Coding effort: more than half a year!)
Usage
Suppose that you work on a project with multiple release versions of which each has its own folder on your local disk. Choose the folder of the current release in your server project. SqlBuilder will create two subfolders in the working directory: DatabaseBackup and Scripts.
SqlBuilder stores an XML file in the working directory with the server settings (server name, user name, password, etc.) If you work with multiple SQL servers, create a working directory for each server.
It is strongly recommended to ALWAYS additionally add a temporary folder in which you can experiment with SQL commands which are NOT stored in Subversion or CVS.
The next step is adding the SQL files or system objects on which you will work:
The Toolbar in the main window offers the following functionality: (from left to right)
- Add a new or existing Procedure, Function, View, Trigger, Table, File to the Filelist
- Delete an item from the Filelist
- Search in one or multiple databases for a text in a Procedure, View, Table column etc... (see below)
- Sort the FileList
- Open the selected table entirely in the Table Editor (see below)
- Open the TOP 10 rows of the selected table in the Table Editor
- Build the Compound Script (see below)
- Create a Database Backup (see below)
Click the leftmost toolbar button and this window will open:
No matter if you:
- Enter the name for a new SQL file or
- Select an already existing procedure from the drop down list or
- Enter the name for a new function,
SqlBuilder will add it to the Filelist of the main window:
There is a big difference between
- adding a File or Table (in blue) and
- adding a Procedure, Function, View or Trigger (in green):
It is very important that you understand the following:
Adding a Procedure, Function, View, Trigger
Adding a system object like a *.PROC, *.FUNC, *.VIEW, *.TRIG file means that all changes to SQL code are stored on the SQL server!
SqlBuilder creates a dummy file (like fn_GetUserById.func) in your working directory which does NOT contain any SQL code!
Adding a File, Table
On the other hand if you add a *.SQL or *.TABL file (like Test.sql above), all changes on SQL code will be stored locally in this file.
You will nearly never use the type "File" (*.sql) except for testing (executing) SQL code under development. Do NOT abuse type "File" (*.sql) to store the definitions of procedures, functions or modifications on tables, etc.!!
With CTRL + TAB you can rapidly switch between all the items in the Filelist that have been selected recently.
For each item in the Filelist, SqlBuilder will create the appropriate file in a subfolder of your Scripts folder. The Scripts folder will have a subfolder for each database.
How to Work with CVS / Subversion
You and all your colleagues will check in the entire content of your Scripts folder into Subversion or CVS. When the new release is ready, you click the button "Build Script" and SqlBuilder will build one huge Compound SQL Script.
This script contains:
- the contents of all *.sql and *.tabl files (unchanged) and
- the ACTUAL VERSION of all procedures, functions, views, triggers loaded directly from your SQL server.
Then you pass the compound script to your clients so they can update their SQL server to the latest version.
The SQL Editor
IMPORTANT:
To work with the SQL Editor you must know the keyborad shortcuts. Click the keyboard help button!!
The SQL editor stores all your changes in an Undo buffer which remains intact even after working on another file meanwhile. With CTRL-Z you can always return to the last workstate.
Executing Code
In a File or Table you can select any part of the SQL code and execute it with CTRL + E. If nothing is selected, the entire code will be executed.
Procedures, functions, views, triggers will always be executed entirely when hitting CTRL + E which will store them on the server.
It is recommended to have at least one Temp file in your Temp working directory where you execute SQL code for testing purposes.
With CTRL + TAB you can rapidly switch between all the items in the Filelist that have been selected recently.
If the result of a SQL query is a scalar value or if the query has no result at all, this will be displayed below in the main window:
If the result is one or multiple tables, these will be displayed in the ResultView window:
You always see the execution time with a real exactness of 1 millisecond. (Performance Counter)
If you want to see the execution time after clearing the server cache, use CTRL + B instead of CTRL + E.
CTRL + B disables all server optimizations, like buffering the query results, so you will see the worst case execution time.
The Toolbar of the Result View window offers the following functionality: (from left to right)
- Search a text inside the query result datagrid
- Compare two query results by their content (see below)
- Export all tables of the returned dataset into an Excel sheet
- Export all tables of the returned dataset and the SQL query into a HTML document
- Export the currently selected table into a XML file
- Show the keyboard shortcut help for this window.
Comparing Query Results by Content
In the ResultView window you can compare the contents of two tables.
To use it, you must send two SQL commands to the server at the same time.
Example:
EXEC proc_GetUserData 877, 1
EXEC proc_GetUserData_New 877, 1
The SQL server will return a dataset with two tables. If proc_GetUserData is a stored procedure and proc_GetUserData_New is a procedure which you have modified, you can test how your modifications affect the returned data.
The Table Editor
The Table Editor is a very powerful tool in SqlBuilder. It allows you to edit tables on the SQL server as if they were Excel tables. You can set the cursor into a cell and modify its value. When you are done with all changes you save the changes. After closing the TableEditor you will see all the SQL code that has been executed in the *.TABL file in the main window. These changes will automatically be included into the compound script if you didn't delete them.
The column's background color changes if the column has a UNIQUE or PRIMARY key. The column's text color depends on the data type (string, integer etc..)
The Toolbar of the Table Editor offers the following functionality: (from left to right)
- Search a text in the table
- Generate all SQL commands required to save all changes to the server that have been made on the table cells
- Reload the entire table from the server.
- Reload the TOP 10 rows of the table from the server.
- Add a new row
- Delete the row which has the cursor
- Add a new column
- Delete the column which has the cursor
- Open the Table Designer (see below)
- Build all SQL commands which are required to create this table and its columns and all its content from the scratch.
- Export the table's content as HTML document
- Export the table's content as Excel sheet
- Export the table's content and the column definitions and their constraints into a XML file
- Import the content from a XML file into the table (append to current content or replace current content)
- Show the keyboard shortcut help for this window.
The Table Designer
The Table Designer is a very powerful tool that allows you to view / modify:
- the data type of a column (e.g. changing
varchar(10) into varchar(50)) - the Identity (Seed / Increment)
- the Default value
- the Indexes of a column
- the Foreign Keys from this table to other tables
- the Foreign Keys from other tables to this table
- allow / disallow NULL for the column
- add / remove a Primary key
- add / remove a Unique key
The job of the Table Designer is very complicated because Microsoft's SQL is so incredibly primitive. You will see that when you study the SQL commands which have been executed after modifying an existing primary key in a column which is referenced by foreign keys.
Autogenerated code example:
As you see, SqlBuilder tries to generate universal SQL scripts:
- If the User with the ID 56196 already exists, its data is updated.
- If the user does not yet exist, a new row is added to the table.
- Microsoft's SQL is so incredibly primitive that there is no easier way. (doing this with one single command)
- You can give a script which updates table data to an administrator of one of your clients and it will always run without errors even if it is run twice.
But if you modify Constraints this will not be possible because the names of the Constraints differ from server to server.
Again: Microsoft's SQL is so incredible primitive that there is no universal way to delete a Constraint without knowing its name.
Commands like the following are not accepted by Microsoft SQL server:
ALTER TABLE [TableName] DROP UNIQUE (ColumnName) <br />ALTER TABLE [TableName] DROP PRIMARY KEY
Using the Developer Comment
The developer comments for procedures, functions, views and triggers are ONLY for internal use for you and your colleagues!
They will neither be written into the compound script nor stored on the server.
Before hitting the "Build Script" button, you should re-order the Filelist in the main window by drag and drop. The compound script will be built in the same order as you see the entries in the Filelist.
If the compound script creates a new procedure which depends on a new function you must FIRST create the function, THEN the procedure to avoid errors on the SQL Server of your clients. Use the developer comment field not to forget this:
The developer comments are stored in the local files *.Proc, *.View, *.Func, *.Trig.
With the next time you check-in int Subversion / CVS the comments will be available for your colleagues.
Database Search
SqlBuilder can search directly in all databases for a text to be contained in a procedure, function, table column etc... The results are displayed as a list of the found system objects with their content beside. You open the Database search from the toolbar in the main window.
Database Backup
It is recommended to use the button "Database Backup" (in the toolbar of the main window) at least once a week and check in the entire folder DatabaseBackup into CVS/subversion to backup your work on procedures etc...
Even if your SQL server has a backup system and you fully trust it, the advantage is that you can compare the procedures, functions, views etc. with older versions by using the built-in compare functions of CVS / Subversion (or another compare tool like Araxis Merge) and you can verify which procedures have changed and what the differences are to older releases.
The files in the backup folder have the date of their creation on the SQL server. Sadly the date of the last modification is not stored on Microsoft SQL Server.
Details
The SQL Parser
The SQL parser is the heart of the application and the most complicated part. Writing a parser for HTML code or for C++ code is extremely simple in comparison to an SQL parser which is a challenge.
While other programming languages have fixed syntax rules (e.g. in HTML every tag starts with <TAG> and ends with </TAG> or in C++, every command has the form function(argument, argument); and every line ends with a semicolon), SQL is a "language" without rules.
- There is no way to know where a command ends. The most complicated command
SELECT may have a WHERE clause or not. It may have a JOIN clause or not, etc. It simply ends where no more clauses follow. - Linebreaks may appear at any place or may be COMPLETELY missing.
- Parenthesis can optionally appear at any place or not.
- Some commands (e.g.
LEFT) may be a keyword (in JOIN LEFT) or a function (in left(string, count)).
Finally, SQL is not a programming "language" at all. And this may be abused by inexperienced programmers to produce code which seems to come directly out of hell. The parser of SqlBuilder is capable of cleaning up any ugly SQL code.
It works in 5 steps:
- The first parser parses the plain SQL text and searches for commands, keywords, functions, data types, operators, strings, numbers, comments etc. and for each of them creates one instance of
ParseItem which is stored in a double linked chain. - The second parser parses all parenthesis and breaks them up onto multiple lines if they have complex content.
- The third parser recursively parses some complex commands like
SELECT, CASE, CREATE, BEGIN, END,.... - The fourth parser parses and corrects all linebreaks
- The last parser fills the
RtfHtmlBuilder (see below) with the parsed data to create an RTF document, HTML code or plain text from the parsed data.
Only the first step works with plain text. The following steps work with objects and this makes the parsing very fast so it can happen while entering text. If you enter a new letter while the previous parsing did not yet finish, the previous parsing is cancelled and parsing is started anew. All this runs in the background invisible for the user!
The Class "Defaults"
SqlBuilder has a class with the name Defaults. Here you can define YOUR preferred default settings for the entire program like the colours for the parser, the timeout or the default SQL server if not yet specified by the user.....
Special Controls
SqlBuilder has some special control classes which you can copy and use in other C# applications.
RichTextBoxEx
This class is derived from RichTextBox and extends it with more functionality:
- A "Search and Replace" dialog
- A "Goto Line" dialog
- A display of the current line and character position in a status bar
- Advanced scrolling functionality
- Flicker-free replacing of the entire RTF content in the control maintaining the cursor position
- Undo Buffer
- Ultra fast RTF creation with
RtfHtmlBuilder
RtfHtmlBuilder
If you ever used Microsoft's RichTextBox control and tried to display coloured text, you already know that:
richTextBox.SelectionColor = Color.Red;
richTextBox.SelectionIndent = 5;
richTextBox.AppendText("Text");
....
is EXTREMELY slow. It is so incredibly slow that for the display of a text of 50 KB, the user has to wait 20 seconds!
RtfHtmlBuilder is an ultra-fast RTF, HTML and PlainText creator class.
rtfBuilder.SelectionColor = Color.Red;
rtfBuilder.SelectionIndent = 5;
rtfBuilder.AppendText("Text");
.....
string s_Rtf = rtfBuilder.BuildRtf(new Font("Microsoft Sans Serif", 17));
richTextBoxEx.ReplaceRtf(s_Rtf);
ListViewEx
This class is derived from ListView and allows drag and drop of the items in the view to change their order.
DataGridEx
This class paints the datagrid cells in different colors and has some more features. It has five workarounds built in for really ugly Microsoft bugs in this control. The DataGrid is by far the most buggy control in .NET framework!
PasswordTextBox
This class allows input of passwords which cannot be spied out with an API spy or a .NET spy. The password is stored internally as encrypted string.
The textbox shows the password as plain text while you are typing it, otherwise it displays 12 stars no matter how long the password is.
P.S. On my homepage, you can download an SQL book for beginners in CHM format (German) and much more utilities.
