db.cpp

用ODBC 游标进行分页 数据源CMSTest

// DB.cpp: implementation of the DB class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "FetchFenYeTest.h"
#include "DB.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

DB::DB()
{

}

DB::~DB()
{

}
LONG DB::DBconnect(){
	//分配环境句柄
	r=SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnv);
	if(r!=SQL_SUCCESS){
		//AfxMessageBox(_T("分配环境句柄失败"));
		return 300876;
	}
	//设置环境句柄
	r=SQLSetEnvAttr(hEnv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
	if(r!=SQL_SUCCESS){
		//AfxMessageBox(_T("设置环境句柄失败"));
		return 300877;
	}
	//分配连接句柄
	r=SQLAllocHandle(SQL_HANDLE_DBC,hEnv,&hConnect);
	if(r!=SQL_SUCCESS){
		//AfxMessageBox(_T("分配连接句柄失败"));
		return 300876;
	}
	//连接ODBC数据库
	r=SQLConnect(hConnect,(SQLCHAR*)"CMSTest",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"",SQL_NTS);
	if (r!=SQL_SUCCESS&&r!=SQL_SUCCESS_WITH_INFO)
	{
		//AfxMessageBox(_T("连接ODBC数据库失败"));
		return 300878;
	}
	//数据库成功连接
	//打开事务
	SQLSetConnectAttr(hConnect,SQL_ATTR_AUTOCOMMIT,(void*)SQL_AUTOCOMMIT_OFF,SQL_IS_UINTEGER);
	return 0;

}
LONG DB :: Disconnect()
{
	//关闭事务
	SQLEndTran(SQL_HANDLE_DBC,hConnect,SQL_COMMIT);
	r=SQLDisconnect(hConnect);//函数断开连接
	if(r!=SQL_SUCCESS){
		//AfxMessageBox(_T("断开连接失败"));
		return 300879;
	}	
	r=SQLFreeConnect(hConnect);//解除连接
	if(r!=SQL_SUCCESS){
		//AfxMessageBox(_T("解除连接失败"));
		return 300880;
	}
	r=SQLFreeEnv(hEnv);//解除环境句柄
	if(r!=SQL_SUCCESS){
		//AfxMessageBox(_T("解除环境句柄失败"));
		return 300881;
	}
	return 0;
}
LONG DB::ExecSQL(int numberPage,int noncePage,CObArray& areaBeanList){
	SQLINTEGER areaIDs,areaNames;
	SQLCHAR areaID[513],areaName[513];
	TCHAR szSql[1024];

	
	//分配句柄
	r = SQLAllocHandle ( SQL_HANDLE_STMT, hConnect, &hstmt); 
	r = SQLSetStmtAttr(hstmt, SQL_ATTR_CURSOR_TYPE,
                  (SQLPOINTER)SQL_CURSOR_DYNAMIC,
                  SQL_IS_INTEGER);
	
	if (r!=SQL_SUCCESS)
	{
		//AfxMessageBox(_T("分配句柄错误"));
		SQLFreeStmt(hstmt,SQL_CLOSE);
		SQLEndTran(SQL_HANDLE_DBC,hConnect,SQL_ROLLBACK);
		return -1;
	}
	
	swprintf (szSql,_T("SELECT C_AREAID,C_AREANAME FROM AREA"));//分配SQL语句
	r = SQLExecDirectW( hstmt, szSql, SQL_NTS );//进行数据查询
	
	if (r != SQL_SUCCESS && r!= SQL_SUCCESS_WITH_INFO)
	{
		SQLFreeStmt(hstmt,SQL_CLOSE);
		SQLEndTran(SQL_HANDLE_DBC,hConnect,SQL_ROLLBACK);
		return -1;
	}
	r = SQLBindCol ( hstmt, 1,SQL_C_CHAR , (SQLPOINTER) &areaID, 
		sizeof(areaID), & areaIDs); 
	r = SQLBindCol ( hstmt, 2,SQL_C_CHAR , (SQLPOINTER) &areaName, 
		sizeof(areaName), & areaNames); //绑定数据
	r = SQLFetchScroll(hstmt,SQL_FETCH_ABSOLUTE,(noncePage-1)*numberPage);
	int i=0;
	while(SQLFetch(hstmt) != SQL_NO_DATA)
	{
		if (i==numberPage)
		{
			return 0;
		}//*/
		CCAreaBean* areaBean=new CCAreaBean();
		areaBean->setAreaID(areaID);
		areaBean->setAreaName(areaName);
		areaBeanList.Add(areaBean);
		i++;
	}
	return 0;
}