querydepartmentdlg.cpp

A HR database application.

// QueryDepartmentDlg.cpp : implementation file
//

#include "stdafx.h"
#include "HRDB.h"
#include "QueryDepartmentDlg.h"
#include "desexec.h"

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

//定义全局SQL环境变量
extern SQLHENV    henv;
extern SQLHDBC    hdbc;
extern SQLHSTMT   hstmt;
extern SQLRETURN  retcode;
extern BOOL       findResult;

/////////////////////////////////////////////////////////////////////////////
// CQueryDepartmentDlg dialog


CQueryDepartmentDlg::CQueryDepartmentDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CQueryDepartmentDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CQueryDepartmentDlg)
	m_intQueryDeptID = 0;
	m_strQueryDeptName = _T("");
	m_intRadioDeptID = 0;
	m_intResultLocationID = 0;
	m_intResultManagerID = 0;
	m_intResultDeptID = 0;
	m_strResultDeptName = _T("");
	m_intResultDeptEmp = 0;
	//}}AFX_DATA_INIT
}


void CQueryDepartmentDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CQueryDepartmentDlg)
	DDX_Control(pDX, IDC_RESULT_DEPT_EMP, m_editResultDeptEmp);
	DDX_Control(pDX, IDC_RESULT_DEPARTMENT_NAME, m_editResultDeptName);
	DDX_Control(pDX, IDC_RESULT_DEPARTMENT_ID, m_editResultDeptID);
	DDX_Control(pDX, IDC_RESULT_D_MANAGER_ID, m_editResultManagerID);
	DDX_Control(pDX, IDC_RESULT_D_LOCATION_ID, m_editResultLocationID);
	DDX_Control(pDX, IDC_RADIO_DEPARTMENT_ID, m_editRadioDeptID);
	DDX_Control(pDX, IDC_QUERY_DEPARTMENT_NAME, m_editQueryDeptName);
	DDX_Control(pDX, IDC_QUERY_DEPARTMENT_ID, m_editQueryDeptID);
	DDX_Text(pDX, IDC_QUERY_DEPARTMENT_ID, m_intQueryDeptID);
	DDX_Text(pDX, IDC_QUERY_DEPARTMENT_NAME, m_strQueryDeptName);
	DDX_Radio(pDX, IDC_RADIO_DEPARTMENT_ID, m_intRadioDeptID);
	DDX_Text(pDX, IDC_RESULT_D_LOCATION_ID, m_intResultLocationID);
	DDX_Text(pDX, IDC_RESULT_D_MANAGER_ID, m_intResultManagerID);
	DDX_Text(pDX, IDC_RESULT_DEPARTMENT_ID, m_intResultDeptID);
	DDX_Text(pDX, IDC_RESULT_DEPARTMENT_NAME, m_strResultDeptName);
	DDX_Text(pDX, IDC_RESULT_DEPT_EMP, m_intResultDeptEmp);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CQueryDepartmentDlg, CDialog)
	//{{AFX_MSG_MAP(CQueryDepartmentDlg)
	ON_BN_CLICKED(IDC_DEPARTMENT_DELETE, OnDepartmentDelete)
	ON_BN_CLICKED(IDC_DEPARTMENT_UPDATE, OnDepartmentUpdate)
	ON_BN_CLICKED(IDC_RADIO_DEPARTMENT_ID, OnRadioDepartmentId)
	ON_BN_CLICKED(IDC_RADIO_DEPARTMENT_NAME, OnRadioDepartmentName)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CQueryDepartmentDlg message handlers

BOOL CQueryDepartmentDlg::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	findResult = FALSE; //初始化
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

void CQueryDepartmentDlg::OnCancel() 
{
	// 重置findResult为FALSE
	findResult = FALSE;
	
	CDialog::OnCancel();
}

void CQueryDepartmentDlg::OnOK() 
{
	// 使用参数绑定的方法查询表中数据
	SQLUINTEGER   param_department_id;        //输入参数
	SQLVARCHAR    param_department_name[30];  //输入参数
	SQLUINTEGER   result_department_id;		  //输出参数
	SQLVARCHAR    result_department_name[30]; //输出参数
	SQLUINTEGER   result_manager_id;		  //输出参数
	SQLUINTEGER   result_location_id;		  //输出参数
	SQLUINTEGER   result_department_emp;	  //输出参数
	SQLINTEGER    param_department_idInd = 0, param_department_nameLenOrInd = 0;
	SQLINTEGER    result_department_idInd = 0, result_department_nameLenOrInd = 0;
	SQLINTEGER    result_manager_idInd = 0, result_location_idInd = 0, result_department_empInd = 0;

	TODBCs ( hstmt, SQLCloseCursor( hstmt ) ); //初始化,关闭游标

	// 绑定到结果集的列
	TODBCs( hstmt, SQLBindCol( hstmt, 1, SQL_C_SLONG, &result_department_id, 0, &result_department_idInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 2, SQL_C_CHAR, result_department_name, sizeof(result_department_name), &result_department_nameLenOrInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 3, SQL_C_SLONG, &result_manager_id, 0, &result_manager_idInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 4, SQL_C_SLONG, &result_location_id, 0, &result_location_idInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 5, SQL_C_SLONG, &result_department_emp, 0, &result_department_empInd ) );

	if ( m_intRadioDeptID == 0 )
	{
		// 准备包含参数标志符的SQL语句
		TODBCs( hstmt, SQLPrepare( hstmt,                        
			(unsigned char *)"select * from departments where department_id = ?", SQL_NTS ) );

		// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
		TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
									4, 0, &param_department_id, 0, &param_department_idInd ) );

		// 先给参数变量赋值，然后执行SQL语句，查询表中数据
		UpdateData(TRUE); //更新控件关联变量
		param_department_id = m_intQueryDeptID;
	}
	else if ( m_intRadioDeptID == 1 )
	{
		// 准备包含参数标志符的SQL语句
		TODBCs( hstmt, SQLPrepare( hstmt,                        
			(unsigned char *)"select * from departments where department_name = ?", SQL_NTS ) );

		// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
		TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									30, 0, param_department_name, sizeof(param_department_name), &param_department_nameLenOrInd ) );

		// 先给参数变量赋值，然后执行SQL语句，查询表中数据
		UpdateData(TRUE); //更新控件关联变量
		strcpy( (char *)param_department_name, m_strQueryDeptName);
		param_department_nameLenOrInd = SQL_NTS;
	}

	// 执行查询
	retcode = TODBCs( hstmt, SQLExecute(hstmt) );

	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
	{
		//捕获异常
		AfxMessageBox("无权查询!");
		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) );
		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_UNBIND ) );
	}
	else
	{
		// 用游标定位结果集
		retcode = TODBCs( hstmt, SQLFetch( hstmt ) );

		if ( retcode == SQL_NO_DATA )
		{
			findResult = FALSE;
			AfxMessageBox("未找到符合条件的记录!");
		}
		else
		{
			findResult = TRUE;
		}

		while ( retcode != SQL_NO_DATA )
		{
			m_intResultDeptID = result_department_id;
			m_strResultDeptName = result_department_name;
			m_intResultManagerID = result_manager_id;
			m_intResultLocationID = result_location_id;
			m_intResultDeptEmp = result_department_emp;
			retcode = TODBCs( hstmt, SQLFetch( hstmt ) );
			UpdateData(FALSE); //刷新编辑框内容
		}

		// 关闭游标
		TODBCs ( hstmt, SQLCloseCursor( hstmt ) );         

		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) );
	
		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_UNBIND ) );
	}
	
	//CDialog::OnOK();
}

void CQueryDepartmentDlg::OnDepartmentDelete() 
{
	// 使用参数绑定的方法查询表中数据
	SQLUINTEGER  result_department_id;           //输出参数 
	SQLINTEGER	 result_department_idInd = 0;

	if (findResult == TRUE)
	{
		if (AfxMessageBox("确定要删除该记录吗?", MB_OKCANCEL, (UINT)-1) == IDOK)
		{
			// 准备包含参数标志符的SQL语句
			TODBCs( hstmt, SQLPrepare( hstmt,                        
				(unsigned char *)"delete from departments where department_id = ?", SQL_NTS ) );

			// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
			TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
								4, 0, &result_department_id, 0, &result_department_idInd ) );

			// 先给参数变量赋值，然后执行SQL语句，更新表中数据
			UpdateData(TRUE); //更新控件关联变量
			result_department_id = m_intResultDeptID;

			// 执行更新
			retcode = TODBCs( hstmt, SQLExecute(hstmt) );   

			if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
			{
				//捕获异常
				AfxMessageBox("无权删除,或删除失败!");
			}

			// 释放绑定的参数
			TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) );
	
			// 释放绑定的参数
			TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_UNBIND ) );
		}
	}
	else
	{
		AfxMessageBox("不能删除数据,因为未找到符合条件的记录!");
	}
}

void CQueryDepartmentDlg::OnDepartmentUpdate() 
{
	// 使用参数绑定的方法查询表中数据
	SQLUINTEGER  result_department_id;        //输出参数
	SQLVARCHAR   result_department_name[30];  //输出参数
	SQLUINTEGER  result_manager_id;           //输出参数
	SQLUINTEGER  result_location_id;          //输出参数
	SQLINTEGER	 result_department_idInd = 0, result_department_nameLenOrInd = 0;
	SQLINTEGER   result_manager_idInd = 0, result_location_idInd = 0;

	if (findResult == TRUE)
	{
		// 准备包含参数标志符的SQL语句
		TODBCs( hstmt, SQLPrepare( hstmt,                        
			(unsigned char *)"update departments set department_name = ?, manager_id = ?, location_id = ? where department_id = ?", SQL_NTS ) );

		// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
		TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									30, 0, result_department_name, sizeof(result_department_name), &result_department_nameLenOrInd ) );

		TODBCs( hstmt, SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
									4, 0, &result_manager_id, 0, &result_manager_idInd ) );

		TODBCs( hstmt, SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
									4, 0, &result_location_id, 0, &result_location_idInd ) );

		TODBCs( hstmt, SQLBindParameter( hstmt, 4, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
									4, 0, &result_department_id, 0, &result_department_idInd ) );

		// 先给参数变量赋值，然后执行SQL语句，更新表中数据
		UpdateData(TRUE); //更新控件关联变量
		result_department_id = m_intResultDeptID;
		strcpy((char *)result_department_name, m_strResultDeptName);
		result_manager_id = m_intResultManagerID;
		result_location_id = m_intResultLocationID;
		result_department_nameLenOrInd = SQL_NTS;

		// 执行更新
		retcode = TODBCs( hstmt, SQLExecute(hstmt) );

		if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
		{
			//捕获异常
			AfxMessageBox("无权更新,或更新失败!");
		}

		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) );
	
		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_UNBIND ) );
	}
	else
	{
		AfxMessageBox("不能更新数据,因为未找到符合条件的记录!");
	}
}

void CQueryDepartmentDlg::OnRadioDepartmentId() 
{
	m_editQueryDeptID.SetReadOnly(FALSE);
	m_editQueryDeptName.SetReadOnly(TRUE);
	m_intRadioDeptID = 0;
}

void CQueryDepartmentDlg::OnRadioDepartmentName() 
{
	m_editQueryDeptID.SetReadOnly(TRUE);
	m_editQueryDeptName.SetReadOnly(FALSE);
	m_intRadioDeptID = 1;
}