mainfrm.cpp

SQL Server 数据库结构查看程序(VC源码)

// MainFrm.cpp : implementation of the CMainFrame class
//

#include "stdafx.h"
#include "VDB.h"
#include "VDBDoc.h"
#include "DBTree.h"
#include "TableList.h"
#include "DlgConnect.h"

#include "MainFrm.h"

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

/////////////////////////////////////////////////////////////////////////////
// CMainFrame

IMPLEMENT_DYNCREATE(CMainFrame, CFrameWnd)

BEGIN_MESSAGE_MAP(CMainFrame, CFrameWnd)
//{{AFX_MSG_MAP(CMainFrame)
ON_WM_CREATE()
ON_COMMAND(ID_DB_CONN, OnDbConn)
	ON_COMMAND(ID_FILE_CREATE, OnFileCreate)
	ON_UPDATE_COMMAND_UI(ID_FILE_CREATE, OnUpdateFileCreate)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

static UINT indicators[] =
{
	ID_SEPARATOR,           // status line indicator
		ID_INDICATOR_CAPS,
		ID_INDICATOR_NUM,
		ID_INDICATOR_SCRL,
};

/////////////////////////////////////////////////////////////////////////////
// CMainFrame construction/destruction

CMainFrame::CMainFrame()
{
	// TODO: add member initialization code here
	
}

CMainFrame::~CMainFrame()
{
}

int CMainFrame::OnCreate(LPCREATESTRUCT lpCreateStruct)
{
	if (CFrameWnd::OnCreate(lpCreateStruct) == -1)
		return -1;
	
	if (!m_wndToolBar.CreateEx(this, TBSTYLE_FLAT, WS_CHILD | WS_VISIBLE | CBRS_TOP
		| CBRS_GRIPPER | CBRS_TOOLTIPS | CBRS_FLYBY | CBRS_SIZE_DYNAMIC) ||
		!m_wndToolBar.LoadToolBar(IDR_MAINFRAME))
	{
		TRACE0("Failed to create toolbar\n");
		return -1;      // fail to create
	}
	
	if (!m_wndStatusBar.Create(this) ||
		!m_wndStatusBar.SetIndicators(indicators,
		sizeof(indicators)/sizeof(UINT)))
	{
		TRACE0("Failed to create status bar\n");
		return -1;      // fail to create
	}
	
	// TODO: Delete these three lines if you don't want the toolbar to
	//  be dockable
	m_wndToolBar.EnableDocking(CBRS_ALIGN_ANY);
	EnableDocking(CBRS_ALIGN_ANY);
	DockControlBar(&m_wndToolBar);
	
	return 0;
}

BOOL CMainFrame::OnCreateClient(LPCREATESTRUCT /*lpcs*/,
								CCreateContext* pContext)
{
	BOOL result = m_wndSplitter.CreateStatic(this, 1, 2);

	result |= m_wndSplitter.CreateView(0, 0, RUNTIME_CLASS(CDBTree), CSize(150, 100), pContext);
	
	result |= m_wndSplitter.CreateView(0, 1, RUNTIME_CLASS(CTableList), CSize(100, 100), pContext);
	m_wndSplitter.SetActivePane(0, 1);
	return result;
}

BOOL CMainFrame::PreCreateWindow(CREATESTRUCT& cs)
{
	cs.style &= ~FWS_ADDTOTITLE;
	
	if( !CFrameWnd::PreCreateWindow(cs) )
		return FALSE;
	// TODO: Modify the Window class or styles here by modifying
	//  the CREATESTRUCT cs
	m_strTitle = "SQL Server 数据库结构查看";
	return TRUE;
}

/////////////////////////////////////////////////////////////////////////////
// CMainFrame diagnostics

#ifdef _DEBUG
void CMainFrame::AssertValid() const
{
	CFrameWnd::AssertValid();
}

void CMainFrame::Dump(CDumpContext& dc) const
{
	CFrameWnd::Dump(dc);
}

#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CMainFrame message handlers


void CMainFrame::OnDbConn() 
{
	// TODO: Add your command handler code here
	CDlgConnect dlg;
	
	if (dlg.DoModal() == IDOK)
	{
		SetCursor(AfxGetApp()->LoadStandardCursor(IDC_WAIT));
		/* 连接数据库 */
		CString ConnStr;
		if (dlg.bWA)
		{
			ConnStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=" + dlg.m_server;
		}
		else
		{
			if (dlg.m_password == "")
			{
				ConnStr = "Provider=SQLOLEDB.1;User ID=" + dlg.m_uid + ";Data Source=" + dlg.m_server;
			}
			else
			{
				ConnStr = "Provider=SQLOLEDB.1;User ID=" + dlg.m_uid + ";Password=" + dlg.m_password + ";Data Source=" + dlg.m_server;
			}
		}
		
		CVDBDoc *pDoc = (CVDBDoc *) GetActiveDocument();
		CTreeCtrl& TreeCtrl = ((CTreeView *)m_wndSplitter.GetPane(0, 0))->GetTreeCtrl();
		TreeCtrl.DeleteAllItems();
		
		if (pDoc->m_pConnection->State == 1)
			pDoc->m_pConnection->Close();
		
		HRESULT hr;
		try
		{
			hr = pDoc->m_pConnection->Open(_bstr_t(ConnStr), "", "", adModeUnknown);
			/* 获取数据库中的表名 */
			_variant_t varValue;
			pDoc->m_pConnection->Execute("use master", NULL, adCmdText);
			pDoc->m_pRecordset = pDoc->m_pConnection->Execute("select name from sysdatabases", NULL, adCmdText);
			HTREEITEM hParent = TreeCtrl.InsertItem(dlg.m_server, 0, 0);
			CStringList *pSL = new CStringList;
			pSL->AddHead("数据库");
			pSL->AddTail("100");

			while (!pDoc->m_pRecordset->adoEOF)
			{
				varValue = pDoc->m_pRecordset->GetCollect("name");
				TreeCtrl.InsertItem(_bstr_t(varValue), 1, 1, hParent);
				pSL->AddTail("1," + _bstr_t(varValue));
				pDoc->m_pRecordset->MoveNext();
			}
			TreeCtrl.SetItemData(hParent, (DWORD)pSL);
			TreeCtrl.SelectItem(TreeCtrl.GetRootItem());
		}
		catch(_com_error e)
		{
			CString errormessage;
			errormessage.Format("连接 " + dlg.m_server + " 失败!\r\n\r\n错误信息:%s",e.ErrorMessage());
			AfxMessageBox(errormessage);
		}
		SetCursor(AfxGetApp()->LoadStandardCursor(IDC_ARROW));
	}
}

void CMainFrame::OnFileCreate() 
{
	// TODO: Add your command handler code here
	SetCursor(AfxGetApp()->LoadStandardCursor(IDC_WAIT));

	CTreeCtrl& TreeCtrl = ((CTreeView *)m_wndSplitter.GetPane(0, 0))->GetTreeCtrl();
	CVDBDoc *pDoc = (CVDBDoc *) GetActiveDocument();
	CString str;
	HTREEITEM hItem;
	int iLevel = 0;					// 选中的结点的级别
	hItem = TreeCtrl.GetSelectedItem();
	
	while (TreeCtrl.GetParentItem(hItem) != NULL)
	{
		hItem = TreeCtrl.GetParentItem(hItem);
		iLevel ++;
	}
	
	hItem = TreeCtrl.GetSelectedItem();
	
	if (iLevel == 2)
	{
		CString strFileBody;
		CFile File;
		CString strOther;
		_RecordsetPtr pRS_Column;
		pRS_Column.CreateInstance("ADODB.Recordset");
		_RecordsetPtr pRS_Comment;
		pRS_Comment.CreateInstance("ADODB.Recordset");
		
		int i = 0, nColumn;
		_variant_t varValue, lRecordAffect;
		CString sServer, sDB, sTable;
		sServer = TreeCtrl.GetItemText(TreeCtrl.GetParentItem(TreeCtrl.GetParentItem(hItem)));
		sDB = TreeCtrl.GetItemText(TreeCtrl.GetParentItem(hItem));
		sTable = TreeCtrl.GetItemText(hItem);
		pDoc->m_pConnection->Execute(_bstr_t("use " + sDB), NULL, adCmdText);
		pDoc->m_pRecordset = pDoc->m_pConnection->Execute(_bstr_t("Select a.status as Status, a.cdefault as cdefault, a.id as id, a.colorder as colorder, c.name as TableName, a.name as ColumnName, b.name as TypeName, a.length as Length, a.scale as Scale, a.isnullable as IsNullAble from syscolumns a, systypes b, sysObjects c where a.xtype = b.xusertype and a.id = c.id and c.xtype='U' and c.name = '" + sTable + "' order by a.colorder"), &lRecordAffect, adCmdText);
		
		CFileDialog m_FileDlg(FALSE, "*.html", sTable + ".html", OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT, "网页 (*.htm; *.html)|*.htm; *.html|All Files (*.*)|*.*||", AfxGetMainWnd());
		int structsize = 0;
		DWORD dwVersion, dwWindowsMajorVersion, dwWindowsMinorVersion;
		dwVersion = GetVersion();
		dwWindowsMajorVersion = (DWORD) (LOBYTE(LOWORD(dwVersion)));
		dwWindowsMinorVersion = (DWORD) (HIBYTE(LOWORD(dwVersion)));

		if (dwVersion < 0x80000000)
			structsize = 88;
		else
			structsize = 76;
		m_FileDlg.m_ofn.lStructSize = structsize;
		if (m_FileDlg.DoModal() != IDOK)
		{
			return;
		}
		CString strFileName = m_FileDlg.GetFileName();
		
		strFileBody = "<html>\r\n<head>\r\n<title>\r\n" + sServer + " - " + sDB + "</title>\r\n";
		strFileBody += "<style>\r\nbody\r\n";
		strFileBody += "{background-color: #ffffff; font-family: 宋体, Tahoma, Arial, Helvetica, sans-serif; font-size: 9pt;\r\n";
		strFileBody += "MARGIN-TOP: 0; margin-left: 0; margin-right: 0;\r\n";
		strFileBody += "SCROLLBAR-FACE-COLOR: #0099FF;\r\n";
		strFileBody += "SCROLLBAR-SHADOW-COLOR: #ffffff;\r\n";
		strFileBody += "SCROLLBAR-3DLIGHT-COLOR: #ffffff;SCROLLBAR-ARROW-COLOR: #ffffff;SCROLLBAR-TRACK-COLOR: #ffffff;SCROLLBAR-DARKSHADOW-COLOR: #ffffff\r\n}";
		strFileBody += "table		{font-size: 9pt; margin-top: 0.5em; margin-bottom: 0em;}\r\n";
		strFileBody += "</style>\r\n</head>\r\n<body>\r\n";
		strFileBody += "<center><font color=\"#FF3300\" style=\"font-size: 16pt\">" + sServer + " - " + sDB + "</font></center><BR>";
		
		
		strFileBody += "<table cellspacing=\"1\" cellpadding=\"4\" width=\"770\" align=\"center\" STYLE=\"border-bottom: 1 solid #6699FF;border-top: 1 solid #6699FF;border-left: 1 solid #6699FF;border-right: 1 solid #6699FF;\" bgcolor=\"black\">\r\n";
		strFileBody += "<tr bgcolor=\"#8FB9F8\"><td colspan=8><B>" + sTable + "</B></td></tr>";
		strFileBody += "<tr bgcolor=\"#FFFFe9\">\r\n";
		strFileBody += "<td width=10>\r\n";
		strFileBody += "<B></B>\r\n";
		strFileBody += "</td>\r\n";
		strFileBody += "<td width=90>\r\n";
		strFileBody += "<B>列名</B>\r\n";
		strFileBody += "</td>\r\n";
		strFileBody += "<td width=60>\r\n";
		strFileBody += "<B>类型</B>\r\n";
		strFileBody += "</td>\r\n";
		strFileBody += "<td width=30>\r\n";
		strFileBody += "<B>长度</B>\r\n";
		strFileBody += "</td>\r\n";
		strFileBody += "<td width=20>\r\n";
		strFileBody += "<B>空</B>\r\n";
		strFileBody += "</td>\r\n";
		strFileBody += "<td width=180>\r\n";
		strFileBody += "<B>默认值</B>\r\n";
		strFileBody += "</td>\r\n";
		strFileBody += "<td width=40>\r\n";
		strFileBody += "<B>自增</B>\r\n";
		strFileBody += "</td>\r\n";
		strFileBody += "<td width=340>\r\n";
		strFileBody += "<B>说明</B>\r\n";
		strFileBody += "</td>\r\n";
		strFileBody += "</tr>\r\n";
		
		pRS_Column = pDoc->m_pConnection->Execute(_bstr_t("sp_pkeys '") + _bstr_t(str) + _bstr_t("'"), NULL, adCmdText);
		while (!pDoc->m_pRecordset->adoEOF)
		{
			strFileBody += "<tr bgcolor=\"#FFFFe9\">\r\n";
			strFileBody += "<td>\r\n";
			nColumn = 1;
			strOther = "";
			pRS_Column->Filter = "COLUMN_NAME = '" + _bstr_t(pDoc->m_pRecordset->GetCollect("ColumnName")) + "'";
			if (!pRS_Column->adoEOF)
				strFileBody += "v";
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			varValue = pDoc->m_pRecordset->GetCollect("ColumnName");
			strFileBody += _bstr_t(varValue);
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			varValue = pDoc->m_pRecordset->GetCollect("TypeName");
			strFileBody += _bstr_t(varValue);
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			varValue = pDoc->m_pRecordset->GetCollect("Length");
			strFileBody += _bstr_t(varValue);
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			
			varValue = pDoc->m_pRecordset->GetCollect("IsNullAble");
			if (_bstr_t(varValue) == _bstr_t("0"))
			{
				strFileBody += "否";
			}
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			pRS_Comment = pDoc->m_pConnection->Execute(_bstr_t("select text from syscomments where id = " + _bstr_t(pDoc->m_pRecordset->GetCollect("cdefault"))), NULL, adCmdText);
			if (!pRS_Comment->adoEOF)
			{
				strFileBody += _bstr_t(pRS_Comment->GetCollect("text"));
			}
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			if (_bstr_t(pDoc->m_pRecordset->GetCollect("Status")) == _bstr_t("128"))
			{
				strFileBody += "是";
			}
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			pRS_Comment = pDoc->m_pConnection->Execute(_bstr_t("select c.name as tablename, a.name as columnname from syscolumns a, (select rkeyid, rkey from sysforeignkeys where fkeyid = " + _bstr_t(pDoc->m_pRecordset->GetCollect("id")) + " and fkey = " + _bstr_t(pDoc->m_pRecordset->GetCollect("colorder")) + ") b, sysobjects c where a.id = b.rkeyid and a.colorder = b.rkey and a.id = c.id"), NULL, adCmdText);
			if (!pRS_Comment->adoEOF)
			{
				strFileBody += "外码: " + _bstr_t(pRS_Comment->GetCollect("tablename")) + " - " + _bstr_t(pRS_Comment->GetCollect("columnname"));
			}
			strFileBody += "</td>\r\n";
			strFileBody += "</tr>\r\n";
			pDoc->m_pRecordset->MoveNext();
		}
		strFileBody += "</table>\r\n<BR><BR>";
		strFileBody += "</body>\r\n</html>";
		File.Open(strFileName, CFile::modeCreate | CFile::typeBinary | CFile::modeReadWrite);
		File.Write(strFileBody, strlen(strFileBody));
		File.Close();
		SetCursor(AfxGetApp()->LoadStandardCursor(IDC_ARROW));
		AfxMessageBox("保存到网页完成！");
	}
}

void CMainFrame::OnUpdateFileCreate(CCmdUI* pCmdUI) 
{
	// TODO: Add your command update UI handler code here
	CTreeCtrl& TreeCtrl = ((CTreeView *)m_wndSplitter.GetPane(0, 0))->GetTreeCtrl();
	HTREEITEM hItem;
	int iLevel = 0;					// 选中的结点的级别
	hItem = TreeCtrl.GetSelectedItem();
	
	while (TreeCtrl.GetParentItem(hItem) != NULL)
	{
		hItem = TreeCtrl.GetParentItem(hItem);
		iLevel ++;
	}
	
	if (iLevel == 2)
	{
		pCmdUI->Enable(TRUE);
	}
	else
	{
		pCmdUI->Enable(FALSE);
	}
}