www.pudn.com > ODBCApi.rar > DbOperator.cpp

#include "StdAfx.h" 
#include ".\dboperator.h" 
CDbOperator::CDbOperator(void) 
{ 
	m_strDSN = "DbMarket"; 
	m_strUSER = "hskj"; 
	m_strPWD = "newtech"; 
 
	henv = SQL_NULL_HANDLE; 
	hdbc = SQL_NULL_HANDLE; 
 
	m_bLink = FALSE; 
	OpenDatabase(); 
} 
 
CDbOperator::~CDbOperator(void) 
{ 
	if(m_bLink) 
	{ 
		SQLDisconnect(hdbc); 
		SQLFreeHandle(SQL_HANDLE_DBC, hdbc);  
		SQLFreeHandle(SQL_HANDLE_ENV, henv); 
		m_bLink = FALSE; 
	} 
} 
 
CDbOperator::CDbOperator(CString strDSN, CString strUSER, CString strPWD) 
{	 
	henv = SQL_NULL_HANDLE; 
	hdbc = SQL_NULL_HANDLE; 
 
	m_strDSN = strDSN; 
	m_strUSER = strUSER; 
	m_strPWD = strPWD; 
 
	m_bLink = FALSE; 
	OpenDatabase(); 
} 
 
BOOL CDbOperator::OpenDatabase() 
{ 
	SQLINTEGER cbLenth = 0 ;	 
	SQLRETURN retcode; 
 
	retcode = SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN,"SQL Server","DSN=DbMarket\0Description=odbctest\0Server=(local)\0Database=DbMarket\0\0"); 
	if(!retcode) 
	{ 
		AfxMessageBox("系统数据源配置失败!"); 
		return FALSE; 
	} 
 
	retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv) ; 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{ 
		retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);  
		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
		{ 
			retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc); 
			if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
			{ 
				retcode = SQLConnect(hdbc, (SQLCHAR*)(LPCTSTR)m_strDSN, SQL_NTS, (SQLCHAR*)(LPCTSTR)m_strUSER, SQL_NTS,  
					(SQLCHAR*)(LPCTSTR)m_strPWD, SQL_NTS); 
				if (!(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)) 
				{ 
					AfxMessageBox("数据库连接失败!") ; 
					SQLFreeHandle(SQL_HANDLE_DBC, hdbc);  
					SQLFreeHandle(SQL_HANDLE_ENV, henv); 
					return FALSE; 
				} 
			}  
			else 
			{ 
				AfxMessageBox("连接句柄分配出错") ; 
				SQLFreeHandle(SQL_HANDLE_DBC, hdbc);  
				SQLFreeHandle(SQL_HANDLE_ENV, henv); 
				return FALSE; 
			} 
		} 
		else 
		{ 
			AfxMessageBox("属性设置出错！") ; 
			SQLFreeHandle(SQL_HANDLE_ENV, henv); 
			return FALSE; 
		} 
	} 
	else 
	{ 
		AfxMessageBox("环境变量分配出错！") ; 
		SQLFreeHandle(SQL_HANDLE_ENV, henv); 
		return FALSE; 
	} 
	m_bLink = TRUE; 
	return TRUE; 
} 
 
BOOL CDbOperator::IsTableExisted(CString strTableName) 
{ 
	SQLHSTMT hstmt ; 
	SQLRETURN retcode; 
	SQLINTEGER cbLenth = 0 ; 
 
	CString strSQL; 
	strSQL.Format("SELECT * FROM sysobjects WHERE name='%s'", strTableName); 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{	 
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR) 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
			return FALSE; 
		} 
		if((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO)) 
		{		 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
			return  TRUE; 
		} 
		else 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
			return FALSE; 
		}			 
	} 
	else 
	{	 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
		return FALSE;	 
	} 
} 
 
BOOL CDbOperator::CreateTable(CString strSQL) 
{ 
	SQLHSTMT hstmt ; 
	SQLRETURN retcode; 
	SQLINTEGER cbLenth = 0 ; 
 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{	 
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) != SQL_ERROR) 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
			return TRUE; 
		} 
		else 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
			return FALSE; 
		}			 
	} 
	else 
	{	 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
		return FALSE;	 
	}	 
} 
 
BOOL CDbOperator::AlterTable(CString strSQL) 
{ 
	SQLHSTMT hstmt ; 
	SQLRETURN retcode; 
	SQLINTEGER cbLenth = 0 ; 
 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{	 
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) != SQL_ERROR) 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
			return TRUE; 
		} 
		else 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
			return FALSE; 
		}			 
	} 
	else 
	{	 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
		return FALSE;	 
	} 
} 
 
BOOL CDbOperator::DropTable(CString strSQL) 
{ 
	SQLHSTMT hstmt ; 
	SQLRETURN retcode; 
	SQLINTEGER cbLenth = 0 ; 
 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{	 
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) != SQL_ERROR) 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
			return TRUE; 
		} 
		else 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
			return FALSE; 
		}			 
	} 
	else 
	{	 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
		return FALSE;	 
	} 
} 
 
BOOL CDbOperator::InitAllTable() 
{ 
	CStdioFile file; 
	CFileException fileException; 
	try 
	{ 
		file.Open("Database.txt", CFile::modeRead | CFile::typeText, &fileException ); 
	} 
	catch(CFileException* pEx) 
	{ 
		char szError[1024]; 
		ZeroMemory(szError, 1024); 
		pEx->GetErrorMessage(szError, 1024); 
		AfxMessageBox(szError); 
		return FALSE; 
	} 
	CString strSQL; 
	CString strTableName; 
	BOOL bIsTableExisted; 
	BOOL bIsCreate; 
	while(file.ReadString(strSQL)) 
	{ 
		if(GetTableNameFromSql(strSQL, strTableName)) 
		{ 
			bIsTableExisted = IsTableExisted(strTableName); 
			if(!bIsTableExisted) 
			{ 
				bIsCreate = CreateTable(strSQL); 
				if(!bIsCreate) 
				{ 
					AfxMessageBox("创建表失败！"); 
					return FALSE; 
				} 
			} 
			else 
			{ 
				//更新数据库，但暂不做更新 
			} 
		} 
	} 
	AfxMessageBox("创建表成功！"); 
	file.Close(); 
	//插入初始化表内容 
	return TRUE; 
} 
 
 
BOOL CDbOperator::InsertContent(CString strSQL) 
{ 
	SQLHSTMT hstmt ; 
	SQLRETURN retcode; 
	SQLINTEGER cbLenth = 0 ; 
 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{	 
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) != SQL_ERROR) 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
			return TRUE; 
		} 
		else 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
			return FALSE; 
		}			 
	} 
	else 
	{	 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
		return FALSE;	 
	} 
} 
BOOL CDbOperator::InitTableContent() 
{ 
	CStdioFile file; 
	CFileException fileException; 
	try 
	{ 
		file.Open("TableContent.txt", CFile::modeRead | CFile::typeText, &fileException ); 
	} 
	catch(CFileException* pEx) 
	{ 
		char szError[1024]; 
		ZeroMemory(szError, 1024); 
		pEx->GetErrorMessage(szError, 1024); 
		AfxMessageBox(szError); 
		return FALSE; 
	} 
	CString strSQL; 
	while(file.ReadString(strSQL)) 
	{ 
		BOOL bIsInsert = InsertContent(strSQL); 
		if(!bIsInsert) 
		{ 
			AfxMessageBox("插入失败"); 
			return FALSE; 
		} 
		else 
		{ 
			//更新数据库，但暂不做更新 
		} 
	} 
	file.Close(); 
	return TRUE; 
} 
 
BOOL CDbOperator::GetTableNameFromSql(CString strSQL, CString& strTableName) 
{ 
	//创建表的格式都是固定的模式：CREATE TABLE [TableName] ... 
	int nTableBegin = strSQL.Find("["); 
	if(nTableBegin == -1) 
	{ 
		AfxMessageBox("没有找到相应的表头创建格式"); 
		return FALSE; 
	} 
	int nTableEnd = strSQL.Find(']'); 
	strTableName = strSQL.Mid(nTableBegin+1, nTableEnd-nTableBegin-1); 
	return TRUE; 
} 
 
BOOL CDbOperator::GetUserIdFromObj_User(int arrUserId[], int& nUserCount) 
{ 
	int nUserId = 0; 
	nUserCount = 0; 
	CString strSQL; 
 
	SQLHSTMT hstmt ; 
	SQLRETURN retcode; 
	SQLINTEGER cbLenth = 0 ; 
	strSQL = "SELECT User_Iden FROM Obj_User"; 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{	 
		SQLBindCol(hstmt, 1, SQL_C_ULONG, (SQLPOINTER)&nUserId, sizeof(nUserId), &cbLenth);	 
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR) 
		{ 
			AfxMessageBox("数据操作失败！") ; 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
			return FALSE; 
		} 
		while((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO)) 
		{ 
			arrUserId[nUserCount] = nUserId; 
			nUserCount++; 		 
		} 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
		return  TRUE;		 
	} 
	else 
	{	 
		AfxMessageBox("数据操作失败！") ; 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
		return FALSE;	 
	} 
	return TRUE; 
} 
 
BOOL CDbOperator::GetNinaByUserId(int nUserId, CString& strNickname) 
{ 
	SQLHSTMT hstmt ; 
	SQLRETURN retcode; 
	SQLINTEGER cbLenth = 0 ; 
	char cNickname[100]; 
	ZeroMemory(cNickname, 100); 
 
	CString strSQL; 
	strSQL.Format("SELECT User_Nina FROM Obj_User WHERE User_Iden=%d", nUserId); 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{	 
		SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)cNickname, 100, &cbLenth);	 
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR) 
		{ 
			AfxMessageBox("数据操作失败！") ; 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
			return FALSE; 
		} 
		if((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO)) 
		{		 
			strNickname = cNickname; 
		} 
		else 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
			return FALSE; 
		} 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
		return  TRUE;		 
	} 
	else 
	{	 
		AfxMessageBox("获取电业局数据操作失败！") ; 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
		return FALSE;	 
	} 
} 
 
BOOL CDbOperator::GetUserpwdByUsernina(CString strUsernina, CString& strUserpwd) 
{ 
	SQLHSTMT hstmt ; 
	SQLRETURN retcode; 
	SQLINTEGER cbLenth = 0 ; 
	char cUserpwd[100]; 
	ZeroMemory(cUserpwd, 100); 
 
	CString strSQL; 
	strSQL.Format("SELECT User_Pawo FROM Obj_User WHERE User_Nina='%s'", strUsernina); 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{	 
		SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)cUserpwd, 100, &cbLenth);	 
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR) 
		{ 
			AfxMessageBox("数据操作失败！") ; 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
			return FALSE; 
		} 
		if((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO)) 
		{		 
			strUserpwd = cUserpwd; 
		} 
		else 
		{ 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
			return FALSE; 
		} 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
		return  TRUE;		 
	} 
	else 
	{	 
		AfxMessageBox("获取电业局数据操作失败！") ; 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
		return FALSE;	 
	} 
	return TRUE; 
} 
 
BOOL CDbOperator::GetAllUserTable(CString strTableName[], int& nTableCount) 
{ 
	SQLHSTMT hstmt ; 
	SQLRETURN retcode; 
	SQLINTEGER cbLenth = 0 ; 
	char cName[100]; 
	ZeroMemory(cName, 100); 
	nTableCount = 0; 
 
	CString strSQL; 
	strSQL.Format("SELECT name FROM sysobjects WHERE xtype='%s'", "U"); 
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);	 
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)  
	{	 
		SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER)cName, 100, &cbLenth);	 
		if (SQLExecDirect(hstmt, (SQLCHAR*)(LPCTSTR)strSQL, SQL_NTS) == SQL_ERROR) 
		{ 
			AfxMessageBox("数据操作失败！") ; 
			SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
			return FALSE; 
		} 
		while((SQLFetch(hstmt) == SQL_SUCCESS) || (SQLFetch(hstmt) == SQL_SUCCESS_WITH_INFO)) 
		{		 
			strTableName[nTableCount] = cName; 
			nTableCount++; 
		} 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt);	 
		return  TRUE;		 
	} 
	else 
	{	 
		AfxMessageBox("获取电业局数据操作失败！") ; 
		SQLFreeHandle(SQL_HANDLE_STMT, hstmt); 
		return FALSE;	 
	} 
	return TRUE; 
}