adodb.cpp

学生管理系统 一个学生管理的东西

// DB.cpp : 实现文件
//

#include "stdafx.h"
#include "afx.h"
#include "ADODB.h"


// CDB
IMPLEMENT_DYNCREATE(CADOConnection, CObject)
CADOConnection::CADOConnection()
{    
	::CoInitialize(NULL);
   HRESULT hr;
   try
   {
     hr=this->m_pConnection.CreateInstance("ADODB.Connection");
     if(SUCCEEDED(hr))
     {
		return;
	 }
   }
   catch(_com_error e)///捕捉异常
   {
        CString errormessage;
        errormessage.Format("连接数据库失败!\r\n错误信息:%s",e.ErrorMessage());
        AfxMessageBox(errormessage);///显示错误信息
    } 
}

CADOConnection::~CADOConnection()
{
	//if (this->m_pConnection->State  
	
	if (this->IsOpen()) 
	{
	  this->m_pConnection->Close();
	  //this->m_pConnection->Release();  
	  this->m_pConnection=NULL; 
	}
	
	::CoUninitialize();
}

bool CADOConnection::ConDB(CString DbName,CString Password,int DBType)
{
  //HRESULT hr;
  CString constr;
  //获取连接字符串 
  ASSERT(DbName.Trim()!=""); 
  if (DBType==0) 
  {
     constr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+DbName+";Persist Security Info=False;";
     if(Password.Trim()!="")
	 {
	  constr=constr+"Jet OLEDB:Database Password="+Password+";";
	 }
  }
  else
  {
  
  }
  return this->Open(constr);

}


bool CADOConnection::Open(LPCTSTR lpstrConnection)
{
	HRESULT hr = S_OK;

	if(IsOpen())
		Close();

	if(strcmp(lpstrConnection, _T("")) != 0)
		m_strConnection = lpstrConnection;

	ASSERT(!m_strConnection.IsEmpty());

	try
	{
		hr = m_pConnection->Open(_bstr_t(m_strConnection), "", "", NULL);
		return hr == S_OK;
	}
	catch(_com_error &e)
	{
		CString errormessage;
        errormessage.Format("连接数据库失败!\r\n错误信息:%s",e.ErrorMessage());
        AfxMessageBox(errormessage);///显示错误信息
	}
	return false;
}

bool CADOConnection::Execute(LPCTSTR lpstrExec)
{
	ASSERT(m_pConnection != NULL);
	ASSERT(strcmp(lpstrExec, _T("")) != 0);

	try
	{
		m_pConnection->Execute(_bstr_t(lpstrExec), NULL, adExecuteNoRecords);
	}
	catch(_com_error &e)
	{
		CString errormessage;
        errormessage.Format("连接数据库失败!\r\n错误信息:%s",e.ErrorMessage());
        AfxMessageBox(errormessage);///显示错误信息
	}
	return true;	
}


bool CADOConnection::IsOpen()
{
	if(m_pConnection )
		return m_pConnection->GetState() != adStateClosed;
	return false;
}

void CADOConnection::Close()
{
	if(IsOpen())
		m_pConnection->Close();
}

// CDB 成员函数

//CADODataset
IMPLEMENT_DYNCREATE(CADODataset, CObject)
CADODataset::CADODataset()
{
	//m_pRecordset.CreateInstance("ADODB.Recordset");
	m_pRecordset = NULL;
	m_pCmd = NULL;
	m_strQuery = _T("");
	m_pRecordset.CreateInstance(__uuidof(Recordset));
	m_pCmd.CreateInstance(__uuidof(Command));
	m_nEditStatus = CADODataset::dbEditNone;
	m_nSearchDirection = CADODataset::searchForward;
}

CADODataset::~CADODataset()
{  
	Close();
	m_pRecordset.Release();
	m_pCmd.Release();
	m_pRecordset = NULL;
	m_pCmd = NULL;
	m_strQuery = _T("");
	m_nEditStatus = dbEditNone;
}

_RecordsetPtr CADODataset::OpenSql(LPCTSTR TblName,LPCTSTR FldList,LPCTSTR Constr,LPCSTR OrderBy)
{  
	//ASSERT(this->m_pAdocon->m_pConnection!=NULL);
   ASSERT(this->m_pConnection!=NULL);
   char sqlstr[256];
   strcpy(sqlstr,"select ");
   strcat(sqlstr,FldList); strcat(sqlstr," from ");
   strcat(sqlstr,TblName);strcat(sqlstr,"  ");
   strcat(sqlstr,Constr);strcat(sqlstr,"  ");strcat(sqlstr,OrderBy);
   if (IsOpen())
   {
	   this->m_pRecordset->Close();
     }
   m_pRecordset->Open((_variant_t) sqlstr,
	                    this->m_pConnection.GetInterfacePtr(),
					   adOpenStatic,
					   adLockOptimistic,
					   adCmdText);
  return m_pRecordset;
}
bool CADODataset::ExecSql(LPCSTR SqlStr)
{
   ASSERT(this->m_pConnection!=NULL);
   ASSERT(this->m_pCmd!=NULL); 
   m_pCmd->ActiveConnection = this->m_pConnection;
   m_pCmd->CommandText = _bstr_t(SqlStr);
   m_pCmd->CommandType = adCmdText;		
   m_pCmd->Execute(NULL, NULL, adCmdText);
   return true;
}
_RecordsetPtr    CADODataset::ExecStoreProc(LPCSTR SqlProc)
{
   ASSERT(this->m_pConnection!=NULL);
   ASSERT(this->m_pCmd!=NULL); 
   m_pCmd->ActiveConnection = this->m_pConnection;
   m_pCmd->CommandText = _bstr_t(SqlProc);
   m_pCmd->CommandType = adCmdStoredProc;		
   this->m_pRecordset=m_pCmd->Execute(NULL, NULL, adCmdText);
   return this->m_pRecordset;
}
void CADODataset::FillList(CListCtrl &ListCtrl1)
{
  	if(this->IsOpen()) 
	{
      ListCtrl1.Invalidate(true);
	  this->ClearList(ListCtrl1);
	  this->FillColumn(ListCtrl1);
	  this->FillContent(ListCtrl1);
	  ListCtrl1.Invalidate(false);
	}
	else
	{
		AfxMessageBox("记录集没有打开！");
	}
}

void CADODataset::ClearList(CListCtrl &ListCtrl1)
{
	 ListCtrl1.DeleteAllItems ();
	
	int nColumnCount = ListCtrl1.GetHeaderCtrl()->GetItemCount();
	// Delete all of the columns.
	for (int i=0;i < nColumnCount;i++)
	{
		ListCtrl1.DeleteColumn (0);
	}
  
}
void CADODataset::FillColumn(CListCtrl &ListCtrl1)
{
	int fieldCount=m_pRecordset->Fields ->Count;
	int fieldLength = 0;
	FieldPtr m_fieldCtl;

	for(int i=0;i<fieldCount;i++)
	{
		m_fieldCtl = m_pRecordset->Fields ->GetItem(long(i));
		fieldLength = m_fieldCtl->DefinedSize*10;
		if (fieldLength >100)   //when field is very long then trim it
		{
			fieldLength = 100;
		}
		else if (fieldLength < 50)
		{
			fieldLength = 50;
		}
		else
		{
		   fieldLength = 80;
		}
		/*
		if (fieldLength < m_fieldCtl->Name.length()*12)
		{
			fieldLength = m_fieldCtl->Name.length()*12;
		}
        */
		ListCtrl1.InsertColumn(i,m_fieldCtl->Name,LVCFMT_LEFT,fieldLength);
	}
   
}

void CADODataset::FillContent(CListCtrl &ListCtrl1)
{
	int nItem = 0;
	FieldPtr m_fieldCtl;
	int fieldCount=m_pRecordset->Fields ->Count;
	_variant_t varValue;
	_bstr_t bstrValue;

	while(!m_pRecordset->adoEOF)
	{
		m_fieldCtl = m_pRecordset->Fields ->GetItem(long(0));
		varValue = m_fieldCtl->Value;
		if (varValue.vt == VT_NULL)
		{
			bstrValue = "";
		}
		else if (varValue.vt==VT_BOOL)
		{
		    if(varValue.boolVal==-1)
			{bstrValue="是";}
			else
			{bstrValue="否";}
		}
		else
		{
			bstrValue=varValue;
		}
		nItem=ListCtrl1.InsertItem(0xffff,bstrValue); //fisrt value

		for(int i=1;i<fieldCount;i++)  //next all value
		{
			m_fieldCtl = m_pRecordset->Fields ->GetItem(long(i));
			varValue = m_fieldCtl->Value;
			if (varValue.vt == VT_NULL)
			{
				bstrValue = "";
			}
			else if (varValue.vt==VT_BOOL)
			{
				if(varValue.boolVal==-1)//-1 true
				{bstrValue="是";}
				else
				{bstrValue="否";}
			}
			else
			{
				bstrValue=varValue;
			}

			ListCtrl1.SetItem(nItem,i,1,bstrValue,NULL,0,0,0);
		}

		m_pRecordset->MoveNext ();
	}

}
bool CADODataset::Open(_ConnectionPtr mpdb, LPCTSTR lpstrExec, int nOption)
{	
	Close();
	
	if(strcmp(lpstrExec, _T("")) != 0)
		m_strQuery = lpstrExec;

	ASSERT(!m_strQuery.IsEmpty());
	
	m_strQuery.TrimLeft();
	BOOL bIsSelect =(m_strQuery.Mid(0, strlen("Select ")).CompareNoCase("select ") == 0);

	try
	{
		m_pRecordset->CursorLocation = adUseClient;
		if(bIsSelect || nOption == openQuery)
			m_pRecordset->Open((LPCSTR)m_strQuery, _variant_t((IDispatch*)mpdb, true), 
							adOpenStatic, adLockOptimistic, adCmdUnknown);
		else if(nOption == openTable)
			m_pRecordset->Open((LPCSTR)m_strQuery, _variant_t((IDispatch*)mpdb, true), 
							adOpenDynamic, adLockOptimistic, adCmdTable);
		else if(nOption == openStoredProc)
		{
			m_pCmd->ActiveConnection = mpdb;
			m_pCmd->CommandText = _bstr_t(m_strQuery);
			m_pCmd->CommandType = adCmdStoredProc;
			
			m_pRecordset = m_pCmd->Execute(NULL, NULL, adCmdText);
		}
		else
		{
			TRACE( "Unknown parameter. %d", nOption);
			return false;
		}
	}
	catch(_com_error &e)
	{
        	
		dump_com_error(e);
		return false;
	}

	return m_pRecordset != NULL;
}

bool CADODataset::Open(LPCTSTR lpstrExec, int nOption)
{
	ASSERT(this->m_pConnection != NULL);
	return Open(this->m_pConnection, lpstrExec, nOption);
}

bool CADODataset::GetFieldValue(LPCTSTR lpFieldName, double& dbValue)
{	
	double val = (double)NULL;
	_variant_t vtFld;
	
	vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
	if(vtFld.vt != VT_NULL)
		val = vtFld.dblVal;
	dbValue = val;
	return true;
}


bool CADODataset::GetFieldValue(int nIndex, double& dbValue)
{	
	double val = (double)NULL;
	_variant_t vtFld;
	_variant_t vtIndex;

	vtIndex.vt = VT_I2;
	vtIndex.iVal = nIndex;
	
	vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
	if(vtFld.vt != VT_NULL)
		val = vtFld.dblVal;
	dbValue = val;
	return true;
}


bool CADODataset::GetFieldValue(LPCTSTR lpFieldName, long& lValue)
{
	long val = (long)NULL;
	_variant_t vtFld;
	
	vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
	if(vtFld.vt != VT_NULL)
		val = vtFld.lVal;
	lValue = val;
	return true;
}

bool CADODataset::GetFieldValue(int nIndex, long& lValue)
{
	long val = (long)NULL;
	_variant_t vtFld;
	_variant_t vtIndex;
	
	vtIndex.vt = VT_I2;
	vtIndex.iVal = nIndex;
	vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
	if(vtFld.vt != VT_NULL)
		val = vtFld.lVal;
	lValue = val;
	return true;
}


bool CADODataset::GetFieldValue(LPCTSTR lpFieldName, int& nValue)
{
	int val = NULL;
	_variant_t vtFld;
	
	vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
	switch(vtFld.vt)
	{
	case VT_I2:
		val = vtFld.iVal;
		break;
	case VT_I4:
		val = vtFld.iVal;
		break;
	case VT_BOOL:
		val = vtFld.boolVal;
		break;
	case VT_NULL:
		val=0;
		break;
	case VT_EMPTY:
		val=0;
		break;
	case VT_BSTR:
		try
		{
		   val=atoi((char*)vtFld.bstrVal);
		   break;
		}
        catch( char* str )
		{
		  AfxMessageBox(str);
		  return 0;
		};
	default:
		nValue = 0;
		return false;
	}	
	nValue = val;
	return true;
}

bool CADODataset::GetFieldValue(int nIndex, int& nValue)
{
	int val = (int)NULL;
	_variant_t vtFld;
	_variant_t vtIndex;
	
	vtIndex.vt = VT_I2;
	vtIndex.iVal = nIndex;
	vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
	switch(vtFld.vt)
	{
	case VT_I2:
		val = vtFld.iVal;
		break;
	case VT_I4:
		val = vtFld.iVal;
		break;
	case VT_NULL:
        val = 0;
		break;
	case VT_BOOL:
		val = vtFld.boolVal;
		break;
	case VT_EMPTY:
		val = 0;
		break;
	default:
		return false;
	}	
	nValue = val;
	return true;
}

bool CADODataset::GetFieldValue(LPCTSTR lpFieldName, CString& strValue)
{
	CString str = _T("");
	_variant_t vtFld;
	
	vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
	switch(vtFld.vt) 
	{
	case VT_BSTR:
		str = vtFld.bstrVal;
		break;
	case VT_DATE:
		{
			COleDateTime dt(vtFld);

			str = dt.Format("%Y-%m-%d %H:%M:%S");
		}
		break;
	case VT_EMPTY:
          
	case VT_NULL:
		str="";
		break;
	case VT_I1:
		str.Format("%d",vtFld.intVal);
		break;
	case VT_I2:
		str.Format("%d",vtFld.iVal);
		break;
	case VT_I4:
		str.Format("%d",vtFld.intVal);
		break;
	case VT_BOOL:
		if(vtFld.boolVal==-1)
		{str="TRUE";}
		else
		{str="FALSE";}
		break;
	case VT_DECIMAL:
		str.Format("%f",vtFld.decVal);
		break;
	default:
		strValue.Empty();
		return false;
	}
	strValue = str;
	return true;
}

bool CADODataset::GetFieldValue(int nIndex, CString& strValue)
{
	CString str = _T("");
	_variant_t vtFld;
	_variant_t vtIndex;

	vtIndex.vt = VT_I2;
	vtIndex.iVal = nIndex;
	
	vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
	switch(vtFld.vt) 
	{
	case VT_BSTR:
		str = vtFld.bstrVal;
		break;
	case VT_DATE:
		{
			COleDateTime dt(vtFld);
			str = dt.Format("%Y-%m-%d %H:%M:%S");
		}
		break;
	case VT_EMPTY:
		str="";
		break;
	case VT_NULL:
		str="";
		break;
	case VT_I1:
		str.Format("%d",vtFld.intVal);
		break;
	case VT_I2: