Chapter 4 Connector/NET Tutorials

Table of Contents

4.1 Tutorial: An Introduction to Connector/NET Programming
4.1.1 The MySqlConnection Object
4.1.2 The MySqlCommand Object
4.1.3 Working with Decoupled Data
4.1.4 Working with Parameters
4.1.5 Working with Stored Procedures
4.2 Tutorial: Connector/NET ASP.NET Membership and Role Provider
4.3 Tutorial: Connector/NET ASP.NET Profile Provider
4.4 Tutorial: Web Parts Personalization Provider
4.5 Tutorial: Simple Membership Web Provider
4.6 Tutorial: Using an Entity Framework Entity as a Windows Forms Data Source
4.7 Tutorial: Data Binding in ASP.NET Using LINQ on Entities
4.8 Tutorial: Generating MySQL DDL from an Entity Framework Model
4.9 Tutorial: Basic CRUD Operations with Connector/NET
4.10 Tutorial: Using SSL with Connector/NET
4.11 Tutorial: Using MySqlScript
4.11.1 Using Delimiters with MySqlScript

The following MySQL Connector/NET tutorials illustrate how to develop MySQL programs using technologies such as Visual Studio, C#, ASP.NET, and the .NET, .NET Core, and Mono frameworks. Work through the first tutorial to verify that you have the right software components installed and configured, then choose other tutorials to try depending on the features you intend to use in your applications.

4.1 Tutorial: An Introduction to Connector/NET Programming

This section provides a gentle introduction to programming with MySQL Connector/NET. The code example 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 database sample, which can be downloaded from the MySQL Documentation page. You can also find details on how to install the database on the same page.

Note

Before compiling the code example, make sure that you have added References to your project as required. The References required are System, System.Data and MySql.Data.

4.1.1 The MySqlConnection Object

For your MySQL Connector/NET application to connect to a MySQL database, it must establish a connection by using a MySqlConnection object.

The MySqlConnection constructor takes a connection string as one of its parameters. The connection string provides necessary information to make the connection to the MySQL database. The connection string is discussed more fully in Section 5.1, “Connecting to MySQL Using Connector/NET”. For a list of supported connection string options, see Chapter 6, Connector/NET 6.10 Connection-String Options Reference.

The following code shows how to create a connection object/

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.");
    }
}

When the MySqlConnection constructor is invoked, it returns a connection object, which is used for subsequent database operations. Open the connection before any other operations take place. Before the application exits, close the connection to the database by calling Close on the connection object.

Sometimes an attempt to perform an Open on a connection object can fail, generating an exception that can be handled using standard exception handling code.

In this section you have learned how to create a connection to a MySQL database, and open and close the corresponding connection object.

4.1.2 The MySqlCommand Object

When a connection has been established with the MySQL database, the next step is do carry out the desired database operations. This can be achieved through the use of the MySqlCommand object.

You will see how to create a MySqlCommand object. After it has been created, there are three main methods of interest that you can call:

  • ExecuteReader to query the database. Results are usually returned in a MySqlDataReader object, created by ExecuteReader.

  • ExecuteNonQuery to insert, update, and delete data.

  • ExecuteScalar to return a single value.

Once a MySqlCommand object has been created, you will call one of the previous methods on it to carry out a database operation, such as perform a query. The results are usually returned into a MySqlDataReader object, and then processed, for example the results might be displayed. The following code demonstrates how this could be 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.");
    }
}

When a connection has been created and opened, the code then creates a MySqlCommand object. Then the SQL query to be executed is passed to the MySqlCommand constructor. The ExecuteReader method is then used to generate a MySqlReader object. The MySqlReader object contains the results generated by the SQL executed on the command object. Once the results have been obtained in a MySqlReader object, the results can be processed. In this case, the information is printed out by a while loop. Finally, the MySqlReader object is disposed of by running its Close method on it.

In the next example, you will see how to use the ExecuteNonQuery method.

The procedure for performing an ExecuteNonQuery method call is simpler, as there is no need to create an object to store results. This is because ExecuteNonQuery is only used for inserting, updating and deleting data. The following example illustrates a simple update to the Country table:

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.");
    }
}

The query is constructed, the command object created and the ExecuteNonQuery method called on the command object. You can access your MySQL database with the mysql command interpreter and verify that the update was carried out correctly.

Finally, you will see how the ExecuteScalar method can be used to return a single value. Again, this is straightforward, as a MySqlDataReader object is not required to store results, a simple variable will do. The following code illustrates how to use ExecuteScalar:

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.");
    }
}

This example uses a simple query to count the rows in the Country table. The result is obtained by calling ExecuteScalar on the command object.

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 MySQL 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 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

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 code example illustrates this point.

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

The Fill method is a MySqlDataAdapter method, and the Data Adapter knows how to establish a connection with the database and retrieve the required data, and then populate 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 previously. 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 previously is shown in the next code example, 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 following figure shows the application started. The World Database Application updated data in three columns: Code, Name, and HeadOfState.

Figure 4.1 World Database Application

Content is described in the surrounding text.

4.1.4 Working with Parameters

This part of the tutorial shows you how to use parameters in your MySQL Connector/NET application.

Although it is possible to build SQL query strings directly from user input, this is not advisable as it does not prevent erroneous or malicious information being entered. It is safer to use parameters as they will be processed as field data only. For example, imagine the following query was constructed from user input:

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

If the string user_continent came from a Text Box control, there would potentially be no control over the string entered by the user. The user could enter a string that generates a runtime error, or in the worst case actually harms the system. When using parameters it is not possible to do this because a parameter is only ever treated as a field parameter, rather than an arbitrary piece of SQL code.

The same query written using a parameter for user input is:

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

The parameter is preceded by an '@' symbol to indicate it is to be treated as a parameter.

As well as marking the position of the parameter in the query string, it is necessary to add a parameter to the Command object. This is illustrated by the following code snippet:

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

In this example the string "North America" is supplied as the parameter value statically, but in a more practical example it would come from a user input control.

A further example illustrates the complete process:

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.");
    }
}

In this part of the tutorial you have see how to use parameters to make your code more secure.

4.1.5 Working with Stored Procedures

This section illustrates how to work with stored procedures. Putting database-intensive operations into stored procedures lets you define an API for your database application. You can reuse this API across multiple applications and multiple programming languages. This technique avoids duplicating database code, saving time and effort when you make updates due to schema changes, tune the performance of queries, or add new database operations for logging, security, and so on. Before working through this tutorial, familiarize yourself with the CREATE PROCEDURE and CREATE FUNCTION statements that create different kinds of stored routines.

For the purposes of this tutorial, you will create a simple stored procedure to see how it can be called from MySQL Connector/NET. In the MySQL Client program, connect to the world database and enter the following stored procedure:

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

Test that the stored procedure works as expected by typing the following into the mysql command interpreter:

CALL country_hos('Europe');
Note

The stored routine takes a single parameter, which is the continent to restrict your search to.

Having confirmed that the stored procedure is present and correct, you can see how to access it from Connector/NET.

Calling a stored procedure from your Connector/NET application is similar to techniques you have seen earlier in this tutorial. A MySqlCommand object is created, but rather than taking an SQL query as a parameter, it takes the name of the stored procedure to call. Set the MySqlCommand object to the type of stored procedure, as shown by the following code snippet:

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

In this case, the stored procedure requires you to pass a parameter. This can be achieved using the techniques seen in the previous section on parameters, Section 4.1.4, “Working with Parameters”, as shown in the following code snippet:

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

The value of the parameter @con could more realistically have come from a user input control, but for simplicity it is set as a static string in this example.

At this point, everything is set up and you can call the routine using techniques also learned in earlier sections. In this case, the ExecuteReader method of the MySqlCommand object is used.

The following code shows the complete stored procedure example.

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.");
    }
}

In this section, you have seen how to call a stored procedure from Connector/NET. For the moment, this concludes our introductory tutorial on programming with Connector/NET.

4.2 Tutorial: Connector/NET ASP.NET Membership and Role Provider

Many websites feature the facility for the user to create a user account. They can then log into the website 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 Connector/NET Membership and Role Providers. It assumes that you have MySQL Server installed, along with Connector/NET and Microsoft Visual Studio. This tutorial was tested with 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.

  1. Create a new database in the MySQL Server using the MySQL Command-Line Client program (mysql), or other suitable tool. It does not matter what name is used for the database, but record it. You specify it in the connection string constructed later in this tutorial. This database contains the tables, automatically created for you later, used to store data about users and roles.

  2. Create a new ASP.NET website in Visual Studio. If you are not sure how to do this, refer to Section 4.7, “Tutorial: Data Binding in ASP.NET Using LINQ on Entities”, which demonstrates how to create a simple ASP.NET website.

  3. Add References to MySql.Data and MySql.Web to the website project.

  4. Locate the machine.config file on your system, which is the configuration file for the .NET Framework.

  5. Search the machine.config file to find the membership provider MySQLMembershipProvider.

  6. Add the attribute autogenerateschema="true". The appropriate section should now resemble the following example.

    Note

    For the sake of brevity, some information is excluded.

    <membership>
     <providers>
       <add name="AspNetSqlMembershipProvider"
         type="System.Web.Security.SqlMembershipProvider"
         ...
         connectionStringName="LocalSqlServer"
         ... />
       <add name="MySQLMembershipProvider"
         autogenerateschema="true"
         type="MySql.Web.Security.MySQLMembershipProvider, MySql.Web, Version=6.0.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"
         connectionStringName="LocalMySqlServer"
         ... />
     </providers>
    </membership>
    
    Note

    The connection string, LocalMySqlServer, connects to the MySQL server that contains the membership database.

    The autogenerateschema="true" attribute will cause Connector/NET to silently create, or upgrade, the schema on the database server, to contain the required tables for storing membership information.

  7. It is now necessary to create the connection string referenced in the previous step. Load the web.config file for the website into Visual Studio.

  8. Locate the section marked <connectionStrings>. Add the following connection string information.

    <connectionStrings>
      <remove name="LocalMySqlServer"/>
      <add name="LocalMySqlServer"
           connectionString="Datasource=localhost;Database=users;uid=root;pwd=password"
           providerName="MySql.Data.MySqlClient"/>
    </connectionStrings>
    

    The database specified is the one created in the first step. You could alternatively have used an existing database.

  9. At this point build the solution to ensure no errors are present. This can be done by selecting Build, Build Solution from the main menu, or pressing F6.

  10. ASP.NET supports the concept of locally and remotely authenticated users. With local authentication the user is validated using their Windows credentials when they attempt to access the website. This can be useful in an Intranet environment. With remote authentication, a user is prompted for their login details when accessing the website, and these credentials are checked against the membership information stored in a database server such as MySQL Server. You will now see how to choose this form of authentication.

    Start the ASP.NET Website Administration Tool. This can be done quickly by clicking the small hammer/Earth icon in the Solution Explorer. You can also launch this tool by selecting Website and then ASP.NET Configuration from the main menu.

  11. In the ASP.NET Website Administration Tool click the Security tab and do the following:

    1. Click the User Authentication Type link.

    2. Select the From the internet option. The website will now need to provide a form to allow the user to enter their login details. The details will be checked against membership information stored in the MySQL database.

  12. You now need to specify the Role and Membership Provider to be used. Click the Provider tab and do the following:

    1. Click the Select a different provider for each feature (advanced) link.

    2. For Membership Provider, select the MySQLMembershipProvider option and for Role Provider, select the MySQLRoleProvider option.

  13. In Visual Studio, rebuild the solution by clicking Build and then Rebuild Solution from the main menu.

  14. Check that the necessary schema has been created. This can be achieved using SHOW DATABASES; and SHOW TABLES; the mysql command interpreter.

    mysql> SHOW DATABASES;
    +---------------------+
    | Database            |
    +---------------------+
    | information_schema  |
    | mysql               |
    | test                |
    | users               |
    | world               |
    +---------------------+
    5 rows in set (0.01 sec)
    
    mysql> SHOW TABLES;
    +---------------------------+
    | Tables_in_users           |
    +---------------------------+
    | my_aspnet_applications    |
    | my_aspnet_membership      |
    | my_aspnet_profiles        |
    | my_aspnet_roles           |
    | my_aspnet_schemaversion   |
    | my_aspnet_users           |
    | my_aspnet_usersinroles    |
    +---------------------------+
    7 rows in set (0.00 sec)
    
  15. Assuming all is present and correct, you can now create users and roles for your web application. The easiest way to do this is with the ASP.NET Website Administration Tool. However, many web applications contain their own modules for creating roles and users. For simplicity, the ASP.NET Website Administration Tool will be used in this tutorial.

  16. In the ASP.NET Website Administration Tool, click the Security tab. Now that both the Membership and Role Provider are enabled, you will see links for creating roles and users. Click the Create or Manage Roles link.

  17. You can now enter the name of a new Role and click Add Role to create the new Role. Create new Roles as required.

  18. Click the Back button.

  19. Click the Create User link. You can now fill in information about the user to be created, and also allocate that user to one or more Roles.

  20. Using the mysql command interpreter, you can check that your database has been correctly populated with the Membership and Role data.

    mysql> SELECT * FROM my_aspnet_users;      
    
    mysql>  SELECT * FROM my_aspnet_roles;      
    

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

4.3 Tutorial: Connector/NET ASP.NET Profile Provider

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.9.9, MySQL Server 5.7.21 and Microsoft Visual Studio 2017 Professional Edition.

Many modern websites 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:

  1. Create a new ASP.NET web project.

  2. Select the MySQL Website Configuration tool.

  3. In the MySQL Website Configuration tool navigate through the tool to the Profiles page.

  4. Select the Use MySQL to manage my profiles check box.

  5. Select the Autogenerate Schema check box.

  6. Click Edit and then configure a connection string for the database that will be used to store user profile information.

  7. Navigate to the last page of the tool and click Finish to save your changes and exit the tool.

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.

  1. Open your web.config file.

  2. Add a simple profile such as the following example.

    <system.web>
      <anonymousIdentification enabled="true"/>
      <profile defaultProvider="MySQLProfileProvider">
        ...
        <properties>
          <add name="Name" allowAnonymous="true"/>
          <add name="Age" allowAnonymous="true" type="System.UInt16"/>
          <group name="UI">
            <add name="Color" allowAnonymous="true" defaultValue="Blue"/>
            <add name="Style" allowAnonymous="true" defaultValue="Plain"/>
          </group>
        </properties>
      </profile>
      ...
    

    Setting anonymousIdentification to true enables unauthenticated users to use profiles. They are identified by a GUID in a cookie rather than by a user name.

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

  1. In Design View, design a simple page with the added controls. The following figure shows the Default.aspx tab open with various text box, list, and button controls.

    Figure 4.2 Simple Profile Application

    Content is described in the surrounding text.

    These will allow the user to enter some profile information. The user can also use the buttons to save their profile, clear the page, and restore their profile data.

  2. In the Code View add the following code snippet.

    ...
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            TextBox1.Text = Profile.Name;
            TextBox2.Text = Profile.Age.ToString();
            Label1.Text = Profile.UI.Color;
        }
    }
      
    // Store Profile
    protected void Button1_Click(object sender, EventArgs e)
    {
        Profile.Name = TextBox1.Text;
        Profile.Age = UInt16.Parse(TextBox2.Text);
    }
      
    // Clear Form
    protected void Button2_Click(object sender, EventArgs e)
    {
        TextBox1.Text = "";
        TextBox2.Text = "";
        Label1.Text = "";
    }
    
    // Retrieve Profile
    protected void Button3_Click(object sender, EventArgs e)
    {
        TextBox1.Text = Profile.Name;
        TextBox2.Text = Profile.Age.ToString();
        Label1.Text = Profile.UI.Color;
    }
    
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        Profile.UI.Color = DropDownList1.SelectedValue;
    }
    ...
    
  3. Save all files and build the solution to check that no errors have been introduced.

  4. Run the application.

  5. Enter your name, age, and select a color from the list. Now store this information in your profile by clicking Store Profile.

    Not selecting a color from the list uses the default color, Blue, that was specified in the web.config file.

  6. Click Clear Form to clear text from the text boxes and the label that displays your chosen color.

  7. Now click Retrieve Profile to restore your profile data from the MySQL database.

  8. Now exit the browser to terminate the application.

  9. Run the application again, which also restores your profile information from the MySQL database.

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

4.4 Tutorial: Web Parts Personalization Provider

MySQL Connector/NET provides a web parts personalization provider that allows you to use a MySQL server to store personalization data.

Note

This feature was added in Connector/NET 6.9.0.

This tutorial demonstrates how to configure the web parts personalization provider using Connector/NET.

Minimum Requirements

  • An ASP.NET website or web application with a membership provider

  • .NET Framework 3.0

  • MySQL 5.5

Configuring MySQL Web Parts Personalization Provider

To configure the provider, do the following:

  1. Add References to MySql.Data and MySql.Web to the website or web application project.

  2. Include a Connector/NET personalization provider into the system.web section in the web.config file.

    
    <webParts>
     <personalization defaultProvider="MySQLPersonalizationProvider">
      <providers>
        <clear/>
        <add name="MySQLPersonalizationProvider"
          type="MySql.Web.Personalization.MySqlPersonalizationProvider,
            MySql.Web, Version=6.9.3.0, Culture=neutral,
            PublicKeyToken=c5687fc88969c44d"
          connectionStringName="LocalMySqlServer"
          applicationName="/" />
      </providers>
      <authorization>
        <allow verbs="modifyState" users="*" />
        <allow verbs="enterSharedScope" users="*"/>
      </authorization>
     </personalization>
    </webParts>
    
    
    

Creating Web Part Controls

To create the web part controls, follow these steps:

  1. Create a web application using Connector/NET ASP.NET Membership. For information about doing this, see Section 4.2, “Tutorial: Connector/NET ASP.NET Membership and Role Provider”.

  2. Create a new ASP.NET page and then change to the Design view.

  3. From the Toolbox, drag a WebPartManager control to the page.

  4. Now define an HTML table with three columns and one row.

  5. From the WebParts Toolbox, drag and drop a WebPartZone control into both the first and second columns.

  6. From the WebParts Toolbox, drag and drop a CatalogZone with PageCatalogPart and EditorZone controls into the third column.

  7. Add controls to the WebPartZone, which should look similar to the following example:

    
    <table>
      <tr>
        <td>
          <asp:WebPartZone ID="LeftZone" runat="server" HeaderText="Left Zone">
            <ZoneTemplate>
              <asp:Label ID="Label1" runat="server" title="Left Zone">
                <asp:BulletedList ID="BulletedList1" runat="server">
                  <asp:ListItem Text="Item 1"></asp:ListItem>
                  <asp:ListItem Text="Item 2"></asp:ListItem>
                  <asp:ListItem Text="Item 3"></asp:ListItem>
                </asp:BulletedList>
              </asp:Label>
            </ZoneTemplate>
          </asp:WebPartZone>
        </td>
        <td>
          <asp:WebPartZone ID="MainZone" runat="server" HeaderText="Main Zone">
            <ZoneTemplate>
              <asp:Label ID="Label11" runat="server" title="Main Zone">
                <h2>This is the Main Zone</h2>
              </asp:Label>
            </ZoneTemplate>
          </asp:WebPartZone>
        </td>
        <td>
          <asp:CatalogZone ID="CatalogZone1" runat="server">
            <ZoneTemplate>
              <asp:PageCatalogPart ID="PageCatalogPart1" runat="server" />
            </ZoneTemplate>
          </asp:CatalogZone>
          <asp:EditorZone ID="EditorZone1" runat="server">
            <ZoneTemplate>
              <asp:LayoutEditorPart ID="LayoutEditorPart1" runat="server" />
              <asp:AppearanceEditorPart ID="AppearanceEditorPart1" runat="server" />
            </ZoneTemplate>
          </asp:EditorZone>
        </td>
      </tr>
    </table>
    
            
  8. Outside of the HTML table, add a drop-down list, two buttons, and a label as follows.

    
    <asp:DropDownList ID="DisplayModes" runat="server" AutoPostBack="True"
       OnSelectedIndexChanged="DisplayModes_SelectedIndexChanged">
    </asp:DropDownList>
    <asp:Button ID="ResetButton" runat="server" Text="Reset"
       OnClick="ResetButton_Click" />
    <asp:Button ID="ToggleButton" runat="server" OnClick="ToggleButton_Click"
       Text="Toggle Scope" />
    <asp:Label ID="ScopeLabel" runat="server"></asp:Label>
    
            
  9. The following code fills the list for the display modes, shows the current scope, resets the personalization state, toggles the scope (between user and the shared scope), and changes the display mode.

    
    public partial class WebPart : System.Web.UI.Page
    {
      protected void Page_Load(object sender, EventArgs e)
      {
        if (!IsPostBack)
        {
          foreach (WebPartDisplayMode mode in WebPartManager1.SupportedDisplayModes)
          {
            if (mode.IsEnabled(WebPartManager1))
            {
              DisplayModes.Items.Add(mode.Name);
            }
          }
        }
        ScopeLabel.Text = WebPartManager1.Personalization.Scope.ToString();
      }
    
      protected void ResetButton_Click(object sender, EventArgs e)
      {
        if (WebPartManager1.Personalization.IsEnabled &&
          WebPartManager1.Personalization.IsModifiable)
        {
          WebPartManager1.Personalization.ResetPersonalizationState();
        }
      }
    
      protected void ToggleButton_Click(object sender, EventArgs e)
      {
        WebPartManager1.Personalization.ToggleScope();
      }
    
      protected void DisplayModes_SelectedIndexChanged(object sender, EventArgs e)
      {
        var mode = WebPartManager1.SupportedDisplayModes[DisplayModes.SelectedValue];
        if (mode != null && mode.IsEnabled(WebPartManager1))
        {
          WebPartManager1.DisplayMode = mode;
        }
      }
    }
    
    

Testing Web Part Changes

Use the following steps to validate your changes:

  1. Run the application and open the web part page. The page should look like similar to the example shown in the following figure in which the Toggle Scope button is set to Shared. The page also includes the drop-down list, the Reset button, and the Left Zone and Main Zone controls.

    Figure 4.3 Web Parts Page

    Content is described in the surrounding text.

    Initially when the user account is not authenticated, the scope is Shared by default. The user account must be authenticated to change settings on the web-part controls. The following figure shows an example in which an authenticated user is able to customize the controls by using the Browse drop-down list. The options in the list are Design, Catalog, and Edit.

    Figure 4.4 Authenticated User Controls

    Content is described in the surrounding text.

  2. Click Toggle Scope to switch the application back to the shared scope.

  3. Now you can personalize the zones using the Edit or Catalog display modes at a specific user or all-users level. The next figure shows Catalog selected from the drop-down list, which include the Catalog Zone control that was added previously.

    Figure 4.5 Personalize Zones

    Content is described in the surrounding text.

4.5 Tutorial: Simple Membership Web Provider

This section documents the ability to use a Simple Membership Provider on MVC 4 templates. The configuration OAuth compatible for the application to login using external credentials from third party providers like Google, Facebook, Twitter, or others.

This tutorial creates an application using a Simple Membership Provider and then adds third-party (Google) OAuth authentication support.

Note

This feature was added in MySQL Connector/NET 6.9.0.

Requirements

  • Connector/NET 6.9.x or higher

  • .NET Framework 4.0 or higher

  • Visual Studio 2012 or higher

  • MVC 4

Creating and Configuring a New Project

To get started with a new project, do the following:

  1. Open Visual Studio, create a new project of ASP.NET MVC 4 Web Application type, and configure the project to use .NET Framework 4.5. The following figure shows and example of the New Project window with the items selected.

    Figure 4.6 Simple Membership: New Project

    Content is described in the surrounding text.

  2. Choose the template and view engine that you like. This tutorial uses the Internet Application Template with the Razor view engine (see the next figure). Optionally, you can add a unit test project by selecting Create a unit test project.

    Figure 4.7 Simple Membership: Choose Template and Engine

    Content is described in the surrounding text.

  3. Add references to the MySql.Data, MySql.Data.Entities, and MySql.Web assemblies. The assemblies chosen must match the .NET Framework and Entity Framework versions added to the project by the template.

  4. Add a valid MySQL connection string to the web.config file, similar to the following example.

    
    <add
      name="MyConnection"
      connectionString="server=localhost;UserId=root;password=pass;database=MySqlSimpleMembership;logging=true;port=3305"
      providerName="MySql.Data.MySqlClient"/>
    
    
  5. Under the <system.data> node, add configuration information similar to the following example.

    
    <membership defaultProvider="MySqlSimpleMembershipProvider">
    <providers>
    <clear/>
    <add
      name="MySqlSimpleMembershipProvider"
      type="MySql.Web.Security.MySqlSimpleMembershipProvider,MySql.Web,Version=6.9.2.0,Culture=neutral,PublicKeyToken=c5687fc88969c44d"
      applicationName="MySqlSimpleMembershipTest"
      description="MySQLdefaultapplication"
      connectionStringName="MyConnection"
      userTableName="MyUserTable"
      userIdColumn="MyUserIdColumn"
      userNameColumn="MyUserNameColumn"
      autoGenerateTables="True"/>
    </providers>
    </membership>
    
    
  6. Update the configuration with valid values for the following properties: connectionStringName, userTableName, userIdColumn, userNameColumn, and autoGenerateTables.

    • userTableName: Name of the table to store the user information. This table is independent from the schema generated by the provider, and it can be changed in the future.

    • userId: Name of the column that stores the ID for the records in the userTableName.

    • userName: Name of the column that stores the name/user for the records in the userTableName.

    • connectionStringName: This property must match a connection string defined in web.config file.

    • autoGenerateTables: This must be set to false if the table to handle the credentials already exists.

  7. Update your DBContext class with the connection string name configured.

  8. Open the InitializeSimpleMembershipAttribute.cs file from the Filters/ folder and locate the SimpleMembershipInitializer class. Then find the WebSecurity.InitializeDatabaseConnection method call and update the parameters with the configuration for connectionStringName, userTableName, userIdColumn, and userNameColumn.

  9. If the database configured in the connection string does not exist, then create it.

  10. After running the web application, the generated home page is displayed on success (see the figure that follows).

    Figure 4.8 Simple Membership: Generated Home Page

    Content is described in the surrounding text.

  11. If the application executed with success, then the generated schema will be similar to the following figure showing an object browser open to the tables.

    Figure 4.9 Simple Membership: Generated Schema and Tables

    Content is described in the surrounding text.

  12. To create a user login, click Register on the generated web page. Type the user name and password, and then execute the registration form. This action redirects you to the home page with the newly created user logged in.

    The data for the newly created user can be located in the UserProfile and Webpages_Membership tables.

Adding OAuth Authentication to a Project

OAuth is another authentication option for websites that use the Simple Membership Provider. A user can be validated using an external account like Facebook, Twitter, Google, and others.

Use the following steps to enable authentication using a Google account in the application:

  1. Locate the AuthConfig.cs file in the App_Start folder.

  2. As this tutorial uses Google, find the RegisterAuth method and uncomment the last line where it calls OauthWebSecurity.RegisterGoogleClient.

  3. Run the application. When the application is running, click Log in to open the log in page. Then, click Google under Use another service to log in (shown in the figure that follows).

    Figure 4.10 Simple Membership with OAuth: Google Service

    Content is described in the surrounding text.

  4. This action redirects to the Google login page (at google.com), and requests you to sign in with your Google account information.

  5. After submitting the correct credentials, a message requests permission for your application to access the user's information. Read the description and then click Accept to allow the quoted actions, and to redirect back to the login page of the application.

  6. The application now can register the account. The User name field will be filled in with the appropriate information (in this case, the email address that is associated with the Google account). Click Register to register the user with your application.

    Now the new user is logged into the application from an external source using OAuth. Information about the new user is stored in the UserProfile and Webpages_OauthMembership tables.

To use another external option to authenticate users, you must enable the client in the same class where we enabled the Google provider in this tutorial. Typically, providers require you to register your application before allowing OAuth authentication, and once registered they typically provide a token/key and an ID that must be used when registering the provider in the application.

4.6 Tutorial: Using an Entity Framework Entity as a Windows Forms Data Source

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 database sample, which can be downloaded from the MySQL Documentation page. You can also find details on how to install the database on the same page.

Creating a New Windows Forms Application

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

  1. In Visual Studio, select File, New, and then Project from the main menu.

  2. Choose the Windows Forms Application installed template. Click OK. The solution is created.

Adding an Entity Data Model

To add an Entity Data Model to your solution, do the following:

  1. In the Solution Explorer, right-click your application and select Add and then New Item. From Visual Studio installed templates, select ADO.NET Entity Data Model (see the figure that follows). Click Add.

    Figure 4.11 Add Entity Data Model

    Content is described in the surrounding text.

  2. You will now see the Entity Data Model Wizard. You will use the wizard to generate the Entity Data Model from the world database sample. Select the icon Generate from database. Click Next.

  3. You can now select the localhost(world) connection you made earlier to the database. Select the following items:

    • Yes, include the sensitive data in the connection string.

    • Save entity connection settings in App.config as:

      worldEntities

    If you have not already done so, you can create the new connection at this time by clicking New Connection (see the figure that follows). For additional instructions on creating a connection to a database see Making a Connection.

    Figure 4.12 Entity Data Model Wizard - Connection

    Content is described in the surrounding text.

    Make a note of the entity connection settings to be used in App.Config, as these will be used later to write the necessary control code. Click Next.

  4. The Entity Data Model Wizard connects to the database.

    As the next figure shows, you are then presented with a tree structure of the database. From here you can select the object you would like to include in your model. If you also created Views and Stored Routines, these items will be displayed along with any tables. In this example you just need to select the tables. Click Finish to create the model and exit the wizard.

    Figure 4.13 Entity Data Model Wizard - Objects and Settings

    Content is described in the surrounding text.

    Visual Studio generates a model with three tables (city, country, and countrylanguage) and then display it, as the following figure shows.

    Figure 4.14 Entity Data Model Diagram

    Content is described in the surrounding text.

  5. From the Visual Studio main menu, select Build and then Build Solution to ensure that everything compiles correctly so far.

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.

  1. From the Visual Studio main menu select Data and then Add New Data Source. You will be presented with the Data Source Configuration Wizard.

  2. Select the Object icon. Click Next.

  3. Select the object to bind to. Expand the tree as the next figure shows.

    In this tutorial, you will select the city table. After the city table has been selected click Next.

    Figure 4.15 Data Source Configuration Wizard

    Content is described in the surrounding text.

  4. The wizard will confirm that the city object is to be added. Click Finish.

  5. The city object will now appear in the Data Sources panel. If the Data Sources panel is not displayed, select Data and then Show Data Sources from the Visual Studio main menu. The docked panel will then be displayed.

Using the Data Source in a Windows Form

This step describes how to use the Data Source in a Windows Form.

  1. In the Data Sources panel select the Data Source you just created and drag and drop it onto the Form Designer. By default, the Data Source object will be added as a Data Grid View control as the following figure shows.

    Note

    The Data Grid View control is bound to cityBindingSource, and the Navigator control is bound to cityBindingNavigator.

    Figure 4.16 Data Form Designer

    Content is described in the surrounding text.

  2. Save and rebuild the solution before continuing.

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.

  1. Double-click the form to access its code.

  2. Add the following code to instantiate the Entity Data Model EntityContainer object and retrieve data from the database to populate the control.

    using System.Windows.Forms;
    
    namespace WindowsFormsApplication4
    {
       public partial class Form1 : Form
       {
          worldEntities we;
    
          public Form1()
          {
              InitializeComponent();
          }
     
          private void Form1_Load(object sender, EventArgs e)
          {
              we = new worldEntities();
              cityBindingSource.DataSource = we.city.ToList();
          }
       }
    }        
    
  3. Save and rebuild the solution.

  4. Run the solution. Confirm that the grid is populated (see the next figure for an example) and that you can navigate the database.

    Figure 4.17 The Populated Grid Control

    Content is described in the surrounding text.

Adding Code to Save Changes to the Database

This step explains how to add code that enables 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.

  1. In the Form Designer, click the Save icon in the Form toolbar and confirm that its Enabled property is set to True.

  2. Double-click the Save icon in the Form toolbar to display its code.

  3. Add the following (or similar) code to ensure that data is saved to the database when a user clicks the save button in the application.

          public Form1()
          {
              InitializeComponent();
          }
     
          private void Form1_Load(object sender, EventArgs e)
          {
              we = new worldEntities();
              cityBindingSource.DataSource = we.city.ToList();
          }
          private void cityBindingNavigatorSaveItem_Click(object sender, EventArgs e)
          {
              we.SaveChanges();
          }
       }
    }        
    
  4. When the code has been added, save the solution and then rebuild it. Run the application and verify that changes made in the grid are saved.

4.7 Tutorial: Data Binding in ASP.NET Using LINQ on Entities

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

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

Creating an ASP.NET Website

In this part of the tutorial, you create an ASP.NET website. The website uses the world database. The main web page features a drop-down list from which you can select a country. Data about the cities of that country is then displayed in a GridView control.

  1. From the Visual Studio main menu select File, New, and then Web Site.

  2. From the Visual Studio installed templates select ASP.NET Web Site. Click OK. You will be presented with the Source view of your web page by default.

  3. Click the Design view tab situated underneath the Source view panel.

  4. In the Design view panel, enter some text to decorate the blank web page.

  5. Click Toolbox. From the list of controls, select DropDownList. Drag and drop the control to a location beneath the text on your web page.

  6. From the DropDownList control context menu, ensure that the Enable AutoPostBack check box is enabled. This will ensure the control's event handler is called when an item is selected. The user's choice will in turn be used to populate the GridView control.

  7. From the Toolbox select the GridView control. Drag and drop the GridView control to a location just below the drop-down list you already placed.

    The following figure shows an example of the decorative text and two controls in the Design view tab. The added GridView control produced a grid with three columns (Column0, Column1, and Column3) and the string abc in each cell of the grid.

    Figure 4.18 Placed GridView Control

    Content is described in the surrounding text.

  8. At this point it is recommended that you save your solution, and build the solution to ensure that there are no errors.

  9. If you run the solution you will see that the text and drop down list are displayed, but the list is empty. Also, the grid view does not appear at all. Adding this functionality is described in the following sections.

At this stage you have a website 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 4.6, “Tutorial: Using an Entity Framework Entity as a Windows Forms Data Source”, and so will not be repeated here.

Populating a List Box by 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.

  1. In the Design view panel, double-click any blank area. This brings up the Page_Load method.

  2. Modify the relevant section of code according to the following listing example.

    ...
    public partial class _Default : System.Web.UI.Page
    {
        worldModel.worldEntities we;
    
        protected void Page_Load(object sender, EventArgs e)
        {
            we = new worldModel.worldEntities();
    
            if (!IsPostBack)
            {
                var countryQuery = from c in we.country
                                   orderby c.Name
                                   select new { c.Code, c.Name };
                DropDownList1.DataValueField = "Code";
                DropDownList1.DataTextField = "Name";
                DropDownList1.DataSource = countryQuery.ToList();
                DataBind();
            }
        }
    ...
    

    The list control only needs to be populated when the page first loads. The conditional code ensures that if the page is subsequently reloaded, the list control is not repopulated, which would cause the user selection to be lost.

  3. Save the solution, build it and run it. You should see that the list control has been populated. You can select an item, but as yet the GridView control does not appear.

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 by 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.

  1. In the Design view, double-click the DropDownList control. This action causes its SelectedIndexChanged code to be displayed. This method is called when a user selects an item in the list control and thus generates an AutoPostBack event.

  2. Modify the relevant section of code accordingly to the following listing example.

    ...
        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
        {
            var cityQuery = from c in we.city
                            where c.CountryCode == DropDownList1.SelectedValue
                            orderby c.Name
                            select new { c.Name, c.Population, c.CountryCode };
            GridView1.DataSource = cityQuery;
            DataBind();
        }
    ...
    

    The grid view control is populated from the result of the LINQ query on the entity data model.

  3. Save, build, and run the solution. As you select a country you will see its cities are displayed in the GridView control. The following figure shows Belgium selected from the list box and a table with three columns: Name, Population, and CountryCode.

    Figure 4.19 The Working Website

    Content is described in the surrounding text.

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

4.8 Tutorial: Generating MySQL DDL from an Entity Framework Model

In this tutorial, you will learn how to create MySQL DDL from an Entity Framework model. Minimally, you will need Microsoft Visual Studio 2017 and MySQL Connector/NET 6.10 to perform this tutorial.

  1. Create a new console application in Visual Studio 2017.

  2. Using the Solution Explorer, add a reference to MySql.Data.Entity.

  3. From the Solution Explorer select Add, New Item. In the Add New Item dialog select Online Templates. Select ADO.NET Entity Data Model and click Add. The Entity Data Model dialog will be displayed.

  4. In the Entity Data Model dialog select Empty Model. Click Finish. A blank model will be created.

  5. Create a simple model. A single Entity will do for the purposes of this tutorial.

  6. In the Properties panel select ConceptualEntityModel from the drop-down list.

  7. In the Properties panel, locate the DDL Generation Template in the category Database Script Generation.

  8. For the DDL Generation property select SSDLToMySQL.tt(VS) from the drop-down list.

  9. Save the solution.

  10. Right-click an empty space in the model design area. The context-sensitive menu will be displayed.

  11. From the context-sensitive menu select Generate Database from Model. The Generate Database Wizard dialog will be displayed.

  12. In the Generate Database Wizard dialog select an existing connection, or create a new connection to a server. Select an appropriate radio button to show or hide sensitive data. For the purposes of this tutorial you can select Yes (although you might skip this for commercial applications).

  13. Click Next. MySQL compatible DDL code will be generated. Click Finish to exit the wizard.

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

4.9 Tutorial: Basic CRUD Operations with Connector/NET

This tutorial provides instructions to get you started using MySQL as a document store with MySQL Connector/NET. For concepts and additional usage examples, see X DevAPI User Guide.

Minimum Requirements

  • MySQL 5.7.12 with the X Protocol enabled. For setup instructions, see Setting Up MySQL as a Document Store.

  • Connector/NET 8.0.8

  • Visual Studio 2013/2015/2017

  • world_x database sample

Import the Document Store Sample

A MySQL script is provided with data and a JSON collection. The sample contains the following:

  • Collection

    • countryinfo: Information about countries in the world.

  • Tables

    • country: Minimal information about countries of the world.

    • city: Information about some of the cities in those countries.

    • countrylanguage: Languages spoken in each country.

To install the world_x database sample, follow these steps:

  1. Download world_x.zip from http://dev.mysql.com/doc/index-other.html.

  2. Extract the installation archive to a temporary location such as /tmp/.

    Unpacking the archive results in two files, one of them named world_x.sql.

  3. Connect to the MySQL server using the MySQL Client with the following command:

    shell> mysql -u root -p
    

    Enter your password when prompted. A non-root account can be used as long as the account has privileges to create new databases. For more information about using the MySQL Client, see mysql — The MySQL Command-Line Client.

  4. Execute the world_x.sql script to create the database structure and insert the data as follows:

    mysql> SOURCE /temp/world_x.sql;
    

    Replace /temp/ with the path to the world_x.sql file on your system.

Add References to Required DLLs

Create a new Visual Studio Console Project targeting .NET Framework 4.5.2 (or later), .NET Core 1.1, or .NET Core 2.0. The code examples in this tutorial are shown in the C# language, but you can use any .NET language.

Add a reference in your project to the following DLLs:

  • MySql.Data.dll

  • Google.Protobuf.dll

Import Namespaces

Import the required namespaces by adding the following statements:

using MySqlX.XDevAPI;
using MySqlX.XDevAPI.Common;
using MySqlX.XDevAPI.CRUD;     

Create a Session

A session in the X DevAPI is a high-level database session concept that is different from working with traditional low-level MySQL connections. It is important to understand that this session is not the same as a traditional MySQL session. Sessions encapsulate one or more actual MySQL connections.

The following example opens a session, which you can use later to retrieve a schema and perform basic CRUD operations.

string schemaName = "world_x"; 
// Define the connection string
string connectionURI = "mysqlx://test:test@localhost:33060";  
Session session = MySQLX.GetSession(connectionURI);
// Get the schema object
Schema schema = session.GetSchema(schemaName);

Find a Row Within a Collection

After the session is instantiated, you can execute a find operation. The next example uses the session object that you created:

// Use the collection 'countryinfo'
var myCollection = schema.GetCollection("countryinfo");
var docParams = new DbDoc(new { name1 = "Albania", _id1 = "ALB" });

// Find a document
DocResult foundDocs = myCollection.Find("Name = :name1 || _id = :_id1").Bind(docParams).Execute();

while (foundDocs.Next())
{
	Console.WriteLine(foundDocs.Current["Name"]);
	Console.WriteLine(foundDocs.Current["_id"]);
}

Insert a New Document into a Collection

//Insert a new document with an identifier
var obj = new { _id = "UKN", Name = "Unknown" };
Result r = myCollection.Add(obj).Execute();

Update an Existing Document

// using the same docParams object previously created
docParams = new DbDoc(new { name1 = "Unknown", _id1 = "UKN" });
r = myCollection.Modify("_id = :Id").Bind("id", "UKN").Set("GNP", "3308").Execute();
if (r.AffectedItemsCount == 1)
{
  foundDocs = myCollection.Find("Name = :name1|| _id = :_id1").Bind(docParams).Execute();
  while (foundDocs.Next())
  {
     Console.WriteLine(foundDocs.Current["Name"]);
     Console.WriteLine(foundDocs.Current["_id"]);
     Console.WriteLine(foundDocs.Current["GNP"]);
  }
}

Delete a Specific Document

r = myCollection.Remove("_id = :id").Bind("id", "UKN").Execute();

Close the Session

session.Close();  

Complete Code Example

The following example shows the basic operations that you can perform with a collection.

using MySqlX.XDevAPI;
using MySqlX.XDevAPI.Common;
using MySqlX.XDevAPI.CRUD;
using System;

namespace MySQLX_Tutorial
{
  class Program
  {
    static void Main(string[] args)
    {
 
      string schemaName = "world_x";
      string connectionURI = "mysqlx://test:test@localhost:33060";  
      Session session = MySQLX.GetSession(connectionURI);
      Schema schema = session.GetSchema(schemaName);
 
      // Use the collection 'countryinfo'
      var myCollection = schema.GetCollection("countryinfo");
      var docParams = new DbDoc(new { name1 = "Albania", _id1 = "ALB" });
 
      // Find a document
      DocResult foundDocs = myCollection.Find("Name = :name1 || _id = :_id1").Bind(docParams).Execute();
 
      while (foundDocs.Next())
      {
        Console.WriteLine(foundDocs.Current["Name"]);
        Console.WriteLine(foundDocs.Current["_id"]);
      }
 
      //Insert a new document with an id
      var obj = new { _id = "UKN", Name = "Unknown" };
      Result r = myCollection.Add(obj).Execute();
 
      //update an existing document
      docParams = new DbDoc(new { name1 = "Unknown", _id1 = "UKN" });
      r = myCollection.Modify("_id = :Id").Bind("id", "UKN").Set("GNP", "3308").Execute();
      if (r.AffectedItemsCount == 1)
      {
        foundDocs = myCollection.Find("Name = :name1|| _id = :_id1").Bind(docParams).Execute();
        while (foundDocs.Next())
        {
          Console.WriteLine(foundDocs.Current["Name"]);
          Console.WriteLine(foundDocs.Current["_id"]);
          Console.WriteLine(foundDocs.Current["GNP"]);
        }
      }
 
      // delete a row in a document
      r = myCollection.Remove("_id = :id").Bind("id", "UKN").Execute();     

      //close the session
      session.Close();

      Console.ReadKey();

    }
  }
} 

4.10 Tutorial: Using SSL with Connector/NET

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 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 Chapter 6, Connector/NET 6.10 Connection-String Options Reference.

Configuring MySQL Server to Use SSL

  1. In the MySQL Server configuration file, set the SSL parameters as follows:

    ssl-ca=path/to/repo/mysql-test/std_data/cacert.pem
    ssl-cert=path/to/repo/mysql-test/std_data/server-cert.pem
    ssl-key=path/to/repo/mysql-test/std_data/server-key.pem
    

    Adjust the directories according to the location in which you installed the MySQL source code.

  2. In this step you create a test user and set the user to require SSL.

    Using the MySQL Command-Line Client, connect as root and create the user sslclient.

  3. To set privileges and requirements, issue the following command:

    GRANT ALL PRIVILEGES ON *.* TO sslclient@'%' REQUIRE SSL;
    

Creating a Certificate File to Use with the .NET Client

  1. The .NET client does not use the PEM file format, as .NET does not support this format natively. You will be using test client certificates from the same server repository, for the purposes of this example. Convert these to PFX format first. This format is also known as PKCS#12. An article describing this procedure can be found at the Citrix website. From the directory server-repository-root/mysql-test/std_data, issue the following command:

    openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -certfile cacert.pem -out client.pfx
    
  2. When asked for an export password, enter the password pass. The file client.pfx will be generated. This file is used in the remainder of the tutorial.

Connecting to the Server Using a File-Based Certificate

  1. You will use PFX file, client.pfx you created in the previous step to authenticate the client. The following example demonstrates how to connect using the SSL Mode, CertificateFile and CertificatePassword connection string options:

    using (MySqlConnection connection = new MySqlConnection(
      "database=test;user=sslclient;" +
      "CertificateFile=H:\\git\\mysql-trunk\\mysql-test\\std_data\\client.pfx;" +
      "CertificatePassword=pass;" +
      "SSL Mode=Required "))
    {
        connection.Open();
    }
    

    The path to the certificate file will need to be changed to reflect your individual installation.

Connecting to the Server Using a Store-Based Certificate

  1. The first step is to import the PFX file, client.pfx, into the Personal Store. Double-click the file in Windows explorer. This launches the Certificate Import Wizard.

  2. Follow the steps dictated by the wizard, and when prompted for the password for the PFX file, enter pass.

  3. Click Finish to close the wizard and import the certificate into the personal store.

Examine Certificates in the Personal Store

  1. Start the Microsoft Management Console by entering mmc.exe at a command prompt.

  2. Select File, Add/Remove snap-in. Click Add. Select Certificates from the list of available snap-ins in the dialog.

  3. Click Add button in the dialog, and select the My user account radio button. This is used for personal certificates.

  4. Click the Finish button.

  5. Click OK to close the Add/Remove Snap-in dialog.

  6. You will now have Certificates – Current User displayed in the left panel of the Microsoft Management Console. Expand the Certificates - Current User tree item and select Personal, Certificates. The right-hand panel will display a certificate issued to MySQL. This is the certificate that was previously imported. Double-click the certificate to display its details.

  7. After you have imported the certificate to the Personal Store, you can use a more succint connection string to connect to the database, as illustrated by the following code:

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

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.

4.11 Tutorial: Using MySqlScript

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. The Query property can be set through the MySqlScript constructor or by 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:

  1. Error - generated if an error occurs.

  2. ScriptCompleted - generated when the script successfully completes execution.

  3. StatementExecuted - generated after each statement is executed.

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());
        }
    }
}

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.

4.11.1 Using Delimiters with MySqlScript

Depending on the nature of the script, you may need control of the delimiter used to separate the statements that will make up a script. The most common example of this is where you have a multi-statement stored routine as part of your script. In this case if the default delimiter of ; is used you will get an error when you attempt to execute the script. For example, consider the following stored routine:

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

This routine actually needs to be executed on the MySQL Server as a single statement. However, with the default delimiter of ;, the MySqlScript class would interpret the above as two statements, the first being:

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

Executing this as a statement would generate an error. To solve this problem MySqlScript supports the ability to set a different delimiter. This is achieved through the Delimiter property. For example, you could set the delimiter to ??, in which case the above stored routine would no longer generate an error when executed. Multiple statements can be delimited in the script, so for example, you could have a three statement script such as:

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()";

You can change the delimiter back at any point by setting the Delimiter property. The following code shows a complete working example:

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.");
        }
    }
}