用于CLR模式的mysql简单封装完成

最新推荐文章于 2023-12-29 15:34:19 发布

puretech_

最新推荐文章于 2023-12-29 15:34:19 发布

阅读量1.5k

点赞数

分类专栏： mysql CLR 文章标签： mysql string tsql system 数据库 delete

本文链接：https://blog.csdn.net/puretech_/article/details/8026532

版权

mysql 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

CLR

5 篇文章 0 订阅

订阅专栏

解决了绑定变量问题后，剩下的事情就简单多了。

设想的工作模式是这样的：

目的：解决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;
	bool	m_bRet;
	int		i;
	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. 设定字符集为gbk
	m_bRet = m_sql->Execute(m_iRet,m_sRet," set names gbk");
	Console::WriteLine("set names gbk:result:{0},{1}",m_iRet,m_sRet);
	// 4. 执行单行SELECT
	m_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	// 返回信息
					 )
{
	bool	m_bRet;
	int		m_iRet;
	System::String^ m_sRet;
	int		rc;
	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	// 返回信息
						)
{
	bool	m_bRet;
	int		m_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语句
{
	bool	m_bRet;
	int		m_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	// 输出的变量集合
				  )
{
	bool	m_bRet;
	int		m_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_result
L_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	// 输出的变量集合
				  )
{
	bool	m_bRet;
	int		m_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;
		else
			iLen = 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)
{
	bool	m_bRet;
	int		m_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);
			}
		}
		else
			result = 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;
}

};

写完这个封装忽然忘记自己要干啥了，为啥要封装这个....