用於CLR模式的mysql簡單封裝完成

解決了綁定變數問題後，剩下的事情就簡單多了。

設想的工作模式是這樣的：

目的：解決mysql的常用訪問。

經常用的是單行的SELECT，以及多行的擷取。

而多條記錄的SELECT，往往是field相同，可以有一個或者多個結果集。

多個不同field的結果集訪問非常少見，可以通過寫程式稍加控制解決。

手段：

結果的擷取，可以使用可變參數...（貌似C#沒有這個？不記得了），而首先可以先把返回結果全部定義成為tResult類，然後取得結果後，再轉換成為相應想使用的類型。

int iId,iBaseType;System::String^ fciname,^fciremark;array<tResult^>^ m_result;m_result = gcnew array<tResult^>(4);for(i=0;i<4;i++)m_result[i] = gcnew tResult();m_sql->ProcessRow2(pRow,m_result[0],m_result[1],m_result[2],m_result[3]);iId = m_result[0];iBaseType = m_result[1];fciname  = m_result[2];fciremark = m_result[3];

在設計tResult類的時候，還可以保留未經處理資料，也即tResult.pUnsigned就是原始的mysql資料結果。

整個遍曆過程：

Execute(執行SQL語句)while（FetchRow == true){ProcessRow(獲得結果）FetchRow}

即首先執行SQL語句，然後FETCH，如果有記錄，那麼就處理記錄，然後再 FETCH下一行。

測試代碼如下：

void TestConnect(){tsql^m_sql;int^m_iRet;boolm_bRet;inti;tRow^pRow=nullptr;System::String^m_sRet;array<tResult^>^ m_result;m_result = gcnew array<tResult^>(4);for(i=0;i<4;i++)m_result[i] = gcnew tResult();// 1. 建立SQL類執行個體m_sql = gcnew tsql();// 2. 串連資料庫m_bRet = m_sql->ConnectDB(L"127.0.0.1","root","","test",3306,m_iRet,m_sRet);Console::WriteLine("Connect result:{0},{1}",m_iRet,m_sRet);m_sRet = Console::ReadLine();// 3. 設定字元集為gbkm_bRet = m_sql->Execute(m_iRet,m_sRet," set names gbk");Console::WriteLine("set names gbk:result:{0},{1}",m_iRet,m_sRet);// 4. 執行單行SELECTm_bRet = m_sql->Select(m_iRet,m_sRet," select count(*) from TCI_TYPEBASEINFO ",m_result[0]);Console::WriteLine("Select result:{0},{1}",m_iRet,m_sRet);if (m_bRet == true){int iCount;iCount = m_result[0];Console::WriteLine(" count(*) of TCI_TYPEBASEINFO is:{0}",iCount);}m_sRet = Console::ReadLine();// 5. 用EXECUTE,FECHROW,PROCESSROW來遍曆多條記錄m_bRet = m_sql->Execute(m_iRet,m_sRet," select FCITYPEID,FCIBASETYPE,FCINAME,FCIREMARK from TCI_TYPEBASEINFO ");Console::WriteLine("Execute result:{0},{1}",m_iRet,m_sRet);m_bRet = m_sql->FetchRow(m_iRet,m_sRet,pRow);while (m_bRet){int iId,iBaseType;System::String^ fciname,^fciremark;m_sql->ProcessRow2(pRow,m_result[0],m_result[1],m_result[2],m_result[3]);iId = m_result[0];iBaseType = m_result[1];fciname  = m_result[2];fciremark = m_result[3];Console::WriteLine(" select * from TCI_TYPEBASEINFO is:{0},{1},{2},{3}",iId,iBaseType,fciname,fciremark);m_bRet = m_sql->FetchRow(m_iRet,m_sRet,pRow);}m_sRet = Console::ReadLine();// 6. 用SELECT2，FETCHROW,PROCESSROW來遍曆多條記錄m_bRet = m_sql->Select2(m_iRet,m_sRet," select FCITYPEID,FCIBASETYPE,FCINAME,FCIREMARK from TCI_TYPEBASEINFO ",pRow,m_result[0],m_result[1],m_result[2],m_result[3]);Console::WriteLine("Select2 result:{0},{1}",m_iRet,m_sRet);while (m_bRet){int iId,iBaseType;System::String^ fciname,^fciremark;iId = m_result[0];iBaseType = m_result[1];fciname  = m_result[2];fciremark = m_result[3];Console::WriteLine(" select * from TCI_TYPEBASEINFO is:{0},{1},{2},{3}",iId,iBaseType,fciname,fciremark);m_bRet = m_sql->FetchRow(m_iRet,m_sRet,pRow);if (m_bRet == false)break;m_sql->ProcessRow2(pRow,m_result[0],m_result[1],m_result[2],m_result[3]);}m_sRet = Console::ReadLine();// 7. 關閉資料庫m_bRet = m_sql->DisConnectDB(m_iRet,m_sRet);Console::WriteLine("DisConnect result:{0},{1}",m_iRet,m_sRet);m_sRet = Console::ReadLine();}

tsql的實現如下：

// This is the main DLL file.#include "stdafx.h"#include "vcclr.h"#include "msql.h"using namespace System::Runtime::InteropServices;namespace msql{tsql::tsql(){m_mysql = NULL;m_bIsConnected = false;}tsql::~tsql(){int^ m_iRet;System::String^ m_sRet;if (m_bIsConnected)DisConnectDB(m_iRet,m_sRet);if (m_mysql)mysql_close(m_mysql);m_bIsConnected =  false;m_mysql = NULL;}/* -------------------------------------------------------------------------------------- * ConnectDB * 串連MYSQL資料庫。 * 參數： * sServer:mysql服務地址 * sUser:使用者名稱 * sPasswd:口令 * sDatabase:資料庫名 * iPort:mysql伺服器port * iRetCode:傳回值 * sRetMsg:返回資訊 * 返回：true: 串連資料庫成功. *false:串連資料庫失敗，iRetCode，sRetMsg中存放著錯誤碼和錯誤資訊 * -------------------------------------------------------------------------------------- * 用法： *m_bRet = m_sql->ConnectDB(L"127.0.0.1","root","mypass","test",3306,m_iRet,m_sRet); * --------------------------------------------------------------------------------------*/bool tsql::ConnectDB(System::String^ sServer,// MYSQL伺服器名 System::String^ sUser,// MYSQL使用者名稱 System::String ^ sPasswd,// MYSQL密碼 System::String^ sDatabase,// MYSQL資料庫 int iPort,// MYSQL連接埠 int ^% iRetCode,// 傳回值 System::String ^% sRetMsg// 返回資訊 ){boolm_bRet;intm_iRet;System::String^ m_sRet;intrc;MYSQL*m_t;m_sRet = L"";m_iRet = 0;m_bRet = true;/*---------------------------------------------------------------------------------*/if (m_bIsConnected == true){m_sRet = L"已經連上資料庫";goto L_RET;}if (m_mysql == NULL){m_mysql = mysql_init(NULL);}m_t = mysql_real_connect(m_mysql,(const char *)(void*)Marshal::StringToHGlobalAnsi(sServer),(const char *)(void*)Marshal::StringToHGlobalAnsi(sUser),(const char *)(void*)Marshal::StringToHGlobalAnsi(sPasswd),(const char *)(void*)Marshal::StringToHGlobalAnsi(sDatabase),iPort,NULL,CLIENT_MULTI_RESULTS);if (m_t == NULL){m_sRet = gcnew System::String((const char*)(mysql_error(m_mysql)));m_iRet = mysql_errno(m_mysql);m_bRet = false;goto  L_RET;}m_bIsConnected = true;L_RET:iRetCode = m_iRet;sRetMsg = m_sRet;return m_bRet;;}/* -------------------------------------------------------------------------------------- * DisConnectDB * 斷開MYSQL資料庫。 * 參數： * iRetCode:傳回值 * sRetMsg:返回資訊 * 返回：true: 斷開資料庫成功. *false:斷開資料庫失敗，iRetCode，sRetMsg中存放著錯誤碼和錯誤資訊 * -------------------------------------------------------------------------------------- * 用法： *m_bRet = m_sql->DisConnectDB(m_iRet,m_sRet); * --------------------------------------------------------------------------------------*/bool tsql::DisConnectDB(int ^% iRetCode,// 傳回值System::String ^% sRetMsg// 返回資訊){boolm_bRet;intm_iRet;System::String^ m_sRet;m_sRet = L"";m_iRet = 0;m_bRet = true;/*---------------------------------------------------------------------------------*/if (m_bIsConnected == false){m_sRet = L"已經斷開資料庫";goto L_RET;}mysql_close(m_mysql);m_mysql = NULL;m_bIsConnected = false;/*---------------------------------------------------------------------------------*/L_RET:iRetCode = m_iRet;sRetMsg = m_sRet;return m_bRet;}/* -------------------------------------------------------------------------------------- * f_StringToChar * System::String轉換為char數組 * 參數： * pStr:被轉換的System::String * pOut:輸出的char指標的指標 * piSize:輸出轉換後的大小 * 返回：轉換後的大小。 * -------------------------------------------------------------------------------------- * 用法： * 由於轉換為char指標需要分配記憶體，如果由調用者分配，則調用者可能不知道實際所需大小。 * 因此這裡採用了兩種的相容方式。如果調用者不分配，則由本函數分配，調用者釋放。如果調用者 * 分配，且大小足夠，則直接用調用者的指標，如果大小不夠，則本函數分配，調用者釋放。 * char * pStr = NULL; * int    iStr = 0; * f_StringToChar(sSql,&pStr,&iStr); * // dosomething, * f_StringToChar(sSql,&pStr,&iStr);//這裡無需考慮pStr和iStr,函數內會自動調整 * // ... * if (pStr)// 最後釋放記憶體 *delete [] pStr; * --------------------------------------------------------------------------------------*/int tsql::f_StringToChar(System::String ^pStr,// 被轉換的System::String char **pOut,// 轉換出的char 數組 int * piSize)// 轉換出的大小.{pin_ptr<const wchar_t> wch = PtrToStringChars(pStr);// Convert to a char*size_t origsize = wcslen(wch) + 1;const size_t newsize = 100;size_t convertedChars = 0;//char* nstring=new char[newsize];if (*pOut == NULL){*pOut = new char [newsize];if (piSize)*piSize = (int)newsize;}else{if (piSize){if (*piSize < (int)newsize){delete [] *pOut;*pOut = new char [newsize];if (piSize)*piSize =(int) newsize;}}}wcstombs_s(&convertedChars, *pOut, origsize, wch, _TRUNCATE);return newsize;}/* -------------------------------------------------------------------------------------- * Execute * 執行SQL語句。執行完成後，也可以用FETCHROW,PROCESSROW來進行處理結果集。 * 參數： * iRetCode:傳回值 * sRetMsg:返回資訊 * sSql:SQL語句 * 返回：true: 執行成功. *false:執行失敗，iRetCode，sRetMsg中存放著錯誤碼和錯誤資訊 * -------------------------------------------------------------------------------------- * 用法： *m_bRet = m_sql->Execute(m_iRet,m_sRet," set names gbk"); * --------------------------------------------------------------------------------------*/bool tsql::Execute(int ^% iRetCode,// 輸出的錯誤碼   System::String ^% sRetMsg,// 輸出的錯誤資訊   System::String^ sSql)// SQL語句{boolm_bRet;intm_iRet;System::String^ m_sRet;char*pStr;int iStr;m_sRet = L"";m_iRet = 0;m_bRet = true;pStr = NULL;iStr = 0;f_StringToChar(sSql,&pStr,&iStr);/*---------------------------------------------------------------------------------*/if (m_bIsConnected == false){m_sRet = L"已經斷開資料庫";m_bRet = false;m_iRet = -1;goto L_RET;}/*---------------------------------------------------------------------------------*/int rc;rc = mysql_query(m_mysql,pStr);if (rc){m_iRet = mysql_errno(m_mysql);m_sRet = gcnew System::String(mysql_error(m_mysql));m_bRet = false;goto  L_RET;}/*---------------------------------------------------------------------------------*/L_RET:if (pStr)delete [] pStr;iRetCode = m_iRet;sRetMsg = m_sRet;return m_bRet;}/* -------------------------------------------------------------------------------------- * Select * 執行單行的SELECT語句。 * 參數： * iRetCode:傳回值 * sRetMsg:返回資訊 * sSql:SQL語句 * ...:輸出的變數集合 * 返回：true: 執行成功. *false:執行失敗，iRetCode，sRetMsg中存放著錯誤碼和錯誤資訊 * -------------------------------------------------------------------------------------- * 用法： *array<tResult^>^ m_result; *int i; *m_result = gcnew array<tResult^>(4); *for(i=0;i<4;i++) *m_result[i] = gcnew tResult(); *m_bRet = m_sql->Select(m_iRet,m_sRet," select count(*) from TCI_TYPEBASEINFO ",m_result[0]); * *Select的結果用tResult儲存，可以轉換為int,double,System::String等類型 * --------------------------------------------------------------------------------------*/bool tsql::Select(int ^%iRetCode,// 輸出的錯誤碼  System::String^%sRetMsg,// 輸出的錯誤資訊  System::String^sSql,// SQL語句  ... array<tResult ^>^ pOutArray// 輸出的變數集合  ){boolm_bRet;intm_iRet;System::String^ m_sRet;char*pStr;int iStr;m_sRet = L"";m_iRet = 0;m_bRet = true;pStr = NULL;iStr = 0;f_StringToChar(sSql,&pStr,&iStr);/*---------------------------------------------------------------------------------*/if (m_bIsConnected == false){m_sRet = L"已經斷開資料庫";m_bRet = false;m_iRet = -1;goto L_RET;}/*---------------------------------------------------------------------------------*/int rc;rc = mysql_query(m_mysql,pStr);if (rc){m_iRet = mysql_errno(m_mysql);m_sRet = gcnew System::String(mysql_error(m_mysql));m_bRet = false;goto  L_RET;}tRow^ pRow;m_bRet = FetchRow(iRetCode,m_sRet,pRow);if (m_bRet == false){m_iRet = *iRetCode;goto L_RET;}ProcessRow(pRow,pOutArray);delete pRow;// 顯示釋放pRow,目的是執行mysql_free_resultL_RET:if (pStr)delete [] pStr;iRetCode = m_iRet;sRetMsg = m_sRet;return m_bRet;}/* -------------------------------------------------------------------------------------- * Select2 * 執行單行返回的的SELECT語句。 * 參數： * iRetCode:傳回值 * sRetMsg:返回資訊 * pRow:中間變數，用於記錄中間結果集 * sSql:SQL語句 * ...:輸出的變數集合 * 返回：true: 執行成功. *false:執行失敗，iRetCode，sRetMsg中存放著錯誤碼和錯誤資訊 * -------------------------------------------------------------------------------------- * 用法： *array<tResult^>^ m_result; *int i; *tRow^pRow = nullptr; * *m_result = gcnew array<tResult^>(4); *for(i=0;i<4;i++) *m_result[i] = gcnew tResult(); *m_bRet = m_sql->Select2(m_iRet,m_sRet, *" select FCITYPEID,FCIBASETYPE,FCINAME,FCIREMARK from TCI_TYPEBASEINFO ",pRow, *m_result[0],m_result[1],m_result[2],m_result[3]); * *Select的結果用tResult儲存，可以轉換為int,double,System::String等類型 * --------------------------------------------------------------------------------------*/bool tsql::Select2(int ^%iRetCode,// 輸出的錯誤碼  System::String ^% sRetMsg,// 輸出的錯誤資訊  System::String^sSql,// SQL語句  tRow ^%pRow,// 中間結果集變數,初始化為nullptr，使用中可以不管  ... array<tResult ^>^ pOutArray// 輸出的變數集合  ){boolm_bRet;intm_iRet;System::String^ m_sRet;char*pStr;int iStr;m_sRet = L"";m_iRet = 0;m_bRet = true;pStr = NULL;iStr = 0;f_StringToChar(sSql,&pStr,&iStr);/*---------------------------------------------------------------------------------*/if (m_bIsConnected == false){m_sRet = L"已經斷開資料庫";m_bRet = false;m_iRet = -1;goto L_RET;}/*---------------------------------------------------------------------------------*/int rc;rc = mysql_query(m_mysql,pStr);if (rc){m_iRet = mysql_errno(m_mysql);m_sRet = gcnew System::String(mysql_error(m_mysql));m_bRet = false;goto  L_RET;}m_bRet = FetchRow(iRetCode,m_sRet,pRow);if (m_bRet == false){m_iRet = *iRetCode;goto L_RET;}ProcessRow(pRow,pOutArray);L_RET:if (pStr)delete [] pStr;iRetCode = m_iRet;sRetMsg = m_sRet;return m_bRet;}/* -------------------------------------------------------------------------------------- * ProcessRow * 將FetchRow的資訊填寫到變數。 * 參數： * pRow:中間變數，用於記錄中間結果集 * ...:輸出的變數集合 * 返回：無 * -------------------------------------------------------------------------------------- * 用法： *ProcessRow(pRow,pOutArray); *m_sql->ProcessRow2(pRow,m_result[0],m_result[1],m_result[2],m_result[3]); * --------------------------------------------------------------------------------------*/void tsql::ProcessRow(tRow ^ pRow,// 中間結果集變數,初始化為nullptr，使用中可以不管  array<tResult ^>^ pOutArray// 輸出的變數集合  ){ProcessRow(pRow->result,pRow->row,pOutArray);}void tsql::ProcessRow2(tRow ^ pRow// 中間結果集變數,初始化為nullptr，使用中可以不管   ,...array<tResult ^>^ pOutArray// 輸出的變數集合   ){ProcessRow(pRow->result,pRow->row,pOutArray);}void tsql::ProcessRow(MYSQL_RES *result,// result記錄  MYSQL_ROW row,// 行記錄  array<tResult ^>^ pOutArray// 輸出的變數集合  ){int i;int iNumFields;MYSQL_FIELD *field;int iLen;iNumFields = mysql_num_fields(result);for(i=0;i<iNumFields;i++){if (i>=pOutArray->Length)break;field = mysql_fetch_field_direct(result,i);if (field == NULL)break;if (field->max_length == 0)iLen = field->length;elseiLen = field->max_length;pOutArray[i]->pUnsigned = new unsigned char [iLen];memcpy(pOutArray[i]->pUnsigned,row[i],iLen);switch(field->type){case MYSQL_TYPE_DECIMAL:case MYSQL_TYPE_TINY:case MYSQL_TYPE_SHORT:case MYSQL_TYPE_LONG:case MYSQL_TYPE_LONGLONG:case MYSQL_TYPE_INT24:case MYSQL_TYPE_NEWDECIMAL:pOutArray[i]->po = gcnew int(atoi(row[i]));break;case MYSQL_TYPE_FLOAT:case MYSQL_TYPE_DOUBLE:pOutArray[i]->po = gcnew double(atof(row[i]));break;case MYSQL_TYPE_TIMESTAMP:case MYSQL_TYPE_DATE:case MYSQL_TYPE_TIME:case MYSQL_TYPE_DATETIME:case MYSQL_TYPE_YEAR:case MYSQL_TYPE_NEWDATE:case MYSQL_TYPE_BIT:case MYSQL_TYPE_VARCHAR:case MYSQL_TYPE_VAR_STRING:case MYSQL_TYPE_STRING:case MYSQL_TYPE_ENUM:pOutArray[i]->po = gcnew System::String(row[i]);break;case MYSQL_TYPE_NULL:default:pOutArray[i]->po = gcnew System::String("");break;}}}/* -------------------------------------------------------------------------------------- * FetchRow * 獲得一行資訊。 * 參數： * iRetCode:傳回值 * sRetMsg:返回資訊 * pRow:獲得的行資訊 * 返回：true: 獲得行成功. *false:獲得行失敗，iRetCode，sRetMsg中存放著錯誤碼和錯誤資訊 * 如果有多個結果集，會自動取得下一個結果集，直到取到行，或者取完。 * -------------------------------------------------------------------------------------- * 用法： *m_bRet = m_sql->FetchRow(m_iRet,m_sRet,pRow); * --------------------------------------------------------------------------------------*/bool tsql::FetchRow(int ^% iRetCode,System::String ^% sRetMsg,tRow ^% pRow){boolm_bRet;intm_iRet;System::String^ m_sRet;m_sRet = L"";m_iRet = 0;m_bRet = true;/*---------------------------------------------------------------------------------*/if (m_bIsConnected == false){m_sRet = L"已經斷開資料庫";m_bRet = false;m_iRet = -1;goto L_RET;}/*---------------------------------------------------------------------------------*/MYSQL_RES *result;MYSQL_ROW row;MYSQL_FIELD *field;int rc;int iNumFields;int i;while (1){if (pRow == nullptr){result = mysql_use_result(m_mysql);// 取結果集while (result == NULL)// 如果沒有結果集，就下一個結果集{L_NEXTRESULT:rc = mysql_next_result(m_mysql);if (rc){m_iRet = -1;m_sRet = L"沒有結果集返回!";m_bRet = false;if (pRow != nullptr){pRow->result = NULL;delete pRow;}pRow = nullptr;goto L_RET;}result = mysql_use_result(m_mysql);}}elseresult = pRow->result;row = mysql_fetch_row(result);// 取行if (row == NULL){mysql_free_result(result);goto L_NEXTRESULT;}break;}if (pRow == nullptr){pRow = gcnew tRow(result,row);}else{pRow->result = result;pRow->row = row;}/*---------------------------------------------------------------------------------*/L_RET:iRetCode = m_iRet;sRetMsg = m_sRet;return m_bRet;}};

寫完這個封裝忽然忘記自己要幹啥了，為啥要封裝這個....