25.2. MySQL Connector/NET

The following properties are available:

The following methods are available:

The following example creates a MySqlCommand and a MySqlConnection. The MySqlConnection is opened and set as the Connection for the MySqlCommand. The example then calls ExecuteNonQuery, and closes the connection. To accomplish this, the ExecuteNonQuery is passed a connection string and a query string that is a SQL INSERT statement.

Public Sub InsertRow(myConnectionString As String)
    ' If the connection string is null, use a default.
    If myConnectionString = "" Then
        myConnectionString = "Database=Test;Data Source=localhost;User Id=username;Password=pass"
    End If
    Dim myConnection As New MySqlConnection(myConnectionString)
    Dim myInsertQuery As String = "INSERT INTO Orders (id, customerId, amount) Values(1001, 23, 30.66)"
    Dim myCommand As New MySqlCommand(myInsertQuery)
    myCommand.Connection = myConnection
    myConnection.Open()
    myCommand.ExecuteNonQuery()
    myCommand.Connection.Close()
End Sub

public void InsertRow(string myConnectionString) 
{
    // If the connection string is null, use a default.
    if(myConnectionString == "") 
    {
        myConnectionString = "Database=Test;Data Source=localhost;User Id=username;Password=pass";
    }
    MySqlConnection myConnection = new MySqlConnection(myConnectionString);
    string myInsertQuery = "INSERT INTO Orders (id, customerId, amount) Values(1001, 23, 30.66)";
    MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
    myCommand.Connection = myConnection;
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    myCommand.Connection.Close();
}

The following properties are available:

The following methods are available:

The following example uses the MySqlCommand, along MySqlDataAdapter and MySqlConnection, to select rows from a data source. The example is passed an initialized DataSet, a connection string, a query string that is a SQL SELECT statement, and a string that is the name of the database table. The example then creates a MySqlCommandBuilder.

  Public Shared Function SelectRows(myConnection As String, mySelectQuery As String, myTableName As String) As DataSet
        Dim myConn As New MySqlConnection(myConnection)
        Dim myDataAdapter As New MySqlDataAdapter()
        myDataAdapter.SelectCommand = New MySqlCommand(mySelectQuery, myConn)
        Dim cb As SqlCommandBuilder = New MySqlCommandBuilder(myDataAdapter)

        myConn.Open()

        Dim ds As DataSet = New DataSet
        myDataAdapter.Fill(ds, myTableName)

        ' Code to modify data in DataSet here 

        ' Without the MySqlCommandBuilder this line would fail.
        myDataAdapter.Update(ds, myTableName)

        myConn.Close()
    End Function 'SelectRows
    

    public static DataSet SelectRows(string myConnection, string mySelectQuery, string myTableName)
    {
      MySqlConnection myConn = new MySqlConnection(myConnection);
      MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
      myDataAdapter.SelectCommand = new MySqlCommand(mySelectQuery, myConn);
      MySqlCommandBuilder cb = new MySqlCommandBuilder(myDataAdapter);

      myConn.Open();

      DataSet ds = new DataSet();
      myDataAdapter.Fill(ds, myTableName);

      //code to modify data in DataSet here

      //Without the MySqlCommandBuilder this line would fail
      myDataAdapter.Update(ds, myTableName);

      myConn.Close();

      return ds;
    }  
    

The following properties are available:

The following methods are available:

The following example creates a MySqlCommand and a MySqlConnection. The MySqlConnection is opened and set as the Connection for the MySqlCommand. The example then calls ExecuteNonQuery, and closes the connection. To accomplish this, the ExecuteNonQuery is passed a connection string and a query string that is a SQL INSERT statement.

Public Sub InsertRow(myConnectionString As String)
    ' If the connection string is null, use a default.
    If myConnectionString = "" Then
        myConnectionString = "Database=Test;Data Source=localhost;User Id=username;Password=pass"
    End If
    Dim myConnection As New MySqlConnection(myConnectionString)
    Dim myInsertQuery As String = "INSERT INTO Orders (id, customerId, amount) Values(1001, 23, 30.66)"
    Dim myCommand As New MySqlCommand(myInsertQuery)
    myCommand.Connection = myConnection
    myConnection.Open()
    myCommand.ExecuteNonQuery()
    myCommand.Connection.Close()
End Sub
      

public void InsertRow(string myConnectionString) 
{
    // If the connection string is null, use a default.
    if(myConnectionString == "") 
    {
        myConnectionString = "Database=Test;Data Source=localhost;User Id=username;Password=pass";
    }
    MySqlConnection myConnection = new MySqlConnection(myConnectionString);
    string myInsertQuery = "INSERT INTO Orders (id, customerId, amount) Values(1001, 23, 30.66)";
    MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
    myCommand.Connection = myConnection;
    myConnection.Open();
    myCommand.ExecuteNonQuery();
    myCommand.Connection.Close();
}

      

The following properties are available:

The following methods are available:

The following example creates a MySqlCommand and a MySqlConnection. The MySqlConnection is opened and set as the Connection for the MySqlCommand. The example then calls ExecuteNonQuery, and closes the connection. To accomplish this, the ExecuteNonQuery is passed a connection string and a query string that is a SQL INSERT statement.

Public Function SelectRows(dataSet As DataSet, connection As String, query As String) As DataSet
    Dim conn As New MySqlConnection(connection)
    Dim adapter As New MySqlDataAdapter()
    adapter.SelectCommand = new MySqlCommand(query, conn)
    adapter.Fill(dataset)
    Return dataset
End Function 

public DataSet SelectRows(DataSet dataset,string connection,string query) 
{
    MySqlConnection conn = new MySqlConnection(connection);
    MySqlDataAdapter adapter = new MySqlDataAdapter();
    adapter.SelectCommand = new MySqlCommand(query, conn);
    adapter.Fill(dataset);
    return dataset;
}   
  

The following properties are available:

The following methods are available:

The following example creates a MySqlConnection, a MySqlCommand, and a MySqlDataReader. The example reads through the data, writing it out to the console. Finally, the example closes the MySqlDataReader, then the MySqlConnection

Public Sub ReadMyData(myConnString As String)
    Dim mySelectQuery As String = "SELECT OrderID, CustomerID FROM Orders"
    Dim myConnection As New MySqlConnection(myConnString)
    Dim myCommand As New MySqlCommand(mySelectQuery, myConnection)
    myConnection.Open()
    Dim myReader As MySqlDataReader
    myReader = myCommand.ExecuteReader()
    ' Always call Read before accessing data.
    While myReader.Read()
        Console.WriteLine((myReader.GetInt32(0) & ", " & myReader.GetString(1)))
    End While
    ' always call Close when done reading.
    myReader.Close()
    ' Close the connection when done with it.
    myConnection.Close()
End Sub 'ReadMyData       
      

public void ReadMyData(string myConnString) {
    string mySelectQuery = "SELECT OrderID, CustomerID FROM Orders";
    MySqlConnection myConnection = new MySqlConnection(myConnString);
    MySqlCommand myCommand = new MySqlCommand(mySelectQuery,myConnection);
    myConnection.Open();
    MySqlDataReader myReader;
    myReader = myCommand.ExecuteReader();
    // Always call Read before accessing data.
    while (myReader.Read()) {
       Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(1));
    }
    // always call Close when done reading.
    myReader.Close();
    // Close the connection when done with it.
    myConnection.Close();
 }     
      

The following properties are available:

The MySqlException class has no methods.

The following example generates a MySqlException due to a missing server, and then displays the exception.

Public Sub ShowException()
     Dim mySelectQuery As String = "SELECT column1 FROM table1"
     Dim myConnection As New MySqlConnection ("Data Source=localhost;Database=Sample;")
     Dim myCommand As New MySqlCommand(mySelectQuery, myConnection)

     Try
         myCommand.Connection.Open()
     Catch e As MySqlException
        MessageBox.Show( e.Message )
     End Try
 End Sub       
      

public void ShowException() 
{
   string mySelectQuery = "SELECT column1 FROM table1";
   MySqlConnection myConnection =
      new MySqlConnection("Data Source=localhost;Database=Sample;");
   MySqlCommand myCommand = new MySqlCommand(mySelectQuery,myConnection);

   try 
   {
      myCommand.Connection.Open();
   }
   catch (MySqlException e) 
   {
        MessageBox.Show( e.Message );
   }
}
    

The MySqlHelper class has no properties.

The following methods are available:

The following properties are available:

The following methods are available:

The following example creates a MySqlConnection and a MySqlTransaction. It also demonstrates how to use the BeginTransaction, Commit, and Rollback methods.

Public Sub RunTransaction(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    
    Dim myCommand As MySqlCommand = myConnection.CreateCommand()
    Dim myTrans As MySqlTransaction
    
    ' Start a local transaction
    myTrans = myConnection.BeginTransaction()
    ' Must assign both transaction object and connection
    ' to Command object for a pending local transaction
    myCommand.Connection = myConnection
    myCommand.Transaction = myTrans
    
    Try
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Both records are written to database.")
    Catch e As Exception
      Try
        myTrans.Rollback()
      Catch ex As MySqlException
        If Not myTrans.Connection Is Nothing Then
          Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                            " was encountered while attempting to roll back the transaction.")
        End If
      End Try
    
      Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                      "was encountered while inserting the data.")
      Console.WriteLine("Neither record was written to database.")
    Finally
      myConnection.Close()
    End Try
End Sub 'RunTransaction       
      

public void RunTransaction(string myConnString) 
 {
    MySqlConnection myConnection = new MySqlConnection(myConnString);
    myConnection.Open();

    MySqlCommand myCommand = myConnection.CreateCommand();
    MySqlTransaction myTrans;

    // Start a local transaction
    myTrans = myConnection.BeginTransaction();
    // Must assign both transaction object and connection
    // to Command object for a pending local transaction
    myCommand.Connection = myConnection;
    myCommand.Transaction = myTrans;

    try
    {
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      Console.WriteLine("Both records are written to database.");
    }
    catch(Exception e)
    {
      try
      {
        myTrans.Rollback();
      }
      catch (MySqlException ex)
      {
        if (myTrans.Connection != null)
        {
          Console.WriteLine("An exception of type " + ex.GetType() +
                            " was encountered while attempting to roll back the transaction.");
        }
      }
    
      Console.WriteLine("An exception of type " + e.GetType() +
                        " was encountered while inserting the data.");
      Console.WriteLine("Neither record was written to database.");
    }
    finally 
    {
      myConnection.Close();
    }
}       
      

All interaction between a .NET application and the MySQL server is routed through a MySqlConnection object. Before your application can interact with the server, a MySqlConnection object must be instanced, configured, and opened.

Even when using the MySqlHelper class, a MySqlConnection object is created by the helper class.

In this section, we will describe how to connect to MySQL using the MySqlConnection object.

The MySqlConnection object is configured using a connection string. A connection string contains sever key/value pairs, separated by semicolons. Each key/value pair is joined with an equals sign.

The following is a sample connection string:

    Server=127.0.0.1;Uid=root;Pwd=12345;Database=test;
    

In this example, the MySqlConnection object is configured to connect to a MySQL server at 127.0.0.1, with a username of root and a password of 12345. The default database for all statements will be the test database.

The following options are typically used (a full list of options is available in the API documentation):

Once you have created a connection string it can be used to open a connection to the MySQL server.

The following code is used to create a MySqlConnection object, assign the connection string, and open the connection.

[VB]

Dim conn As New MySql.Data.MySqlClient.MySqlConnection
Dim myConnectionString as String

myConnectionString = "server=127.0.0.1;" _
            & "uid=root;" _
            & "pwd=12345;" _
            & "database=test;"

Try
  conn.ConnectionString = myConnectionString
  conn.Open()

Catch ex As MySql.Data.MySqlClient.MySqlException
  MessageBox.Show(ex.Message)
End Try
  

[C#]

MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;
    
myConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test;";
  
try
{
    conn = new MySql.Data.MySqlClient.MySqlConnection();
    conn.ConnectionString = myConnectionString;
    conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message);
}

You can also pass the connection string to the constructor of the MySqlConnection class:

[VB]

Dim myConnectionString as String

myConnectionString = "server=127.0.0.1;" _
              & "uid=root;" _
              & "pwd=12345;" _
              & "database=test;" 

Try
    Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
    conn.Open()
Catch ex As MySql.Data.MySqlClient.MySqlException
   MessageBox.Show(ex.Message)
End Try
  

[C#]

MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;

myConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test;";

try
{
    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
    conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message);
}

Once the connection is open it can be used by the other MySQL Connector/NET classes to communicate with the MySQL server.

Because connecting to an external server is unpredictable, it is important to add error handling to your .NET application. When there is an error connecting, the MySqlConnection class will return a MySqlException object. This object has two properties that are of interest when handling errors:

When handling errors, you can your application's response based on the error number. The two most common error numbers when connecting are as follows:

The following code shows how to adapt the application's response based on the actual error:

[VB]

Dim myConnectionString as String

myConnectionString = "server=127.0.0.1;" _
          & "uid=root;" _
          & "pwd=12345;" _
          & "database=test;" 

Try
    Dim conn As New MySql.Data.MySqlClient.MySqlConnection(myConnectionString)
    conn.Open()
Catch ex As MySql.Data.MySqlClient.MySqlException
    Select Case ex.Number
        Case 0
            MessageBox.Show("Cannot connect to server. Contact administrator")
        Case 1045
            MessageBox.Show("Invalid username/password, please try again")
    End Select
End Try
  

[C#]

MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;

myConnectionString = "server=127.0.0.1;uid=root;" +  
    "pwd=12345;database=test;";

try
{
    conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString);
    conn.Open();
}
    catch (MySql.Data.MySqlClient.MySqlException ex)
{
    switch (ex.Number)
    {
        case 0:
            MessageBox.Show("Cannot connect to server.  Contact administrator");
        case 1045:
            MessageBox.Show("Invalid username/password, please try again");
    }
}
  

As of MySQL 4.1, it is possible to use prepared statements with MySQL Connector/NET. Use of prepared statements can provide significant performance improvements on queries that are executed more than once.

Prepared execution is faster than direct execution for statements executed more than once, primarily because the query is parsed only once. In the case of direct execution, the query is parsed every time it is executed. Prepared execution also can provide a reduction of network traffic because for each execution of the prepared statement, it is necessary only to send the data for the parameters.

Another advantage of prepared statements is that it uses a binary protocol that makes data transfer between client and server more efficient.

To prepare a statement, create a command object and set the .CommandText property to your query.

After entering your statement, call the .Prepare method of the MySqlCommand object. After the statement is prepared, add parameters for each of the dynamic elements in the query.

After you enter your query and enter parameters, execute the statement using the .ExecuteNonQuery(), .ExecuteScalar(), or .ExecuteReader methods.

For subsequent executions, you need only modify the values of the parameters and call the execute method again, there is no need to set the .CommandText property or redefine the parameters.

[VB]

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
  
conn.ConnectionString = strConnection

Try
   conn.Open()
   cmd.Connection = conn
 
   cmd.CommandText = "INSERT INTO myTable VALUES(NULL, ?number, ?text)"
   cmd.Prepare()

   cmd.Parameters.Add("?number", 1)
   cmd.Parameters.Add("?text", "One")

   For i = 1 To 1000
       cmd.Parameters("?number").Value = i
       cmd.Parameters("?text").Value = "A string value"

       cmd.ExecuteNonQuery()
     Next 
Catch ex As MySqlException
    MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
  

[C#]

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
  
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

conn.ConnectionString = strConnection;

try
{
    conn.Open();
    cmd.Connection = conn;

    cmd.CommandText = "INSERT INTO myTable VALUES(NULL, ?number, ?text)";
    cmd.Prepare();

    cmd.Parameters.Add("?number", 1);
    cmd.Parameters.Add("?text", "One");

    for (int i=1; i <= 1000; i++)
    {
        cmd.Parameters["?number"].Value = i;
        cmd.Parameters["?text"].Value = "A string value";

        cmd.ExecuteNonQuery();
    }
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
        "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

With the release of MySQL version 5 the MySQL server now supports stored procedures with the SQL 2003 stored procedure syntax.

A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead.

Stored procedures can be particularly useful in situations such as the following:

MySQL Connector/NET supports the calling of stored procedures through the MySqlCommand object. Data can be passed in and our of a MySQL stored procedure through use of the MySqlCommand.Parameters collection.

This section will not provide in-depth information on creating Stored Procedures, for such information please refer to the Stored Procedures section of the MySQL Reference Manual.

A sample application demonstrating how to use stored procedures with MySQL Connector/NET can be found in the Samples directory of your MySQL Connector/NET installation.

Stored procedures in MySQL can be created using a variety of tools. First, stored procedures can be created using the mysql command-line client. Second, stored procedures can be created using the MySQL Query Browser GUI client. Finally, stored procedures can be created using the .ExecuteNonQuery method of the MySqlCommand object:

[VB]

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=test"

Try
    conn.Open()
    cmd.Connection = conn

    cmd.CommandText = "CREATE PROCEDURE add_emp(" _
        & "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT) " _
        & "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " _
        & "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END"
 
    cmd.ExecuteNonQuery()
Catch ex As MySqlException
    MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

[C#]

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test;";

try
{
    conn.Open();
    cmd.Connection = conn;

    cmd.CommandText = "CREATE PROCEDURE add_emp(" +
        "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT) " +
        "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
        "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END";

    cmd.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
    "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

It should be noted that, unlike the command-line and GUI clients, you are not required to specify a special delimiter when creating stored procedures in MySQL Connector/NET.

To call a stored procedure using MySQL Connector/NET, create a MySqlCommand object and pass the stored procedure name as the .CommandText property. Set the .CommandType property to CommandType.StoredProcedure.

After the stored procedure is named, create one MySqlCommand parameter for every parameter in the stored procedure. IN parameters are defined with the parameter name and the object containing the value, OUT parameters are defined with the parameter name and the datatype that is expected to be returned. All parameters need the parameter direction defined.

After defining parameters, call the stored procedure by using the MySqlCommand.ExecuteNonQuery() method:

[VB]

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=test"

Try
    conn.Open()
    cmd.Connection = conn

    cmd.CommandText = "add_emp"
    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.Add("?lname", 'Jones')
    cmd.Parameters("?lname").Direction = ParameterDirection.Input

    cmd.Parameters.Add("?fname", 'Tom')
    cmd.Parameters("?fname").Direction = ParameterDirection.Input

    cmd.Parameters.Add("?bday", #12/13/1977 2:17:36 PM#)
    cmd.Parameters("?bday").Direction = ParameterDirection.Input

    cmd.Parameters.Add("?empno", MySqlDbType.Int32)
    cmd.Parameters("?empno").Direction = ParameterDirection.Output

    cmd.ExecuteNonQuery()

    MessageBox.Show(cmd.Parameters("?empno").Value)
Catch ex As MySqlException
    MessageBox.Show("Error " & ex.Number & " has occurred: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

[C#]

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test;";

try
{
    conn.Open();
    cmd.Connection = conn;

    cmd.CommandText = "add_emp";
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("?lname", "Jones");
    cmd.Parameters("?lname").Direction = ParameterDirection.Input;

    cmd.Parameters.Add("?fname", "Tom");
    cmd.Parameters("?fname").Direction = ParameterDirection.Input;

    cmd.Parameters.Add("?bday", DateTime.Parse("12/13/1977 2:17:36 PM"));
    cmd.Parameters("?bday").Direction = ParameterDirection.Input;

    cmd.Parameters.Add("?empno", MySqlDbType.Int32);
    cmd.Parameters("?empno").Direction = ParameterDirection.Output;

    cmd.ExecuteNonQuery();

    MessageBox.Show(cmd.Parameters("?empno").Value);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
      "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Once the stored procedure is called, the values of output parameters can be retrieved by using the .Value property of the MySqlConnector.Parameters collection.

One common use for MySQL is the storage of binary data in BLOB columns. MySQL supports four different BLOB datatypes: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

Data stored in a BLOB column can be accessed using Connector/NET and manipulated using client-side code. There are no special requirements for using Connector/NET with BLOB data.

Simple code examples will be presented within this section, and a full sample application can be found in the Samples directory of the MySQL Connector/NET installation.

The first step is using MySQL with BLOB data is to configure the server. Let's start by creating a table to be accessed. In my file tables, I usually have four columns: an AUTO_INCREMENT column of appropriate size (UNSIGNED SMALLINT) to serve as a primary key to identify the file, a VARCHAR column that stores the filename, an UNSIGNED MEDIUMINT column that stores the size of the file, and a MEDIUMBLOB column that stores the file itself. For this example, I will use the following table definition:

CREATE TABLE file(
file_id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
file_name VARCHAR(64) NOT NULL,
file_size MEDIUMINT UNSIGNED NOT NULL,
file MEDIUMBLOB NOT NULL);

After creating a table, you may need to modify the max_allowed_packet system variable. This variable determines how large of a packet (i.e. a single row) can be sent to the MySQL server. By default, the server will only accept a maximum size of 1 meg from our client application. If you do not intend to exceed 1 meg, this should be fine. If you do intend to exceed 1 meg in your file transfers, this number has to be increased.

The max_allowed_packet option can be modified using MySQL Administrator's Startup Variables screen. Adjust the Maximum allowed option in the Memory section of the Networking tab to an appropriate setting. After adjusting the value, click the Apply Changes button and restart the server using the Service Control screen of MySQL Administrator. You can also adjust this value directly in the my.cnf file (add a line that reads max_allowed_packet=xxM), or use the SET max_allowed_packet=xxM; syntax from within MySQL.

Try to be conservative when setting max_allowed_packet, as transfers of BLOB data can take some time to complete. Try to set a value that will be adequate for your intended use and increase the value if necessary.

To write a file to a database we need to convert the file to a byte array, then use the byte array as a parameter to an INSERT query.

The following code opens a file using a FileStream object, reads it into a byte array, and inserts it into the file table:

[VB]

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand

Dim SQL As String

Dim FileSize As UInt32
Dim rawData() As Byte
Dim fs As FileStream

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=test"

Try
    fs = New FileStream("c:\image.png", FileMode.Open, FileAccess.Read)
    FileSize = fs.Length
    
    rawData = New Byte(FileSize) {}
    fs.Read(rawData, 0, FileSize)
    fs.Close()
    
    conn.Open()
    
    SQL = "INSERT INTO file VALUES(NULL, ?FileName, ?FileSize, ?File)"
    
    cmd.Connection = conn
    cmd.CommandText = SQL
    cmd.Parameters.Add("?FileName", strFileName)
    cmd.Parameters.Add("?FileSize", FileSize)
    cmd.Parameters.Add("?File", rawData)
    
    cmd.ExecuteNonQuery()
    
    MessageBox.Show("File Inserted into database successfully!", _
    "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
    
    conn.Close()
Catch ex As Exception
    MessageBox.Show("There was an error: " & ex.Message, "Error", _
        MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
  

[C#]

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

string SQL;
UInt32 FileSize;
byte[] rawData;
FileStream fs;

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test;";

try
{
    fs = new FileStream(@"c:\image.png", FileMode.Open, FileAccess.Read);
    FileSize = fs.Length;

    rawData = new byte[FileSize];
    fs.Read(rawData, 0, FileSize);
    fs.Close();

    conn.Open();

    SQL = "INSERT INTO file VALUES(NULL, ?FileName, ?FileSize, ?File)";

    cmd.Connection = conn;
    cmd.CommandText = SQL;
    cmd.Parameters.Add("?FileName", strFileName);
    cmd.Parameters.Add("?FileSize", FileSize);
    cmd.Parameters.Add("?File", rawData);

    cmd.ExecuteNonQuery();

    MessageBox.Show("File Inserted into database successfully!",
        "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

    conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
        "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
 

The Read method of the FileStream object is used to load the file into a byte array which is sized according to the Length property of the FileStream object.

After assigning the byte array as a parameter of the MySqlCommand object, the ExecuteNonQuery method is called and the BLOB is inserted into the file table.

Once a file is loaded into the file table, we can use the MySqlDataReader class to retrieve it.

The following code retrieves a row from the file table, then loads the data into a FileStream object to be written to disk:

[VB]

Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim myData As MySqlDataReader
Dim SQL As String
Dim rawData() As Byte
Dim FileSize As UInt32
Dim fs As FileStream

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=test"

SQL = "SELECT file_name, file_size, file FROM file"

Try
    conn.Open()
    
    cmd.Connection = conn
    cmd.CommandText = SQL
    
    myData = cmd.ExecuteReader
    
    If Not myData.HasRows Then Throw New Exception("There are no BLOBs to save")
    
    myData.Read()
    
    FileSize = myData.GetUInt32(myData.GetOrdinal("file_size"))
    rawData = New Byte(FileSize) {}
    
    myData.GetBytes(myData.GetOrdinal("file"), 0, rawData, 0, FileSize)
    
    fs = New FileStream("C:\newfile.png", FileMode.OpenOrCreate, FileAccess.Write)
    fs.Write(rawData, 0, FileSize)
    fs.Close()
    
    MessageBox.Show("File successfully written to disk!", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)
    
    myData.Close()
    conn.Close()
Catch ex As Exception
    MessageBox.Show("There was an error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
  

[C#]

MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataReader myData;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

string SQL;
UInt32 FileSize;
byte[] rawData;
FileStream fs;

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test;";

SQL = "SELECT file_name, file_size, file FROM file";

try
{
    conn.Open();

    cmd.Connection = conn;
    cmd.CommandText = SQL;

    myData = cmd.ExecuteReader();

    if (! myData.HasRows)
        throw new Exception("There are no BLOBs to save");

    myData.Read();

    FileSize = myData.GetUInt32(myData.GetOrdinal("file_size"));
    rawData = new byte[FileSize];

    myData.GetBytes(myData.GetOrdinal("file"), 0, rawData, 0, FileSize);

    fs = new FileStream(@"C:\newfile.png", FileMode.OpenOrCreate, FileAccess.Write);
    fs.Write(rawData, 0, FileSize);
    fs.Close();

    MessageBox.Show("File successfully written to disk!",
        "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

    myData.Close();
    conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
        "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
 

After connecting, the contents of the file table are loaded into a MySqlDataReader object. The GetBytes method of the MySqlDataReader is used to load the BLOB into a byte array, which is then written to disk using a FileStream object.

The GetOrdinal method of the MySqlDataReader can be used to determine the integer index of a named column. Use of the GetOrdinal method prevents errors if the column order of the SELECT query is changed.

Crystal Reports is a common tool used by Windows application developers to perform reporting an document generation. In this section we will show how to use Crystal Reports XI with MySQL and Connector/NET.

Complete sample applications are available in the CrystalDemo subdirectory of the Samples directory of your MySQL Connector/NET installation.

When creating a report in Crystal Reports there are two options for accessing the MySQL data while designing your report.

The first option is to use Connector/ODBC as an ADO data source when designing your report. You will be able to browse your database and choose tables and fields using drag and drop to build your report. The disadvantage of this approach is that additional work must be performed within your application to produce a dataset that matches the one expected by your report.

The second option is to create a dataset in VB.NET and save it as XML. This XML file can then be used to design a report. This works quite well when displaying the report in your application, but is less versatile at design time because you must choose all relevant columns when creating the dataset. If you forget a column you must re-create the dataset before the column can be added to the report.

The following code can be used to create a dataset from a query and write it to disk:

[VB]

Dim myData As New DataSet
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=world"

Try
    conn.Open()
    cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " _ 
        & "country.name, country.population, country.continent " _
        & "FROM country, city ORDER BY country.continent, country.name"
    cmd.Connection = conn
    
    myAdapter.SelectCommand = cmd
    myAdapter.Fill(myData)
    
    myData.WriteXml("C:\dataset.xml", XmlWriteMode.WriteSchema)
Catch ex As Exception
    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
 

[C#]

DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
  "pwd=12345;database=test;";
  
try
{
  cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " +
  "country.name, country.population, country.continent " +
  "FROM country, city ORDER BY country.continent, country.name";
  cmd.Connection = conn;
  
  myAdapter.SelectCommand = cmd;
  myAdapter.Fill(myData);
  
  myData.WriteXml(@"C:\dataset.xml", XmlWriteMode.WriteSchema);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
  MessageBox.Show(ex.Message, "Report could not be created",
  MessageBoxButtons.OK, MessageBoxIcon.Error);
}

The resulting XML file can be used as an ADO.NET XML datasource when designing your report.

If you choose to design your reports using Connector/ODBC, it can be downloaded from dev.mysql.com.

For most purposes the Standard Report wizard should help with the initial creation of a report. To start the wizard, open Crystal Reports and choose the New > Standard Report option from the File menu.

The wizard will first prompt you for a data source. If you are using Connector/ODBC as your data source, use the OLEDB provider for ODBC option from the OLE DB (ADO) tree instead of the ODBC (RDO) tree when choosing a data source. If using a saved dataset, choose the ADO.NET (XML) option and browse to your saved dataset.

The remainder of the report creation process is done automatically by the wizard.

After the report is created, choose the Report Options... entry of the File menu. Un-check the Save Data With Report option. This prevents saved data from interfering with the loading of data within our application.

To display a report we first populate a dataset with the data needed for the report, then load the report and bind it to the dataset. Finally we pass the report to the crViewer control for display to the user.

The following references are needed in a project that displays a report:

The following code assumes that you created your report using a dataset saved using the code shown in Creating a Data Source and have a crViewer control on your form named myViewer.

[VB]

Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data
Imports MySql.Data.MySqlClient

Dim myReport As New ReportDocument
Dim myData As New DataSet
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter

conn.ConnectionString = _
    "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=test"

Try
    conn.Open()
    
    cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " _ 
        & "country.name, country.population, country.continent " _
        & "FROM country, city ORDER BY country.continent, country.name"
    cmd.Connection = conn
    
    myAdapter.SelectCommand = cmd
    myAdapter.Fill(myData)
    
    myReport.Load(".\world_report.rpt")
    myReport.SetDataSource(myData)
    myViewer.ReportSource = myReport
Catch ex As Exception
    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

[C#]

using CrystalDecisions.CrystalReports.Engine;
using System.Data;
using MySql.Data.MySqlClient;

ReportDocument myReport = new ReportDocument();
DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test;";

try
{
    cmd.CommandText = "SELECT city.name AS cityName, city.population AS CityPopulation, " +
        "country.name, country.population, country.continent " +
        "FROM country, city ORDER BY country.continent, country.name";
    cmd.Connection = conn;

    myAdapter.SelectCommand = cmd;
    myAdapter.Fill(myData);

    myReport.Load(@".\world_report.rpt");
    myReport.SetDataSource(myData);
    myViewer.ReportSource = myReport;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message, "Report could not be created",
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}

A new dataset it generated using the same query used to generate the previously saved dataset. Once the dataset is filled, a ReportDocument is used to load the report file and bind it to the dataset. The ReportDocument is the passed as the ReportSource of the crViewer.

This same approach is taken when a report is created from a single table using Connector/ODBC. The dataset replaces the table used in the report and the report is displayed properly.

When a report is created from multiple tables using Connector/ODBC, a dataset with multiple tables must be created in our application. This allows each table in the report data source to be replaced with a report in the dataset.

We populate a dataset with multiple tables by providing multiple SELECT statements in our MySqlCommand object. These SELECT statements are based on the SQL query shown in Crystal Reports in the Database menu's Show SQL Query option. Assume the following query:

SELECT `country`.`Name`, `country`.`Continent`, `country`.`Population`, `city`.`Name`, `city`.`Population`
FROM `world`.`country` `country` LEFT OUTER JOIN `world`.`city` `city` ON `country`.`Code`=`city`.`CountryCode`
ORDER BY `country`.`Continent`, `country`.`Name`, `city`.`Name`

This query is converted to two SELECT queries and displayed with the following code:

[VB]

Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data
Imports MySql.Data.MySqlClient

Dim myReport As New ReportDocument
Dim myData As New DataSet
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter

conn.ConnectionString = "server=127.0.0.1;" _
    & "uid=root;" _
    & "pwd=12345;" _
    & "database=world"

Try
    conn.Open()
    cmd.CommandText = "SELECT name, population, countrycode FROM city ORDER BY countrycode, name; " _
        & "SELECT name, population, code, continent FROM country ORDER BY continent, name"
    cmd.Connection = conn
    
    myAdapter.SelectCommand = cmd
    myAdapter.Fill(myData)
    
    myReport.Load(".\world_report.rpt")
    myReport.Database.Tables(0).SetDataSource(myData.Tables(0))
    myReport.Database.Tables(1).SetDataSource(myData.Tables(1))
    myViewer.ReportSource = myReport
Catch ex As Exception
    MessageBox.Show(ex.Message, "Report could not be created", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

[C#]

using CrystalDecisions.CrystalReports.Engine;
using System.Data;
using MySql.Data.MySqlClient;

ReportDocument myReport = new ReportDocument();
DataSet myData = new DataSet();
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
MySql.Data.MySqlClient.MySqlDataAdapter myAdapter;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
myAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter();

conn.ConnectionString = "server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test;";

try
{
    cmd.CommandText = "SELECT name, population, countrycode FROM city ORDER " +
        "BY countrycode, name; SELECT name, population, code, continent FROM " +
        "country ORDER BY continent, name";
    cmd.Connection = conn;

    myAdapter.SelectCommand = cmd;
    myAdapter.Fill(myData);

    myReport.Load(@".\world_report.rpt");
    myReport.Database.Tables(0).SetDataSource(myData.Tables(0));
    myReport.Database.Tables(1).SetDataSource(myData.Tables(1));
    myViewer.ReportSource = myReport;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message, "Report could not be created",
        MessageBoxButtons.OK, MessageBoxIcon.Error);
}  
 

It is important to order the SELECT queries in alphabetical order, as this is the order the report will expect its source tables to be in. One SetDataSource statement is needed for each table in the report.

This approach can cause performance problems because Crystal Reports must bind the tables together on the client-side, which will be slower than using a pre-saved dataset.

MySQL and the .NET languages handle date and time information differently, with MySQL allowing dates that cannot be represented by a .NET data type, such as '0000-00-00 00:00:00'. These differences can cause problems if not properly handled.

In this section we will demonstrate how to properly handle date and time information when using MySQL Connector/NET.

The differences in date handling can cause problems for developers who use invalid dates. Invalid MySQL dates cannot be loaded into native .NET DateTime objects, including NULL dates.

Because of this issue, .NET DataSet objects cannot be populated by the Fill method of the MySqlDataAdapter class as invalid dates will cause a System.ArgumentOutOfRangeException exception to occur.

The best solution to the date problem is to restrict users from entering invalid dates. This can be done on either the client or the server side.

Restricting invalid dates on the client side is as simple as always using the .NET DateTime class to handle dates. The DateTime class will only allow valid dates, ensuring that the values in your database are also valid. The disadvantage of this is that it is not useful in a mixed environment where .NET and non .NET code are used to manipulate the database, as each application must perform its own date validation.

Users of MySQL 5.0.2 and higher can use the new traditional SQL mode to restrict invalid date values. For information on using the traditional SQL mode, see http://dev.mysql.com/doc/mysql/en/server-sql-mode.html.

While it is strongly recommended that you avoid the use of invalid dates within your .NET application, it is possible to use invalid dates by means of the MySqlDateTime datatype.

The MySqlDateTime datatype supports the same date values that are supported by the MySQL server. The default behavior of MySQL Connector/NET is to return a .NET DateTime object for valid date values, and return an error for invalid dates. This default can be modified to cause MySQL Connector/NET to return MySqlDateTime objects for invalid dates.

To instruct MySQL Connector/NET to return a MySqlDateTime object for invalid dates, add the following line to your connection string:

  Allow Zero Datetime=True
  

Please note that the use of the MySqlDateTime class can still be problematic. The following are some known issues:

Because of the known issues, the best recommendation is still to use only valid dates in your application.

The .NET DateTime datatype cannot handle NULL values. As such, when assigning values from a query to a DateTime variable, you must first check whether the value is in fact NULL.

When using a MySqlDataReader, use the .IsDBNull method to check whether a value is NULL before making the assignment:

[VB]

If Not myReader.IsDBNull(myReader.GetOrdinal("mytime")) Then
    myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime"))
Else
    myTime = DateTime.MinValue
End If
  

[C#]

if (! myReader.IsDBNull(myReader.GetOrdinal("mytime")))
    myTime = myReader.GetDateTime(myReader.GetOrdinal("mytime"));
else
    myTime = DateTime.MinValue;
  

NULL values will work in a dataset and can be bound to form controls without special handling.

• Fixed an exception when trying to use a stored procedure when Connection.Database is not populated. (Bug #11450)

• Certain malformed queries will trigger a "Connection must be valid and open" error message. (Bug #11490)

• A statement that contained multiple references to the same parameter could not be prepared. (Bug #13541)

• Ping() method did not update State property of Connection object. (Bug #13658)

• nant build sequence had problems. (Bug #12978)

• Serializing a parameter fails if first value passed in NULL. (Bug #13276)

• Field names that contained the following characters caused errors: ()%<>/ (Bug #13036)

• MySQL Connector/NET 1.0.5 installer would not install alongside MySQL Connector/NET 1.0.4. (Bug #12835)

• MySQL Connector/NET 1.0.5 could not connect on Mono. (Bug #13345)

• With multiple hosts in the connection string, MySQL Connector/NET would not connect to the last host in the list. (Bug #12628)

• MySQL Connector/NET interpreted the new decimal data type as a byte array. (Bug #11294)

• The cp1250 character set was not supported. (Bug #11621)

• Connection could fail when .NET thread pool had no available worker threads. (Bug #10637)

• Decimal parameters caused syntax errors. (Bug #11550, Bug #10486, Bug #10152)

• Call to stored procedure causes exception if stored procedure has no parameters. (Bug #11542)

• Certain malformed queries will trigger a "Connection must be valid and open" error message. (Bug #11490)

• Fixed a bug where the MySqlCommandBuilder class could not handle queries that references tables in a database other than the default database. (Bug #8382)

• Fixed a bug where MySQL Connector/NET could not work properly with certain regional settings. (WL#8228)

• Fixed an exception when trying to use a stored procedure when Connection.Database is not populated. (Bug #11450)

• Fixed an exception when trying to read a TIMESTAMP column. (Bug #7951)

• Fixed error where parameters not recognised when separated by linefeeds. (Bug #9722)

• Fixed error when MySqlConnection.clone is called and a connection string had not yet been set on the original connection. (Bug #10281)

• Added support to call a stored function from Connector/NET. (Bug #10644)

• MySQL Connector/NET could not connect to MySQL 4.1.14. (Bug #12771)

• ConnectionString property could not be set when MySqlConnection object added with designer. (Bug #12551, Bug #8724)

• Bug #7243 calling prepare causing exception [fixed]

• Fixed another small problem with prepared statements

• Bug #7258 MySqlCommand.Connection returns an IDbConnection [fixed]

• Bug #7345 MySqlAdapter.Fill method throws Error message : Non-negative number required [fixed]

• Bug #7478 Clone method bug in MySqlCommand [fixed]

• Bug #7612 MySqlDataReader.GetString(index) returns non-Null value when field is Null [fixed]

• Bug #7755 MySqlReader.GetInt32 throws exception if column is unsigned [fixed]

• Bug #7704 GetBytes is working no more [fixed]

• Bug #7724 Quote character \222 not quoted in EscapeString [fixed]

• Fixed problem that causes named pipes to not work with some blob functionality

• Fixed problem with shared memory connections

• Bug #7436 Problem with Multiple resultsets... [fixed]

• Added or filled out several more topics in the API reference documentation

• Made MySQL the default named pipe name

• Now SHOW COLLATION is used upon connection to retrieve the full list of charset ids

• Fixed Invalid character set index: 200 (Bug #6547)

• Installer now includes options to install into GAC and create Start Menu items

• Bug #6863 - Int64 Support in MySqlCommand Parameters [fixed]

• Connections now do not have to give a database on the connection string

• Bug #6770 - MySqlDataReader.GetChar(int i) throws IndexOutOfRange Exception [fixed]

• Fixed problem where multiple resultsets having different numbers of columns would cause a problem

• Bug #6983 Exception stack trace lost when re-throwing exceptions [fixed]

• Fixed major problem with detecting null values when using prepared statements

• Bug #6902 Errors in parsing stored procedure parameters [fixed]

• Bug #6668 Integer "out" parameter from stored procedure returned as string [fixed]

• Bug #7032 MySqlDateTime in Datatables sorting by Text, not Date. [fixed]

• Bug #7133 Invalid query string when using inout parameters [fixed]

• Bug #6831 Test suite fails with MySQL 4.0 because of case sensitivity of table names [fixed]

• Bug #7132 Inserting DateTime causes System.InvalidCastException to be thrown [fixed]

• Bug #6879 InvalidCast when using DATE_ADD-function [fixed]

• Bug #6634 An Open Connection has been Closed by the Host System [fixed]

• Added ServerThread property to MySqlConnection to expose server thread id

• Added Ping method to MySqlConnection

• Changed the name of the test suite to MySql.Data.Tests.dll

• Fixed problem with MySqlBinary where string values could not be used to update extended text columns

• Fixed Installation directory ignored using custom installation (Bug #6329)

• Fixed problem where setting command text leaves the command in a prepared state

• Fixed double type handling in MySqlParameter(string parameterName, object value) (Bug #6428)

• Fixed Zero date "0000-00-00" is returned wrong when filling Dataset (Bug #6429)

• Fixed problem where calling stored procedures might cause an "Illegal mix of collations" problem.

• Added charset connection string option

• Fixed #HY000 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ (Bug #6322)

• Added the TableEditor CS and VB sample

• Fixed Charset-map for UCS-2 (Bug #6541)

• Updated the installer to include the new samples

• Fixed Long inserts take very long time (Bu #5453)

• Fixed Objects not being disposed (Bug #6649)

• Provider is now using character set specified by server as default

• Fixed BUG #5602 Possible bug in MySqlParameter(string, object) constructor

• Fixed BUG #5458 Calling GetChars on a longtext column throws an exception

• Fixed BUG #5474 cannot run a stored procedure populating mysqlcommand.parameters

• Fixed BUG #5469 Setting DbType throws NullReferenceException

• Fixed problem where connector was not issuing a CMD_QUIT before closing the socket

• Fixed BUG #5392 MySqlCommand sees "?" as parameters in string literals

• Fixed problem with ConnectionInternal where a key might be added more than once

• CP1252 is now used for Latin1 only when the server is 4.1.2 and later

• Fixed BUG #5388 DataReader reports all rows as NULL if one row is NULL

• Virtualized driver subsystem so future releases could easily support client or embedded server support

• Field buffers being reused to decrease memory allocations and increase speed

• Fixed problem where using old syntax while using the interfaces caused problems

• Using PacketWriter instead of Packet for writing to streams

• Refactored compression code into CompressedStream to clean up NativeDriver

• Added test case for resetting the command text on a prepared command

• Fixed problem where MySqlParameterCollection.Add() would throw unclear exception when given a null value (Bug #5621)

• Fixed construtor initialize problems in MySqlCommand() (Bug #5613)

• Fixed Parsing the ';' char (Bug #5876)

• Fixed missing Reference in DbType setter (Bug #5897)

• Fixed System.OverflowException when using YEAR datatype (Bug #6036)

• Added Aggregate function test (wasn't really a bug)

• Fixed serializing of floating point parameters (double, numeric, single, decimal) (Bug #5900)

• IsNullable error (Bug #5796)

• Fixed problem where connection lifetime on the connect string was not being respected

• Fixed problem where Min Pool Size was not being respected

• Fixed MySqlDataReader and 'show tables from ...' behavior (Bug #5256)

• Implemented SequentialAccess

• Fixed MySqlDateTime sets IsZero property on all subseq.records after first zero found (Bug #6006)

• Fixed Can't display Chinese correctly (Bug #5288)

• Fixed Russian character support as well

• Fixed Method TokenizeSql() uses only a limited set of valid characters for parameters (Bug #6217)

• Fixed NET Connector source missing resx files (Bug #6216)

• Fixed DBNull Values causing problems with retrieving/updating queries. (Bug #5798)

• Fixed Yet Another "object reference not set to an instance of an object" (Bug #5496)

• Fixed problem in PacketReader where it could try to allocate the wrong buffer size in EnsureCapacity

• Fixed GetBoolean returns wrong values (Bug #6227)

• Fixed IndexOutOfBounds when reading BLOB with DataReader with GetString(index) (Bug #6230)

• Fixed BUG# 3889 Thai encoding not correctly supported

• Updated many of the test cases

• Fixed problem with using compression

• Bumped version number to 1.0.0 for beta 1 release

• Added COPYING.rtf file for use in installer

• Removed all of the XML comment warnings (I'll clean them up better later)

• Removed some last references to ByteFX

• Added test fixture for prepared statements

• All type classes now implement a SerializeBinary method for sending their data to a PacketWriter

• Added PacketWriter class that will enable future low-memory large object handling

• Fixed many small bugs in running prepared statements and stored procedures

• Changed command so that an exception will not be throw in executing a stored procedure with parameters in old syntax mode

• SingleRow behavior now working right even with limit

• GetBytes now only works on binary columns

• Logger now truncates long sql commands so blob columns don't blow out our log

• host and database now have a default value of "" unless otherwise set

• FIXED BUG# 5214 Connection Timeout seems to be ignored

• Added test case for bug# 5051: GetSchema not working correctly

• Fixed problem where GetSchema would return false for IsUnique when the column is key

• MySqlDataReader GetXXX methods now using the field level MySqlValue object and not performing conversions

• FIXED BUG# 5097: DataReader returning NULL for time column

• Added test case for LOAD DATA LOCAL INFILE

• Added replacetext custom nant task

• Added CommandBuilderTest fixture

• Added Last One Wins feature to CommandBuilder

• Fixed persist security info case problem

• Fixed GetBool so that 1, true, "true", and "yes" all count as trueWL# 2024 Make parameter mark configurable

• Added the "old syntax" connection string parameter to allow use of @ parameter marker

• Fixed Bug #4658 MySqlCommandBuilder

• Fixed Bug #4864 ByteFX.MySqlClient caches passwords if 'Persist Security Info' is false

• Updated license banner in all source files to include FLOSS exception

• Added new .Types namespace and implementations for most current MySql types

• Added MySqlField41 as a subclass of MySqlField

• Changed many classes to now use the new .Types types

• Changed type enum int to Int32, short to Int16, and bigint to Int64

• Added dummy types UInt16, UInt32, and UInt64 to allow an unsigned parameter to be made

• Connections are now reset when they are pulled from the connection pool

• Refactored auth code in driver so it can be used for both auth and reset

• Added UserReset test in PoolingTests.cs

• Connections are now reset using COM_CHANGE_USER when pulled from the pool

• Implemented SingleResultSet behavior

• Implemented support of unicode

• Added char set mappings for utf-8 and ucs-2

• fixed Bug #4520 time fields overflow using bytefx .net mysql driver

• Modified time test in data type test fixture to check for time spans where hours > 24

• Fixed Bug #4505 Wrong string with backslash escaping in ByteFx.Data.MySqlClient.MySqlParameter

• Added code to Parameter test case TestQuoting to test for backslashes

• Fixed Bug #4486 mysqlcommandbuilder fails with multi-word column names

• Fixed bug in TokenizeSql where underscore would terminate character capture in parameter name

• Added test case for spaces in column names

• Fixed bug# 4324 - MySqlDataReader.GetBytes don't works correctly

• Added GetBytes() test case to DataReader test fixture

• Now reading all server variables in InternalConnection.Configure into Hashtable

• Now using string[] for index map in CharSetMap

• Added CRInSQL test case for carriage returns in SQL

• setting maxPacketSize to default value in Driver.ctor

• Fixed bug #4442 - Setting MySqlDbType on a parameter doesn't set generic type

• Removed obsolete column types Long and LongLong

• Fixed bug# 4071 - Overflow exception thrown when using "use pipe" on connection string

• Changed "use pipe" keyword to "pipe name" or just "pipe"

• Allow reading multiple resultsets from a single query

• Added flags attribute to ServerStatusFlags enum

• Changed name of ServerStatus enum to ServerStatusFlags

• Fixed BUG #4386 - Inserted data row doesn't update properly

• Fixed bug #4074 - Error processing show create table

• Change Packet.ReadLenInteger to ReadPackedLong and added packet.ReadPackedInteger that alwasy reads integers packed with 2,3,4

• Added syntax.cs test fixture to test various SQL syntax bugs

• Fixed bug# 4149 Improper handling of time values. Now time value of 00:00:00 is not treated as null.

• Moved all test suite files into TestSuite folder

• Fixed bug where null column would move the result packet pointer backward

• Added new nant build script

• Fixed BUG #3917 - clear tablename so it will be regen'ed properly during the next GenerateSchema.

• Fixed bug #3915 - GetValues was always returning zero and was also always trying to copy all fields rather than respecting the size of the array passed in.

• Implemented shared memory access protocol

• Implemented prepared statements for MySQL 4.1

• Implemented stored procedures for MySQL 5.0

• Renamed MySqlInternalConnection to InternalConnection

• SQL is now parsed as chars, fixes problems with other languages

• Added logging and allow batch connection string options

• Fixed bug #3888 - RowUpdating event not set when setting the DataAdapter property

• Fixed bug in char set mapping

• Implemented 4.1 authentication

• Improved open/auth code in driver

• Improved how connection bits are set during connection

• Database name is now passed to server during initial handshake

• Changed namespace for client to MySql.Data.MySqlClient

• Changed assembly name of client to MySql.Data.dll

• Changed license text in all source files to GPL

• Added the MySqlClient.build Nant file

• Removed the mono batch files

• Moved some of the unused files into notused folder so nant build file can use wildcards

• Implemented shared memory accesss

• Major revamp in code structure

• Prepared statements now working for MySql 4.1.1 and later

• Finished implementing auth for 4.0, 4.1.0, and 4.1.1

• Changed namespace from MySQL.Data.MySQLClient back to MySql.Data.MySqlClient

• Fixed bug in CharSetMapping where it was trying to use text names as ints

• Changed namespace to MySQL.Data.MySQLClient

• Integrated auth changes from UC2004

• Fixed bug where calling any of the GetXXX methods on a datareader before or after reading data would not throw the appropriate exception (thanks Luca Morelli <[email protected]>)

• Added TimeSpan code in parameter.cs to properly serialize a timespan object to mysql time format (thanks Gianluca Colombo <[email protected]>)

• Added TimeStamp to parameter serialization code. Prevented DataAdatper updates from working right (thanks MIchael King)

• Fixed a misspelling in MySqlHelper.cs (thanks Patrick Kristiansen)

• Driver now using charset number given in handshake to create encoding

• Changed command editor to point to MySqlClient.Design

• Fixed bug in Version.isAtLeast

• Changed DBConnectionString to support changes done to MySqlConnectionString

• Removed SqlCommandEditor and DataAdapterPreviewDialog

• Using new long return values in many places

• Integrated new CompressedStream class

• Changed ConnectionString and added attributes to allow it to be used in MySqlClient.Design

• Changed packet.cs to support newer lengths in ReadLenInteger

• changed other classes to use new properties and fields of MySqlConnectionString

• ConnectionInternal is now using PING to see if the server is alive

• Moved toolbox bitmaps into resource/

• Changed field.cs to allow values to come directly from row buffer

• Changed to use the new driver.Send syntax

• Using a new packet queueing system

• started work handling the "broken" compression packet handling

• Fixed bug in StreamCreator where failure to connect to a host would continue to loop infinitly (thanks Kevin Casella)

• Improved connectstring handling

• Moved designers into Pro product

• Removed some old commented out code from command.cs

• Fixed a problem with compression

• Fixed connection object where an exception throw prior to the connection opening would not leave the connection in the connecting state (thanks Chris Cline )

• Added GUID support

• Fixed sequence out of order bug (thanks Mark Reay)

• Enum values now supported as parameter values (thanks Philipp Sumi)

• Year datatype now supported

• fixed compression

• Fixed bug where a parameter with a TimeSpan as the value would not serialize properly

• Fixed bug where default ctor would not set default connection string values

• Added some XML comments to some members

• Work to fix/improve compression handling

• Improved ConnectionString handling so that it better matches the standard set by SqlClient.

• A MySqlException is now thrown if a username is not included in the connection string

• Localhost is now used as the default if not specified on the connection string

• An exception is now thrown if an attempt is made to set the connection string while the connection is open

• Small changes to ConnectionString docs

• Removed MultiHostStream and MySqlStream. Replaced it with Common/StreamCreator

• Added support for Use Pipe connection string value

• Added Platform class for easier access to platform utility functions

• Fixed small pooling bug where new connection was not getting created after IsAlive fails

• Added Platform.cs and StreamCreator.cs

• Fixed Field.cs to properly handle 4.1 style timestamps

• Changed Common.Version to Common.DBVersion to avoid name conflict

• Fixed field.cs so that text columns return the right field type (thanks [email protected])

• Added MySqlError class to provide some reference for error codes (thanks Geert Veenstra)

• Added Unix socket support (thanks Mohammad DAMT [[email protected]])

• only calling Thread.Sleep when no data is available

• improved escaping of quote characters in parameter data

• removed misleading comments from parameter.cs

• fixed pooling bug

• same pooling bug fixed again!! ;-)

• Fixed ConnectionSTring editor dialog (thanks marco p (pomarc))

• UserId now supported in connection strings (thanks Jeff Neeley)

• Attempting to create a parameter that is not input throws an exception (thanks Ryan Gregg)

• Added much documentation

• checked in new MultiHostStream capability. Big thanks to Dan Guisinger for this. he originally submitted the code and idea of supporting multiple machines on the connect string.

• Added alot of documentation. Still alot to do.

• Fixed speed issue with 0.73

• changed to Thread.Sleep(0) in MySqlDataStream to help optimize the case where it doesn't need to wait (thanks Todd German)

• Prepopulating the idlepools to MinPoolSize

• Fixed MySqlPool deadlock condition as well as stupid bug where CreateNewPooledConnection was not ever adding new connections to the pool. Also fixed MySqlStream.ReadBytes and ReadByte to not use TicksPerSecond which does not appear to always be right. (thanks Matthew J. Peddlesden)

• Fix for precision and scale (thanks Matthew J. Peddlesden)

• Added Thread.Sleep(1) to stream reading methods to be more cpu friendly (thanks Sean McGinnis)

• Fixed problem where ExecuteReader would sometime return null (thanks Lloyd Dupont )

• Fixed major bug with null field handling (thanks Naucki)

• enclosed queries for max_allowed_packet and characterset inside try catch (and set defaults)

• fixed problem where socket was not getting closed properly (thanks Steve!)

• Fixed problem where ExecuteNonQuery was not always returning the right value

• Fixed InternalConnection to not use @@session.max_allowed_packet but use @@max_allowed_packet. (Thanks Miguel)

• Added many new XML doc lines

• Fixed sql parsing to not send empty queries (thanks Rory)

• Fixed problem where the reader was not unpeeking the packet on close

• Fixed problem where user variables were not being handled (thanks Sami Vaaraniemi)

• Fixed loop checking in the MySqlPool (thanks Steve M. Brown)

• Fixed ParameterCollection.Add method to match SqlClient (thanks Joshua Mouch)

• Fixed ConnectionSTring parsing to handle no and yes for boolean and not lowercase values (thanks Naucki)

• Added InternalConnection class, changes to pooling

• Implemented Persist Security Info

• Added security.cs and version.cs to project

• Fixed DateTime handling in Parameter.cs (thanks Burkhard Perkens-Golomb)

• Fixed parameter serialization where some types would throw a cast exception

• Fixed DataReader to convert all returned values to prevent casting errors (thanks Keith Murray)

• Added code to Command.ExecuteReader to return null if the initial SQL command throws an exception (thanks Burkhard Perkens-Golomb)

• Fixed ExecuteScalar bug introduced with restructure

• Restructure to allow for LOCAL DATA INFILE and better sequencing of packets

• Fixed several bugs related to restructure.

• Early work done to support more secure passwords in Mysql 4.1. Old passwords in 4.1 not supported yet

• Parameters appearing after system parameters are now handled correctly (Adam M. (adammil))

• strings can now be assigned directly to blob fields (Adam M.)

• Fixed float parameters (thanks Pent)

• Improved Parameter ctor and ParameterCollection.Add methods to better match SqlClient (thx Joshua Mouch )

• Corrected Connection.CreateCommand to return a MySqlCommand type

• Fixed connection string designer dialog box problem (thanks Abraham Guyt)

• Fixed problem with sending commands not always reading the response packet (thanks Joshua Mouch )

• Fixed parameter serialization where some blobs types were not being handled (thanks Sean McGinnis )

• Removed spurious MessageBox.show from DataReader code (thanks Joshua Mouch )

• Fixed a nasty bug in the split sql code (thanks everyone! :-) )

• Fixed bug in MySqlStream where too much data could attempt to be read (thanks Peter Belbin)

• Implemented HasRows (thanks Nash Pherson)

• Fixed bug where tables with more than 252 columns cause an exception ( thanks Joshua Kessler )

• Fixed bug where SQL statements ending in ; would cause a problem ( thanks Shane Krueger )

• Fixed bug in driver where error messsages were getting truncated by 1 character (thanks Shane Krueger)

• Made MySqlException serializable (thanks Mathias Hasselmann)

• Updated some of the character code pages to be more accurate

• Fixed problem where readers could be opened on connections that had readers open

• Release of 0.70

• Moved test to separate assembly MySqlClientTests

• Fixed stupid problem in driver with sequence out of order (Thanks Peter Belbin)

• Added some pipe tests

• Increased default max pool size to 50

• Compiles with Mono 0-24

• Fixed connection and data reader dispose problems

• Added String datatype handling to parameter serialization

• Fixed sequence problem in driver that occured after thrown exception (thanks Burkhard Perkens-Golomb)

• Added support for CommandBehavior.SingleRow to DataReader

• Fixed command sql processing so quotes are better handled (thanks Theo Spears)

• Fixed parsing of double, single, and decimal values to account for non-English separators. You still have to use the right syntax if you using hard coded sql, but if you use parameters the code will convert floating point types to use '.' appropriately internal both into the server and out. [ Thanks anonymous ]

• Added MySqlStream class to simplify timeOuts and driver coding.

• Fixed DataReader so that it is closed properly when the associated connection is closed. [thanks smishra]

• Made client more SqlClient compliant so that DataReaders have to be closed before the connection can be used to run another command

• Improved DBNull.Value handling in the fields

• Added several unit tests

• Fixed MySqlException so that the base class is actually called :-o

• Improved driver coding

• Fixed bug where NextResult was returning false on the last resultset

• Added more tests for MySQL

• Improved casting problems by equating unsigned 32bit values to Int64 and usigned 16bit values to Int32, etc

• Added new ctor for MySqlParameter for (name, type, size, srccol)

• Fixed bug in MySqlDataReader where it didn't check for null fieldlist before returning field count

• Started adding MySqlClient unit tests (added MySqlClient/Tests folder and some test cases)

• Fixed some things in Connection String handling

• Moved INIT_DB to MySqlPool. I may move it again, this is in preparation of the conference.

• Fixed bug inside CommandBuilder that prevented inserts from happening properly

• Reworked some of the internals so that all three execute methods of Command worked properly

• FIxed many small bugs found during benchmarking

• The first cut of CoonectionPooling is working. "min pool size" and "max pool size" are respected.

• Work to enable multiple resultsets to be returned

• Character sets are handled much more intelligently now. The driver queries MySQL at startup for the default character set. That character set is then used for conversions if that code page can be loaded. If not, then the default code page for the current OS is used.

• Added code to save the inferred type in the name,value ctor of Parameter

• Also, inferred type if value of null parameter is changed using Value property

• Converted all files to use proper Camel case. MySQL is now MySql in all files. PgSQL is now PgSql

• Added attribute to PgSql code to prevent designer from trying to show

• Added MySQLDbType property to Parameter object and added proper conversion code to convert from DbType to MySQLDbType)

• Removed unused ObjectToString method from MySQLParameter.cs

• Fixed Add(..) method in ParameterCollection so that it doesn't use Add(name, value) instead.

• Fixed IndexOf and Contains in ParameterCollection to be aware that parameter names are now stored without @

• Fixed Command.ConvertSQLToBytes so it only allows characters that can be in MySQL variable names

• Fixed DataReader and Field so that blob fields read their data from Field.cs and GetBytes works right

• Added simple query builder editor to CommandText property of MySQLCommand

• Fixed CommandBuilder and Parameter serialization to account for Parameters not storing @ in their names

• Removed MySQLFieldType enum from Field.cs. Now using MySQLDbType enum

• Added Designer attribute to several classes to prevent designer view when using VS.Net

• Fixed Initial catalog typo in ConnectionString designer

• Removed 3 parameter ctor for MySQLParameter that conflicted with (name, type, value)

• changed MySQLParameter so paramName is now stored without leading @ (this fixed null inserts when using designer)

• Changed TypeConverter for MySQLParameter to use the ctor with all properties

• Fixed sequence issue in driver

• Added DbParametersEditor to make parameter editing more like SqlClient

• Fixed Command class so that parameters can be edited using the designer

• Update connection string designer to support Use Compression flag

• Fixed string encoding so that European characters like ä will work correctly

• Creating base classes to aid in building new data providers

• Added support for UID key in connection string

• Field, parameter, command now using DBNull.Value instead of null

• CommandBuilder using DBNull.Value

• CommandBuilder now builds insert command correctly when an auto_insert field is not present

• Field now uses typeof keyword to return System.Types (performance)

• MySQLCommandBuilder now implemented

• Transaction support now implemented (not all table types support this)

• GetSchemaTable fixed to not use xsd (for Mono)

• Driver is now Mono-compatible!!

• TIME data type now supported

• More work to improve Timestamp data type handling

• Changed signatures of all classes to match corresponding SqlClient classes

• Protocol compression using SharpZipLib (www.icsharpcode.net)

• Named pipes on Windows now working properly

• Work done to improve Timestamp data type handling

• Implemented IEnumerable on DataReader so DataGrid would work

• Speed increased dramatically by removing bugging network sync code

• Driver no longer buffers rows of data (more ADO.Net compliant)

• Conversion bugs related to TIMESTAMP and DATETIME fields fixed