adodemoview.cpp

ADO&VC&SQL的例子

	//
	ULONG ulColCount=0;
	if(!OpenCurRecordset(m_strCurTableName)) return;
	if(!RefreshColumn(&ulColCount)) return;
	if(0==ulColCount) return;
	if(!RefreshData(ulColCount)) return;
}

void CADODemoView::EraseList()
{
	CListCtrl& ctlList = (CListCtrl&) GetListCtrl();
	ctlList.DeleteAllItems();
	while(ctlList.DeleteColumn(0));
	UpdateWindow();
}

BOOL CADODemoView::RefreshColumn(ULONG *pulColCount)
{
	if(!m_fConnected) return FALSE;
	//
	HRESULT hr;
	CListCtrl &listCtrl = GetListCtrl();
	CString strColName;
	Fields* fields = NULL;


	try{	
		hr = m_recordset->get_Fields(&fields);
		if (SUCCEEDED(hr))
			hr = fields->get_Count((long *)pulColCount);

		for(long i=0;i<*pulColCount;i++){
			BSTR bstrColName;
			hr = fields->Item[i]->get_Name(&bstrColName);
			strColName = bstrColName;
			
			int nWidth = listCtrl.GetStringWidth(strColName) + 15;
			listCtrl.InsertColumn(i, strColName, LVCFMT_LEFT, nWidth);
		}

		if (SUCCEEDED(hr))	fields->Release();
	}
	catch (_com_error &e){
	   MessageBox(e.ErrorMessage()); 
	   return FALSE;
	}

	return (SUCCEEDED(hr));
}

BOOL CADODemoView::RefreshData(ULONG ulColCount)
{
	if(!m_fConnected) return FALSE;
	if(0 == ulColCount) return FALSE;
	//
	HRESULT hr;
	CListCtrl &listCtrl = GetListCtrl();
	CString strValue;
	VARIANT var_value;

	VARIANT_BOOL ValEof;
	int nRowCount = 0;
	Field *field = NULL;

	VARIANT varCounter;
	varCounter.vt = VT_I4;
	varCounter.lVal = 0;

	try{
		ValEof = m_recordset->get_adoEOF(&ValEof);
		while(TRUE){
			if(ValEof) break;
			varCounter.lVal = 0;
			m_recordset->Fields->get_Item(varCounter, &field);
			DataTypeEnum data_type;
			field->get_Value(&var_value);
			strValue = VariantToCtring(var_value);
				
			listCtrl.InsertItem(nRowCount, strValue);
			for(int i=1;i<ulColCount;i++){
				varCounter.lVal = i;
				m_recordset->Fields->get_Item(varCounter, &field);  
				field->get_Value(&var_value);
				strValue = VariantToCtring(var_value);
				listCtrl.SetItemText(nRowCount, i, strValue);
			}
			nRowCount ++;
			m_recordset->MoveNext(); 
			m_recordset->get_adoEOF(&ValEof);
		}
		m_recordset->Close(); 
	}
	catch (_com_error &e){
	   MessageBox(e.ErrorMessage()); 
	   return FALSE;
	}

	return (SUCCEEDED(hr));
}

BOOL CADODemoView::OpenCurRecordset(CString strTableName)
{
	if(!m_fConnected) return FALSE;
	if(strTableName.IsEmpty()) return FALSE;
	//
	HRESULT hr;
	CString strQuery;
	strQuery.Format("select * from %s", strTableName);
	_bstr_t query = strQuery;
	_bstr_t source =  m_strSource;
	try{
		hr = m_recordset->Open(query, source, adOpenDynamic, adLockOptimistic, adCmdText);
	}

	catch (_com_error &e){
	   MessageBox(e.ErrorMessage()); 
	   return FALSE;
	}
	return (SUCCEEDED(hr));
}

CString CADODemoView::VariantToCtring(VARIANT var)
{
	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 = var.date;
		time_value = cur_time;
		strValue = time_value.Format("%A, %B %d, %Y");
		break;  
	case VT_BOOL: strValue.Format("%d", var.boolVal); break;  
	default: strValue = _T(""); break;
	}
	return strValue;
}

BOOL CADODemoView::InsertRow(CString strTableName, CStringArray &saValues)
{
	int nColNum = 0;
	if(!m_fConnected) return FALSE;
	if(strTableName.IsEmpty()) return FALSE;
	if(0 == (nColNum = saValues.GetSize())) return FALSE;
	//
	HRESULT hr;
	Fields* fields = NULL;
	Field* field = NULL;
	VARIANT varValue;
	VARIANT varCount;
	DataTypeEnum data_type;
	varCount.vt = VT_I4;

	if(!OpenCurRecordset(strTableName)) return FALSE;
	try{
		hr = m_recordset->AddNew(); 
		hr = m_recordset->get_Fields(&fields);
		for(int i=0;i<nColNum;i++){			
			varCount.lVal = i;
			hr = fields->get_Item(varCount, &field);			
			field->get_Type(&data_type); 
			if(!ConstructVar(saValues.GetAt(i), data_type, &varValue)) return FALSE;
			hr = field->put_Value(varValue);
		}
		fields->Release();
		field->Release();
	}
	catch (_com_error &e){
	   MessageBox(e.ErrorMessage()); 
	   return FALSE;
	}
	
	return (SUCCEEDED(hr));
}


BOOL CADODemoView::ConstructVar(CString strValue, int type, VARIANT *var)
{
	COleDateTime var_date;
	CURRENCY cy;
	cy.Hi = 0;
	switch(type){
	case adInteger:
		var->vt = VT_I4;
		var->lVal = atoi(strValue);
		break;
	case adCurrency:
		var->vt = VT_CY;
		cy.Lo = atoi(strValue);
		cy.int64 = atoi(strValue);
		var->cyVal = cy;
		break;
	case adChar:
	case adVarWChar:
		var->vt = VT_BSTR;
		var->bstrVal = (_bstr_t)strValue;
		break;
	case adDBTimeStamp:
		var->vt = VT_DATE;
		var->date = var_date;
		break;
	default:
		var->vt = VT_EMPTY;
		break;
	}

	return TRUE;
}

BOOL CADODemoView::GetInfoArray(CString strTableName, CUIntArray &uaID, CStringArray &saArray)
{
	if(!m_fConnected) return FALSE;
	//
	HRESULT hr;
	CString strQuery;
	CString strValue;
	VARIANT var_value;

	VARIANT_BOOL ValEof;
	int nRowCount = 0;
	Field *field = NULL;

	_variant_t var_t;
	_bstr_t bst_t;

	VARIANT varCounter;
	varCounter.vt = VT_I4;
	varCounter.lVal = 0;


	strQuery.Format("select * from %s", strTableName);
	_bstr_t query = strQuery;
	_bstr_t source =  m_strSource;
	try{
		hr = m_recordset->Open(query, source, adOpenDynamic, adLockOptimistic, adCmdText);
		ValEof = m_recordset->get_adoEOF(&ValEof);
		while(TRUE){
			if(ValEof) break;

			varCounter.lVal = 0;
			m_recordset->Fields->get_Item(varCounter, &field);
			field->get_Value(&var_value);
			uaID.Add(var_value.lVal); 
				
			varCounter.lVal = 1;
			m_recordset->Fields->get_Item(varCounter, &field);  
			field->get_Value(&var_value);
			var_t = var_value;
			bst_t = var_t;
			saArray.Add((const char *)bst_t); 

			nRowCount ++;
			m_recordset->MoveNext(); 
			m_recordset->get_adoEOF(&ValEof);
		}
		m_recordset->Close(); 
	}
	catch (_com_error &e){
	   MessageBox(e.ErrorMessage()); 
	   return FALSE;
	}

	return (SUCCEEDED(hr));
}

BOOL CADODemoView::DeleteRow(CString strTableName, CString strColName, CString strValue)
{
	if(!m_fConnected) return FALSE;
	//
	HRESULT hr;
	CString strQuery;
	VARIANT_BOOL ValEof;

	strQuery.Format("select * from %s where %s=%s", strTableName, strColName, strValue);
	_bstr_t query = strQuery;
	_bstr_t source =  m_strSource;
	try{
		hr = m_recordset->Open(query, source, adOpenDynamic, adLockOptimistic, adCmdText);
		ValEof = m_recordset->get_adoEOF(&ValEof);
		if(!ValEof)	m_recordset->Delete(adAffectCurrent); 
		m_recordset->Close(); 
	}
	catch (_com_error &e){
	   MessageBox(e.ErrorMessage()); 
	   return FALSE;
	}


	return (SUCCEEDED(hr));
}