Build and use SQL / PL stored procedure with Visual Studio 2005

In this article, we will look at how to create a stored procedure using the SQL / PL Stored Procedure designer and apply them in a simple WinForm application.

Attention:

To perform the following steps in this article, you must first create a SAMPLE database (template) by entering the db2sampl command from the operating system's Command Promp command window. You must also add a connection to this database in the Server Explorer virtual table.

Develop SQL / PL procedures using designers

To build the SQL / PL stored procedure, select the employees of a certain department based on the designer tool DB2 9 SQL / PL Stored Procedure in Visual Studio 2005, follow these steps:

1, Expand the SAMPLE database button in Server Explorer, select the Procedures folder, then Add New SQL / PL Procedure with Designer .

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 1

2, Open the Procedure designer dialog box. Call the SELECTSTAFFBYDEPT procedure in the Procedure identification area of ​​this designer. You can choose to describe the schema name below or not. This routine will be created with a comment describing it.

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 2

You can use the settings of the Debug model, or set the Allow mode in the 'Debug mode' box if you want to use the IDE's built-in error handling functions for this routine.

3, In the Procedure Parameters window, add a parameter to the routine by clicking on the Add parameter () button.

4, Click the Name field and change the name for this parameter to DEPTNUME .

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 3

5, Click the Import Parameters button () to know the data type of the parameter used to query employee information in the STAFF table, based on the DEPT column.

6, Import window opens. You can use the Schema name to filter the tables in the window, find the STAFF table, select the DEPT column, move it to the selected list by clicking Add column to selected list () and clicking OK .

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 4

The IBM add-ins for Visual Studio 2005 provide you with a number of unique components, improving the ability of rapid application development (RAD) in Visual Studio 2005 IDE. The ability to know the parameter data type is one of them. Performing this function, you will not get the error in selecting the data type for the input parameter in the stored procedure and minimizing the possibility of coding error.

There are a number of other unique DB2 9 RAD components that are also included in Visual Studio 2005, such as the column definition function in IntelliSense table creation, linked to DB2 schema below. Like the IBM Output Message Pane, describe in detail the DB2 data server activity and error messages .

The Procedure Parameters of this designer are as follows:

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 5

7, Delete the DEPTNUMB parameter by clicking on it and clicking the Delete Parameter () button. Rename the DEPT parameter to DEPTNUMB .

Obviously, you can choose the description or detect the exact parameter immediately. The reason why we want to show you these steps is to illustrate the ability of multi-component designers.

8, Collapse the Procedure Identification and Procedure Parameters sections by clicking on the () icon. Now, the designer takes the form:

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 6

9, Replace

 select ROUTINENAME, ROUTINESCHEMA from SYSIBM.SYSROUTINES; 

equal

 select * from STAFF where dept = deptnumb; 

Note : When you enter this new command, you can enable IntelliSense to connect to the schema of the data server by giving the schema name before the STAFF table name, and giving the schema name and table names before the DEPT column as shown below. below:

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 7

10, Build this stored procedure by pressing Ctrl + S , then Yes .

There are many other components in the SQL / PL Stored Procedure designer, but beyond the scope of this article. You should build a number of other routines if you want to learn more about the capabilities of this amazing and unique tool.

11, Refresh the virtual table of the Procedures folder in Server Explorer by clicking on this folder and selecting Refresh if necessary.

12, Check the stored procedure with the value 20 for the DEPTNUMB parameter as follows:

a. Right-click on the SELECTSTAFFBYDEPT procedure, select Run . The Run Options window opens.

b. Uncheck the Null box .

c. Click Value and enter 20 in the box.

d. Press Run .

These steps are illustrated below:

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 8

Note that, when running the procedure using the IBM add-in in Visual Studio 2005, you can use the options for pre-run or post-run files (additional runs). For example, you might want to use a pre-run script to create and populate the table, a post-run script to delete the table. The Commit command after running the script will check the safety of routine code in Visual Source . In addition, the next time the stored procedure will record the parameter values ​​entered to make it easier to run the routine.

Results are returned in the DB2 data grid and similar to the following:

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 9

The DB2 data grid in Visual Studio 2005 is a feature rich feature with many different activities. Such as data export, import (including XML format), projects, limits, row level filtering, sorting .

Use the SQL / PL stored procedure in the application

We have the DB2 SQL / PL procedure, now it's up to the task to merge it into your application.

Note : In this article, I will show you how to build a stored procedure on a DB2 data server running in Linux, UNIX or Windows operating systems. But you can easily extend this capability to the DB2 treen i5 / OS or z / OS database, using DB2 Connect. The process is quite similar.

1, Create a new Visual Basic application called CallMySPProject: go to File > New Project > Windows Application , select Visual Basic (can be located under Other Languages ). Type the project name in the Name field and click OK .

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 10

Note : Unlike other application programs, this application program is built in Visual Basic .NET.

2, Make sure the Add Data Source view is shown in the IDE. If not, press Shift + Alt + Delete or select Data > Show Data Sources from the menu bar.

3, Click to select Add New Data Source in the Data Sources window , select Database and click Next.

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 11

4, Select the SAMPLE database from the dropdown list ' Which data connection should your application use to connect to the database? '(What type of data connection will the application use to connect to the database?).

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 12

5, Click the ' Yes, include sensitive data in the connection string ' box (Use both sensitive data in the string) and click Next (Suppose you are working on a separate test machine and notice that Visual Studio 2005 collapses the Connection String field automatically).

6, Accept the default settings on the next page. Click Next .

7, Expand the Stored Procedure node, select the SELECTSTAFFBYDEPT stored procedure and click Finish .

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 13

Note : This step is slightly different from some of the previously introduced articles. We are creating a data set from the stored procedure instead of directly from a table.

The designer palette is now similar to the image below. (You may need to extend SAMPLEDataSet manually).

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 14

8, Drag the stored procedure SELECTSTAFFBYDEPT from Data Sources and drop down your WinForm. It will automatically add a data grid and tool sections similar to the following:

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 15

You can see that two different pieces of tools are automatically created after performing this function.

9, Click on the pull-down menu to the right of the Fill tool, select Label .

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 16

Note : If there is no pull-down menu next to Fill , click on Fill , the menu will appear.

The toolStripLabell component (in the IDE may be named a little differently) appears:

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 17

10, Right-click on the toolStripLabel1 object , select Properties, then change from Text to Department Number.

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 18

11, Create a field that describes the input parameter for the stored procedure by clicking the Fill drop-down box and selecting the Textbox .

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 19

12, Double-click Fill , you will see all the generated code:

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 20

You can see, the variable DEPTNUMB is automatically dimensioned (DIM) in Visual Studio 2005.

13, Change the following code:

 Dim DEPTNUMB As Object = New Object 

city

 Dim DEPTNUMB As Object = ToolstripTextBox1.Text 

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 21

14, Press F5 to build the application. Application program opens.

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 22

15, Enter the valid department code for the STAFF table in the Department Number field and press Fill. WinForm will put the parameter value in the Department Number into the stored procedure SELECTSTAFFBYDEPT and populate the data grid.

You can use any valid parameter for the department code in the STAFF table. If given to the application some is not in the STAFF table, it will return an empty set.

The illustration below gives room code of 20 to the stored procedure SELECTSTAFFBYDEPT:

Build and use SQL / PL stored procedure with Visual Studio 2005 Picture 23

Summary

In this article we have shown you how to use the SQL / PL designer to create a stored procedure, in which an input parameter is used to represent the DEPT column in the STAFF table. We have looked at some advanced components of SQL / PL. And the designer executes the procedure with IntelliSense that binds the database schema and the ability to export / import input parameters from the table itself (both reduce setup time and help eliminate malicious errors). Finally, the operation of the stored procedure in the application program is used. Hopefully, readers have obtained all the basic information about building and using the SQL / PL procedure in Visual Studio 2005, as a basis for further understanding the deeper issues in advanced documents later .