On Windows, you can install either through a binary installation process or by downloading a zip file with the Connector/Net components.

Before installing, ensure that your system is up to date, including installing the latest version of the .NET Framework.

C:\> msiexec /package connector-net.msi /quiet

There is no installer available for installing the Connector/Net component on your Unix installation. Before installing, ensure that you have a working Mono project installation. To test whether your system has Mono installed, enter:

shell> mono --version

The version of the Mono JIT compiler is displayed.

To compile C# source code, make sure a Mono C# compiler is installed. Note that there are two Mono C# compilers available, mcs, which accesses the 1.0-profile libraries, and gmcs, which accesses the 2.0-profile libraries.

To install Connector/Net on Unix/Mono:

You are now ready to compile your application. You must ensure that when you compile your application you include the Connector/Net component using the -r: command-line option. For example:

shell> gmcs -r:System.dll -r:System.Data.dll -r:MySql.Data.dll HelloWorld.cs

Note, the assemblies that are referenced depend on the requirements of the application, but applications using Connector/Net must provide -r:MySql.Data as a minimum.

You can further check your installation by running the compiled program, for example:

shell> mono HelloWorld.exe

Obtaining the Source Code

To obtain the most recent development source tree, first download and install Bazaar. You can obtain Bazaar from the Bazaar VCS Website. Bazaar is supported by any platform that supports Python, and is therefore compatible with any Linux, Unix, Windows or Mac OS X host. Instructions for downloading and installing Bazaar on the different platforms are available on the Bazaar Web site.

The most recent development source tree is available from our public Subversion trees at http://dev.mysql.com/tech-resources/sources.html.

To check out out the Connector/Net sources, change to the directory where you want the copy of the Connector/Net tree to be stored, then use the following command:

shell> bzr branch lp:connectornet/trunk

To download a specific version of Connector/Net, specify the version number instead of trunk. For example, to obtain a copy of the 6.0 version of the source tree:

shell> bzr branch lp:connectornet/6.0

Source packages are also available on the downloads page.

Building the Source Code on Windows

The following procedure can be used to build the connector on Microsoft Windows.

Building the Source Code on Unix

Support for building Connector/Net on Mono/Unix is currently not available.

Once the connector 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:

After the connection is successfully established, all settings are saved for future use. When you start Visual Studio for the next time, open the connection node in Server Explorer to establish a connection to the MySQL server again.

To modify and delete a connection, use the Server Explorer context menu for the corresponding node. You can modify any of the settings by overwriting the existing values with new ones. Note that the connection may be modified or deleted only if no active editor for its objects is opened: otherwise, you may lose your data.

IntelliSense support is available starting in Connector/Net 6.5. Once you have established a connection, for example, using the Connect to MySql toolbar button, you can get autocompletion as you type, or by pressing Control+J. Depending on the context, the autocompletion dialog can show the list of available tables, table columns, or stored procedures (with the routine's signature as a tooltip). Typing some characters before pressing Control+J filters the choices to those items starting with that prefix.

Connector/Net contains a table editor, which enables the visual creation and modification of tables.

The Table Designer can be accessed through a mouse action on table-type node of Server Explorer. To create a new table, right-click the Tables node (under the connection node) and choose the Create Table command from the context menu.

To modify an existing table, double-click the node of the table to modify, or right-click this node and choose the Design item from the context menu. Either of the commands opens the Table Designer.

The table editor is implemented in the manner of the well-known Query Browser Table Editor, but with minor differences.

Figure 22.5. Editing New Table

Please click here to enlarge image

Table Designer consists of the following parts:

Each of these areas is discussed in more detail in subsequent sections.

To save changes you have made in the Table Designer, use either Save or Save All button of the Visual Studio main toolbar, or press Control+S. If you have not already named the table, you will be prompted to do so.

Figure 22.6. Choose Table Name

Once the table is created, you can view it in the Server Explorer.

Figure 22.7. Newly Created Table

The Table Designer main menu lets you set a primary key column, edit relationships such as foreign keys, and create indexes.

Figure 22.8. Table Designer Main Menu

22.2.3.3.2. Editing Indexes

Indexes management is performed using the Indexes/Keys dialog.

To add an index, select Table Designer, Indexes/Keys... from the main menu, and click Add to add a new index. You can then set the index name, index kind, index type, and a set of index columns.

Figure 22.9. Indexes Dialog

To remove an index, select it in the list box on the left, and click the Delete button.

To change index settings, select the needed index in the list box on the left. The detailed information about the index is displayed in the panel on the right hand side. Change the desired values.

22.2.3.3.3. Editing Foreign Keys

You manage foreign keys for InnoDB tables using the Foreign Key Relationships dialog.

To add a foreign key, select Table Designer, Relationships... from the main menu. This displays the Foreign Key Relationship dialog. Click Add. You can then set the foreign key name, referenced table name, foreign key columns, and actions upon update and delete.

To remove a foreign key, select it in the list box on the left, and click the Delete button.

To change foreign key settings, select the required foreign key in the list box on the left. The detailed information about the foreign key is displayed in the right hand panel. Change the desired values.

Figure 22.10. Foreign Key Relationships Dialog

22.2.3.3.5. Table Properties

To bring up Table Properties select the table and right-click to activate the context menu. Select Properties. The Table Properties dockable window will be displayed.

Figure 22.11. Table Properties Menu Item

Please click here to enlarge image

The following table properties can be set:

• Auto Increment.

• Average Row Length.

• Character Set.

• Collation.

• Comment.

• Data Directory.

• Index Directory.

• Maximum Rows.

• Minimum Rows.

• Name.

• Row Format.

• Schema.

• Storage Engine. Note that in MySQL 5.5 and higher, the default storage engine for new tables is InnoDB. See Section 14.3.1, "InnoDB as the Default MySQL Storage Engine" for more information about the choice of storage engine, and considerations if you convert existing tables to InnoDB.

The property Schema is read-only.

Figure 22.12. Table Properties

Please click here to enlarge image

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

Figure 22.13. Editing View SQL

You can then enter the SQL for your view.

Figure 22.14. View SQL Added

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

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

Some of these properties can have arbitrary text values, others accept values from a predefined set. In the latter case, set the desired value with an embedded combobox.

The properties Is Updatable and Schema are readonly.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or press Control+S.

Figure 22.15. View SQL Saved

Please click here to enlarge image

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

Figure 22.16. Edit Stored Procedure SQL

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 the node of the routine to modify, or right-click this node and choose the Alter Routine command from the 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:

Some of these properties can have arbitrary text values, others accept values from a predefined set. In the latter case, set the desired value using the embedded combo box.

You can also 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.

To save changes you have made, use either Save or Save All buttons of the Visual Studio main toolbar, or press Control+S.

Figure 22.17. Stored Procedure SQL Saved

Please click here to enlarge image

To observe the runtime behavior of a stored routine and debug any problems, use the Stored Procedure Debugger (available in Connector/Net 6.6 and higher). See Section 22.2.3.8, "Debugging Stored Procedures and Functions" for details.

To create a new trigger, right-click the node of the table in which to add the 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 the node of the trigger to modify, or right-click this node and choose the Alter Trigger command from the 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.

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

Some of these properties can have arbitrary text values, others accept values from a predefined set. In the latter case, set the desired value using the embedded combo box.

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 press Control+S. Before changes are saved, you will be asked to confirm the execution of the corresponding SQL query in a confirmation dialog.

To observe the runtime behavior of a stored routine and debug any problems, use the Stored Procedure Debugger (available in Connector/Net 6.6 and higher). See Section 22.2.3.8, "Debugging Stored Procedures and Functions" for details.

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 the node of the UDF to modify, or right-click this node and choose the Alter UDF command from the context menu. Either of these commands opens the UDF Editor.

The UDF editor enables you to set the following properties:

There are text fields for both names, a combo box for the return type, and a check box to indicate if the UDF is aggregate. All these options are also accessible using the Properties window.

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 press Control+S. Before changes are saved, you will be asked to confirm the execution of the corresponding SQL query in a confirmation dialog.

To observe the runtime behavior of a stored routine and debug any problems, use the Stored Procedure Debugger (available in Connector/Net 6.6 and higher). See Section 22.2.3.8, "Debugging Stored Procedures and Functions" for details.

The stored procedure debugger, new in Connector/Net 6.6, provides facilities for setting breakpoints, stepping into individual statements (Step Into, Step Out, Step Over), evaluating and changing local variable values, evaluating breakpoints, and other typical debugging tasks.

Installing the Debugger

To enable the stored procedure debugger, install Connector/Net 6.6 or higher and choose the Complete option.

Privileges

The debugger recreates at the start of each debug session a serversidedebugger database in your server. This database helps to track the instrumented code and implement observability logic in the debugged routine. Your current connection needs to have privileges to create that database, and its associated stored routines, functions, and tables.

The debugger makes changes behind the scenes to temporarily add instrumentation code to the stored routines that you debug. You must have the ALTER ROUTINE privilege for each stored procedure, function, or trigger that you debug. (Including procedures and functions that are called, and triggers that are fired, by a procedure that you are debugging.)

Starting the Debugger

To start the debugger, follow these steps:

At this point, Visual Studio switches to debug mode, opening the source code of the routine being debugged in step mode, positioned on the first statement.

If the initial routine you debug has one or more arguments, a popup will show up with a grid (a row per each argument and three columns: one for the argument, one for the argument value (this is editable) and one for nullifying that argument value (a checkbox)). After setting up all the argument values, you can press OK to start the debug session, or Cancel to cancel the debug session.

Figure 22.20. Setting Arguments (1 of 2)

Figure 22.21. Setting Arguments (2 of 2)

How the Debugger Works

To have visibility into the internal workings of a stored routine, the debugger prepares a special version of the procedure, function, or trigger being debugged, instrumented with extra code to keep track of the current line being stepped into and the values of all the local variables. Any other stored procedures, functions, or triggers called from the routine being debugged are instrumented the same way. The debug versions of the routines are prepared for you automatically, and when the debug session ends (by either pressing F5 or Shift+F5), the original versions of the routines are automatically restored.

A copy of the original version of each instrumented routine (the version without instrumentation) is stored in the AppData\Roaming\MySqlDebuggerCache folder for the current Windows user (the path returned by calling System.Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) in .NET, plus appending MySqlDebuggerCache. There is one file for each instrumented routine, named routine_name.mysql. For example, in Windows 7, for a user named fergs, the path is C:\Users\fergs\AppData\Roaming\MySqlDebuggerCache.

Two threads are used, one for the debugger and one for the routine being debugged. The threads run in strict alternation, switching between the debugger and the routine as each statement is executed in the stored routine.

Basic Debugging Operations

The debugger has the same look and feel as the standard Visual Studio debuggers for C#, VB.NET or C++. In particular, the following are true:

Locals and Watches

Call Stack

Stepping

Breakpoints

Other Features

Limitations

Keyboard Shortcuts

The following list summarizes the keyboard shortcuts for debugging:

Tables, views, stored procedures, and functions can be cloned using the appropriate Clone command from the 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.

The editor is filled with values of the original object. You can modify these values in the usual manner.

To save the cloned object, use either Save or Save All buttons of the Visual Studio main toolbar, or press Control+S. Before changes are saved, you will be asked to confirm the execution of the corresponding SQL query in a confirmation dialog.

Tables, views, stored routines, triggers, and UDFs can be dropped with the appropriate Drop command selected 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.

You can only drop a single object at a time.

Connector/Net 6.0 introduced support for the ADO.NET Entity Framework. ADO.NET Entity Framework was included with .NET Framework 3.5 Service Pack 1, and Visual Studio 2008 Service Pack 1. ADO.NET Entity Framework was released on 11th August 2008.

ADO.NET Entity Framework provides an Object Relational Mapping (ORM) service, mapping the relational database schema to objects. The ADO.NET Entity Framework defines several layers, these can be summarized as:

Connector/Net integrates with Visual Studio 2008 to provide a range of helpful tools to assist the developer.

A full treatment of ADO.NET Entity Framework is beyond the scope of this manual. If you are unfamiliar with ADO.NET, review the Microsoft ADO.NET Entity Framework documentation.

Tutorials on getting started with ADO.NET Entity Framework are available. See Section 22.2.4.5, "Tutorial: Using an Entity Framework Entity as a Windows Forms Data Source" and Section 22.2.4.6, "Tutorial: Databinding in ASP.NET using LINQ on Entities".

MySQL Connector/Net 6.1 introduced the MySQL Website Configuration Tool. This is a facility available in Visual Studio that enables you to configure the Membership, Role, Session State and Profile Provider, without editing configuration files. You set your configuration options within the tool, and the tool modifies your web.config file accordingly.

The MySQL Website Configuration Tool appears as a small icon on the Solution Explorer toolbar in Visual Studio, as show by the following screenshot:

Figure 22.29. MySQL Website Configuration Tool

Clicking the Website Configuration Tool icon launches the wizard and displays the first screen:

Figure 22.30. MySQL Website Configuration Tool - Membership

This allows you to enable use of the MySQL Membership Provider. Click the check box to enable this. You can now enter the name of the application that you are creating the configuration for. You can also enter a description for the application.

You can then click the Edit... button to launch the Connection String Editor:

Figure 22.31. MySQL Website Configuration Tool - Connection String Editor

Note that if you have already defined a connection string for the providers manually in web.config, or previously using the tool, this will be automatically loaded and displayed, and can then be modified in this dialog.

You can also ensure that the necessary schemas are created automatically for you by selecting the Autogenerate Schema check box. These schemas are used to store membership information. The database used to storage is the one specified in the connection string.

You can also ensure that exceptions generated by the application will be written to the Windows event log by selecting the Write exceptions to event log check box.

Clicking the Advanced... button launches a dialog that enables you to set Membership Options. These options dictate such variables as password length required when a user signs up, whether the password is encrypted and whether the user can reset their password or not.

Figure 22.32. MySQL Website Configuration Tool - Advanced Options

Once information has been set up as required for configuration of the Membership Provider, the Next button can be clicked to display the Roles Provider screen:

Figure 22.33. MySQL Website Configuration Tool - Roles

Again the connection string can be edited, a description added and Autogenerate Schema can be enabled before clicking Next to go to the Profiles Provider screen:

Figure 22.34. MySQL Website Configuration Tool - Profiles

This screen display similar options to the previous screens.

Click Next to proceed to the Session State configuration page:

Figure 22.35. MySQL Website Configuration Tool - Session State

Once you have set up the Session State Provider as required, click Finish to exit the wizard.

At this point, select the Authentication Type to From Internet. Launch the ASP.NET Configuration Tool and select the Security tab. Click the Select authentication type link and ensure that the From the internet radio button is selected. You can now examine the database you created to store membership information. All the necessary tables will have been created for you:

Figure 22.36. MySQL Website Configuration Tool - Tables

MySQL Connector/Net 6.3.2 introduced a new MySQL SQL Editor. The easiest way to invoke the editor is by selecting the New, File menu item from the Visual Studio main menu. This displays the New File dialog:

Figure 22.37. MySQL SQL Editor - New File

From the New File dialog, select the MySQL template, and then double-click the MySQL SQL Script document, or click the Open button.

The MySQL SQL Editor will be displayed. You can now enter SQL code as required, or connect to a MySQL server. Click the Connect to MySQL button in the MySQL SQL Editor toolbar. You can enter the connection details into the Connect to MySQL dialog that is displayed. You can enter the server name, user ID, password and database to connect to, or click the Advanced button to select other connection string options. Click the Connect button to connect to the MySQL server. To execute your SQL code against the server, click the Run SQL button on the toolbar.

Figure 22.38. MySQL SQL Editor - Query

The results from any queries are displayed on the Results tab. Any errors are displayed on the Messages tab.

MySQL Connector/Net 6.3 introduced the ability to convert an Entity Framework model to MySQL DDL code. Starting with a blank model, you can develop an entity model in Visual Studio's designer. Once the model is created, you can select the model's properties, and in the Database Script Generation category of the model's properties, the property DDL Generation can be found. Select the value SSDLToMySQL.tt(VS) from the drop-down listbox.

Figure 22.39. DDL T4 Template Macro - Model Properties

Please click here to enlarge image

Right-clicking the model design area displays a context-sensitive menu. Selecting Generate Database from Model from the menu displays the Generate Database Wizard. The wizard can then be used to generate MySQL DDL code.

Figure 22.40. DDL T4 Template Macro - Generate Database Wizard

This section provides a gentle introduction to programming with Connector/Net. The example code is written in C#, and is designed to work on both Microsoft .NET Framework and Mono.

This tutorial is designed to get you up and running with Connector/Net as quickly as possible, it does not go into detail on any particular topic. However, the following sections of this manual describe each of the topics introduced in this tutorial in more detail. In this tutorial you are encouraged to type in and run the code, modifying it as required for your setup.

This tutorial assumes you have MySQL and Connector/Net already installed. It also assumes that you have installed the World example database, which can be downloaded from the MySQL Documentation page. You can also find details on how to install the database on the same page.

using System;using System.Data;using MySql.Data;using MySql.Data.MySqlClient;public class Tutorial1{ public static void Main() { string connStr = "server=localhost;user=root;database=world;port=3306;password=******;"; MySqlConnection conn = new MySqlConnection(connStr); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); // Perform database operations } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); Console.WriteLine("Done."); }}

using System;using System.Data;using MySql.Data;using MySql.Data.MySqlClient;public class Tutorial2{ public static void Main() { string connStr = "server=localhost;user=root;database=world;port=3306;password=******;"; MySqlConnection conn = new MySqlConnection(connStr); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent='Oceania'"; MySqlCommand cmd = new MySqlCommand(sql, conn); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(rdr[0]+" -- "+rdr[1]); } rdr.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); Console.WriteLine("Done."); }}

using System;using System.Data;using MySql.Data;using MySql.Data.MySqlClient;public class Tutorial3{ public static void Main() { string connStr = "server=localhost;user=root;database=world;port=3306;password=******;"; MySqlConnection conn = new MySqlConnection(connStr); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string sql = "INSERT INTO Country (Name, HeadOfState, Continent) VALUES ('Disneyland','Mickey Mouse', 'North America')"; MySqlCommand cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); Console.WriteLine("Done."); }}

using System;using System.Data;using MySql.Data;using MySql.Data.MySqlClient;public class Tutorial4{ public static void Main() { string connStr = "server=localhost;user=root;database=world;port=3306;password=******;"; MySqlConnection conn = new MySqlConnection(connStr); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string sql = "SELECT COUNT(*) FROM Country"; MySqlCommand cmd = new MySqlCommand(sql, conn); object result = cmd.ExecuteScalar(); if (result != null) { int r = Convert.ToInt32(result); Console.WriteLine("Number of countries in the World database is: " + r); } } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); Console.WriteLine("Done."); }}

22.2.4.1.3. Working with Decoupled Data

Previously, when using MySqlDataReader, the connection to the database was continually maintained, unless explicitly closed. It is also possible to work in a manner where a connection is only established when needed. For example, in this mode, a connection could be established to read a chunk of data, the data could then be modified by the application as required. A connection could then be reestablished only if and when the application writes data back to the database. This decouples the working data set from the database.

This decoupled mode of working with data is supported by Connector/Net. There are several parts involved in allowing this method to work:

• Data Set - The Data Set is the area in which data is loaded to read or modify it. A DataSet object is instantiated, which can store multiple tables of data.

• Data Adapter - The Data Adapter is the interface between the Data Set and the database itself. The Data Adapter is responsible for efficiently managing connections to the database, opening and closing them as required. The Data Adapter is created by instantiating an object of the MySqlDataAdapter class. The MySqlDataAdapter object has two main methods: Fill which reads data into the Data Set, and Update, which writes data from the Data Set to the database.

• Command Builder - The Command Builder is a support object. The Command Builder works in conjunction with the Data Adapter. When a MySqlDataAdapter object is created, it is typically given an initial SELECT statement. From this SELECT statement the Command Builder can work out the corresponding INSERT, UPDATE and DELETE statements that would be required to update the database. To create the Command Builder, an object of the class MySqlCommandBuilder is created.

Each of these classes will now be discussed in more detail.

Instantiating a DataSet object

A DataSet object can be created simply, as shown in the following example code snippet:

DataSet dsCountry;...dsCountry = new DataSet();

Although this creates the DataSet object, it has not yet filled it with data. For that, a Data Adapter is required.

Instantiating a MySqlDataAdapter object

The MySqlDataAdapter can be created as illustrated by the following example:

MySqlDataAdapter daCountry;...string sql = "SELECT Code, Name, HeadOfState FROM Country WHERE Continent='North America'";daCountry = new MySqlDataAdapter (sql, conn);

Note, the MySqlDataAdapter is given the SQL specifying the data to work with.

Instantiating a MySqlCommandBuilder object

Once the MySqlDataAdapter has been created, it is necessary to generate the additional statements required for inserting, updating and deleting data. There are several ways to do this, but in this tutorial you will see how this can most easily be done with MySqlCommandBuilder. The following code snippet illustrates how this is done:

MySqlCommandBuilder cb = new MySqlCommandBuilder(daCountry);

Note that the MySqlDataAdapter object is passed as a parameter to the command builder.

Filling the Data Set

To do anything useful with the data from your database, you need to load it into a Data Set. This is one of the jobs of the MySqlDataAdapter object, and is carried out with its Fill method. The following example code illustrates this:

DataSet dsCountry;...dsCountry = new DataSet();...daCountry.Fill(dsCountry, "Country");

Note the Fill method is a MySqlDataAdapter method, the Data Adapter knows how to establish a connec tion with the database and retrieve the required data, and then populates the Data Set when the Fill method is called. The second parameter "Country" is the table in the Data Set to update.

Updating the Data Set

The data in the Data Set can now be manipulated by the application as required. At some point, changes to data will need to be written back to the database. This is achieved through a MySqlDataAdapter method, the Update method.

daCountry.Update(dsCountry, "Country");

Again, the Data Set and the table within the Data Set to update are specified.

Working Example

The interactions between the DataSet, MySqlDataAdapter and MySqlCommandBuilder classes can be a little confusing, so their operation can perhaps be best illustrated by working code.

In this example, data from the World database is read into a Data Grid View control. Here, the data can be viewed and changed before clicking an update button. The update button then activates code to write changes back to the database. The code uses the principles explained above. The application was built using the Microsoft Visual Studio to place and create the user interface controls, but the main code that uses the key classes described above is shown below, and is portable.

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using MySql.Data;using MySql.Data.MySqlClient;namespace WindowsFormsApplication5{ public partial class Form1 : Form { MySqlDataAdapter daCountry; DataSet dsCountry; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { string connStr = "server=localhost;user=root;database=world;port=3306;password=******;"; MySqlConnection conn = new MySqlConnection(connStr); try { label2.Text = "Connecting to MySQL..."; string sql = "SELECT Code, Name, HeadOfState FROM Country WHERE Continent='North America'"; daCountry = new MySqlDataAdapter (sql, conn); MySqlCommandBuilder cb = new MySqlCommandBuilder(daCountry); dsCountry = new DataSet(); daCountry.Fill(dsCountry, "Country"); dataGridView1.DataSource = dsCountry; dataGridView1.DataMember = "Country"; } catch (Exception ex) { label2.Text = ex.ToString(); } } private void button1_Click(object sender, EventArgs e) { daCountry.Update(dsCountry, "Country"); label2.Text = "MySQL Database Updated!"; } }}

The application running is shown below:

Figure 22.41. World Database Application

string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent = "+user_continent;

string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent = @Continent";

cmd.Parameters.AddWithValue("@Continent", "North America");

using System;using System.Data;using MySql.Data;using MySql.Data.MySqlClient;public class Tutorial5{ public static void Main() { string connStr = "server=localhost;user=root;database=world;port=3306;password=******;"; MySqlConnection conn = new MySqlConnection(connStr); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent=@Continent"; MySqlCommand cmd = new MySqlCommand(sql, conn); Console.WriteLine("Enter a continent e.g. 'North America','Europe': "); string user_input = Console.ReadLine(); cmd.Parameters.AddWithValue("@Continent", user_input); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(rdr["Name"]+" --- "+rdr["HeadOfState"]); } rdr.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); Console.WriteLine("Done."); }}

DELIMITER //CREATE PROCEDURE country_hos(IN con CHAR(20))BEGIN  SELECT Name, HeadOfState FROM Country  WHERE Continent = con;END //DELIMITER ;

CALL country_hos('Europe');

string rtn = "country_hos";MySqlCommand cmd = new MySqlCommand(rtn, conn);cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@con", "Europe");

using System;using System.Data;using MySql.Data;using MySql.Data.MySqlClient;public class Tutorial6{ public static void Main() { string connStr = "server=localhost;user=root;database=world;port=3306;password=******;"; MySqlConnection conn = new MySqlConnection(connStr); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string rtn = "country_hos"; MySqlCommand cmd = new MySqlCommand(rtn, conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@con", "Europe"); MySqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Console.WriteLine(rdr[0] + " --- " + rdr[1]); } rdr.Close(); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); Console.WriteLine("Done."); }}

Many web sites feature the facility for the user to create a user account. They can then log into the web site and enjoy a personalized experience. This requires that the developer creates database tables to store user information, along with code to gather and process this data. This represents a burden on the developer, and there is the possibility for security issues to creep into the developed code. However, ASP.NET 2.0 introduced the Membership system. This system is designed around the concept of Membership, Profile and Role Providers, which together provide all of the functionality to implement a user system, that previously would have to have been created by the developer from scratch.

Currently, MySQL Connector/Net provides Membership, Role, Profile and Session State Providers.

This tutorial shows you how to set up your ASP.NET web application to use the MySQL Connector/Net Membership and Role Providers. It assumes that you have MySQL Server installed, along with MySQL Connector/Net and Microsoft Visual Studio. This tutorial was tested with MySQL Connector/Net 6.0.4 and Microsoft Visual Studio 2008 Professional Edition. It is recommended you use 6.0.4 or above for this tutorial.

In this tutorial, you have seen how to set up the MySQL Connector/Net Membership and Role Providers for use in your ASP.NET web application.

MySQL Connector/Net from version 6.1 has included a MySQL Session State Provider. This provider enables you to store session state in a MySQL database. The following tutorial shows you how to prepare to use the MySQL Session State Provider, and then store session data into the MySQL database. This tutorial uses Microsoft Visual Studio 2008 Professional Edition, MySQL Connector/Net 6.1.1 and MySQL Server 5.1. This tutorial also assumes you have created an empty database, for example test, where you will store session data. You could do this using the mysql command interpreter.

At this point, you are ready to use the MySQL database to store session data. To test that the set up has worked, you can write a simple program that uses session variables.

At this point, you have installed the Session State Provider and carried out a preliminary test of the installation. You will now work a bit more with the Session State Provider.

In this part of the tutorial, you will set and retrieve a session variable. You can work with your existing project.

This illustrates that the session state data is not destroyed when a page is reloaded.

This tutorial shows you how to use the MySQL Profile Provider to store user profile information in a MySQL database. The tutorial uses MySQL Connector/Net 6.1.1, MySQL Server 5.1 and Microsoft Visual Studio 2008 Professional Edition.

Many modern web sites allow the user to create a personal profile. This requires a significant amount of code, but ASP.NET reduces this considerable by including the functionality in its Profile classes. The Profile Provider provides an abstraction between these classes and a data source. The MySQL Profile Provider enables profile data to be stored in a MySQL database. This enables the profile properties to be written to a persistent store, and be retrieved when required. The Profile Provider also enables profile data to be managed effectively, for example it enables profiles that have not been accessed since a specific date to be deleted.

The following steps show you how you can select the MySQL Profile Provider.

At this point you are now ready to start using the MySQL Profile Provider. With the following steps you can carry out a preliminary test of your installation.

Now that the simple profile has been defined in web.config, the next step is to write some code to test the profile.

In this tutorial you have seen how to using the MySQL Profile Provider with MySQL Connector/Net.

In this tutorial you will learn how to create a Windows Forms Data Source from an Entity in an Entity Data Model. This tutorial assumes that you have installed the World example database, which can be downloaded from the MySQL Documentation page. You can also find details on how to install the database on the same page. It will also be convenient for you to create a connection to the World database after it is installed. For instructions on how to do this see Section 22.2.3.1, "Making a Connection".

Creating a new Windows Forms application

The first step is to create a new Windows Forms application.

Adding an Entity Data Model

You will now add an Entity Data Model to your solution.

Adding a new Data Source

You will now add a new Data Source to your project and see how it can be used to read and write to the database.

Using the Data Source in a Windows Form

You will now learn how to use the Data Source in a Windows Form.

Adding Code to Populate the Data Grid View

You are now ready to add code to ensure that the Data Grid View control will be populated with data from the City database table.

Adding Code to Save Changes to the Database

You will now add code to enable you to save changes to the database.

The Binding source component ensures that changes made in the Data Grid View control are also made to the Entity classes bound to it. However, that data needs to be saved back from the entities to the database itself. This can be achieved by the enabling of the Save button in the Navigator control, and the addition of some code.

In this tutorial you create an ASP.NET web page that binds LINQ queries to entities using the Entity Framework mapping.

If you have not already done so, install the World example database prior to attempting this tutorial. See the tutorial Section 22.2.4.5, "Tutorial: Using an Entity Framework Entity as a Windows Forms Data Source" for instructions on downloading and installing this database.

Creating an ASP.NET web site

In this part of the tutorial, you create an ASP.NET web site. The web site uses the World database. The main web page features a drop down list from which you can select a country. Data about that country's cities is then displayed in a grid view control.

At this stage you have a web site that will build, but further functionality is required. The next step will be to use the Entity Framework to create a mapping from the World database into entities that you can control programmatically.

Creating an ADO.NET Entity Data Model

In this stage of the tutorial you will add an ADO.NET Entity Data Model to your project, using the World database at the storage level. The procedure for doing this is described in the tutorial Section 22.2.4.5, "Tutorial: Using an Entity Framework Entity as a Windows Forms Data Source", and so will not be repeated here.

Populating a Drop Data List Box with using the results of a entity LINQ query

In this part of the tutorial you will write code to populate the DropDownList control. When the web page loads the data to populate the list will be achieved by using the results of a LINQ query on the model created previously.

At this point you have a working Drop Down List control, populated by a LINQ query on your entity data model.

Populating a Grid View control using an entity LINQ query

In the last part of this tutorial you will populate the Grid View Control using a LINQ query on your entity data model.

In this tutorial you have seen how to create an ASP.NET web site, you have also seen how you can access a MySQL database using LINQ queries on an entity data model.

In this tutorial you will learn how you can use MySQL Connector/Net to connect to a MySQL server configured to use SSL. Support for SSL client certificates was added with MySQL Connector/Net 6.2.

MySQL Server uses the PEM format for certificates and private keys. This tutorial will use the test certificates from the server test suite by way of example. You can obtain the MySQL Server source code from MySQL Downloads. The certificates can be found in the directory ./mysql-test/std_data.

To carry out the steps in this tutorial, you must have Open SSL installed. This can be downloaded for Microsoft Windows at no charge from Shining Light Productions.

Further details on the connection string options used in this tutorial can be found at Section 22.2.6, "Connector/Net Connection String Options Reference".

Configuring the MySQL Server to use SSL

Creating a certificate file to use with the .NET client

Connecting to the server using a file-based certificate

Connecting to the server using a store-based certificate

Examine certificates in the Personal Store

Certificate Thumbprint Parameter

If you have a large number of certificates in your store, and many have the same Issuer, this can be a source of confusion and result in the wrong certificate being used. To alleviate this situation, there is an optional Certificate Thumbprint parameter that can additionally be specified as part of the connection string. As mentioned before, you can double-click a certificate in the Microsoft Management Console to display the certificate's details. When the Certificate dialog is displayed click the Details tab and scroll down to see the thumbprint. The thumbprint will typically be a number such as 47 94 36 00 9a 40 f3 01 7a 14 5c f8 47 9e 76 94 d7 aa de f0. This thumbprint can be used in the connection string, as the following code illustrates:

using (MySqlConnection connection = new MySqlConnection(   "database=test;user=sslclient;" +   "Certificate Store Location=CurrentUser;" +   "Certificate Thumbprint=479436009a40f3017a145cf8479e7694d7aadef0;"+   "SSL Mode=Required")) { connection.Open(); }

Spaces in the thumbprint parameter are optional and the value is case-insensitive.

This tutorial teaches you how to use the MySqlScript class. This class enables you to execute a series of statements. Depending on the circumstances, this can be more convenient than using the MySqlCommand approach.

Further details of the MySqlScript class can be found in the reference documentation supplied with MySQL Connector/Net.

To run the example programs in this tutorial, set up a simple test database and table using the mysql Command Line Client or MySQL Workbench. Commands for the mysql Command Line Client are given here:

CREATE DATABASE TestDB;USE TestDB;CREATE TABLE TestTable (id INT NOT NULL PRIMARY KEY  AUTO_INCREMENT, name VARCHAR(100));

The main method of the MySqlScript class is the Execute method. This method causes the script (sequence of statements) assigned to the Query property of the MySqlScript object to be executed. Note the Query property can be set through the MySqlScript constructor or using the Query property. Execute returns the number of statements executed.

The MySqlScript object will execute the specified script on the connection set using the Connection property. Again, this property can be set directly or through the MySqlScript constructor. The following code snippets illustrate this:

string sql = "SELECT * FROM TestTable";...MySqlScript script = new MySqlScript(conn, sql);...MySqlScript script = new MySqlScript();script.Query = sql;script.Connection = conn;...script.Execute();

The MySqlScript class has several events associated with it. There are:

It is possible to assign event handlers to each of these events. These user-provided routines are called back when the connected event occurs. The following code shows how the event handlers are set up.

script.Error += new MySqlScriptErrorEventHandler(script_Error);script.ScriptCompleted += new EventHandler(script_ScriptCompleted);script.StatementExecuted += new MySqlStatementExecutedEventHandler(script_StatementExecuted);

In VisualStudio, you can save typing by using tab completion to fill out stub routines. Start by typing, for example, "script.Error +=". Then press TAB, and then press TAB again. The assignment is completed, and a stub event handler created. A complete working example is shown below:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using MySql.Data;using MySql.Data.MySqlClient;namespace MySqlScriptTest{ class Program { static void Main(string[] args) { string connStr = "server=localhost;user=root;database=TestDB;port=3306;password=******;"; MySqlConnection conn = new MySqlConnection(connStr); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string sql = "INSERT INTO TestTable(name) VALUES ('Superman');" + "INSERT INTO TestTable(name) VALUES ('Batman');" + "INSERT INTO TestTable(name) VALUES ('Wolverine');" + "INSERT INTO TestTable(name) VALUES ('Storm');"; MySqlScript script = new MySqlScript(conn, sql); script.Error += new MySqlScriptErrorEventHandler(script_Error); script.ScriptCompleted += new EventHandler(script_ScriptCompleted); script.StatementExecuted += new MySqlStatementExecutedEventHandler(script_StatementExecuted); int count = script.Execute(); Console.WriteLine("Executed " + count + " statement(s)."); Console.WriteLine("Delimiter: " + script.Delimiter); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); Console.WriteLine("Done."); } static void script_StatementExecuted(object sender, MySqlScriptEventArgs args) { Console.WriteLine("script_StatementExecuted"); } static void script_ScriptCompleted(object sender, EventArgs e) { /// EventArgs e will be EventArgs.Empty for this method Console.WriteLine("script_ScriptCompleted!"); } static void script_Error(Object sender, MySqlScriptErrorEventArgs args) { Console.WriteLine("script_Error: " + args.Exception.ToString()); } }}

Note that in the script_ScriptCompleted event handler, the EventArgs parameter e will be EventArgs.Empty. In the case of the ScriptCompleted event there is no additional data to be obtained, which is why the event object is EventArgs.Empty.

CREATE PROCEDURE test_routine() BEGIN SELECT name FROM TestTable ORDER BY name; SELECT COUNT(name) FROM TestTable;END

CREATE PROCEDURE test_routine() BEGIN SELECT name FROM TestTable ORDER BY name;

string sql = "DROP PROCEDURE IF EXISTS test_routine??" + "CREATE PROCEDURE test_routine() " +  "BEGIN " +  "SELECT name FROM TestTable ORDER BY name;" +  "SELECT COUNT(name) FROM TestTable;" + "END??" + "CALL test_routine()";

using System;using System.Collections.Generic;using System.Linq;using System.Text;using MySql.Data;using MySql.Data.MySqlClient;namespace ConsoleApplication8{ class Program { static void Main(string[] args) { string connStr = "server=localhost;user=root;database=TestDB;port=3306;password=******;"; MySqlConnection conn = new MySqlConnection(connStr); try { Console.WriteLine("Connecting to MySQL..."); conn.Open(); string sql = "DROP PROCEDURE IF EXISTS test_routine??" + "CREATE PROCEDURE test_routine() " + "BEGIN " + "SELECT name FROM TestTable ORDER BY name;" + "SELECT COUNT(name) FROM TestTable;" + "END??" + "CALL test_routine()"; MySqlScript script = new MySqlScript(conn); script.Query = sql; script.Delimiter = "??"; int count = script.Execute(); Console.WriteLine("Executed " + count + " statement(s)"); script.Delimiter = ";"; Console.WriteLine("Delimiter: " + script.Delimiter); Console.WriteLine("Query: " + script.Query); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } conn.Close(); Console.WriteLine("Done."); } }}

In this tutorial, you will learn how to create MySQL DDL from an Entity Framework model. Use Visual Studio 2010 and MySQL Connector/Net 6.3 to carry out this tutorial.

You have seen how to create MySQL DDL code from an Entity Framework model.