myodbcsmplview.cpp

实例有关说明： 数据源：本实例使用的数据源在本章实例MyOdbcSmpl目录中

// MyOdbcSmplView.cpp : implementation of the CMyOdbcSmplView class
//

#include "stdafx.h"
#include "MyOdbcSmpl.h"

#include "MyOdbcSmplDoc.h"
#include "MyOdbcSmplView.h"
#include "TableSet.h"
#include "ODBCDynamic.h"
#include "ChildFrm.h"

#define SEL_LIST_LEN	1000
#define SEL_TAB_LEN		20
#define SEL_CON_LEN		200

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

/////////////////////////////////////////////////////////////////////////////
// CMyOdbcSmplView

IMPLEMENT_DYNCREATE(CMyOdbcSmplView, CFormView)

BEGIN_MESSAGE_MAP(CMyOdbcSmplView, CFormView)
	//{{AFX_MSG_MAP(CMyOdbcSmplView)
	ON_BN_CLICKED(IDC_QUERY, OnQuery)
	ON_CBN_CLOSEUP(IDC_DSN_COMBO, OnCloseupDsnCombo)
	ON_COMMAND(ID_FILE_CONNECT, OnFileConnect)
	ON_BN_CLICKED(IDC_CONNECT, OnConnect)
	ON_UPDATE_COMMAND_UI(ID_VIEW_SHOWRES, OnUpdateViewShowres)
	ON_COMMAND(ID_VIEW_SHOWRES, OnViewShowres)
	ON_BN_CLICKED(IDC_BUTTON1, OnButton1)
	//}}AFX_MSG_MAP
	// Standard printing commands
	ON_COMMAND(ID_FILE_PRINT, CFormView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_DIRECT, CFormView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_PREVIEW, CFormView::OnFilePrintPreview)
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CMyOdbcSmplView construction/destruction

CMyOdbcSmplView::CMyOdbcSmplView()
	: CFormView(CMyOdbcSmplView::IDD)
{
	//{{AFX_DATA_INIT(CMyOdbcSmplView)
	m_strDsn = _T("");
	m_strCondition = _T("");
	m_strSqlStmt = _T("");
	m_strDB = _T("");
	//}}AFX_DATA_INIT
	// TODO: add construction code here

}

CMyOdbcSmplView::~CMyOdbcSmplView()
{
}

void CMyOdbcSmplView::DoDataExchange(CDataExchange* pDX)
{
	CFormView::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CMyOdbcSmplView)
	DDX_Control(pDX, IDC_REC_LIST, m_reclist);
	DDX_Control(pDX, IDC_SEL_LIST, m_list);
	DDX_Control(pDX, IDC_DSN_COMBO, m_dsn_combo);
	DDX_CBString(pDX, IDC_DSN_COMBO, m_strDsn);
	DDX_Text(pDX, IDC_SEL_CONDITION, m_strCondition);
	DDX_Text(pDX, IDC_SQLSTMT, m_strSqlStmt);
	DDX_Text(pDX, IDC_DB, m_strDB);
	//}}AFX_DATA_MAP
}


BOOL CMyOdbcSmplView::_InitQueryView()
{
	//获得数据库表信息
	CTableSet* pTabSet = &GetDocument()->m_tabset;
	try
	{
		if (!pTabSet->IsOpen())
		{
			// 获取数据源表或视图记录集
			pTabSet->Open();
			if( !pTabSet->IsOpen())return FALSE;
			if(!pTabSet->IsBOF())pTabSet->MoveFirst();
			int i = 0;
			while( !pTabSet->IsEOF())
			{
				// 在下拉列表框中，显示表或视图信息
				m_dsn_combo.InsertString(i++,pTabSet->m_strName);
				pTabSet->MoveNext();
			}
			pTabSet->Close();
			
		}
	}
	catch( CDBException* e )
	{
		e->ReportError(MB_ICONEXCLAMATION);
		return FALSE;
	}	
	return TRUE;
}

void CMyOdbcSmplView::OnInitialUpdate()
{
	CFormView::OnInitialUpdate();
	ResizeParentToFit();
	GetDlgItem(IDC_DSN_COMBO)->EnableWindow(FALSE);
	GetDlgItem(IDC_SEL_CONDITION)->EnableWindow(FALSE);
	GetDlgItem(IDC_REC_LIST)->EnableWindow(FALSE);
	GetDlgItem(IDC_SEL_LIST)->EnableWindow(FALSE);
	GetDlgItem(IDC_QUERY)->EnableWindow(FALSE);

}
////////////////////////////////////////////////////////////////////////////
// CMyOdbcSmplView printing

BOOL CMyOdbcSmplView::OnPreparePrinting(CPrintInfo* pInfo)
{
	return DoPreparePrinting(pInfo);
}

void CMyOdbcSmplView::OnBeginPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
}

void CMyOdbcSmplView::OnEndPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
}

void CMyOdbcSmplView::OnPrint(CDC* pDC, CPrintInfo* /*pInfo*/)
{
}

/////////////////////////////////////////////////////////////////////////////
// CMyOdbcSmplView diagnostics

#ifdef _DEBUG
void CMyOdbcSmplView::AssertValid() const
{
	CFormView::AssertValid();
}

void CMyOdbcSmplView::Dump(CDumpContext& dc) const
{
	CFormView::Dump(dc);
}

CMyOdbcSmplDoc* CMyOdbcSmplView::GetDocument() // non-debug version is inline
{
	ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CMyOdbcSmplDoc)));
	return (CMyOdbcSmplDoc*)m_pDocument;
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CMyOdbcSmplView message handlers

void CMyOdbcSmplView::OnQuery() 
{
	// 清除上一次查询列表显示
	_ResetDataListControl();
	
	CChildFrame* pFrm = (CChildFrame*)GetParentFrame();
	if(pFrm->m_bShowRes) // 显示查询结果
		_QueryWithRecs();
	else // 不显示查询结果
		_QueryWithoutRecs();
}

void CMyOdbcSmplView::OnCloseupDsnCombo() 
{
	UpdateData(TRUE);
	//清空选择列表和选择条件已有信息
	m_list.ResetContent();
	m_strCondition = "";
	m_strSqlStmt = "";
	_ResetDataListControl();


	CColumnSet* pColSet = &GetDocument()->m_colset;
	// 找到数据表对应的信息
	CTableSet* pTabSet = &GetDocument()->m_tabset;
	int nSel = m_dsn_combo.GetCurSel();
	if(!pTabSet->IsOpen())pTabSet->Open();
	if(!pTabSet->IsBOF())pTabSet->MoveFirst();
	for(int i = 0; i< nSel;i++)
	{
		if(pTabSet->IsEOF())return;
			pTabSet->MoveNext();

	}
	ASSERT(!pColSet->IsOpen());

	try
	{
		//获得数据表的列信息
		pColSet->m_strQualifierParam =pTabSet->m_strQualifier;
		pColSet->m_strOwnerParam =pTabSet->m_strOwner;
		pColSet->m_strTableNameParam =pTabSet->m_strName;

		if (pColSet->m_pDatabase == NULL)
			pColSet->m_pDatabase =pTabSet->m_pDatabase;

		pTabSet->Close();
		
		if (!pColSet->Open())return;
		if(!pColSet->IsBOF())pColSet->MoveFirst();
		int i = 0;
		while( !pColSet->IsEOF())
		{
			// 将列信息添加到列表中
			m_list.InsertString( i++,pColSet->m_strColumnName);
			pColSet->MoveNext();
		}
		pColSet->Close();
			
	}
	catch( CDBException* e )
	{
		e->ReportError(MB_ICONEXCLAMATION);
	}		
	UpdateData(FALSE);

}
//==========================================
//		获取ODBC数据源名称
//==========================================
CString  CMyOdbcSmplView::_GetOdbcDsn()
{
	CString strDsn =  GetDocument()->m_tabset.m_pDatabase->GetConnect();
	int nLeft = strDsn.Find( ";DSN=",0) + 5;
	int nRight = strDsn.Find(";",nLeft);
	strDsn = strDsn.Mid( nLeft, nRight-nLeft);
	return strDsn;
}
//==========================================
//		获取选择列列表
//==========================================
CString CMyOdbcSmplView::_GetSelList()
{
	UpdateData( TRUE );
	CString strList;
	for( int i = 0 ; i < m_list.GetSelCount(); i++)
	{
		if( i != 0)strList += ",";
		CString strSel;
		m_list.GetText( i, strSel);
		strList += strSel;
	}
	return strList;

}
//==========================================
//		获取查询表名称
//==========================================
CString CMyOdbcSmplView::_GetSelTab()
{
	UpdateData( TRUE );
	return m_strDsn;
}
//==========================================
//		获取查询条件
//==========================================
CString CMyOdbcSmplView::_GetSelCon()
{
	UpdateData( TRUE);
	return m_strCondition;
}
//==========================================
//		获取查询SQL语句
//==========================================
CString CMyOdbcSmplView::_GetSQLStmt()
{
	CString strSql = "";
	// 获得用户查询信息
	strSql = "SELECT " + _GetSelList();
	strSql += " FROM " + _GetSelTab();
	if( !_GetSelCon().IsEmpty())
	{
		strSql += " WHERE " + _GetSelCon();
	}
	return strSql;
}

void CMyOdbcSmplView::_QueryWithoutRecs()
{
	// 定义环境句柄、连接句柄、语句句柄
	SQLHENV henv;
	SQLHDBC hdbc;
	SQLHSTMT hstmt;
	// 定义返回值
	SQLRETURN rtcode;

	// 分配环境句柄
	rtcode = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
	if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
	{
		// 设置环境属性（ODBC版本号）
		rtcode = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
		if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
		{
			// 分配连接句柄
			rtcode = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc);
			if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
			{
				// 设置连接属性（登录超时 = 10s）
				SQLSetConnectAttr(hdbc,SQL_ATTR_LOGIN_TIMEOUT,(void*)10,0);
				// 连接数据源
				LPTSTR lpstrDsn = m_strDB.GetBuffer(m_strDB.GetLength());
				rtcode = SQLConnect(hdbc,(SQLCHAR*)lpstrDsn,SQL_NTS,
										(SQLCHAR*)"",SQL_NTS,
										(SQLCHAR*)"",SQL_NTS);
				if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
				{
					// 分配语句句柄
					rtcode = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);
					if(rtcode == SQL_SUCCESS || rtcode == SQL_SUCCESS_WITH_INFO)
					{
						/******** 查询数据 ********/
						// 定义参数
						SQLCHAR sqlSelList[SEL_LIST_LEN],sqlTabList[SEL_TAB_LEN],sqlConList[SEL_CON_LEN];
						// 获得参数
						CString strList = _GetSelList();
						CString strTab = _GetSelTab();
						CString strCon = _GetSelCon();
						strcpy((char*) sqlSelList ,strList);
						strcpy((char*) sqlTabList ,strTab);
						strcpy((char*) sqlConList ,strCon);
						// 直接执行
						char str[2000];
						strcpy( str, "SELECT ");
						strcat( str, strList);
						strcat( str, " FROM ");
						strcat( str, strTab);
						if( !strCon.IsEmpty())
						{
							strcat( str, " WHERE ");
							strcat( str, strCon);
						}
						rtcode = SQLExecDirect( hstmt, (SQLCHAR*)str,SQL_NTS);
				
						m_strSqlStmt = CString(str);
						UpdateData(FALSE);
						// 释放语句句柄
						SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
					}
					// 断开连接
					SQLDisconnect(hdbc);
				}
				// 释放连接句柄
				SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
			}
		}
		// 释放环境句柄
		SQLFreeHandle(SQL_HANDLE_ENV,henv);

	}
}

void CMyOdbcSmplView::_QueryWithRecs()
{
  
	try
	{
		CClientDC dc(this);
		CTableSet* pTabSet = &GetDocument()->m_tabset;
		SQLHDBC hdbc = pTabSet->m_pDatabase->m_hdbc;
		// 动态连接数据源
		CODBCDynamic odbcDynamic(m_strDB,"sa","");
		// 获得查询语句
		m_strSqlStmt = _GetSQLStmt();
		// 执行查询
		odbcDynamic.ExecuteSQL( m_strSqlStmt );
		UpdateData(FALSE);
		
		// 显示查询结果集
		CODBCRecordArray* pODBCRecordArray = &odbcDynamic.m_ODBCRecordArray;

		for (int iRecord = 0; iRecord < pODBCRecordArray->GetSize(); iRecord++)
		{
			POSITION pos;
			CString strColName;
			CDBVariantEx* pvarValue;
			char szValue[255];

			int iCol = 0;
			CODBCRecord* pODBCRecord = (*pODBCRecordArray)[iRecord];
			for (pos = pODBCRecord->GetStartPosition(); pos != NULL;)
			{
				pODBCRecord->GetNextAssoc(pos, strColName, pvarValue);
				// 添加结果集列表的标题
				if (0 == iRecord)
				{
					CSize size = dc.GetTextExtent(strColName.GetBuffer(strColName.GetLength()));
					m_reclist.InsertColumn(iCol, strColName, LVCFMT_LEFT, size.cx, iCol);
				}
				// 添加结果集记录
				if (pvarValue)
				{
					pvarValue->GetStringValue(szValue);
					if (0 < strlen(szValue))
					{
						if (0 == iCol)
						{
							iRecord = m_reclist.InsertItem(iRecord, szValue, iCol);
						}
						else
						{
							m_reclist.SetItemText(iRecord, iCol, szValue);
						}

						iCol++;
					}
				}
			}
		}
	}
	catch (CUserException* pe)
	{
		pe->ReportError();
		pe->Delete();
	}
	UpdateData(FALSE);
}


void CMyOdbcSmplView::_ResetDataListControl()
{
	m_reclist.DeleteAllItems();
	int iNbrOfColumns;
	CHeaderCtrl* pHeader = (CHeaderCtrl*)m_reclist.GetDlgItem(0);
	if (pHeader)
	{
		iNbrOfColumns = pHeader->GetItemCount();
	}
	for (int i = iNbrOfColumns; i >= 0; i--)
	{
		m_reclist.DeleteColumn(i);
	}
}




void CMyOdbcSmplView::OnFileConnect() 
{
	OnConnect();
}


void CMyOdbcSmplView::OnConnect() 
{
	// 获取数据源中表或视图信息
	if(!_InitQueryView())return;
	// 激活有关查询控件
	GetDlgItem(IDC_DSN_COMBO)->EnableWindow(TRUE);
	GetDlgItem(IDC_SEL_CONDITION)->EnableWindow(TRUE);
	GetDlgItem(IDC_REC_LIST)->EnableWindow(TRUE);
	GetDlgItem(IDC_SEL_LIST)->EnableWindow(TRUE);
	GetDlgItem(IDC_QUERY)->EnableWindow(TRUE);
	// 将数据源连接入口封掉
	GetDlgItem(IDC_CONNECT)->EnableWindow(FALSE);
	// 显示数据源
	m_strDB = _GetOdbcDsn();
	UpdateData(FALSE);
}



void CMyOdbcSmplView::OnUpdateViewShowres(CCmdUI* pCmdUI) 
{
	CChildFrame* pFrm = (CChildFrame*)GetParentFrame();
	pCmdUI->SetCheck(pFrm->m_bShowRes);
}

void CMyOdbcSmplView::OnViewShowres() 
{
	CChildFrame* pFrm = (CChildFrame*)GetParentFrame();
	pFrm->m_bShowRes = !pFrm->m_bShowRes ;
}

void CMyOdbcSmplView::OnButton1() 
{
	try
	{
		CClientDC dc(this);
		CTableSet* pTabSet = &GetDocument()->m_tabset;
		SQLHDBC hdbc = pTabSet->m_pDatabase->m_hdbc;
		// 动态连接数据源
		CODBCDynamic odbcDynamic(m_strDB,"sa","");
		// 获得查询语句
		//m_strSqlStmt = CString("select jysdm,gddm,gdxm,zjzh,hbdm from gdk ");
		m_strSqlStmt = CString("exec up_mid_nbit32 0,'$exec up_mid_nbit32 0$','$$','$$','$$'");
		// 执行查询
		odbcDynamic.ExecuteSQL( m_strSqlStmt );
		UpdateData(FALSE);
		
		// 显示查询结果集
		CODBCRecordArray* pODBCRecordArray = &odbcDynamic.m_ODBCRecordArray;
		
		for (int iRecord = 0; iRecord < pODBCRecordArray->GetSize(); iRecord++)
		{
			POSITION pos;
			CString strColName;
			CDBVariantEx* pvarValue;
			char szValue[255];
			
			int iCol = 0;
			CODBCRecord* pODBCRecord = (*pODBCRecordArray)[iRecord];
			for (pos = pODBCRecord->GetStartPosition(); pos != NULL;)
			{
				pODBCRecord->GetNextAssoc(pos, strColName, pvarValue);
				// 添加结果集列表的标题
				if (0 == iRecord)
				{
					CSize size = dc.GetTextExtent(strColName.GetBuffer(strColName.GetLength()));
					m_reclist.InsertColumn(iCol, strColName, LVCFMT_LEFT, size.cx, iCol);
				}
				// 添加结果集记录
				if (pvarValue)
				{
					pvarValue->GetStringValue(szValue);
					if (0 < strlen(szValue))
					{
						if (0 == iCol)
						{
							iRecord = m_reclist.InsertItem(iRecord, szValue, iCol);
						}
						else
						{
							m_reclist.SetItemText(iRecord, iCol, szValue);
						}
						
						iCol++;
					}
				}
			}
		}
	}
	catch (CUserException* pe)
	{
		pe->ReportError();
		pe->Delete();
	}
	UpdateData(FALSE);
}