Go C # connection Operation MySQL Instance

This article transferred from: HTTP://HI.BAIDU.COM/ZHQNGWENG/ITEM/C4D2520CB7216877BFE97EDF

Third-party components: Mysql.Data.dll
Description: Go to the official website to download Mysql.Data.dll, then add a reference to the component in the project, enter using Mysql.Data.MysqlClient in the code page, we can use the class library function to establish a connection.

Here are a few common functions:

#region establishing a MySQL database connection
<summary>
Establish a database connection.
</summary>
<returns> return Mysqlconnection Objects </returns>
Public Mysqlconnection Getmysqlcon ()
{
String M_str_sqlcon = "Server=localhost;user id=root;password=root;database=abc"; According to your own settings
Mysqlconnection mycon = new Mysqlconnection (M_str_sqlcon);
return mycon;
}
#endregion

#region Execute Mysqlcommand command
<summary>
Executive Mysqlcommand
</summary>
<param name= "m_str_sqlstr" >sql statement </param>
public void getmysqlcom (string m_str_sqlstr)
{
Mysqlconnection Mysqlcon = This.getmysqlcon ();
Mysqlcon. Open ();
Mysqlcommand mysqlcom = new Mysqlcommand (M_STR_SQLSTR, Mysqlcon);
Mysqlcom. ExecuteNonQuery ();
Mysqlcom. Dispose ();
Mysqlcon. Close ();
Mysqlcon. Dispose ();
}
#endregion

#region Creating Mysqldatareader Objects
<summary>
Create a Mysqldatareader object
</summary>
<param name= "m_str_sqlstr" >sql statement </param>
<returns> return Mysqldatareader Objects </returns>
Public Mysqldatareader Getmysqlread (string m_str_sqlstr)
{
Mysqlconnection Mysqlcon = This.getmysqlcon ();
Mysqlcommand mysqlcom = new Mysqlcommand (M_STR_SQLSTR, Mysqlcon);
Mysqlcon. Open ();
Mysqldatareader Mysqlread = mysqlcom. ExecuteReader (commandbehavior.closeconnection);
return mysqlread;
}
#endregion

Another article:

Test environment: Windows XP + MySql 5.0.24 + Visual C # Exdivss Edition
by Lucas 2008.12.29
1. Connect MySQL database with Mysqldrivercs
Download and install Mysqldrivercs First, address:
http://sourceforge.net/projects/mysqldrivercs/
Locate MySQLDriver.dll under the installation folder, and then add the MySQLDriver.dll reference to the project
Note: I downloaded the version is Mysqldrivercs-n-easyquerytools-4.0.1-dotnet2.0.exe

Using System;
Using System.Collections.Generic;
Using System.ComponentModel;
Using System.Data;
Using System.Data.Odbc;
Using System.Drawing;
Using System.Linq;
Using System.Text;
Using System.Windows.Forms;
Using Mysqldrivercs;
namespace MySQL
{
public partial class Form1:form
{
Public Form1 ()
{
InitializeComponent ();
}
private void Form1_Load (object sender, EventArgs e)
{
Mysqlconnection conn = null;
conn = new Mysqlconnection (new mysqlconnectionstring ("localhost", "Inv", "root", "831025"). asstring);
Conn. Open ();
Mysqlcommand commn = new Mysqlcommand ("Set names gb2312", conn);
Commn. ExecuteNonQuery ();
String sql = "SELECT * from Exchange";
Mysqldataadapter MDA = new Mysqldataadapter (SQL, conn);
DataSet ds = new DataSet ();
Mda. Fill (ds, "Table1");
This.dataGrid1.DataSource = ds. tables["Table1"];
Conn. Close ();
}
}
}

2. Access MySQL database via ODBC:
Reference:http://www.microsoft.com/china/community/Column/63.mspx
1. Install Microsoft Odbc.net: I am installing Mysql-connector-odbc-3.51.22-win32.msi
2. Install MDAC 2.7 or later: I installed the Mdac_typ.exe 2.7 Simplified Chinese version
3. Install the ODBC driver for MySQL: I installed the odbc_net.msi.
4. Management tools, data source odbc–> configuration DSN ...
5. Add Reference Microsoft.Data.Odbc.dll (1.0.3300) in solution management
6. Add reference using MICROSOFT.DATA.ODBC in the code;

Using System;
Using System.Collections.Generic;
Using System.ComponentModel;
Using System.Drawing;
Using System.Linq; VS2005 does not seem to have this namespace, test automatically generated under c#2008
Using System.Text;
Using System.Windows.Forms;
Using Microsoft.Data.Odbc;
namespace MySQL
{
public partial class Form1:form
{
Public Form1 ()
{
InitializeComponent ();
}
private void Form1_Load (object sender, EventArgs e)
{
String myconstring = "Driver={mysql ODBC 3.51 DRIVER};" +
"Server=localhost;" +
"DATABASE=INV;" +
"Uid=root;" +
"PASSWORD=831025;" +
"Option=3";
OdbcConnection myconnection = new OdbcConnection (myconstring);
Myconnection.open ();
Console.WriteLine ("\ Success, connected successfully!\n");
string query = "INSERT INTO test values (' ' Hello ', ' ' Lucas ', ' Liu ')";
OdbcCommand cmd = new OdbcCommand (query, MyConnection);
Handling Exceptions: Inserting duplicate records with exception
try{
Cmd. ExecuteNonQuery ();
}
catch (Exception ex) {
Console.WriteLine ("Record Duplicate.");
}finally{
Cmd. Dispose ();
}
/
/
Myconnection.close ();
}
}
}

Examples of Use:

Using System;
Using System.Configuration;
Using MySql.Data.MySqlClient;
<summary>
Summary description of Testdatebase
</summary>
public class Testdatebase
{
Public Testdatebase ()
{
//
TODO: Add constructor logic here
//
}
public static void Main (string[] args)
{
mysqlconnection MySQL = Getmysqlcon ();
Querying SQL
String sqlsearch = "SELECT * from student";
Insert SQL
String Sqlinsert = "INSERT into student values (12, ' Zhang San ', 25, ' College ')";
Modify SQL
String sqlupdate = "Update student set Name= ' John Doe ' where id= 3";
Delete SQL
String Sqldel = "Delete from student where id = 12";
Print SQL statements
Console.WriteLine (Sqldel);
Four types of statement objects
Mysqlcommand Mysqlcommand = Getsqlcommand (Sqlsearch, MySQL);
Mysqlcommand Mysqlcommand = Getsqlcommand (Sqlinsert, MySQL);
Mysqlcommand Mysqlcommand = Getsqlcommand (sqlupdate, MySQL);
Mysqlcommand Mysqlcommand = Getsqlcommand (Sqldel, MySQL);
Mysql. Open ();
Getresultset (Mysqlcommand);
Getinsert (Mysqlcommand);
GetUpdate (Mysqlcommand);
Getdel (Mysqlcommand);
Remember to close
Mysql. Close ();
String readLine = Console.ReadLine ();
}
<summary>
Build a MySQL database link
</summary>
<returns></returns>
public static mysqlconnection Getmysqlcon ()
{
String mysqlstr = "Database=test;data source=127.0.0.1; User Id=root; Password=root;pooling=false; charset=utf8;port=3306 ";
String Mysqlcon = configurationmanager.connectionstrings["Mysqlcon"]. ConnectionString;
mysqlconnection mysql = new mysqlconnection (MYSQLSTR);
return MySQL;
}
<summary>
Creating an Execute Command statement object
</summary>
<param name= "SQL" ></param>
<param name= "MySQL" ></param>
<returns></returns>
public static Mysqlcommand Getsqlcommand (String sql,mysqlconnection MySQL)
{
Mysqlcommand Mysqlcommand = new Mysqlcommand (sql, MySQL);
Mysqlcommand Mysqlcommand = new Mysqlcommand (SQL);
mysqlcommand.connection = MySQL;
return mysqlcommand;
}
<summary>
Query and get the result set and traverse
</summary>
<param name= "Mysqlcommand" ></param>
public static void Getresultset (Mysqlcommand mysqlcommand)
{
Mysqldatareader reader = Mysqlcommand.executereader ();
Try
{
while (reader. Read ())
{
if (reader. HasRows)
{
Console.WriteLine ("Number:" + reader.) GetInt32 (0) + "| Name:" + Reader. GetString (1) + "| Age:" + reader. GetInt32 (2) + "| Education:" + reader. GetString (3));
}
}
}
catch (Exception)
{

Console.WriteLine ("The query failed! ");
}
Finally
{
Reader. Close ();
}
}
<summary>
Add data
</summary>
<param name= "Mysqlcommand" ></param>
public static void Getinsert (Mysqlcommand mysqlcommand)
{
Try
{
Mysqlcommand.executenonquery ();
}
catch (Exception ex)
{
String message = ex. Message;
Console.WriteLine ("Insert data failed!") "+ message);
}

}
<summary>
modifying data
</summary>
<param name= "Mysqlcommand" ></param>
public static void GetUpdate (Mysqlcommand mysqlcommand)
{
Try
{
Mysqlcommand.executenonquery ();
}
catch (Exception ex)
{

String message = ex. Message;
Console.WriteLine ("Failed to modify the data!") "+ message);
}
}
<summary>
Delete data
</summary>
<param name= "Mysqlcommand" ></param>
public static void Getdel (Mysqlcommand mysqlcommand)
{
Try
{
Mysqlcommand.executenonquery ();
}
catch (Exception ex)
{
String message = ex. Message;
Console.WriteLine ("Delete data failed!") "+ message);
}
}
}

Go C # connection Operation MySQL Instance