OxyGen Code Generator Overview. 4   Table of Contents. 4
Previous Topic: Generating Virtual Views . 4   Next Topic: Generating Stored Procedure Configurations. 4

In this chapter, we will show how to specify the configurations that are used for stored procedures

1. Open OxyGen Code Management Studio.
2. In the Connect to Server dialog specify the Server Name (the default instance of SQL Server often uses the machine name), the Authentication mode, user credentials (if SQL Server Authentication is selected), and then click the Connect button.

Figure 19: Connect to Server dialog

Figure 20: Internal View

The Database Explorer tree control is populated with the databases from the specified server. Accessible databases are depicted by an enabled cylinder () while the databases that are not accessible are depicted by a disabled cylinder ().

Figure 21: Stored Procedure Configuration panel

3. On Database Explorer select a database (AdventureWorks) and a stored procedure (dbo.uspGetManagerEmployees). The Stored Procedure Configuration panel is displayed, as shown in the following screen shot. You will notice that there are two tabs in the main panel.
1. Configuration:  configures the stored procedure
2. SQL Script: shows the source code of the stored procedure
4. Specify a SQL operation type for a given stored procedure.  There are five types ( see “Figure 22: SQL operation types of the Stored Procedure”):
1. Unknown
2. Select
3. Update
4. Delete
5. Insert

Figure 22: SQL operation types of the Stored Procedure

5.       Specify a Stored procedure execution type for a given stored procedure. There are seven types of the Stored Procedure execution (see “Figure 23: Stored Procedure execution types”):

                                 I.            Execute Reader:  the command returns a DataReader that can be used to retrieve multiple rows.

                                II.            Execute Scalar: the command returns only a single data cell (the 1st column of the 1st row). If more data exists, it will be ignored.

                              III.            Execute XML Reader: the command returns Xml. This should be used when the command is known to stream out Xml data. This is a function of the query the command executes (Example, MS SQL Server 'FOR XML' clause being used in the query).

                              IV.            Execute Single Row: the command is known to return only a single data row. If more rows exist, they will be ignored.

                               V.            Execute Non Query:  The command doesn't return data. Functions generated based on this activity will return void.

                              VI.            Execute Non Query With Rows Affected:  the command doesn't return data. Functions generated based on this activity will return a Boolean that indicates if any rows were affected by the call.

                            VII.            Execute Non Query With Row Count: The command doesn't return data. Functions generated based on this activity will return an integer that indicates the number of rows affected by the call.

Figure 23: Stored Procedure execution types

6.       Click on the Containing entity insertion button  to define the containing entity name (Figure 24: Stored Procedure Processing Parent dialog). We can choose between using default entity or we can specify a different one choosing from a list of tables, views and virtual views.               

Figure 24: Stored Procedure Processing Parent dialog

7. Click on the Method name insertion button  to define the name of the method.

Figure 25: Stored Procedure Method Name dialog

8.       Click on the Resultset Configuration insertion button  to define set of results generated by a given stored procedure. Specify the element type (table, view or virtual view), choose a particular element and specify the index of a given result set (Resultset Index).

Figure 26: Stored Procedure Resultset Configuration execution types

OxyGen Code Generator Overview. 4   Table of Contents. 4
Previous Topic: Generating Virtual Views . 4   Next Topic: Generating Stored Procedure Configurations. 4