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.
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" ?>
<add key="CONNECTION" value="data source=
user ID=sa;password=" />
|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.
Private ReadOnly Property ConnectionString() As String
Dim sConnectionString As String
sConnectionString = _
private string ConnectionString
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.
Private Function Retrieve(ByVal sTable As String) _
Dim ds As DataSet
Dim sWhere As String
sWhere = sTable.TrimEnd("s"c) & "ID = ''"
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.")
private DataSet Retrieve(string sTable)
char cRemove = 's';
sWhere = sTable.TrimEnd(cRemove) + "ID = ''";
ds = SqlHelper.ExecuteDataset(ConnectionString,
CommandType.Text, "SELECT * FROM " +
sTable + " WHERE " + sWhere);
throw new ArgumentNullException("The table: " +
sTable + " either does not exist or " +
"cannot be accessed.");
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.