Tutorial_DPQ_analytical_tool

Open Touryo Tutorial (Dynamic Parameterized Query Analytical Tool edition)

November 27th, 2014

Introduction

Objective of this document

This tutorial describes how to use the Dynamic Parameterized Query Analytical Tool bundled in Open Touryo. By using Dynamic Parameterized Query Analytical Tool, the developers can check the error of dynamic parameterized query.

Scope of this document

Scope of this tutorial is learning how to use the Dynamic Parameterized Query Analytical Tool.

Overview of this document

This tutorial describes, the generation method of dynamic parameterized query using the Dynamic Parameterized Query Analytical Tool of Open Touryo and the confirmation method etc. of execution results, in sequence.

As we are pressed for time, some images are only displayed in Japanese.

Use of copyrights and trademarks of other companies

The company names and product names used in this document are the trademarks or registered trademarks of the respective companies.

License

This document can use Creative commons CC BY 2.1 JP license.

1. Overview of Open Touryo framework

Open Touryo framework is an application framework for .NET. Open Touryo framework targets .NET Framework 4.6 or above and can be used in various applications like C/S (Windows Forms, WPF), Web (ASP.NET) and RIA (Silverlight).

Figure 1-1 shows the class configuration of Open Touryo framework.

Figure 1-1 Class diagram of Open Touryo framework

Open Touryo dynamic parameterized query function can define dynamic SQL with XML format file. Also, Open Touryo provides the Dynamic Parameterized Query Analytical Tool to generate, check syntax, test the dynamic parameterized query. This tutorial shows the usage method of dynamic parameterized query analytical tool, in sequence.

The Dynamic Parameterized Query Analytical Tool is abbreviated to Analytical Tool in this tutorial.

2. Set up the environment

The followings are the prerequisites for this tutorial.

Development environment
- IDE
  - Visual Studio 2015 (Express Edition is also available)
- Application framework
  - Open Touryo Template Base for Visual Studio 2015
Runtime environment
- Runtime
  - .NET Framework 4.6
- DB
  - SQL Server Express 2008 R2
Others
- OS
  - Windows 7
- Programming language
  - C#

Install Visual Studio referring to Microsoft homepage beforehand.

Next, set up Open Touryo Template Base and database.

Click [Download ZIP] button on GitHub and obtain OpenTouryoTemplates.zip. Unzip this zip file and obtain Open Touryo Template Base for Visual Studio 2015.
Set up Open Touryo Template Base and database according to Readme.md in root_VS2015 folder.

3. Exercises

This section describes the usage method of the analytical tool.
This tutorial does not describe the details of the Open Touryo dynamic parameterized query feature. Refer to the Open Touryo user guide (dynamic parameterized query version) as necessary.

3.1 Launch the analytical tool

Open C:\root\programs\C#\Frameworks\DPQuery_Tool\bin\Debug\DPQuery_Tool.exe.
Confirm that the analytical tool is launched.

3.2 Connect to database

Confirm that the Select Data Provider drop down list of the analytical tool is set to SQL Server - sqlClient.

Input the following connection string in the Set the Connection String textbox.

Data Source=localhost\sqlexpress;Initial Catalog=Northwind;Integrated Security=True;

Click Connect button to connect to database.
Confirm that database connection finished successfully, and Connect button get disable, and Execute Query button get enable.

3.3 Create and execute static sql

3.3.1 Execute static sql without criteria

Input the following sql statement in the textbox in the upper part of analytical tool.
```
SELECT * FROM SHIPPERS
```
Click Execute Query.
Click OK in Execute from TextBox dialog.
Confirm that all records of Shippers table are displayed in result dialog.
Click Close to close dialog.

3.3.2 Execute static sql with criteria

To execute query with criteria, edit sql statement in the upper part of analytical tool as follows.
```
SELECT * FROM SHIPPERS WHERE SHIPPERID = 1
```
In the same way to section 3.3.1, click Execute Query to execute query. Confirm that the record, that the value of ShipperId is "1", of Shipper table is only displayed in result dialog.
Click Close to close dialog.

3.3.3 Execute static sql with parameterized criteria

Next, parameterize the sql statement in the upper part of analytical tool as follows. (Analytical tool analyze /*PARAM* p1, Int32, 1 *PARAM*/, and set 1 (Int32) to parameter p1.)
```
SELECT * FROM SHIPPERS WHERE SHIPPERID = @p1
/*PARAM* p1, Int32, 1 *PARAM*/
```
Click Execute Query and confirm that the record, that the value of ShipperId is "1", of Shipper table is only displayed in result dialog.
Click Close to close dialog.

3.4 Create and execute dynamic sql

3.4.1 Convert static sql to dynamic sql

In this section, convert static sql with parameterized criteria, created in section 3.3.3, to dynamic sql according to the method of dynamic parameterized query of Open Touryo.

Clear the textbox in upper part of analytical tool.
Right-click textbox and select TEMPLATE in tag list.
Xml template is inserted in textbox. Edit the query statement as follows.
```
<?xml version="1.0"?>
<ROOT>
    SELECT * FROM SHIPPERS
    <WHERE>
        WHERE
            <IF>SHIPPERID = @p1</IF>
    </WHERE>
    <PARAM>
        p1, Int32, 1
    </PARAM>
</ROOT>
```
Note:
As above, dynamic parameterized query of Open Touryo is xml format. And query statement is described in xml tags.
- Describe the criteria in WHERE tag.
- Use IF tag and ELSE tag when change the criteria according to situation.
- Describe the parameter value in PARAM tag.
For more details, refer Open Touryo user guide (Dynamic parameterized query edition).
Click Execute Query.
Click OK in Execute from TextBox dialog.
Confirm that the record, that the value of ShipperId is "1", of Shipper table is only displayed in result dialog.
Click Close to close dialog.
Click SQL tab to confirm the executed sql.
Click LOG tab to confirm the execution log.
Click Close to close dialog.

3.4.2 Add criteria to where phrase

Edit the sql statement in textbox in upper part of analytical tool to select Shippers table with ShipperId and CompanyName. (As follows, use <DIV/> for the separator of the parameters.)

<?xml version="1.0"?>
<ROOT>
    SELECT * FROM SHIPPERS
    <WHERE>
        WHERE
            <IF>SHIPPERID = @p1</IF>
            <IF>AND COMPANYNAME = @p2</IF>
    </WHERE>
    <PARAM>
        p1, Int32, 1<DIV/>
        p2, String, Speedy Express
    </PARAM>
</ROOT>

Confirm that the record, that the value of ShipperId is "1" and the value of CompanyName is "Speedy Express", of Shipper table is only displayed in result dialog.
Click SQL tab to confirm the executed sql.
Click LOG tab to confirm the execution log.

Next, comment out the criteria for parameter p1 to confirm the behavior when the parameter is not set.

<?xml version="1.0"?>
<ROOT>
    SELECT * FROM SHIPPERS
    <WHERE>
        WHERE
            <IF>SHIPPERID = @p1</IF>
            <IF>AND COMPANYNAME = @p2</IF>
    </WHERE>
    <PARAM>
        <!--p1, Int32, 1<DIV/>-->
        p2, String, Speedy Express
    </PARAM>
</ROOT>

Click Execute Query.
Click SQL tab to confirm that the criteria contains CompanyName only and does not contain ShipperId.
Click Log tab to confirm that the criteria does not contain ShipperId.
Click Close to close dialog.

Next, comment out the criteria for parameter p2.

<?xml version="1.0"?>
<ROOT>
    SELECT * FROM SHIPPERS
    <WHERE>
        WHERE
            <IF>SHIPPERID = @p1</IF>
            <IF>AND COMPANYNAME = @p2</IF>
    </WHERE>
    <PARAM>
        p1, Int32, 1<!--<DIV/>
        p2, String, Speedy Express-->
    </PARAM>
</ROOT>

Click Execute Query.
Click SQL tab to confirm that the criteria contains ShipperId only and does not contain CompanyName.
Click LOG tab to confirm that the criteria does not contain CompanyName.
Click Close to close dialog.

3.4.3 List criteria

This section describes how to select records with In clause.

Edit the sql statement to select records with In clause as follows. (Analytical tool analyze p1, Int32, 1, 2 and set 1 and 2 (Int32) to parameter p1)

<?xml version="1.0"?>
<ROOT>
    SELECT * FROM SHIPPERS
    <WHERE>
        WHERE
            <LIST>SHIPPERID IN (@p1)</LIST>
    </WHERE>
    <PARAM>
        p1, Int32, 1, 2
    </PARAM>
</ROOT>

Click Execute Query to confirm that the record, that the value of ShipperId is "1" or "2", of Shipper table is only displayed in result dialog.
Click SQL tab to confirm that the query with In clause has been executed.
Click LOG tab to confirm that the query with In clause has been executed.

3.5 Use dynamic parameterized query from application

3.5.1 Save query file

Click Save in Query file group.
Set the properties in Save dialog and save query file.
- Path
  - The path that is set as the path to the folder contains sql files in application configuration file.
- File name
  - GetShippers.xml
Note:
In case of using Open Touryo, the path to the folder contains sql files is set in Web.config or app.config. For details, refer the Open Touryo user guide (Leader Edition).
```
<?xml version="1.0"?>
<configration>
    <appSettings>
        <add key="SqlTextFilePath" value="the path to the folder contains sql files" />
    </appSettings>
</configuration>
```

3.5.2 Call the query file from application

Create the application using Open Touryo.

Note:
This tutorial does not describe how to create the application using Open Touryo. Refer Open Touryo tutorial (ASP.NET Edition, Two-tier client server application (C#) Edition, etc.).

Implement as follows to use saved query file in data access class.

public DataTable GetShippers()
{
    // Set query file
    this.SetSqlByFile2("GetShippers.xml");

    // Set parameter (Because of listed parameter, set as ArrayList)
    this.SetParameter("p1", new ArrayList(new int[] { 1, 2 }));

    // Execute query
    DataTable dt = new DataTable();
    this.ExecSelectFill_DT(dt);

    // Return the result of query
    return dt;
}

Run the application and confirm the behavior.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly