貼一份示例代碼。非常適合於初學者使用。 1) C#訪問mysql using System; using System.Collections.Generic; using System.Text; using MySql.Data.MySqlClient; using System.Data; using System.Data.Common; namespace SybaseUtilTest { class Program { // http://bugs.mysql.com/47422, 有興趣的朋友,可以看看這個bug是怎麼回事 static void testDataAdapter() { try { MySqlClientFactory factory = MySqlClientFactory.Instance; DbConnection conn = factory.CreateConnection(); conn.ConnectionString = string.Format("server={0};user id={1}; password={2}; database={3}; port={4}; pooling=false", "localhost", "root", "passwd", "test", 3306); conn.Open(); DbDataAdapter da = factory.CreateDataAdapter(); da.SelectCommand = conn.CreateCommand(); da.SelectCommand.CommandText = "select * from t12345"; da.DeleteCommand = conn.CreateCommand(); da.DeleteCommand.CommandText = "delete from t12345 where id = @id"; DbParameter param = factory.CreateParameter(); param.ParameterName = "@id"; param.DbType = DbType.Int32; param.SourceColumn = "id"; param.SourceVersion = DataRowVersion.Current; da.DeleteCommand.Parameters.Add(param); da.DeleteCommand.UpdatedRowSource = UpdateRowSource.None; DataTable dt = new DataTable("t12345"); da.Fill(dt); int index = 0; foreach ( DataRow o in dt.Rows ) { if (o["id"].Equals(4)) { Console.WriteLine(String.Format("index={0}, to delete id = 4, col2 = {1}" , index, o["col2"])); break; } index++; } dt.Rows[index].Delete(); da.Update(dt); dt.AcceptChanges(); da.Dispose(); conn.Close(); } catch (Exception ex) { Console.WriteLine(ex.Source + " " + ex.Message + " " + ex.StackTrace); } } static void Main(string[] args) { testDataAdapter(); } } } 2) C++訪問 (直接調用C-API) #include <iostream> #include <windows.h> #include <mysql.h> #include <string> static const char host[32] = "localhost"; static const char user[32] = "test"; static const char passwd[32] = "passwd"; static const char db[32] = "test"; /** mysql> select * from t; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql> delimiter // mysql> create procedure get_t(in t1 int) -> begin -> select id from t where id=t1; -> end -> // Query OK, 0 rows affected (0.05 sec) mysql> call get_t(1); -> // +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) */ void test_more_results(MYSQL* h) { char str[512] = "insert into test_num values(101);insert into test_num values(122);commit;"; int r = mysql_real_query(h, str, strlen(str)); if (r) { const char * error = mysql_error(h); std::cout<<"*** Connection Error " << error << std::endl; } do { MYSQL_RES* res = mysql_store_result(h); mysql_free_result(res); } while ( (0 == mysql_next_result(h)) ); } void test_proc_stmt(MYSQL* h) { MYSQL* mysql_ = h; MYSQL_BIND bind; MYSQL_BIND obind[1]; // test_more_results(mysql_); MYSQL_STMT *hStmt = mysql_stmt_init(mysql_); my_bool true_value= 1; mysql_stmt_attr_set(hStmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*) &true_value); char sql[] = "call get_t(?)"; //char sql[] = "select id from t where id=?"; if (mysql_stmt_prepare(hStmt, sql, strlen(sql))) { std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl; mysql_stmt_reset(hStmt); if (mysql_stmt_prepare(hStmt, sql, strlen(sql))) { std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl; mysql_close(mysql_); exit( -1); } } int id = 1; unsigned long id_len = 0; memset(&bind, 0, sizeof(bind)); bind.buffer_type = FIELD_TYPE_LONG; bind.buffer = (void*)&id; bind.is_unsigned = true; bind.length = &id_len; // bind[0].buffer_length = sizeof(id); // bind[0].is_null = 0; if (mysql_stmt_bind_param(hStmt,(MYSQL_BIND*)(&bind)) != 0) { std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl; mysql_close(mysql_); exit( -1); } if (mysql_stmt_execute(hStmt) != 0) { std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl; mysql_close(mysql_); exit( -1); } int t2; memset(obind, 0, sizeof(obind)); obind[0].buffer_type= MYSQL_TYPE_LONG; obind[0].buffer= (char *)&t2; obind[0].buffer_length = sizeof(t2); if (mysql_stmt_bind_result(hStmt, (MYSQL_BIND*)&obind[0]) != 0) { std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl; mysql_close(mysql_); exit( -1); } if ( mysql_stmt_store_result(hStmt) != 0 ) { std::cout<<__LINE__<<": stmt prepare error: "<< (mysql_stmt_error(hStmt))<<std::endl; mysql_close(mysql_); exit( -1); } int rows = mysql_stmt_num_rows(hStmt); for (int i=0; i<rows; i++) { if (mysql_stmt_fetch(hStmt) == 0) { std::cout<<"id = "<<t2<<std::endl; } } mysql_stmt_free_result(hStmt); mysql_stmt_close(hStmt); } // // Just for demo only. // int main() { MYSQL* mysql_ = NULL; MYSQL_RES* result_ = NULL; MYSQL_ROW row_; mysql_ = mysql_init(mysql_); // if (mysql_real_connect(mysql_, host, user, passwd, db, 3306, NULL, CLIENT_MULTI_STATEMENTS) == NULL) if (mysql_real_connect(mysql_, host, user, passwd, db, 3306, NULL, CLIENT_MULTI_STATEMENTS) == NULL) { const char * error = mysql_error(mysql_); std::cout<<"*** Connection Error " << error << std::endl; return -1; } mysql_autocommit(mysql_, false); std::string encodeStr = "set names 'gbk'"; mysql_real_query(mysql_, encodeStr.c_str(), encodeStr.size()); /* const char* tmpTableName = "t"; // assume you are querying the table 't' char str[512]; int cnt = 0; sprintf(str,"select count(*) as cnt from %s", tmpTableName); mysql_real_query(mysql_, str, strlen(str)); result_ = mysql_store_result(mysql_); while (row_ = mysql_fetch_row(result_)) { // get the field value if (row_[0]) { std::cout<<"count = "<<row_[0]<<std::endl; // convert it into int cnt = atoi(row_[0]); std::cout<<"cnt value = "<<row_[0]<<std::endl; } } mysql_free_result(result_); test_more_results(); */ test_proc_stmt(mysql_); do { MYSQL_RES* res = mysql_store_result(mysql_); mysql_free_result(res); } while ( (0 == mysql_next_result(mysql_)) ); test_proc_stmt(mysql_); mysql_close(mysql_); return 0; }
