Finish the User Interface
Now that you've generated the script, you need to output it to somewhere. You could create an actual file containing the stored procedure script. Or you could choose to display the script and allow the user to decide what to do with it. This stored procedure generator uses the second option, as shown in Figure 1
To finish the user interface of the stored procedure generator, declare a module-level variable and create an instance of the SPGenerator class.
Private m_oSPGen As SPGenerator
Private Sub StartupWin_Load(ByVal sender _
As Object, ByVal e As System.EventArgs) _
m_oSPGen = New SPGenerator
private SPGenerator m_oSPGen;
private void StartupWin_Load(object sender,
m_oSPGen = new SPGenerator();
Add event procedures for the two stored procedure buttons. These procedures call the appropriate method and display the result in the multi-line textbox. You can see the event procedure that calls BuildRetrieveByIDSP
below. The event procedure that calls BuildRetrieveListSP
(not shown) is similar.
Private Sub btnRetrieveByID_Click(ByVal sender As_
System.Object, ByVal e As System.EventArgs) _
txtSPScript.Text = _
Catch ex As Exception
private void btnRetrieveByID_Click(object sender,
catch (Exception ex)
The code for the "Copy to Clipboard" button uses the Clipboard class to copy the information from the script to the clipboard.
Private Sub btnCopy_Click(ByVal sender As _
System.Object, ByVal e As System.EventArgs) _
private void btnCopy_Click(object sender,
That's it. You can run the project, type in a table name (such as Customers
) and click on one of the buttons to generate the script. Then click on the "Copy to Clipboard" button to copy the script to the clipboard.
But, now that you have the script on the Clipboard, what do you do with it? The best answer is to paste it into a Database project script.
|A Database project allows you to create a project in your solution that manages your stored procedure scripts and other database script files.|
A Database project lets you create a project in your solution that manages your stored procedure scripts and other database script files.
Add a Database project to your solution as follows:
- Right-click the solution in the Solution Explorer and select Add then choose New Project from the context menu, or from the File menu select Add Project then select New Project. You'll see the Add New Project dialog box.
- Select Database Projects as the project type from the Other Projects node in the Add New Project dialog box.
- Define a project name for the Database project, verify the location, and click OK.
|Author's Note: CAUTION: The location of the database project cannot be changed, so ensure that it is correct before clicking OK in the Add New Project dialog box.
- The Add Database Reference dialog box is displayed. This dialog box lists all the database connections defined in the Server Explorer Data Connections node. Select the database to be used by the Database project or add a new database connection. For this example, add a new database connection to the Northwind database. Then click OK.
The new Database project is then added to your solution and appears in the Solution Explorer with all your other projects. Under the Database project is a set of folders for managing your scripts, queries, and database references.
Create new stored procedures in the Create Scripts folder of the Database project by right-clicking "Create Scripts" and selecting "Add SQL Script," which displays the Add New Item dialog box. To create a new stored procedure, select "Stored Procedure Script." You should now see a template for a stored procedure script displayed as a tab in the Visual Studio editor.
Select the location in the script where you wish to paste the stored procedure script text that you copied to the clipboard with the stored procedure generator, and paste it in.
Test the resulting stored procedure script by right-clicking it and choosing "Run Selection." Apply it to the database by right-clicking in the stored procedure script file and choosing "Run."
Repeat this process for each table and type of stored procedure that you wish to generate.
The Database project is a convenient place to manage your database script files. They are all saved as part of the solutionand if the solution is managed by a source code control product, the script files will be under source code control as well.
Developing basic retrieve stored procedures is often a required, but repetitive process and prone to typographical errors. Developing a stored procedure generator to generate these types of stored procedures minimizes the amount of time needed to create and debug your stored procedures.
Now that you have the basics of a stored procedure generator, you can enhance it as you need. Modify it to include a standard comment header block. Add code to support generation of insert, update, and delete stored procedures. Add standard concurrency processing. Add code to loop through all your tables and generate all your scripts instead of doing one at a time. Make the stored procedure generator work for you.