1. MySQL Connector/NET

secrettownpanamanianMobile - Wireless

Dec 10, 2013 (3 years and 7 months ago)

308 views

1.MySQL Connector/NET
1.1.Introduction
MySQL Connector/NET enables developers to easily create.NET applications that require secure,
high-performance data connectivity with MySQL.It implements the required ADO.NET interfaces
and integrates into ADO.NET aware tools.Developers can build applications using their choice of
.NET languages.MySQL Connector/NET is a fully managed ADO.NET driver written in 100%
pure C#.
MySQL Connector/NET includes full support for:
 MySQL 5.0 features (stored procedures,etc.)
 MySQL 4.1 features (server-side prepared statements,Unicode,and shared memory access,etc.)
 Large-packet support for sending and receiving rows and BLOBs up to 2 gigabytes in size.
 Protocol compression which allows for compressing the data streambetween the client and serv-
er.
 Support for connecting using TCP/IP sockets,named pipes,or shared memory on Windows.
 Support for connecting using TCP/IP sockets or Unix sockets on Unix.
 Support for the open source Mono framework developed by Novell.
 Fully managed,does not utilize the MySQL client library.
The developers of MySQL Connector/NET greatly value the input of our users in the software de-
velopment process.If you find MySQL Connector/NET lacking some feature important to you,or if
you discover a bug,please use our MySQL Bug System [http://bugs.mysql.com] to request features
or report problems.
Community support for MySQL Connector/NET can be found through the forums at ht-
tp://forums.mysql.com and through the mailing lists at http://lists.mysql.com.Paid support is avail-
able fromMySQL AB,additional information is available at http://www.mysql.com/support/.
This document is intended as a user's guide to MySQL Connector/NET and not as a syntax refer-
ence.If you need detailed syntax information you should read the Documentation.chm file in-
cluded with the MySQL Connector/NET distribution.
1.2.Downloading and Installing MySQL Connector/
NET
MySQL Connector/NET runs on any platform that supports the.NET framework.The.NET frame-
work is primarily supported on recent versions of Microsoft Windows,and is supported on Linux
through the open source Mono framework developed by Novell (see
http://www.mono-project.com).
MySQL Connector/NET is installed through the use of a Windows Installer (.msi) installation
package,which can be used to install MySQL Connector/NET on all Windows operating systems.
The MSI package in contained within a ZIP archive named
mysql-connector-net-version.zip,where version indicates the MySQL Connector/
NET version.
MySQL Connector/NET is available for download from ht-
tp://dev.mysql.com/downloads/connector/net/1.0.html.
1
The Windows Installer engine was updated with the release of Windows XP;those using an older
version can reference this Microsoft Knowledge Base article
[http://support.microsoft.com/default.aspx?scid=kb;EN-US;292539] for information on upgrading
to the latest version.
To install MySQL Connector/NET,right-click on the MSI file and select Install.The installation
will begin automatically after the installer prompts you for your installation preferences.The Typ-
ical installation is recommended for most users.
If you are having problems running the installer,you can download a ZIP file without an installer as
an alternative.That file is called mysql-connector-net-version-noinstall.zip.Us-
ing a ZIP program,unpack it to a directory of your choice.
Unless you choose otherwise,MySQL Connector/NET is installed in C:\Program
Files\MySQL\MySQL Connector Net X.X.X,where X.X.X is replaced with the version
of MySQL Connector/NET you are installing.New installations do not overwrite existing versions
of MySQL Connector/NET.
1.3.Connector/NET Architecture
MySQL Connector/NET comprises several classes that are used to connect to the database,execute
queries and statements,and manage query results.
The following are the major classes of MySQL Connector/NET:
 MySqlCommand:Represents a SQL statement to execute against a MySQL database.
 MySqlCommandBuilder:Automatically generates single-table commands used to reconcile
changes made to a DataSet with the associated MySQL database.
 MySqlConnection:Represents an open connection to a MySQL Server database.
 MySqlDataAdapter:Represents a set of data commands and a database connection that are
used to fill a dataset and update a MySQL database.
 MySqlDataReader:Provides a means of reading a forward-only stream of rows from a
MySQL database.
 MySqlException:The exception that is thrown when MySQL returns an error.
 MySqlHelper:Helper class that makes it easier to work with the provider.
 MySqlTransaction:Represents a SQL transaction to be made in a MySQL database.
Each of these objects will be described in the upcoming sections.These sections are intended to be
an overview of the major classes of MySQL Connector/NET,and not a syntax reference.If you
need more detailed information you should read the Documentation.chm file included with the
MySQL Connector/NET distribution.
1.3.1.The MySqlCommand Class
The MySqlCommand class represents a SQL statement to execute against a MySQL database.
Note:Prior versions of the provider used the'@'symbol to mark parameters in SQL.This is incom-
patible with MySQL user variables,so the provider now uses the'?'symbol to locate parameters in
SQL.To support older code,you can set'old syntax=yes'in your connection string.If you do this,
please be aware that an exception will not be thrown if you fail to define a parameter that you inten-
ded to use in your SQL.
1.3.1.1.Properties
MySQL Connector/NET
2
The following properties are available:
 CommandText:Gets or sets the SQL statement to execute at the data source.
 CommandTimeout:Gets or sets the wait time before terminating the attempt to execute a com-
mand and generating an error.
 CommandType:Gets or sets a value indicating how the CommandText property is to be inter-
preted.Possible types are StoredProcedure,TableDirect,and Text.
 Connection:Gets or sets the MySqlConnection used by this instance of the MySqlCommand.
 IsPrepared:Is true if this command has been prepared,false otherwise.
 Parameters:Gets the MySqlParameterCollection.
 Transaction:Gets or sets the MySqlTransaction within which the MySqlCommand ex-
ecutes.
 UpdatedRowSource:Gets or sets how command results are applied to the DataRow when
used by the Update method of the DbDataAdapter.
1.3.1.2.Methods
The following methods are available:
 Cancel:Attempts to cancel the execution of a MySqlCommand.This operation is not suppor-
ted.
 Clone:Creates a clone of this MySqlCommand object.CommandText,Connection,and Trans-
action properties are included as well as the entire parameter list.
 CreateParameter:Creates a new instance of a MySqlParameter object.
 Dispose:Disposes of this instance of MySqlCommand.
 ExecuteNonQuery:Executes a SQL statement against the connection and returns the number
of rows affected.
 ExecuteReader:Sends the CommandText to the Connection and builds a MySqlDataReader.
 ExecuteScalar:Executes the query,and returns the first column of the first row in the result
set returned by the query.Extra columns or rows are ignored.
 Prepare:Creates a prepared version of the command on an instance of MySQL Server.
1.3.1.3.Usage
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 ExecuteNon-
Query,and closes the connection.To accomplish this,the ExecuteNonQuery is passed a connection
string and a query string that is a SQL INSERT statement.
1.3.1.3.1.VB.NET
The following example show how to use the MySqlCommand class with VB.NET:
Public Sub InsertRow(myConnectionString As String)
'If the connection string is null,use a default.
If myConnectionString =""Then
MySQL Connector/NET
3
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
1.3.1.3.2.C#
The following example show how to use the MySqlCommand class with C#:
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();
}
1.3.2.The MySqlCommandBuilder Class
The MySqlDataAdapter does not automatically generate the SQL statements required to reconcile
changes made to a DataSet with the associated instance of MySQL.However,you can create a
MySqlCommandBuilder object to automatically generate SQL statements for single-table updates if
you set the SelectCommand property of the MySqlDataAdapter.Then,any additional SQL state-
ments that you do not set are generated by the MySqlCommandBuilder.
The MySqlCommandBuilder registers itself as a listener for OnRowUpdating events whenever you
set the DataAdapter property.You can only associate one MySqlDataAdapter or MySqlCommand-
Builder object with each other at one time.
To generate INSERT,UPDATE,or DELETE statements,the MySqlCommandBuilder uses the Se-
lectCommand property to retrieve a required set of metadata automatically.If you change the Se-
lectCommand after the metadata has is retrieved (for example,after the first update),you should call
the RefreshSchema method to update the metadata.
The SelectCommand must also return at least one primary key or unique column.If none are
present,an InvalidOperation exception is generated,and the commands are not generated.
The MySqlCommandBuilder also uses the Connection,CommandTimeout,and Transaction proper-
ties referenced by the SelectCommand.The user should call RefreshSchema if any of these proper-
ties are modified,or if the SelectCommand itself is replaced.Otherwise the InsertCommand,Up-
dateCommand,and DeleteCommand properties retain their previous values.
If you call Dispose,the MySqlCommandBuilder is disassociated from the MySqlDataAdapter,and
the generated commands are no longer used.
1.3.2.1.Properties
The following properties are available:
MySQL Connector/NET
4
 DataAdapter:The MySqlCommandBuilder registers itself as a listener for RowUpdating
events that are generated by the MySqlDataAdapter specified in this property.When you create
a new instance MySqlCommandBuilder,any existing MySqlCommandBuilder associated with
this MySqlDataAdapter is released.
 QuotePrefix,QuoteSuffix:Database objects in MySQL can contain special characters
such as spaces that would make normal SQL strings impossible to correctly parse.Use of the
QuotePrefix and the QuoteSuffix properties allows the MySqlCommandBuilder to build SQL
commands that handle this situation.
1.3.2.2.Methods
The following methods are available:
 DeriveParameters:Retrieves parameter information from the stored procedure specified in
the MySqlCommand and populates the Parameters collection of the specified MySqlCommand
object.This method is not currently supported since stored procedures are not available in
MySql.
 GetDeleteCommand:Gets the automatically generated MySqlCommand object required to
performdeletions on the database.
 GetInsertCommand:Gets the automatically generated MySqlCommand object required to
performinsertions on the database.
 GetUpdateCommand:Gets the automatically generated MySqlCommand object required to
performupdates on the database.
 RefreshSchema:Refreshes the database schema information used to generate INSERT,UP-
DATE,or DELETE statements.
1.3.2.3.Usage
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.
1.3.2.3.1.VB.NET
The following example shows how to use the MySqlCommandBuilder class with VB.NET:
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
MySQL Connector/NET
5
1.3.2.3.2.C#
The following example shows how to use the MySqlCommandBuilder class with C#:
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;
}
1.3.3.The MySqlConnection Class
A MySqlConnection object represents a session to a MySQL Server data source.When you create
an instance of MySqlConnection,all properties are set to their initial values.For a list of these val-
ues,see the MySqlConnection constructor.
If the MySqlConnection goes out of scope,it is not closed.Therefore,you must explicitly close the
connection by calling Close or Dispose.
1.3.3.1.Properties
The following properties are available:
 ConnectionString:Gets or sets the string used to connect to a MySQL Server database.
 ConnectionTimeout:Gets the time to wait while trying to establish a connection before ter-
minating the attempt and generating an error.
 Database:Gets the name of the current database or the database to be used after a connection
is opened.
 DataSource:Gets the name of the MySQL server to which to connect.
 ServerThread:Returns the id of the server thread this connection is executing on.
 ServerVersion:Gets a string containing the version of the MySQL server to which the cli-
ent is connected.
 State:Gets the current state of the connection.
 UseConnection:Indicates if this connection should use compression when communicating
with the server.
1.3.3.2.Methods
MySQL Connector/NET
6
The following methods are available:
 BeginTransaction:Begins a database transaction.
 ChangeDatabase:Changes the current database for an open MySqlConnection.
 Close:Closes the connection to the database.This is the preferred method of closing any open
connection.
 CreateCommand:Creates and returns a MySqlCommand object associated with the MySql-
Connection.
 Dispose:Releases the resources used by the MySqlConnection.
 Open:Opens a database connection with the property settings specified by the Connection-
String.
 Ping:Pings the MySQL server.
1.3.3.3.Usage
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 ExecuteNon-
Query,and closes the connection.To accomplish this,the ExecuteNonQuery is passed a connection
string and a query string that is a SQL INSERT statement.
1.3.3.3.1.VB.NET
The following example shows how to use the MySqlConnection class with VB.NET:
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
1.3.3.3.2.C#
The following example shows how to use the MySqlConnection class with C#:
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();
MySQL Connector/NET
7
}
1.3.4.The MySqlDataAdapter Class
The MySQLDataAdapter serves as a bridge between a DataSet and MySQL for retrieving and sav-
ing data.The MySQLDataAdapter provides this bridge by mapping Fill,which changes the data in
the DataSet to match the data in the data source,and Update,which changes the data in the data
source to match the data in the DataSet,using the appropriate SQL statements against the data
source.
When the MySQLDataAdapter fills a DataSet,it will create the necessary tables and columns for the
returned data if they do not already exist.However,primary key information will not be included in
the implicitly created schema unless the MissingSchemaAction property is set to AddWithKey.You
may also have the MySQLDataAdapter create the schema of the DataSet,including primary key in-
formation,before filling it with data using FillSchema.
MySQLDataAdapter is used in conjunction with MySqlConnection and MySqlCommand to in-
crease performance when connecting to a MySQL database.
The MySQLDataAdapter also includes the SelectCommand,InsertCommand,DeleteCommand,Up-
dateCommand,and TableMappings properties to facilitate the loading and updating of data.
1.3.4.1.Properties
The following properties are available:
 AcceptChangesDuringFill:Gets or sets a value indicating whether AcceptChanges is
called on a DataRow after it is added to the DataTable during any of the Fill operations.
 ContinueUpdateOnError:Gets or sets a value that specifies whether to generate an excep-
tion when an error is encountered during a row update.
 DeleteCommand:Gets or sets a SQL statement or stored procedure used to delete records
fromthe data set.
 InsertCommand:Gets or sets a SQL statement or stored procedure used to insert records into
the data set.
 MissingMappingAction:Determines the action to take when incoming data does not have
a matching table or column.
 MissingSchemaAction:Determines the action to take when existing DataSet schema does
not match incoming data.
 SelectCommand:Gets or sets a SQL statement or stored procedure used to select records in
the data source.
 TableMappings:Gets a collection that provides the master mapping between a source table
and a DataTable.
 UpdateCommand:Gets or sets a SQL statement or stored procedure used to updated records in
the data source.
1.3.4.2.Methods
The following methods are available:
MySQL Connector/NET
8
 Fill:Adds or refreshes rows in the DataSet to match those in the data source using the DataSet
name,and creates a DataTable named"Table".
 FillSchema:Adds a DataTable named"Table"to the specified DataSet and configures the
schema to match that in the data source based on the specified SchemaType.
 GetFillParameters:Gets the parameters set by the user when executing an SQL SELECT
statement.
 Update:Calls the respective INSERT,UPDATE,or DELETE statements for each inserted,up-
dated,or deleted row in the specified DataSet.
1.3.4.3.Usage
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 ExecuteNon-
Query,and closes the connection.To accomplish this,the ExecuteNonQuery is passed a connection
string and a query string that is a SQL INSERT statement.
1.3.4.3.1.VB.NET
The following example shows how to use the MySqlDataAdapter class with VB.NET:
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
1.3.4.3.2.C#
The following example shows how to use the MySqlDataAdapter class with C#:
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;
}
1.3.5.The MySqlDataReader Class
The MySqlDataReader class provides a means of reading a forward-only stream of rows from a
MySQL database.
To create a MySQLDataReader,you must call the ExecuteReader method of the MySqlCommand
object,rather than directly using a constructor.
While the MySqlDataReader is in use,the associated MySqlConnection is busy serving the
MySqlDataReader,and no other operations can be performed on the MySqlConnection other than
closing it.This is the case until the Close method of the MySqlDataReader is called.
IsClosed and RecordsAffected are the only properties that you can call after the MySqlDataReader
is closed.Though the RecordsAffected property may be accessed at any time while the
MySqlDataReader exists,always call Close before returning the value of RecordsAffected to ensure
an accurate return value.
MySQL Connector/NET
9
For optimal performance,MySqlDataReader avoids creating unnecessary objects or making unne-
cessary copies of data.As a result,multiple calls to methods such as GetValue return a reference to
the same object.Use caution if you are modifying the underlying value of the objects returned by
methods such as GetValue.
1.3.5.1.Properties
The following properties are available:
 Depth:Gets a value indicating the depth of nesting for the current row.This method is not sup-
ported currently and always returns 0.
 FieldCount:Gets the number of columns in the current row.
 HasRows:Gets a value indicating whether the MySqlDataReader contains one or more rows.
 IsClosed:Gets a value indicating whether the data reader is closed.
 Item:Gets the value of a column in its native format.In C#,this property is the indexer for the
MySqlDataReader class.
 RecordsAffected:Gets the number of rows changed,inserted,or deleted by execution of
the SQL statement.
1.3.5.2.Methods
The following methods are available:
 Close:Closes the MySqlDataReader object.
 GetBoolean:Gets the value of the specified column as a Boolean.
 GetByte:Gets the value of the specified column as a byte.
 GetBytes:Reads a stream of bytes from the specified column offset into the buffer an array
starting at the given buffer offset.
 GetChar:Gets the value of the specified column as a single character.
 GetChars:Reads a stream of characters from the specified column offset into the buffer as an
array starting at the given buffer offset.
 GetDataTypeName:Gets the name of the source data type.
 GetDateTime:Gets the value of the specified column as a DateTime object.
 GetDecimal:Gets the value of the specified column as a Decimal object.
 GetDouble:Gets the value of the specified column as a double-precision floating point num-
ber.
 GetFieldType:Gets the Type that is the data type of the object.
 GetFloat:Gets the value of the specified column as a single-precision floating point number.
 GetGuid:Gets the value of the specified column as a GUID.
 GetInt16:Gets the value of the specified column as a 16-bit signed integer.
 GetInt32:Gets the value of the specified column as a 32-bit signed integer.
MySQL Connector/NET
10
 GetInt64:Gets the value of the specified column as a 64-bit signed integer.
 GetMySqlDateTime:Gets the value of the specified column as a MySqlDateTime object.
 GetName:Gets the name of the specified column.
 GetOrdinal:Gets the column ordinal,given the name of the column.
 GetSchemaTable:Returns a DataTable that describes the column metadata of the
MySqlDataReader.
 GetString:Gets the value of the specified column as a String object.
 GetTimeSpan:Gets the value of the specified column as a TimeSpan object.
 GetUInt16:Gets the value of the specified column as a 16-bit unsigned integer.
 GetUInt32:Gets the value of the specified column as a 32-bit unsigned integer.
 GetUInt64:Gets the value of the specified column as a 64-bit unsigned integer.
 GetValue:Gets the value of the specified column in its native format.
 GetValues:Gets all attribute columns in the collection for the current row.
 IsDBNull:Gets a value indicating whether the column contains non-existent or missing val-
ues.
 NextResult:Advances the data reader to the next result,when reading the results of batch
SQL statements.
 Read:Advances the MySqlDataReader to the next record.
1.3.5.3.Usage
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
1.3.5.3.1.VB.NET
The following example shows how to use the MySqlDataReader class with VB.NET:
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
1.3.5.3.2.C#
The following example shows how to use the MySqlDataReader class with C#:
MySQL Connector/NET
11
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();
}
1.3.6.The MySqlException Class
This class is created whenever the MySql Data Provider encounters an error generated from the
server.
Any open connections are not automatically closed when an exception is thrown.If the client applic-
ation determines that the exception is fatal,it should close any open MySqlDataReader objects or
MySqlConnection objects.
1.3.6.1.Properties
The following properties are available:
 HelpLink:Gets or sets a link to the help file associated with this exception.
 InnerException:Gets the Exception instance that caused the current exception.
 IsFatal:True if this exception was fatal and cause the closing of the connection,false other-
wise.
 Message:Gets a message that describes the current exception.
 Number:Gets a number that identifies the type of error.
 Source:Gets or sets the name of the application or the object that causes the error.
 StackTrace:Gets a string representation of the frames on the call stack at the time the current
exception was thrown.
 TargetSite:Gets the method that throws the current exception.
1.3.6.2.Methods
The MySqlException class has no methods.
1.3.6.3.Usage
The following example generates a MySqlException due to a missing server,and then displays the
exception.
1.3.6.3.1.VB.NET
This example demonstrates how to use the MySqlException class with VB.NET:
MySQL Connector/NET
12
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
1.3.6.3.2.C#
This example demonstrates how to use the MySqlException class with C#:
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 );
}
}
1.3.7.The MySqlHelper Class
Helper class that makes it easier to work with the provider.Developers can use the methods of this
class to automatically performcommon tasks.
1.3.7.1.Properties
The MySqlHelper class has no properties.
1.3.7.2.Methods
The following methods are available:
 ExecuteDataRow:Executes a single SQL command and returns the first row of the resultset.
A new MySqlConnection object is created,opened,and closed during this method.
 ExecuteDataset:Executes a single SQL command and returns the resultset in a DataSet.A
new MySqlConnection object is created,opened,and closed during this method.
 ExecuteNonQuery:Executes a single command against a MySQL database.The MySqlCon-
nection is assumed to be open when the method is called and remains open after the method
completes.
 ExecuteReader:Overloaded.Executes a single command against a MySQL database.
 ExecuteScalar:Execute a single command against a MySQL database.
 UpdateDataSet:Updates the given table with data fromthe given DataSet.
MySQL Connector/NET
13
1.3.8.The MySqlTransaction Class
Represents a SQL transaction to be made in a MySQL database.
1.3.8.1.Properties
The following properties are available:
 Connection:Gets the MySqlConnection object associated with the transaction,or a null ref-
erence (Nothing in Visual Basic) if the transaction is no longer valid.
 IsolationLevel:Specifies the IsolationLevel for this transaction.
1.3.8.2.Methods
The following methods are available:
 Commit:Commits the database transaction.
 Rollback:Rolls back a transaction froma pending state.
1.3.8.3.Usage
The following example creates a MySqlConnection and a MySqlTransaction.It also demonstrates
how to use the BeginTransaction,Commit,and Rollback methods.
1.3.8.3.1.VB.NET
The following example shows how to use the MySqlTransaction class with VB.NET:
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() & _
MySQL Connector/NET
14
"was encountered while inserting the data.")
Console.WriteLine("Neither record was written to database.")
Finally
myConnection.Close()
End Try
End Sub'RunTransaction
1.3.8.3.2.C#
The following example shows how to use the MySqlTransaction class with C#:
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();
}
}
1.4.Using MySQL Connector/NET
1.4.1.Introduction
MySQL Connector/NET
15
In this section we will cover some of the more common use cases for Connector/NET,including
BLOB handling,date handling,and using Connector/NET with common tools such as Crystal Re-
ports.
1.4.2.Connecting to MySQL Using MySQL Connector/NET
1.4.2.1.Introduction
All interaction between a.NET application and the MySQL server is routed through a MySqlCon-
nection object.Before your application can interact with the server,a MySqlConnection ob-
ject 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.
1.4.2.2.Creating a Connection String
The MySqlConnection object is configured using a connection string.A connection string con-
tains 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 documenta-
tion):
 Server:The name or network address of the instance of MySQL to which to connect.The de-
fault is localhost.Aliases include host,Data Source,DataSource,Address,Ad-
dr and Network Address.
 Uid:The MySQL user account to use when connecting.Aliases include User Id,Username
and User name.
 Pwd:The password for the MySQL account being used.Alias Password can also be used.
 Database:The default database that all statements are applied to.Default is mysql.Alias
Initial Catalog can also be used.
 Port:The port MySQL is using to listen for connections.Default is 3306.Specify -1 for this
value to use a named pipe connection.
1.4.2.3.Opening a Connection
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
MySQL Connector/NET
16
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#]
C#EXAMPLE PENDING
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#]
C#EXAMPLE PENDING
Once the connection is open it can be used by the other MySQL Connector/NET classes to commu-
nicate with the MySQL server.
1.4.2.4.Handling Connection Errors
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 re-
turn a MySqlException object.This object has two properties that are of interest when handling
errors:
 Message:A message that describes the current exception.
 Number:The MySQL error number.
When handling errors,you can your application's response based on the error number.The two most
MySQL Connector/NET
17
common error numbers when connecting are as follows:
 0:Cannot connect to server.
 1045:Invalid username and/or password.
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#]
C#EXAMPLE PENDING
1.4.3.Using the MySQL Connector/NET with Prepared Statements
1.4.3.1.Introduction
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.
1.4.3.2.Preparing Statements in MySQL Connector/NET
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
MySQL Connector/NET
18
.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#]
C#EXAMPLE PENDING
1.4.4.Accessing Stored Procedures with MySQL Connector/NET
1.4.4.1.Introduction
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 pro-
cedure instead.
Stored procedures can be particularly useful in situations such as the following:
 When multiple client applications are written in different languages or work on different plat-
forms,but need to performthe same database operations.
 When security is paramount.Banks,for example,use stored procedures for all common opera-
tions.This provides a consistent and secure environment,and procedures can ensure that each
operation is properly logged.In such a setup,applications and users would not get any access to
the database tables directly,but can only execute specific stored procedures.
MySQL Connector/NET supports the calling of stored procedures through the MySqlCommand ob-
ject.Data can be passed in and our of a MySQL stored procedure through use of the MySqlCom-
mand.Parameters collection.
MySQL Connector/NET
19
This section will not provide in-depth information on creating Stored Procedures,for such informa-
tion please refer to the Stored Procedures
[http://dev.mysql.com/doc/mysql/en/stored-procedures.html] 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.
1.4.4.2.Creating Stored Procedures fromMySQL Connector/NET
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#]
C#EXAMPLE PENDING
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.
1.4.4.3.Calling a Stored Procedure fromMySQL 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 prop-
erty 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 MySqlCom-
mand.ExecuteNonQuery() method:
[VB]
Dim conn As New MySqlConnection
Dim cmd As New MySqlCommand
MySQL Connector/NET
20
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#]
C#EXAMPLE PENDING
Once the stored procedure is called,the values of output parameters can be retrieved by using the
.Value property of the MySqlConnector.Parameters collection.
1.4.5.Handling BLOB Data With Connector/NET
1.4.5.1.Introduction
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.
1.4.5.2.Preparing the MySQL Server
The first step is using MySQL with BLOB data is to configure the server.Let's start by creating a ta-
ble 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,
MySQL Connector/NET
21
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 vari-
able determines how large of a packet (i.e.a single row) can be sent to the MySQL server.By de-
fault,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 ap-
propriate setting.After adjusting the value,click the Apply Changes button and restart the server us-
ing the Service Control screen of MySQL Administrator.You can also adjust this value dir-
ectly in the my.cnf file (add a line that reads max_allowed_packet=xxM),or use the SET
max_allowed_packet=xxM;syntax fromwithin 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.
1.4.5.3.Writing a File to the Database
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 FileStreamobject,reads it into a byte array,and inserts it in-
to 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!",_
MySQL Connector/NET
22
"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#]
C#EXAMPLE PENDING
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 FileStreamobject.
After assigning the byte array as a parameter of the MySqlCommand object,the ExecuteNon-
Query method is called and the BLOB is inserted into the file table.
1.4.5.4.Reading a BLOBfromthe Database to a File on Disk
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()
MySQL Connector/NET
23
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#]
C#EXAMPLE PENDING
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 FileStreamobject.
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.
1.4.6.Using MySQL Connector/NET with Crystal Reports
1.4.6.1.Introduction
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 direct-
ory of your MySQL Connector/NET installation.
1.4.6.2.Creating a Data Source
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 ap-
plication 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 data-
set.If you forget a column you must re-create the dataset before the column can be added to the re-
port.
The following code can be used to create a dataset froma 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"
MySQL Connector/NET
24
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#]
C#EXAMPLE PENDING
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[http://dev.mysql.com/downloads/connector/odbc/3.51.html].
1.4.6.3.Creating the Report
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.
1.4.6.4.Displaying the Report
To display a report we first populate a dataset with the data needed for the report,then load the re-
port 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:
 CrytalDecisions.CrystalReports.Engine
 CrystalDecisions.ReportSource
 CrystalDecisions.Shared
 CrystalDecisions.Windows.Forms
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 formnamed myViewer.
MySQL Connector/NET
25
[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#]
C#EXAMPLE PENDING
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 MySql-
Command 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:
Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data
Imports MySql.Data.MySqlClient
MySQL Connector/NET
26
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
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 to-
gether on the client-side,which will be slower than using a pre-saved dataset.
1.4.7.Handling Date and Time Information in MySQL Connector/
NET
1.4.7.1.Introduction
MySQL and the.NET languages handle date and time information differently,with MySQL allow-
ing 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.
1.4.7.2.Problems when Using Invalid Dates
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 Sys-
tem.ArgumentOutOfRangeException exception to occur.
1.4.7.3.Restricting Invalid Dates
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
MySQL Connector/NET
27
where.NET and non.NET code are used to manipulate the database,as each application must per-
formits 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 ht-
tp://dev.mysql.com/doc/mysql/en/server-sql-mode.html.
1.4.7.4.Handling Invalid Dates
While it is strongly recommended that you avoid the use of invalid dates within your.NET applica-
tion,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:
1.Data binding for invalid dates can still cause errors (zero dates like 0000-00-00 do not seem to
have this problem).
2.The ToString method return a date formatted in the standard MySQL format (e.g.
2005-02-23 08:50:25).This differs fromthe ToString behavior of the.NET DateTime class.
3.The MySqlDateTime class supports NULL dates,while the.NET DateTime class does not.
This can cause errors when trying to convert a MySQLDateTime to a DateTime if you do not
check for NULL first.
Because of the known issues,the best recommendation is still to use only valid dates in your applic-
ation.
1.4.7.5.Handling NULL Dates
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 = Nothing
End If
[C#]
C#EXAMPLE PENDING
MySQL Connector/NET
28
NULL values will work in a dataset and can be bound to formcontrols without special handling.
1.5.Changelogs
1.5.1.Version 2.0.0
 Fixed an exception when trying to use a stored procedure when Connection.Database is not pop-
ulated.(Bug#11450)
 Certain malformed queries will trigger a"Connection must be valid and open"error message.
(Bug#11490)
1.5.2.Version 1.0.5
 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 refer-
ences 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 set-
tings.(WL#8228)
 Fixed an exception when trying to use a stored procedure when Connection.Database is not pop-
ulated.(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
[http://bugs.mysql.com/bug.php?id=9722])
 Fixed error when MySqlConnection.clone is called and a connection string had not yet been set
on the original connection.(Bug#10281 [http://bugs.mysql.com/bug.php?id=10281])
 Added support to call a stored function from Connector/NET.(Bug#10644
[http://bugs.mysql.com/bug.php?id=10644])
1.5.3.Version 1.0.4 1-20-05
 Bug#7243 calling prepare causing exception [fixed]
 Fixed another small problemwith prepared statements
 Bug#7258 MySqlCommand.Connection returns an IDbConnection [fixed]
MySQL Connector/NET
29
 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 problemthat causes named pipes to not work with some blob functionality
 Fixed problemwith shared memory connections
 Bug#7436 Problemwith Multiple resultsets...[fixed]
 Added or filled out several more topics in the API reference documentation
1.5.4.Version 1.0.3-gamma 12-10-04
 Made MySQL the default named pipe name
 Now SHOWCOLLATION 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 problemwith detecting null values when using prepared statements
 Bug#6902 Errors in parsing stored procedure parameters [fixed]
 Bug#6668 Integer"out"parameter fromstored 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
MySQL Connector/NET
30
 Added Ping method to MySqlConnection
 Changed the name of the test suite to MySql.Data.Tests.dll
1.5.5.Version 1.0.2-gamma 04-11-15
 Fixed problem with MySqlBinary where string values could not be used to update extended text
columns
 Fixed Installation directory ignored using custominstallation (Bug#6329)
 Fixed problemwhere 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"prob-
lem.
 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
1.5.6.Version 1.0.1-beta2 04-10-27
 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 problemwhere connector was not issuing a CMD_QUIT before closing the socket
 Fixed BUG#5392 MySqlCommand sees"?"as parameters in string literals
 Fixed problemwith 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
MySQL Connector/NET
31
 Field buffers being reused to decrease memory allocations and increase speed
 Fixed problemwhere using old syntax while using the interfaces caused problems
 Using PacketWriter instead of Packet for writing to streams
 Refactored compression code into CompressedStreamto 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 problemwhere connection lifetime on the connect string was not being respected
 Fixed problemwhere Min Pool Size was not being respected
 Fixed MySqlDataReader and'show tables from...'behaviour (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 EnsureCa-
pacity
 Fixed GetBoolean returns wrong values (Bug#6227)
 Fixed IndexOutOfBounds when reading BLOB with DataReader with GetString(index) (Bug
#6230)
1.5.7.Version 1.0.0 04-09-01
 Fixed BUG#3889 Thai encoding not correctly supported
MySQL Connector/NET
32
 Updated many of the test cases
 Fixed problemwith 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 themup better later)
 Removed some last references to ByteFX
1.5.8.Version 0.9.0 04-08-30
 Added test fixture for prepared statements
 All type classes now implement a SerializeBinary method for sending their data to a Packet-
Writer
 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 problemwhere GetSchema would return false for IsUnique when the column is key
 MySqlDataReader GetXXX methods now using the field level MySqlValue object and not per-
forming conversions
 FIXED BUG#5097:DataReader returning NULL for time column
 Added test case for LOAD DATA LOCAL INFILE
 Added replacetext customnant 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
MySQL Connector/NET
33
 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 enumint 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 fromthe 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 fromthe 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 Byte-
Fx.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 froma single query
MySQL Connector/NET
34
 Added flags attribute to ServerStatusFlags enum
 Changed name of ServerStatus enumto 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 GenerateS-
chema.
 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
MySQL Connector/NET
35
 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 fromMySQL.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 fromUC2004
 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 <morelli.luca@iol.it>)
 Added TimeSpan code in parameter.cs to properly serialize a timespan object to mysql time
format (thanks Gianluca Colombo <g.colombo@alfi.it>)
 Added TimeStamp to parameter serialization code.Prevented DataAdatper updates from work-
ing right (thanks MIchael King)
 Fixed a misspelling in MySqlHelper.cs (thanks Patrick Kristiansen)
1.5.9.Version 0.76
 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 CompressedStreamclass
 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 fromrow 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)
MySQL Connector/NET
36
 Improved connectstring handling
 Moved designers into Pro product
 Removed some old commented out code fromcommand.cs
 Fixed a problemwith 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)
1.5.10.Version 0.75
 Enumvalues 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 connec-
tion is open
 Small changes to ConnectionString docs
 Removed MultiHostStreamand MySqlStream.Replaced it with Common/StreamCreator
 Added support for Use Pipe connection string value
 Added Platformclass for easier access to platformutility 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 beni27@gmx.net)
 Added MySqlError class to provide some reference for error codes (thanks Geert Veenstra)
1.5.11.Version 0.74
MySQL Connector/NET
37
 Added Unix socket support (thanks Mohammad DAMT [md@mt.web.id])
 only calling Thread.Sleep when no data is available
 improved escaping of quote characters in parameter data
 removed misleading comments fromparameter.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 McGin-
nis)
 Fixed problemwhere 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 problemwhere socket was not getting closed properly (thanks Steve!)
 Fixed problemwhere 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 problemwhere the reader was not unpeeking the packet on close
 Fixed problemwhere user variables were not being handled (thanks Sami Vaaraniemi)
 Fixed loop checking in the MySqlPool (thanks Steve M.Brown)
MySQL Connector/NET
38
 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 sup-
ported yet
 Parameters appearing after systemparameters are now handled correctly (AdamM.(adammil))
 strings can now be assigned directly to blob fields (AdamM.)
 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 AbrahamGuyt)
 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 Mc-
Ginnis )
 Removed spurious MessageBox.show fromDataReader code (thanks Joshua Mouch )
 Fixed a nasty bug in the split sql code (thanks everyone!:-) )
1.5.12.Version 0.71
 Fixed bug in MySqlStreamwhere 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 )
MySQL Connector/NET
39
 Fixed bug in driver where error messsages were getting truncated by 1 character (thanks Shane
Krueger)
 Made MySqlException serializable (thanks Mathias Hasselmann)
1.5.13.Version 0.70
 Updated some of the character code pages to be more accurate
 Fixed problemwhere readers could be opened on connections that had readers open
 Release of 0.70
 Moved test to separate assembly MySqlClientTests
 Fixed stupid problemin 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 MySqlStreamclass 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 connec-
tion 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
MySQL Connector/NET
40
 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 fromhappening 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 respec-
ted.
 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 fromtrying to show
 Added MySQLDbType property to Parameter object and added proper conversion code to con-
vert fromDbType to MySQLDbType)
 Removed unused ObjectToString method fromMySQLParameter.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 vari-
able 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 enumfromField.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)
MySQL Connector/NET
41
 changed MySQLParameter so paramName is now stored without leading @ (this fixed null in-
serts when using designer)
 Changed TypeConverter for MySQLParameter to use the ctor with all properties
1.5.14.Version 0.68
 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)
1.5.15.Version 0.65
 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
1.5.16.Version 0.60
 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
1.5.17.Version 0.50
MySQL Connector/NET
42
 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
MySQL Connector/NET
43