dhlistbind.cpp

ADO查看数据库工具的原码

// DHListBind.cpp: implementation of the CDHListBind class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "..\DHDBView.h"
#include "DHListBind.h"
#include "..\DHDBViewDoc.h"
#include "..\BinaryViewDlg.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif
#include <atlbase.h>
//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CDHListBind::CDHListBind()
{
	m_pColsSortStateArray=NULL;
	m_sizePage = 30;
	m_bBind = false;
	Reset();
}

CDHListBind::~CDHListBind()
{
	if(m_pColsSortStateArray!=NULL)
	{
		delete []m_pColsSortStateArray;
		m_pColsSortStateArray = NULL;
	}
}

BOOL CDHListBind::BindListToTable(CString strCon,CString strCmd, CString strTable, CListCtrl *pListCtrl,bool bSortQuery)
{
	if(!bSortQuery)
	{
		Reset();
	}
	if(pListCtrl==NULL)
		return FALSE;
	m_pListCtrl = pListCtrl;
	strCon.TrimLeft();
	strCon.TrimRight();
	strTable.TrimLeft();
	strTable.TrimRight();
	if(strCon.IsEmpty() || strTable.IsEmpty())
	{
		return FALSE;
	}
	while(pListCtrl->DeleteColumn(0));
	if(IsOpen())
		Close();
	SetConnectString(strCon);
	CString strColName;
	if(strCmd.IsEmpty())
	{
		strCmd.Format("SELECT * FROM %s", strTable);
		m_strTableName=strTable;
	}
	else
	{
		CString str;
		int n, nlen;
		str=strCmd;
		str.MakeUpper();
		//取出FROM子句
		n=str.Find("FROM", 0);
		nlen=str.GetLength();
		str=str.Right(nlen-n);
		//取表名以后语句
		str.TrimLeft(' ');
		n=str.Find(' ', 0);
		nlen=str.GetLength();
		str=str.Right(nlen-n);
		str.TrimLeft(' ');
		//截表名右边的语句
		n=str.Find(' ', 0);
		if(n>0)	str=str.Left(n);
		m_strTableName=str;
		str=strCmd;
		n=str.Find("WHERE", 0);
		nlen=str.GetLength();
		if(n>=0) str.Delete(n, nlen-n+1);
		else
		{
			n=str.Find("ORDER BY", 0);
			if(n>=0) m_strSort=str.Mid(n);
		}
	}
	m_strCmd = strCmd;
	m_strCon = strCon;
	//m_Rst->CursorLocation  = adUseClient;
	//m_Rst->Open(_bstr_t("Users"),_bstr_t(strCon),adOpenStatic,adLockOptimistic,adCmdTable);
	Open(strCmd,adOpenStatic,adLockOptimistic,adCmdText);
	//MessageBox(0,"ok",0,0);
	//m_sizeList = 
	//nPageSize
	m_bBind = true;
	long nItemCount = m_Rst->GetFields()->GetCount();
	if(!bSortQuery)
	{
		m_pColsSortStateArray = new int[nItemCount];
	}
	CDC*  pDC;
	pDC = pListCtrl->GetDC();
	CSize sz;
	
	for(long i =0;i<nItemCount;i++)
	{
		strColName = (char*)(m_Rst->GetFields()->GetItem(i)->GetName());
		sz=pDC->GetTextExtent(strColName);
		sz.cx+=50;
		pListCtrl->InsertColumn(i, strColName, LVCFMT_LEFT, sz.cx);
	}
	pListCtrl->ReleaseDC(pDC);
	m_Rst->PageSize=m_sizePage;
	m_nTotalPage = m_Rst->PageCount;
	m_nCurPage =1;
	if(IsBOF())
		 return TRUE;
	DisplayPage(m_nCurPage);
	return TRUE;
}

void CDHListBind::SetPageSize(int nPageSize)
{
	m_sizePage = nPageSize;
//	m_Rst->PageSize=nPageSize;
}

CString CDHListBind::GetColData(long nCol)
{
	CString strCol;
	switch(m_Rst->GetFields()->GetItem(nCol)->GetType())
	{
	case	adEmpty:
			strCol.Empty();
			break;

	case	adBoolean:
			strCol.Format("%s", m_Rst->GetFields()->GetItem(nCol)->Value.bVal ? "T" : "F" );
			break;

	case    adInteger:
	case	adSmallInt:
			strCol.Format( "%hd", m_Rst->GetFields()->GetItem(nCol)->Value.intVal );
			break;

	case	adBigInt:
			strCol.Format( "%ld", m_Rst->GetFields()->GetItem(nCol)->Value.lVal );
			break;

	case	adSingle:
			strCol.Format( "%f", m_Rst->GetFields()->GetItem(nCol)->Value.fltVal );
			break;
	case    adNumeric:
	case	adDouble:
			strCol.Format( "%f", m_Rst->GetFields()->GetItem(nCol)->Value.dblVal );
			break;

	case	adDate:
	case 	adDBDate:
	case	adDBTime:
	case	adDBTimeStamp:
			strCol.Format( "%s", COleDateTime(m_Rst->GetFields()->GetItem(nCol)->Value.date).Format());
			break;
	
	case adChar:
	case adWChar:
	case adBSTR:
	case adVarChar:
	case adLongVarChar:
	case adLongVarWChar:
	case adVarWChar:
			if(m_Rst->GetFields()->GetItem(nCol)->Value.vt!=VT_NULL)
			{
				strCol = (char*)((_bstr_t)m_Rst->GetFields()->GetItem(nCol)->Value);
			}
			else
			{
				strCol = "";
			}
			strCol.TrimRight();
			break;

	case	adBinary:
	case	adVarBinary:
	case	adLongVarBinary:
			if(m_Rst->GetFields()->GetItem(nCol)->Value.vt!=VT_NULL)
			{
				strCol.Format("<Bin:%d>", m_Rst->GetFields()->GetItem(nCol)->GetActualSize());
			}
			else
			{
				strCol = "<Bin:0>";
			}
			strCol.TrimRight();
			break;
	
	default:
			strCol = "未知类型";	
	}
	return strCol;
}

BOOL CDHListBind::DisplayPage(int nPage)
{
	m_Rst->PutAbsolutePage((enum PositionEnum)nPage);
	if(!IsEOF()) 
	{
		m_pListCtrl->DeleteAllItems();
		long nItemCount = m_Rst->GetFields()->GetCount();
		int nItem=-1;
		for(long j=1;j<m_Rst->PageSize+1;j++)
		{
			nItem = m_pListCtrl->GetItemCount();
			for(long i=0;i<nItemCount;i++)
			{
				if(i==0)
				{
					m_pListCtrl->InsertItem(nItem, GetColData(i));
				}
				else
				{
					m_pListCtrl->SetItemText(nItem, i, GetColData(i));
				}
			}
			MoveNext();
			if(IsEOF()) break;
		}
	}
	return true;
}

void CDHListBind::NextPage()
{
	if(m_nCurPage<m_nTotalPage&&m_bBind)
	{
		m_nCurPage++;
		DisplayPage(m_nCurPage);
	}
}

void CDHListBind::PrevPage()
{
	if(m_nCurPage>1&&m_bBind)
	{
		m_nCurPage--;
		DisplayPage(m_nCurPage);
	}
}

int CDHListBind::GetCurPage()
{
	return m_nCurPage;
}

int CDHListBind::GetTotalPage()
{
	return m_nTotalPage;
}

CString CDHListBind::GetTableName()
{
	return m_strTableName;
}

void CDHListBind::SortReQuery(int nCol)
{
	CString strColName = (char*)(m_Rst->GetFields()->GetItem(long(nCol))->GetName());
	if(strColName.IsEmpty()||m_strCon.IsEmpty()||m_strTableName.IsEmpty()||m_pListCtrl==NULL)
		return;
	int n,nlen;
	CString strCmd;
	strCmd = m_strCmd;
	
	nlen=strCmd.GetLength();
	n=strCmd.Find("ORDER BY", 0);
	if(n>=0) strCmd.Delete(n, nlen-n+1);
	strCmd+= " ORDER BY ";
	strCmd+= strColName;
	if(m_pColsSortStateArray[nCol] == 1)
	{
		strCmd+= " ASC";
		m_pColsSortStateArray[nCol] = 0;
	}
	else
	{
		strCmd+= " DESC";
		m_pColsSortStateArray[nCol] = 1;
	}
	BindListToTable(m_strCon,strCmd, m_strTableName,m_pListCtrl,true);
}

void CDHListBind::Reset()
{
	m_sizeCol = 0;
	m_nTotalPage = 0;
	m_nCurPage = 0;
	m_strCmd = "";
	m_strCon = "";
	m_pListCtrl = NULL;
	m_strTableName = "";
	m_strSort ="";
	m_bBind =false;
	if(m_pColsSortStateArray!=NULL)
	{
		delete m_pColsSortStateArray;
		m_pColsSortStateArray= NULL;
	}
}
#include "..\DBFILE\DBFileExt.h"
BOOL CDHListBind::SaveTOFile()
{
	char szFilter[] ="Data Files (*.dat)|*.dat|All Files (*.*)|*.*||";
	char szExtFile[]="*.dat";
	CFileDialog  dlg(FALSE,NULL,szExtFile,OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT,szFilter);
	if(dlg.DoModal()!=IDOK) return FALSE;
	CString stPathName;
	stPathName=dlg.GetPathName();
    CDBFileExt dbf;
	dbf.SetFilePath(stPathName.GetBuffer(0));
	CDHDBAdo DBAdo;
	DBAdo.SetConnectString(m_strCon);
	if (DBAdo.Open(m_strCmd) ==FALSE) return FALSE;

	//// int count= m_lstDB.GetItemCount();
	int  ret = dbf.Open(CDBFileExt::DB_MODE_CREATE);
	if (ret==-1) return FALSE;
//	char *info;

	CString aaa;
/////	aaa.GetBufferSetLength(
	dbf.AddInfo(m_strCmd.GetBuffer(0),m_strCmd.GetLength());
	
	//// dbf.AddTableList(m_TableList.GetBuffer(0),m_FieldNum);	

	dbf.SetTableSize(DBAdo.GetFieldNum());
	CString fieldname;
	int FieldNum = DBAdo.GetFieldNum();
	for(int i = 0 ; i < FieldNum ; i++) {
		fieldname = DBAdo.GetFieldName(i);
		dbf.SetTableName(i,fieldname.GetBuffer(0),GetFDBType(DBAdo.Field(i).GetType()));
	}
	dbf.AddTable();

//	CString tmp;
//	tmp.Format("Count = %d " , DBAdo.GetCount());
//	GetDlgItem(IDC_INFO)->SetWindowText(tmp);

	// for(i = 0 ; i < count ; i ++) {
	i = 0;
////	vsd.MoveAbsolute(100);
	while(!DBAdo.IsEOF()) {
	for(int j = 0 ; j < FieldNum; j ++ ) { 
			if (DBAdo.Field(j).IsBinary()) {  // 2进制字段
				BYTE *ptr;	
				CLongBinary *pBin;
				pBin = DBAdo.Field(j).AsBinary();
				ptr=(BYTE *)GlobalLock(pBin->m_hData);
				dbf.SetFieldData(j,(char *)ptr,pBin->m_dwDataLength);
				GlobalUnlock(pBin->m_hData);
			}
			else {   // 普通字段
				dbf.SetFieldData(j,DBAdo.Field(j).AsString().GetBuffer(0),DBAdo.Field(j).AsString().GetLength());
			}
		}
		dbf.AddRecord();
		DBAdo.MoveNext();
		
		//tmp.Format("Count = %d  i  = %d  " , count, i++);
	//	GetDlgItem(IDC_INFO)->SetWindowText(tmp);
	//	UpdateWindow();
	//	MSG msg;
	//	if (PeekMessage(&msg,0,0,0,PM_REMOVE)) {
	//		::TranslateMessage(&msg);
	//		::DispatchMessage(&msg);
	//	};
	}
	DBAdo.Close();
//	AfxMessageBox("完成");	
	//dbf.AddIndex();
	dbf.Close();
	CString  strMes;
	strMes.Format("数据成功保存到文件:%s中", stPathName);
	AfxMessageBox(strMes);
	return TRUE;
}

void CDHListBind::StartPage()
{
	if(m_bBind)
	{
		m_nCurPage =1;
		DisplayPage(m_nCurPage);
	}
}

void CDHListBind::EndPage()
{
	if(m_bBind)
	{
		m_nCurPage = m_nTotalPage;
		DisplayPage(m_nCurPage);
	}
}

void CDHListBind::OnColumnclick(int nCol)
{
	if(Field(nCol).IsBinary())
	{
		ShowBinView(nCol);
	}
	else
	{
		SortReQuery(nCol);
	}
}

void CDHListBind::ShowBinView(int nCol)
{
	POSITION pos;
	pos = m_pListCtrl->GetFirstSelectedItemPosition();
	if(pos == NULL)
		 return;
	int nItem = m_pListCtrl->GetNextSelectedItem(pos);
	_RecordsetPtr rst;
	HRESULT hr = NULL;
	try
	{
		hr = rst.CreateInstance(__uuidof(Recordset));
		if (FAILED(hr))
		{
			TRACE0(_T("创建数据库连接对象失败!"));
			return;
 		}
	//	rst->CursorLocation  = adUseClient;
		if(rst->Open(_variant_t(m_strCmd), _variant_t(m_strCon),adOpenStatic,adLockOptimistic,adCmdText))
 		{
 			TRACE0(_T("打开连接失败!"));
			return;
 		}
	}
	catch(_com_error& e)
	{
		TRACE1("%s\n", e.Description());
	}
	rst->PageSize=m_sizePage;
	rst->PutAbsolutePage((enum PositionEnum)m_nCurPage);
	if(!rst->adoEOF) 
	{
		long j=1;
		while(j<nItem+1)
		{
			rst->MoveNext();
			if(rst->adoEOF) break;
			j++;
		}

	}
	CDHAdoField field(rst->GetFields()->GetItem((long)nCol));
	CLongBinary* binData =  field.AsBinary();
	CBinaryViewDlg dlg;
	dlg.SetBinary((LPBYTE)binData->m_hData,binData->m_dwDataLength);
	dlg.DoModal();
}
int CDHListBind::GetFDBType(int type)
{

	switch(type)
	{
	case	adEmpty:
			return DBVT_NULL;

	case	adBoolean:
			return DBVT_BOOL;

	case	adSmallInt:
			return DBVT_SHORT;
	case    adInteger:
	case	adBigInt:
			return DBVT_LONG;

	case	adSingle:
			return DBVT_LONG;

	case    adNumeric:
	case	adDouble:
			return DBVT_DOUBLE;

	case	adDate:
	case 	adDBDate:
	case	adDBTime:
	case	adDBTimeStamp:
			return DBVT_DATE;
	
	case adChar:
	case adWChar:
	case adBSTR:
	case adVarChar:
	case adLongVarChar:
	case adLongVarWChar:
	case adVarWChar:
			return DBVT_STRING;

	case	adBinary:
	case	adVarBinary:
	case	adLongVarBinary:
			return DBVT_BINARY;
	
	default:
			return -1;	
	}
}

CString CDHListBind::GetCmd()
{
	return m_strCmd;
}