Browse DevX
Sign up for e-mail newsletters from DevX


Building a Stored Procedure Generator : Page 2

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

Retrieve the Column Data
After defining the basic structure of the stored procedures that your stored procedure generator should generate, it's time to write some code.

Begin by creating a new solution. Add a Windows Forms project to the solution. Update the form for the desired user interface for your generator. Figure 1 shows an example interface.

Figure 1: This user interface allows the user to define the name of the table for which the stored procedure is to be generated and then click on a button to select the type of Retrieve stored procedure to generate.
Because the stored procedure generator needs to obtain the name of each column in the table, it needs to contain code to access the database. The easiest way to write this code is to not write this code at all! Instead, use the pre-existing and pre-tested Microsoft Data Access Blocks that are part of the Enterprise Library (www.microsoft.com/practices). Simply download and install the Data Access Application Block (I used the 2.0 version, but you can pick any version). Then select to add an existing project to your solution and add the Data Access Application Block project.

Author's Note: You can use the Microsoft Data Access Blocks (part of the Enterprise Library) to retrieve data for any application. See www.microsoft.com/practices for more information. If you select the 2.0 version, you will have the option to include a VB or C# project into your solution.

The next step is to define a stored procedure generator class (SPGenerator) to hold the code that generates the stored procedure. You can add this class as a separate component in your solution, or just add the class to the Windows Forms project that you already created.

Before you begin to create the code in this SPGenerator class, create a reference to the Data Access Block. Then add a directive for this block and the other .NET Framework components that the class will need as follows.

In VB:

Imports Microsoft.ApplicationBlocks.Data Imports System.Configuration

In C#:

using System; using System.Data; using Microsoft.ApplicationBlocks.Data; using System.Configuration;

Notice that the C# version has more directives. This is because the System and System.Data namespaces are already defined for the VB project. (See Project Properties, Common Properties, Imports)

The Data Access Application blocks methods require a connection string. One of the most common ways to manage a connection string is to define the string in the application configuration file (App.config) as follows.

<?xml version="1.0" encoding="utf-8" ?> <configuration> <appSettings> <add key="CONNECTION" value="data source= (local);initial catalog=Northwind; user ID=sa;password=" /> </appSettings> </configuration>

Author's Note: For improved security in your application, you should store the user ID and password as encrypted values or store them somewhere else. They are included here to make it easier for you to access the sample Northwind database.

Define a property in the SPGenerator class that retrieves the connection string from the configuration file. This ConnectionString property uses the ConfigurationSettings class to retrieve the connection string.

In VB:

Private ReadOnly Property ConnectionString() As String Get Dim sConnectionString As String sConnectionString = _ ConfigurationSettings.AppSettings( _ "CONNECTION") Return sConnectionString End Get End Property

In C#:

private string ConnectionString { get { string sConnectionString; sConnectionString = ConfigurationSettings.AppSettings ["CONNECTION"]; return sConnectionString; } }

After defining the connection string, a method in the SPGenerator class can call a method in the Data Access Blocks to retrieve the data needed to generate the stored procedure.

In VB:

Private Function Retrieve(ByVal sTable As String) _ As DataSet Dim ds As DataSet Dim sWhere As String Try sWhere = sTable.TrimEnd("s"c) & "ID = ''" ds=SqlHelper.ExecuteDataset(ConnectionString, _ CommandType.Text, "SELECT * FROM " & _ sTable & " WHERE " & sWhere) Catch ex As Exception Throw New ArgumentNullException("The table: " _ & sTable & " either does not exist " & _ "or cannot be accessed.") End Try Return ds End Function

In C#:

private DataSet Retrieve(string sTable) { DataSet ds; string sWhere; try { char cRemove = 's'; sWhere = sTable.TrimEnd(cRemove) + "ID = ''"; ds = SqlHelper.ExecuteDataset(ConnectionString, CommandType.Text, "SELECT * FROM " + sTable + " WHERE " + sWhere); } catch { throw new ArgumentNullException("The table: " + sTable + " either does not exist or " + "cannot be accessed."); } return ds; }

The Retrieve method retrieves a DataSet with all the columns and no rows. It does not need any specific data from the table, only the column definitions. To retrieve the columns but no rows, the SELECT statement uses a WHERE clause with a blank primary key, assuming it is not valid to have a row with a blank primary key.

Author's Note: If you use a numeric key, the WHERE clause could use a primary key value of 0, assuming 0 is not a valid primary key value.

The code uses the TrimEnd method of the String class to trim any "s" character from the end of the table name and then concatenates "ID" to the table name to define the primary key name. This makes the assumption that the primary key has the singular form of the table name plus the string "ID". In the Northwind example, the table name is Customers and the primary key is CustomerID, the table name is Employees and the primary key is EmployeeID, and so on.

Most database naming standards define the table name as singular and then the primary key as the exact table name with "ID" appended to it. If this is the style that you use, you can remove the TrimEnd method call. If your naming conventions differ, you may need to modify this code further.

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