Writing SQL
-
fix-SQL Editor limits the use of dialogs and any other obstructions to the natural flow of a developer writing code. It will provide all the meta data and case corrections for objects you use in the code and it tries to support whatever works best for you. When you're adding a column to the query you can use AutoComplete or dble click columns from tables in the Tables and Aliases list or select multiple columns from the list with the check box or just type a column name. This flexability provides the best support for you writing code faster.
Before you begin, select the output type for Parameters. If you're writing ADO.net code and you're not using a custom helper class you will select the appropriate ADO Sql data Object from the list. If you are either not writing .net data objects or you're using a helper class you will probably want to select the SqlParameter Array option. When you use ADO data objects it's usefull to include them in your query so the name of the object can be incorporated into the resulting Parameters. For example, consider a SqlDataAdpter you dimensioned as aDapt so you might have 'Adapt.SelectCommand=New SqlCommand(...' in the query. When the parameters are assembled you will see aDapt.SelectCommand.Parameters.Add(...... If you don't include the Data Object in the code fix-SQL Editor will create one for you.
Autocomplete for all Schema, Tables, Columns and meta data is available at the cursor so you can write complex SQL with very few key strokes. All of the Schema information is loaded in the application. Importantly, all UDT's registered in the database are also stored in the application so parameters using UDT's are correctly handled. Any time you auto complete the word JOIN fix-SQL Editor will display a list of the relationships configured for this table. When the join is added to a query the joined table is now available in Auto complete menus. When you want referenced joined tables to use an aliase in the join just add it to the Tables and Aliases list first. When fix-SQL Editor sees that you are referencing this table in the join the aliase will be used instead of the table name. You could also add an aliase after the join is in place in the query, fisSQL will pick it up.
-
When your'e writing SELECT Queries the application can't know the table name for the query until you write FROM and you will want to have access to table meta data before this so the solution in fix-SQL Editor is simple. You just add the source tables and their aliases you want to reference columns from using the Tables and Aliases textbox. The tables are autocomplete so start typing and Auto complete with TAB for no aliase or SPACE then type the aliase don't use AS for the aliase in this textbox, you can use this syntax in the SQL if you want. Click + sign to add to the treeview (or press Enter). If you expand the table you will see all columns for the table and their datatype and size. Now you're ready to start writing your SQL query. When you reset for a new query these tables remain so when you're writing several queries using the same tables you can re use these. If the table isn't used in a query it doesn't matter and the result will not be affected. When you add additional tables to your query you don't need to add them to the Tables and Aliases list, fix-SQL Editor will keep track of the tables in use and adjust the autocomplete menu's to reflect any changes. The Load button on the Tables and Aliases list is very usefull, any time you click this button all of the tables and aliases in the query will be added to the list so you can see the tables columns and meta data. Saves a lot of time when you're editing existing SQL just paste the query in the editor and fix-SQL Editor will extract this data and display it in Tables and Aliases.
-
Start your query and you will see that autocomplete is extensively used in the proceess. Start typing SELECT and autocomplete will appear. When you're selecting an autocompleted word you use whatever you need to follow the word. That is, if you typed SEL and was offered SELECT you would select it with the space bar so select will be followed by a space. For Columns selected you may want a comma so select the offered word with a comma. Same for a word that will be followed by = you use = to select the word. The program will take care of formatting and spacing. Now, consider writing a SELECT query, you have entered the table used and so now as you write the SQL you have several ways to select columns. Start typing and autocomplete will be looking for columns from the tables you entered earlier. When you have a good idea about what your SQL will be accessing you can write extremely fast code using autocomplete with mimimal keystrokes and high accuracy. In addition to this you can use the table name, a schema and table name or just dble click a column from the Tables and Aliases list and it will appear at the cursor. You can also select multiple columns with the check mark from mutliple tables and then add them to the query with a right click menu. If Tables have aliases they are included in the added columns. You may also just want to type the column without reference to autocomplete and the program will check the case and change as needed. ie if you type mycolumn and the column name is actually MyColumn the program will correct this so as long as all your database objects are mixed case you immediately see if an entered value is correct. A value which fix-SQL Editor can't recognise will be marked with an underscore.
Don't worry about using string concatenation in the query this is mostly handled by fix-SQL Editor and you save a lot of time simply typing the query. Values require you to indicate a literal value by enclosing the value with single quotes otherwise fix-SQL Editor will assume a variable. When you're done hit F5 or the RUN button and your query will be written out in the results pane as a fully typed (Type Safe) parameterised query. fix-SQL Editor is will concentrate on creating compliant SQL it won't write the .net code required to create objects and open connections etc and a SQL Parameter Array is assumed to be added to an appropriate command object by a Helper Class. The Sql text and parameters are always complete and ready for use.