C++和C#访问MySQL的简单代码示例

贴一份示例代码。非常适合于初学者使用。

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

<script type="text/javascript"><!-- google_ad_client = "ca-pub-7104628658411459"; /* wide2 */ google_ad_slot = "5922063526"; google_ad_width = 468; google_ad_height = 60; //--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>