dbmanagerdlg.cpp

来自「利用OLEDB以数据库的方式打开Excell文件」· C++ 代码 · 共 1,946 行 · 第 1/4 页

// DBManagerDlg.cpp : implementation file
//

#include "stdafx.h"
#include "DBManager.h"
#include "DBManagerDlg.h"
#include "EditDlg.h"
#include "afx.h"
#include "Company.h"
#include "afxwin.h"
#include "QUERY.h"
//#include "CRsGrid.h"

#ifdef _DEBUG

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


/////////////////////////////////////////////////////////////////////////////
// CAboutDlg dialog used for App About
CString cs_table_name;
CString field_info;

class CAboutDlg : public CDialog
{
public:
	CAboutDlg();

// Dialog Data
	//{{AFX_DATA(CAboutDlg)
	enum { IDD = IDD_ABOUTBOX };
	//}}AFX_DATA

	// ClassWizard generated virtual function overrides
	//{{AFX_VIRTUAL(CAboutDlg)
	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV support
	//}}AFX_VIRTUAL
	
// Implementation
protected:
	//{{AFX_MSG(CAboutDlg)
	//}}AFX_MSG
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
	//{{AFX_DATA_INIT(CAboutDlg)
	//}}AFX_DATA_INIT
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAboutDlg)
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
	//{{AFX_MSG_MAP(CAboutDlg)
		// No message handlers
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CDBManagerDlg dialog

CDBManagerDlg::CDBManagerDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CDBManagerDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CDBManagerDlg)
	m_csSQL = _T("");
	m_csListValue = _T("");
	m_phone_num = _T("");
	m_phone_user_name = _T("");
	m_csSQL = "SELECT * FROM ";
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
	m_pTableset = 0;
	m_pColumnset = 0;
	m_pDynamicBulkSet = 0;
	m_iMaxFieldLength = 0;

}

void CDBManagerDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CDBManagerDlg)
	DDX_Control(pDX, IDC_FEIYONG, m_feiyong);
	DDX_Control(pDX, IDC_COMBO_ASSURE_COMPANY, m_assure_company);
	DDX_Control(pDX, IDC_LIST_DB2, m_ListCtrl2);
	DDX_Control(pDX, IDC_LIST_Correspond, m_ListCorrespond);
	DDX_Control(pDX, IDC_LIST_DB, m_ListCtrl);
	DDX_Text(pDX, IDC_EDIT_SQL, m_csSQL);
	DDX_LBString(pDX, IDC_LIST_DB, m_csListValue);
	DDX_Control(pDX, IDC_RSGRIDCTRL_RESULT, m_RsGridResult);
	DDX_Control(pDX, IDC_RSGRIDCTRL_VIEW, m_RsGridView);
	DDX_Text(pDX, IDC_PHONE_NUM, m_phone_num);
	DDX_Control(pDX, IDC_RSGRIDCTRL_VIEW2, m_RsGridView2);
	DDX_Text(pDX, IDC_PHONE_USER_NAME, m_phone_user_name);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CDBManagerDlg, CDialog)
	//{{AFX_MSG_MAP(CDBManagerDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_OPEN_DB, OnOpenDb)
	ON_COMMAND(ID_EDIT_SHOWATA, OnEditShowata)
	ON_LBN_SELCHANGE(IDC_LIST_DB, OnSelchangeListDb)
	ON_BN_CLICKED(IDC_BUT_VIEW1, OnButView1)
	ON_BN_CLICKED(IDC_BUT_VIEW2, OnButView2)
	ON_BN_CLICKED(IDC_BUT_VIEW3, OnButView3)
	ON_BN_CLICKED(IDC_BUT_VIEW4, OnButView4)
	ON_BN_CLICKED(IDC_EXECUTE, OnExecute)
	ON_WM_CANCELMODE()
	ON_BN_CLICKED(IDC_OPEN_CONFIG, OnOpenConfig)
	ON_BN_CLICKED(IDC_BUTTON1, OnButton1)
	ON_NOTIFY(NM_CLICK, IDC_LIST_Correspond, OnClickLISTCorrespond)
	ON_LBN_SELCHANGE(IDC_LIST_Correspond, OnSelchangeLISTCorrespond)
	ON_BN_CLICKED(IDC_TempToWhole, OnTempToWhole)
	ON_BN_CLICKED(IDC_ADDTOLIST, OnAddtolist)
	ON_BN_CLICKED(IDC_BUTTON1, OnDELETEList)
	ON_BN_CLICKED(IDC_BUTTON_HELP, OnButtonHelp)
	ON_LBN_SELCHANGE(IDC_LIST_DB2, OnSelchangeListDb2)
	ON_BN_CLICKED(IDC_BUTTON2, OnButton2)
	ON_BN_CLICKED(IDC_BUTTON_QUERY, OnButtonQuery)
	ON_BN_CLICKED(IDC_UPDATETOTAL, OnUpdatetotal)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CDBManagerDlg message handlers

BOOL CDBManagerDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// Add "About..." menu item to system menu.

	// IDM_ABOUTBOX must be in the system command range.
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// Set the icon for this dialog.  The framework does this automatically
	//  when the application's main window is not a dialog
	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon
	

//	m_RsGridResult.OrderByColumn( 

	// TODO: Add extra initialization here

	m_ListCorrespond.SetExtendedStyle(LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES);
	m_ListCorrespond.InsertColumn(0,"总表",LVCFMT_LEFT,60,-1);
	m_ListCorrespond.InsertColumn(1,"总表字段",LVCFMT_LEFT,70,-1);

	m_ListCorrespond.InsertColumn(2,"对应",LVCFMT_LEFT,35);
	m_ListCorrespond.InsertColumn(3,"临时表",LVCFMT_LEFT,60,-1);

	m_ListCorrespond.InsertColumn(4,"临时表字段名",LVCFMT_LEFT,80);
	
	
	GetDlgItem(IDOK)->EnableWindow(FALSE);
	GetDlgItem(IDC_BUTTON_QUERY)->EnableWindow(FALSE);
	return TRUE;  // return TRUE  unless you set the focus to a control
}

void CDBManagerDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CDBManagerDlg::OnPaint() 
{
	if (IsIconic())
	{
		CPaintDC dc(this); // device context for painting

		SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);

		// Center icon in client rectangle
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// Draw the icon
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

// The system calls this to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CDBManagerDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}
CString CDBManagerDlg::GetDSN()
{
	if (!m_Database.IsOpen())
		return _T("[No Data Source Selected]");

	// pull DSN from database connect string
	CString string = m_Database.GetConnect();
	string = string.Right(string.GetLength() - (string.Find(_T("DSN=")) + 4));
	string = string.Left(string.Find(_T(";")));
	return string;
}
int CDBManagerDlg::GetProfileValue(LPCTSTR lpszSection,LPCTSTR lpszItem)
{
	int nValue = AfxGetApp()->GetProfileInt(lpszSection,lpszItem,-1);
	if (nValue == -1)
	{
		nValue = 0;
		AfxGetApp()->WriteProfileInt(lpszSection,lpszItem,nValue);
	}
	return nValue;
}
void CDBManagerDlg::FetchColumnInfo(CString lpszName,CRsGrid &m_RsGridView)
{
	if (m_pColumnset)
	{
		if (m_pColumnset->IsOpen())
			m_pColumnset->Close();
		delete m_pColumnset;
		m_pColumnset = 0;
	}


	if (m_pDynamicBulkSet)
	{
		if (m_pDynamicBulkSet->IsOpen())
			m_pDynamicBulkSet->Close();
		delete m_pDynamicBulkSet;
		m_pDynamicBulkSet = 0;
	}



	m_pColumnset = new CColumns(&m_Database);
	m_pColumnset->Open(NULL,NULL,lpszName,NULL,CRecordset::snapshot);

	//insert table Info into RsGrid
	int item = 1;
//	m_RsGridView.SetColCount( 3 );
	m_RsGridView.SetColCount( 1 );
	m_RsGridView.SetCellText( 0, 0, "字段名");
//	m_RsGridView.SetCellText( 0, 1, "字段属性");
//	m_RsGridView.SetCellText( 0, 2, "字段长度");

	while( m_RsGridView.GetCellText( 1, 1 ) != "" )
	{
		m_RsGridView.DeleteRow( 1, 1 );
	}

	int iRowCount =  m_RsGridView.GetRowCount();
	if( iRowCount == 1 )
		m_RsGridView.InsertRow( 0, 1 );

	m_pColumnset->MoveFirst();
	while (!m_pColumnset->IsEOF())
	{
		m_RsGridView.SetCellText( item, 0, m_pColumnset->m_strColumnName );
		m_csFieldName += m_pColumnset->m_strColumnName;
		m_csFieldName += ",";
//		m_RsGridView.SetCellText( item, 1, m_pColumnset->m_strTypeName );
//		m_csFieldType += m_pColumnset->m_strTypeName;
//		m_csFieldType += ",";
//		m_RsGridView.SetCellNumber( item, 2, m_pColumnset->m_nLength );
//		CString csLenth;
//		csLenth.Format( "%d", m_pColumnset->m_nLength );
//		m_csFieldLength += csLenth;
//		m_csFieldLength += ",";
		m_RsGridView.InsertRow( item, 1 );
		item++;
		m_pColumnset->MoveNext();
	}

}

BOOL CDBManagerDlg::FetchTableInfo()
{
	m_pTableset = new CTables(&m_Database);

	// Must use char array for ODBC interface
	// (can simply hard code max size)
	char lpszType[64];

	strcpy(lpszType, "'TABLE'");
	strcat(lpszType, ",'VIEW'");
	strcat(lpszType, ",'SYSTEM TABLE'");
	strcat(lpszType, ",'ALIAS','SYNONYM'");

	if (!m_pTableset->Open(NULL,NULL,NULL,lpszType,CRecordset::snapshot))
	{
		delete m_pTableset;
		m_pTableset = NULL;
		m_Database.Close();
		return FALSE;
	}

	//show Table Info
	
	int iTemp = 0;
	int iTemp2 = 0;
	m_pTableset->MoveFirst();

	CString m_wholetable ;
			m_wholetable = biaoming;
			m_wholetable += "$";

	while (!m_pTableset->IsEOF())
	{
		if( m_pTableset->m_strTableType == "TABLE" || m_pTableset->m_strTableType == "SYSTEM TABLE" )
		{	
			CString m_ShowTable =m_pTableset->m_strTableName;
			
			if (m_ShowTable[0]==0x27)
			{
				m_ShowTable=m_ShowTable.Right(m_ShowTable.GetLength()-1);
				m_ShowTable=m_ShowTable.Left(m_ShowTable.GetLength()-1);
				
			}

			if (m_ShowTable==m_wholetable)	
			{	
					m_ListCtrl.InsertString( iTemp, m_ShowTable );
					iTemp++;
			}
			else			
			{
				m_ListCtrl2.InsertString( iTemp2, m_ShowTable);
				iTemp2++;
			}
		}
		
		m_pTableset->MoveNext();

	}

	return TRUE;
}

void GetExcelDriver( CString &csExcelDriver )
{
	char szBuf[2001];
	WORD cbBufMax = 2000;
	WORD cbBufOut;
	char *pszBuf = szBuf;

	// Get the names of the installed drivers ("odbcinst.h" has to be included )
	if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
	{
		csExcelDriver = "";
	}
	
	// Search for the driver...
	do
	{
		if( strstr( pszBuf, "Excel" ) != 0 )
		{
			// Found !
			csExcelDriver = CString( pszBuf );
			break;
		}
		pszBuf = strchr( pszBuf, '\0' ) + 1;
	}
	while( pszBuf[1] != '\0' );
}


BOOL CDBManagerDlg::OnOpenDb()
{
	// close the database
	if (m_Database.IsOpen())
		m_Database.Close();


///////////////////////////////////////////////////
	CFileDialog m_ExcelDlg(TRUE,NULL,NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, NULL,NULL); 

	if ( IDOK == m_ExcelDlg.DoModal()) 
		{ 
			CString m_Excelpath=m_ExcelDlg.GetPathName();
			CString m_filename=m_ExcelDlg.GetFileName();
			CString m_csfpath=m_Excelpath;
		
			CString csExcelDriver;
			CString sDriver;
			CString sDsn;	

			// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)" 
   			GetExcelDriver(csExcelDriver);
			sDsn.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", csExcelDriver, m_csfpath, m_csfpath);
			if (m_Database.OpenEx(sDsn, CDatabase::noOdbcDialog))
			{
				if (FetchTableInfo() )
				{
					GetDlgItem( IDC_OPEN_DB )->EnableWindow(FALSE);
					m_kuming=kuming;
					m_kuming=m_kuming+".xls";
					if (m_filename==m_kuming)
					{
						GetDlgItem( IDOK )->EnableWindow(TRUE);
						GetDlgItem(IDC_BUTTON_QUERY)->EnableWindow(TRUE);
						//在担保公司下拉菜单中显示所有的公司名称
						CString SQl1,m_biaoming_company;
						CRecordset rs(&m_Database);
						m_biaoming_company=biaoming;
						SQl1="select distinct [担保公司] from ["+m_biaoming_company+"$]";
						rs.Open(CRecordset::dynaset,(SQl1));
						while (!rs.IsEOF())
						{
							CString content_value;
							rs.GetFieldValue((int)0,content_value);
							m_assure_company.AddString(content_value);
							rs.MoveNext();
						}
						int assure_company_count=rs.GetRecordCount();
						rs.Close();
						return TRUE;
					}
				}
				else
					return FALSE;
			}
			return FALSE;
	}
}

void CDBManagerDlg::OnEditShowata() 
{

	int iNowSel = m_ListCtrl.GetCurSel();
	CString csNowSel;
	m_ListCtrl.GetText( iNowSel, csNowSel.GetBuffer(0) );

	CDynamicBulkSet rs(&m_Database);
	CDataDialog dlgData;

	// Get the current table selected and validate

	CString strSQL;
	strSQL.Format("SELECT * FROM %s", csNowSel);

	// Open the recordset, create the dialog and hand it the recordset
	rs.Open(CRecordset::snapshot, strSQL,
		CRecordset::readOnly | CRecordset::useMultiRowFetch);