Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » General » Downloads
 
Add your own
alternative version
Go to top

Database Visualization

, 31 May 2006
This article aims to create a simple tool for visualizing database tables and relations, a database map to refer to.
src.zip
src
msado15.tlh
msado15.tli
QueryBuilder.dsp
QueryBuilder.dsw
QueryBuilder.exe
QueryBuilder.clw
res
QueryBuilder.ico
logo.bmp
msado15.tlh
msado15.tli
Manifest
default1.bin
ListIcons.bmp
wingraphviz.zip
WinGraphViz
WinGraphviz_v1.02.25s.cab
WinGraphviz_v1.02.24.msi
WinGraphviz_v1.02.24.cab
WinGraphviz_v1.02.20.msi
WinGraphviz_v1.02.20.cab
dotguide.pdf
// DBConnectionDlg.cpp : implementation file
//

#include "stdafx.h"
#include "querybuilder.h"
#include "DBConnectionDlg.h"

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

/////////////////////////////////////////////////////////////////////////////
// CDBConnectionDlg property page
extern CGlobals GlobalVars;

IMPLEMENT_DYNCREATE(CDBConnectionDlg, CPropertyPage)

CDBConnectionDlg::CDBConnectionDlg() : CPropertyPage(CDBConnectionDlg::IDD)
{
	//{{AFX_DATA_INIT(CDBConnectionDlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT

	GlobalVars.g_bConnectionEstablished = FALSE;
	GlobalVars.g_bServerListUpdated = FALSE;
	GlobalVars.g_pDatabase = NULL;
	GlobalVars.g_pRecordset = NULL;
}

CDBConnectionDlg::~CDBConnectionDlg()
{
}

void CDBConnectionDlg::DoDataExchange(CDataExchange* pDX)
{
	CPropertyPage::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CDBConnectionDlg)
	//}}AFX_DATA_MAP
	DDX_Control(pDX, IDC_CMB_SERVERS, m_cmbServers);
	DDX_Control(pDX, IDC_COMBODATABASES, m_cmbDatabases);

	DDX_CBString(pDX, IDC_CMB_SERVERS, GlobalVars.g_strSQLServer);
	DDX_Text(pDX, IDC_EDITPASSWORD, GlobalVars.g_strPassword);
	DDX_Text(pDX, IDC_EDITUSERID, GlobalVars.g_strUserId);
	DDX_Text(pDX, IDC_COMBODATABASES,GlobalVars.g_strDatabase);
}


BEGIN_MESSAGE_MAP(CDBConnectionDlg, CPropertyPage)
	//{{AFX_MSG_MAP(CDBConnectionDlg)
	ON_BN_CLICKED(IDC_BTN_REFRESH_SERVER_LIST, OnBtnRefreshServerList)
	ON_BN_CLICKED(IDC_BTN_CONNECT, OnBtnConnect)
	ON_CBN_DROPDOWN(IDC_CMB_SERVERS, OnDropdownCmbServers)
	ON_CBN_SELCHANGE(IDC_CMB_SERVERS, OnSelchangeCmbServers)
	ON_CBN_SELCHANGE(IDC_COMBODATABASES, OnSelchangeComboDatabases)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CDBConnectionDlg message handlers

BOOL CDBConnectionDlg::OnSetActive() 
{
	CPropertySheet* psheet = (CPropertySheet*) GetParent();   
	psheet->SetWizardButtons(0);

	return CPropertyPage::OnSetActive();
}

void CDBConnectionDlg::OnBtnRefreshServerList() 
{
	if(GlobalVars.g_pRecordset!=NULL)
		GlobalVars.g_pRecordset->Close();

	if(GlobalVars.g_pDatabase!=NULL)
		GlobalVars.g_pDatabase->Close();

	GlobalVars.g_bConnectionEstablished = FALSE;

	GlobalVars.g_bServerListUpdated = FALSE;

	OnDropdownCmbServers();
}

void CDBConnectionDlg::OnBtnConnect() 
{
	UpdateData();

	if(GlobalVars.g_strSQLServer.IsEmpty()) return;

	RetrieveDatabaseList(GlobalVars.g_strSQLServer,GlobalVars.g_strUserId,GlobalVars.g_strPassword);

	m_cmbDatabases.ResetContent();

	m_cmbDatabases.LockWindowUpdate();

		int nDatabaseCount = GlobalVars.g_strArrayDatabases.GetSize();

		for(int nIndex = 0; nIndex < nDatabaseCount; nIndex++)
			m_cmbDatabases.AddString(GlobalVars.g_strArrayDatabases.GetAt(nIndex));

	m_cmbDatabases.UnlockWindowUpdate();

	CWnd *pWnd = (CWnd *)GetDlgItem(IDC_COMBODATABASES);
	pWnd->SetFocus();
}

void CDBConnectionDlg::OnDropdownCmbServers()
{
	RetrieveServerList();

	m_cmbServers.ResetContent();

	m_cmbServers.LockWindowUpdate();

		int nServerCount = GlobalVars.g_strArrayServers.GetSize();

		for(int nIndex = 0; nIndex < nServerCount; nIndex++)
			m_cmbServers.AddString(GlobalVars.g_strArrayServers.GetAt(nIndex));

	m_cmbServers.UnlockWindowUpdate();
}	

void CDBConnectionDlg::OnSelchangeCmbServers() 
{
	OnSelchangeComboDatabases();
}

void CDBConnectionDlg::OnSelchangeComboDatabases() 
{
	if(GlobalVars.g_strUserId.IsEmpty()) return;

	RetrieveTableList(GlobalVars.g_strSQLServer,GlobalVars.g_strUserId,GlobalVars.g_strPassword);

	CPropertySheet* psheet = (CPropertySheet*) GetParent();   
	psheet->SetWizardButtons(PSWIZB_NEXT);
}

/*------------------------------ Generic Functions ------------------------------*/

void CDBConnectionDlg::RetrieveServerList()
{
	if(GlobalVars.g_bServerListUpdated) return;

	GlobalVars.g_strArrayServers.RemoveAll();

	// Fill List of SQL Servers in a String Array
	CWaitCursor wait;

	CSQLInfoEnumerator obSQLEnumerate;

	if(obSQLEnumerate.EnumerateSQLServers())
	{
		int nServerCount = obSQLEnumerate.m_szSQLServersArray.GetSize();

		for(int nIndex = 0; nIndex < nServerCount; nIndex++)
			GlobalVars.g_strArrayServers.Add(obSQLEnumerate.m_szSQLServersArray[nIndex]);

		GlobalVars.g_bServerListUpdated = TRUE;
	}
}

void CDBConnectionDlg::RetrieveTableList(CString strServerName,CString strUserId,CString strPassword)
{
	UpdateData();

	CString strDatabase(_T(""));
	int nCurSel = m_cmbDatabases.GetCurSel();

	if(nCurSel<0) return;

	m_cmbDatabases.GetLBText(nCurSel,strDatabase);

	if(strServerName.IsEmpty() || strUserId.IsEmpty() || strDatabase.IsEmpty())
		return;

	GlobalVars.g_strDatabase = strDatabase;

	if(GlobalVars.g_bConnectionEstablished==FALSE)
	{
		BOOL bConnect = ConnectToDatabase(strServerName,strUserId,strPassword,strDatabase);

		if(bConnect == FALSE)
		{
			AfxMessageBox("Error connecting to database");
			return;
		}
	}
}

void CDBConnectionDlg::RetrieveDatabaseList(CString strServerName, CString strUserId, CString strPassword)
{
	if( strServerName.IsEmpty() || strUserId.IsEmpty() ) return;

	GlobalVars.g_strArrayDatabases.RemoveAll();

	//Fill List of Databases in a String Array
	CWaitCursor wait;

	CSQLInfoEnumerator obSQLEnumerate;

	if(obSQLEnumerate.EnumerateDatabase(strServerName,strUserId,strPassword))
	{
		int nDatabaseCount = obSQLEnumerate.m_szSQLServerDatabaseArray.GetSize();
	
		for(int nIndex = 0; nIndex < nDatabaseCount; nIndex++)
			GlobalVars.g_strArrayDatabases.Add(obSQLEnumerate.m_szSQLServerDatabaseArray[nIndex]);
	}
}

BOOL CDBConnectionDlg::ConnectToDatabase(CString strServerName, CString strUserId, CString strPassword,CString strDatabase)
{
	HRESULT hr;

	GlobalVars.g_bConnectionEstablished = FALSE;

	hr = GlobalVars.g_pDatabase.CreateInstance(__uuidof(Connection));

	if(FAILED(hr))
		return FALSE;

	GlobalVars.g_strArrayTables.RemoveAll();

	CString strConnection(_T(""));
	strConnection.Format("Provider=SQLOLEDB;Server=%s;UID=%s;PWD=%s;Database=%s",strServerName,strUserId,strPassword,strDatabase);

	_bstr_t bstConnectionString(strConnection);

	try
	{
		GlobalVars.g_pDatabase->Open(_bstr_t(strConnection), _bstr_t(""), _bstr_t(""), adOpenUnspecified);
	
		GlobalVars.g_pRecordset.CreateInstance(__uuidof(Recordset));

		CString strSqlStmt(_T(""));
		strSqlStmt = "Exec sp_tables @table_type = \"'TABLE'\" ";

		USES_CONVERSION;

		_variant_t varntSqlStmt;
		varntSqlStmt = A2W(strSqlStmt);

		GlobalVars.g_pRecordset->Open(varntSqlStmt,GlobalVars.g_pDatabase.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);

		_variant_t varntHolder;
    
		while(!GlobalVars.g_pRecordset->EndOfFile)
		{
			 varntHolder = GlobalVars.g_pRecordset->GetCollect("TABLE_NAME"); // rs->GetFields->Field->(_variant_t(FieldNumber))->Value;

			 if(varntHolder.vt!=VT_NULL)
				GlobalVars.g_strArrayTables.Add((char*)_bstr_t(varntHolder));

			 GlobalVars.g_pRecordset->MoveNext();
		 }

		GlobalVars.g_bConnectionEstablished = TRUE;

		return TRUE;
	}
	catch(_com_error &e)
	{
		AfxMessageBox(_com_util::ConvertBSTRToString(e.Description()));
		GlobalVars.g_bConnectionEstablished = FALSE;
	}
	catch(...)
	{
		AfxMessageBox("Unknown Error");
		GlobalVars.g_bConnectionEstablished = FALSE;
	}

	return FALSE;
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

VGirish
Founder
India India
No Biography provided

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 31 May 2006
Article Copyright 2006 by VGirish
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid