Simple MySQL encapsulation for CLR Mode

After the variable binding problem is solved, the rest is much simpler.

The expected working mode is as follows:

Objective: To solve common MySQL access problems.

It is often used to select a single row and obtain multiple rows.

The Select statements of multiple records are usually the same as those of fields and can have one or more result sets.

Access to result sets of multiple different fields is rare and can be controlled by a Write Program.

Means:

The variable parameter can be used to obtain the result... (it seems that C # does not have this? Instead, you can first define all the returned results as the tresult class, then obtain the results, and then convert them to the desired type.

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

When designing the tresult class, you can retain the original data, that is, tresult. punsigned is the result of the original Mysql Data.

The entire traversal process:

Execute (Execute SQL statement) while (fetchrow = true) {processrow (obtain result) fetchrow}

That is, first execute the SQL statement, and then fetch. If there is a record, then process the record and then fetch the next line.

The test code is as follows:

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. create an SQL instance m_ SQL = gcnew tsql (); // 2. connect to the database 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. set the character set to 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. run the single row 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, and processrow are used to traverse multiple records 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. use select2, fetchrow, and processrow to traverse multiple records 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. disable the database m_bret = m_ SQL-> disconnectdb (m_iret, m_sret); Console: writeline ("Disconnect result: {0 },{ 1}", m_iret, m_sret ); m_sret = console: Readline ();}

Tsql is implemented as follows:

// 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 * connects to the MySQL database. * Parameter: * sserver: MySQL service address * suser: User name * spasswd: password * sdatabase: database name * iport: MySQL server port * iretcode: Return Value * sretmsg: return information * return: true: the database is connected successfully. * false: failed to connect to the database. iretcode and sretmsg contain error codes and error messages. * optional * usage: * 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 Server Name System: String ^ suser, // MySQL user name system: String ^ spasswd, // MySQL password system: String ^ sdatabase, // MySQL database int iport, // MySQL port int ^ % iretcode, // return value system :: string ^ % sretmsg // return information) {boolm_bret; intm_iret; System: String ^ m_sret; intrc; MySQL * M_t; m_sret = l ""; m_iret = 0; m_bret = true ;/*-- Optional */If (m_bisconnected = true) {m_sret = l "connected to Database"; 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), (C Onst 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;}/* restart ;;}/*------------------------------------------------------------------------------------- -* Disconnectdb * disconnect the MySQL database. * Parameter: * iretcode: Return Value * sretmsg: return information * return: true: Database disconnection successful. * false: Database disconnection fails. iretcode and sretmsg stores error codes and error messages. * optional * usage: * m_bret = m_ SQL-> disconnectdb (m_iret, m_sret ); * optional */bool tsql: disconnectdb (INT ^ % iretcode, // return value system: String ^ % sret MSG // return information) {boolm_bret; intm_iret; System: String ^ m_sret; m_sret = l ""; m_iret = 0; m_bret = true; /* optional */If (m_bisconnected = false) {m_sret = l "database disconnected"; goto l_ret;} mysql_close (m_mysql); m_mysql = NULL; m_bisconnected = false; /* --------------------------------------------------------------------------------- */l_ret: iretcode = M_iret; sretmsg = m_sret; return m_bret;}/* convert * f_stringtochar * system: string to char array * parameter: * pstr: System: string * pout: the pointer to the output char pointer * pisize: The size after the output conversion * returns the size after the conversion. * Usage *: * memory needs to be allocated because the char pointer is converted. If it is allocated by the caller, the caller may not know the actual size. * Two compatibility methods are used here. If the caller is not assigned, the caller is assigned by this function and the caller is released. If * is allocated by the caller and the size is sufficient, the caller's pointer is directly used. If the size is insufficient, this function is allocated and the caller is released. * Char * pstr = NULL; * int istr = 0; * f_stringtochar (ssql, & pstr, & istr); * // dosomething, * f_stringtochar (ssql, & pstr, & istr); // pstr and istr are not required here, and will be automatically adjusted in the function *//... * If (pstr) // finally releases the memory * Delete [] pstr; * bytes */INT tsql: f_stringtochar (System: String ^ pstr, // the system to be converted:: String char ** pout, // converted char array int * pisize )/ /The size of the conversion. {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) N Ewsize ;}} wcstombs_s (& convertedchars, * pout, origsize, wch, _ truncate); Return newsize;}/* execute * to execute the SQL statement. After execution, you can also use fetchrow and processrow to process the result set. * Parameter: * iretcode: Return Value * sretmsg: return information * ssql: SQL statement * return: true: Execution successful. * false: execution fails. iretcode and sretmsg contain error codes and error messages. * optional * usage: * m_bret = m_ SQL-> execute (m_iret, m_sret, "Set names GBK"); * optional */bool tsql: Execute (INT ^ % iretcode, // output error code sys TEM: String ^ % sretmsg, // output error message system: String ^ ssql) // SQL statement {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 ); /* disconnect */If (m_bisconnected = false) {m_sret = l "database disconnected"; m_bret = false; m_iret =-1; goto l_ret ;}/*------ Optional */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;}/* success */l_ret: If (pstr) Delete [] pstr; iretcode = m_iret; sretmsg = m_sret; return m_bret ;}/* Explain * select * executes the SELECT statement for a single row. * Parameter: * iretcode: returned value * sretmsg: returned information * ssql: SQL statement *...: output variable set * return: true: Execution successful. * false: execution fails. iretcode and sretmsg contain error codes and error messages. * optional * usage: * 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 (*) fro M tci_typebaseinfo ", m_result [0]); ** Save the select result with tresult, which can be converted to int, double, system: string, and Other types * optional */bool tsql :: select (INT ^ % iretcode, // output error code system: String ^ % sretmsg, // output error message system: String ^ ssql, // SQL statement... array <tresult ^> ^ poutarray // set of output variables) {boolm_bret; intm_iret; System: String ^ m_sret; char * pstr; int istr; M_s Ret = l ""; m_iret = 0; m_bret = true; pstr = NULL; istr = 0; f_stringtochar (ssql, & pstr, & istr ); /* disconnect */If (m_bisconnected = false) {m_sret = l "database disconnected"; 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; // display release prow, the purpose is to execute mysql_free_resultl_ret: If (pstr) Delete [] pstr; iretcode = m_iret; sretmsg = m_sret; return m_bret ;}/*--------------------------------------- --------------------------------------------- * Select2 * executes the SELECT statement returned by a single row. * Parameter: * iretcode: Return Value * sretmsg: return information * prow: Intermediate variable, used to record intermediate result set * ssql: SQL statement *...: output variable set * return: true: Execution successful. * false: execution fails. iretcode and sretmsg contain error codes and error messages. * optional * usage: * 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_s QL-> 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]); ** the select result is saved using tresult and can be converted to int, double, system: string, and Other types * optional */bool tsql:: select2 (INT ^ % iretcode, // output error code system: String ^ % sretmsg, // output error message system: strin G ^ ssql, // SQL statement Trow ^ % prow, // intermediate result set variable, initialized to nullptr, can be used regardless... array <tresult ^> ^ poutarray // set of output variables) {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 "database disconnected"; 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;}/* optional * processrow * fill in the fetchrow information to the variable. * Parameter: * prow: Intermediate variable, used to record intermediate result set *...: output variable set * return: No * optional * usage: * processrow (prow, poutarray); * m_ SQL-> processrow2 (prow, m_result [0], m_result [1], m_result [2], m_result [3]); * optional */void tsql: processrow (Trow ^ prow, // intermediate result set variable, initialized to nullptr, in use Regardless of the array <tresult ^> ^ poutarray // set of output variables) {processrow (prow-> result, prow-> row, poutarray);} void tsql :: processrow2 (Trow ^ prow // intermediate result set variable, initialized to nullptr, can be ignored in use ,... array <tresult ^> ^ poutarray // set of output variables) {processrow (prow-> result, prow-> row, poutarray);} void tsql :: processrow (mysql_res * result, // Result Records mysql_row row, // Row Records array <tresult ^> ^ poutarray // a set of output variables) {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 when: poutarray [I]-> po = gcnew int (atoi (row [I]); break; case when: Case mysql_type_double: poutarray [I]-> po = gcnew double (atof (row [I]); break; case when: Case mysql_type_date: Case mysql_type_time: Case mysql_type_datetime: case mysql_type_year: Case mysql_type_ne Wdate: Case mysql_type_bit: case when: 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 ;}}/ * Rows * fetchrow * to obtain a row of information. * Parameter: * iretcode: returned value * sretmsg: returned information * prow: Row information * returned: true: Row succeeded. * false: the row fails to be obtained. iretcode and sretmsg contain error codes and error messages. * if multiple result sets exist, the next result set is automatically obtained until the row is obtained or the result set is completed. * Optional * usage: * m_bret = m_ SQL-> fetchrow (m_iret, m_sret, prow); * optional */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 ;/*-------- Optional */If (m_bisconnected = false) {m_sret = l "database disconnected"; m_bret = false; m_iret =-1; goto l_ret ;} /* optional */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); // obtain the result set While (result = NULL) // If no result set exists, the next result set {l_nextresult: rc = mysql_next_result (m_mysql); If (RC) {m_iret =-1; m_sret = l "no result set is returned! "; 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); // obtain the row 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;}/* cursor */l_ret: iretcode = m_iret; sretmsg = m_sret; return m_bret ;}};

After writing this encapsulation, I suddenly forgot what I was doing. Why did I encapsulate this ....