If it's online, ITSBITS®


ITSBITS Home
Catapult Garage Home
Getting Started
Main Workshop
Personal/Private
Emergency Workshop
Tech Support
Alarm/Timer Tool
Image Tool
Icon Extraction Tool
Color Picker

Make Project
View Files In A Project
Mass Changes In A Project
Copy/Move Project
Change Images Format In Project

Build A Budget
Car Cost
Fixed-RateAmortization
Save vs. Borrow To Buy

Library Management Tool
Personal Inventory

Folder Management
Folder Permissions
View Files In A Folder

Contacts Manager
Build/Test Connection String
Code To Code(VB)
File Dump
Font/Test Viewer
SQL DB Code Builder
VB Code Gen w/Stepper

Configure Sharing And Backup (Multiple Shops and Tools)
List Builder Forms (Multiple Shops and Tools)

ITSBITS Catapult Garage Software
SQL Code Builder Tool

Purpose Of The Tool

This tool is for use by VB.net developers who are utilizing a Microsoft® SQL Data Base. Using this tool, you can generate stored procedures for the most common update and selection problems--stored procedures to use when processing data from a SQL DB table. Also, using this tool, you can generate VB code for a class that uses each of the stored procedures you've generated--you can then use this class from any other program you've written. Finally, you can download a free template that provides a GUI with up to 100 different label/textbox combinations and can be used with the class that uses the stored procedures. In short, in just a few minutes you can generate all the code you need to do updates and selections in your SQL DB table.

Caveat: Binary Large Objects (BLOBS) are not handled directly from within this tool because of the huge variety of possibilities that entails.

-- Back to top of this page --

Start The Tool

On the opening screen or "WELCOME" tab, go to the "Tools" menu, then select the option to "SQL Code Builder." When this configuration option is selected, you will see an image something like the one shown below:

ITSBITS Catapult Garage SQL DB Code Builder tool

-- Back to top of this page --

Configure The Tool

ITSBITS Catapult Garage Software SQL DB Code Generator Tool's Configuration Menu To configure the tool, click on the "Configure" menu item at the top of the form. This will reveal a menu something like the one at right. As usual, we'll discuss each of the menu items in turn, moving down the list. Notice that all of these configuration options function just as those in the Getting Started section .

The first menu item, "Local Data Folder To Use," will allow you to change the folder where your configuration and other data for this tool will be stored. WE DO NOT RECOMMEND THAT YOU CHANGE THIS FOLDER LOCATION unless you are an IT professional who has a valid reason for making the change.

The second menu item, "Program .cfg file," will let you specify the complete file path for your configuration file. This will be a file in your local data folder path (see just above).

The third option lets you specify where your "Work folder" will be. The tool will need a folder where you have write permission to store some of the code files it generates. Caution: You must have write permission on the folder you designate as your work folder or it simply can't work.

The next option lets you specify a "backup folder " where your tool's configuration and data can be stored. You must have write permission on this folder or it won't work. Ideally, the backup folder should be on a completely different hard disk drive so that if your HDD coughs, kicks its heels to the sky, and goes to dinosaur land you won't lose your data. We recommend that you configure and use a backup on a different hard disk drive to avoid such a lost.

Finally, the last option lets you specify the path to your Microsoft Internet Explorer® browser.

-- Back to top of this page --

ITSBITS Catapult Garage Software SQL Code Generator Tool Schema view Using The Tool

As you look at the tool, you'll see that the left side has a number of buttons labelled one through 5. These are the steps to start actually using the tool (after it has been configured as outlined above.)

  1. The first thing you'll do in using this tool is to use the embedded Build/Test Connection String Tool to build and test your connection string.

  2. Once you're certain it works, step two is to actually connect to the named database.

  3. Next, you'll specify the name of the table you're going to work with inside the specified database

  4. Now you'll let the tool get the schema for the named Db table. (Once this is done, you can copy the schema to the clipboard for use in your programming documentation if you'd like.

  5. Disconnect from the database so it doesn't give you conflicts and so you're not holding state on the DB unnecessarily. (At this point, 2 new buttons will appear. These let you copy the DB table's schema, either full or just the part used to generate your VB code, to the system clipboard. In addition, the menu option to "GenUpdateCode" will be enabled for use.)

ITSBITS Catapult Garage Software SQL Code Generator Tool Menu to generate VB update code At this point, the menu option to "GenUpdateCode" will be enabled. If you click this option now, you'll see a menu that looks something like the one at right.


The first option on this menu will produce a set of stored procedure files in your configured work-folder. Click the option, then move to the work folder. In our minimalist-case sample, we used a Db named "churches" and a table named "Locales" and the following stored procedure files were created:


Churches.LocalesDeleteRecordForAPK.txt A stored procedure to delete a record based upon its primary key
Churches.LocalesInsertNewRecord.txt A stored procedure to add a new record to your Db table
Churches.LocalesSelectFullTable.txt A stored procedure to select all records (and their field(s)) in the DB table
Churches.LocalesSelectPKs.txt A stored procedure to select the all primary-key field entries from a given DB table. (Load them into a drop-down or list box and let the user select the PK they're after.)
Churches.LocalesSelectRecordForAPK.txt A stored procedure to select the record (all fields) from the Db table for a given Primary Key
Churches.LocalesUpdateExistingRecord.txt A stored procedure to update (change) an existing record based upon its Primary Key field as identification

Just as a sample, the minimalist sample file to "InsertNewRecord" that we've generated is shown here. (Db="Churches" Table="Locales" PK="Locale")

USE [Church]
GO
/**** Object: StoredProcedure [dbo].[LocalesInsertNewRecord] *****/
/**** Scripted 12/3/2011 by Catapult Software from ITSBITS.com ****/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[LocalesInsertNewRecord]
(
@New_Locale char(15)
)
AS
SET NOCOUNT OFF;
INSERT INTO Locales
(
Locale
)
VALUES
(
@New_Locale
);
SELECT * FROM Locales WHERE (@New_Locale = Locale) ORDER BY Locale;

-- Back to top of this page --

At this point, you'll need to select the stored-proc. file(s) you need to use and copy/paste their contents into the Microsoft® SQL Management program as you create new stored procedures. If you're going to use the tool to generate a class that will access these stored procedures, be sure to use these same names in the SQL Management program for your stored procedures that we used. After you paste the stored-proc. code into the Management program to replace the generic blank code it generates for you, be sure to execute the code. You'll notice that each of our stored-proc. cods files includes a "Create Procedure" line (see example above): the actual stored procedure file will not be created inside of your SQL Db until and unless you execute it. (Later, if you decide to change it in some way, replace "Create" with "Modify" and execute it again to recreate the actual stored procedure file inside of the SQL Db.)



ITSBITS Catapult Garage Software SQL Code Generator VB Class Output Query


On the menu, our next option is to "Gen VB Class for Updates Using Stored Procedures." When you select this menu option, you'll be asked where to put the file you're about to generate. This will look something like the image at right. You'll probably want to find where your VB project files are being stored and use the same folder so you can easily incorporate the class generated by our tool right into your VB project using their menu option to add an existing class to your project.

Once you have the path entered here as you want it to be, click the "OK" button to generate your output.

NOTE: The top two lines of the generated class's file read as follows:

'(c)2011 by ITSBITS. All rights under all copyright laws in all jurisdictions reserved. You are free to use
'and modify this programming so long as this copyright notice remains at the top of the resulting program.

Your Catapult Garage Software user license requires that all copyright notices on code we generate remain intact and at the top of the generated file(s) as is noted here. You may use and modify the code generated by this tool as much as you want, but you must leave the copyright notice in the file(s) you make from this code.

-- Back to top of this page --

The final option on the menu lets you "Gen VB Template-100 Table-update program." You'll need to download our template file from the Catapult Garage Software website page and use it in your project as your main GUI for updating the DB table. Just as with the option shown just above, you'll be asked for the file path to use for your output and we again recommend that you place it in the same folder where your VB project code is being placed. Then you can easily add the existing file to your code set.

Once you've specified the path for your output, click the "OK" button to continue. The file will be generated for you. In most cases, you'll find that you can use this file pretty much as it is, though you'll need to delete unused labells and text boxes from the template and resize the labells and text boxes you are going to use.

You'll also find that you can copy/paste "stuff" from the template and from the generated code into your own code and quickly have the ability to update and/or select records from the Db table.

NOTE: The top two lines of the generated class's file read as follows:

'(c)2011 by ITSBITS. All rights under all copyright laws in all jurisdictions reserved. You are free to use
'and modify this programming so long as this copyright notice remains at the top of the resulting program.

Your Catapult Garage Software user license requires that all copyright notices on code we generate remain intact and at the top of the generated file(s) as is noted here. You may use and modify the code generated by this tool as much as you want, but you must leave the copyright notice in the file(s) you make from this code.

-- Back to top of this page --















ITSBITS® is registered in the United States Patent and Trade mark office. Microsoft, Windows, Excel, Outlook, Exchange, and Word are registered trademarks of the Microsoft Corporation.
©2012 by ITSBITS, Incorporated...This applies to all information, graphics, and software on this site. All rights under all applicable copyright laws in all jurisdictions are reserved.