Working with Project

Last modified: February 7, 2025

The following walkthrough describes how to create a sample project, add folders and files to it, deploy the project and export it to a single script. A sample project contains scripts, that create database objects, that were created manually in previous walkthroughs.

To create a new project, select New on the File menu, and then click Project. In the New Project dialog box, double-click Devart Database Projects, then select MySQL Project in the Templates box, and click OK. A project will appear in Solution Explorer.

Prerequisites

In order to complete this walkthrough, you will need access to MySQL server 5.0.13 or higher.

You should have the following privileges:

• CREATE privilege on the demobase database
• DROP privilege on the demobase database
• INSERT privilege on the demobase database
• DELETE privilege on the demobase database
• UPDATE privilege on the demobase database
• SELECT global privilege
• CREATE USER global privilege
• GRANT OPTION

Creating Project

Creating Folder Structure

Folders help to organize related scripts and queries into groups, so, first of all, it’s necessary to create a folder structure for the new project. The root folder will contain three folders: Security, Database objects, Data. Folders for every database object type that is created in the project will be available in the Database objects folder. To create such folder structure, perform the following steps.

1. Right-click the project node in the Solution Explorer and choose Add Folder on the shortcut menu.

• or - Select the project node in the Solution Explorer and choose Add New Solution Folder from the Project menu.

  Note

  Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. These elements are determined by your Visual Studio edition and settings.

1. Enter the new folder name (Security).
2. Repeat steps 1 - 3 for creating Database objects and Data folders.
3. Right-click the Database objects folder node in the Solution Explorer.
4. Select Add Folder on the shortcut menu.
5. Enter the new folder name (Tables).
6. Repeat steps 4 - 6 for creating Views and Triggers folders.

Now, you have a folder structure for our project. Note that project folders are not real Windows folders, information about these folders is stored in a project file. When you create or delete folders, corresponding Windows folders are not created or deleted.

Adding Scripts to the Project

Now it’s necessary to create required SQL script files and add them to the project. You’ll learn how to add existing files to the project first. Existing files can be added to a project in two ways. Perform the following actions to create files and add them to the project:

1. Click the New SQL button on the Devart - Main toolbar.

2. Type or paste following code into the SQL document:

    CREATE TABLE dept (
    DeptNo INT(11) NOT NULL default 0,
    Dname VARCHAR(14)NOT NULL,
    loc VARCHAR(13), 
    SALSUM FLOAT, 
    PRIMARY KEY (DeptNo) 
    )
   

3. Click the Save button on the Standard toolbar.
4. Choose file location and enter file name (for example, Table Dept) in the Save As dialog box that appeared.
5. Save the file.
6. Right-click on the Tables folder in the Solution Explorer and choose Add Existing File on the shortcut menu

• or -

  Click on the Tables folder in the Solution Explorer and choose Add Existing Item on the Project menu.

1. Choose the created file in the Open dialog box.

   Now, the file is added to the project. Another way to add an existing file to a project is to open it. Then, on the File menu, select Move <file name> into, and then click Project <project name>. Perform the following actions to do this:

2. Click the New SQL button on the Devart - Main toolbar.

3. Type or paste following code into the SQL document:

    CREATE TABLE emp ( 
    EmpNo INT(11) NOT NULL default 0, 
    EName VARCHAR(10), 
    Job VARCHAR(9), 
    MGR INT(11), 
    HireDate DATETIME, 
    Sal FLOAT, 
    Commission FLOAT, 
    DeptNo INT(11), 
    PRIMARY KEY (EmpNo), 
    UNIQUE INDEX index1 USING BTREE (EmpNo) 
    )
   

4. Click the Save button on the Standard toolbar.
5. Choose file location and enter file name (for example, Table Emp) in the Save As dialog box that appeared.
6. Save the file.
7. On the File menu, select Move Table emp.sql into, and then click New Project1.
8. Drag the file shortcut node in the Solution Explorer to the Tables folder node.

You can add not only existing files to the project, but also create new files, that will belong to the project, too. You can use file templates when adding a new file to the project. Perform the following actions to create an SQL file for creating the view STAFF.

1. Right-click the Views folder node in the Solution Explorer and select Add New File on the shortcut menu

   or

   Click the Views folder node in the Solution Explorer and select Add New Item on the Project menu.

2. Select the View item in the Templates box and click Add.
3. Replace the view name Test.VIEW1 with staff in the SQL document that appeared.

4. Type or paste the following query text after the AS keyword.

    SELECT
    emp.ename AS name,
    emp.job, 
    emp.sal AS salary,  
    dept.dname AS department
    FROM emp, dept 
    WHERE emp.deptno = dept.deptno
   

5. Save the document with the name View Staff.

To complete the project, you should add some more scripts to it. Create and add the following scripts to the project in the same way as the previous ones:

• Emp data to the Data folder

INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, 0, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-15', 2975, 0, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, 0, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, 0, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-08-19', 3000, 0, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, 0, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, 0, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, 0, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, 0, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, 0, 10);

• Dept data to the Data folder

  INSERT INTO dept(deptno, dname, loc) VALUES (10, 'ACCOUNTING', 'NEW YORK'); 
  INSERT INTO dept(deptno, dname, loc) VALUES (20, 'RESEARCH', 'DALLAS'); 
  INSERT INTO dept(deptno, dname, loc) VALUES (30, 'SALES', 'CHICAGO');
  

• Trigger SalsumIns to the Triggers folder

  CREATE TRIGGER SalsumIns 
   AFTER INSERT
   ON emp FOR EACH ROW 
   UPDATE DEPT 
   SET SALSUM = (
  SELECT SUM(EMP.SAL) 
    FROM EMP 
    WHERE EMP.DEPTNO = DEPT.DEPTNO)
  

• Trigger SalsumUpd to the Triggers folder

  CREATE TRIGGER SalsumUpd 
   AFTER UPDATE 
   ON emp FOR EACH ROW 
   UPDATE DEPT 
   SET SALSUM = (
  SELECT SUM(EMP.SAL) 
    FROM EMP 
    WHERE EMP.DEPTNO = DEPT.DEPTNO)
  

• Trigger SalsumDel to the Triggers folder

  CREATE TRIGGER SalsumDel 
   AFTER DELETE 
   ON emp FOR EACH ROW 
   UPDATE DEPT 
   SET SALSUM = (
  SELECT SUM(EMP.SAL) 
    FROM EMP 
    WHERE EMP.DEPTNO = DEPT.DEPTNO)
  

• User Jones to the Security folder

  CREATE USER `Jones`@`localhost`; 
  GRANT SELECT ON * TO `Jones`@`localhost`; 
  GRANT INSERT, UPDATE(Sal) ON emp TO `Jones`@`localhost`;
  

After you added these scripts to project, save the project and all unsaved files for future use. To do it, perform the following actions:

1. Select the project node in the Solution Explorer and select Save NewProject1 or Save NewProject1 As on the File menu.
2. Type the project name ‘Demobase project’ and choose project location.
3. Click OK to save the project.

Working with Project

Setting Project Options

Let’s set our project options for building and deploying project. To set project options, right-click the project in the Solution Explorer and select Properties on the shortcut menu or select Properties on the Project menu.

On the Build tab, you can customize build results. In this walkthrough, you don’t need to change anything on this tab. Click Database to set Target Database Options and perform the following actions.

1. Set Target database name to demobase.
2. Specify your MySQL Server version.
3. Click Always re-create database.

Then, switch to the Build order tab. When building project, project scripts will be processed and placed to result script according to build order. Build order should be the following: at first you should create tables, then triggers, then tables should be filled with data, after that user and view should be created.

So, the build order must be the following.

• Table Dept.sql
• Table Emp.sql
• Trigger SalsumIns.sql
• Trigger SalsumDel.sql
• Trigger SalsumUpd.sql
• Dept data.sql
• Emp data.sql
• User Jones.sql
• View Staff.sql

Use the Move Up and Move Down buttons or drag-n-drop to change build order.

Creating Project Configuration

Suppose you should deploy the project on MySQL 5.0.13 or higher, and in some cases on older version of MySQL, such as 4.0 or 4.1. Let’s use project configurations to build project in two different variants of result script. Default configuration will be used for MySQL 5 and OldServer configuration will be used for MySQL 4.

To create a configuration perform the following steps.

1. Select Configuration Manager from the Solution Configurations drop-down list on the Standard toolbar.
2. Click the Add button.
3. Enter the configuration name (OldServer).
4. Select Default in the Copy settings from drop-down list.
5. Click OK to add the configuration.
6. Click OK to close the Configuration Manager.

Now, you can set options for new project configuration. In the Project properties document (not on the Solution toolbar), select OldServer from the Configuration drop-down list. Specify the Server version on the Database tab and clear checkboxes for view script and trigger scripts on the Build order tab.

Building and Deploying Project

Now, our project is ready to build it. To start the build, click the Build button. The project will be built to result script which will be located by default in the default subfolder of the project folder.

When deploying the project, the build result script will be executed using the project connection. If the project does not have a connection, it can not be deployed. So, you should assign a connection to the project. It must be a connection to account of a user that have administrative rights, because deploying this project creates a database. So, create such a connection or use the existing one. To assign a connection to a project perform one of the following.

• Right-click the project in Solution Explorer and select Assign connection on the shortcut menu, then select the required connection from the list and click OK.
• Right-click the connection in Database Explorer and select Assign to project on the shortcut menu.
• Drag the connection from Database Explorer to Solution Explorer.

After performing this you can deploy project. To deploy the project, select Deploy on the Build menu and click Yes in the appeared dialog box. You will see deployment log in the General tab of the Output window. After deployment is finished, you can access demobase database and its objects.

After deploying project you can add database object shortcuts to it. To add a database object shortcut drag the database object from the project connection in the Database Explorer to the Solution Explorer or select Add to Project Demobase on the shortcut menu of the object node in Database Explorer. Database object shortcuts can be used for fast opening of object editors by double-clicking the shortcuts. You can also easily find the object in Database Explorer by right-clicking its shortcut in Solution Explorer and selecting Find in Database Explorer on the shortcut menu.