vdbview.cpp

sql server 数据库结构查看程序.rar

		/* 打印表内容 */
		hNewFont.CreateFont(-34,
							0, 
							0, 
							0, 
							FW_NORMAL, 
							0, 
							0, 
							0, 
							DEFAULT_CHARSET, 
							OUT_DEFAULT_PRECIS,
							CLIP_DEFAULT_PRECIS, 
							DEFAULT_QUALITY,
							DEFAULT_PITCH|FF_DONTCARE, 
							"宋体");
		pDC->SelectObject(&hNewFont);
		pDC->SetTextAlign(TA_LEFT);
		
		while (!pRS_Column->adoEOF)
		{
			x = left;
			y -= line;
			if (y < bottom - Height)
				return;

			pRS_Column_Detail = pDoc->m_pConnection->Execute(_bstr_t("sp_pkeys '") + _bstr_t(strTableName) + _bstr_t("'"), NULL, adCmdText);
			bKeys = false;
			while (!pRS_Column_Detail->adoEOF)
			{
				bKeys = (bKeys || (_bstr_t(pRS_Column_Detail->GetCollect("COLUMN_NAME")) == _bstr_t(pRS_Column->GetCollect("ColumnName"))));
				pRS_Column_Detail->MoveNext();
			}
			if (bKeys)
				strPrint = "K";
			else
				strPrint = "";
			pDC->TextOut(x + 7, y, strPrint);
			pDC->MoveTo(x, y + 10);
			pDC->LineTo(x, y - line + 10);
			x += lColumn0;

			/* 第一列 */
			varValue = pRS_Column->GetCollect("ColumnName");
			strPrint = "";
			strPrint += _bstr_t(varValue);

			pDC->TextOut(x + lCellSpace, y, strPrint);
			pDC->MoveTo(x, y + 10);
			pDC->LineTo(x, y - line + 10);
			x += lColumn1;

			/* 第二列 */
			varValue = pRS_Column->GetCollect("TypeName");
			strPrint = "";
			strPrint += _bstr_t(varValue);
			pDC->TextOut(x + lCellSpace, y, strPrint);
			pDC->MoveTo(x, y + 10);
			pDC->LineTo(x, y - line + 10);
			x += lColumn2;

			/* 第三列 */
			varValue = pRS_Column->GetCollect("Length");
			strPrint = "";
			strPrint += _bstr_t(varValue);
			pDC->TextOut(x + lCellSpace, y, strPrint);
			pDC->MoveTo(x, y + 10);
			pDC->LineTo(x, y - line + 10);
			x += lColumn3;

			/* 第四列 */
			varValue = pRS_Column->GetCollect("IsNullAble");
			strPrint = "";
			strPrint += _bstr_t(varValue);
			if (strPrint == "0")
			{
				pDC->TextOut(x + lCellSpace, y, "否");
			}
			pDC->MoveTo(x, y + 10);
			pDC->LineTo(x, y - line + 10);
			x += lColumn4;

			/* 第五列 */
			pRS_Column_Detail = pDoc->m_pConnection->Execute(_bstr_t("select text from syscomments where id = " + _bstr_t(pRS_Column->GetCollect("cdefault"))), NULL, adCmdText);
			if (!pRS_Column_Detail->adoEOF)
			{
				varValue = pRS_Column_Detail->GetCollect("text");
				strPrint = "";
				strPrint += _bstr_t(varValue);
				pDC->TextOut(x + lCellSpace, y, strPrint);
			}
			pDC->MoveTo(x, y + 10);
			pDC->LineTo(x, y - line + 10);
			x += lColumn5;

			/* 第六列 */
			if (_bstr_t(pRS_Column->GetCollect("Status")) == _bstr_t("128"))
			{
				pDC->TextOut(x + lCellSpace, y, "是");
			}
			pDC->MoveTo(x, y + 10);
			pDC->LineTo(x, y - line + 10);
			x += lColumn6;


			/* 第七列 */
			pRS_Column_Detail = 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(pRS_Column->GetCollect("id")) + " and fkey = " + _bstr_t(pRS_Column->GetCollect("colorder")) + ") b, sysobjects c where a.id = b.rkeyid and a.colorder = b.rkey and a.id = c.id"), NULL, adCmdText);
			if (!pRS_Column_Detail->adoEOF)
			{
				strPrint = "外码: ";
				strPrint += _bstr_t(pRS_Column_Detail->GetCollect("tablename")) + " - " + _bstr_t(pRS_Column_Detail->GetCollect("columnname"));
				pDC->TextOut(x + lCellSpace, y, strPrint);
			}
			pDC->MoveTo(x, y + 10);
			pDC->LineTo(x, y - line + 10);
			pDC->MoveTo(Width - right, y + 10);
			pDC->LineTo(Width - right, y - line + 10);

			/* 底线 */
			pDC->MoveTo(left, y - line + 10);
			pDC->LineTo(Width - right, y - line + 10);

			pRS_Column->MoveNext();
		}
		hNewFont.DeleteObject();
		y -= line;
	}
	memDc.SelectObject(obmp);

	CSize sizeTotal1(344, 291);
	CSize sizePage1(sizeTotal.cx, sizeTotal.cy);
	CSize sizeLine1(sizeTotal.cx, sizeTotal.cy);
	SetScrollSizes(MM_LOMETRIC, sizeTotal1, sizePage1, sizeLine1);
}

/////////////////////////////////////////////////////////////////////////////
// CVDBView diagnostics

#ifdef _DEBUG
void CVDBView::AssertValid() const
{
	CFormView::AssertValid();
}

void CVDBView::Dump(CDumpContext& dc) const
{
	CFormView::Dump(dc);
}

CVDBDoc* CVDBView::GetDocument() // non-debug version is inline
{
	ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CVDBDoc)));
	return (CVDBDoc*)m_pDocument;
}
#endif //_DEBUG

/////////////////////////////////////////////////////////////////////////////
// CVDBView message handlers

void CVDBView::OnButtonConnect() 
{
	// TODO: Add your control notification handler code here
	CString strServer;
	CString strUserName;
	CString strPassword;
	CString strCatalog;
	CEdit *pEdit;
	CListBox *pBox;
	CStatic *pStatic;
	pStatic = (CStatic *) GetDlgItem(IDC_STATIC_INFO);

	CButton *pButton;
	pButton = (CButton *) GetDlgItem(IDC_BUTTON_CREATEHTML);

	/* 获取用户输入的服务器内容 */
	pEdit = (CEdit *) GetDlgItem(IDC_EDIT_SERVER);
	pEdit->GetWindowText(strServer);					// 服务器名
	pEdit = (CEdit *) GetDlgItem(IDC_EDIT_USERNAME);
	pEdit->GetWindowText(strUserName);					// 用户名
	pEdit = (CEdit *) GetDlgItem(IDC_EDIT_PASSWORD);
	pEdit->GetWindowText(strPassword);					// 密码
	pEdit = (CEdit *) GetDlgItem(IDC_EDIT_CATALOG);
	pEdit->GetWindowText(strCatalog);					// 数据库名

	pBox = (CListBox *) GetDlgItem(IDC_LIST_TABLENAME);
	pBox->ResetContent();

	/* 连接数据库 */
	CString ConnStr;
	if (strPassword == "")
	{
		ConnStr = "Provider=SQLOLEDB.1;User ID=" + strUserName + ";Initial Catalog=" + strCatalog + ";Data Source=" + strServer;
	}
	else
	{
		ConnStr = "Provider=SQLOLEDB.1;User ID=" + strUserName + ";Password=" + strPassword + ";Initial Catalog=" + strCatalog + ";Data Source=" + strServer;
	}

	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_pRecordset = pDoc->m_pConnection->Execute("select name from sysobjects where type = 'U' and name <> 'dtproperties'", NULL, adCmdText);
		while (!pDoc->m_pRecordset->adoEOF)
		{
			varValue = pDoc->m_pRecordset->GetCollect("name");
			pBox->AddString(_bstr_t(varValue));
			pDoc->m_pRecordset->MoveNext();
		}

		pDoc->m_pRecordset = pDoc->m_pConnection->Execute("select crdate from master.dbo.sysdatabases where name = '" + _bstr_t(strCatalog) + "'", NULL, adCmdText);
		if (!pDoc->m_pRecordset->adoEOF)
		{
			varValue = pDoc->m_pRecordset->GetCollect("crdate");
			pStatic->SetWindowText("创建日期: " + _bstr_t(varValue));
		}
		/* 设置保存按钮可用 */
		pButton->EnableWindow(true);
	}
	catch(_com_error e)
	{
		CString errormessage;
		errormessage.Format("连接数据库失败!\r\n\r\n错误信息:%s",e.ErrorMessage());
		AfxMessageBox(errormessage);
		pButton->EnableWindow(false);
	}
}

void CVDBView::OnButtonCreatehtml() 
{
	// TODO: Add your control notification handler code here
	CString strFileName;
	CString strFileBody;
	CString strServer;
	CString strCatalog;
	CString strTableName;

	BOOL bKeys;

	int nSelCount;
	int nStep;
	CEdit *pEdit;
	pEdit = (CEdit *) GetDlgItem(IDC_EDIT_SERVER);
	pEdit->GetWindowText(strServer);
	pEdit = (CEdit *) GetDlgItem(IDC_EDIT_CATALOG);
	pEdit->GetWindowText(strCatalog);

	CListBox *pBox;
	pBox = (CListBox *) GetDlgItem(IDC_LIST_TABLENAME);
	nSelCount = pBox->GetSelCount();
	CArray<int, int> aryListBoxSel;
	if (nSelCount)
	{
		aryListBoxSel.SetSize(nSelCount);
		pBox->GetSelItems(nSelCount, aryListBoxSel.GetData()); 
	}
	else
	{
		nSelCount = pBox->GetCount();
		aryListBoxSel.SetSize(nSelCount);
		for (nStep = 0; nStep < nSelCount; nStep ++)
			aryListBoxSel[nStep] = nStep;
	}

	CFileDialog m_FileDlg(FALSE, "*.html", strCatalog + ".html", OFN_HIDEREADONLY|OFN_OVERWRITEPROMPT, "网页 (*.htm; *.html)|*.htm; *.html|All Files (*.*)|*.*||", AfxGetMainWnd());
	if (m_FileDlg.DoModal() != IDOK)
	{
		return;
	}
	strFileName = m_FileDlg.GetFileName();

	CFile File;
	strFileBody = "<html>\r\n<head>\r\n<title>\r\n" + strServer + " - " + strCatalog + "</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\">" + strServer + " - " + strCatalog + "</font></center><BR>";

	_variant_t varValue;
	_RecordsetPtr pRS_Column, pRS_Column_Detail;
	pRS_Column.CreateInstance("ADODB.Recordset");
	pRS_Column_Detail.CreateInstance("ADODB.Recordset");
	for (nStep = 0; nStep < nSelCount; nStep ++)
	{
		pBox->GetText(aryListBoxSel[nStep], strTableName);
		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>" + strTableName + "</B></td></tr>";
		pRS_Column = 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.xtype and a.id = c.id and c.xtype='U' and c.name = '" + strTableName + "' order by a.colorder"), NULL, adCmdText);
		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";
		while (!pRS_Column->adoEOF)
		{
			strFileBody += "<tr bgcolor=\"#FFFFe9\">\r\n";
			strFileBody += "<td>\r\n";
			pRS_Column_Detail = pDoc->m_pConnection->Execute(_bstr_t("sp_pkeys '") + _bstr_t(strTableName) + _bstr_t("'"), NULL, adCmdText);
			bKeys = false;
			while (!pRS_Column_Detail->adoEOF)
			{
				bKeys = (bKeys || (_bstr_t(pRS_Column_Detail->GetCollect("COLUMN_NAME")) == _bstr_t(pRS_Column->GetCollect("ColumnName"))));
				pRS_Column_Detail->MoveNext();
			}
			if (bKeys)
			{
				strFileBody += "v";
			}

			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			varValue = pRS_Column->GetCollect("ColumnName");
			strFileBody += _bstr_t(varValue);
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			varValue = pRS_Column->GetCollect("TypeName");
			strFileBody += _bstr_t(varValue);
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			varValue = pRS_Column->GetCollect("Length");
			strFileBody += _bstr_t(varValue);
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			varValue = pRS_Column->GetCollect("IsNullAble");
			if (_bstr_t(varValue) == _bstr_t("0"))
			{
				strFileBody += "否";
			}
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			pRS_Column_Detail = pDoc->m_pConnection->Execute(_bstr_t("select text from syscomments where id = " + _bstr_t(pRS_Column->GetCollect("cdefault"))), NULL, adCmdText);
			if (!pRS_Column_Detail->adoEOF)
			{
				strFileBody += _bstr_t(pRS_Column_Detail->GetCollect("text"));
			}
			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			if (_bstr_t(pRS_Column->GetCollect("Status")) == _bstr_t("128"))
			{
				strFileBody += "是";
			}

			strFileBody += "</td>\r\n";
			strFileBody += "<td>\r\n";
			pRS_Column_Detail = 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(pRS_Column->GetCollect("id")) + " and fkey = " + _bstr_t(pRS_Column->GetCollect("colorder")) + ") b, sysobjects c where a.id = b.rkeyid and a.colorder = b.rkey and a.id = c.id"), NULL, adCmdText);
			if (!pRS_Column_Detail->adoEOF)
			{
				strFileBody += "外码: " + _bstr_t(pRS_Column_Detail->GetCollect("tablename")) + " - " + _bstr_t(pRS_Column_Detail->GetCollect("columnname"));
			}
			strFileBody += "</td>\r\n";
			strFileBody += "</tr>\r\n";
			pRS_Column->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();
}

void CVDBView::OnSelchangeListTablename() 
{
	// TODO: Add your control notification handler code here
	BOOL bKeys;
	CString strTableName, strOther;
	CListCtrl *pList;
	CListBox *pBox;
	_RecordsetPtr pRS_Column;
	pRS_Column.CreateInstance("ADODB.Recordset");
	pBox = (CListBox *) GetDlgItem(IDC_LIST_TABLENAME);
	pBox->GetText(pBox->GetCurSel(), strTableName);

	pList = (CListCtrl *) GetDlgItem(IDC_LIST_COLUMNS);
	pList->DeleteAllItems();
	int i = 0, nColumn, nItem;
	_variant_t varValue;
	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 = '" + strTableName + "' order by a.colorder"), NULL, adCmdText);
	while (!pDoc->m_pRecordset->adoEOF)
	{
		nColumn = 1;
		strOther = "";
		pRS_Column = pDoc->m_pConnection->Execute(_bstr_t("sp_pkeys '") + _bstr_t(strTableName) + _bstr_t("'"), NULL, adCmdText);
		bKeys = false;
		while (!pRS_Column->adoEOF)
		{
			bKeys = (bKeys || (_bstr_t(pRS_Column->GetCollect("COLUMN_NAME")) == _bstr_t(pDoc->m_pRecordset->GetCollect("ColumnName"))));
			pRS_Column->MoveNext();
		}
		if (bKeys)
		{
			strOther = "v";
		}
		nItem = pList->InsertItem(i, strOther);
		varValue = pDoc->m_pRecordset->GetCollect("ColumnName");
		pList->SetItemText(nItem, nColumn++, (char*)_bstr_t(varValue));
		varValue = pDoc->m_pRecordset->GetCollect("TypeName");
		pList->SetItemText(nItem, nColumn++, (char*)_bstr_t(varValue));
		varValue = pDoc->m_pRecordset->GetCollect("Length");
		pList->SetItemText(nItem, nColumn++, (char*)_bstr_t(varValue));
		varValue = pDoc->m_pRecordset->GetCollect("IsNullAble");
		if (_bstr_t(varValue) == _bstr_t("0"))
		{
			pList->SetItemText(nItem, nColumn++, "否");
		}
		else
		{
			nColumn ++;
		}
		pRS_Column = pDoc->m_pConnection->Execute(_bstr_t("select text from syscomments where id = " + _bstr_t(pDoc->m_pRecordset->GetCollect("cdefault"))), NULL, adCmdText);
		if (!pRS_Column->adoEOF)
		{
			pList->SetItemText(nItem, nColumn++, (char *)(_bstr_t(pRS_Column->GetCollect("text"))));
		}
		else
		{
			nColumn ++;
		}
		if (_bstr_t(pDoc->m_pRecordset->GetCollect("Status")) == _bstr_t("128"))
		{
			pList->SetItemText(nItem, nColumn, "是");
		}
		nColumn ++;
		strOther = "";
		pRS_Column = 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_Column->adoEOF)
		{
			strOther = "外码: ";
			strOther += _bstr_t(pRS_Column->GetCollect("tablename")) + " - " + _bstr_t(pRS_Column->GetCollect("columnname"));
		}
		pList->SetItemText(nItem, nColumn++, strOther);
		nColumn ++;
		i++;
		pDoc->m_pRecordset->MoveNext();
	}
}