Browse DevX
Sign up for e-mail newsletters from DevX


Building a Stored Procedure Generator : Page 4

Creating basic data access stored procedures is time consuming and boring work. Relieve the tedium by writing code that writes these stored procedures for you.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

In VB:

Private m_oSPGen As SPGenerator Private Sub StartupWin_Load(ByVal sender _ As Object, ByVal e As System.EventArgs) _ Handles MyBase.Load m_oSPGen = New SPGenerator End Sub

In C#:

private SPGenerator m_oSPGen; private void StartupWin_Load(object sender, System.EventArgs e) { 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.

In VB:

Private Sub btnRetrieveByID_Click(ByVal sender As_ System.Object, ByVal e As System.EventArgs) _ Handles btnRetrieveByID.Click Try txtSPScript.Text = _ m_oSPGen.BuildRetrieveByIDSP(txtTableName.Text) Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub

In C#:

private void btnRetrieveByID_Click(object sender, System.EventArgs e) { try { txtSPScript.Text = m_oSPGen.BuildRetrieveByIDSP(txtTableName.Text); } catch (Exception ex) { MessageBox.Show(ex.Message); } }

The code for the "Copy to Clipboard" button uses the Clipboard class to copy the information from the script to the clipboard.

In VB:

Private Sub btnCopy_Click(ByVal sender As _ System.Object, ByVal e As System.EventArgs) _ Handles btnCopy.Click Clipboard.SetDataObject(txtSPScript.Text) End Sub

In C#:

private void btnCopy_Click(object sender, System.EventArgs e) { Clipboard.SetDataObject(txtSPScript.Text); }

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:

  1. 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.
  2. Select Database Projects as the project type from the Other Projects node in the Add New Project dialog box.
  3. Define a project name for the Database project, verify the location, and click OK.
  4. 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.

  5. 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 solution—and 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.

Deborah Kurata is cofounder of InStep Technologies Inc., a professional consulting firm that focuses on turning your business vision into reality using Microsoft .NET technologies. She has over 15 years of experience in architecting, designing, and developing successful .NET applications. Deborah is the author of several books, including "Doing Objects in Visual Basic 6.0" (SAMS) and "Doing Web Development: Client-Side Techniques" (APress). She is on the INETA Speaker's Bureau and is a well-known speaker at technical conferences.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date