dbserverfiletree.cpp

VC 连接 SQL的例子

// DbServerFileTree.cpp : implementation file
//

#include "stdafx.h"
#include "DbFile.h"
#include "DbServerFileTree.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/////////////////////////////////////////////////////////////////////////////
// CDbServerFileTree

CDbServerFileTree::CDbServerFileTree()
{	
	m_img.Create(16, 16, ILC_COLOR8, 4, 2);
	CBitmap bmp;
	bmp.LoadBitmap(IDB_DIRECTORY);
	m_img.Add(&bmp, RGB(0, 0, 0));
	bmp.DeleteObject();
}

CDbServerFileTree::~CDbServerFileTree()
{	// m_img.Destory();
	// m_img.DeleteImageList();
	// m_img.Detach();
}


BEGIN_MESSAGE_MAP(CDbServerFileTree, CTreeCtrl)
	//{{AFX_MSG_MAP(CDbServerFileTree)
	ON_NOTIFY_REFLECT(TVN_ITEMEXPANDED, OnItemexpanded)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CDbServerFileTree message handlers

BOOL CDbServerFileTree::ConnectDB(CString serverip, CString database, CString username, CString userpass)
{
	HRESULT hr;
	try
	{
		hr = pConnection.CreateInstance("ADODB.Connection");
		if (SUCCEEDED(hr))
		{
			CString sql; 
			sql.Format("driver={SQL Server};Server=%s; DATABASE=%s; UID=%s; PWD=%s",serverip,database,username,userpass);
			hr = pConnection->Open((LPCTSTR)sql, "", "", adModeUnknown);
		}
	}
	catch (_com_error e)
	{
		AfxMessageBox(e.Description());
		return false;
	}
	return true;
}

BOOL CDbServerFileTree::BuildTree()
{
	CString createsql, sql;
	HTREEITEM hrootitem;
	this->SetImageList(&m_img, TVSIL_NORMAL);
	try
	{
		createsql.Format("create  table #drivertree(a varchar(1000), b int)");
		pConnection->Execute((LPCTSTR)createsql, &RecordsAffected, adCmdText);
		createsql.Format("insert into #drivertree exec master..xp_fixeddrives");
		pConnection->Execute((LPCTSTR)createsql, &RecordsAffected, adCmdText);
		m_pRecordset.CreateInstance("ADODB.Recordset");
		sql.Format("select * from #drivertree");
		m_pRecordset->Open((LPCTSTR)sql, _variant_t((IDispatch *)pConnection, true), adOpenDynamic, adLockPessimistic, adCmdText);
		this->SetRedraw(FALSE);
		while (!m_pRecordset->adoEOF)//不为空
		{
			CString driver = (char *)(_bstr_t)m_pRecordset->GetCollect("a");
			CString driverspace = (char *)(_bstr_t)m_pRecordset->GetCollect("b");
			driver = driver + ":("+driverspace + ")剩余空间";
			hrootitem = this->InsertItem(driver, 3, 3, NULL);
			this->InsertItem("ll", 1, 1, hrootitem);//随便添一个就可以
			
			m_pRecordset->MoveNext();
		} 
		this->SetRedraw(TRUE);
	}
	catch (_com_error e)
	{
		AfxMessageBox(e.Description());
		return 0;
	}
	return 1;
}

void CDbServerFileTree::DisConnectDb()
{
	pConnection->Close();
	pConnection.Release();
}





void CDbServerFileTree::OnItemexpanded(NMHDR* pNMHDR, LRESULT* pResult) 
{
	NM_TREEVIEW* pNMTreeView =(NM_TREEVIEW*)pNMHDR;
	// TODO: Add your control notification handler code here
	TV_ITEM itm = pNMTreeView->itemNew;
	
	
	if (itm.state&TVIS_EXPANDED)
	{
		HTREEITEM parent = GetParentItem(itm.hItem);
		
		ExpandBranch(itm.hItem);//展开树结点
		if (parent != NULL)
			SetItemImage(itm.hItem, 0, 0);
	}
	else
	{//关闭树结点
		HTREEITEM parent = GetParentItem(itm.hItem);
		if (parent != NULL)
			SetItemImage(itm.hItem, 1, 1);
	}
	
	
	*pResult = 0;
}

void CDbServerFileTree::ExpandBranch(HTREEITEM parent)
{
	CString fullpath;
	fullpath = GetPathFromHere(fullpath, parent);//得到当前路径
	DeleteAllChild(parent);
	InsertUnderItem(parent, fullpath);
}

void CDbServerFileTree::DeleteAllChild(HTREEITEM itm)
{
	HTREEITEM child;
	child = GetChildItem(itm);
	if (GetChildItem(itm))
	{	
		DeleteItem(child);
		
	}
	
}

CString CDbServerFileTree::GetPathFromHere(CString s, HTREEITEM itm)
{
	CString str;
	HTREEITEM parent = GetParentItem(itm);
	HTREEITEM itmChild = GetChildItem(itm);
	if (parent == NULL)
	{
		str = GetItemText(itm);
		str = str.Left(2);
		str += "\\";
		s = str + s;
	}
	else
	{
		if (GetItemText(itm) != CString(""))
			s = GetItemText(itm) + '\\'+s;
		s = GetPathFromHere(s, parent);
	}
	if (!itmChild && s.GetLength()>0)//没有子文件夹了
		s = s.Left(s.GetLength() - 1);
	return s;
}

BOOL CDbServerFileTree::InsertUnderItem(HTREEITEM itm, CString path)
{
	CString createsql, sql;
	HTREEITEM parent;
	_variant_t RecordsAffected;
	_RecordsetPtr m_pRecordset;
	this->SetRedraw(FALSE);
	try
	{
		createsql.Format("create  table #serverfiletree(a varchar(1000), b int,c int)");// a 文件或目录名称,b是文件或目录标志
		pConnection->Execute((LPCTSTR)createsql, &RecordsAffected, adCmdText);
		createsql.Format("insert into #serverfiletree exec master..xp_dirtree @path='%s',@depth=1,@file=1", path);
		pConnection->Execute((LPCTSTR)createsql, &RecordsAffected, adCmdText);
		m_pRecordset.CreateInstance("ADODB.Recordset");
		sql.Format("select * from #serverfiletree");
		m_pRecordset->Open((LPCTSTR)sql, _variant_t((IDispatch *)pConnection, true), adOpenDynamic, adLockPessimistic, adCmdText);
		
		while (!m_pRecordset->adoEOF)//找到相同id值，更新
		{
			CString fileflag = (char *)(_bstr_t)m_pRecordset->GetCollect("c");
			if (fileflag == "0")
			{
				CString filevul = (char *)(_bstr_t)m_pRecordset->GetCollect("a");
				parent = this->InsertItem(filevul, 1, 1, itm);
				// if(TreeHaveChildren(GetPathFromHere("",parent)))
				//{
				this->InsertItem("", 0, 0, parent);
				//}//如果加上这两句可以判断是否有子项，但速度会变慢
				m_pRecordset->MoveNext();
			}
			if (fileflag == "1")
			{
				CString filevul = (char *)(_bstr_t)m_pRecordset->GetCollect("a");
				this->InsertItem(filevul, 2, 2, itm);
				m_pRecordset->MoveNext();
			}
		}
		createsql.Format("drop  table #serverfiletree");// a 文件或目录名称,b是文件或目录标志
		pConnection->Execute((LPCTSTR)createsql, &RecordsAffected, adCmdText);
	}
	catch (_com_error e)
	{
		AfxMessageBox(e.Description());
		return 0;
	}
	this->SetRedraw(TRUE);
	m_pRecordset->Close();
	return 1;
}

BOOL CDbServerFileTree::TreeHaveChildren(CString path)
{
	_variant_t RecordsAffected;
	_RecordsetPtr m_pRecordset;
	CString createsql, sql;
	
	BOOL flag = 0;
	try
	{
		createsql.Format("create  table #treefileflag(a varchar(1000), b int)");// a 文件或目录名称,b是文件或目录标志
		pConnection->Execute((LPCTSTR)createsql, &RecordsAffected, adCmdText);
		createsql.Format("insert into #treefileflag exec master..xp_dirtree @path='%s',@depth=1", path);
		if (pConnection->Execute((LPCTSTR)createsql, &RecordsAffected, adCmdText))
			m_pRecordset.CreateInstance("ADODB.Recordset");
		sql.Format("select * from #treefileflag");
		m_pRecordset->Open((LPCTSTR)sql, _variant_t((IDispatch *)pConnection, true), adOpenDynamic, adLockPessimistic, adCmdText);
		if (!m_pRecordset->adoEOF)//
		{
			flag = 1;
		}
		else
			flag = 0;
		
		
		createsql.Format("drop  table #treefileflag");// a 文件或目录名称,b是文件或目录标志
		pConnection->Execute((LPCTSTR)createsql, &RecordsAffected, adCmdText);
	}
	catch (_com_error e)
	{
		AfxMessageBox(e.Description());
		flag = 0;
	}
	return flag;
}