JavaScript Editor Source code editor     What Is Ajax 

Main Page

23.3. MySQL Visual Studio Plugin

The MySQL Visual Studio Plugin is a DDEX provider; a plug-in for Visual Studio 2005 that allows developers to maintain database structures, and supports built-in data-driven application development tools.

The current version of the MySQL Visual Studio Plugin includes only database maintenance tools. Data-driven application development tools are not supported.

The MySQL DDEX Provider operates as a standard extension to the Visual Studio Data Designer functionality available through the Server Explorer menu of Visual Studio 2005, and enables developers to create database objects and data within a MySQL database.

The MySQL Visual Studio Plugin is designed to work with MySQL version 5.0, but is also compatible with MySQL 4.1.1 and provides limited compatibility with MySQL 5.1.

23.3.1. Installing the MySQL Visual Studio Plugin

The MySQL Visual Studio Plugin requires one of Visual Studio 2005 Standard, Professional or Team Developer Edition to be installed. Other editions of Visual Studio 2005 are not supported.

Note

Starting with Connector/NET 5.1.2, the Visual Studio Plugin is included in the installation. If you have installed Connector/NET 5.1.2, then you do not need to separately install the Visual Studio Plugin.

Here is the list of components that should already be installed before starting the installation of the MySQL Visual Studio Plugin:

  • Visual Studio 2005 Standard, Professional or Team Developer Edition.

  • MySQL Server 4.1.1 or later (either installed on the same machine, or a separate server).

  • MySQL Connector/NET 5.0.

Note

When installing Connector/NET you must ensure that the connector is installed into the Global Assembly Cache (GAC). The Connector/NET installer handles this for you automatically, but in a custom installation the option may have been disabled.

The user used to connect to the MySQL server must have the following privileges to use the functionality provided by the MySQL Visual Studio Plugin:

  • The SELECT privilege for the INFORMATION_SCHEMA database.

  • The EXECUTE privilege for the SHOW CREATE TABLE statement.

  • The SELECT privilege for the mysql.proc table (required for operations with stored procedures and functions).

  • The SELECT privilege for the mysql.func table (required for operations with User Defined Functions (UDF)).

  • The EXECUTE privilege for the SHOW ENGINE STATUS statement (required for retrieving extended error information).

  • Appropriate privileges for performed operations (e.g. the SELECT privilege is required to browse data from a table etc.).

The MySQL Visual Studio Plugin is delivered as a MSI package that can be used to install, uninstall or reinstall the Provider. If you are not using Windows XP or Windows Server 2003 you upgrade the Windows Installer system to the latest version (see http://support.microsoft.com/default.aspx?scid=kb;EN-US;292539 for details).

The MSI-package is named MySQL.VisualStudio.msi. To install the MySQL Visual Studio Plugin, right click on the MSI file and select Install. The installation process is as follow:

  1. The standard Welcome dialog is opened. Click Next to continue installation.

  2. The License agreement (GNU GPL) window is opened. Accept the agreement and click Next to continue.

  3. The destination folder choice dialog is opened. Here you can point out the folder where the MySQL Visual Studio Plugin will be installed. The default destination folder is %ProgramFilesDir%\MySQL\MySQL DDEX Data Provider, where %ProgramFilesDir% is the Program Files folder of the installation machine. After choosing the destination folder, click Next to continue.

  4. The installer will ask to confirm that installation. Click Install to start installation process.

  5. The installation will now take place. At the end of this step the Visual Studio command table is rebuilt (this process may take several minutes).

  6. Once installation is complete, click Finish to end the installation process.

To uninstall the MySQL Visual Studio Plugin, you can use either Add/Remove Programs component of the Control Panel or the same MSI-package. Choose the Remove option, and the Provider will be uninstalled automatically.

To repair the Provider, right click the MSI-package and choose the Repair option. The MySQL Visual Studio Plugin will be repaired automatically.

The installation package includes the following files:

  • MySQL.VisualStudio.dll — the MySQL DDEX Provider assembly.

  • MySQL.Data.dll — the assembly containing the MySQL Connector .NET which is used by the Provider.

  • MySql.VisualStudio.dll.config — the configuration file for the MySQL Visual Studio Plugin. This file contains default values for the provider GUI layout.

    Note

    Do not remove this file before the first use of the Provider.

  • Register.reg — the file with registry entries that can be used to register the MySQL DDEX Provider in the case of the manual installation.

  • Install.js — the script used to register the Connector .NET as an ADO.NET data provider in the machine.config file.

  • Release notes.doc — the document with release notes.

To install the Provider manually, copy all files of the installation package in a desired folder, then set the full path to the Provider assembly as a value of the CodeBase entry. For example:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\VisualStudio\8.0\Packages\{79A115C9-B133-4891-9E7B-242509DAD272}]@="MySql.Data.VisualStudio.MySqlDataProviderPackage"
"InprocServer32"="C:\\WINNT\\system32\\mscoree.dll"
"Class"="MySql.Data.VisualStudio.MySqlDataProviderPackage"
"CodeBase"="C:\\MySqlDdexProvider\\MySql.VisualStudio.dll"

Then import information from the Register.reg file to the registry by clicking of the file. At the confirmation dialog choose Yes. Next you must run the command devenv.exe /setup within a Command Prompt to rebuild the Visual Studio command table.

23.3.2. Creating a connection to the MySQL server

Once the MySQL Visual Studio Plugin is installed, you can use it to create, modify and delete connections to MySQL databases. To create a connection with a MySQL database, perform the following steps:

  1. Start Visual Studio 2005 and open Server Explorer window by choosing the Server Explorer option from the View menu.

  2. Right click on the Data Connections node and choose the Add Connection button.

  3. The Add Connection dialog is opened. Press the Change button to choose MySQL Database as a data source.

  4. Change Data Source dialog is opened. Choose MySQL Database in the list of data sources (or the other option, if MySQL Database is absent), and then choose .NET Framework Data Provider for MySQL in the combo box of data providers.

    Choosing a data source

    Press OK to confirm your choice.

  5. Enter the connection settings: the server host name (for example, localhost if the MySQL server is installed on the local machine), the user name, the password, and the default database schema. Note that you must specify the default schema name to open the connection.

    Setting connection properties
  6. You can also set the port to connect with the MySQL server by pressing the Advanced button. To test a connection with the MySQL server, ser the server host name, the user name, and the password, and press the Test Connection button. If the test fails, check the connection values that you have supplied are correct and that the corresponding user and privileges have been configured on the MySQL server.

  7. After you set all settings and test the connection, press OK. The newly created connection is displayed in Server Explorer. Now you can work with the MySQL server through standard Server Explorer interface.

After a connection is successfully established, all the connection settings are saved. When you next open Visual Studio, the connection to the MySQL server will appear within Server Explorer so that you can re-establish a connection to the MySQL server.

To modify and delete a connection, use the Server Explorer context menu for the corresponding node. You can modify any of the settings just by overwriting the existing values with new ones. Note that a connection should be modified or deleted only if no active editor for it's objects is opened. Otherwise your data could be lost.

23.3.3. Using the MySQL Visual Studio Plugin

To work with a MySQL server using the MySQL Visual Studio Plugin, open the Visual Studio 2005, open the Server Explorer, and select the required connection. The working area of the MySQL Visual Studio Plugin consists of three parts.

DDEX enviroment
  • Database objects (tables, views, stored routines, triggers, and user defined functions) are displayed in the Server Explorer tree. Here you can choose an object and edit its properties and definition.

  • Properties of a selected database object are displayed in the Properties panel. Certain properties can be edited directly within this window.

  • The editor panel provides direct access to the SQL statement and definition of specific objects. Fore example, the SQL statements within a stored procedure definition are shown and edited within this panel.

23.3.3.1. Editing Tables

The Table Editor can be accessed through a mouse action on table-type node of Server Explorer. To create a new table, right click on the Tables node (under the connection node) and choose the Create Table command from a context menu. To modify an existing table, double click on a node of the table you wish to modify, or right click on this node and choose the Alter Table command from a context menu. Either of the commands opens the Table Editor.

Editing a table

The MySQL Visual Studio Plugin Table Editor is implemented in a similar fashion to the standard Query Browser Table Editor, but with minor differences.

The Table Editor consists of the following parts:

  • Columns Editor — for column creation, modification and deletion.

  • Indexes tab — for table/column index management.

  • Foreign Keys tab — for configuration of foreign keys.

  • Column Details tab — used to set advanced column options.

  • Properties window — used to set table properties.

To save changes you have made in the Table Editor, use either Save or Save All buttons of the Visual Studio main toolbar, or just press Ctrl+S. Before changes are saved, a confirmation dialog will be displayed to confirm that you want to update the corresponding object within the MySQL database.

23.3.3.1.1. Column Editor

You can use the Column Editor to set or change the name, data type, default value and other properties of a table column. To set the properties of an individual column, select the column using the mouse. Alternatively, you can move through the grid using Tab and Shift+Tab keys.

  • To set or change the name, data type, default value and comment of a column, select the appropriate cell and edit the desired value.

  • To set or unset flag-type column properties (i.e., primary key, NOT NULL, auto incremented, flags), check or uncheck the corresponding checkboxes. Note that the available column flags will depend on the columns data type.

  • To reorder columns, index columns or foreign key columns in the Column Editor, select the whole column you wish to reorder by clicking on the selector column at the left of the column grid. Then move the column by using Ctrl+Up (to move the column up) and Ctrl+Down (to move the column down) keys.

  • To delete a column, select it by clicking on the selector column at the left of the column grid, then press the Delete button on a keyboard.

23.3.3.1.2. Indexes tab

Index management is performed via the Indexes tab.

  • To add an index, press the + button and set the properties in the Index Settings groupbox at the right. You can set the index name, index kind, index type and a set of index columns.

  • To remove an index, select the index from the list and press the - button.

  • To change index settings, select the index from the list; detailed information about the index is displayed in the Index Settings panel.

You cannot change a table column to an index column using drag and drop. Instead, you can add new index columns to a table and set their table columns by using the embedded editor within the Indexes tab

23.3.3.1.3. Foreign Keys tab

Foreign Key management is performed via the Foreign Keys tab.

  • To add a foreign key, press the + button and set properties in the Foreign Keys Settings panel. You can set the foreign key name, referenced table name, foreign key columns and actions on update and delete.

  • To remove a foreign key, select the foreign key and press the - button.

  • To change foreign key settings, select the foreign key and use the Foreign Keys Settings panel to edit the properties.

  • When a foreign key is changed, the MySQL Visual Studio Plugin generates two queries: the first query drops the changed keys and the second one recreates the new values. The reason for such a behavior is to avoid the Bug#8377 and Bug#8919.

Note

If changed values are for some reason inconsistent and cause the second query to fail, all affected foreign keys will be dropped. If this is the case, the MySQL Visual Studio Plugin will mark them as new in the Table Editor, and you will have to recreate them later. But if you close the Table Editor without saving, these foreign keys will be lost.

23.3.3.1.4. Column Details tab

The Column Details tab can be used to set column options. Besides the main column properties that are presented in the Column Editor, in the Column Details tab you can set two additional properties options: the character set and the collation sequence.

23.3.3.1.5. Table Properties window

There is no separate tab for table options and advanced options. All table options can be browsed and changed using the Properties window of Visual Studio 2005.

The following table properties can be set:

  • Auto Increment

  • Average Row Length

  • Character Set

  • Checksum for Rows

  • Collation

  • Comment

  • Connection

  • Data Directory

  • Delay Key Updates

  • Engine

  • Index Directory

  • Insert Method

  • Maximum Rows

  • Minimum Rows

  • Name

  • Pack Keys

  • Password

  • Row Format

  • Union

Some of these properties can have arbitrary text values, others accept values from a predefined set.

The properties Schema and Server are read only.

23.3.3.2. Editing Table Data

The Table Data Editor, allows a user to browse, create and edit data of tables. The Table Data Editor is implemented as a simple data grid with auto generated columns.

To access the Table Data Editor, right click on a node representing the table or view in Server Explorer. From the nodes context menu, choose the Browse or Edit Data command. For tables and updatable views, this command opens the Table Data Editor in edit mode. For non-updatable views, this command opens the Table Data Editor in read-only mode.

When in the edit mode, you can modify table data by modifying the displayed table contents directly. To add a row, set desired values in the last row of the grid. To modify values, set new values in appropriate cells. To delete a row, select it by clicking on the selector column at the left of the grid, then press the Delete button.

To save changes you have made in the Table Data Editor, use either Save or Save All buttons of the Visual Studio main toolbar, or just press Ctrl+S. A confirmation dialog will confirm whether you want the changes saved to the database.

23.3.3.3. Editing Views

To create a new view, right click the Views node under the connection node in Server Explorer. From the nodes context menu, choose the Create View command. This command opens the SQL Editor.

To modify an existing view, double click on a node of the view you wish to modify, or right click on this node and choose the Alter View command from a context menu. Either of the commands opens the SQL Editor.

To create or alter the view definition using SQL Editor, type the appropriate SQL statement in the SQL Editor.

Note

You should enter only the defining statement itself, without the CREATE VIEW AS preface.

All other view properties can be set in the Properties window. These properties are:

  • Algorithm

  • Check Option

  • Definer

  • Name

  • Security Type

Some of these properties can have arbitrary text values, others accept values from a predefined set.

The properties Is Updatable, Schema and Server are readonly.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or just press Ctrl+S. A confirmation dialog will confirm whether you want the changes saved to the database.

23.3.3.4. Editing Stored Procedures and Functions

To create a new stored procedure, right click the Stored Procedures node under the connection node in Server Explorer. From the nodes context menu, choose the Create Routine command. This command opens the SQL Editor.

To create a new stored function, right click the Functions node under the connection node in Server Explorer. From the node's context menu, choose the Create Routine command.

To modify an existing stored routine (procedure or function), double click on a node of the routine you wish to modify, or right click on this node and choose the Alter Routine command from a context menu. Either of the commands opens the SQL Editor.

To create or alter the routine definition using SQL Editor, type this definition in the SQL Editor using standard SQL.

All other routine properties can be set in the Properties window. These properties are:

  • Comment

  • Data Access

  • Definer

  • Is Deterministic

  • Security Type

Some of these properties can have arbitrary text values, others accept values only from a predefined set.

Also you can set all the options directly in the SQL Editor, using the standard CREATE PROCEDURE or CREATE FUNCTION statement. However, it is recommended to use the Properties window instead.

Note

You should never add the CREATE preface to the routine definition.

The properties Name, Schema and Server in the Properties window are read-only. Set or change the procedure name in the SQL editor.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or just press Ctrl+S. A confirmation dialog will confirm whether you want the changes saved to the database..

23.3.3.5. Editing Triggers

To create a new trigger, right click on a node of a table for which you wish to add a trigger. From the node's context menu, choose the Create Trigger command. This command opens the SQL Editor.

To modify an existing trigger, double click on a node of the trigger you wish to modify, or right click on this node and choose the Alter Trigger command from a context menu. Either of the commands opens the SQL Editor.

To create or alter the trigger definition using SQL Editor, type the trigger statement in the SQL Editor using standard SQL.

Note

You should enter only the trigger statement, that is the part of the CREATE TRIGGER query that is placed after the FOR EACH ROW clause.

All other trigger properties are set in the Properties window. These properties are:

  • Definer

  • Event Manipulation

  • Name

  • Timing

Some of these properties can have arbitrary text values, others accept values only from a predefined set.

The properties Event Table, Schema and Server in the Properties window are read-only.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or just press Ctrl+S. A confirmation dialog will confirm whether you want the changes saved to the database.

23.3.3.6. Editing User Defined Functions (UDF)

To create a new User Defined Function (UDF), right click the UDFs node under the connection node in Server Explorer. From the node's context menu, choose the Create UDF command. This command opens the UDF Editor.

To modify an existing UDF, double click on a node of the UDF you wish to modify, or right click on this node and choose the Alter UDF command from a context menu. Either of the commands opens the UDF Editor.

The UDF editor allows you to set the following properties through the properties panel:

  • Name

  • So-name (DLL name)

  • Return type

  • Is Aggregate

The property Server in the Properties window is read-only.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or just press Ctrl+S. A confirmation dialog will confirm whether you want the changes saved to the database.

23.3.3.7. Dropping database objects

Tables, views, stored routines, triggers, an UDFs can be dropped with the appropriate Drop command from its context menu: Drop Table, Drop View, Drop Routine, Drop Trigger, Drop UDF.

You will be asked to confirm the execution of the corresponding drop query in a confirmation dialog.

Dropping of multiple objects is not supported.

23.3.3.8. Cloning database objects

Tables, views, stored procedures and functions can be cloned with the appropriate Clone command from its context menu: Clone Table, Clone View, Clone Routine. The clone commands open the corresponding editor for a new object: the Table Editor for cloning a table and the SQL Editor for cloning a view or a routine.

To save the cloned object, use either Save or Save All buttons of the Visual Studio main toolbar, or just press Ctrl+S. A confirmation dialog will confirm whether you want the changes saved to the database.

23.3.4. Visual Studio Plugin Support

If you have a comment, or if you discover a bug, please, use our MySQL bug tracking system (http://bugs.mysql.com) to report problem or add your suggestion.

23.3.4.1. Visual Studio Plugin FAQ

Questions

  • 24.3.4.1.1: When creating a connection, typing the connection details causes the connection window to immediately close.

Questions and Answers

24.3.4.1.1: When creating a connection, typing the connection details causes the connection window to immediately close.

There are known issues with versions of Connector/NET earlier than 5.0.2. Connector/NET 1.0.x is known not to work. If you have any of these versions installed, or have previously upgraded from an earlier version, uninstall Connector/NET completely and then install Connector/NET 5.0.2.

JavaScript Editor Source code editor     What Is Ajax