ociexampledlg.cpp

使用OCI访问Oracle数据库。包含基本的数据库连接、访问、更新等操作。

// OCIExampleDlg.cpp : implementation file
//

#include "stdafx.h"
#include "OCIExample.h"
#include "OCIExampleDlg.h"

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

/////////////////////////////////////////////////////////////////////////////
// CAboutDlg dialog used for App About

class CAboutDlg : public CDialog
{
public:
	CAboutDlg();

// Dialog Data
	//{{AFX_DATA(CAboutDlg)
	enum { IDD = IDD_ABOUTBOX };
	//}}AFX_DATA

	// ClassWizard generated virtual function overrides
	//{{AFX_VIRTUAL(CAboutDlg)
	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV support
	//}}AFX_VIRTUAL

// Implementation
protected:
	//{{AFX_MSG(CAboutDlg)
	//}}AFX_MSG
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
	//{{AFX_DATA_INIT(CAboutDlg)
	//}}AFX_DATA_INIT
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAboutDlg)
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
	//{{AFX_MSG_MAP(CAboutDlg)
		// No message handlers
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// COCIExampleDlg dialog

COCIExampleDlg::COCIExampleDlg(CWnd* pParent /*=NULL*/)
	: CDialog(COCIExampleDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(COCIExampleDlg)
	m_strUserName = _T("");
	m_strServerName = _T("");
	m_strPassword = _T("");
	m_english = 0;
	m_name = _T("");
	m_politics = 0;
	m_speciality1 = 0;
	m_speciality2 = 0;
	m_totalscore = 0;
	m_sortStyle = 0;
	m_strFiledvalue = _T("");
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void COCIExampleDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(COCIExampleDlg)
	DDX_Control(pDX, IDC_COMBO_CONDITION, m_CombolCondition);
	DDX_Control(pDX, IDC_COMBO_FILEDNAME, m_CombolFieldName);
	DDX_Control(pDX, IDC_MSFLEXGRID, m_grid);
	DDX_Text(pDX, IDC_EDIT_USERNAME, m_strUserName);
	DDX_Text(pDX, IDC_EDIT_SERVERNAME, m_strServerName);
	DDX_Text(pDX, IDC_EDIT_PASSWORD, m_strPassword);
	DDX_Text(pDX, IDC_EDIT_ENGLISH, m_english);
	DDV_MinMaxInt(pDX, m_english, 0, 100);
	DDX_Text(pDX, IDC_EDIT_NAME, m_name);
	DDX_Text(pDX, IDC_EDIT_POLITICS, m_politics);
	DDV_MinMaxInt(pDX, m_politics, 0, 100);
	DDX_Text(pDX, IDC_EDIT_SPECIALITY1, m_speciality1);
	DDV_MinMaxInt(pDX, m_speciality1, 0, 150);
	DDX_Text(pDX, IDC_EDIT_SPECIALITY2, m_speciality2);
	DDV_MinMaxInt(pDX, m_speciality2, 0, 150);
	DDX_Text(pDX, IDC_EDIT_TOTALSCORE, m_totalscore);
	DDV_MinMaxInt(pDX, m_totalscore, 0, 500);
	DDX_Radio(pDX, IDC_RADIO_ASC, m_sortStyle);
	DDX_Text(pDX, IDC_EDIT_FILEDVALUE, m_strFiledvalue);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(COCIExampleDlg, CDialog)
	//{{AFX_MSG_MAP(COCIExampleDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_BUTTON_CONNECT, OnButtonConnect)
	ON_WM_DESTROY()
	ON_BN_CLICKED(IDC_BUTTON_EXIT, OnButtonExit)
	ON_BN_CLICKED(IDC_BUTTON_ADD, OnButtonAdd)
	ON_EN_CHANGE(IDC_EDIT_ENGLISH, OnChangeEditEnglish)
	ON_EN_CHANGE(IDC_EDIT_POLITICS, OnChangeEditPolitics)
	ON_EN_CHANGE(IDC_EDIT_SPECIALITY1, OnChangeEditSpeciality1)
	ON_EN_CHANGE(IDC_EDIT_SPECIALITY2, OnChangeEditSpeciality2)
	ON_BN_CLICKED(IDC_BUTTON_MODIFY, OnButtonModify)
	ON_BN_CLICKED(IDC_BUTTON_DELETE, OnButtonDelete)
	ON_BN_CLICKED(IDC_RADIO_ASC, OnRadioAsc)
	ON_BN_CLICKED(IDC_RADIO_DESC, OnRadioDesc)
	ON_BN_CLICKED(IDC_BUTTON_SORT, OnButtonSort)
	ON_BN_CLICKED(IDC_BUTTON_QUERY, OnButtonQuery)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// COCIExampleDlg message handlers

BOOL COCIExampleDlg::OnInitDialog()
{

	CDialog::OnInitDialog();

	// Add "About..." menu item to system menu.

	// IDM_ABOUTBOX must be in the system command range.
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// 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
	
	SQL="";

	/*初始服务器名,用户名和密码*/
	m_strServerName="Hora";
	m_strUserName="scott";
	m_strPassword="tiger";
	this->UpdateData (FALSE);//更新对应在编辑框控件内容
	
	Init_Grid();//初始化表格控件
	Init_OCI();//初始化OCI
	Init_condition();//初始化查询的符号
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

void COCIExampleDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// 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 COCIExampleDlg::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 COCIExampleDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

//连接数据库
void COCIExampleDlg::OnButtonConnect() 
{
	
	this->UpdateData ();   //更新数据
	
	//代码5.2，处理SQL的第一步，准备SQL
	wsprintf((char*)textSQL, "SELECT * FROM Score %s",SQL);

	if(status=OCIStmtPrepare(stmthp, errhp,textSQL, strlen((char*)textSQL),OCI_NTV_SYNTAX, OCI_DEFAULT))
	{
		ErrorProc (errhp, status) ; 
		return;
	}
	
	//代码5.3，处理SOL的第三步，执行
	if (status=OCIStmtExecute(svchp, stmthp,errhp, (ub4)0, 0, NULL, NULL, OCI_DEFAULT))
	{
		ErrorProc (errhp, status) ; 
		return;
	}
		
	//代码5.4，处理SOL的第四步，描述	
	//代码5.4.1，读取选择列表中的项数
	ErrorProc(errhp, OCIAttrGet(stmthp,OCI_HTYPE_STMT,&col_num, 0, OCI_ATTR_PARAM_COUNT,errhp ));
	int ColumnNumbers=(int) col_num;
	text	*namep;		//字段名称
	ub4		sizep;		//字段名称的字符串长度
	text	tempText[100];

	//获取表的字段的属性信息
	int nindex=m_CombolFieldName.GetCurSel();
	if(nindex<0) nindex=0;
	m_CombolFieldName.ResetContent();
	for(int i=0;i<(int)col_num; i++)
	{
		//为选择项分配参数描述符
		ErrorProc(errhp, OCIParamGet(stmthp,OCI_HTYPE_STMT, errhp, (void**)&colhp, ub4(i+1)));
	
		//读取选择项的数据长度
		ErrorProc(errhp, OCIAttrGet(colhp, OCI_DTYPE_PARAM,&collen[i], 0, OCI_ATTR_DATA_SIZE, errhp ));
		
		//读取选择项的数据类型
		ErrorProc(errhp, OCIAttrGet(colhp, OCI_DTYPE_PARAM,&coltype[i],0, OCI_ATTR_DATA_TYPE, errhp));
	
		//若这个字段为日期型，则把其字符宽度置为50
		if (coltype[i]==SQLT_DATE)collen[i] =50;
		
		//分配缓冲区
		colbuf[i]=(text*)new text[(int)collen[i]+1];
		
		//代码5.4.2获取字段名称
		ErrorProc(errhp, OCIAttrGet(colhp, OCI_DTYPE_PARAM,(dvoid*)&namep, (ub4*)&sizep, OCI_ATTR_NAME, errhp ));
		strncpy((char*)tempText, (char*)namep, (size_t)sizep) ;
		tempText [sizep]= '\0';
		m_grid.SetTextMatrix(0,i+1,(const char *)tempText);	//将数据写入表格控件
		m_CombolFieldName.AddString((const char *)tempText); //将字段名称添加入下拉列表框
	}	
    m_CombolFieldName.SetCurSel (nindex);
 
	//代码5.5，处理SQL的第五步，定义变量
	for(i=0; i<(int)col_num; i++)
	{
		if (status=OCIDefineByPos (stmthp,&defhp[i],errhp, i+1,(ub1*)colbuf[i],
			collen[i]+1,SQLT_STR,&ind[i], 0, 0, OCI_DEFAULT))
		{
			ErrorProc(errhp, status); 
			return;
		}
	}

	//代码5.6，处理SQL的第六步，取值			
	int row=0;
	CString tempstr;
	while((OCIStmtFetch(stmthp, errhp,1,OCI_FETCH_NEXT,OCI_DEFAULT))!=OCI_NO_DATA)
	{
		row = row+1;
		m_grid.SetRows(row+1); //根据数据的行数动态设置表格控件的最大行数
		tempstr.Format ("%d",row);
		m_grid.SetTextMatrix(row,0,tempstr); //设置序号
		for(i=0; i<(int)col_num; i++)
		{
			tempstr=(char*)colbuf[i];
			tempstr.TrimRight(' '); //删除右空格
			//把获取的用户的基表的数据赋予字段数值数组
			m_grid.SetTextMatrix(row,i+1,tempstr);
		}
		
	}
		
	SQL="";
	
}

//错误处理函数
void COCIExampleDlg::ErrorProc(dvoid *err, sword status)
{
	CString str; sb4 errcode; text errbuf [512 ] ;
	if (status==OCI_ERROR)
	{
		//调用OCI的错误获取函数
		OCIErrorGet((dvoid*)errhp, (ub4 )1,NULL,&errcode, errbuf,
			(ub4) sizeof (errbuf), OCI_HTYPE_ERROR);
		str.Format("错误号:%d\n错误信息:%s\n", errcode, errbuf);
		AfxMessageBox (str );
	}
}

//初始化表格控件
void COCIExampleDlg::Init_Grid()
{
	m_grid.SetCols (7);				//设置最大列数