Simple use of MySQLConnector/Net

For simple use of MySQLConnectorNet, first create a project (WindowsApplication) and then add reference (MySql. data) Note: According to the usage. select MySql for different net versions. after the Data version, place the control three TextBox, two comboboxes, and one DataGridView. In the Password box, set the drop-down box, and set the Data setting connection.

For simple use of MySQL Connector/Net, first create a project (Windows Application) and then add reference (MySql. data) Note: According to the usage. select MySql for different net versions. after the Data version, place the control three TextBox, two comboboxes, and one DataGridView. In the Password box, set the drop-down box, and set the Data setting connection.

Simple use of MySQL Connector/Net

First, create a project (Windows Application)

Then add reference (MySql. Data)

Note: select MySql. Data Based on the. net version.

Then place the control

3 TextBox, 2 ComboBox, 1 DataGridView, etc.

Password box settings

Drop-down box settings

Data Grid settings

Code of the connection button:

String connStr = string. format ("server = {0}; user id = {1}; password = {2}; database = mysql; pooling = false", edtSrv. text, edtUser. text, edtPwd. text); try {conn = new MySqlConnection (connStr); conn. open (); // obtain the Database List
 
  
Cmd = new List
  
   
(); Cmd. Add ("show databases"); List
   
    
List = getDataList (cmd); // clear the drop-down box cBoxDBs. items. clear (); // Add the foreach (string str in list) cBoxDBs in the drop-down list. items. add (str);} catch (MySqlException ex) {MessageBox. show ("no database found:" + ex. message );}
   
  
 

Drop-down list (Database List)

// Obtain the Database List
 
  
CmdList = new List
  
   
(); CmdList. Add ("USE" + cBoxDBs. SelectedItem. ToString (); cmdList. Add ("show tables"); List
   
    
List = getDataList (cmdList); // Clear the drop-down box cBoxTables. Items. Clear (); // Add the drop-down box list foreach (string str in list) cBoxTables. Items. Add (str );
   
  
 

Drop-down list (data table list)

// Obtain the data table name string tableName = cBoxTables. selectedItem. toString (); // set the data bridge dataAdapter = new MySqlDataAdapter ("Select * from" + tableName, conn); // DataSet sqlCmdBuilder = new MySqlCommandBuilder (dataAdapter ); // create a data table = new able (tableName); // fill in the data table to the data bridge dataAdapter. fill (table); // specify the data source dataGridView1.DataSource = table;

Running effect:

Procedure (1, 2, 3)

Development Environment: VS2005

Connector (For. net): V6.8.3

Form1.cs

Using System; using System. collections. generic; using System. componentModel; using System. data; using System. drawing; using System. text; using System. windows. forms; using MySql. data. mySqlClient; namespace testMySQL {public partial class frmain: Form {private MySqlConnection conn; private DataTable table; private MySqlDataAdapter dataAdapter; private MySqlCommandBuilder sqlCmdBuilder; public frmain () {InitializeComponent ();} private void btnConn_Click (object sender, EventArgs e) {// string connStr = string. format ("server = {0}; user id = {1}; password = {2}; database = mysql; pooling = false", edtSrv. text, edtUser. text, edtPwd. text); try {conn = new MySqlConnection (connStr); conn. open (); // obtain the Database List
 
  
Cmd = new List
  
   
(); Cmd. Add ("show databases"); List
   
    
List = getDataList (cmd); // clear the drop-down box cBoxDBs. items. clear (); // Add the foreach (string str in list) cBoxDBs in the drop-down list. items. add (str);} catch (MySqlException ex) {MessageBox. show ("no database found:" + ex. message) ;}} private List
    
     
GetDataList (List
     
      
CmdList) {List
      
        Result = new List
       
         (); // SQL data reader MySqlDataReader dataReader = null; // SQL command executor MySqlCommand sqlCmd = new MySqlCommand (); // set the connection to sqlCmd. connection = conn; try {// execute the SQL command foreach (string cmd in cmdList) {sqlCmd. commandText = cmd; sqlCmd. executeNonQuery ();} // dataReader = sqlCmd. executeReader (); while (dataReader. read () {string strDbName = dataReader. getString (0); result. add (strDbName) ;}} catch (My SqlException ex) {MessageBox. Show ("failed to read data:" + ex. Message);} finally {if (dataReader! = Null) dataReader. Close ();} return result;} private void cBoxDBs_SelectedIndexChanged (object sender, EventArgs e) {// obtain the Database List
        
          CmdList = new List
         
           (); CmdList. Add ("USE" + cBoxDBs. SelectedItem. ToString (); cmdList. Add ("show tables"); List
          
            List = getDataList (cmdList); // clear the Database list cBoxTables. items. clear (); // Add the foreach (string str in list) cBoxTables in the drop-down list. items. add (str);} private void cBoxTables_SelectedIndexChanged (object sender, EventArgs e) {// obtain the data table name string tableName = cBoxTables. selectedItem. toString (); // set the data bridge dataAdapter = new MySqlDataAdapter ("Select * from" + tableName, conn); // DataSet sqlCmdBuilder = new MySqlCommandBuilder (dataAdapter ); // create a data table = new able (tableName); // fill in the data table to the data bridge dataAdapter. fill (table); // specify the data source dataGridView1.DataSource = table ;}}}
          
         
        
       
      
     
    
   
  
 

FAQ:

1. Unable to convert MySQL date/time value to System. DateTime

Cause:

The DateTime column contains "YYYY-mm-dd" or "0000-00-00 00:00:00" (YYYY-mm-dd HH: mm: ss) data.

Solution:

Add "Convert Zero Datetime = True" to the connection string format"

server={0}; user id={1}; password={2}; database=mysql; pooling=false; Convert Zero Datetime=True