datalink.cpp

数据访问的定义类

	case VT_I1:
		value = var.bVal;
		break;
	case VT_UI2:
	case VT_I2:
		value = var.iVal;
		break;
	case VT_I4:
	case VT_UI4:
		value = var.lVal;
		break;
	case VT_INT:
		value = var.intVal;
		break;
	case VT_R4:
		value = (long)(var.fltVal + 0.5);
		break;
	case VT_R8:
		value = (long)(var.dblVal + 0.5);
		break;
	case VT_DECIMAL:
		value = (long)var;
		break;
	case VT_CY:
		value = (long)var;
		break;
	case VT_BSTR://字符串
	case VT_LPSTR://字符串
	case VT_LPWSTR://字符串
		value = atol((LPCTSTR)(_bstr_t)var);
		break;
	case VT_NULL:
	case VT_EMPTY:
		value = 0;
		break;
	default:
		TRACE(_T("Warning: 未处理的 _variant_t 类型值; 文件: %s; 行: %d\n"), __FILE__, __LINE__);
	}
	return value;
}

double vartof(const _variant_t& var)
{
	double value = 0;
	switch (var.vt)
	{
	case VT_R4:
		value = var.fltVal;
		break;
	case VT_R8:
		value = var.dblVal;
		break;
	case VT_DECIMAL:
		value = (double)var;
		break;
	case VT_CY:
		value = (double)var;
		break;
	case VT_BOOL:
		value = var.boolVal;
		break;
	case VT_UI1:
	case VT_I1:
		value = var.bVal;
		break;
	case VT_UI2:
	case VT_I2:
		value = var.iVal;
		break;
	case VT_UI4:
	case VT_I4:
		value = var.lVal;
		break;
	case VT_INT:
		value = var.intVal;
		break;
	case VT_BSTR://字符串
	case VT_LPSTR://字符串
	case VT_LPWSTR://字符串
		value = atof((LPCTSTR)(_bstr_t)var);
		break;
	case VT_NULL:
	case VT_EMPTY:
		value = 0;
		break;
	default:
		value = 0;
		TRACE(_T("Warning: 未处理的 _variant_t 类型值; 文件: %s; 行: %d\n"), __FILE__, __LINE__);
	}
	return value;
}

CString vartostr(const _variant_t &var)
{
	CString strFormat;
	CString strValue;

	switch (var.vt)
	{
	case VT_BSTR://字符串
	case VT_LPSTR://字符串
	case VT_LPWSTR://字符串
		strValue = (LPCTSTR)(_bstr_t)var;
		break;
	case VT_I1:
	case VT_UI1:
		strValue.Format("%d", var.bVal);
		break;
	case VT_I2://短整型
		strValue.Format("%d", var.iVal);
		break;
	case VT_UI2://无符号短整型
		strValue.Format("%d", var.uiVal);
		break;
	case VT_INT://整型
		strValue.Format("%d", var.intVal);
		break;
	case VT_I4: //整型
		strValue.Format("%d", var.lVal);
		break;
	case VT_I8: //长整型
		strValue.Format("%d", var.lVal);
		break;
	case VT_UINT://无符号整型
		strValue.Format("%d", var.uintVal);
		break;
	case VT_UI4: //无符号整型
		strValue.Format("%d", var.ulVal);
		break;
	case VT_UI8: //无符号长整型
		strValue.Format("%d", var.ulVal);
		break;
	case VT_VOID:
		strValue.Format("%8x", var.byref);
		break;
	case VT_R4://浮点型
		strValue.Format("%.2f", var.fltVal);
		break;
	case VT_R8://双精度型
		strValue.Format("%.4f", var.dblVal);
		break;
	case VT_DECIMAL: //小数
		strFormat.Format("%%.%df", min(var.decVal.scale, 6));
		strValue.Format(strFormat, (double)var);
		break;
	case VT_CY:
		{
			COleCurrency cy = var.cyVal;
			strValue = cy.Format();
		}
		break;
	case VT_BLOB:
	case VT_BLOB_OBJECT:
	case 0x2011:
		strValue = "[BLOB]";
		break;
	case VT_BOOL://布尔型
		strValue = var.boolVal ? "TRUE" : "FALSE";
		break;
	case VT_DATE: //日期型
		{
			DATE dt = var.date;
			COleDateTime da = COleDateTime(dt); 
			strValue = da.Format("%Y-%m-%d");
			//strValue = da.Format("%d/%m/%Y");
		}
		break;
	case VT_NULL://NULL值
		strValue = "";
		break;
	case VT_EMPTY://空
		strValue = "";
		break;
	case VT_UNKNOWN://未知类型
	default:
		strValue = "UN_KNOW";
		break;
	}
	return strValue;
}





// CConnectionString。
// 有关类定义的信息，请参阅 DataLink.h

CConnectionString::CConnectionString()
{ 
}

CConnectionString::CConnectionString(const CString& stringSrc) : CString(stringSrc)
{
}

CConnectionString::CConnectionString(LPCTSTR lpsz) : CString(lpsz)
{
}

CConnectionString::CConnectionString(LPCTSTR lpch, int nLength) : CString(lpch, nLength)
{
}

CConnectionString::CConnectionString(UINT nProviderType, LPCTSTR datasrc, 
	LPCTSTR catalog /* = NULL */, LPCTSTR userid /* = NULL */, LPCTSTR password /* = NULL */)
{
	CString provider;
	switch (nProviderType)
	{
	case 0:
		provider = _T("Microsoft.Jet.OLEDB.4.0");
		break;
	case 1:
		provider = _T("MSDAORA");
		break;
	case 2:
		provider = _T("SQLOLEDB");
		break;
	default:
		break;
	}

	// 构造连接字符串
	*this = CConnectionString(provider, datasrc, catalog, userid, password);
}

CConnectionString::CConnectionString(LPCTSTR provider, LPCTSTR datasrc, 
	LPCTSTR catalog /* = NULL */, LPCTSTR userid /* = NULL */, LPCTSTR password /* = NULL */)
{
	ASSERT(provider != NULL);
	ASSERT(datasrc != NULL);
	SetKeyValue(_T("Provider"), provider);
	SetKeyValue(_T("Data Source"), datasrc);
	SetKeyValue(_T("Initial Catalog"), catalog);
	SetKeyValue(_T("User ID"), userid);
	SetKeyValue(_T("Password"), password);
	SetKeyValue(_T("Persist Security Info"), _T("False"));
}

CConnectionString::~CConnectionString()
{
}

// 设置关键字的值
void CConnectionString::SetKeyValue(LPCTSTR keyName, LPCTSTR value)
{
	ASSERT(keyName != NULL);
	LPCTSTR lpchLeft, lpchRight;
	lpchLeft = lpchRight = LPCTSTR(*this);
	TCHAR szKeyName[_MAX_PATH], szValue[_MAX_PATH];
	while (lpchRight != NULL)
	{
		lpchRight = _tsplitattr(lpchRight, szKeyName, NULL);
		if (_tcsicmp(szKeyName, keyName) == 0)
			break;
		lpchLeft = lpchRight;
	}

	_tcscpy2(szKeyName, keyName, _T('='));
	_tcscpy2(szValue, value, _T('\"'));
	CString strItem;
	if (value != NULL && lstrlen(value) > 0)
	{
		if (_tcscspn(value, _T(" \'\";")) < (size_t)lstrlen(value))
			strItem.Format(_T("%s=\"%s\";"), szKeyName, szValue);
		else
			strItem.Format(_T("%s=%s;"), szKeyName, szValue);
	}

	int nStart = (lpchLeft == NULL) ? GetLength() : (int)(lpchLeft - LPCTSTR(*this));
	int nEnd = (lpchRight == NULL) ? GetLength() : (int)(lpchRight -LPCTSTR(*this));
	Delete(nStart, nEnd - nStart);
	Insert(nStart, strItem);
}

CString CConnectionString::GetKeyValue(LPCTSTR keyName)
{
	CString value;
	LPCTSTR lpFullString = LPCTSTR(*this);
	TCHAR szKeyName[_MAX_PATH], szValue[_MAX_PATH];
	while (lpFullString != NULL)
	{
		lpFullString = _tsplitattr(lpFullString, szKeyName, szValue);
		if (_tcsicmp(szKeyName, keyName) == 0)
		{
			value = szValue;
			break;
		}
	}
	return value;
}

CString CConnectionString::GetKeyName(int nIndex)
{
	CString keyName;
	LPCTSTR lpFullString = LPCTSTR(*this);
	TCHAR szKeyName[_MAX_PATH];
	while (nIndex >= 0)
	{
		if (lpFullString == NULL)
			return keyName;
		lpFullString = _tsplitattr(lpFullString, szKeyName, NULL);
		nIndex--;
	}
	keyName = szKeyName;
	return keyName;
}

// 测试数据链接
BOOL CConnectionString::TestConnect()
{
	if (IsEmpty())
		return FALSE;

	IDispatch* pConnection = OpenConnection();
	if (pConnection == NULL)
		return FALSE;

	// 关闭数据库连接
	pConnection->Release();
	return TRUE;
}

// 打开数据库连接
IDispatch* CConnectionString::OpenConnection()
{
	_ConnectionPtr pConnection;
	if (pConnection.CreateInstance(_T("ADODB.Connection")) != S_OK)
		return NULL;

	// 连接数据库
	_bstr_t sEmpty = _T("");
	pConnection->put_ConnectionTimeout(5);
	HRESULT _hr = pConnection->raw_Open((_bstr_t)(*this), sEmpty, sEmpty, adConnectUnspecified);
	if (FAILED(_hr))
	{
		_SetErrorInfo(pConnection->Errors);
		return NULL;
	}

	pConnection.AddRef();
	return (IDispatch*)pConnection;
}


// CSqlString。
// 有关类定义的信息，请参阅 DataLink.h

CSqlString::CSqlString()
{
}

CSqlString::CSqlString(const CString& stringSrc) : CString(stringSrc)
{
}

CSqlString::CSqlString(LPCTSTR lpsz) : CString(lpsz)
{
}

CSqlString::CSqlString(LPCTSTR lpch, int nLength) : CString(lpch, nLength)
{
}

CSqlString::~CSqlString()
{
}

CString CSqlString::Execute(IDispatch* pConnection, LPCTSTR lpszCmdText, 
	UINT* pRecordsAffected)
{
	ASSERT(pConnection != NULL);
	ASSERT(lpszCmdText != NULL);

	CString strResult;
	_RecordsetPtr pRecordset;
	_variant_t var;
	CString strSQL;
	int iSubString = 0;
	UINT nRecordsAffected = 0;
	HRESULT _hr = S_OK;

	// 使用连接对象执行 SQL 语句
	_ConnectionPtr pConnection1 = (_ConnectionPtr)pConnection;
	while (AfxExtractSubString(strSQL, lpszCmdText, iSubString++, _T(';')))
	{
		_hr = pConnection1->raw_Execute((_bstr_t)strSQL, &var, adCmdText, &pRecordset);
		if (FAILED(_hr))
		{
			_SetErrorInfo(pConnection1->Errors);
			nRecordsAffected = -1;
			break;
		}

		if ((pRecordset->State & adStateOpen) && (!pRecordset->IsEOF))
		{
			// 将记录集的第一行以TAB分隔符输出
			long nFieldCount = pRecordset->Fields->GetCount();
			for (long nIndex=0; nIndex<nFieldCount; nIndex++)
			{
				var = pRecordset->GetCollect(COleVariant(nIndex));
				if (nIndex > 0)
					strResult.Insert(strResult.GetLength(), _T('\t'));
				strResult.Insert(strResult.GetLength(), vartostr(var));
			}
			// 关闭数据集和数据库连接
			pRecordset->Close();
		}
		else
		{
			// 增加受影响记录计数
			nRecordsAffected += var.intVal;
		}
	}

	if (pRecordsAffected != NULL)
		*pRecordsAffected = nRecordsAffected;

	return strResult;
}

CString CSqlString::Execute()
{
	CString strResult;
	if (IsEmpty())
	{
		m_nRecordsAffected = 0;
		return strResult;
	}

	CConnectionString strConnection = GetConnectionString();
	IDispatch* pConnection = strConnection.OpenConnection();
	if (pConnection != NULL)
	{
		strResult = CSqlString::Execute(pConnection, *this, &m_nRecordsAffected);

		// 关闭数据库连接
		pConnection->Release();
	}

	return strResult;
}

CString CSqlString::Execute(LPCTSTR lpszFormat, ...)
{
	va_list argList;;
	va_start(argList, lpszFormat);
	CString& str = ExecuteV(lpszFormat, argList);
	va_end(argList);
	return str;
}

CString CSqlString::ExecuteV(LPCTSTR lpszFormat, va_list args)
{
	CSqlString strSQL;
	strSQL.FormatV(lpszFormat, args);
	return strSQL.Execute();
}

int CSqlString::GetRecordsAffected() const
{
	return m_nRecordsAffected;
}

CString CSqlString::GetConnectionString() const
{
	if (!m_strConnectionString.IsEmpty())
		return m_strConnectionString;

	_AFX_DATALINK_STATE* pState = _afxDataLinkState.GetData();
	return pState->m_pszConnectionString;
}

void CSqlString::SetConnectionString(LPCTSTR value)
{
	m_strConnectionString = value;
}

void CSqlString::Normalize()
{
	SqlString_Normalize(*this);
}

// select_list
CString CSqlString::GetSelectList()
{
	int nIndex, nCount;
	SqlString_SelectList(*this, nIndex, nCount);
	return Mid(nIndex, nCount);
}

void CSqlString::SetSelectList(LPCTSTR lpszSelectList)
{
	int nIndex, nCount;
	if (!SqlString_SelectList(*this, nIndex, nCount))
		SqlString_Insert(*this, nIndex, _T("select "));
	SqlString_Stuff(*this, nIndex, nCount, lpszSelectList);
}

// column_alias, column_name and expression
int CSqlString::GetColumnCount()