Database Visualization

// 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;
}