JavaScript Editor Source code editor     What Is Ajax 

Main Page

23.2. MySQL Connector/NET

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. Connector/NET is a fully managed ADO.NET driver written in 100% pure C#.

Connector/NET includes full support for:

This document is intended as a user's guide to Connector/NET and includes a full syntax reference. Syntax information is also included within the Documentation.chm file included with the Connector/NET distribution.

If you are using MySQL 5.0 or later, and Visual Studio as your development environment, you may want also want to use the MySQL Visual Studio Plugin. The plugin acts as a DDEX (Data Designer Extensibility) provider, enabling you to use the data design tools within Visual Studio to manipulate the schema and objects within a MySQL database. For more information, see Section 23.3, “MySQL Visual Studio Plugin”.

Note

Connector/NET 5.1.2 and later include the Visual Studio Plugin by default.

23.2.1. Connector/NET Versions

There are currently three versions of Connector/NET available:

  • Connector/NET 1.0 includes support for MySQL 4.0, and MySQL 5.0 features, and full compatibility with the ADO.NET driver interface.

    Connector/NET 5.0 includes support for MySQL 4.0, MySQL 4.1, MySQL 5.0 and MySQL 5.1 features. Connector/NET 5.0 also includes full support for the ADO.Net 2.0 interfaces and subclasses, includes support for the usage advisor and performance monitor (PerfMon) hooks.

    Connector/NET 5.1 includes support for MySQL 4.0, MySQL 5.0, MySQL 5.1 and MySQL 6.0 (Falcon Preview) features. Connector/NET 5.1 also includes support for a new membership/role provider, Compact Framework 2.0, a new stored procedure parser and improvements to GetSchema. Connector/NET 5.1 also includes the Visual Studio Plugin as a standard installable component.

Note

Version numbers for MySQL products are formatted as X.X.X. However, Windows tools (Control Panel, properties display) may show the version numbers as XX.XX.XX. For example, the official MySQL formatted version number 5.0.9 may be displayed by Windows tools as 5.00.09. The two versions are the same; only the number display format is different.

23.2.2. Connector/NET Installation

Connector/NET runs on any platform that supports the .NET framework. The .NET framework is primarily supported on recent versions of Microsoft Windows, and is supported on Linux through the Open Source Mono framework (see http://www.mono-project.com).

Connector/NET is available for download from http://dev.mysql.com/downloads/connector/net/1.0.html.

23.2.2.1. Installing Connector/NET on Windows

On Windows, installation is supported either through a binary installation process or by downloading a Zip file with the Connector/NET components.

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

23.2.2.1.1. Installing Connector/NET using the Installer

Using the installer is the most straightforward method of installing Connector/NET on Windows and the installed components include the source code, test code and full reference documentation.

Connector/NET is installed through the use of a Windows Installer (.msi) installation package, which can be used to install 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 Connector/NET version.

To install Connector/NET:

  1. Double click on the MSI installer file extracted from the Zip you downloaded. Click Next to start the installation.

    Connector/NET Windows Installer -
                Welcome
  2. You must choose the type of installation that you want to perform.

    Connector/NET Windows Installer -
                Installation type

    For most situations, the Typical installation will be suitable. Click the Typical button and proceed to Step 5. A Complete installation installs all the available files. To conduct a Complete installation, click the Complete button and proceed to step 5. If you want to customize your installation, including choosing the components to install and some installation options, click the Custom button and proceed to Step 3.

    The Connector/NET installer will register the connector within the Global Assembly Cache (GAC) - this will make the Connector/NET component available to all applications, not just those where you explicitly reference the Connector/NET component. The installer will also create the necessary links in the Start menu to the documentation and release notes.

  3. If you have chosen a custom installation, you can select the individual components that you want to install, including the core interface component, supporting documentation (a CHM file) samples and examples and the source code. Select the items, and their installation level, and then click Next to continue the installation.

    Note

    For Connector/NET 1.0.8 or lower and Connector 5.0.4 and lower the installer will attempt to install binaries for both 1.x and 2.x of the .NET Framework. If you only have one version of the framework installed, the connector installation may fail. If this happens, you can choose the framework version to be installed through the custom installation step.

    Connector/NET Windows Installer -
                Custom setup
  4. You will be given a final opportunity to confirm the installation. Click Install to copy and install the files onto your machine.

    Connector/NET Windows Installer -
                Confirming installation
  5. Once the installation has been completed, click Finish to exit the installer.

Unless you choose otherwise, 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 Connector/NET you are installing. New installations do not overwrite existing versions of Connector/NET.

Depending on your installation type, the installed components will include some or all of the following components:

  • bin - Connector/NET MySQL libraries for different versions of the .NET environment.

  • docs - contains a CHM of the Connector/NET documentation.

  • samples - sample code and applications that use the Connector/NET component.

  • src - the source code for the Connector/NET component.

You may also use the /quiet or /q command line option with the msiexec tool to install the Connector/NET package automatically (using the default options) with no notification to the user. Using this option you cannot select options and no prompts, messages or dialog boxes will be displayed.

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

To provide a progress bar to the user during automatic installation, but still without presenting the user with a dialog box of the ability to select options, use the /passive option.

23.2.2.1.2. Installing Connector/NET using the Zip package

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. Once downloaded, you can extract the files to a location of your choice.

The .zip file contains the following directories:

  • bin - Connector/NET MySQL libraries for different versions of the .NET environment.

  • doc - contains a CHM of the Connector/NET documentation.

  • Samples - sample code and applications that use the Connector/NET component.

  • mysqlclient - the source code for the Connector/NET component.

  • testsuite - the test suite used to verify the operation of the Connector/NET component.

23.2.2.2. Installing Connector/NET on Unix with Mono

There is no installer available for installing the Connector/NET component on your Unix installation. However, the installation is very simple. Before installing, please ensure that you have a working Mono project installation.

Note that you should only install the Connector/NET component on Unix environments where you want to connect to a MySQL server through the Mono project. If you are deploying or developing on a different environment such as Java or Perl then you should use a more appropriate connectivity component. See Chapter 23, Connectors, or Chapter 22, APIs and Libraries, for more information.

To install Connector/NET on Unix/Mono:

  1. Download the mysql-connector-net-version-noinstall.zip and extract the contents.

  2. Copy the MySql.Data.dll file to your Mono project installation folder.

  3. You must register the Connector/NET component in the Global Assembly Cache using the gacutil command:

    shell> gacutil /i MySql.Data.dll

Once installed, applications that are compiled with the Connector/NET component need no further changes. However, you must ensure that when you compile your applications you include the Connector/NET component using the -r:MySqlData.dll command line option.

23.2.2.3. Installing Connector/NET using the Source

Caution

You should read this section only if you are interested in helping us test our new code. If you just want to get Connector/NET up and running on your system, you should use a standard release distribution.

To be able to access the Connector/NET source tree, you must have Subversion installed. Subversion is freely available from http://subversion.tigris.org/.

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

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

shell> svn co
http://svn.mysql.com/svnpublic/connector-net

A Visual Studio project is included in the source which you can use to build Connector/NET.

23.2.3. Connector/NET Examples and Usage Guide

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 Connector/NET:

  • MySqlCommand: Represents an 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 an SQL transaction to be made in a MySQL database.

This section contains basic information and examples for each of the above classes. For a more detailed reference guide please see Section 23.2.4, “Connector/NET Reference”.

23.2.3.1. Using MySqlCommand

Represents a SQL statement to execute against a MySQL database. This class cannot be inherited.

MySqlCommand features the following methods for executing commands at a MySQL database:

Item Description
ExecuteReader Executes commands that return rows.
ExecuteNonQuery Executes commands such as SQL INSERT, DELETE, and UPDATE statements.
ExecuteScalar Retrieves a single value (for example, an aggregate value) from a database.

You can reset the CommandText property and reuse the MySqlCommand object. However, you must close the MySqlDataReader before you can execute a new or previous command.

If a MySqlException is generated by the method executing a MySqlCommand, the MySqlConnection remains open. It is the responsibility of the programmer to close the connection.

Note

Prior versions of the provider used the '@' symbol to mark parameters in SQL. This is incompatible 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' on your connection string. If you do this, please be aware that an exception will not be throw if you fail to define a parameter that you intended to use in your SQL.

Examples

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.

Visual Basic example:

  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

C# example:

  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();
}
23.2.3.1.1. Class MySqlCommand Constructor Form 1

Overload methods for MySqlCommand

Initializes a new instance of the MySqlCommand class.

Examples

The following example creates a MySqlCommand and sets some of its properties.

Note

This example shows how to use one of the overloaded versions of the MySqlCommand constructor. For other examples that might be available, see the individual overload topics.

Visual Basic example:

  Public Sub CreateMySqlCommand()
    Dim myConnection As New MySqlConnection _
        ("Persist Security Info=False;database=test;server=myServer")
    myConnection.Open()
    Dim myTrans As MySqlTransaction = myConnection.BeginTransaction()
    Dim mySelectQuery As String = "SELECT * FROM MyTable"
    Dim myCommand As New MySqlCommand(mySelectQuery, myConnection, myTrans)
    myCommand.CommandTimeout = 20
  End Sub

C# example:

  public void CreateMySqlCommand() 
  {
    MySqlConnection myConnection = new MySqlConnection("Persist Security Info=False;
      database=test;server=myServer");
    myConnection.Open();
    MySqlTransaction myTrans = myConnection.BeginTransaction();
    string mySelectQuery = "SELECT * FROM myTable";
    MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection,myTrans);
    myCommand.CommandTimeout = 20;
  }

C++ example:

  public:
  void CreateMySqlCommand()
  {
    MySqlConnection* myConnection = new MySqlConnection(S"Persist Security Info=False;
      database=test;server=myServer");
    myConnection->Open();
    MySqlTransaction* myTrans = myConnection->BeginTransaction();
    String* mySelectQuery = S"SELECT * FROM myTable";
    MySqlCommand* myCommand = new MySqlCommand(mySelectQuery, myConnection, myTrans);
    myCommand->CommandTimeout = 20;
  };

Initializes a new instance of the MySqlCommand class.

The base constructor initializes all fields to their default values. The following table shows initial property values for an instance of MySqlCommand.

Properties Initial Value
CommandText empty string ("")
CommandTimeout 0
CommandType CommandType.Text
Connection Null

You can change the value for any of these properties through a separate call to the property.

Examples

The following example creates a MySqlCommand and sets some of its properties.

Visual Basic example:

Public Sub CreateMySqlCommand()
    Dim myCommand As New MySqlCommand()
    myCommand.CommandType = CommandType.Text
End Sub

C# example:

public void CreateMySqlCommand() 
{
   MySqlCommand myCommand = new MySqlCommand();
   myCommand.CommandType = CommandType.Text;
}
23.2.3.1.2. Class MySqlCommand Constructor Form 2

Initializes a new instance of the MySqlCommand class with the text of the query.

Parameters: The text of the query.

When an instance of MySqlCommand is created, the following read/write properties are set to initial values.

Properties Initial Value
CommandText cmdText
CommandTimeout 0
CommandType CommandType.Text
Connection Null

You can change the value for any of these properties through a separate call to the property.

Examples

The following example creates a MySqlCommand and sets some of its properties.

Visual Basic example:

Public Sub CreateMySqlCommand()
  Dim sql as String = "SELECT * FROM mytable"
    Dim myCommand As New MySqlCommand(sql)
    myCommand.CommandType = CommandType.Text
End Sub

C# example:

public void CreateMySqlCommand() 
{
  string sql = "SELECT * FROM mytable";
  MySqlCommand myCommand = new MySqlCommand(sql);
  myCommand.CommandType = CommandType.Text;
}
23.2.3.1.3. Class MySqlCommand Constructor Form 3

Initializes a new instance of the MySqlCommand class with the text of the query and a MySqlConnection.

Parameters: The text of the query.

Parameters: A MySqlConnection that represents the connection to an instance of SQL Server.

When an instance of MySqlCommand is created, the following read/write properties are set to initial values.

Properties Initial Value
CommandText cmdText
CommandTimeout 0
CommandType CommandType.Text
Connection connection

You can change the value for any of these properties through a separate call to the property.

Examples

The following example creates a MySqlCommand and sets some of its properties.

Visual Basic example:

Public Sub CreateMySqlCommand()
  Dim conn as new MySqlConnection("server=myServer")
  Dim sql as String = "SELECT * FROM mytable"
    Dim myCommand As New MySqlCommand(sql, conn)
    myCommand.CommandType = CommandType.Text
End Sub

C# example:

public void CreateMySqlCommand() 
{
  MySqlConnection conn = new MySqlConnection("server=myserver")
  string sql = "SELECT * FROM mytable";
  MySqlCommand myCommand = new MySqlCommand(sql, conn);
  myCommand.CommandType = CommandType.Text;
}
23.2.3.1.4. Class MySqlCommand Constructor Form 4

Initializes a new instance of the MySqlCommand class with the text of the query, a MySqlConnection, and the MySqlTransaction.

Parameters: The text of the query.

Parameters: A MySqlConnection that represents the connection to an instance of SQL Server.

Parameters: The MySqlTransaction in which the MySqlCommand executes.

When an instance of MySqlCommand is created, the following read/write properties are set to initial values.

Properties Initial Value
CommandText cmdText
CommandTimeout 0
CommandType CommandType.Text
Connection connection

You can change the value for any of these properties through a separate call to the property.

Examples

The following example creates a MySqlCommand and sets some of its properties.

Visual Basic example:

Public Sub CreateMySqlCommand()
  Dim conn as new MySqlConnection("server=myServer")
  conn.Open();
  Dim txn as MySqlTransaction = conn.BeginTransaction()
  Dim sql as String = "SELECT * FROM mytable"
    Dim myCommand As New MySqlCommand(sql, conn, txn)
    myCommand.CommandType = CommandType.Text
End Sub

C# example:

public void CreateMySqlCommand() 
{
  MySqlConnection conn = new MySqlConnection("server=myserver")
  conn.Open();
  MySqlTransaction txn = conn.BeginTransaction();
  string sql = "SELECT * FROM mytable";
  MySqlCommand myCommand = new MySqlCommand(sql, conn, txn);
  myCommand.CommandType = CommandType.Text;
}
23.2.3.1.5. ExecuteNonQuery

Executes a SQL statement against the connection and returns the number of rows affected.

Returns: Number of rows affected

You can use ExecuteNonQuery to perform any type of database operation, however any resultsets returned will not be available. Any output parameters used in calling a stored procedure will be populated with data and can be retrieved after execution is complete. For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

Examples

The following example creates a MySqlCommand and then executes it using ExecuteNonQuery. The example is passed a string that is a SQL statement (such as UPDATE, INSERT, or DELETE) and a string to use to connect to the data source.

Visual Basic example:

    Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As MySqlConnection)
      Dim myCommand As New MySqlCommand(myExecuteQuery, myConnection)
      myCommand.Connection.Open()
      myCommand.ExecuteNonQuery()
      myConnection.Close()
    End Sub

C# example:

    public void CreateMySqlCommand(string myExecuteQuery, MySqlConnection myConnection) 
    {
      MySqlCommand myCommand = new MySqlCommand(myExecuteQuery, myConnection);
      myCommand.Connection.Open();
      myCommand.ExecuteNonQuery();
      myConnection.Close();
    }
23.2.3.1.6. ExecuteReader1

Sends the CommandText to the MySqlConnectionConnection, and builds a MySqlDataReader using one of the CommandBehavior values.

Parameters: One of the CommandBehavior values.

When the CommandType property is set to StoredProcedure, the CommandText property should be set to the name of the stored procedure. The command executes this stored procedure when you call ExecuteReader.

The MySqlDataReader supports a special mode that enables large binary values to be read efficiently. For more information, see the SequentialAccess setting for CommandBehavior.

While the MySqlDataReader is in use, the associated MySqlConnection is busy serving the MySqlDataReader. While in this state, no other operations can be performed on the MySqlConnection other than closing it. This is the case until the MySqlDataReader.Close method of the MySqlDataReader is called. If the MySqlDataReader is created with CommandBehavior set to CloseConnection, closing the MySqlDataReader closes the connection automatically.

Note

When calling ExecuteReader with the SingleRow behavior, you should be aware that using a limit clause in your SQL will cause all rows (up to the limit given) to be retrieved by the client. The MySqlDataReader.Read method will still return false after the first row but pulling all rows of data into the client will have a performance impact. If the limit clause is not necessary, it should be avoided.

Returns: A MySqlDataReader object.

23.2.3.1.7. Using ExecuteReader

Sends the CommandText to the MySqlConnectionConnection and builds a MySqlDataReader.

Returns: A MySqlDataReader object.

When the CommandType property is set to StoredProcedure, the CommandText property should be set to the name of the stored procedure. The command executes this stored procedure when you call ExecuteReader.

While the MySqlDataReader is in use, the associated MySqlConnection is busy serving the MySqlDataReader. While in this state, no other operations can be performed on the MySqlConnection other than closing it. This is the case until the MySqlDataReader.Close method of the MySqlDataReader is called.

Examples

The following example creates a MySqlCommand, then executes it by passing a string that is a SQL SELECT statement, and a string to use to connect to the data source.

Visual Basic example:

Public Sub CreateMySqlDataReader(mySelectQuery As String, myConnection As MySqlConnection)
    Dim myCommand As New MySqlCommand(mySelectQuery, myConnection)
    myConnection.Open()
    Dim myReader As MySqlDataReader
    myReader = myCommand.ExecuteReader()
    Try
    While myReader.Read()
        Console.WriteLine(myReader.GetString(0))
    End While
Finally
    myReader.Close
    myConnection.Close
    End Try
End Sub

C# example:

public void CreateMySqlDataReader(string mySelectQuery, MySqlConnection myConnection) 
 {
    MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
    myConnection.Open();
    MySqlDataReader myReader;
    myReader = myCommand.ExecuteReader();
    try
    {
      while(myReader.Read()) 
      {
        Console.WriteLine(myReader.GetString(0));
      }
    }
    finally
    {
      myReader.Close();
      myConnection.Close();
    }
 }
23.2.3.1.8. Using Prepare

Creates a prepared version of the command on an instance of MySQL Server.

Prepared statements are only supported on MySQL version 4.1 and higher. Calling prepare while connected to earlier versions of MySQL will succeed but will execute the statement in the same way as unprepared.

Examples

The following example demonstrates the use of the Prepare method.

Visual Basic example:

  public sub PrepareExample()
    Dim cmd as New MySqlCommand("INSERT INTO mytable VALUES (?val)", myConnection)
    cmd.Parameters.Add( "?val", 10 )
    cmd.Prepare()
    cmd.ExecuteNonQuery()
    
    cmd.Parameters(0).Value = 20
    cmd.ExecuteNonQuery()
  end sub

C# example:

  private void PrepareExample()
  {
    MySqlCommand cmd = new MySqlCommand("INSERT INTO mytable VALUES (?val)", myConnection);
    cmd.Parameters.Add( "?val", 10 );
    cmd.Prepare();
    cmd.ExecuteNonQuery();
    
    cmd.Parameters[0].Value = 20;
    cmd.ExecuteNonQuery();
  }
23.2.3.1.9. 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.

Returns: The first column of the first row in the result set, or a null reference if the result set is empty

Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations necessary to generate the single value using the data returned by a MySqlDataReader

A typical ExecuteScalar query can be formatted as in the following C# example:

C# example:

cmd.CommandText = "select count(*) from region";
Int32 count = (int32) cmd.ExecuteScalar();

Examples

The following example creates a MySqlCommand and then executes it using ExecuteScalar. The example is passed a string that is a SQL statement that returns an aggregate result, and a string to use to connect to the data source.

Visual Basic example:

Public Sub CreateMySqlCommand(myScalarQuery As String, myConnection As MySqlConnection)
    Dim myCommand As New MySqlCommand(myScalarQuery, myConnection)
    myCommand.Connection.Open()
    myCommand.ExecuteScalar()
    myConnection.Close()
End Sub

C# example:

public void CreateMySqlCommand(string myScalarQuery, MySqlConnection myConnection) 
 {
    MySqlCommand myCommand = new MySqlCommand(myScalarQuery, myConnection);
    myCommand.Connection.Open();
    myCommand.ExecuteScalar();
    myConnection.Close();
 }

C++ example:

public:
    void CreateMySqlCommand(String* myScalarQuery, MySqlConnection* myConnection)
    {
        MySqlCommand* myCommand = new MySqlCommand(myScalarQuery, myConnection);
        myCommand->Connection->Open();
        myCommand->ExecuteScalar();
        myConnection->Close();
    }
23.2.3.1.10. CommandText

Gets or sets the SQL statement to execute at the data source.

Value: The SQL statement or stored procedure to execute. The default is an empty string.

When the CommandType property is set to StoredProcedure, the CommandText property should be set to the name of the stored procedure. The user may be required to use escape character syntax if the stored procedure name contains any special characters. The command executes this stored procedure when you call one of the Execute methods.

Examples

The following example creates a MySqlCommand and sets some of its properties.

Visual Basic example:

Public Sub CreateMySqlCommand()
    Dim myCommand As New MySqlCommand()
    myCommand.CommandText = "SELECT * FROM Mytable ORDER BY id"
    myCommand.CommandType = CommandType.Text
End Sub

C# example:

public void CreateMySqlCommand() 
 {
    MySqlCommand myCommand = new MySqlCommand();
    myCommand.CommandText = "SELECT * FROM mytable ORDER BY id";
    myCommand.CommandType = CommandType.Text;
 }
23.2.3.1.11. CommandTimeout

Gets or sets the wait time before terminating the attempt to execute a command and generating an error.

Value: The time (in seconds) to wait for the command to execute. The default is 0 seconds.

MySQL currently does not support any method of canceling a pending or executing operation. All commands issued against a MySQL server will execute until completion or until an exception occurs.

MySQL Enterprise.  MySQL Enterprise subscribers will find more information about CommandTimeout in the Knowledge Base article, Why CommandTimeout is not Supported. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information see http://www.mysql.com/products/enterprise/advisors.html.

23.2.3.1.12. CommandType

Gets or sets a value indicating how the CommandText property is to be interpreted.

Value: One of the System.Data.CommandType values. The default is Text.

When you set the CommandType property to StoredProcedure, you should set the CommandText property to the name of the stored procedure. The command executes this stored procedure when you call one of the Execute methods.

Examples

The following example creates a MySqlCommand and sets some of its properties.

Visual Basic example:

Public Sub CreateMySqlCommand()
    Dim myCommand As New MySqlCommand()
    myCommand.CommandType = CommandType.Text
End Sub

C# example:

public void CreateMySqlCommand() 
{
   MySqlCommand myCommand = new MySqlCommand();
   myCommand.CommandType = CommandType.Text;
}
23.2.3.1.13. Connection

Gets or sets the MySqlConnection used by this instance of the MySqlCommand.

Value: The connection to a data source. The default value is a null reference (Nothing in Visual Basic).

If you set Connection while a transaction is in progress and the Transaction property is not null, an InvalidOperationException is generated. If the Transaction property is not null and the transaction has already been committed or rolled back, Transaction is set to null.

Examples

The following example creates a MySqlCommand and sets some of its properties.

Visual Basic example:

Public Sub CreateMySqlCommand()
    Dim mySelectQuery As String = "SELECT * FROM mytable ORDER BY id"
    Dim myConnectString As String = "Persist Security Info=False;database=test;server=myServer"
    Dim myCommand As New MySqlCommand(mySelectQuery)
    myCommand.Connection = New MySqlConnection(myConnectString)
    myCommand.CommandType = CommandType.Text
End Sub

C# example:

public void CreateMySqlCommand() 
 {
    string mySelectQuery = "SELECT * FROM mytable ORDER BY id";
    string myConnectString = "Persist Security Info=False;database=test;server=myServer";
    MySqlCommand myCommand = new MySqlCommand(mySelectQuery);
    myCommand.Connection = new MySqlConnection(myConnectString);
    myCommand.CommandType = CommandType.Text;
 }
23.2.3.1.14. IsPrepared

Returns true if the statement is prepared.

23.2.3.1.15. Parameters

Get the MySqlParameterCollection

Value: The parameters of the SQL statement or stored procedure. The default is an empty collection.

Connector/Net does not support unnamed parameters. Every parameter added to the collection must have an associated name.

Examples

The following example creates a MySqlCommand and displays its parameters. To accomplish this, the method is passed a MySqlConnection, a query string that is a SQL SELECT statement, and an array of MySqlParameter objects.

Visual Basic example:

Public Sub CreateMySqlCommand(myConnection As MySqlConnection, _
mySelectQuery As String, myParamArray() As MySqlParameter)
    Dim myCommand As New MySqlCommand(mySelectQuery, myConnection)
    myCommand.CommandText = "SELECT id, name FROM mytable WHERE age=?age"
    myCommand.UpdatedRowSource = UpdateRowSource.Both
    myCommand.Parameters.Add(myParamArray)
    Dim j As Integer
    For j = 0 To myCommand.Parameters.Count - 1
       myCommand.Parameters.Add(myParamArray(j))
    Next j
    Dim myMessage As String = ""
    Dim i As Integer
    For i = 0 To myCommand.Parameters.Count - 1
        myMessage += myCommand.Parameters(i).ToString() & ControlChars.Cr
    Next i
    Console.WriteLine(myMessage)
End Sub

C# example:

public void CreateMySqlCommand(MySqlConnection myConnection, string mySelectQuery, 
  MySqlParameter[] myParamArray) 
{
   MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
   myCommand.CommandText = "SELECT id, name FROM mytable WHERE age=?age";    
   myCommand.Parameters.Add(myParamArray);
   for (int j=0; j<myParamArray.Length; j++)
   {
      myCommand.Parameters.Add(myParamArray[j]) ;
   }
   string myMessage = "";
   for (int i = 0; i < myCommand.Parameters.Count; i++) 
   {
      myMessage += myCommand.Parameters[i].ToString() + "\n";
   }
   MessageBox.Show(myMessage);
}
23.2.3.1.16. Transaction

Gets or sets the MySqlTransaction within which the MySqlCommand executes.

Value: The MySqlTransaction. The default value is a null reference (Nothing in Visual Basic).

You cannot set the Transaction property if it is already set to a specific value, and the command is in the process of executing. If you set the transaction property to a MySqlTransaction object that is not connected to the same MySqlConnection as the MySqlCommand object, an exception will be thrown the next time you attempt to execute a statement.

23.2.3.1.17. UpdatedRowSource

Gets or sets how command results are applied to the DataRow when used by the System.Data.Common.DbDataAdapter.Update method of the System.Data.Common.DbDataAdapter.

Value: One of the UpdateRowSource values.

The default System.Data.UpdateRowSource value is Both unless the command is automatically generated (as in the case of the MySqlCommandBuilder), in which case the default is None.

23.2.3.2. Using MySqlCommandBuilder

Automatically generates single-table commands used to reconcile changes made to a DataSet with the associated MySQL database. This class cannot be inherited.

The MySqlDataAdapter does not automatically generate the SQL statements required to reconcile changes made to a System.Data.DataSetDataSet 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 MySqlDataAdapter.SelectCommandSelectCommand property of the MySqlDataAdapter. Then, any additional SQL statements that you do not set are generated by the MySqlCommandBuilder.

The MySqlCommandBuilder registers itself as a listener for MySqlDataAdapter.OnRowUpdatingRowUpdating events whenever you set the DataAdapter property. You can only associate one MySqlDataAdapter or MySqlCommandBuilder object with each other at one time.

To generate INSERT, UPDATE, or DELETE statements, the MySqlCommandBuilder uses the SelectCommand property to retrieve a required set of metadata automatically. If you change the SelectCommand 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 MySqlCommand.ConnectionConnection, MySqlCommand.CommandTimeoutCommandTimeout, and MySqlCommand.TransactionTransaction properties referenced by the SelectCommand. The user should call RefreshSchema if any of these properties are modified, or if the SelectCommand itself is replaced. Otherwise the MySqlDataAdapter.InsertCommandInsertCommand, MySqlDataAdapter.UpdateCommandUpdateCommand, and MySqlDataAdapter.DeleteCommandDeleteCommand properties retain their previous values.

If you call Dispose, the MySqlCommandBuilder is disassociated from the MySqlDataAdapter, and the generated commands are no longer used.

Note

Caution must be used when using MySqlCommandBuilder on MySql 4.0 systems. With MySQL 4.0, database/schema information is not provided to the connector for a query. This means that a query that pulls columns from two identically named tables in two or more different databases will not cause an exception to be thrown but will not work correctly. Even more dangerous is the situation where your select statement references database X but is executed in database Y and both databases have tables with similar layouts. This situation can cause unwanted changes or deletes. This note does not apply to MySQL versions 4.1 and later.

Examples

The following example uses the MySqlCommand, along MySqlDataAdapter and MySqlConnection, to select rows from a data source. The example is passed an initialized System.Data.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.

Visual Basic example:

  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

C# example:

  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;
  }
23.2.3.2.1. Class MySqlCommandBuilder Constructor

Initializes a new instance of the MySqlCommandBuilder class.

23.2.3.2.2. Class MySqlCommandBuilder Constructor Form 1

Initializes a new instance of the MySqlCommandBuilder class and sets the last one wins property.

Parameters: False to generate change protection code. True otherwise.

The lastOneWins parameter indicates whether SQL code should be included with the generated DELETE and UPDATE commands that checks the underlying data for changes. If lastOneWins is true then this code is not included and data records could be overwritten in a multi-user or multi-threaded environments. Setting lastOneWins to false will include this check which will cause a concurrency exception to be thrown if the underlying data record has changed without our knowledge.

23.2.3.2.3. Class MySqlCommandBuilder Constructor Form 2

Initializes a new instance of the MySqlCommandBuilder class with the associated MySqlDataAdapter object.

Parameters: The MySqlDataAdapter to use.

The MySqlCommandBuilder registers itself as a listener for MySqlDataAdapter.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.

23.2.3.2.4. Class MySqlCommandBuilder Constructor Form 3

Initializes a new instance of the MySqlCommandBuilder class with the associated MySqlDataAdapter object.

Parameters: The MySqlDataAdapter to use.

Parameters: False to generate change protection code. True otherwise.

The MySqlCommandBuilder registers itself as a listener for MySqlDataAdapter.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.

The lastOneWins parameter indicates whether SQL code should be included with the generated DELETE and UPDATE commands that checks the underlying data for changes. If lastOneWins is true then this code is not included and data records could be overwritten in a multi-user or multi-threaded environments. Setting lastOneWins to false will include this check which will cause a concurrency exception to be thrown if the underlying data record has changed without our knowledge.

23.2.3.2.5. DataAdapter

Gets or sets a MySqlDataAdapter object for which SQL statements are automatically generated.

Value: A MySqlDataAdapter object.

The MySqlCommandBuilder registers itself as a listener for MySqlDataAdapter.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.

23.2.3.2.6. QuotePrefix

Gets or sets the beginning character or characters to use when specifying MySQL database objects (for example, tables or columns) whose names contain characters such as spaces or reserved tokens.

Value: The beginning character or characters to use. The default value is `.

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.

23.2.3.2.7. QuoteSuffix

Gets or sets the beginning character or characters to use when specifying MySQL database objects (for example, tables or columns) whose names contain characters such as spaces or reserved tokens.

Value: The beginning character or characters to use. The default value is `.

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.

23.2.3.2.8. DeriveParameters

23.2.3.2.9. GetDeleteCommand

Gets the automatically generated MySqlCommand object required to perform deletions on the database.

Returns: The MySqlCommand object generated to handle delete operations.

An application can use the GetDeleteCommand method for informational or troubleshooting purposes because it returns the MySqlCommand object to be executed.

You can also use GetDeleteCommand as the basis of a modified command. For example, you might call GetDeleteCommand and modify the MySqlCommand.CommandTimeout value, and then explicitly set that on the MySqlDataAdapter.

After the SQL statement is first generated, the application must explicitly call RefreshSchema if it changes the statement in any way. Otherwise, the GetDeleteCommand will be still be using information from the previous statement, which might not be correct. The SQL statements are first generated either when the application calls System.Data.Common.DataAdapter.Update or GetDeleteCommand.

23.2.3.2.10. GetInsertCommand

Gets the automatically generated MySqlCommand object required to perform insertions on the database.

Returns: The MySqlCommand object generated to handle insert operations.

An application can use the GetInsertCommand method for informational or troubleshooting purposes because it returns the MySqlCommand object to be executed.

You can also use the GetInsertCommand as the basis of a modified command. For example, you might call GetInsertCommand and modify the MySqlCommand.CommandTimeout value, and then explicitly set that on the MySqlDataAdapter.

After the SQL statement is first generated, the application must explicitly call RefreshSchema if it changes the statement in any way. Otherwise, the GetInsertCommand will be still be using information from the previous statement, which might not be correct. The SQL statements are first generated either when the application calls System.Data.Common.DataAdapter.Update or GetInsertCommand.

23.2.3.2.11. GetUpdateCommand

Gets the automatically generated MySqlCommand object required to perform updates on the database.

Returns: The MySqlCommand object generated to handle update operations.

An application can use the GetUpdateCommand method for informational or troubleshooting purposes because it returns the MySqlCommand object to be executed.

You can also use GetUpdateCommand as the basis of a modified command. For example, you might call GetUpdateCommand and modify the MySqlCommand.CommandTimeout value, and then explicitly set that on the MySqlDataAdapter.

After the SQL statement is first generated, the application must explicitly call RefreshSchema if it changes the statement in any way. Otherwise, the GetUpdateCommand will be still be using information from the previous statement, which might not be correct. The SQL statements are first generated either when the application calls System.Data.Common.DataAdapter.Update or GetUpdateCommand.

23.2.3.2.12. RefreshSchema

Refreshes the database schema information used to generate INSERT, UPDATE, or DELETE statements.

An application should call RefreshSchema whenever the SELECT statement associated with the MySqlCommandBuilder changes.

An application should call RefreshSchema whenever the MySqlDataAdapter.SelectCommand value of the MySqlDataAdapter changes.

MySQL Enterprise.  MySQL Enterprise subscribers will find more information on this topic in the Knowledge Base article, Understanding MySqlCommandBuilder and the LastOneWins Setting . For information about subscribing to MySQL Enterprise see http://www.mysql.com/products/enterprise/advisors.html.

23.2.3.3. Using MySqlConnection

Represents an open connection to a MySQL Server database. This class cannot be inherited.

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 values, see the MySqlConnection constructor.

If the MySqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling MySqlConnection.Close or MySqlConnection.Dispose.

Examples

The following example creates a MySqlCommand and a MySqlConnection. The MySqlConnection is opened and set as the MySqlCommand.Connection for the MySqlCommand. The example then calls MySqlCommand.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.

Visual Basic example:

  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

C# example:

  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();
  }
23.2.3.3.1. Class MySqlConnection Constructor (Default)

Initializes a new instance of the MySqlConnection class.

When a new instance of MySqlConnection is created, the read/write properties are set to the following initial values unless they are specifically set using their associated keywords in the ConnectionString property.

Properties Initial Value
ConnectionString empty string ("")
ConnectionTimeout 15
Database empty string ("")
DataSource empty string ("")
ServerVersion empty string ("")

You can change the value for these properties only by using the ConnectionString property.

Examples

Overload methods for MySqlConnection

Initializes a new instance of the MySqlConnection class.

23.2.3.3.2. Class MySqlConnection Constructor Form 1

Initializes a new instance of the MySqlConnection class when given a string containing the connection string.

When a new instance of MySqlConnection is created, the read/write properties are set to the following initial values unless they are specifically set using their associated keywords in the ConnectionString property.

Properties Initial Value
ConnectionString empty string ("")
ConnectionTimeout 15
Database empty string ("")
DataSource empty string ("")
ServerVersion empty string ("")

You can change the value for these properties only by using the ConnectionString property.

Examples

Parameters: The connection properties used to open the MySQL database.

23.2.3.3.3. Open

Opens a database connection with the property settings specified by the ConnectionString.

Exception: Cannot open a connection without specifying a data source or server.

Exception: A connection-level error occurred while opening the connection.

The MySqlConnection draws an open connection from the connection pool if one is available. Otherwise, it establishes a new connection to an instance of MySQL.

Examples

The following example creates a MySqlConnection, opens it, displays some of its properties, then closes the connection.

Visual Basic example:

Public Sub CreateMySqlConnection(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    MessageBox.Show("ServerVersion: " + myConnection.ServerVersion _
    + ControlChars.Cr + "State: " + myConnection.State.ToString())
    myConnection.Close()
End Sub

C# example:

public void CreateMySqlConnection(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MessageBox.Show("ServerVersion: " + myConnection.ServerVersion + 
          "\nState: " + myConnection.State.ToString());
  myConnection.Close();
}
23.2.3.3.4. Database

Gets the name of the current database or the database to be used after a connection is opened.

Returns: The name of the current database or the name of the database to be used after a connection is opened. The default value is an empty string.

The Database property does not update dynamically. If you change the current database using a SQL statement, then this property may reflect the wrong value. If you change the current database using the ChangeDatabase method, this property is updated to reflect the new database.

Examples

The following example creates a MySqlConnection and displays some of its read-only properties.

Visual Basic example:

Public Sub CreateMySqlConnection()
  Dim myConnString As String = _
    "Persist Security Info=False;database=test;server=localhost;user id=joeuser;pwd=pass"
  Dim myConnection As New MySqlConnection( myConnString )
  myConnection.Open()
  MessageBox.Show( "Server Version: " + myConnection.ServerVersion _
    + ControlChars.NewLine + "Database: " + myConnection.Database )
  myConnection.ChangeDatabase( "test2" )
  MessageBox.Show( "ServerVersion: " + myConnection.ServerVersion _ 
    + ControlChars.NewLine + "Database: " + myConnection.Database )
  myConnection.Close()
End Sub

C# example:

public void CreateMySqlConnection()
{
  string myConnString = 
    "Persist Security Info=False;database=test;server=localhost;user id=joeuser;pwd=pass";
  MySqlConnection myConnection = new MySqlConnection( myConnString );
  myConnection.Open();
  MessageBox.Show( "Server Version: " + myConnection.ServerVersion 
    + "\nDatabase: " + myConnection.Database );
  myConnection.ChangeDatabase( "test2" );
  MessageBox.Show( "ServerVersion: " + myConnection.ServerVersion  
    + "\nDatabase: " + myConnection.Database );
  myConnection.Close();
}
23.2.3.3.5. State

Gets the current state of the connection.

Returns: A bitwise combination of the System.Data.ConnectionState values. The default is Closed.

The allowed state changes are:

  • From Closed to Open, using the Open method of the connection object.

  • From Open to Closed, using either the Close method or the Dispose method of the connection object.

Examples

The following example creates a MySqlConnection, opens it, displays some of its properties, then closes the connection.

Visual Basic example:

Public Sub CreateMySqlConnection(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    MessageBox.Show("ServerVersion: " + myConnection.ServerVersion _
    + ControlChars.Cr + "State: " + myConnection.State.ToString())
    myConnection.Close()
End Sub

C# example:

public void CreateMySqlConnection(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MessageBox.Show("ServerVersion: " + myConnection.ServerVersion + 
          "\nState: " + myConnection.State.ToString());
  myConnection.Close();
}
23.2.3.3.6. ServerVersion

Gets a string containing the version of the MySQL server to which the client is connected.

Returns: The version of the instance of MySQL.

Exception: The connection is closed.

Examples

The following example creates a MySqlConnection, opens it, displays some of its properties, then closes the connection.

Visual Basic example:

Public Sub CreateMySqlConnection(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    MessageBox.Show("ServerVersion: " + myConnection.ServerVersion _
    + ControlChars.Cr + "State: " + myConnection.State.ToString())
    myConnection.Close()
End Sub

C# example:

public void CreateMySqlConnection(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MessageBox.Show("ServerVersion: " + myConnection.ServerVersion + 
          "\nState: " + myConnection.State.ToString());
  myConnection.Close();
}
23.2.3.3.7. Close

Closes the connection to the database. This is the preferred method of closing any open connection.

The Close method rolls back any pending transactions. It then releases the connection to the connection pool, or closes the connection if connection pooling is disabled.

An application can call Close more than one time. No exception is generated.

Examples

The following example creates a MySqlConnection, opens it, displays some of its properties, then closes the connection.

Visual Basic example:

Public Sub CreateMySqlConnection(myConnString As String)
    Dim myConnection As New MySqlConnection(myConnString)
    myConnection.Open()
    MessageBox.Show("ServerVersion: " + myConnection.ServerVersion _
    + ControlChars.Cr + "State: " + myConnection.State.ToString())
    myConnection.Close()
End Sub

C# example:

public void CreateMySqlConnection(string myConnString) 
{
  MySqlConnection myConnection = new MySqlConnection(myConnString);
  myConnection.Open();
  MessageBox.Show("ServerVersion: " + myConnection.ServerVersion + 
          "\nState: " + myConnection.State.ToString());
  myConnection.Close();
}
23.2.3.3.8. CreateCommand

Creates and returns a MySqlCommand object associated with the MySqlConnection.

Returns: A MySqlCommand object.

23.2.3.3.9. BeginTransaction

Begins a database transaction.

Returns: An object representing the new transaction.

Exception: Parallel transactions are not supported.

This command is equivalent to the MySQL BEGIN TRANSACTION command.

You must explicitly commit or roll back the transaction using the MySqlTransaction.Commit or MySqlTransaction.Rollback method.

Note

If you do not specify an isolation level, the default isolation level is used. To specify an isolation level with the BeginTransaction method, use the overload that takes the iso parameter.

Examples

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

Visual Basic example:

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 Test (id, desc) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into Test (id, desc) 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

C# example:

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 Test (id, desc) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "insert into Test (id, desc) VALUES (101, 'Description')";
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      Console.WriteLine("Both records are written to database.");
    }
    catch(Exception e)
    {
      try
      {
        myTrans.Rollback();
      }
      catch (SqlException 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();
    }
}
23.2.3.3.10. BeginTransaction1

Begins a database transaction with the specified isolation level.

Parameters: The isolation level under which the transaction should run.

Returns: An object representing the new transaction.

Exception: Parallel exceptions are not supported.

This command is equivalent to the MySQL BEGIN TRANSACTION command.

You must explicitly commit or roll back the transaction using the MySqlTransaction.Commit or MySqlTransaction.Rollback method.

Note

If you do not specify an isolation level, the default isolation level is used. To specify an isolation level with the BeginTransaction method, use the overload that takes the iso parameter.

Examples

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

Visual Basic example:

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 Test (id, desc) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into Test (id, desc) 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

C# example:

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 Test (id, desc) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "insert into Test (id, desc) VALUES (101, 'Description')";
      myCommand.ExecuteNonQuery();
      myTrans.Commit();
      Console.WriteLine("Both records are written to database.");
    }
    catch(Exception e)
    {
      try
      {
        myTrans.Rollback();
      }
      catch (SqlException 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();
    }
}
23.2.3.3.11. ChangeDatabase

Changes the current database for an open MySqlConnection.

Parameters: The name of the database to use.

The value supplied in the database parameter must be a valid database name. The database parameter cannot contain a null value, an empty string, or a string with only blank characters.

When you are using connection pooling against MySQL, and you close the connection, it is returned to the connection pool. The next time the connection is retrieved from the pool, the reset connection request executes before the user performs any operations.

MySQL Enterprise.  MySQL Enterprise subscribers will find more information on this subject in the Knowledge Base article, Understanding and Using Connection Pooling. Access to the MySQL Knowledge Base collection of articles is one of the advantages of subscribing to MySQL Enterprise. To subscribe see http://www.mysql.com/products/enterprise/advisors.html.

Exception: The database name is not valid.

Exception: The connection is not open.

Exception: Cannot change the database.

Examples

The following example creates a MySqlConnection and displays some of its read-only properties.

Visual Basic example:

Public Sub CreateMySqlConnection()
  Dim myConnString As String = _
    "Persist Security Info=False;database=test;server=localhost;user id=joeuser;pwd=pass"
  Dim myConnection As New MySqlConnection( myConnString )
  myConnection.Open()
  MessageBox.Show( "Server Version: " + myConnection.ServerVersion _
    + ControlChars.NewLine + "Database: " + myConnection.Database )
  myConnection.ChangeDatabase( "test2" )
  MessageBox.Show( "ServerVersion: " + myConnection.ServerVersion _ 
    + ControlChars.NewLine + "Database: " + myConnection.Database )
  myConnection.Close()
End Sub

C# example:

public void CreateMySqlConnection()
{
  string myConnString = 
    "Persist Security Info=False;database=test;server=localhost;user id=joeuser;pwd=pass";
  MySqlConnection myConnection = new MySqlConnection( myConnString );
  myConnection.Open();
  MessageBox.Show( "Server Version: " + myConnection.ServerVersion 
    + "\nDatabase: " + myConnection.Database );
  myConnection.ChangeDatabase( "test2" );
  MessageBox.Show( "ServerVersion: " + myConnection.ServerVersion  
    + "\nDatabase: " + myConnection.Database );
  myConnection.Close();
}
23.2.3.3.12. StateChange

Occurs when the state of the connection changes.

The StateChange event fires whenever the State changes from closed to opened, or from opened to closed. StateChange fires immediately after the MySqlConnection transitions.

If an event handler throws an exception from within the StateChange event, the exception propagates to the caller of the Open or Close method.

The StateChange event is not raised unless you explicitly call Close or Dispose.

The event handler receives an argument of type System.Data.StateChangeEventArgs containing data related to this event. The following StateChangeEventArgs properties provide information specific to this event.

PropertyDescription
System.Data.StateChangeEventArgs.CurrentState Gets the new state of the connection. The connection object will be in the new state already when the event is fired.
System.Data.StateChangeEventArgs.OriginalState Gets the original state of the connection.
23.2.3.3.13. InfoMessage

Occurs when MySQL returns warnings as a result of executing a command or query.

23.2.3.3.14. ConnectionTimeout

Gets the time to wait while trying to establish a connection before terminating the attempt and generating an error.

Exception: The value set is less than 0.

A value of 0 indicates no limit, and should be avoided in a MySqlConnection.ConnectionString because an attempt to connect will wait indefinitely.

Examples

The following example creates a MySqlConnection and sets some of its properties in the connection string.

Visual Basic example:

Public Sub CreateSqlConnection()
  Dim myConnection As New MySqlConnection()
  myConnection.ConnectionString = "Persist Security Info=False;Username=user;Password=pass;database=test1;server=localhost;Connect Timeout=30"
  myConnection.Open()
End Sub

C# example:

public void CreateSqlConnection() 
{
  MySqlConnection myConnection = new MySqlConnection();
  myConnection.ConnectionString = "Persist Security Info=False;Username=user;»
      Password=pass;database=test1;server=localhost;Connect Timeout=30";
  myConnection.Open();
}
23.2.3.3.15. ConnectionString

Gets or sets the string used to connect to a MySQL Server database.

The ConnectionString returned may not be exactly like what was originally set but will be indentical in terms of keyword/value pairs. Security information will not be included unless the Persist Security Info value is set to true.

You can use the ConnectionString property to connect to a database. The following example illustrates a typical connection string.

"Persist Security Info=False;database=MyDB;»
    server=MySqlServer;user id=myUser;Password=myPass"

The ConnectionString property can be set only when the connection is closed. Many of the connection string values have corresponding read-only properties. When the connection string is set, all of these properties are updated, except when an error is detected. In this case, none of the properties are updated. MySqlConnection properties return only those settings contained in the ConnectionString.

To connect to a local machine, specify "localhost" for the server. If you do not specify a server, localhost is assumed.

Resetting the ConnectionString on a closed connection resets all connection string values (and related properties) including the password. For example, if you set a connection string that includes "Database= MyDb", and then reset the connection string to "Data Source=myserver;User Id=myUser;Password=myPass", the MySqlConnection.Database property is no longer set to MyDb.

The connection string is parsed immediately after being set. If errors in syntax are found when parsing, a runtime exception, such as ArgumentException, is generated. Other errors can be found only when an attempt is made to open the connection.

The basic format of a connection string consists of a series of keyword/value pairs separated by semicolons. The equal sign (=) connects each keyword and its value. Additional notes on setting values for options:

  • To include values that contain a semicolon, single-quote character, or double-quote character, the value must be enclosed in double quotes. If the value contains both a semicolon and a double-quote character, the value can be enclosed in single quotes. The single quote is also useful if the value begins with a double-quote character. Conversely, the double quote can be used if the value begins with a single quote. If the value contains both single-quote and double-quote characters, the quote character used to enclose the value must be doubled each time it occurs within the value.

  • To include preceding or trailing spaces in the string value, the value must be enclosed in either single quotes or double quotes. Any leading or trailing spaces around integer, Boolean, or enumerated values are ignored, even if enclosed in quotes. However, spaces within a string literal keyword or value are preserved. Using .NET Framework version 1.1, single or double quotes may be used within a connection string without using delimiters (for example, Data Source= my'Server or Data Source= my"Server), unless a quote character is the first or last character in the value.

  • To include an equal sign (=) in a keyword or value, it must be preceded by another equal sign. For example, in the hypothetical connection string

    "key==word=value"

    the keyword is "key=word" and the value is "value".

  • If a specific keyword in a keyword= value pair occurs multiple times in a connection string, the last occurrence listed is used in the value set.

  • Keywords are not case sensitive.

The following table lists the valid names for keyword values within the ConnectionString.

NameDefaultDescription
Connect Timeout, Connection Timeout15The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
Host, Server, Data Source, DataSource, Address, Addr, Network AddresslocalhostThe name or network address of the instance of MySQL to which to connect. Multiple hosts can be specified separated by &. This can be useful where multiple MySQL servers are configured for replication and you are not concerned about the precise server you are connecting to. No attempt is made by the provider to synchronize writes to the database so care should be taken when using this option. In Unix environment with Mono, this can be a fully qualified path to MySQL socket filename. With this configuration, the Unix socket will be used instead of TCP/IP socket. Currently only a single socket name can be given so accessing MySQL in a replicated environment using Unix sockets is not currently supported.
Ignore PreparetrueWhen true, instructs the provider to ignore any calls to MySqlCommand.Prepare(). This option is provided to prevent issues with corruption of the statements when use with server side prepared statements. If you want to use server-side prepare statements, set this option to false. This option was added in Connector/NET 5.0.3 and Connector/NET 1.0.9.
Port3306The port MySQL is using to listen for connections. Specify -1 for this value to use a named pipe connection (Windows only). This value is ignored if Unix socket is used.
ProtocolsocketSpecifies the type of connection to make to the server.Values can be: socket or tcp for a socket connection pipe for a named pipe connection unix for a Unix socket connection memory to use MySQL shared memory
CharSet, Character Set Specifies the character set that should be used to encode all queries sent to the server. Resultsets are still returned in the character set of the data returned.
LoggingfalseWhen true, various pieces of information is output to any configured TraceListeners.
Allow BatchtrueWhen true, multiple SQL statements can be sent with one command execution. -Note- Starting with MySQL 4.1.1, batch statements should be separated by the server-defined seperator character. Commands sent to earlier versions of MySQL should be seperated with ';'.
EncryptfalseFor Connector/NET 5.0.3 and later, when true, SSL encryption is used for all data sent between the client and server if the server has a certificate installed. Recognized values are true, false, yes, and no. In versions before 5.0.3, this option had no effect.
Initial Catalog, DatabasemysqlThe name of the database to use intially
Password, pwd The password for the MySQL account being used.
Persist Security InfofalseWhen set to false or no (strongly recommended), security-sensitive information, such as the password, is not returned as part of the connection if the connection is open or has ever been in an open state. Resetting the connection string resets all connection string values including the password. Recognized values are true, false, yes, and no.
User Id, Username, Uid, User name The MySQL login account being used.
Shared Memory NameMYSQLThe name of the shared memory object to use for communication if the connection protocol is set to memory.
Allow Zero DatetimefalseTrue to have MySqlDataReader.GetValue() return a MySqlDateTime for date or datetime columns that have illegal values. False will cause a System.DateTime object to be returned for legal values and an exception will be thrown for illegal values.
Convert Zero DatetimefalseTrue to have MySqlDataReader.GetValue() and MySqlDataReader.GetDateTime() return DateTime.MinValue for date or datetime columns that have illegal values.
Old Syntax, OldSyntaxfalseAllows use of '@' symbol as a parameter marker. See MySqlCommand for more info. This is for compatibility only. All future code should be written to use the new '?' parameter marker.
Pipe Name, PipemysqlWhen set to the name of a named pipe, the MySqlConnection will attempt to connect to MySQL on that named pipe.This settings only applies to the Windows platform.
Procedure Cache25Sets the size of the stored procedure cache. By default, Connector/NET will store the metadata (input/output datatypes) about the last 25 stored procedures used. To disable the stored procedure cache, set the value to zero (0). This option was added in Connector/NET 5.0.2 and Connector/NET 1.0.9.
Use Procedure BodiestrueSetting this option to false indicates that the user connecting to the database does not have the SELECT privileges for the mysql.proc (stored procedures) table. When to set to false, Connector/NET will not rely on this information being available when the procedure is called. Because Connector/NET will be unable to determine this information, you should explicitly set the types of the all the parameters before the call and the parameters should be added to the command in the exact same order as they appear in the procedure definition. This option was added in Connector/NET 5.0.4 and Connector/NET 1.0.10.

The following table lists the valid names for connection pooling values within the ConnectionString. For more information about connection pooling, see Connection Pooling for the MySQL Data Provider.

NameDefaultDescription
Connection Lifetime0When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online. A value of zero (0) causes pooled connections to have the maximum connection timeout.
Max Pool Size100The maximum number of connections allowed in the pool.
Min Pool Size0The minimum number of connections allowed in the pool.
PoolingtrueWhen true, the MySqlConnection object is drawn from the appropriate pool, or if necessary, is created and added to the appropriate pool. Recognized values are true, false, yes, and no.
Reset Pooled Connections, ResetConnections, ResetPooledConnectionstrueSpecifies whether a ping and a reset should be sent to the server before a pooled connection is returned. Not resetting will yeild faster connection opens but also will not clear out session items such as temp tables.
Cache Server Configuration, CacheServerConfiguration, CacheServerConfigfalseSpecifies whether server variables should be updated when a pooled connection is returned. Turning this one will yeild faster opens but will also not catch any server changes made by other connections.

When setting keyword or connection pooling values that require a Boolean value, you can use 'yes' instead of 'true', and 'no' instead of 'false'.

Note The MySQL Data Provider uses the native socket protocol to communicate with MySQL. Therefore, it does not support the use of an ODBC data source name (DSN) when connecting to MySQL because it does not add an ODBC layer.

CAUTION In this release, the application should use caution when constructing a connection string based on user input (for example when retrieving user ID and password information from a dialog box, and appending it to the connection string). The application should ensure that a user cannot embed extra connection string parameters in these values (for example, entering a password as "validpassword;database=somedb" in an attempt to attach to a different database).

Examples

The following example creates a MySqlConnection and sets some of its properties

Visual Basic example:

  Public Sub CreateConnection()
    Dim myConnection As New MySqlConnection()
    myConnection.ConnectionString = "Persist Security Info=False;database=myDB;server=myHost;Connect Timeout=30;user id=myUser; pwd=myPass"
    myConnection.Open()
  End Sub 'CreateConnection

C# example:

  public void CreateConnection() 
  {
    MySqlConnection myConnection = new MySqlConnection();
    myConnection.ConnectionString = "Persist Security Info=False;database=myDB;server=myHost;Connect Timeout=30;user id=myUser; pwd=myPass";
    myConnection.Open();
  }

Examples

The following example creates a MySqlConnection in Unix environment with Mono installed. MySQL socket filename used in this example is "/var/lib/mysql/mysql.sock". The actual filename depends on your MySQL configuration.

Visual Basic example:

  Public Sub CreateConnection()
    Dim myConnection As New MySqlConnection()
    myConnection.ConnectionString = "database=myDB;server=/var/lib/mysql/mysql.sock;user id=myUser; pwd=myPass"
    myConnection.Open()
  End Sub 'CreateConnection

C# example:

  public void CreateConnection() 
  {
    MySqlConnection myConnection = new MySqlConnection();
    myConnection.ConnectionString = "database=myDB;server=/var/lib/mysql/mysql.sock;user id=myUser; pwd=myPass";
    myConnection.Open();
  }

23.2.3.4. Using MySqlDataAdapter

Represents a set of data commands and a database connection that are used to fill a dataset and update a MySQL database. This class cannot be inherited.

The MySQLDataAdapter, serves as a bridge between a System.Data.DataSet and MySQL for retrieving and saving data. The MySQLDataAdapter provides this bridge by mapping DbDataAdapter.Fill, which changes the data in the DataSet to match the data in the data source, and DbDataAdapter.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 System.Data.MissingSchemaAction property is set to System.Data.MissingSchemaAction.AddWithKey. You may also have the MySQLDataAdapter create the schema of the DataSet, including primary key information, before filling it with data using System.Data.Common.DbDataAdapter.FillSchema.

MySQLDataAdapter is used in conjunction with MySqlConnection and MySqlCommand to increase performance when connecting to a MySQL database.

The MySQLDataAdapter also includes the MySqlDataAdapter.SelectCommand, MySqlDataAdapter.InsertCommand, MySqlDataAdapter.DeleteCommand, MySqlDataAdapter.UpdateCommand, and DataAdapter.TableMappings properties to facilitate the loading and updating of data.

When an instance of MySQLDataAdapter is created, the read/write properties are set to initial values. For a list of these values, see the MySQLDataAdapter constructor.

Note

Please be aware that the DataColumn class in .NET 1.0 and 1.1 does not allow columns with type of UInt16, UInt32, or UInt64 to be autoincrement columns. If you plan to use autoincremement columns with MySQL, you should consider using signed integer columns.

Examples

The following example creates a MySqlCommand and a MySqlConnection. The MySqlConnection is opened and set as the MySqlCommand.Connection for the MySqlCommand. The example then calls MySqlCommand.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.

Visual Basic example:

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

C# example:

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;
}
23.2.3.4.1. Class MySqlDataAdapter Constructor

Overload methods for MySqlDataAdapter

Initializes a new instance of the MySqlDataAdapter class.

When an instance of MySqlDataAdapter is created, the following read/write properties are set to the following initial values.

Properties Initial Value
MissingMappingAction MissingMappingAction.Passthrough
MissingSchemaAction MissingSchemaAction.Add

You can change the value of any of these properties through a separate call to the property.

Examples

The following example creates a MySqlDataAdapter and sets some of its properties.

Visual Basic example:

 
Public Sub CreateSqlDataAdapter()
    Dim conn As MySqlConnection = New MySqlConnection("Data Source=localhost;" & _
    "database=test")
    Dim da As MySqlDataAdapter = New MySqlDataAdapter
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey    
   
    da.SelectCommand = New MySqlCommand("SELECT id, name FROM mytable", conn)
    da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _
                                            "VALUES (?id, ?name)", conn)
    da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=?id, name=?name " & _
                                            "WHERE id=?oldId", conn)
    da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
End Sub

C# example:

public static void CreateSqlDataAdapter() 
{
    MySqlConnection conn = new MySqlConnection("Data Source=localhost;database=test");
    MySqlDataAdapter da = new MySqlDataAdapter();
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   
    da.SelectCommand = new MySqlCommand("SELECT id, name FROM mytable", conn);
    da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " +
                                            "VALUES (?id, ?name)", conn);
    da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=?id, name=?name " +
                                            "WHERE id=?oldId", conn);
    da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
}
23.2.3.4.2. Class MySqlDataAdapter Constructor Form 1

Initializes a new instance of the MySqlDataAdapter class with the specified MySqlCommand as the SelectCommand property.

Parameters: MySqlCommand that is a SQL SELECT statement or stored procedure and is set as the SelectCommand property of the MySqlDataAdapter.

When an instance of MySqlDataAdapter is created, the following read/write properties are set to the following initial values.

Properties Initial Value
MissingMappingAction MissingMappingAction.Passthrough
MissingSchemaAction MissingSchemaAction.Add

You can change the value of any of these properties through a separate call to the property.

When SelectCommand (or any of the other command properties) is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The SelectCommand maintains a reference to the previously created MySqlCommand object.

Examples

The following example creates a MySqlDataAdapter and sets some of its properties.

Visual Basic example:

 
Public Sub CreateSqlDataAdapter()
    Dim conn As MySqlConnection = New MySqlConnection("Data Source=localhost;" & _
    "database=test")
  Dim cmd as new MySqlCommand("SELECT id, name FROM mytable", conn)
    Dim da As MySqlDataAdapter = New MySqlDataAdapter(cmd)
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey    
   
    da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _
                                            "VALUES (?id, ?name)", conn)
    da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=?id, name=?name " & _
                                            "WHERE id=?oldId", conn)
    da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
End Sub

C# example:

public static void CreateSqlDataAdapter() 
{
    MySqlConnection conn = new MySqlConnection("Data Source=localhost;database=test");
    MySqlCommand cmd = new MySqlCommand("SELECT id, name FROM mytable", conn);
    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   
    da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " +
                                            "VALUES (?id, ?name)", conn);
    da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=?id, name=?name " +
                                            "WHERE id=?oldId", conn);
    da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
}
23.2.3.4.3. Class MySqlDataAdapter Constructor Form 2

Initializes a new instance of the MySqlDataAdapter class with a SelectCommand and a MySqlConnection object.

Parameters: A String that is a SQL SELECT statement or stored procedure to be used by the SelectCommand property of the MySqlDataAdapter.

Parameters: A MySqlConnection that represents the connection.

This implementation of the MySqlDataAdapter opens and closes a MySqlConnection if it is not already open. This can be useful in a an application that must call the DbDataAdapter.Fill method for two or more MySqlDataAdapter objects. If the MySqlConnection is already open, you must explicitly call MySqlConnection.Close or MySqlConnection.Dispose to close it.

When an instance of MySqlDataAdapter is created, the following read/write properties are set to the following initial values.

Properties Initial Value
MissingMappingAction MissingMappingAction.Passthrough
MissingSchemaAction MissingSchemaAction.Add

You can change the value of any of these properties through a separate call to the property.

Examples

The following example creates a MySqlDataAdapter and sets some of its properties.

Visual Basic example:

 
Public Sub CreateSqlDataAdapter()
    Dim conn As MySqlConnection = New MySqlConnection("Data Source=localhost;" & _
    "database=test")
    Dim da As MySqlDataAdapter = New MySqlDataAdapter("SELECT id, name FROM mytable", conn)
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey    
   
    da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _
                                            "VALUES (?id, ?name)", conn)
    da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=?id, name=?name " & _
                                            "WHERE id=?oldId", conn)
    da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
End Sub

C# example:

public static void CreateSqlDataAdapter() 
{
    MySqlConnection conn = new MySqlConnection("Data Source=localhost;database=test");
    MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name FROM mytable", conn);
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   
    da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " +
                                            "VALUES (?id, ?name)", conn);
    da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=?id, name=?name " +
                                            "WHERE id=?oldId", conn);
    da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
}
23.2.3.4.4. Class MySqlDataAdapter Constructor Form 3

Initializes a new instance of the MySqlDataAdapter class with a SelectCommand and a connection string.

Parameters: A string that is a SQL SELECT statement or stored procedure to be used by the SelectCommand property of the MySqlDataAdapter.

Parameters: The connection string

When an instance of MySqlDataAdapter is created, the following read/write properties are set to the following initial values.

Properties Initial Value
MissingMappingAction MissingMappingAction.Passthrough
MissingSchemaAction MissingSchemaAction.Add

You can change the value of any of these properties through a separate call to the property.

Examples

The following example creates a MySqlDataAdapter and sets some of its properties.

Visual Basic example:

 
Public Sub CreateSqlDataAdapter()
    Dim da As MySqlDataAdapter = New MySqlDataAdapter("SELECT id, name FROM mytable", "Data Source=localhost;database=test")
    Dim conn As MySqlConnection = da.SelectCommand.Connection
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey    
   
    da.InsertCommand = New MySqlCommand("INSERT INTO mytable (id, name) " & _
                                            "VALUES (?id, ?name)", conn)
    da.UpdateCommand = New MySqlCommand("UPDATE mytable SET id=?id, name=?name " & _
                                            "WHERE id=?oldId", conn)
    da.DeleteCommand = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name")
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original
End Sub

C# example:

public static void CreateSqlDataAdapter() 
{
    MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, name FROM mytable", "Data Source=localhost;database=test");
    MySqlConnection conn = da.SelectCommand.Connection;
    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   
    da.InsertCommand = new MySqlCommand("INSERT INTO mytable (id, name) " +
                                            "VALUES (?id, ?name)", conn);
    da.UpdateCommand = new MySqlCommand("UPDATE mytable SET id=?id, name=?name " +
                                            "WHERE id=?oldId", conn);
    da.DeleteCommand = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
    da.InsertCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.InsertCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
 
    da.UpdateCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
    da.UpdateCommand.Parameters.Add("?name", MySqlDbType.VarChar, 40, "name");
    da.UpdateCommand.Parameters.Add("?oldId", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
    da.DeleteCommand.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id").SourceVersion = DataRowVersion.Original;
}
23.2.3.4.5. DeleteCommand

Gets or sets a SQL statement or stored procedure used to delete records from the data set.

Value: A MySqlCommand used during System.Data.Common.DataAdapter.Update to delete records in the database that correspond to deleted rows in the DataSet.

During System.Data.Common.DataAdapter.Update, if this property is not set and primary key information is present in the DataSet, the DeleteCommand can be generated automatically if you set the SelectCommand property and use the MySqlCommandBuilder. Then, any additional commands that you do not set are generated by the MySqlCommandBuilder. This generation logic requires key column information to be present in the DataSet.

When DeleteCommand is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The DeleteCommand maintains a reference to the previously created MySqlCommand object.

Examples

The following example creates a MySqlDataAdapter and sets the SelectCommand and DeleteCommand properties. It assumes you have already created a MySqlConnection object.

Visual Basic example:

Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter 
  
  Dim da As MySqlDataAdapter = New MySqlDataAdapter()
  Dim cmd As MySqlCommand
  Dim parm As MySqlParameter
  ' Create the SelectCommand.
  cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
  da.SelectCommand = cmd
  ' Create the DeleteCommand.
  cmd = New MySqlCommand("DELETE FROM mytable WHERE id=?id", conn)
  parm = cmd.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id")
  parm.SourceVersion = DataRowVersion.Original
  da.DeleteCommand = cmd
  Return da
End Function

C# example:

public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn)
{
  MySqlDataAdapter da = new MySqlDataAdapter();
  MySqlCommand cmd;
  MySqlParameter parm;
  // Create the SelectCommand.
  cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15);
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15);
  da.SelectCommand = cmd;
  // Create the DeleteCommand.
  cmd = new MySqlCommand("DELETE FROM mytable WHERE id=?id", conn);
  parm = cmd.Parameters.Add("?id", MySqlDbType.VarChar, 5, "id");
  parm.SourceVersion = DataRowVersion.Original;
  da.DeleteCommand = cmd;
  return da;
}
23.2.3.4.6. InsertCommand

Gets or sets a SQL statement or stored procedure used to insert records into the data set.

Value: A MySqlCommand used during System.Data.Common.DataAdapter.Update to insert records into the database that correspond to new rows in the DataSet.

During System.Data.Common.DataAdapter.Update, if this property is not set and primary key information is present in the DataSet, the InsertCommand can be generated automatically if you set the SelectCommand property and use the MySqlCommandBuilder. Then, any additional commands that you do not set are generated by the MySqlCommandBuilder. This generation logic requires key column information to be present in the DataSet.

When InsertCommand is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The InsertCommand maintains a reference to the previously created MySqlCommand object.

Note

If execution of this command returns rows, these rows may be added to the DataSet depending on how you set the MySqlCommand.UpdatedRowSource property of the MySqlCommand object.

Examples

The following example creates a MySqlDataAdapter and sets the SelectCommand and InsertCommand properties. It assumes you have already created a MySqlConnection object.

Visual Basic example:

Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter 
  
  Dim da As MySqlDataAdapter = New MySqlDataAdapter()
  Dim cmd As MySqlCommand
  Dim parm As MySqlParameter
  ' Create the SelectCommand.
  cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
  da.SelectCommand = cmd
  ' Create the InsertCommand.
  cmd = New MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id, ?name)", conn)
  cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" )
  cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" )  
  da.InsertCommand = cmd
  
  Return da
End Function

C# example:

public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn)
{
  MySqlDataAdapter da = new MySqlDataAdapter();
  MySqlCommand cmd;
  MySqlParameter parm;
  // Create the SelectCommand.
  cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15);
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15);
  da.SelectCommand = cmd;
  // Create the InsertCommand.
  cmd = new MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id,?name)", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15, "id" );
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15, "name" );
  
  da.InsertCommand = cmd;  
  return da;
}
23.2.3.4.7. UpdateCommand

Gets or sets a SQL statement or stored procedure used to updated records in the data source.

Value: A MySqlCommand used during System.Data.Common.DataAdapter.Update to update records in the database with data from the DataSet.

During System.Data.Common.DataAdapter.Update, if this property is not set and primary key information is present in the DataSet, the UpdateCommand can be generated automatically if you set the SelectCommand property and use the MySqlCommandBuilder. Then, any additional commands that you do not set are generated by the MySqlCommandBuilder. This generation logic requires key column information to be present in the DataSet.

When UpdateCommand is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The UpdateCommand maintains a reference to the previously created MySqlCommand object.

Note

If execution of this command returns rows, these rows may be merged with the DataSet depending on how you set the MySqlCommand.UpdatedRowSource property of the MySqlCommand object.

Examples

The following example creates a MySqlDataAdapter and sets the SelectCommand and UpdateCommand properties. It assumes you have already created a MySqlConnection object.

Visual Basic example:

Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter 
  
  Dim da As MySqlDataAdapter = New MySqlDataAdapter()
  Dim cmd As MySqlCommand
  Dim parm As MySqlParameter
  ' Create the SelectCommand.
  cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
  da.SelectCommand = cmd
  ' Create the UpdateCommand.
  cmd = New MySqlCommand("UPDATE mytable SET id=?id, name=?name WHERE id=?oldId", conn)
  cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" )
  cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" )  
  
  parm = cmd.Parameters.Add("?oldId", MySqlDbType.VarChar, 15, "id")
  parm.SourceVersion = DataRowVersion.Original
  
  da.UpdateCommand = cmd
  
  Return da
End Function

C# example:

public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn)
{
  MySqlDataAdapter da = new MySqlDataAdapter();
  MySqlCommand cmd;
  MySqlParameter parm;
  // Create the SelectCommand.
  cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15);
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15);
  da.SelectCommand = cmd;
  // Create the UpdateCommand.
  cmd = new MySqlCommand("UPDATE mytable SET id=?id, name=?name WHERE id=?oldId", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15, "id" );
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15, "name" );
  
  parm = cmd.Parameters.Add( "?oldId", MySqlDbType.VarChar, 15, "id" );
  parm.SourceVersion = DataRowVersion.Original;
  
  da.UpdateCommand = cmd;  
  return da;
}
23.2.3.4.8. SelectCommand

Gets or sets a SQL statement or stored procedure used to select records in the data source.

Value: A MySqlCommand used during System.Data.Common.DbDataAdapter.Fill to select records from the database for placement in the DataSet.

When SelectCommand is assigned to a previously created MySqlCommand, the MySqlCommand is not cloned. The SelectCommand maintains a reference to the previously created MySqlCommand object.

If the SelectCommand does not return any rows, no tables are added to the DataSet, and no exception is raised.

Examples

The following example creates a MySqlDataAdapter and sets the SelectCommand and InsertCommand properties. It assumes you have already created a MySqlConnection object.

Visual Basic example:

Public Shared Function CreateCustomerAdapter(conn As MySqlConnection) As MySqlDataAdapter 
  
  Dim da As MySqlDataAdapter = New MySqlDataAdapter()
  Dim cmd As MySqlCommand
  Dim parm As MySqlParameter
  ' Create the SelectCommand.
  cmd = New MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn)
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15)
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15)
  da.SelectCommand = cmd
  ' Create the InsertCommand.
  cmd = New MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id, ?name)", conn)
  cmd.Parameters.Add( "?id", MySqlDbType.VarChar, 15, "id" )
  cmd.Parameters.Add( "?name", MySqlDbType.VarChar, 15, "name" )  
  da.InsertCommand = cmd
  
  Return da
End Function

C# example:

public static MySqlDataAdapter CreateCustomerAdapter(MySqlConnection conn)
{
  MySqlDataAdapter da = new MySqlDataAdapter();
  MySqlCommand cmd;
  MySqlParameter parm;
  // Create the SelectCommand.
  cmd = new MySqlCommand("SELECT * FROM mytable WHERE id=?id AND name=?name", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15);
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15);
  da.SelectCommand = cmd;
  // Create the InsertCommand.
  cmd = new MySqlCommand("INSERT INTO mytable (id,name) VALUES (?id,?name)", conn);
  cmd.Parameters.Add("?id", MySqlDbType.VarChar, 15, "id" );
  cmd.Parameters.Add("?name", MySqlDbType.VarChar, 15, "name" );
  
  da.InsertCommand = cmd;  
  return da;
}

23.2.3.5. Using MySqlDataReader

To create a MySQLDataReader, you must call the MySqlCommand.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 MySqlDataReader.Close method of the MySqlDataReader is called.

MySqlDataReader.IsClosed and MySqlDataReader.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.

For optimal performance, MySqlDataReader avoids creating unnecessary objects or making unnecessary copies of data. As a result, multiple calls to methods such as MySqlDataReader.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.

Examples

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.

Visual Basic example:

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

C# example:

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

GetBytes returns the number of available bytes in the field. In most cases this is the exact length of the field. However, the number returned may be less than the true length of the field if GetBytes has already been used to obtain bytes from the field. This may be the case, for example, if the MySqlDataReader is reading a large data structure into a buffer. For more information, see the SequentialAccess setting for MySqlCommand.CommandBehavior.

If you pass a buffer that is a null reference (Nothing in Visual Basic), GetBytes returns the length of the field in bytes.

No conversions are performed; therefore the data retrieved must already be a byte array.

23.2.3.5.2. GetTimeSpan

Gets the value of the specified column as a TimeSpan object.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.3. GetDateTime

Gets the value of the specified column as a System.DateTime object.

Note

MySQL allows date columns to contain the value '0000-00-00' and datetime columns to contain the value '0000-00-00 00:00:00'. The DateTime structure cannot contain or represent these values. To read a datetime value from a column that might contain zero values, use GetMySqlDateTime. The behavior of reading a zero datetime column using this method is defined by the ZeroDateTimeBehavior connection string option. For more information on this option, please refer to MySqlConnection.ConnectionString.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.4. GetMySqlDateTime

Gets the value of the specified column as a MySql.Data.Types.MySqlDateTime object.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.5. GetString

Gets the value of the specified column as a String object.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.6. GetDecimal

Gets the value of the specified column as a Decimal object.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.7. GetDouble

Gets the value of the specified column as a double-precision floating point number.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.8. GetFloat

Gets the value of the specified column as a single-precision floating point number.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.9. GetGiud

Gets the value of the specified column as a globally-unique identifier (GUID).

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.10. GetInt16

Gets the value of the specified column as a 16-bit signed integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.11. GetInt32

Gets the value of the specified column as a 32-bit signed integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.12. GetInt64

Gets the value of the specified column as a 64-bit signed integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.13. GetUInt16

Gets the value of the specified column as a 16-bit unsigned integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.14. GetUInt32

Gets the value of the specified column as a 32-bit unsigned integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.5.15. GetUInt64

Gets the value of the specified column as a 64-bit unsigned integer.

Parameters: The zero-based column ordinal.

Returns: The value of the specified column.

23.2.3.6. Using MySqlException

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 application determines that the exception is fatal, it should close any open MySqlDataReader objects or MySqlConnection objects.

Examples

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

Visual Basic example:

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

C# example:

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

23.2.3.7. Using MySqlParameter

Parameter names are not case sensitive.

Examples

The following example creates multiple instances of MySqlParameter through the MySqlParameterCollection collection within the MySqlDataAdapter. These parameters are used to select data from the data source and place the data in the DataSet. This example assumes that a DataSet and a MySqlDataAdapter have already been created with the appropriate schema, commands, and connection.

Visual Basic example:

Public Sub AddSqlParameters()
    ' ...
    ' create myDataSet and myDataAdapter
    ' ...
    myDataAdapter.SelectCommand.Parameters.Add("@CategoryName", MySqlDbType.VarChar, 80).Value = "toasters"
    myDataAdapter.SelectCommand.Parameters.Add("@SerialNum", MySqlDbType.Long).Value = 239
    
    myDataAdapter.Fill(myDataSet)
End Sub 'AddSqlParameters

C# example:

public void AddSqlParameters() 
{
// ...
// create myDataSet and myDataAdapter
// ...
  myDataAdapter.SelectCommand.Parameters.Add("@CategoryName", MySqlDbType.VarChar, 80).Value = "toasters";
  myDataAdapter.SelectCommand.Parameters.Add("@SerialNum", MySqlDbType.Long).Value = 239;
  myDataAdapter.Fill(myDataSet);
}

23.2.3.8. Using MySqlParameterCollection

The number of the parameters in the collection must be equal to the number of parameter placeholders within the command text, or an exception will be generated.

Examples

The following example creates multiple instances of MySqlParameter through the MySqlParameterCollection collection within the MySqlDataAdapter. These parameters are used to select data within the data source and place the data in the DataSet. This code assumes that a DataSet and a MySqlDataAdapter have already been created with the appropriate schema, commands, and connection.

Visual Basic example:

Public Sub AddParameters()
    ' ...
    ' create myDataSet and myDataAdapter
    ' ...
    myDataAdapter.SelectCommand.Parameters.Add("@CategoryName", MySqlDbType.VarChar, 80).Value = "toasters"
    myDataAdapter.SelectCommand.Parameters.Add("@SerialNum", MySqlDbType.Long).Value = 239
    
    myDataAdapter.Fill(myDataSet)
End Sub 'AddSqlParameters

C# example:

public void AddSqlParameters() 
{
// ...
// create myDataSet and myDataAdapter
// ...
  myDataAdapter.SelectCommand.Parameters.Add("@CategoryName", MySqlDbType.VarChar, 80).Value = "toasters";
  myDataAdapter.SelectCommand.Parameters.Add("@SerialNum", MySqlDbType.Long).Value = 239;
  myDataAdapter.Fill(myDataSet);
}

23.2.3.9. Using MySqlTransaction

Represents a SQL transaction to be made in a MySQL database. This class cannot be inherited.

The application creates a MySqlTransaction object by calling MySqlConnection.BeginTransaction on the MySqlConnection object. All subsequent operations associated with the transaction (for example, committing or aborting the transaction), are performed on the MySqlTransaction object.

Examples

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

Visual Basic example:

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

C# example:

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

Rolls back a transaction from a pending state.

The Rollback method is equivalent to the MySQL statement ROLLBACK. The transaction can only be rolled back from a pending state (after BeginTransaction has been called, but before Commit is called).

Examples

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

Visual Basic example:

Public Sub RunSqlTransaction(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 mytable (id, desc) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Success.")
    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

C# example:

public void RunSqlTransaction(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 mytable (id, desc) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "Insert into mytable (id, desc) 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();
    }
}
23.2.3.9.2. Commit

Commits the database transaction.

The Commit method is equivalent to the MySQL SQL statement COMMIT.

Examples

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

Visual Basic example:

Public Sub RunSqlTransaction(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 mytable (id, desc) VALUES (100, 'Description')"
      myCommand.ExecuteNonQuery()
      myCommand.CommandText = "Insert into mytable (id, desc) VALUES (101, 'Description')"
      myCommand.ExecuteNonQuery()
      myTrans.Commit()
      Console.WriteLine("Success.")
    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

C# example:

public void RunSqlTransaction(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 mytable (id, desc) VALUES (100, 'Description')";
      myCommand.ExecuteNonQuery();
      myCommand.CommandText = "Insert into mytable (id, desc) 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();
    }
}

23.2.4. Connector/NET Reference

This section of the manual contains a complete reference to the Connector/NET ADO.NET component, automatically generated from the embedded documentation.

23.2.4.1. MySql.Data.MySqlClient

Namespace hierarchy

Classes

ClassDescription
MySqlCommand 
MySqlCommandBuilder 
MySqlConnection 
MySqlDataAdapter 
MySqlDataReaderProvides a means of reading a forward-only stream of rows from a MySQL database. This class cannot be inherited.
MySqlErrorCollection of error codes that can be returned by the server
MySqlExceptionThe exception that is thrown when MySQL returns an error. This class cannot be inherited.
MySqlHelperHelper class that makes it easier to work with the provider.
MySqlInfoMessageEventArgsProvides data for the InfoMessage event. This class cannot be inherited.
MySqlParameterRepresents a parameter to a MySqlCommand , and optionally, its mapping to DataSetcolumns. This class cannot be inherited.
MySqlParameterCollectionRepresents a collection of parameters relevant to a MySqlCommand as well as their respective mappings to columns in a DataSet. This class cannot be inherited.
MySqlRowUpdatedEventArgsProvides data for the RowUpdated event. This class cannot be inherited.
MySqlRowUpdatingEventArgsProvides data for the RowUpdating event. This class cannot be inherited.
MySqlTransaction 

Delegates

DelegateDescription
MySqlInfoMessageEventHandlerRepresents the method that will handle the InfoMessage event of a MySqlConnection.
MySqlRowUpdatedEventHandlerRepresents the method that will handle the RowUpdatedevent of a MySqlDataAdapter .
MySqlRowUpdatingEventHandlerRepresents the method that will handle the RowUpdatingevent of a MySqlDataAdapter .

Enumerations

EnumerationDescription
MySqlDbTypeSpecifies MySQL specific data type of a field, property, for use in a MySqlParameter .
MySqlErrorCode 
23.2.4.1.1. MySql.Data.MySqlClientHierarchy

See Also

MySql.Data.MySqlClient Namespace

23.2.4.1.2. MySqlCommand Class

For a list of all members of this type, see MySqlCommand Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlCommand_
  Inherits Component_
  Implements IDbCommand, ICloneable

Syntax: C#

public sealed class MySqlCommand : Component, IDbCommand, ICloneable

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlCommand Members , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1. MySqlCommand Members

MySqlCommand overview

Public Instance Constructors

MySqlCommandOverloaded. Initializes a new instance of the MySqlCommand class.

Public Instance Properties

CommandText 
CommandTimeout 
CommandType 
Connection 
Container(inherited from Component)Gets the IContainerthat contains the Component.
IsPrepared 
Parameters 
Site(inherited from Component)Gets or sets the ISiteof the Component.
Transaction 
UpdatedRowSource 

Public Instance Methods

CancelAttempts to cancel the execution of a MySqlCommand. This operation is not supported.
CreateObjRef(inherited from MarshalByRefObject)Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.
CreateParameterCreates a new instance of a MySqlParameter object.
Dispose(inherited from Component)Releases all resources used by the Component.
Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
ExecuteNonQuery 
ExecuteReaderOverloaded.
ExecuteScalar 
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetLifetimeService(inherited from MarshalByRefObject)Retrieves the current lifetime service object that controls the lifetime policy for this instance.
GetType(inherited from Object)Gets the Typeof the current instance.
InitializeLifetimeService(inherited from MarshalByRefObject)Obtains a lifetime service object to control the lifetime policy for this instance.
Prepare 
ToString(inherited from Component)Returns a Stringcontaining the name of the Component, if any. This method should not be overridden.

Public Instance Events

Disposed(inherited from Component)Adds an event handler to listen to the Disposedevent on the component.

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1. MySqlCommand Constructor

Initializes a new instance of the MySqlCommand class.

Overload List

Initializes a new instance of the MySqlCommand class.

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.1. MySqlCommand Constructor ()

Initializes a new instance of the MySqlCommand class.

Syntax: Visual Basic

Overloads Public Sub New()

Syntax: C#

public MySqlCommand();

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace , MySqlCommand Constructor Overload List

23.2.4.1.2.1.1.2. MySqlCommand Constructor (String)

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal cmdText As String _
)

Syntax: C#

public MySqlCommand(
stringcmdText
);

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace , MySqlCommand Constructor Overload List

23.2.4.1.2.1.1.3. MySqlCommand Constructor

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal cmdText As String, _
   ByVal connection As MySqlConnection _
)

Syntax: C#

public MySqlCommand(
stringcmdText,
MySqlConnectionconnection
);

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace , MySqlCommand Constructor Overload List

23.2.4.1.2.1.1.3.1. MySqlConnection Class

For a list of all members of this type, see MySqlConnection Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlConnection_
  Inherits Component_
  Implements IDbConnection, ICloneable

Syntax: C#

public sealed class MySqlConnection : Component, IDbConnection, ICloneable

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlConnection Members , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1. MySqlConnection Members

MySqlConnection overview

Public Instance Constructors

MySqlConnectionOverloaded. Initializes a new instance of the MySqlConnection class.

Public Instance Properties

ConnectionString 
ConnectionTimeout 
Container(inherited from Component)Gets the IContainerthat contains the Component.
Database 
DataSourceGets the name of the MySQL server to which to connect.
ServerThreadReturns the id of the server thread this connection is executing on
ServerVersion 
Site(inherited from Component)Gets or sets the ISiteof the Component.
State 
UseCompressionIndicates if this connection should use compression when communicating with the server.

Public Instance Methods

BeginTransactionOverloaded.
ChangeDatabase 
Close 
CreateCommand 
CreateObjRef(inherited from MarshalByRefObject)Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.
Dispose(inherited from Component)Releases all resources used by the Component.
Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetLifetimeService(inherited from MarshalByRefObject)Retrieves the current lifetime service object that controls the lifetime policy for this instance.
GetType(inherited from Object)Gets the Typeof the current instance.
InitializeLifetimeService(inherited from MarshalByRefObject)Obtains a lifetime service object to control the lifetime policy for this instance.
Open 
PingPing
ToString(inherited from Component)Returns a Stringcontaining the name of the Component, if any. This method should not be overridden.

Public Instance Events

Disposed(inherited from Component)Adds an event handler to listen to the Disposedevent on the component.
InfoMessage 
StateChange 

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.1. MySqlConnection Constructor

Initializes a new instance of the MySqlConnection class.

Overload List

Initializes a new instance of the MySqlConnection class.

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.1.1. MySqlConnection Constructor

Initializes a new instance of the MySqlConnection class.

Syntax: Visual Basic

Overloads Public Sub New()

Syntax: C#

public MySqlConnection();

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace , MySqlConnection Constructor Overload List

23.2.4.1.2.1.1.3.1.1.1.2. MySqlConnection Constructor

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal connectionString As String _
)

Syntax: C#

public MySqlConnection(
stringconnectionString
);

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace , MySqlConnection Constructor Overload List

23.2.4.1.2.1.1.3.1.1.2. ConnectionString Property

Syntax: Visual Basic

NotOverridable Public Property ConnectionString As String _
_
  Implements IDbConnection.ConnectionString

Syntax: C#

public string ConnectionString {get; set;}

Implements

IDbConnection.ConnectionString

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.3. ConnectionTimeout Property

Syntax: Visual Basic

NotOverridable Public ReadOnly Property ConnectionTimeout As Integer _
_
  Implements IDbConnection.ConnectionTimeout

Syntax: C#

public int ConnectionTimeout {get;}

Implements

IDbConnection.ConnectionTimeout

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.4. Database Property

Syntax: Visual Basic

NotOverridable Public ReadOnly Property Database As String _
_
  Implements IDbConnection.Database

Syntax: C#

public string Database {get;}

Implements

IDbConnection.Database

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.5. DataSource Property

Gets the name of the MySQL server to which to connect.

Syntax: Visual Basic

Public ReadOnly Property DataSource As String

Syntax: C#

public string DataSource {get;}

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.6. ServerThread Property

Returns the id of the server thread this connection is executing on

Syntax: Visual Basic

Public ReadOnly Property ServerThread As Integer

Syntax: C#

public int ServerThread {get;}

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.7. ServerVersion Property

Syntax: Visual Basic

Public ReadOnly Property ServerVersion As String

Syntax: C#

public string ServerVersion {get;}

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.8. State Property

Syntax: Visual Basic

NotOverridable Public ReadOnly Property State As ConnectionState _
_
  Implements IDbConnection.State

Syntax: C#

public System.Data.ConnectionState State {get;}

Implements

IDbConnection.State

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.9. UseCompression Property

Indicates if this connection should use compression when communicating with the server.

Syntax: Visual Basic

Public ReadOnly Property UseCompression As Boolean

Syntax: C#

public bool UseCompression {get;}

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.10. BeginTransaction Method

Overload List

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.10.1. MySqlConnection.BeginTransaction Method

Syntax: Visual Basic

Overloads Public Function BeginTransaction() As MySqlTransaction

Syntax: C#

public MySqlTransaction BeginTransaction();

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace , MySqlConnection.BeginTransaction Overload List

23.2.4.1.2.1.1.3.1.1.10.1.1. MySqlTransaction Class

For a list of all members of this type, see MySqlTransaction Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlTransaction_
  Implements IDbTransaction, IDisposable

Syntax: C#

public sealed class MySqlTransaction : IDbTransaction, IDisposable

Thread Safety

Public static (Sharedin Visual Basic) members of this type are safe for multithreaded operations. Instance members are notguaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlTransaction Members , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.10.1.1.1. MySqlTransaction Members

MySqlTransaction overview

Public Instance Properties

ConnectionGets the MySqlConnection object associated with the transaction, or a null reference (Nothing in Visual Basic) if the transaction is no longer valid.
IsolationLevelSpecifies the IsolationLevelfor this transaction.

Public Instance Methods

Commit 
Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetType(inherited from Object)Gets the Typeof the current instance.
Rollback 
ToString(inherited from Object)Returns a Stringthat represents the current Object.

See Also

MySqlTransaction Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.10.1.1.1.1. Connection Property

Gets the MySqlConnection object associated with the transaction, or a null reference (Nothing in Visual Basic) if the transaction is no longer valid.

Syntax: Visual Basic

Public ReadOnly Property Connection As MySqlConnection

Syntax: C#

public MySqlConnection Connection {get;}

Property Value

The MySqlConnection object associated with this transaction.

Remarks

A single application may have multiple database connections, each with zero or more transactions. This property enables you to determine the connection object associated with a particular transaction created by BeginTransaction .

See Also

MySqlTransaction Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.10.1.1.1.2. IsolationLevel Property

Specifies the IsolationLevelfor this transaction.

Syntax: Visual Basic

NotOverridable Public ReadOnly Property IsolationLevel As IsolationLevel _
_
  Implements IDbTransaction.IsolationLevel

Syntax: C#

public System.Data.IsolationLevel IsolationLevel {get;}

Property Value

The IsolationLevel for this transaction. The default is ReadCommitted.

Implements

IDbTransaction.IsolationLevel

Remarks

Parallel transactions are not supported. Therefore, the IsolationLevel applies to the entire transaction.

See Also

MySqlTransaction Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.10.1.1.1.3. MySqlTransaction.Commit Method

Syntax: Visual Basic

NotOverridable Public Sub Commit() _
_
  Implements IDbTransaction.Commit

Syntax: C#

public void Commit();

Implements

IDbTransaction.Commit

See Also

MySqlTransaction Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.10.1.1.1.4. MySqlTransaction.Rollback Method

Syntax: Visual Basic

NotOverridable Public Sub Rollback() _
_
  Implements IDbTransaction.Rollback

Syntax: C#

public void Rollback();

Implements

IDbTransaction.Rollback

See Also

MySqlTransaction Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.10.2. MySqlConnection.BeginTransaction Method

Syntax: Visual Basic

Overloads Public Function BeginTransaction( _
   ByVal iso As IsolationLevel _
) As MySqlTransaction

Syntax: C#

public MySqlTransaction BeginTransaction(
IsolationLeveliso
);

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace , MySqlConnection.BeginTransaction Overload List

23.2.4.1.2.1.1.3.1.1.11. MySqlConnection.ChangeDatabase Method

Syntax: Visual Basic

NotOverridable Public Sub ChangeDatabase( _
   ByVal databaseName As String _
) _
_
  Implements IDbConnection.ChangeDatabase

Syntax: C#

public void ChangeDatabase(
stringdatabaseName
);

Implements

IDbConnection.ChangeDatabase

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.12. MySqlConnection.Close Method

Syntax: Visual Basic

NotOverridable Public Sub Close() _
_
  Implements IDbConnection.Close

Syntax: C#

public void Close();

Implements

IDbConnection.Close

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.13. MySqlConnection.CreateCommand Method

Syntax: Visual Basic

Public Function CreateCommand() As MySqlCommand

Syntax: C#

public MySqlCommand CreateCommand();

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.14. MySqlConnection.Open Method

Syntax: Visual Basic

NotOverridable Public Sub Open() _
_
  Implements IDbConnection.Open

Syntax: C#

public void Open();

Implements

IDbConnection.Open

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.15. MySqlConnection.Ping Method

Ping

Syntax: Visual Basic

Public Function Ping() As Boolean

Syntax: C#

public bool Ping();

Return Value

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16. MySqlConnection.InfoMessage Event

Syntax: Visual Basic

Public Event InfoMessage As MySqlInfoMessageEventHandler

Syntax: C#

public event MySqlInfoMessageEventHandler InfoMessage;

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1. MySqlInfoMessageEventHandler Delegate

Represents the method that will handle the InfoMessage event of a MySqlConnection .

Syntax: Visual Basic

Public Delegate Sub MySqlInfoMessageEventHandler( _
   ByVal sender As Object, _
   ByVal args As MySqlInfoMessageEventArgs _
)

Syntax: C#

public delegate void MySqlInfoMessageEventHandler(
objectsender,
MySqlInfoMessageEventArgsargs
);

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1. MySqlInfoMessageEventArgs Class

Provides data for the InfoMessage event. This class cannot be inherited.

For a list of all members of this type, see MySqlInfoMessageEventArgs Members .

Syntax: Visual Basic

Public Class MySqlInfoMessageEventArgs_
  Inherits EventArgs

Syntax: C#

public class MySqlInfoMessageEventArgs : EventArgs

Thread Safety

Public static (Sharedin Visual Basic) members of this type are safe for multithreaded operations. Instance members are notguaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlInfoMessageEventArgs Members , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1.1. MySqlInfoMessageEventArgs Members

MySqlInfoMessageEventArgs overview

Public Instance Constructors

Public Instance Fields

Public Instance Methods

Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetType(inherited from Object)Gets the Typeof the current instance.
ToString(inherited from Object)Returns a Stringthat represents the current Object.

Protected Instance Methods

Finalize(inherited from Object)Allows an Objectto attempt to free resources and perform other cleanup operations before the Objectis reclaimed by garbage collection.
MemberwiseClone(inherited from Object)Creates a shallow copy of the current Object.

See Also

MySqlInfoMessageEventArgs Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1.1.1. MySqlInfoMessageEventArgs Constructor

Initializes a new instance of the MySqlInfoMessageEventArgs class.

Syntax: Visual Basic

Public Sub New()

Syntax: C#

public MySqlInfoMessageEventArgs();

See Also

MySqlInfoMessageEventArgs Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1.1.2. MySqlInfoMessageEventArgs.errors Field

Syntax: Visual Basic

Public errors As MySqlError()

Syntax: C#

public MySqlError[] errors;

See Also

MySqlInfoMessageEventArgs Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1.1.2.1. MySqlError Class

Collection of error codes that can be returned by the server

For a list of all members of this type, see MySqlError Members .

Syntax: Visual Basic

Public Class MySqlError

Syntax: C#

public class MySqlError

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlError Members , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1.1.2.1.1. MySqlError Members

MySqlError overview

Public Instance Constructors

Public Instance Properties

CodeError code
LevelError level
MessageError message

Public Instance Methods

Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetType(inherited from Object)Gets the Typeof the current instance.
ToString(inherited from Object)Returns a Stringthat represents the current Object.

Protected Instance Methods

Finalize(inherited from Object)Allows an Objectto attempt to free resources and perform other cleanup operations before the Objectis reclaimed by garbage collection.
MemberwiseClone(inherited from Object)Creates a shallow copy of the current Object.

See Also

MySqlError Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1.1.2.1.1.1. MySqlError Constructor

Syntax: Visual Basic

Public Sub New( _
   ByVal level As String, _
   ByVal code As Integer, _
   ByVal message As String _
)

Syntax: C#

public MySqlError(
stringlevel,
intcode,
stringmessage
);

Parameters

  • level:

  • code:

  • message:

See Also

MySqlError Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1.1.2.1.1.2. Code Property

Error code

Syntax: Visual Basic

Public ReadOnly Property Code As Integer

Syntax: C#

public int Code {get;}

See Also

MySqlError Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1.1.2.1.1.3. Level Property

Error level

Syntax: Visual Basic

Public ReadOnly Property Level As String

Syntax: C#

public string Level {get;}

See Also

MySqlError Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.16.1.1.1.2.1.1.4. Message Property

Error message

Syntax: Visual Basic

Public ReadOnly Property Message As String

Syntax: C#

public string Message {get;}

See Also

MySqlError Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.3.1.1.17. MySqlConnection.StateChange Event

Syntax: Visual Basic

Public Event StateChange As StateChangeEventHandler

Syntax: C#

public event StateChangeEventHandler StateChange;

See Also

MySqlConnection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.1.4. MySqlCommand Constructor

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal cmdText As String, _
   ByVal connection As MySqlConnection, _
   ByVal transaction As MySqlTransaction _
)

Syntax: C#

public MySqlCommand(
stringcmdText,
MySqlConnectionconnection,
MySqlTransactiontransaction
);

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace , MySqlCommand Constructor Overload List

23.2.4.1.2.1.2. CommandText Property

Syntax: Visual Basic

NotOverridable Public Property CommandText As String _
_
  Implements IDbCommand.CommandText

Syntax: C#

public string CommandText {get; set;}

Implements

IDbCommand.CommandText

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.3. CommandTimeout Property

Syntax: Visual Basic

NotOverridable Public Property CommandTimeout As Integer _
_
  Implements IDbCommand.CommandTimeout

Syntax: C#

public int CommandTimeout {get; set;}

Implements

IDbCommand.CommandTimeout

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.4. CommandType Property

Syntax: Visual Basic

NotOverridable Public Property CommandType As CommandType _
_
  Implements IDbCommand.CommandType

Syntax: C#

public System.Data.CommandType CommandType {get; set;}

Implements

IDbCommand.CommandType

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.5. Connection Property

Syntax: Visual Basic

Public Property Connection As MySqlConnection

Syntax: C#

public MySqlConnection Connection {get; set;}

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.6. IsPrepared Property

Syntax: Visual Basic

Public ReadOnly Property IsPrepared As Boolean

Syntax: C#

public bool IsPrepared {get;}

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7. Parameters Property

Syntax: Visual Basic

Public ReadOnly Property Parameters As MySqlParameterCollection

Syntax: C#

public MySqlParameterCollection Parameters {get;}

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1. MySqlParameterCollection Class

Represents a collection of parameters relevant to a MySqlCommand as well as their respective mappings to columns in a DataSet. This class cannot be inherited.

For a list of all members of this type, see MySqlParameterCollection Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlParameterCollection_
  Inherits MarshalByRefObject_
  Implements IDataParameterCollection, IList, ICollection, IEnumerable

Syntax: C#

public sealed class MySqlParameterCollection : MarshalByRefObject, IDataParameterCollection, IList, ICollection, IEnumerable

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlParameterCollection Members , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1. MySqlParameterCollection Members

MySqlParameterCollection overview

Public Instance Constructors

MySqlParameterCollection ConstructorInitializes a new instance of the MySqlParameterCollection class.

Public Instance Properties

CountGets the number of MySqlParameter objects in the collection.
ItemOverloaded. Gets the MySqlParameter with a specified attribute. In C#, this property is the indexer for the MySqlParameterCollection class.

Public Instance Methods

AddOverloaded. Adds the specified MySqlParameter object to the MySqlParameterCollection .
ClearRemoves all items from the collection.
ContainsOverloaded. Gets a value indicating whether a MySqlParameter exists in the collection.
CopyToCopies MySqlParameter objects from the MySqlParameterCollection to the specified array.
CreateObjRef(inherited from MarshalByRefObject)Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.
Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetLifetimeService(inherited from MarshalByRefObject)Retrieves the current lifetime service object that controls the lifetime policy for this instance.
GetType(inherited from Object)Gets the Typeof the current instance.
IndexOfOverloaded. Gets the location of a MySqlParameter in the collection.
InitializeLifetimeService(inherited from MarshalByRefObject)Obtains a lifetime service object to control the lifetime policy for this instance.
InsertInserts a MySqlParameter into the collection at the specified index.
RemoveRemoves the specified MySqlParameter from the collection.
RemoveAtOverloaded. Removes the specified MySqlParameter from the collection.
ToString(inherited from Object)Returns a Stringthat represents the current Object.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.1. MySqlParameterCollection Constructor

Initializes a new instance of the MySqlParameterCollection class.

Syntax: Visual Basic

Public Sub New()

Syntax: C#

public MySqlParameterCollection();

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.2. Count Property

Gets the number of MySqlParameter objects in the collection.

Syntax: Visual Basic

NotOverridable Public ReadOnly Property Count As Integer _
_
  Implements ICollection.Count

Syntax: C#

public int Count {get;}

Implements

ICollection.Count

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3. Item Property

Gets the MySqlParameter with a specified attribute. In C#, this property is the indexer for the MySqlParameterCollection class.

Overload List

Gets the MySqlParameter at the specified index.

Gets the MySqlParameter with the specified name.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1. MySqlParameter Class

Represents a parameter to a MySqlCommand , and optionally, its mapping to DataSetcolumns. This class cannot be inherited.

For a list of all members of this type, see MySqlParameter Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlParameter_
  Inherits MarshalByRefObject_
  Implements IDataParameter, IDbDataParameter, ICloneable

Syntax: C#

public sealed class MySqlParameter : MarshalByRefObject, IDataParameter, IDbDataParameter, ICloneable

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlParameter Members , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1. MySqlParameter Members

MySqlParameter overview

Public Instance Constructors

MySqlParameterOverloaded. Initializes a new instance of the MySqlParameter class.

Public Instance Properties

DbTypeGets or sets the DbTypeof the parameter.
DirectionGets or sets a value indicating whether the parameter is input-only, output-only, bidirectional, or a stored procedure return value parameter. As of MySQL version 4.1 and earlier, input-only is the only valid choice.
IsNullableGets or sets a value indicating whether the parameter accepts null values.
IsUnsigned 
MySqlDbTypeGets or sets the MySqlDbType of the parameter.
ParameterNameGets or sets the name of the MySqlParameter.
PrecisionGets or sets the maximum number of digits used to represent the Value property.
ScaleGets or sets the number of decimal places to which Value is resolved.
SizeGets or sets the maximum size, in bytes, of the data within the column.
SourceColumnGets or sets the name of the source column that is mapped to the DataSetand used for loading or returning the Value .
SourceVersionGets or sets the DataRowVersionto use when loading Value .
ValueGets or sets the value of the parameter.

Public Instance Methods

CreateObjRef(inherited from MarshalByRefObject)Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.
Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetLifetimeService(inherited from MarshalByRefObject)Retrieves the current lifetime service object that controls the lifetime policy for this instance.
GetType(inherited from Object)Gets the Typeof the current instance.
InitializeLifetimeService(inherited from MarshalByRefObject)Obtains a lifetime service object to control the lifetime policy for this instance.
ToStringOverridden. Gets a string containing the ParameterName .

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.1. MySqlParameter Constructor

Initializes a new instance of the MySqlParameter class.

Overload List

Initializes a new instance of the MySqlParameter class.

Initializes a new instance of the MySqlParameter class with the parameter name and the data type.

Initializes a new instance of the MySqlParameter class with the parameter name, the MySqlDbType , and the size.

Initializes a new instance of the MySqlParameter class with the parameter name, the type of the parameter, the size of the parameter, a ParameterDirection, the precision of the parameter, the scale of the parameter, the source column, a DataRowVersionto use, and the value of the parameter.

Initializes a new instance of the MySqlParameter class with the parameter name, the MySqlDbType , the size, and the source column name.

Initializes a new instance of the MySqlParameter class with the parameter name and a value of the new MySqlParameter.

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.1.1. MySqlParameter Constructor ()

Initializes a new instance of the MySqlParameter class.

Syntax: Visual Basic

Overloads Public Sub New()

Syntax: C#

public MySqlParameter();

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace , MySqlParameter Constructor Overload List

23.2.4.1.2.1.7.1.1.3.1.1.1.2. MySqlParameter Constructor

Initializes a new instance of the MySqlParameter class with the parameter name and the data type.

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType _
)

Syntax: C#

public MySqlParameter(
stringparameterName,
MySqlDbTypedbType
);

Parameters

  • parameterName: The name of the parameter to map.

  • dbType: One of the MySqlDbType values.

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace , MySqlParameter Constructor Overload List

23.2.4.1.2.1.7.1.1.3.1.1.1.2.1. MySqlDbType Enumeration

Specifies MySQL specific data type of a field, property, for use in a MySqlParameter .

Syntax: Visual Basic

Public Enum MySqlDbType

Syntax: C#

public enum MySqlDbType

Members

Member NameDescription
VarStringA variable-length string containing 0 to 65535 characters
TimestampA timestamp. The range is '1970-01-01 00:00:01' to sometime in the year 2038
LongBlobA BLOB or TEXT column with a maximum length of 4294967295 or 4G (2^32 - 1) characters
Time

The range is '-838:59:59' to '838:59:59'.

TinyBlobA BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters
DatetimeThe supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
Decimal

A fixed precision and scale numeric value between -1038 -1 and 10 38 -1.

UByte 
BlobA BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters
Double

A normal-size (double-precision) floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.

NewdateObsolete Use Datetime or Date type
Byte

The signed range is -128 to 127. The unsigned range is 0 to 255.

DateDate The supported range is '1000-01-01' to '9999-12-31'.
VarCharA variable-length string containing 0 to 255 characters
UInt16 
UInt24 
Int16

A 16-bit signed integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535

NewDecimalNew Decimal
SetA set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members.
StringObsolete Use VarChar type
EnumAn enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special "" error value. An ENUM can have a maximum of 65535 distinct values
Geometry 
UInt64 
Int64

A 64-bit signed integer.

UInt32 
Int24Specifies a 24 (3 byte) signed or unsigned value.
BitBit-field data type
Float

A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38.

YearA year in 2- or 4-digit format (default is 4-digit). The allowable values are 1901 to 2155, 0000 in the 4-digit year format, and 1970-2069 if you use the 2-digit format (70-69)
Int32

A 32-bit signed integer

MediumBlobA BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.1.3. MySqlParameter Constructor (String, MySqlDbType, Int32)

Initializes a new instance of the MySqlParameter class with the parameter name, the MySqlDbType , and the size.

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer _
)

Syntax: C#

public MySqlParameter(
stringparameterName,
MySqlDbTypedbType,
intsize
);

Parameters

  • parameterName: The name of the parameter to map.

  • dbType: One of the MySqlDbType values.

  • size: The length of the parameter.

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace , MySqlParameter Constructor Overload List

23.2.4.1.2.1.7.1.1.3.1.1.1.4. MySqlParameter Constructor

Initializes a new instance of the MySqlParameter class with the parameter name, the type of the parameter, the size of the parameter, a ParameterDirection, the precision of the parameter, the scale of the parameter, the source column, a DataRowVersionto use, and the value of the parameter.

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer, _
   ByVal direction As ParameterDirection, _
   ByVal isNullable As Boolean, _
   ByVal precision As Byte, _
   ByVal scale As Byte, _
   ByVal sourceColumn As String, _
   ByVal sourceVersion As DataRowVersion, _
   ByVal value As Object _
)

Syntax: C#

public MySqlParameter(
stringparameterName,
MySqlDbTypedbType,
intsize,
ParameterDirectiondirection,
boolisNullable,
byteprecision,
bytescale,
stringsourceColumn,
DataRowVersionsourceVersion,
objectvalue
);

Parameters

  • parameterName: The name of the parameter to map.

  • dbType: One of the MySqlDbType values.

  • size: The length of the parameter.

  • direction: One of the ParameterDirectionvalues.

  • isNullable: true if the value of the field can be null, otherwise false.

  • precision: The total number of digits to the left and right of the decimal point to which Value is resolved.

  • scale: The total number of decimal places to which Value is resolved.

  • sourceColumn: The name of the source column.

  • sourceVersion: One of the DataRowVersionvalues.

  • value: An Objectthat is the value of the MySqlParameter .

Exceptions

Exception TypeCondition
ArgumentException 

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace , MySqlParameter Constructor Overload List

23.2.4.1.2.1.7.1.1.3.1.1.1.4.1. Value Property

Gets or sets the value of the parameter.

Syntax: Visual Basic

NotOverridable Public Property Value As Object _
_
  Implements IDataParameter.Value

Syntax: C#

public object Value {get; set;}

Implements

IDataParameter.Value

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.1.5. MySqlParameter Constructor

Initializes a new instance of the MySqlParameter class with the parameter name, the MySqlDbType , the size, and the source column name.

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer, _
   ByVal sourceColumn As String _
)

Syntax: C#

public MySqlParameter(
stringparameterName,
MySqlDbTypedbType,
intsize,
stringsourceColumn
);

Parameters

  • parameterName: The name of the parameter to map.

  • dbType: One of the MySqlDbType values.

  • size: The length of the parameter.

  • sourceColumn: The name of the source column.

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace , MySqlParameter Constructor Overload List

23.2.4.1.2.1.7.1.1.3.1.1.1.6. MySqlParameter Constructor

Initializes a new instance of the MySqlParameter class with the parameter name and a value of the new MySqlParameter.

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal parameterName As String, _
   ByVal value As Object _
)

Syntax: C#

public MySqlParameter(
stringparameterName,
objectvalue
);

Parameters

  • parameterName: The name of the parameter to map.

  • value: An Objectthat is the value of the MySqlParameter .

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace , MySqlParameter Constructor Overload List

23.2.4.1.2.1.7.1.1.3.1.1.2. DbType Property

Gets or sets the DbTypeof the parameter.

Syntax: Visual Basic

NotOverridable Public Property DbType As DbType _
_
  Implements IDataParameter.DbType

Syntax: C#

public System.Data.DbType DbType {get; set;}

Implements

IDataParameter.DbType

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.3. Direction Property

Gets or sets a value indicating whether the parameter is input-only, output-only, bidirectional, or a stored procedure return value parameter. As of MySQL version 4.1 and earlier, input-only is the only valid choice.

Syntax: Visual Basic

NotOverridable Public Property Direction As ParameterDirection _
_
  Implements IDataParameter.Direction

Syntax: C#

public System.Data.ParameterDirection Direction {get; set;}

Implements

IDataParameter.Direction

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.4. IsNullable Property

Gets or sets a value indicating whether the parameter accepts null values.

Syntax: Visual Basic

NotOverridable Public Property IsNullable As Boolean _
_
  Implements IDataParameter.IsNullable

Syntax: C#

public bool IsNullable {get; set;}

Implements

IDataParameter.IsNullable

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.5. IsUnsigned Property

Syntax: Visual Basic

Public Property IsUnsigned As Boolean

Syntax: C#

public bool IsUnsigned {get; set;}

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.6. MySqlDbType Property

Gets or sets the MySqlDbType of the parameter.

Syntax: Visual Basic

Public Property MySqlDbType As MySqlDbType

Syntax: C#

public MySqlDbType MySqlDbType {get; set;}

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.7. ParameterName Property

Gets or sets the name of the MySqlParameter.

Syntax: Visual Basic

NotOverridable Public Property ParameterName As String _
_
  Implements IDataParameter.ParameterName

Syntax: C#

public string ParameterName {get; set;}

Implements

IDataParameter.ParameterName

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.8. Precision Property

Gets or sets the maximum number of digits used to represent the Value property.

Syntax: Visual Basic

NotOverridable Public Property Precision As Byte _
_
  Implements IDbDataParameter.Precision

Syntax: C#

public byte Precision {get; set;}

Implements

IDbDataParameter.Precision

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.9. Scale Property

Gets or sets the number of decimal places to which Value is resolved.

Syntax: Visual Basic

NotOverridable Public Property Scale As Byte _
_
  Implements IDbDataParameter.Scale

Syntax: C#

public byte Scale {get; set;}

Implements

IDbDataParameter.Scale

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.10. Size Property

Gets or sets the maximum size, in bytes, of the data within the column.

Syntax: Visual Basic

NotOverridable Public Property Size As Integer _
_
  Implements IDbDataParameter.Size

Syntax: C#

public int Size {get; set;}

Implements

IDbDataParameter.Size

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.11. SourceColumn Property

Gets or sets the name of the source column that is mapped to the DataSetand used for loading or returning the Value .

Syntax: Visual Basic

NotOverridable Public Property SourceColumn As String _
_
  Implements IDataParameter.SourceColumn

Syntax: C#

public string SourceColumn {get; set;}

Implements

IDataParameter.SourceColumn

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.12. SourceVersion Property

Gets or sets the DataRowVersionto use when loading Value .

Syntax: Visual Basic

NotOverridable Public Property SourceVersion As DataRowVersion _
_
  Implements IDataParameter.SourceVersion

Syntax: C#

public System.Data.DataRowVersion SourceVersion {get; set;}

Implements

IDataParameter.SourceVersion

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.1.1.13. MySqlParameter.ToString Method

Overridden. Gets a string containing the ParameterName .

Syntax: Visual Basic

Overrides Public Function ToString() As String

Syntax: C#

public override string ToString();

Return Value

See Also

MySqlParameter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.3.2. Item Property (Int32)

Gets the MySqlParameter at the specified index.

Syntax: Visual Basic

Overloads Public Default Property Item( _
   ByVal index As Integer _
) As MySqlParameter

Syntax: C#

public MySqlParameter this[
intindex
] {get; set;}

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Item Overload List

23.2.4.1.2.1.7.1.1.3.3. Item Property (String)

Gets the MySqlParameter with the specified name.

Syntax: Visual Basic

Overloads Public Default Property Item( _
   ByVal name As String _
) As MySqlParameter

Syntax: C#

public MySqlParameter this[
stringname
] {get; set;}

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Item Overload List

23.2.4.1.2.1.7.1.1.4. Add Method

Adds the specified MySqlParameter object to the MySqlParameterCollection .

Overload List

Adds the specified MySqlParameter object to the MySqlParameterCollection .

Adds the specified MySqlParameter object to the MySqlParameterCollection .

Adds a MySqlParameter to the MySqlParameterCollection given the parameter name and the data type.

Adds a MySqlParameter to the MySqlParameterCollection with the parameter name, the data type, and the column length.

Adds a MySqlParameter to the MySqlParameterCollection with the parameter name, the data type, the column length, and the source column name.

Adds a MySqlParameter to the MySqlParameterCollection given the specified parameter name and value.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.4.1. MySqlParameterCollection.Add Method

Adds the specified MySqlParameter object to the MySqlParameterCollection .

Syntax: Visual Basic

Overloads Public Function Add( _
   ByVal value As MySqlParameter _
) As MySqlParameter

Syntax: C#

public MySqlParameter Add(
MySqlParametervalue
);

Parameters

Return Value

The newly added MySqlParameter object.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Add Overload List

23.2.4.1.2.1.7.1.1.4.2. MySqlParameterCollection.Add Method

Adds the specified MySqlParameter object to the MySqlParameterCollection .

Syntax: Visual Basic

NotOverridable Overloads Public Function Add( _
   ByVal value As Object _
) As Integer _
_
  Implements IList.Add

Syntax: C#

public int Add(
objectvalue
);

Parameters

Return Value

The index of the new MySqlParameter object.

Implements

IList.Add

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Add Overload List

23.2.4.1.2.1.7.1.1.4.3. MySqlParameterCollection.Add Method

Adds a MySqlParameter to the MySqlParameterCollection given the parameter name and the data type.

Syntax: Visual Basic

Overloads Public Function Add( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType _
) As MySqlParameter

Syntax: C#

public MySqlParameter Add(
stringparameterName,
MySqlDbTypedbType
);

Parameters

  • parameterName: The name of the parameter.

  • dbType: One of the MySqlDbType values.

Return Value

The newly added MySqlParameter object.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Add Overload List

23.2.4.1.2.1.7.1.1.4.4. MySqlParameterCollection.Add Method

Adds a MySqlParameter to the MySqlParameterCollection with the parameter name, the data type, and the column length.

Syntax: Visual Basic

Overloads Public Function Add( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer _
) As MySqlParameter

Syntax: C#

public MySqlParameter Add(
stringparameterName,
MySqlDbTypedbType,
intsize
);

Parameters

  • parameterName: The name of the parameter.

  • dbType: One of the MySqlDbType values.

  • size: The length of the column.

Return Value

The newly added MySqlParameter object.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Add Overload List

23.2.4.1.2.1.7.1.1.4.5. MySqlParameterCollection.Add Method

Adds a MySqlParameter to the MySqlParameterCollection with the parameter name, the data type, the column length, and the source column name.

Syntax: Visual Basic

Overloads Public Function Add( _
   ByVal parameterName As String, _
   ByVal dbType As MySqlDbType, _
   ByVal size As Integer, _
   ByVal sourceColumn As String _
) As MySqlParameter

Syntax: C#

public MySqlParameter Add(
stringparameterName,
MySqlDbTypedbType,
intsize,
stringsourceColumn
);

Parameters

  • parameterName: The name of the parameter.

  • dbType: One of the MySqlDbType values.

  • size: The length of the column.

  • sourceColumn: The name of the source column.

Return Value

The newly added MySqlParameter object.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Add Overload List

23.2.4.1.2.1.7.1.1.4.6. MySqlParameterCollection.Add Method

Adds a MySqlParameter to the MySqlParameterCollection given the specified parameter name and value.

Syntax: Visual Basic

Overloads Public Function Add( _
   ByVal parameterName As String, _
   ByVal value As Object _
) As MySqlParameter

Syntax: C#

public MySqlParameter Add(
stringparameterName,
objectvalue
);

Parameters

  • parameterName: The name of the parameter.

  • value: The Value of the MySqlParameter to add to the collection.

Return Value

The newly added MySqlParameter object.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Add Overload List

23.2.4.1.2.1.7.1.1.5. MySqlParameterCollection.Clear Method

Removes all items from the collection.

Syntax: Visual Basic

NotOverridable Public Sub Clear() _
_
  Implements IList.Clear

Syntax: C#

public void Clear();

Implements

IList.Clear

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.6. Contains Method

Gets a value indicating whether a MySqlParameter exists in the collection.

Overload List

Gets a value indicating whether a MySqlParameter exists in the collection.

Gets a value indicating whether a MySqlParameter with the specified parameter name exists in the collection.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.6.1. MySqlParameterCollection.Contains Method

Gets a value indicating whether a MySqlParameter exists in the collection.

Syntax: Visual Basic

NotOverridable Overloads Public Function Contains( _
   ByVal value As Object _
) As Boolean _
_
  Implements IList.Contains

Syntax: C#

public bool Contains(
objectvalue
);

Parameters

Return Value

true if the collection contains the MySqlParameter object; otherwise, false.

Implements

IList.Contains

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Contains Overload List

23.2.4.1.2.1.7.1.1.6.2. MySqlParameterCollection.Contains Method

Gets a value indicating whether a MySqlParameter with the specified parameter name exists in the collection.

Syntax: Visual Basic

NotOverridable Overloads Public Function Contains( _
   ByVal name As String _
) As Boolean _
_
  Implements IDataParameterCollection.Contains

Syntax: C#

public bool Contains(
stringname
);

Parameters

Return Value

true if the collection contains the parameter; otherwise, false.

Implements

IDataParameterCollection.Contains

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.Contains Overload List

23.2.4.1.2.1.7.1.1.7. MySqlParameterCollection.CopyTo Method

Copies MySqlParameter objects from the MySqlParameterCollection to the specified array.

Syntax: Visual Basic

NotOverridable Public Sub CopyTo( _
   ByVal array As Array, _
   ByVal index As Integer _
) _
_
  Implements ICollection.CopyTo

Syntax: C#

public void CopyTo(
Arrayarray,
intindex
);

Parameters

  • array:

  • index:

Implements

ICollection.CopyTo

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.8. IndexOf Method

Gets the location of a MySqlParameter in the collection.

Overload List

Gets the location of a MySqlParameter in the collection.

Gets the location of the MySqlParameter in the collection with a specific parameter name.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.8.1. MySqlParameterCollection.IndexOf Method

Gets the location of a MySqlParameter in the collection.

Syntax: Visual Basic

NotOverridable Overloads Public Function IndexOf( _
   ByVal value As Object _
) As Integer _
_
  Implements IList.IndexOf

Syntax: C#

public int IndexOf(
objectvalue
);

Parameters

Return Value

The zero-based location of the MySqlParameter in the collection.

Implements

IList.IndexOf

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.IndexOf Overload List

23.2.4.1.2.1.7.1.1.8.2. MySqlParameterCollection.IndexOf Method

Gets the location of the MySqlParameter in the collection with a specific parameter name.

Syntax: Visual Basic

NotOverridable Overloads Public Function IndexOf( _
   ByVal parameterName As String _
) As Integer _
_
  Implements IDataParameterCollection.IndexOf

Syntax: C#

public int IndexOf(
stringparameterName
);

Parameters

Return Value

The zero-based location of the MySqlParameter in the collection.

Implements

IDataParameterCollection.IndexOf

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.IndexOf Overload List

23.2.4.1.2.1.7.1.1.9. MySqlParameterCollection.Insert Method

Inserts a MySqlParameter into the collection at the specified index.

Syntax: Visual Basic

NotOverridable Public Sub Insert( _
   ByVal index As Integer, _
   ByVal value As Object _
) _
_
  Implements IList.Insert

Syntax: C#

public void Insert(
intindex,
objectvalue
);

Parameters

  • index:

  • value:

Implements

IList.Insert

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.10. MySqlParameterCollection.Remove Method

Removes the specified MySqlParameter from the collection.

Syntax: Visual Basic

NotOverridable Public Sub Remove( _
   ByVal value As Object _
) _
_
  Implements IList.Remove

Syntax: C#

public void Remove(
objectvalue
);

Parameters

  • value:

Implements

IList.Remove

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.11. RemoveAt Method

Removes the specified MySqlParameter from the collection.

Overload List

Removes the specified MySqlParameter from the collection using a specific index.

Removes the specified MySqlParameter from the collection using the parameter name.

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.7.1.1.11.1. MySqlParameterCollection.RemoveAt Method

Removes the specified MySqlParameter from the collection using a specific index.

Syntax: Visual Basic

NotOverridable Overloads Public Sub RemoveAt( _
   ByVal index As Integer _
) _
_
  Implements IList.RemoveAt

Syntax: C#

public void RemoveAt(
intindex
);

Parameters

  • index: The zero-based index of the parameter.

Implements

IList.RemoveAt

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.RemoveAt Overload List

23.2.4.1.2.1.7.1.1.11.2. MySqlParameterCollection.RemoveAt Method

Removes the specified MySqlParameter from the collection using the parameter name.

Syntax: Visual Basic

NotOverridable Overloads Public Sub RemoveAt( _
   ByVal name As String _
) _
_
  Implements IDataParameterCollection.RemoveAt

Syntax: C#

public void RemoveAt(
stringname
);

Parameters

Implements

IDataParameterCollection.RemoveAt

See Also

MySqlParameterCollection Class , MySql.Data.MySqlClient Namespace , MySqlParameterCollection.RemoveAt Overload List

23.2.4.1.2.1.8. Transaction Property

Syntax: Visual Basic

Public Property Transaction As MySqlTransaction

Syntax: C#

public MySqlTransaction Transaction {get; set;}

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.9. UpdatedRowSource Property

Syntax: Visual Basic

NotOverridable Public Property UpdatedRowSource As UpdateRowSource _
_
  Implements IDbCommand.UpdatedRowSource

Syntax: C#

public System.Data.UpdateRowSource UpdatedRowSource {get; set;}

Implements

IDbCommand.UpdatedRowSource

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.10. MySqlCommand.Cancel Method

Attempts to cancel the execution of a MySqlCommand. This operation is not supported.

Syntax: Visual Basic

NotOverridable Public Sub Cancel() _
_
  Implements IDbCommand.Cancel

Syntax: C#

public void Cancel();

Implements

IDbCommand.Cancel

Remarks

Cancelling an executing command is currently not supported on any version of MySQL.

Exceptions

Exception TypeCondition
NotSupportedExceptionThis operation is not supported.

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.11. MySqlCommand.CreateParameter Method

Creates a new instance of a MySqlParameter object.

Syntax: Visual Basic

Public Function CreateParameter() As MySqlParameter

Syntax: C#

public MySqlParameter CreateParameter();

Return Value

A MySqlParameter object.

Remarks

This method is a strongly-typed version of CreateParameter.

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.12. MySqlCommand.ExecuteNonQuery Method

Syntax: Visual Basic

NotOverridable Public Function ExecuteNonQuery() As Integer _
_
  Implements IDbCommand.ExecuteNonQuery

Syntax: C#

public int ExecuteNonQuery();

Implements

IDbCommand.ExecuteNonQuery

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13. ExecuteReader Method

Overload List

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1. MySqlCommand.ExecuteReader Method

Syntax: Visual Basic

Overloads Public Function ExecuteReader() As MySqlDataReader

Syntax: C#

public MySqlDataReader ExecuteReader();

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace , MySqlCommand.ExecuteReader Overload List

23.2.4.1.2.1.13.1.1. MySqlDataReader Class

Provides a means of reading a forward-only stream of rows from a MySQL database. This class cannot be inherited.

For a list of all members of this type, see MySqlDataReader Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlDataReader_
  Inherits MarshalByRefObject_
  Implements IEnumerable, IDataReader, IDisposable, IDataRecord

Syntax: C#

public sealed class MySqlDataReader : MarshalByRefObject, IEnumerable, IDataReader, IDisposable, IDataRecord

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlDataReader Members , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1. MySqlDataReader Members

MySqlDataReader overview

Public Instance Properties

DepthGets a value indicating the depth of nesting for the current row. This method is not supported currently and always returns 0.
FieldCountGets the number of columns in the current row.
HasRowsGets a value indicating whether the MySqlDataReader contains one or more rows.
IsClosedGets a value indicating whether the data reader is closed.
ItemOverloaded. Overloaded. Gets the value of a column in its native format. In C#, this property is the indexer for the MySqlDataReader class.
RecordsAffectedGets the number of rows changed, inserted, or deleted by execution of the SQL statement.

Public Instance Methods

CloseCloses the MySqlDataReader object.
CreateObjRef(inherited from MarshalByRefObject)Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.
Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetBooleanGets the value of the specified column as a Boolean.
GetByteGets the value of the specified column as a byte.
GetBytesReads a stream of bytes from the specified column offset into the buffer an array starting at the given buffer offset.
GetCharGets the value of the specified column as a single character.
GetCharsReads a stream of characters from the specified column offset into the buffer as an array starting at the given buffer offset.
GetDataTypeNameGets the name of the source data type.
GetDateTime 
GetDecimal 
GetDouble 
GetFieldTypeGets the Type that is the data type of the object.
GetFloat 
GetGuid 
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetInt16 
GetInt32 
GetInt64 
GetLifetimeService(inherited from MarshalByRefObject)Retrieves the current lifetime service object that controls the lifetime policy for this instance.
GetMySqlDateTime 
GetNameGets the name of the specified column.
GetOrdinalGets the column ordinal, given the name of the column.
GetSchemaTableReturns a DataTable that describes the column metadata of the MySqlDataReader.
GetString 
GetTimeSpan 
GetType(inherited from Object)Gets the Typeof the current instance.
GetUInt16 
GetUInt32 
GetUInt64 
GetValueGets the value of the specified column in its native format.
GetValuesGets all attribute columns in the collection for the current row.
InitializeLifetimeService(inherited from MarshalByRefObject)Obtains a lifetime service object to control the lifetime policy for this instance.
IsDBNullGets a value indicating whether the column contains non-existent or missing values.
NextResultAdvances the data reader to the next result, when reading the results of batch SQL statements.
ReadAdvances the MySqlDataReader to the next record.
ToString(inherited from Object)Returns a Stringthat represents the current Object.

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.1. Depth Property

Gets a value indicating the depth of nesting for the current row. This method is not supported currently and always returns 0.

Syntax: Visual Basic

NotOverridable Public ReadOnly Property Depth As Integer _
_
  Implements IDataReader.Depth

Syntax: C#

public int Depth {get;}

Implements

IDataReader.Depth

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.2. FieldCount Property

Gets the number of columns in the current row.

Syntax: Visual Basic

NotOverridable Public ReadOnly Property FieldCount As Integer _
_
  Implements IDataRecord.FieldCount

Syntax: C#

public int FieldCount {get;}

Implements

IDataRecord.FieldCount

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.3. HasRows Property

Gets a value indicating whether the MySqlDataReader contains one or more rows.

Syntax: Visual Basic

Public ReadOnly Property HasRows As Boolean

Syntax: C#

public bool HasRows {get;}

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.4. IsClosed Property

Gets a value indicating whether the data reader is closed.

Syntax: Visual Basic

NotOverridable Public ReadOnly Property IsClosed As Boolean _
_
  Implements IDataReader.IsClosed

Syntax: C#

public bool IsClosed {get;}

Implements

IDataReader.IsClosed

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.5. Item Property

Overloaded. Gets the value of a column in its native format. In C#, this property is the indexer for the MySqlDataReader class.

Overload List

Overloaded. Gets the value of a column in its native format. In C#, this property is the indexer for the MySqlDataReader class.

Gets the value of a column in its native format. In C#, this property is the indexer for the MySqlDataReader class.

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.5.1. Item Property (Int32)

Overloaded. Gets the value of a column in its native format. In C#, this property is the indexer for the MySqlDataReader class.

Syntax: Visual Basic

NotOverridable Overloads Public Default ReadOnly Property Item( _
   ByVal i As Integer _
) _
_
  Implements IDataRecord.Item As Object _
_
  Implements IDataRecord.Item

Syntax: C#

public object this[
inti
] {get;}

Implements

IDataRecord.Item

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace , MySqlDataReader.Item Overload List

23.2.4.1.2.1.13.1.1.1.5.2. Item Property (String)

Gets the value of a column in its native format. In C#, this property is the indexer for the MySqlDataReader class.

Syntax: Visual Basic

NotOverridable Overloads Public Default ReadOnly Property Item( _
   ByVal name As String _
) _
_
  Implements IDataRecord.Item As Object _
_
  Implements IDataRecord.Item

Syntax: C#

public object this[
stringname
] {get;}

Implements

IDataRecord.Item

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace , MySqlDataReader.Item Overload List

23.2.4.1.2.1.13.1.1.1.6. RecordsAffected Property

Gets the number of rows changed, inserted, or deleted by execution of the SQL statement.

Syntax: Visual Basic

NotOverridable Public ReadOnly Property RecordsAffected As Integer _
_
  Implements IDataReader.RecordsAffected

Syntax: C#

public int RecordsAffected {get;}

Implements

IDataReader.RecordsAffected

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.7. MySqlDataReader.Close Method

Closes the MySqlDataReader object.

Syntax: Visual Basic

NotOverridable Public Sub Close() _
_
  Implements IDataReader.Close

Syntax: C#

public void Close();

Implements

IDataReader.Close

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.8. MySqlDataReader.GetBoolean Method

Gets the value of the specified column as a Boolean.

Syntax: Visual Basic

NotOverridable Public Function GetBoolean( _
   ByVal i As Integer _
) As Boolean _
_
  Implements IDataRecord.GetBoolean

Syntax: C#

public bool GetBoolean(
inti
);

Parameters

  • i:

Return Value

Implements

IDataRecord.GetBoolean

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.9. MySqlDataReader.GetByte Method

Gets the value of the specified column as a byte.

Syntax: Visual Basic

NotOverridable Public Function GetByte( _
   ByVal i As Integer _
) As Byte _
_
  Implements IDataRecord.GetByte

Syntax: C#

public byte GetByte(
inti
);

Parameters

  • i:

Return Value

Implements

IDataRecord.GetByte

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.10. MySqlDataReader.GetBytes Method

Reads a stream of bytes from the specified column offset into the buffer an array starting at the given buffer offset.

Syntax: Visual Basic

NotOverridable Public Function GetBytes( _
   ByVal i As Integer, _
   ByVal dataIndex As Long, _
   ByVal buffer As Byte(), _
   ByVal bufferIndex As Integer, _
   ByVal length As Integer _
) As Long _
_
  Implements IDataRecord.GetBytes

Syntax: C#

public long GetBytes(
inti,
longdataIndex,
byte[]buffer,
intbufferIndex,
intlength
);

Parameters

  • i: The zero-based column ordinal.

  • dataIndex: The index within the field from which to begin the read operation.

  • buffer: The buffer into which to read the stream of bytes.

  • bufferIndex: The index for buffer to begin the read operation.

  • length: The maximum length to copy into the buffer.

Return Value

The actual number of bytes read.

Implements

IDataRecord.GetBytes

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.11. MySqlDataReader.GetChar Method

Gets the value of the specified column as a single character.

Syntax: Visual Basic

NotOverridable Public Function GetChar( _
   ByVal i As Integer _
) As Char _
_
  Implements IDataRecord.GetChar

Syntax: C#

public char GetChar(
inti
);

Parameters

  • i:

Return Value

Implements

IDataRecord.GetChar

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.12. MySqlDataReader.GetChars Method

Reads a stream of characters from the specified column offset into the buffer as an array starting at the given buffer offset.

Syntax: Visual Basic

NotOverridable Public Function GetChars( _
   ByVal i As Integer, _
   ByVal fieldOffset As Long, _
   ByVal buffer As Char(), _
   ByVal bufferoffset As Integer, _
   ByVal length As Integer _
) As Long _
_
  Implements IDataRecord.GetChars

Syntax: C#

public long GetChars(
inti,
longfieldOffset,
char[]buffer,
intbufferoffset,
intlength
);

Parameters

  • i:

  • fieldOffset:

  • buffer:

  • bufferoffset:

  • length:

Return Value

Implements

IDataRecord.GetChars

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.13. MySqlDataReader.GetDataTypeName Method

Gets the name of the source data type.

Syntax: Visual Basic

NotOverridable Public Function GetDataTypeName( _
   ByVal i As Integer _
) As String _
_
  Implements IDataRecord.GetDataTypeName

Syntax: C#

public string GetDataTypeName(
inti
);

Parameters

  • i:

Return Value

Implements

IDataRecord.GetDataTypeName

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.14. MySqlDataReader.GetDateTime Method

Syntax: Visual Basic

NotOverridable Public Function GetDateTime( _
   ByVal index As Integer _
) As Date _
_
  Implements IDataRecord.GetDateTime

Syntax: C#

public DateTime GetDateTime(
intindex
);

Implements

IDataRecord.GetDateTime

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.15. MySqlDataReader.GetDecimal Method

Syntax: Visual Basic

NotOverridable Public Function GetDecimal( _
   ByVal index As Integer _
) As Decimal _
_
  Implements IDataRecord.GetDecimal

Syntax: C#

public decimal GetDecimal(
intindex
);

Implements

IDataRecord.GetDecimal

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.16. MySqlDataReader.GetDouble Method

Syntax: Visual Basic

NotOverridable Public Function GetDouble( _
   ByVal index As Integer _
) As Double _
_
  Implements IDataRecord.GetDouble

Syntax: C#

public double GetDouble(
intindex
);

Implements

IDataRecord.GetDouble

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.17. MySqlDataReader.GetFieldType Method

Gets the Type that is the data type of the object.

Syntax: Visual Basic

NotOverridable Public Function GetFieldType( _
   ByVal i As Integer _
) As Type _
_
  Implements IDataRecord.GetFieldType

Syntax: C#

public Type GetFieldType(
inti
);

Parameters

  • i:

Return Value

Implements

IDataRecord.GetFieldType

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.18. MySqlDataReader.GetFloat Method

Syntax: Visual Basic

NotOverridable Public Function GetFloat( _
   ByVal index As Integer _
) As Single _
_
  Implements IDataRecord.GetFloat

Syntax: C#

public float GetFloat(
intindex
);

Implements

IDataRecord.GetFloat

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.19. MySqlDataReader.GetGuid Method

Syntax: Visual Basic

NotOverridable Public Function GetGuid( _
   ByVal index As Integer _
) As Guid _
_
  Implements IDataRecord.GetGuid

Syntax: C#

public Guid GetGuid(
intindex
);

Implements

IDataRecord.GetGuid

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.20. MySqlDataReader.GetInt16 Method

Syntax: Visual Basic

NotOverridable Public Function GetInt16( _
   ByVal index As Integer _
) As Short _
_
  Implements IDataRecord.GetInt16

Syntax: C#

public short GetInt16(
intindex
);

Implements

IDataRecord.GetInt16

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.21. MySqlDataReader.GetInt32 Method

Syntax: Visual Basic

NotOverridable Public Function GetInt32( _
   ByVal index As Integer _
) As Integer _
_
  Implements IDataRecord.GetInt32

Syntax: C#

public int GetInt32(
intindex
);

Implements

IDataRecord.GetInt32

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.22. MySqlDataReader.GetInt64 Method

Syntax: Visual Basic

NotOverridable Public Function GetInt64( _
   ByVal index As Integer _
) As Long _
_
  Implements IDataRecord.GetInt64

Syntax: C#

public long GetInt64(
intindex
);

Implements

IDataRecord.GetInt64

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.23. MySqlDataReader.GetMySqlDateTime Method

Syntax: Visual Basic

Public Function GetMySqlDateTime( _
   ByVal index As Integer _
) As MySqlDateTime

Syntax: C#

public MySqlDateTime GetMySqlDateTime(
intindex
);

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.24. MySqlDataReader.GetName Method

Gets the name of the specified column.

Syntax: Visual Basic

NotOverridable Public Function GetName( _
   ByVal i As Integer _
) As String _
_
  Implements IDataRecord.GetName

Syntax: C#

public string GetName(
inti
);

Parameters

  • i:

Return Value

Implements

IDataRecord.GetName

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.25. MySqlDataReader.GetOrdinal Method

Gets the column ordinal, given the name of the column.

Syntax: Visual Basic

NotOverridable Public Function GetOrdinal( _
   ByVal name As String _
) As Integer _
_
  Implements IDataRecord.GetOrdinal

Syntax: C#

public int GetOrdinal(
stringname
);

Parameters

  • name:

Return Value

Implements

IDataRecord.GetOrdinal

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.26. MySqlDataReader.GetSchemaTable Method

Returns a DataTable that describes the column metadata of the MySqlDataReader.

Syntax: Visual Basic

NotOverridable Public Function GetSchemaTable() As DataTable _
_
  Implements IDataReader.GetSchemaTable

Syntax: C#

public DataTable GetSchemaTable();

Return Value

Implements

IDataReader.GetSchemaTable

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.27. MySqlDataReader.GetString Method

Syntax: Visual Basic

NotOverridable Public Function GetString( _
   ByVal index As Integer _
) As String _
_
  Implements IDataRecord.GetString

Syntax: C#

public string GetString(
intindex
);

Implements

IDataRecord.GetString

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.28. MySqlDataReader.GetTimeSpan Method

Syntax: Visual Basic

Public Function GetTimeSpan( _
   ByVal index As Integer _
) As TimeSpan

Syntax: C#

public TimeSpan GetTimeSpan(
intindex
);

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.29. MySqlDataReader.GetUInt16 Method

Syntax: Visual Basic

Public Function GetUInt16( _
   ByVal index As Integer _
) As UInt16

Syntax: C#

public ushort GetUInt16(
intindex
);

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.30. MySqlDataReader.GetUInt32 Method

Syntax: Visual Basic

Public Function GetUInt32( _
   ByVal index As Integer _
) As UInt32

Syntax: C#

public uint GetUInt32(
intindex
);

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.31. MySqlDataReader.GetUInt64 Method

Syntax: Visual Basic

Public Function GetUInt64( _
   ByVal index As Integer _
) As UInt64

Syntax: C#

public ulong GetUInt64(
intindex
);

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.32. MySqlDataReader.GetValue Method

Gets the value of the specified column in its native format.

Syntax: Visual Basic

NotOverridable Public Function GetValue( _
   ByVal i As Integer _
) As Object _
_
  Implements IDataRecord.GetValue

Syntax: C#

public object GetValue(
inti
);

Parameters

  • i:

Return Value

Implements

IDataRecord.GetValue

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.33. MySqlDataReader.GetValues Method

Gets all attribute columns in the collection for the current row.

Syntax: Visual Basic

NotOverridable Public Function GetValues( _
   ByVal values As Object() _
) As Integer _
_
  Implements IDataRecord.GetValues

Syntax: C#

public int GetValues(
object[]values
);

Parameters

  • values:

Return Value

Implements

IDataRecord.GetValues

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.34. MySqlDataReader.IsDBNull Method

Gets a value indicating whether the column contains non-existent or missing values.

Syntax: Visual Basic

NotOverridable Public Function IsDBNull( _
   ByVal i As Integer _
) As Boolean _
_
  Implements IDataRecord.IsDBNull

Syntax: C#

public bool IsDBNull(
inti
);

Parameters

  • i:

Return Value

Implements

IDataRecord.IsDBNull

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.35. MySqlDataReader.NextResult Method

Advances the data reader to the next result, when reading the results of batch SQL statements.

Syntax: Visual Basic

NotOverridable Public Function NextResult() As Boolean _
_
  Implements IDataReader.NextResult

Syntax: C#

public bool NextResult();

Return Value

Implements

IDataReader.NextResult

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.1.1.1.36. MySqlDataReader.Read Method

Advances the MySqlDataReader to the next record.

Syntax: Visual Basic

NotOverridable Public Function Read() As Boolean _
_
  Implements IDataReader.Read

Syntax: C#

public bool Read();

Return Value

Implements

IDataReader.Read

See Also

MySqlDataReader Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.13.2. MySqlCommand.ExecuteReader Method

Syntax: Visual Basic

Overloads Public Function ExecuteReader( _
   ByVal behavior As CommandBehavior _
) As MySqlDataReader

Syntax: C#

public MySqlDataReader ExecuteReader(
CommandBehaviorbehavior
);

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace , MySqlCommand.ExecuteReader Overload List

23.2.4.1.2.1.14. MySqlCommand.ExecuteScalar Method

Syntax: Visual Basic

NotOverridable Public Function ExecuteScalar() As Object _
_
  Implements IDbCommand.ExecuteScalar

Syntax: C#

public object ExecuteScalar();

Implements

IDbCommand.ExecuteScalar

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.2.1.15. MySqlCommand.Prepare Method

Syntax: Visual Basic

NotOverridable Public Sub Prepare() _
_
  Implements IDbCommand.Prepare

Syntax: C#

public void Prepare();

Implements

IDbCommand.Prepare

See Also

MySqlCommand Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3. MySqlCommandBuilder Class

For a list of all members of this type, see MySqlCommandBuilder Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlCommandBuilder_
  Inherits Component

Syntax: C#

public sealed class MySqlCommandBuilder : Component

Thread Safety

Public static (Sharedin Visual Basic) members of this type are safe for multithreaded operations. Instance members are notguaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlCommandBuilder Members , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1. MySqlCommandBuilder Members

MySqlCommandBuilder overview

Public Static (Shared) Methods

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

Public Instance Constructors

MySqlCommandBuilderOverloaded. Initializes a new instance of the MySqlCommandBuilder class.

Public Instance Properties

Container(inherited from Component)Gets the IContainerthat contains the Component.
DataAdapter 
QuotePrefix 
QuoteSuffix 
Site(inherited from Component)Gets or sets the ISiteof the Component.

Public Instance Methods

CreateObjRef(inherited from MarshalByRefObject)Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.
Dispose(inherited from Component)Releases all resources used by the Component.
Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetDeleteCommand 
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetInsertCommand 
GetLifetimeService(inherited from MarshalByRefObject)Retrieves the current lifetime service object that controls the lifetime policy for this instance.
GetType(inherited from Object)Gets the Typeof the current instance.
GetUpdateCommand 
InitializeLifetimeService(inherited from MarshalByRefObject)Obtains a lifetime service object to control the lifetime policy for this instance.
RefreshSchema 
ToString(inherited from Component)Returns a Stringcontaining the name of the Component, if any. This method should not be overridden.

Public Instance Events

Disposed(inherited from Component)Adds an event handler to listen to the Disposedevent on the component.

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.1. DeriveParameters Method

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.

Overload List

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.

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.1.1. MySqlCommandBuilder.DeriveParameters Method

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.

Syntax: Visual Basic

Overloads Public Shared Sub DeriveParameters( _
   ByVal command As MySqlCommand _
)

Syntax: C#

public static void DeriveParameters(
MySqlCommandcommand
);

Parameters

  • command: The MySqlCommand referencing the stored procedure from which the parameter information is to be derived. The derived parameters are added to the Parameters collection of the MySqlCommand.

Exceptions

Exception TypeCondition
InvalidOperationExceptionThe command text is not a valid stored procedure name.

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace , MySqlCommandBuilder.DeriveParameters Overload List

23.2.4.1.3.1.1.2. MySqlCommandBuilder.DeriveParameters Method

Syntax: Visual Basic

Overloads Public Shared Sub DeriveParameters( _
   ByVal command As MySqlCommand, _
   ByVal useProc As Boolean _
)

Syntax: C#

public static void DeriveParameters(
MySqlCommandcommand,
booluseProc
);

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace , MySqlCommandBuilder.DeriveParameters Overload List

23.2.4.1.3.1.2. MySqlCommandBuilder Constructor

Initializes a new instance of the MySqlCommandBuilder class.

Overload List

Initializes a new instance of the MySqlCommandBuilder class.

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.1. MySqlCommandBuilder Constructor

Initializes a new instance of the MySqlCommandBuilder class.

Syntax: Visual Basic

Overloads Public Sub New()

Syntax: C#

public MySqlCommandBuilder();

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace , MySqlCommandBuilder Constructor Overload List

23.2.4.1.3.1.2.2. MySqlCommandBuilder Constructor

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal adapter As MySqlDataAdapter _
)

Syntax: C#

public MySqlCommandBuilder(
MySqlDataAdapteradapter
);

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace , MySqlCommandBuilder Constructor Overload List

23.2.4.1.3.1.2.2.1. MySqlDataAdapter Class

For a list of all members of this type, see MySqlDataAdapter Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlDataAdapter_
  Inherits DbDataAdapter

Syntax: C#

public sealed class MySqlDataAdapter : DbDataAdapter

Thread Safety

Public static (Sharedin Visual Basic) members of this type are safe for multithreaded operations. Instance members are notguaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlDataAdapter Members , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1. MySqlDataAdapter Members

MySqlDataAdapter overview

Public Instance Constructors

MySqlDataAdapterOverloaded. Initializes a new instance of the MySqlDataAdapter class.

Public Instance Properties

AcceptChangesDuringFill(inherited from DataAdapter)Gets or sets a value indicating whether AcceptChangesis called on a DataRowafter it is added to the DataTableduring any of the Fill operations.
AcceptChangesDuringUpdate(inherited from DataAdapter)Gets or sets whether AcceptChangesis called during a Update.
Container(inherited from Component)Gets the IContainerthat contains the Component.
ContinueUpdateOnError(inherited from DataAdapter)Gets or sets a value that specifies whether to generate an exception when an error is encountered during a row update.
DeleteCommandOverloaded.
FillLoadOption(inherited from DataAdapter)Gets or sets the LoadOptionthat determines how the adapter fills the DataTablefrom the DbDataReader.
InsertCommandOverloaded.
MissingMappingAction(inherited from DataAdapter)Determines the action to take when incoming data does not have a matching table or column.
MissingSchemaAction(inherited from DataAdapter)Determines the action to take when existing DataSetschema does not match incoming data.
ReturnProviderSpecificTypes(inherited from DataAdapter)Gets or sets whether the Fillmethod should return provider-specific values or common CLS-compliant values.
SelectCommandOverloaded.
Site(inherited from Component)Gets or sets the ISiteof the Component.
TableMappings(inherited from DataAdapter)Gets a collection that provides the master mapping between a source table and a DataTable.
UpdateBatchSize(inherited from DbDataAdapter)Gets or sets a value that enables or disables batch processing support, and specifies the number of commands that can be executed in a batch.
UpdateCommandOverloaded.

Public Instance Methods

CreateObjRef(inherited from MarshalByRefObject)Creates an object that contains all the relevant information required to generate a proxy used to communicate with a remote object.
Dispose(inherited from Component)Releases all resources used by the Component.
Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
Fill(inherited from DbDataAdapter)Overloaded. Adds or refreshes rows in the DataSetto match those in the data source using the DataSetname, and creates a DataTablenamed "Table."
FillSchema(inherited from DbDataAdapter)Overloaded. Configures the schema of the specified DataTablebased on the specified SchemaType.
GetFillParameters(inherited from DbDataAdapter)Gets the parameters set by the user when executing an SQL SELECT statement.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetLifetimeService(inherited from MarshalByRefObject)Retrieves the current lifetime service object that controls the lifetime policy for this instance.
GetType(inherited from Object)Gets the Typeof the current instance.
InitializeLifetimeService(inherited from MarshalByRefObject)Obtains a lifetime service object to control the lifetime policy for this instance.
ResetFillLoadOption(inherited from DataAdapter)Resets FillLoadOptionto its default state and causes Fillto honor AcceptChangesDuringFill.
ShouldSerializeAcceptChangesDuringFill(inherited from DataAdapter)Determines whether the AcceptChangesDuringFillproperty should be persisted.
ShouldSerializeFillLoadOption(inherited from DataAdapter)Determines whether the FillLoadOptionproperty should be persisted.
ToString(inherited from Component)Returns a Stringcontaining the name of the Component, if any. This method should not be overridden.
Update(inherited from DbDataAdapter)Overloaded. Calls the respective INSERT, UPDATE, or DELETE statements for each inserted, updated, or deleted row in the specified DataSet.

Public Instance Events

Disposed(inherited from Component)Adds an event handler to listen to the Disposedevent on the component.
FillError(inherited from DataAdapter)Returned when an error occurs during a fill operation.
RowUpdatedOccurs during Update after a command is executed against the data source. The attempt to update is made, so the event fires.
RowUpdatingOccurs during Update before a command is executed against the data source. The attempt to update is made, so the event fires.

Protected Internal Instance Properties

FillCommandBehavior(inherited from DbDataAdapter)Gets or sets the behavior of the command used to fill the data adapter.

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.1. MySqlDataAdapter Constructor

Initializes a new instance of the MySqlDataAdapter class.

Overload List

Initializes a new instance of the MySqlDataAdapter class.

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.1.1. MySqlDataAdapter Constructor

Initializes a new instance of the MySqlDataAdapter class.

Syntax: Visual Basic

Overloads Public Sub New()

Syntax: C#

public MySqlDataAdapter();

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace , MySqlDataAdapter Constructor Overload List

23.2.4.1.3.1.2.2.1.1.1.2. MySqlDataAdapter Constructor

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal selectCommand As MySqlCommand _
)

Syntax: C#

public MySqlDataAdapter(
MySqlCommandselectCommand
);

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace , MySqlDataAdapter Constructor Overload List

23.2.4.1.3.1.2.2.1.1.1.3. MySqlDataAdapter Constructor

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal selectCommandText As String, _
   ByVal connection As MySqlConnection _
)

Syntax: C#

public MySqlDataAdapter(
stringselectCommandText,
MySqlConnectionconnection
);

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace , MySqlDataAdapter Constructor Overload List

23.2.4.1.3.1.2.2.1.1.1.4. MySqlDataAdapter Constructor

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal selectCommandText As String, _
   ByVal selectConnString As String _
)

Syntax: C#

public MySqlDataAdapter(
stringselectCommandText,
stringselectConnString
);

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace , MySqlDataAdapter Constructor Overload List

23.2.4.1.3.1.2.2.1.1.2. DeleteCommand Property

Syntax: Visual Basic

Overloads Public Property DeleteCommand As MySqlCommand

Syntax: C#

new public MySqlCommand DeleteCommand {get; set;}

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.3. InsertCommand Property

Syntax: Visual Basic

Overloads Public Property InsertCommand As MySqlCommand

Syntax: C#

new public MySqlCommand InsertCommand {get; set;}

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.4. SelectCommand Property

Syntax: Visual Basic

Overloads Public Property SelectCommand As MySqlCommand

Syntax: C#

new public MySqlCommand SelectCommand {get; set;}

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.5. UpdateCommand Property

Syntax: Visual Basic

Overloads Public Property UpdateCommand As MySqlCommand

Syntax: C#

new public MySqlCommand UpdateCommand {get; set;}

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.6. MySqlDataAdapter.RowUpdated Event

Occurs during Update after a command is executed against the data source. The attempt to update is made, so the event fires.

Syntax: Visual Basic

Public Event RowUpdated As MySqlRowUpdatedEventHandler

Syntax: C#

public event MySqlRowUpdatedEventHandler RowUpdated;

Event Data

The event handler receives an argument of type MySqlRowUpdatedEventArgs containing data related to this event. The following MySqlRowUpdatedEventArgsproperties provide information specific to this event.

PropertyDescription
CommandGets or sets the MySqlCommand executed when Update is called.
ErrorsGets any errors generated by the .NET Framework data provider when the Commandwas executed.
RecordsAffectedGets the number of rows changed, inserted, or deleted by execution of the SQL statement.
RowGets the DataRowsent through an Update.
RowCountGets the number of rows processed in a batch of updated records.
StatementTypeGets the type of SQL statement executed.
StatusGets the UpdateStatusof the Commandproperty.
TableMappingGets the DataTableMappingsent through an Update.

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.6.1. MySqlRowUpdatedEventHandler Delegate

Represents the method that will handle the RowUpdatedevent of a MySqlDataAdapter .

Syntax: Visual Basic

Public Delegate Sub MySqlRowUpdatedEventHandler( _
   ByVal sender As Object, _
   ByVal e As MySqlRowUpdatedEventArgs _
)

Syntax: C#

public delegate void MySqlRowUpdatedEventHandler(
objectsender,
MySqlRowUpdatedEventArgse
);

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.6.1.1. MySqlRowUpdatedEventArgs Class

Provides data for the RowUpdated event. This class cannot be inherited.

For a list of all members of this type, see MySqlRowUpdatedEventArgs Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlRowUpdatedEventArgs_
  Inherits RowUpdatedEventArgs

Syntax: C#

public sealed class MySqlRowUpdatedEventArgs : RowUpdatedEventArgs

Thread Safety

Public static (Sharedin Visual Basic) members of this type are safe for multithreaded operations. Instance members are notguaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlRowUpdatedEventArgs Members , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.6.1.1.1. MySqlRowUpdatedEventArgs Members

MySqlRowUpdatedEventArgs overview

Public Instance Constructors

MySqlRowUpdatedEventArgs ConstructorInitializes a new instance of the MySqlRowUpdatedEventArgs class.

Public Instance Properties

CommandOverloaded. Gets or sets the MySqlCommand executed when Update is called.
Errors(inherited from RowUpdatedEventArgs)Gets any errors generated by the .NET Framework data provider when the Commandwas executed.
RecordsAffected(inherited from RowUpdatedEventArgs)Gets the number of rows changed, inserted, or deleted by execution of the SQL statement.
Row(inherited from RowUpdatedEventArgs)Gets the DataRowsent through an Update.
RowCount(inherited from RowUpdatedEventArgs)Gets the number of rows processed in a batch of updated records.
StatementType(inherited from RowUpdatedEventArgs)Gets the type of SQL statement executed.
Status(inherited from RowUpdatedEventArgs)Gets the UpdateStatusof the Commandproperty.
TableMapping(inherited from RowUpdatedEventArgs)Gets the DataTableMappingsent through an Update.

Public Instance Methods

CopyToRows(inherited from RowUpdatedEventArgs)Overloaded. Copies references to the modified rows into the provided array.
Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetType(inherited from Object)Gets the Typeof the current instance.
ToString(inherited from Object)Returns a Stringthat represents the current Object.

See Also

MySqlRowUpdatedEventArgs Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.6.1.1.1.1. MySqlRowUpdatedEventArgs Constructor

Initializes a new instance of the MySqlRowUpdatedEventArgs class.

Syntax: Visual Basic

Public Sub New( _
   ByVal row As DataRow, _
   ByVal command As IDbCommand, _
   ByVal statementType As StatementType, _
   ByVal tableMapping As DataTableMapping _
)

Syntax: C#

public MySqlRowUpdatedEventArgs(
DataRowrow,
IDbCommandcommand,
StatementTypestatementType,
DataTableMappingtableMapping
);

Parameters

  • row: The DataRowsent through an Update.

  • command: The IDbCommandexecuted when Updateis called.

  • statementType: One of the StatementTypevalues that specifies the type of query executed.

  • tableMapping: The DataTableMappingsent through an Update.

See Also

MySqlRowUpdatedEventArgs Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.6.1.1.1.2. Command Property

Gets or sets the MySqlCommand executed when Update is called.

Syntax: Visual Basic

Overloads Public ReadOnly Property Command As MySqlCommand

Syntax: C#

new public MySqlCommand Command {get;}

See Also

MySqlRowUpdatedEventArgs Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.7. MySqlDataAdapter.RowUpdating Event

Occurs during Update before a command is executed against the data source. The attempt to update is made, so the event fires.

Syntax: Visual Basic

Public Event RowUpdating As MySqlRowUpdatingEventHandler

Syntax: C#

public event MySqlRowUpdatingEventHandler RowUpdating;

Event Data

The event handler receives an argument of type MySqlRowUpdatingEventArgs containing data related to this event. The following MySqlRowUpdatingEventArgsproperties provide information specific to this event.

PropertyDescription
CommandGets or sets the MySqlCommand to execute when performing the Update.
ErrorsGets any errors generated by the .NET Framework data provider when the Commandexecutes.
RowGets the DataRowthat will be sent to the server as part of an insert, update, or delete operation.
StatementTypeGets the type of SQL statement to execute.
StatusGets or sets the UpdateStatusof the Commandproperty.
TableMappingGets the DataTableMappingto send through the Update.

See Also

MySqlDataAdapter Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.7.1. MySqlRowUpdatingEventHandler Delegate

Represents the method that will handle the RowUpdatingevent of a MySqlDataAdapter .

Syntax: Visual Basic

Public Delegate Sub MySqlRowUpdatingEventHandler( _
   ByVal sender As Object, _
   ByVal e As MySqlRowUpdatingEventArgs _
)

Syntax: C#

public delegate void MySqlRowUpdatingEventHandler(
objectsender,
MySqlRowUpdatingEventArgse
);

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.7.1.1. MySqlRowUpdatingEventArgs Class

Provides data for the RowUpdating event. This class cannot be inherited.

For a list of all members of this type, see MySqlRowUpdatingEventArgs Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlRowUpdatingEventArgs_
  Inherits RowUpdatingEventArgs

Syntax: C#

public sealed class MySqlRowUpdatingEventArgs : RowUpdatingEventArgs

Thread Safety

Public static (Sharedin Visual Basic) members of this type are safe for multithreaded operations. Instance members are notguaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlRowUpdatingEventArgs Members , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.7.1.1.1. MySqlRowUpdatingEventArgs Members

MySqlRowUpdatingEventArgs overview

Public Instance Constructors

MySqlRowUpdatingEventArgs ConstructorInitializes a new instance of the MySqlRowUpdatingEventArgs class.

Public Instance Properties

CommandOverloaded. Gets or sets the MySqlCommand to execute when performing the Update.
Errors(inherited from RowUpdatingEventArgs)Gets any errors generated by the .NET Framework data provider when the Commandexecutes.
Row(inherited from RowUpdatingEventArgs)Gets the DataRowthat will be sent to the server as part of an insert, update, or delete operation.
StatementType(inherited from RowUpdatingEventArgs)Gets the type of SQL statement to execute.
Status(inherited from RowUpdatingEventArgs)Gets or sets the UpdateStatusof the Commandproperty.
TableMapping(inherited from RowUpdatingEventArgs)Gets the DataTableMappingto send through the Update.

Public Instance Methods

Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetType(inherited from Object)Gets the Typeof the current instance.
ToString(inherited from Object)Returns a Stringthat represents the current Object.

See Also

MySqlRowUpdatingEventArgs Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.7.1.1.1.1. MySqlRowUpdatingEventArgs Constructor

Initializes a new instance of the MySqlRowUpdatingEventArgs class.

Syntax: Visual Basic

Public Sub New( _
   ByVal row As DataRow, _
   ByVal command As IDbCommand, _
   ByVal statementType As StatementType, _
   ByVal tableMapping As DataTableMapping _
)

Syntax: C#

public MySqlRowUpdatingEventArgs(
DataRowrow,
IDbCommandcommand,
StatementTypestatementType,
DataTableMappingtableMapping
);

Parameters

  • row: The DataRowto Update.

  • command: The IDbCommandto execute during Update.

  • statementType: One of the StatementTypevalues that specifies the type of query executed.

  • tableMapping: The DataTableMappingsent through an Update.

See Also

MySqlRowUpdatingEventArgs Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.2.1.1.7.1.1.1.2. Command Property

Gets or sets the MySqlCommand to execute when performing the Update.

Syntax: Visual Basic

Overloads Public Property Command As MySqlCommand

Syntax: C#

new public MySqlCommand Command {get; set;}

See Also

MySqlRowUpdatingEventArgs Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.2.3. MySqlCommandBuilder Constructor

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal adapter As MySqlDataAdapter, _
   ByVal lastOneWins As Boolean _
)

Syntax: C#

public MySqlCommandBuilder(
MySqlDataAdapteradapter,
boollastOneWins
);

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace , MySqlCommandBuilder Constructor Overload List

23.2.4.1.3.1.2.4. MySqlCommandBuilder Constructor

Syntax: Visual Basic

Overloads Public Sub New( _
   ByVal lastOneWins As Boolean _
)

Syntax: C#

public MySqlCommandBuilder(
boollastOneWins
);

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace , MySqlCommandBuilder Constructor Overload List

23.2.4.1.3.1.3. DataAdapter Property

Syntax: Visual Basic

Public Property DataAdapter As MySqlDataAdapter

Syntax: C#

public MySqlDataAdapter DataAdapter {get; set;}

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.4. QuotePrefix Property

Syntax: Visual Basic

Public Property QuotePrefix As String

Syntax: C#

public string QuotePrefix {get; set;}

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.5. QuoteSuffix Property

Syntax: Visual Basic

Public Property QuoteSuffix As String

Syntax: C#

public string QuoteSuffix {get; set;}

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.6. MySqlCommandBuilder.GetDeleteCommand Method

Syntax: Visual Basic

Public Function GetDeleteCommand() As MySqlCommand

Syntax: C#

public MySqlCommand GetDeleteCommand();

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.7. MySqlCommandBuilder.GetInsertCommand Method

Syntax: Visual Basic

Public Function GetInsertCommand() As MySqlCommand

Syntax: C#

public MySqlCommand GetInsertCommand();

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.8. MySqlCommandBuilder.GetUpdateCommand Method

Syntax: Visual Basic

Public Function GetUpdateCommand() As MySqlCommand

Syntax: C#

public MySqlCommand GetUpdateCommand();

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.3.1.9. MySqlCommandBuilder.RefreshSchema Method

Syntax: Visual Basic

Public Sub RefreshSchema()

Syntax: C#

public void RefreshSchema();

See Also

MySqlCommandBuilder Class , MySql.Data.MySqlClient Namespace

23.2.4.1.4. MySqlException Class

The exception that is thrown when MySQL returns an error. This class cannot be inherited.

For a list of all members of this type, see MySqlException Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlException_
  Inherits SystemException

Syntax: C#

public sealed class MySqlException : SystemException

Thread Safety

Public static (Sharedin Visual Basic) members of this type are safe for multithreaded operations. Instance members are notguaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlException Members , MySql.Data.MySqlClient Namespace

23.2.4.1.4.1. MySqlException Members

MySqlException overview

Public Instance Properties

Data(inherited from Exception)Gets a collection of key/value pairs that provide additional, user-defined information about the exception.
HelpLink(inherited from Exception)Gets or sets a link to the help file associated with this exception.
InnerException(inherited from Exception)Gets the Exceptioninstance that caused the current exception.
Message(inherited from Exception)Gets a message that describes the current exception.
NumberGets a number that identifies the type of error.
Source(inherited from Exception)Gets or sets the name of the application or the object that causes the error.
StackTrace(inherited from Exception)Gets a string representation of the frames on the call stack at the time the current exception was thrown.
TargetSite(inherited from Exception)Gets the method that throws the current exception.

Public Instance Methods

Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetBaseException(inherited from Exception)When overridden in a derived class, returns the Exceptionthat is the root cause of one or more subsequent exceptions.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetObjectData(inherited from Exception)When overridden in a derived class, sets the SerializationInfowith information about the exception.
GetType(inherited from Exception)Gets the runtime type of the current instance.
ToString(inherited from Exception)Creates and returns a string representation of the current exception.

See Also

MySqlException Class , MySql.Data.MySqlClient Namespace

23.2.4.1.4.1.1. Number Property

Gets a number that identifies the type of error.

Syntax: Visual Basic

Public ReadOnly Property Number As Integer

Syntax: C#

public int Number {get;}

See Also

MySqlException Class , MySql.Data.MySqlClient Namespace

23.2.4.1.5. MySqlHelper Class

Helper class that makes it easier to work with the provider.

For a list of all members of this type, see MySqlHelper Members .

Syntax: Visual Basic

NotInheritable Public Class MySqlHelper

Syntax: C#

public sealed class MySqlHelper

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlHelper Members , MySql.Data.MySqlClient Namespace

23.2.4.1.5.1. MySqlHelper Members

MySqlHelper overview

Public Static (Shared) Methods

ExecuteDataRowExecutes a single SQL command and returns the first row of the resultset. A new MySqlConnection object is created, opened, and closed during this method.
ExecuteDatasetOverloaded. Executes a single SQL command and returns the resultset in a DataSet. A new MySqlConnection object is created, opened, and closed during this method.
ExecuteNonQueryOverloaded. Executes a single command against a MySQL database. The MySqlConnection is assumed to be open when the method is called and remains open after the method completes.
ExecuteReaderOverloaded. Executes a single command against a MySQL database.
ExecuteScalarOverloaded. Execute a single command against a MySQL database.
UpdateDataSetUpdates the given table with data from the given DataSet

Public Instance Methods

Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetType(inherited from Object)Gets the Typeof the current instance.
ToString(inherited from Object)Returns a Stringthat represents the current Object.

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace

23.2.4.1.5.1.1. MySqlHelper.ExecuteDataRow Method

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.

Syntax: Visual Basic

Public Shared Function ExecuteDataRow( _
   ByVal connectionString As String, _
   ByVal commandText As String, _
   ParamArray parms As MySqlParameter() _
) As DataRow

Syntax: C#

public static DataRow ExecuteDataRow(
stringconnectionString,
stringcommandText,
   params MySqlParameter[]parms
);

Parameters

  • connectionString: Settings to be used for the connection

  • commandText: Command to execute

  • parms: Parameters to use for the command

Return Value

DataRow containing the first row of the resultset

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace

23.2.4.1.5.1.2. ExecuteDataset Method

Executes a single SQL command and returns the resultset in a DataSet. The state of the MySqlConnection object remains unchanged after execution of this method.

Overload List

Executes a single SQL command and returns the resultset in a DataSet. The state of the MySqlConnection object remains unchanged after execution of this method.

Executes a single SQL command and returns the resultset in a DataSet. The state of the MySqlConnection object remains unchanged after execution of this method.

Executes a single SQL command and returns the resultset in a DataSet. A new MySqlConnection object is created, opened, and closed during this method.

Executes a single SQL command and returns the resultset in a DataSet. A new MySqlConnection object is created, opened, and closed during this method.

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace

23.2.4.1.5.1.2.1. MySqlHelper.ExecuteDataset Method

Executes a single SQL command and returns the resultset in a DataSet. The state of the MySqlConnection object remains unchanged after execution of this method.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteDataset( _
   ByVal connection As MySqlConnection, _
   ByVal commandText As String _
) As DataSet

Syntax: C#

public static DataSet ExecuteDataset(
MySqlConnectionconnection,
stringcommandText
);

Parameters

Return Value

DataSetcontaining the resultset

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteDataset Overload List

23.2.4.1.5.1.2.2. MySqlHelper.ExecuteDataset Method

Executes a single SQL command and returns the resultset in a DataSet. The state of the MySqlConnection object remains unchanged after execution of this method.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteDataset( _
   ByVal connection As MySqlConnection, _
   ByVal commandText As String, _
   ParamArray commandParameters As MySqlParameter() _
) As DataSet

Syntax: C#

public static DataSet ExecuteDataset(
MySqlConnectionconnection,
stringcommandText,
   params MySqlParameter[]commandParameters
);

Parameters

  • connection: MySqlConnection object to use

  • commandText: Command to execute

  • commandParameters: Parameters to use for the command

Return Value

DataSetcontaining the resultset

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteDataset Overload List

23.2.4.1.5.1.2.3. MySqlHelper.ExecuteDataset Method

Executes a single SQL command and returns the resultset in a DataSet. A new MySqlConnection object is created, opened, and closed during this method.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteDataset( _
   ByVal connectionString As String, _
   ByVal commandText As String _
) As DataSet

Syntax: C#

public static DataSet ExecuteDataset(
stringconnectionString,
stringcommandText
);

Parameters

  • connectionString: Settings to be used for the connection

  • commandText: Command to execute

Return Value

DataSetcontaining the resultset

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteDataset Overload List

23.2.4.1.5.1.2.4. MySqlHelper.ExecuteDataset Method

Executes a single SQL command and returns the resultset in a DataSet. A new MySqlConnection object is created, opened, and closed during this method.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteDataset( _
   ByVal connectionString As String, _
   ByVal commandText As String, _
   ParamArray commandParameters As MySqlParameter() _
) As DataSet

Syntax: C#

public static DataSet ExecuteDataset(
stringconnectionString,
stringcommandText,
   params MySqlParameter[]commandParameters
);

Parameters

  • connectionString: Settings to be used for the connection

  • commandText: Command to execute

  • commandParameters: Parameters to use for the command

Return Value

DataSetcontaining the resultset

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteDataset Overload List

23.2.4.1.5.1.3. ExecuteNonQuery Method

Executes a single command against a MySQL database. The MySqlConnection is assumed to be open when the method is called and remains open after the method completes.

Overload List

Executes a single command against a MySQL database. The MySqlConnection is assumed to be open when the method is called and remains open after the method completes.

Executes a single command against a MySQL database. A new MySqlConnection is created using the ConnectionString given.

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace

23.2.4.1.5.1.3.1. MySqlHelper.ExecuteNonQuery Method

Executes a single command against a MySQL database. The MySqlConnection is assumed to be open when the method is called and remains open after the method completes.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteNonQuery( _
   ByVal connection As MySqlConnection, _
   ByVal commandText As String, _
   ParamArray commandParameters As MySqlParameter() _
) As Integer

Syntax: C#

public static int ExecuteNonQuery(
MySqlConnectionconnection,
stringcommandText,
   params MySqlParameter[]commandParameters
);

Parameters

  • connection: MySqlConnection object to use

  • commandText: SQL command to be executed

  • commandParameters: Array of MySqlParameter objects to use with the command.

Return Value

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteNonQuery Overload List

23.2.4.1.5.1.3.2. MySqlHelper.ExecuteNonQuery Method

Executes a single command against a MySQL database. A new MySqlConnection is created using the ConnectionString given.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteNonQuery( _
   ByVal connectionString As String, _
   ByVal commandText As String, _
   ParamArray parms As MySqlParameter() _
) As Integer

Syntax: C#

public static int ExecuteNonQuery(
stringconnectionString,
stringcommandText,
   params MySqlParameter[]parms
);

Parameters

Return Value

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteNonQuery Overload List

23.2.4.1.5.1.4. ExecuteReader Method

Executes a single command against a MySQL database.

Overload List

Executes a single command against a MySQL database.

Executes a single command against a MySQL database.

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace

23.2.4.1.5.1.4.1. MySqlHelper.ExecuteReader Method

Executes a single command against a MySQL database.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteReader( _
   ByVal connectionString As String, _
   ByVal commandText As String _
) As MySqlDataReader

Syntax: C#

public static MySqlDataReader ExecuteReader(
stringconnectionString,
stringcommandText
);

Parameters

  • connectionString: Settings to use for this command

  • commandText: Command text to use

Return Value

MySqlDataReader object ready to read the results of the command

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteReader Overload List

23.2.4.1.5.1.4.2. MySqlHelper.ExecuteReader Method

Executes a single command against a MySQL database.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteReader( _
   ByVal connectionString As String, _
   ByVal commandText As String, _
   ParamArray commandParameters As MySqlParameter() _
) As MySqlDataReader

Syntax: C#

public static MySqlDataReader ExecuteReader(
stringconnectionString,
stringcommandText,
   params MySqlParameter[]commandParameters
);

Parameters

  • connectionString: Settings to use for this command

  • commandText: Command text to use

  • commandParameters: Array of MySqlParameter objects to use with the command

Return Value

MySqlDataReader object ready to read the results of the command

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteReader Overload List

23.2.4.1.5.1.5. ExecuteScalar Method

Execute a single command against a MySQL database.

Overload List

Execute a single command against a MySQL database.

Execute a single command against a MySQL database.

Execute a single command against a MySQL database.

Execute a single command against a MySQL database.

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace

23.2.4.1.5.1.5.1. MySqlHelper.ExecuteScalar Method

Execute a single command against a MySQL database.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteScalar( _
   ByVal connection As MySqlConnection, _
   ByVal commandText As String _
) As Object

Syntax: C#

public static object ExecuteScalar(
MySqlConnectionconnection,
stringcommandText
);

Parameters

  • connection: MySqlConnection object to use

  • commandText: Command text to use for the command

Return Value

The first column of the first row in the result set, or a null reference if the result set is empty.

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteScalar Overload List

23.2.4.1.5.1.5.2. MySqlHelper.ExecuteScalar Method

Execute a single command against a MySQL database.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteScalar( _
   ByVal connection As MySqlConnection, _
   ByVal commandText As String, _
   ParamArray commandParameters As MySqlParameter() _
) As Object

Syntax: C#

public static object ExecuteScalar(
MySqlConnectionconnection,
stringcommandText,
   params MySqlParameter[]commandParameters
);

Parameters

  • connection: MySqlConnection object to use

  • commandText: Command text to use for the command

  • commandParameters: Parameters to use for the command

Return Value

The first column of the first row in the result set, or a null reference if the result set is empty.

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteScalar Overload List

23.2.4.1.5.1.5.3. MySqlHelper.ExecuteScalar Method

Execute a single command against a MySQL database.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteScalar( _
   ByVal connectionString As String, _
   ByVal commandText As String _
) As Object

Syntax: C#

public static object ExecuteScalar(
stringconnectionString,
stringcommandText
);

Parameters

  • connectionString: Settings to use for the update

  • commandText: Command text to use for the update

Return Value

The first column of the first row in the result set, or a null reference if the result set is empty.

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteScalar Overload List

23.2.4.1.5.1.5.4. MySqlHelper.ExecuteScalar Method

Execute a single command against a MySQL database.

Syntax: Visual Basic

Overloads Public Shared Function ExecuteScalar( _
   ByVal connectionString As String, _
   ByVal commandText As String, _
   ParamArray commandParameters As MySqlParameter() _
) As Object

Syntax: C#

public static object ExecuteScalar(
stringconnectionString,
stringcommandText,
   params MySqlParameter[]commandParameters
);

Parameters

  • connectionString: Settings to use for the command

  • commandText: Command text to use for the command

  • commandParameters: Parameters to use for the command

Return Value

The first column of the first row in the result set, or a null reference if the result set is empty.

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace , MySqlHelper.ExecuteScalar Overload List

23.2.4.1.5.1.6. MySqlHelper.UpdateDataSet Method

Updates the given table with data from the given DataSet

Syntax: Visual Basic

Public Shared Sub UpdateDataSet( _
   ByVal connectionString As String, _
   ByVal commandText As String, _
   ByVal ds As DataSet, _
   ByVal tablename As String _
)

Syntax: C#

public static void UpdateDataSet(
stringconnectionString,
stringcommandText,
DataSetds,
stringtablename
);

Parameters

  • connectionString: Settings to use for the update

  • commandText: Command text to use for the update

  • ds: DataSetcontaining the new data to use in the update

  • tablename: Tablename in the dataset to update

See Also

MySqlHelper Class , MySql.Data.MySqlClient Namespace

23.2.4.1.6. MySqlErrorCode Enumeration

Syntax: Visual Basic

Public Enum MySqlErrorCode

Syntax: C#

public enum MySqlErrorCode

Members

Member NameDescription
PacketTooLarge 
PasswordNotAllowed 
DuplicateKeyEntry 
HostNotPrivileged 
PasswordNoMatch 
AnonymousUser 
DuplicateKey 
KeyNotFound 
DuplicateKeyName 

Requirements

Namespace: MySql.Data.MySqlClient

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySql.Data.MySqlClient Namespace

23.2.4.2. MySql.Data.Types

Namespace hierarchy

Classes

ClassDescription
MySqlConversionExceptionSummary description for MySqlConversionException.
MySqlDateTimeSummary description for MySqlDateTime.
MySqlValue 
23.2.4.2.1. MySql.Data.TypesHierarchy

See Also

MySql.Data.Types Namespace

23.2.4.2.2. MySqlConversionException Class

Summary description for MySqlConversionException.

For a list of all members of this type, see MySqlConversionException Members .

Syntax: Visual Basic

Public Class MySqlConversionException_
  Inherits ApplicationException

Syntax: C#

public class MySqlConversionException : ApplicationException

Thread Safety

Public static (Sharedin Visual Basic) members of this type are safe for multithreaded operations. Instance members are notguaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.Types

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlConversionException Members , MySql.Data.Types Namespace

23.2.4.2.2.1. MySqlConversionException Members

MySqlConversionException overview

Public Instance Constructors

Public Instance Properties

Data(inherited from Exception)Gets a collection of key/value pairs that provide additional, user-defined information about the exception.
HelpLink(inherited from Exception)Gets or sets a link to the help file associated with this exception.
InnerException(inherited from Exception)Gets the Exceptioninstance that caused the current exception.
Message(inherited from Exception)Gets a message that describes the current exception.
Source(inherited from Exception)Gets or sets the name of the application or the object that causes the error.
StackTrace(inherited from Exception)Gets a string representation of the frames on the call stack at the time the current exception was thrown.
TargetSite(inherited from Exception)Gets the method that throws the current exception.

Public Instance Methods

Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetBaseException(inherited from Exception)When overridden in a derived class, returns the Exceptionthat is the root cause of one or more subsequent exceptions.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetObjectData(inherited from Exception)When overridden in a derived class, sets the SerializationInfowith information about the exception.
GetType(inherited from Exception)Gets the runtime type of the current instance.
ToString(inherited from Exception)Creates and returns a string representation of the current exception.

Protected Instance Properties

HResult(inherited from Exception)Gets or sets HRESULT, a coded numerical value that is assigned to a specific exception.

Protected Instance Methods

Finalize(inherited from Object)Allows an Objectto attempt to free resources and perform other cleanup operations before the Objectis reclaimed by garbage collection.
MemberwiseClone(inherited from Object)Creates a shallow copy of the current Object.

See Also

MySqlConversionException Class , MySql.Data.Types Namespace

23.2.4.2.2.1.1. MySqlConversionException Constructor

Syntax: Visual Basic

Public Sub New( _
   ByVal msg As String _
)

Syntax: C#

public MySqlConversionException(
stringmsg
);

See Also

MySqlConversionException Class , MySql.Data.Types Namespace

23.2.4.2.3. MySqlDateTime Class

Summary description for MySqlDateTime.

For a list of all members of this type, see MySqlDateTime Members .

Syntax: Visual Basic

Public Class MySqlDateTime_
  Inherits MySqlValue_
  Implements IConvertible, IComparable

Syntax: C#

public class MySqlDateTime : MySqlValue, IConvertible, IComparable

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.Types

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlDateTime Members , MySql.Data.Types Namespace

23.2.4.2.3.1. MySqlDateTime Members

MySqlDateTime overview

Public Static (Shared) Type Conversions

Public Instance Properties

DayReturns the day portion of this datetime
HourReturns the hour portion of this datetime
IsNull (inherited from MySqlValue) 
IsValidDateTimeIndicates if this object contains a value that can be represented as a DateTime
MinuteReturns the minute portion of this datetime
MonthReturns the month portion of this datetime
SecondReturns the second portion of this datetime
ValueAsObject (inherited from MySqlValue)Returns the value of this field as an object
YearReturns the year portion of this datetime

Public Instance Methods

Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetDateTimeReturns this value as a DateTime
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetType(inherited from Object)Gets the Typeof the current instance.
ToStringReturns a MySQL specific string representation of this value

Protected Instance Fields

classType (inherited from MySqlValue)The system type represented by this value
dbType (inherited from MySqlValue)The generic dbtype of this value
isNull (inherited from MySqlValue)Is this value null
mySqlDbType (inherited from MySqlValue)The specific MySQL db type
mySqlTypeName (inherited from MySqlValue)The MySQL specific typename of this value
objectValue (inherited from MySqlValue) 

Protected Instance Methods

Finalize(inherited from Object)Allows an Objectto attempt to free resources and perform other cleanup operations before the Objectis reclaimed by garbage collection.
MemberwiseClone(inherited from Object)Creates a shallow copy of the current Object.

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.1. MySqlDateTime Explicit MySqlDateTime to DateTime Conversion

Syntax: Visual Basic

MySqlDateTime.op_Explicit(val)

Syntax: C#

public static explicit operator DateTime(
MySqlDateTimeval
);

Parameters

  • val:

Return Value

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.2. Day Property

Returns the day portion of this datetime

Syntax: Visual Basic

Public Property Day As Integer

Syntax: C#

public int Day {get; set;}

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.3. Hour Property

Returns the hour portion of this datetime

Syntax: Visual Basic

Public Property Hour As Integer

Syntax: C#

public int Hour {get; set;}

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4. IsNull Property

Syntax: Visual Basic

Public Property IsNull As Boolean

Syntax: C#

public bool IsNull {get; set;}

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1. MySqlValue Class

For a list of all members of this type, see MySqlValue Members .

Syntax: Visual Basic

MustInherit Public Class MySqlValue

Syntax: C#

public abstract class MySqlValue

Thread Safety

Public static (Shared in Visual Basic) members of this type are safe for multithreaded operations. Instance members are not guaranteed to be thread-safe.

Requirements

Namespace: MySql.Data.Types

Assembly: MySql.Data (in MySql.Data.dll)

See Also

MySqlValue Members , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1. MySqlValue Members

MySqlValue overview

Protected Static (Shared) Fields

Public Instance Constructors

MySqlValue ConstructorInitializes a new instance of the MySqlValue class.

Public Instance Properties

IsNull 
ValueAsObjectReturns the value of this field as an object

Public Instance Methods

Equals(inherited from Object)Determines whether the specified Objectis equal to the current Object.
GetHashCode(inherited from Object)Serves as a hash function for a particular type. GetHashCodeis suitable for use in hashing algorithms and data structures like a hash table.
GetType(inherited from Object)Gets the Typeof the current instance.
ToStringReturns a string representation of this value

Protected Instance Fields

classTypeThe system type represented by this value
dbTypeThe generic dbtype of this value
isNullIs this value null
mySqlDbTypeThe specific MySQL db type
mySqlTypeNameThe MySQL specific typename of this value
objectValue 

Protected Instance Methods

Finalize(inherited from Object)Allows an Objectto attempt to free resources and perform other cleanup operations before the Objectis reclaimed by garbage collection.
MemberwiseClone(inherited from Object)Creates a shallow copy of the current Object.

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1.1. MySqlValue.numberFormat Field

Syntax: Visual Basic

Protected Shared numberFormat As NumberFormatInfo

Syntax: C#

protected static NumberFormatInfo numberFormat;

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1.2. MySqlValue Constructor

Initializes a new instance of the MySqlValue class.

Syntax: Visual Basic

Public Sub New()

Syntax: C#

public MySqlValue();

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1.3. ValueAsObject Property

Returns the value of this field as an object

Syntax: Visual Basic

Public ReadOnly Property ValueAsObject As Object

Syntax: C#

public object ValueAsObject {get;}

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1.4. MySqlValue.ToString Method

Returns a string representation of this value

Syntax: Visual Basic

Overrides Public Function ToString() As String

Syntax: C#

public override string ToString();

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1.5. MySqlValue.classType Field

The system type represented by this value

Syntax: Visual Basic

Protected classType As Type

Syntax: C#

protected Type classType;

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1.6. MySqlValue.dbType Field

The generic dbtype of this value

Syntax: Visual Basic

Protected dbType As DbType

Syntax: C#

protected DbType dbType;

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1.7. MySqlValue.mySqlDbType Field

The specific MySQL db type

Syntax: Visual Basic

Protected mySqlDbType As MySqlDbType

Syntax: C#

protected MySqlDbType mySqlDbType;

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1.8. MySqlValue.mySqlTypeName Field

The MySQL specific typename of this value

Syntax: Visual Basic

Protected mySqlTypeName As String

Syntax: C#

protected string mySqlTypeName;

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.4.1.1.9. MySqlValue.objectValue Field

Syntax: Visual Basic

Protected objectValue As Object

Syntax: C#

protected object objectValue;

See Also

MySqlValue Class , MySql.Data.Types Namespace

23.2.4.2.3.1.5. IsValidDateTime Property

Indicates if this object contains a value that can be represented as a DateTime

Syntax: Visual Basic

Public ReadOnly Property IsValidDateTime As Boolean

Syntax: C#

public bool IsValidDateTime {get;}

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.6. Minute Property

Returns the minute portion of this datetime

Syntax: Visual Basic

Public Property Minute As Integer

Syntax: C#

public int Minute {get; set;}

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.7. Month Property

Returns the month portion of this datetime

Syntax: Visual Basic

Public Property Month As Integer

Syntax: C#

public int Month {get; set;}

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.8. Second Property

Returns the second portion of this datetime

Syntax: Visual Basic

Public Property Second As Integer

Syntax: C#

public int Second {get; set;}

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.9. Year Property

Returns the year portion of this datetime

Syntax: Visual Basic

Public Property Year As Integer

Syntax: C#

public int Year {get; set;}

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.10. MySqlDateTime.GetDateTime Method

Returns this value as a DateTime

Syntax: Visual Basic

Public Function GetDateTime() As Date

Syntax: C#

public DateTime GetDateTime();

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.4.2.3.1.11. MySqlDateTime.ToString Method

Returns a MySQL specific string representation of this value

Syntax: Visual Basic

Overrides Public Function ToString() As String

Syntax: C#

public override string ToString();

See Also

MySqlDateTime Class , MySql.Data.Types Namespace

23.2.5. Connector/NET Notes and Tips

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

23.2.5.1. Connecting to MySQL Using Connector/NET

23.2.5.1.1. Introduction

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.

23.2.5.1.2. Creating a Connection String

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 for Section 23.2.3.3.15, “ConnectionString”):

  • Server: The name or network address of the instance of MySQL to which to connect. The default is localhost. Aliases include host, Data Source, DataSource, Address, Addr 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.

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

Visual Basic Example

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# Example

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:

Visual Basic Example

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# Example

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 Connector/NET classes to communicate with the MySQL server.

23.2.5.1.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 return 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 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:

Visual Basic Example

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# Example

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

Important: Note that if you are using multilanguage databases you must specify the character set in the connection string. If you do not specify the character set, the connection defaults to the latin1 charset. You can specify the character set as part of the connection string, for example:

MySqlConnection myConnection = new MySqlConnection("server=127.0.0.1;uid=root;" +
    "pwd=12345;database=test;charset=utf-8;");

23.2.5.2. Using the Connector/NET with Prepared Statements

23.2.5.2.1. Introduction

As of MySQL 4.1, it is possible to use prepared statements with 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.

23.2.5.2.2. Preparing Statements in 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 .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.

Visual Basic Example

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# Example

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

23.2.5.3. Accessing Stored Procedures with Connector/NET

23.2.5.3.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 procedure 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 platforms, but need to perform the same database operations.

  • When security is paramount. Banks, for example, use stored procedures for all common operations. 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.

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.

Note

When you call a stored procedure, the command object makes an additional SELECT call to determine the parameters of the stored procedure. You must ensure that the user calling the procedure has the SELECT privilege on the mysql.proc table to enable them to verify the parameters. Failure to do this will result in an error when calling the procedure.

This section will not provide in-depth information on creating Stored Procedures. For such information, please refer to http://dev.mysql.com/doc/mysql/en/stored-procedures.html.

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

23.2.5.3.2. Creating Stored Procedures from 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:

Visual Basic Example

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# Example

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 Connector/NET.

23.2.5.3.3. Calling a Stored Procedure from Connector/NET

To call a stored procedure using 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:

Visual Basic Example

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# Example

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.

23.2.5.4. Handling BLOB Data With Connector/NET

23.2.5.4.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 Connector/NET installation.

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

23.2.5.4.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 FileStream object, reads it into a byte array, and inserts it into the file table:

Visual Basic Example

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# Example

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.

23.2.5.4.4. Reading a BLOB from the 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:

Visual Basic Example

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# Example

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.

23.2.5.5. Using Connector/NET with Crystal Reports

23.2.5.5.1. Introduction

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

23.2.5.5.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 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:

Visual Basic Example

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# Example

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.

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

23.2.5.5.4. Displaying the Report

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:

  • 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 Section 23.2.5.5.2, “Creating a Data Source”, and have a crViewer control on your form named myViewer.

Visual Basic Example

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# Example

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:

Visual Basic Example

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# Example

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.

23.2.5.6. Handling Date and Time Information in Connector/NET

23.2.5.6.1. Introduction

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 Connector/NET.

23.2.5.6.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 System.ArgumentOutOfRangeException exception to occur.

23.2.5.6.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 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 Section 5.2.6, “SQL Modes”.

23.2.5.6.4. Handling Invalid Dates

Although 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 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 Connector/NET to return MySqlDateTime objects for invalid dates.

To instruct 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 (for example, 2005-02-23 08:50:25). This differs from the 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 application.

23.2.5.6.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:

Visual Basic Example

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

C# Example

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.

23.2.6. Connector/NET Support

The developers of Connector/NET greatly value the input of our users in the software development process. If you find Connector/NET lacking some feature important to you, or if you discover a bug and need to file a bug report, please use the instructions in Section 1.8, “How to Report Bugs or Problems”.

23.2.6.1. Connector/NET Community Support

23.2.6.2. How to report Connector/NET Problems or Bugs

If you encounter difficulties or problems with Connector/NET, contact the Connector/NET community Section 23.2.6.1, “Connector/NET Community Support”.

You should first try to execute the same SQL statements and commands from the mysql client program or from admndemo. This helps you determine whether the error is in Connector/NET or MySQL.

If reporting a problem, you should ideally include the following information with the email:

  • Operating system and version

  • Connector/NET version

  • MySQL server version

  • Copies of error messages or other unexpected output

  • Simple reproducible sample

Remember that the more information you can supply to us, the more likely it is that we can fix the problem.

If you believe the problem to be a bug, then you must report the bug through http://bugs.mysql.com/.

23.2.6.3. Connector/NET Change History

The Connector/NET Change History (Changelog) is located with the main Changelog for MySQL. See Section E.4, “Connector/NET Change History”.

JavaScript Editor Source code editor     What Is Ajax