dbtestdlg.cpp

通过ADO数据控件,访问Oracle数据库的样例程序

// DBTestDlg.cpp : implementation file
//

#include "stdafx.h"
#include "DBTest.h"
#include "DBTestDlg.h"

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

/////////////////////////////////////////////////////////////////////////////
// CDBTestDlg dialog

CDBTestDlg::CDBTestDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CDBTestDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CDBTestDlg)
	m_result = _T("");
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CDBTestDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CDBTestDlg)
	DDX_Text(pDX, IDC_ST_RESULT, m_result);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CDBTestDlg, CDialog)
	//{{AFX_MSG_MAP(CDBTestDlg)
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_WM_CLOSE()
	ON_BN_CLICKED(IDC_MDY, OnMdy)
	ON_BN_CLICKED(IDC_EXIT, OnExit)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CDBTestDlg message handlers

BOOL CDBTestDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// Set the icon for this dialog.  The framework does this automatically
	//  when the application's main window is not a dialog
	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon
	
	// TODO: Add extra initialization here	
	//---创建Connection实例----------------------//
	HRESULT hr;
	try
	{		
		//m_pConnection->ConnectionTimeout = 5;///设置超时时间为5秒
		//if(m_pConnection->State)  //如果已打开连接
        //  m_pConnection->Close();
		hr = m_pConnection.CreateInstance("ADODB.Connection");///创建Connection对象,或m_pConnection.CreateInstance(__uuidof(Connection));		
		if(SUCCEEDED(hr))
		{
			//hr = m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb","","",adModeUnknown);///连接数据库
			///上面一句中连接字串中的Provider是针对ACCESS2000环境的，对于ACCESS97,需要改为:Provider=Microsoft.Jet.OLEDB.3.51; 
			//hr = m_pConnection->Open("Provider=MSDAORA.1;Password=game88;User ID=game88;Data Source=jo;Persist Security Info=True","","",adModeUnknown); 
			hr = m_pConnection->Open("Provider=MSDAORA.1;Password=game88;User ID=game88;Data Source=jo;Persist Security Info=True","","",adModeUnknown);  //连接oracle数据库
		}else
		{
			AfxMessageBox("创建连接失败");	
			return false;
		}
		//创建命令执行组件
		hr=m_pSQL.CreateInstance("ADODB.Command");
		if(FAILED(hr))
		{
			AfxMessageBox("创建命令组件失败");
			return false;
		}
		m_pSQL->ActiveConnection=m_pConnection;

	}
	catch(_com_error e)///捕捉异常
	{
		CString errormessage;		
		errormessage.Format("连接数据库失败!\r\n错误信息:%s",e.ErrorMessage());
		AfxMessageBox(errormessage);///显示错误信息
	}

	//----------------------------------//	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CDBTestDlg::OnPaint() 
{
	if (IsIconic())
	{
		CPaintDC dc(this); // device context for painting

		SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);

		// Center icon in client rectangle
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// Draw the icon
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

// The system calls this to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CDBTestDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void CDBTestDlg::OnOK() 
{
	// TODO: Add extra validation here	
	//CDialog::OnOK();	
	//------------//
	m_result="";
	HRESULT hr;
	CString cErr="";
	//创建一个结果集
	if(m_pRecordset!=NULL)
    {  
	 if(m_pRecordset->State)
       m_pRecordset->Close();
	}
    //--------------// 	
	hr=m_pRecordset.CreateInstance("ADODB.Recordset");    //或如下写法:m_pRecordset.CreateInstance(__uuidof(Recordset))
	if(FAILED(hr))
	{
		AfxMessageBox("创建一个结果集出错");
		return;
	}
	//找开一个结果集,并查得数据显示
	try
	{
		hr=m_pRecordset->Open("select * from t_game where rownum <= 5",(IDispatch*)m_pConnection,adOpenDynamic,adLockOptimistic,adCmdText);
		if(FAILED(hr))
		{			
			AfxMessageBox("打开结果集出错");
			return;
		}    
    }catch(_com_error e)
	{
	    cErr.Format("打开结果集出错,原因如下：\n%s",e.ErrorMessage());
		AfxMessageBox(cErr);
	}
	CString sResult="",sId="",sNo="";
	sResult="game_id,game_no"; 
	VARIANT tmpVal;
    while(!m_pRecordset->adoEOF)
	{
		if(sResult.GetLength()>0)
          sResult+="\n";  //加入换行
		//----------------//
		tmpVal=m_pRecordset->Fields->GetItem("game_id")->Value;	
		sId=VariantToCString(tmpVal);			
		//str=(char*)(_bstr_t)(m_pRecordset->Fields->GetItem("game_id")->Value);
		tmpVal=m_pRecordset->Fields->GetItem("game_no")->Value;
		sNo=VariantToCString(tmpVal);
		//-------------//
        sResult+=sId+","+sNo;
		//-----------------//
		m_pRecordset->MoveNext();
	}	
    //显示操作结果
	m_result=sResult;
	//关闭结束集
    m_pRecordset->Close();
    //----------------//
	UpdateData(false);    	
}

void CDBTestDlg::OnClose() 
{
	// TODO: Add your message handler code here and/or call default	
	CDialog::OnClose();
	//关闭查询组件
	//if(m_pSQL!=NULL)
    //  free(m_pSQL);
	//--关闭数据库连接------------//
	if(m_pConnection->State)
      m_pConnection->Close();	
}

CString CDBTestDlg::VariantToCString(VARIANT var)
{
    //将VARIANT转化为CString的成员函数
	CString   strValue;   
	_variant_t   var_t;   
	_bstr_t   bst_t;   
	time_t   cur_time;   
	CTime   time_value;   
	COleCurrency   var_currency;	
	//先判断变体的数据类型
	switch(var.vt)   
	{   
	   case  VT_EMPTY:   
		   strValue=_T("");   
		   break;   
	   case   VT_UI1:   
		   strValue.Format("%d",var.bVal);   
		   break;   
	   case   VT_I2:   
		   strValue.Format("%d",var.iVal);   
		   break;   
	   case   VT_I4:   
		   strValue.Format("%d",var.lVal);   
		   break;   
	   case   VT_R4:   
		   strValue.Format("%f",var.fltVal);   
		   break;   
	   case   VT_R8:   
		   strValue.Format("%f",var.dblVal);   
		   break;   
	   case   VT_CY:   
		   var_currency   =   var;   
		   strValue   =   var_currency.Format(0);   
		   break;   
	   case   VT_BSTR:   
		   var_t   =   var;   
		   bst_t   =   var_t;   
		   strValue.Format   ("%s",(const   char*)bst_t);   
		   break;   
	   case   VT_NULL:   
		   strValue   =   _T("");   
		   break;   
	   case   VT_DATE:   
		   cur_time   =   (long)var.date;   
		   time_value   =   cur_time;   
		   strValue   =   time_value.Format("%A,%B%d,%Y");   
		   break;   
	   case   VT_BOOL:   
		   strValue.Format("%d",var.boolVal   );   
		   break;   
	   case   VT_DECIMAL:
		   char pp[256];
		   ZeroMemory(pp,256);
		   _ui64toa(var.decVal.Lo64,pp,10);  //_ui64toa参数说明：第三个参数代表要转化成的进制
           strValue.Format("%s",pp);		   
		   break;
	   default:     
		   strValue   =   _T("");   
		   break;   
	}   
	return   strValue; 
}

void CDBTestDlg::OnMdy() 
{
	// TODO: Add your control notification handler code here
	//往表中插入数据
    m_pSQL->CommandText="insert into t_sys_parameter(para_type,type_name,para_name,para_value,memo,state) values('MY_CUST','测试自订参数','MY_NAME','林某某','没有',99)";
	try
	{
		m_pSQL->Execute(NULL,NULL,adCmdText);
        CString sDisp;
		sDisp.Format("成功插入表数据");
        AfxMessageBox(sDisp);
	}catch(_com_error e)
	{
		CString cErr;
		cErr=e.ErrorMessage();
		cErr="插入表出错，原因如下:\n"+cErr;
		AfxMessageBox(cErr);
	}
}

void CDBTestDlg::OnExit() 
{
	// TODO: Add your control notification handler code here
	CDialog::OnCancel();
}