ociexampledlg.cpp

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

	m_grid.SetColWidth(0,500);		//设置第0列宽度
	m_grid.SetColWidth(1,1000);		//设置第1列宽度
	for(int i=2;i<=4;i++)				//设置第2-4列宽度
	m_grid.SetColWidth(i,900);		//设置第5列宽度
	m_grid.SetColWidth(5,1100);		//设置第6列宽度
	
}

void COCIExampleDlg::OnDestroy() 
{
	CDialog::OnDestroy();
	
	//代码4.5.1，//结束会话
	OCISessionEnd(svchp, errhp, authp, (ub4) 0);
	//断开与数据库的连接
	OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
	//释放OCI句柄
	OCIHandleFree((dvoid*)srvhp, OCI_HTYPE_SERVER);
	OCIHandleFree((dvoid*)svchp, OCI_HTYPE_SVCCTX);
	OCIHandleFree((dvoid*)errhp, OCI_HTYPE_ERROR);
	OCIHandleFree((dvoid*)authp, OCI_HTYPE_SESSION );
	OCIHandleFree((dvoid*)stmthp, OCI_HTYPE_STMT );
	OCIHandleFree((dvoid*)dschp, OCI_HTYPE_DESCRIBE);
	
}

void COCIExampleDlg::OnButtonExit() 
{
	this->EndDialog(IDOK);	
}

//初始化OCI
void COCIExampleDlg::Init_OCI()
{
	//代码4.1.2，初始化OCI环境
	OCIEnvInit(&envhp, OCI_DEFAULT,0, 0);

	//代码4.2.1:分配句柄与数据结构
	OCIHandleAlloc(envhp, (dvoid**)&errhp,OCI_HTYPE_ERROR, 0, 0);  	//分配错误句柄
	ErrorProc (errhp,OCIHandleAlloc(envhp, (dvoid**)&srvhp, OCI_HTYPE_SERVER, 0, 0)); 	//分配服务器句柄
	ErrorProc (errhp,OCIHandleAlloc(envhp, (dvoid**)&svchp, OCI_HTYPE_SVCCTX, 0, 0 )); //分配服务环境句柄
	ErrorProc (errhp,OCIHandleAlloc(envhp, (dvoid**)&authp, OCI_HTYPE_SESSION, 0, 0)); 	//分配会话句柄
	ErrorProc (errhp,OCIHandleAlloc((dvoid*)envhp,(dvoid**)&dschp, OCI_HTYPE_DESCRIBE,0,0)); //分配描述句柄
	ErrorProc (errhp,OCIHandleAlloc ((dvoid*)envhp,(dvoid**)&stmthp,OCI_HTYPE_STMT, 0, 0)); //分配语句句柄
	/*
	  说明:常量"OC_ HTYPE _STMT"等等是OCI句柄的宏定义，标明我们所分配的句柄的类型。
	  句柄的宏定义具体可以参考oci. h文件中的"Handle Types"部分。
	 */

	//代码4.3.2，连接数据库
	ErrorProc(errhp,OCIServerAttach(srvhp, errhp,(unsigned char*)(LPCTSTR)
		m_strServerName, (sb4)strlen(m_strServerName),OCI_DEFAULT));
	
	//	设置服务环境的服务器属性	
	ErrorProc (errhp, OCIAttrSet ((dvoid*)svchp, (ub4) OCI_HTYPE_SVCCTX, 
		(dvoid*)srvhp, (ub4) 0,OCI_ATTR_SERVER, errhp));

	//代码4.3.3，认证用户并且开始会话
	//认证用户名称;
	ErrorProc(errhp,OCIAttrSet(authp,OCI_HTYPE_SESSION,(dvoid*)(LPCTSTR)m_strUserName,
		(ub4)strlen(m_strUserName),OCI_ATTR_USERNAME,errhp));
	//认证用户密码
	ErrorProc(errhp,OCIAttrSet(authp,OCI_HTYPE_SESSION,(dvoid*)(LPCTSTR)m_strPassword,
		(ub4)strlen(m_strPassword), OCI_ATTR_PASSWORD,errhp));
	//创建并开始一个用户会话,应用程序必须在连接服务器之后才能调用该函数
	ErrorProc(errhp,OCISessionBegin (svchp, errhp, authp,OCI_CRED_RDBMS,OCI_DEFAULT));
	
	//设置服务器环境的会话属性
	ErrorProc(errhp,OCIAttrSet(svchp, OCI_HTYPE_SVCCTX,(dvoid*)authp, 0, OCI_ATTR_SESSION, errhp));

}

BEGIN_EVENTSINK_MAP(COCIExampleDlg, CDialog)
    //{{AFX_EVENTSINK_MAP(COCIExampleDlg)
	ON_EVENT(COCIExampleDlg, IDC_MSFLEXGRID, -600 /* Click */, OnClickMsflexgrid, VTS_NONE)
	//}}AFX_EVENTSINK_MAP
END_EVENTSINK_MAP()

void COCIExampleDlg::OnClickMsflexgrid() 
{
    if(m_grid.GetRow()<1) return;
	
	m_name=m_grid.GetTextMatrix(m_grid.GetRow(),1);
	m_english=atoi(m_grid.GetTextMatrix(m_grid.GetRow(),2));
	m_politics=atoi(m_grid.GetTextMatrix(m_grid.GetRow(),3));
	m_speciality1=atoi(m_grid.GetTextMatrix(m_grid.GetRow(),4));
	m_speciality2=atoi(m_grid.GetTextMatrix(m_grid.GetRow(),5));
	m_totalscore=atoi(m_grid.GetTextMatrix(m_grid.GetRow(),6));
	this->UpdateData (FALSE);
	
}



//英语成绩改变时,总成绩也应随着动态改变
void COCIExampleDlg::OnChangeEditEnglish() 
{
	this->UpdateData (TRUE); //更新数据
	m_totalscore=m_english+m_politics+m_speciality1+m_speciality2;
	this->UpdateData (FALSE); //更新总成绩编辑框内容
}

//政治成绩改变时,总成绩也应随着动态改变
void COCIExampleDlg::OnChangeEditPolitics() 
{
	this->UpdateData (TRUE); //更新数据
	m_totalscore=m_english+m_politics+m_speciality1+m_speciality2;
	this->UpdateData (FALSE); //更新总成绩编辑框内容
}

//专业课1成绩改变时,总成绩也应随着动态改变
void COCIExampleDlg::OnChangeEditSpeciality1() 
{
	this->UpdateData (TRUE); //更新数据
	m_totalscore=m_english+m_politics+m_speciality1+m_speciality2;
	this->UpdateData (FALSE); //更新总成绩编辑框内容
}

//专业课2成绩改变时,总成绩也应随着动态改变
void COCIExampleDlg::OnChangeEditSpeciality2() 
{
	this->UpdateData (TRUE); //更新数据
	m_totalscore=m_english+m_politics+m_speciality1+m_speciality2;
	this->UpdateData (FALSE); //更新总成绩编辑框内容
	
}

/*增加记录*/
void COCIExampleDlg::OnButtonAdd() 
{
	
	//准备SOL语句
	wsprintf ((char*)textSQL,"INSERT INTO Score VALUES('%s',%d,%d,%d,%d,%d)",m_name,m_english,m_politics,m_speciality1,m_speciality2,m_totalscore);
	if (status =OCIStmtPrepare(stmthp, errhp,textSQL, strlen ((char*)textSQL),OCI_NTV_SYNTAX, OCI_DEFAULT))
	{
		ErrorProc (errhp, status);	//捕捉错误
		return;
	}
	
	//执行SOL语句
	if (status =OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1,0,NULL, NULL, OCI_DEFAULT))
	{
		ErrorProc(errhp,status);	//捕捉错误
		return;
	}
	
	//提交
	if (status =OCITransCommit(svchp, errhp, (ub4)0))
	{
		ErrorProc(errhp,status);	//捕捉错误
		return;
	}
	else
		MessageBox("增加记录成功!");

	/*刷新表格里的数据*/
	int nRows=m_grid.GetRows();  //得到表格控件中总行数
	m_grid.SetRows(nRows+1);	//表格控件中总行数加1(因为新增加了一条记录)
	m_grid.SetTopRow(nRows+1);	//设置表格控件的顶行,是了将光标滚动的新增记录行
	
	CString tempstr;
    tempstr.Format("%d",nRows);	m_grid.SetTextMatrix(nRows,0,tempstr);	//写入序号
	m_grid.SetTextMatrix(nRows,1,m_name); //写入姓名
	tempstr.Format("%d",m_english);	m_grid.SetTextMatrix(nRows,2,tempstr);		//写入英语成绩
	tempstr.Format("%d",m_politics);	m_grid.SetTextMatrix(nRows,2,tempstr);	//写入政治成绩
	tempstr.Format("%d",m_speciality1);	m_grid.SetTextMatrix(nRows,3,tempstr);	//写入专业课1成绩	
	tempstr.Format("%d",m_speciality2);	m_grid.SetTextMatrix(nRows,4,tempstr);	//写入专业课2成绩
	tempstr.Format("%d",m_totalscore);	m_grid.SetTextMatrix(nRows,5,tempstr);	//写入总成绩
		
}

/*修改记录*/
void COCIExampleDlg::OnButtonModify() 
{
	int nRow=m_grid.GetRow();  //得到表格控件中光标所在行(即所要修改的行)
	
	if(m_grid.GetRows()>1 && nRow<1)
	{
		MessageBox("请先选择要修改的记录行!");
		return;
	}

	CString	stroldname=m_grid.GetTextMatrix (m_grid.GetRow (),1);
    CString strSQL;
	
	//准备SOL语句
	 strSQL.Format ("UPDATE Score SET  姓名='%s',英语=%d,政治=%d,专业课1=%d,\
	  专业课2=%d,总成绩=%d WHERE 姓名='%s'",m_name, m_english, m_politics,\
	  m_speciality1,m_speciality2,m_totalscore,stroldname);
	
	  wsprintf ((char*)textSQL,strSQL);

	//准备SQL语句
	if (status =OCIStmtPrepare(stmthp, errhp,textSQL, strlen ((char*)textSQL),OCI_NTV_SYNTAX, OCI_DEFAULT))
	{
		ErrorProc (errhp, status);	//捕捉错误
		return;
	}
	
	//执行SOL语句
	if (status =OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1,0,NULL, NULL, OCI_DEFAULT))
	{
		ErrorProc(errhp,status);	//捕捉错误
		return;
	}
	
	//提交
	if (status =status =OCITransCommit(svchp, errhp, (ub4)0))
	{
		ErrorProc(errhp,status);	//捕捉错误
		return;
	}
	else
		MessageBox("修改记录成功!");	// TODO: Add your control notification handler code here
	
	/*刷新表格里的数据*/
	CString tempstr;
	m_grid.SetTextMatrix(nRow,1,m_name); //写入姓名
	tempstr.Format("%d",m_english);	m_grid.SetTextMatrix(nRow,2,tempstr);		//写入英语成绩
	tempstr.Format("%d",m_politics);	m_grid.SetTextMatrix(nRow,2,tempstr);	//写入政治成绩
	tempstr.Format("%d",m_speciality1);	m_grid.SetTextMatrix(nRow,3,tempstr);	//写入专业课1成绩	
	tempstr.Format("%d",m_speciality2);	m_grid.SetTextMatrix(nRow,4,tempstr);	//写入专业课2成绩
	tempstr.Format("%d",m_totalscore);	m_grid.SetTextMatrix(nRow,5,tempstr);	//写入总成绩
	
}

//删除记录
void COCIExampleDlg::OnButtonDelete() 
{
	int nRow=m_grid.GetRow();  //得到表格控件中光标所在行(即所要修改的行)
	
	if(m_grid.GetRows()>1 && nRow<1)
	{
		MessageBox("请先选择要删除的记录行!");
		return;
	}
	
	CString	stroldname=m_grid.GetTextMatrix (m_grid.GetRow (),1);
    CString strSQL;
	
	//准备SOL语句
	strSQL.Format ("DELETE FROM Score WHERE  姓名='%s'",m_name);
	
	wsprintf ((char*)textSQL,strSQL);
	
	//准备SQL语句
	if (status =OCIStmtPrepare(stmthp, errhp,textSQL, strlen ((char*)textSQL),OCI_NTV_SYNTAX, OCI_DEFAULT))
	{
		ErrorProc (errhp, status);	//捕捉错误
		return;
	}
	
	//执行SOL语句
	if (status =OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1,0,NULL, NULL, OCI_DEFAULT))
	{
		ErrorProc(errhp,status);	//捕捉错误
		return;
	}
	
	//提交
	if (status =status =OCITransCommit(svchp, errhp, (ub4)0))
	{
		ErrorProc(errhp,status);	//捕捉错误
		return;
	}
	else
		MessageBox("删除记录成功!");	// TODO: Add your control notification handler code here
	OnButtonConnect();  //刷新表格数据
}

//升序
void COCIExampleDlg::OnRadioAsc() 
{
	m_sortStyle=0;	
}

//降序
void COCIExampleDlg::OnRadioDesc() 
{
	m_sortStyle=1;		
}

//排序
void COCIExampleDlg::OnButtonSort() 
{
	CString sortASC_DESC,sortFiled;
   
	if(m_sortStyle==0)   //得到升序或降序
		sortASC_DESC="ASC";
	else
		sortASC_DESC="DESC";
	
	//排序的字段名
	m_CombolFieldName.GetLBText (m_CombolFieldName.GetCurSel (),sortFiled);
	
	//设置附加SQL语句
	SQL.Format("ORDER BY %s %s",sortFiled,sortASC_DESC);
	
	OnButtonConnect();  //刷新表格数据


}

//初始化查询的符号
void COCIExampleDlg::Init_condition()
{
	m_CombolCondition.ResetContent ();
	m_CombolCondition.ResetContent ();
	m_CombolCondition.AddString (">");
	m_CombolCondition.AddString (">=");
	m_CombolCondition.AddString ("<");
	m_CombolCondition.AddString ("<=");
	m_CombolCondition.AddString ("=");
	m_CombolCondition.AddString ("!=");
	m_CombolCondition.SetCurSel(0); 
}

void COCIExampleDlg::OnButtonQuery() 
{
	CString sortFiled,sortcondition;
    this->UpdateData ();
	//排序的字段名
	m_CombolFieldName.GetLBText (m_CombolFieldName.GetCurSel (),sortFiled);
	//排序的字段名
	m_CombolCondition.GetLBText (m_CombolCondition.GetCurSel (),sortcondition);
	
	//设置附加SQL语句
	if(m_CombolFieldName.GetCurSel()==0) //姓名
		SQL.Format("WHERE %s %s '%s'",sortFiled,sortcondition,m_strFiledvalue);
    else
		SQL.Format("WHERE %s %s %d",sortFiled,sortcondition,atoi(m_strFiledvalue));
	
	OnButtonConnect();  //刷新表格数据	
}