MySQL and C #

1. Connection:
1. Install Microsoft odbc.net.
2. Install the ODBC driver of MySQL.
2. add reference to Microsoft. Data. ODBC. dll (1.0.3300) in solution management)
3. Add references to the code.
Using Microsoft. Data. ODBC;
4. write code
String myconstring = "driver = {MySQL ODBC 3.51 driver};" +
"Server = localhost;" +
"Database = samp_db;" +
"Uid = root;" +
"Password =;" +
"Option = 3 ";
// Connect to MySQL using connector/ODBC
Odbcconnection myconnection = new odbcconnection (myconstring );
Myconnection. open ();
Console. writeline ("/n !!! Success, connected successfully !!! /N ");
Myconnection. Close ();

5 detailed routine

Namespace mydbc3
{
Class mycon
{
Static void main (string [] ARGs)
{
Try
{
// Connection string for connector/ODBC 3.51
String myconstring = "driver = {MySQL ODBC 3.51 driver};" +
"Server = localhost;" +
"Database = test;" +
"Uid = root;" +
"Password = 1;" +
"Option = 3 ";

// Connect to MySQL using connector/ODBC
Odbcconnection myconnection = new odbcconnection (myconstring );
Myconnection. open ();

Console. writeline ("/n !!! Success, connected successfully !!! /N ");

// Display connection information
Console. writeline ("connection information :");
Console. writeline ("/tconnection string:" + myconnection. connectionstring );
Console. writeline ("/tconnection Timeout:" + myconnection. connectiontimeout );
Console. writeline ("/tdatabase:" + myconnection. database );
Console. writeline ("/tdatasource:" + myconnection. datasource );
Console. writeline ("/tdriver:" + myconnection. Driver );
Console. writeline ("/tserverversion:" + myconnection. serverversion );

// Create a sample table
Odbccommand mycommand = new odbccommand ("Drop table if exists my_odbc_net", myconnection );
Mycommand. executenonquery ();
Mycommand. commandtext = "create table my_odbc_net (ID int, name varchar (20), IDB bigint )";
Mycommand. executenonquery ();

// Insert
Mycommand. commandtext = "insert into my_odbc_net values (10, 'venu', 300 )";
Console. writeline ("insert, total rows affected:" + mycommand. executenonquery ());;

// Insert
Mycommand. commandtext = "insert into my_odbc_net values (20, 'mysql', 400 )";
Console. writeline ("insert, total rows affected:" + mycommand. executenonquery ());

// Insert
Mycommand. commandtext = "insert into my_odbc_net values (20, 'mysql', 500 )";
Console. writeline ("insert, total rows affected:" + mycommand. executenonquery ());

// Update
Mycommand. commandtext = "Update my_odbc_net Set ID = 999 where id = 20 ";
Console. writeline ("Update, total rows affected:" + mycommand. executenonquery ());

// Count (*)
Mycommand. commandtext = "select count (*) as trows from my_odbc_net ";
Console. writeline ("Total rows:" + mycommand. executescalar ());

// Fetch
Mycommand. commandtext = "select * From my_odbc_net ";
Odbcdatareader mydatareader;
Mydatareader = mycommand. executereader ();
While (mydatareader. Read ())
{
If (string. Compare (myconnection. Driver, "mydbc3.dll") = 0)
{
Console. writeline ("data:" + mydatareader. getint32 (0) + "+
Mydatareader. getstring (1) + "" +
Mydatareader. getint64 (2); // supported only by connector/ODBC 3.51
}
Else
{
Console. writeline ("data:" + mydatareader. getint32 (0) + "+
Mydatareader. getstring (1) + "" +
Mydatareader. getint32 (2); // bigints not supported by connector/ODBC
}
}

// Close Reader
Mydatareader. Close ();
//
Odbcdataadapter ADP = new odbcdataadapter (mycommand );
Dataset DS = new dataset ();
ADP. Fill (DS );
Console. writeline ("odbcdataadapter count:" + Ds. Tables. Count + "rows:" + Ds. Tables [0]. Rows. Count );
// Close connection
Myconnection. Close ();

}
Catch (odbcexception mydbcexception) // catch any ODBC exception ..
{
For (INT I = 0; I <mydbcexception. errors. Count; I ++)
{
Console. Write ("Error #" + I + "/N" +
"Message:" + myodbcexception. errors [I]. Message + "/N" +
"Native:" + myodbcexception. errors [I]. nativeerror. tostring () + "/N" +
"Source:" + myodbcexception. errors [I]. Source + "/N" +
"SQL:" + myodbcexception. errors [I]. sqlstate + "/N ");
}
}
Console. Read ();
}
}
}

 

5 Adapter

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 (); 54ne.com

 

6. MySQL 4.1 supports transaction.

Http://remindme.blogbus.com/logs/34155274.html

First, you must set the automatic submission function to zero:

Set @ autocommit = 0;

Check whether it is set to zero:

Select @ autocommit;

Example of transaction processing:

Start transaction;

Michael's account amount = Michael's account amount-1000;

Li Si's account amount = Li Si's account amount + 1000;

Commit;