remotedbdlg.cpp

访问远程数据库MS SQLServer实例

	_variant_t var;
	int i = 0;

	try
	{
		while(!m_pRecordset->adoEOF)
		{
			var=m_pRecordset->GetCollect("name");
			if(var.vt!=VT_NULL)
				strName = (LPCSTR)_bstr_t(var);

			var=m_pRecordset->GetCollect("sex");
			if(var.vt!=VT_NULL)
				strSex = (LPCSTR)_bstr_t(var);

			var=m_pRecordset->GetCollect("age");
			if(var.vt!=VT_NULL)
				strAge.Format("%d",var.intVal);

			m_Data.InsertItem(i,strName);
			m_Data.SetItemText(i,1,strSex);
			m_Data.SetItemText(i,2,strAge);

			m_pRecordset->MoveNext();

			i++;
		}
	}
	catch(_com_error &e)
	{
		AfxMessageBox("读取个人信息表内数据失败！");
		AfxMessageBox(e.ErrorMessage());
	}
}



BOOL CRemoteDBDlg::DestroyWindow() 
{
	// TODO: Add your specialized code here and/or call the base class
 	//关闭记录集
	if (m_pRecordset->State)
 		m_pRecordset->Close();
 	m_pRecordset = NULL;
	


	return CDialog::DestroyWindow();
}

void CRemoteDBDlg::OnClose() 
{
	// TODO: Add your message handler code here and/or call default
	DestroyWindow();
	CDialog::OnClose();
}

void CRemoteDBDlg::OnClickData(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	POSITION posUserSel = m_Data.GetFirstSelectedItemPosition();
	if(posUserSel == NULL)
	{
		m_Name=m_Sex=m_Age="";
		m_BDelData.EnableWindow(false);
		m_BModifyData.EnableWindow(false);
		UpdateData(false);
		return;
	}

	m_BDelData.EnableWindow(true);
	m_BModifyData.EnableWindow(true);

	
	m_nItem = m_Data.GetNextSelectedItem(posUserSel);

	m_Name=m_Data.GetItemText(m_nItem,0);
	m_Sex=m_Data.GetItemText(m_nItem,1);
	m_Age=m_Data.GetItemText(m_nItem,2);

	UpdateData(false);

	*pResult = 0;
}

void CRemoteDBDlg::OnDeldata() 
{
	// TODO: Add your control notification handler code here
 	if (MessageBox("确认删除?","提示",MB_OKCANCEL | MB_ICONQUESTION) == IDCANCEL)
		return;

	CString strSQL;
	strSQL = "DELETE FROM 个人信息 WHERE name='"+m_Name+"'";

	theApp.ExcuteCommandADO(strSQL);
	
	ListData();

	m_BDelData.EnableWindow(false);
	m_BModifyData.EnableWindow(false);


	m_Name=m_Sex=m_Age="";
	UpdateData(false);

	MessageBox("删除成功！");

}

void CRemoteDBDlg::OnInsertdata() 
{
	// TODO: Add your control notification handler code here
	UpdateData(TRUE);
	//判断所填信息是否完整
	if ( (m_Name=="") || (m_Sex=="") || (m_Age=="")  )
	{
		MessageBox("您填写的信息不全,请检查后重新输入!");
		m_Name=m_Sex=m_Age="";
		UpdateData(FALSE);
		return;
	}

	int i;
	int ItemNum=m_Data.GetItemCount();
	for (i=0;i<ItemNum;i++)
	{
		if (m_Name == m_Data.GetItemText(i,0))
		{
			AfxMessageBox("您填写的用户名已存在,请检查后重新输入!");
			m_Name=m_Sex=m_Age="";
			UpdateData(FALSE);
			return;
		}
	}	

	//提示是否增加
 	if (MessageBox("确认增加?","提示",MB_OKCANCEL | MB_ICONQUESTION) == IDCANCEL)
	{
		m_Name=m_Sex=m_Age="";
		UpdateData(FALSE);
		return;
	}

		

	CString strSQL;
	strSQL.Format("INSERT INTO 个人信息 (name,sex,age) VALUES ('"+m_Name+\
		"','"+m_Sex+"',%d)",atoi(m_Age));

	theApp.ExcuteCommandADO(strSQL);


	m_Name=m_Sex=m_Age="";
	UpdateData(false);

	ListData();

	MessageBox("插入成功！");

}

void CRemoteDBDlg::OnModifydata() 
{
	// TODO: Add your control notification handler code here
	if (MessageBox("确认修改?","提示",MB_OKCANCEL | MB_ICONQUESTION) == IDCANCEL)
	{
		m_Name=m_Sex=m_Age="";
		UpdateData(FALSE);
		return;
	}

//////////////////////////////////////////////////////////////////////////////
	m_pRecordsetMod.CreateInstance(__uuidof(Recordset));
	CString strSQLMod,strSelName;
	strSelName=m_Data.GetItemText(m_nItem,0);
	strSQLMod = "SELECT * FROM 个人信息 WHERE name = '"+strSelName+"'";
	m_pRecordsetMod->Open(strSQLMod.AllocSysString(),                
						   theApp.m_pConnection.GetInterfacePtr(),
						   adOpenDynamic,
						   adLockOptimistic,
						   adCmdText);
	//////////////////////////////////////////////////////////
	UpdateData();
	try
	{
		// 修改当前记录的字段值
		m_pRecordsetMod->PutCollect("name", _variant_t(m_Name));
		m_pRecordsetMod->PutCollect("sex", _variant_t(m_Sex));
		m_pRecordsetMod->PutCollect("age", (long)atoi(m_Age));
		m_pRecordsetMod->Update();
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}


	m_Name=m_Sex=m_Age="";
	UpdateData(false);

	ListData();

	m_BDelData.EnableWindow(false);
	m_BModifyData.EnableWindow(false);

	MessageBox("修改成功！");

}

void CRemoteDBDlg::OnDelalldata() 
{
	// TODO: Add your control notification handler code here
 	if (MessageBox("删除全部数据?","提示",MB_OKCANCEL | MB_ICONQUESTION) == IDCANCEL)
		return;

	CString strSQL;
	strSQL = "DELETE * FROM 个人信息";

	theApp.ExcuteCommandADO(strSQL);
	m_Data.DeleteAllItems();

	
	MessageBox("删除成功！");
}

void CRemoteDBDlg::OnListdata() 
{
	// TODO: Add your control notification handler code here
	ListData();
	
}

void CRemoteDBDlg::OnSelendokItem() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	if (m_Item == "年龄")
	{
		m_ctrOper.ShowWindow(true);
	}
	else
	{
		m_ctrOper.ShowWindow(false);
		m_ctrTerm2.ShowWindow(false);
	}
}

void CRemoteDBDlg::OnSelendokOper() 
{
	// TODO: Add your control notification handler code here
	UpdateData();
	if (m_Oper == "介于")
	{
		m_ctrTerm2.ShowWindow(true);
	}
	else
	{
		m_ctrTerm2.ShowWindow(false);
	}	
}

void CRemoteDBDlg::OnQuery() 
{
	// TODO: Add your control notification handler code here
	CString strQuery;

	UpdateData();

	if (m_ctrOper.IsWindowVisible()) 
	{	
		if ((m_Item == "") || (m_Oper == ""))
		{
			AfxMessageBox("请选择查询条件！");
			return;
		}
	}
	else
	{
		if (m_Item == "") 
		{
			AfxMessageBox("请选择查询条件！");
			return;
		}
	}

	if (m_ctrTerm2.IsWindowVisible())
	{
		if ((m_Term == "")||(m_Term2 == ""))
		{
			AfxMessageBox("请选择查询内容！");
			return;
		}
	}
	else
	{
		if (m_Term == "")
		{
			AfxMessageBox("请选择查询内容！");
			return;
		}
	}


	if (m_Item == "姓名")
		strQuery = "SELECT * FROM 个人信息 WHERE name = '"+m_Term+"' ";
	else if (m_Item == "性别")
		strQuery = "SELECT * FROM 个人信息 WHERE sex = '"+m_Term+"' ";
	else
	{
		if (m_Oper != "介于")
			strQuery.Format( "SELECT * FROM 个人信息 WHERE age "+m_Oper+" %s",m_Term);
		else
			strQuery.Format( "SELECT * FROM 个人信息 WHERE age BETWEEN %s AND %s",m_Term,m_Term2);
	}
	ListQueryData(strQuery);

	m_ctrOper.ShowWindow(false);
	m_ctrTerm2.ShowWindow(false);
	m_Item=m_Term="";
	UpdateData(false);	
}

void CRemoteDBDlg::ListQueryData(CString str)
{
	
//////////////////////////////////////////////////////////////////////////////
	m_pRecordsetQuery.CreateInstance(__uuidof(Recordset));
	m_pRecordsetQuery->Open(str.AllocSysString(),                
						   theApp.m_pConnection.GetInterfacePtr(),
						   adOpenDynamic,
						   adLockOptimistic,
						   adCmdText);
//////////////////////////////////////////////////////////
	CString strName,strSex,strAge;
	
	if(!m_pRecordsetQuery->BOF)
			m_pRecordsetQuery->MoveFirst();
	else
	{
		AfxMessageBox("表内数据为空");
		return;
	}
	
	m_Data.DeleteAllItems();
	_variant_t var;
	int i = 0;

	try
	{
		while(!m_pRecordsetQuery->adoEOF)
		{
			var=m_pRecordsetQuery->GetCollect("name");
			if(var.vt!=VT_NULL)
				strName = (LPCSTR)_bstr_t(var);

			var=m_pRecordsetQuery->GetCollect("sex");
			if(var.vt!=VT_NULL)
				strSex = (LPCSTR)_bstr_t(var);

			var=m_pRecordsetQuery->GetCollect("age");
			if(var.vt!=VT_NULL)
				strAge.Format("%d",var.intVal);

			m_Data.InsertItem(i,strName);
			m_Data.SetItemText(i,1,strSex);
			m_Data.SetItemText(i,2,strAge);

			m_pRecordsetQuery->MoveNext();

			i++;
		}
	}
	catch(_com_error &e)
	{
		AfxMessageBox("读取用户信息表内数据失败！");
		AfxMessageBox(e.ErrorMessage());
	}
}

void CRemoteDBDlg::OnImport() 
{
	// TODO: Add your control notification handler code here
	int loadtime=0;
	UpdateData();
	loadtime = atoi(m_Hour);
	loadtime = 60 *loadtime;//分钟
	if (transferParam == NULL) 
	{
		transferParam = new TransferParam;	//定义一个结构体
	}
	TransferParam* transferParam = new TransferParam;
	transferParam->accessODBC = "tanfen";
	transferParam->oraODBC = "SecSourceData";//SecSourceData
	transferParam->sampleID = "";
	transferParam->timeInterval = loadtime;
	transferParam->sampleType = 0;
	transferParam->yuanYeSampleID = "269";
	transferParam->cFenSampleID = "269";
	transferParam->tableName = "原始数据";
	transferParam->iGroup = 2;
	transferParam->connectDelay = 1000;
	transferParam->retryCount = 5;
	transferParam->caller = this->GetSafeHwnd();
	
	hThread = CreateOra2AccessThread(transferParam,hThread);	//全局函数，用来创建线程	
}