adodatebaseview.cpp

实现了在VC++环境下操作excel数据库

// AdoDateBaseView.cpp : implementation of the CAdoDateBaseView class
//

#include "stdafx.h"
#include "AdoDateBase.h"

#include "AdoDateBaseDoc.h"
#include "AdoDateBaseView.h"

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

/////////////////////////////////////////////////////////////////////////////
// CAdoDateBaseView

IMPLEMENT_DYNCREATE(CAdoDateBaseView, CFormView)

BEGIN_MESSAGE_MAP(CAdoDateBaseView, CFormView)
	//{{AFX_MSG_MAP(CAdoDateBaseView)
	ON_BN_CLICKED(IDC_CONNECT, OnConnect)
	ON_BN_CLICKED(IDC_SOURCE, OnSource)
	ON_BN_CLICKED(IDC_EXECUTE, OnExecute)
	ON_BN_CLICKED(IDC_DISCONNECT, OnDisconnect)
	ON_BN_CLICKED(IDC_QUIT, OnQuit)
	ON_LBN_SELCHANGE(IDC_LIST, OnSelchangeList)
	//}}AFX_MSG_MAP
	// Standard printing commands
	ON_COMMAND(ID_FILE_PRINT, CFormView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_DIRECT, CFormView::OnFilePrint)
	ON_COMMAND(ID_FILE_PRINT_PREVIEW, CFormView::OnFilePrintPreview)
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CAdoDateBaseView construction/destruction

CAdoDateBaseView::CAdoDateBaseView()
	: CFormView(CAdoDateBaseView::IDD)
{
	//{{AFX_DATA_INIT(CAdoDateBaseView)
	m_strConnection = _T("");
	m_strSQL = _T("");
	//}}AFX_DATA_INIT
	// TODO: add construction code here
	m_strTableName = _T("");
	isDesc = FALSE;
	m_strError = _T("");

}

CAdoDateBaseView::~CAdoDateBaseView()
{
}

void CAdoDateBaseView::DoDataExchange(CDataExchange* pDX)
{
	CFormView::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAdoDateBaseView)
	DDX_Control(pDX, IDC_LIST, m_ListBox);
	DDX_Control(pDX, IDC_DATAGRID, m_DataGrid);
	DDX_Text(pDX, IDC_CONNECTION_STRING, m_strConnection);
	DDX_Text(pDX, IDC_EXECUTE_STRING, m_strSQL);
	//}}AFX_DATA_MAP
}

BOOL CAdoDateBaseView::PreCreateWindow(CREATESTRUCT& cs)
{
	// TODO: Modify the Window class or styles here by modifying
	//  the CREATESTRUCT cs

	return CFormView::PreCreateWindow(cs);
}

void CAdoDateBaseView::OnInitialUpdate()
{
	CFormView::OnInitialUpdate();
	GetParentFrame()->RecalcLayout();
	ResizeParentToFit();

}

/////////////////////////////////////////////////////////////////////////////
// CAdoDateBaseView printing

BOOL CAdoDateBaseView::OnPreparePrinting(CPrintInfo* pInfo)
{
	// default preparation
	return DoPreparePrinting(pInfo);
}

void CAdoDateBaseView::OnBeginPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add extra initialization before printing
}

void CAdoDateBaseView::OnEndPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
	// TODO: add cleanup after printing
}

void CAdoDateBaseView::OnPrint(CDC* pDC, CPrintInfo* /*pInfo*/)
{
	// TODO: add customized printing code here
}

/////////////////////////////////////////////////////////////////////////////
// CAdoDateBaseView diagnostics

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

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

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

/////////////////////////////////////////////////////////////////////////////
// CAdoDateBaseView message handlers

void CAdoDateBaseView::GenerateError(HRESULT hr,PWSTR pwszDescription)
{
	CString strError;
	m_strError.Format("Run-time error'%d(%x)'",hr,hr);
	m_strError += "\n\r";
	m_strError += pwszDescription;
	AfxMessageBox(m_strError);
}

void CAdoDateBaseView::OnConnect() 
{
	// TODO: Add your control notification handler code here
   /////更新新变量
	UpdateData();

	try
	{
		//创建实例
		m_Connection.CreateInstance(_uuidof(Connection));

		///根据连接字符串开启数据连接
		m_Connection->Open(_bstr_t(m_strConnection.GetBuffer(0)),"","",-1);
		//m_Connection->Open(_bstr_t(m_strConnection),"","",-1);
	}

	///捕获例外_com_error
		catch(_com_error &e)
		{
			GenerateError(e.Error(),e.Description());
		}

		///将变量中的值保存到控件中
		UpdateData(FALSE);

		//显示表名
		getTables();
}

void CAdoDateBaseView::OnSource() 
{
	// TODO: Add your control notification handler code here
	HRESULT hr;
	IDataSourceLocatorPtr m_dlPrompt = NULL;
	_ConnectionPtr m_Conn = NULL;


	///初始化COM组件
	::CoInitialize(NULL);

	////创建IDataSourceLocatorPtr的实例
	hr = m_dlPrompt.CreateInstance(_uuidof(DataLinks));

	////弹出数据连接的对话框
	m_Conn = m_dlPrompt->PromptNew();
	if(m_Conn!= NULL)
	{
		/////将连接字符串复制到m_strConnect中
		m_strConnection.Format("%s",(char*)m_Conn->ConnectionString);

		////将变量中的值保存到控件中
		UpdateData(FALSE);
	}
}

void CAdoDateBaseView::getTables()
{
	_bstr_t tablesName;
	CString kooky;
	////先将ListBox清空
	m_ListBox.ResetContent();
    
	//将控件中的值保存到变量中
	UpdateData(true);
	try
	{

		///创建实例
		m_Recordset.CreateInstance(_uuidof(Recordset));

        /////以只读方式打开结果集，得到表名信息
        m_Recordset = m_Connection->OpenSchema(adSchemaTables,vtMissing,vtMissing);

		/////如果结果集没有结果
		while(!m_Recordset->adoEOF)
		{
			////得到表项的名字
			tablesName = m_Recordset->GetCollect("TABLE_NAME");
			kooky = (char*)tablesName;

			//如果是表项，则加到ListBox中
			if(kooky.Left(4) != "MSys")
				m_ListBox.AddString(kooky);

			//移到下一个表项
			m_Recordset->MoveNext();
		}
	}

		//捕获例外_com_error
		catch(_com_error &e)
		{
			GenerateError(e.Error(),e.Description());
		}
       
		///将变量中的值保存到控件中
		UpdateData(false);

		///最后将结果集置为空
		m_Recordset = NULL;

}

void CAdoDateBaseView::OnExecute() 
{
	// TODO: Add your control notification handler code here
	try
	{
		m_Recordset.CreateInstance(_uuidof(Recordset));
		/////将控件中的值保存到变量中，主要是保存SQL语句
		UpdateData();
		
		//设定光标服务
		m_Connection->CursorLocation = adUseClient;
		
		
		//根据连接字符串开启数据连接，得到结果集
		m_Recordset->Open(m_strSQL.GetBuffer(0),m_Connection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
	}

	////捕获例外_com_error
	 catch(_com_error &e)
	 {
		 GenerateError(e.Error(),e.Description());
	 }

	 ////将结果集中的内容在datagrid中显示出来
	 m_DataGrid.SetRefDataSource((LPUNKNOWN)m_Recordset);

	 //刷新DataGrid
	 m_DataGrid.Refresh();

	 //将变量中的值保存到控件中
	 UpdateData(false);

	 //将结果集置空
	 m_Recordset = NULL;
}

void CAdoDateBaseView::OnDisconnect() 
{
	// TODO: Add your control notification handler code here
	try
	{
		m_Recordset->Close();
	}
	catch(_com_error &e)
	{
		GenerateError(e.Error(),e.Description());
	}
	

	///清空ListBox控件
	m_ListBox.ResetContent();

	//将DataGrid控件置空
	m_DataGrid.SetRefDataSource(NULL);

	//将DataGrid控件设置成默认状态
	m_DataGrid.ClearFields();

	//刷新DataGrid控件
	m_DataGrid.Refresh();

	//将SQL语句清空
	////这里不清空连接语句，使为了断开连接以后如果重新连接不需要重新设定数据源
	m_strSQL = "";

	///将变量值保存到控件中
	UpdateData(false);
}

void CAdoDateBaseView::OnQuit() 
{
	// TODO: Add your control notification handler code here
//	try
//	{
		////关闭记录集
		if(m_Recordset != NULL)
			m_Recordset->Close();

		//关闭连接
		if(m_Connection!= NULL)
			m_Connection->Close();

		//退出
		PostMessage(WM_CLOSE);
//	}
}

void CAdoDateBaseView::OnSelchangeList() 
{
	// TODO: Add your control notification handler code here
	/////得到鼠标单击项在ListBox中的索引值

	int index = m_ListBox.GetCurSel();

	///根据索引值得到表项的名字
	m_ListBox.GetText(index,m_strTableName);

	//构造SQL查询语言
	m_strSQL = "select * from ["+m_strTableName+"]";

	///将变量值保存到控件中
	UpdateData(false);

	//进行查询
	OnExecute();
}

BEGIN_EVENTSINK_MAP(CAdoDateBaseView, CFormView)
    //{{AFX_EVENTSINK_MAP(CAdoDateBaseView)
	ON_EVENT(CAdoDateBaseView, IDC_DATAGRID, 216 /* HeadClick */, OnHeadClickDatagrid, VTS_I2)
	//}}AFX_EVENTSINK_MAP
END_EVENTSINK_MAP()

void CAdoDateBaseView::OnHeadClickDatagrid(short ColIndex) 
{
	// TODO: Add your control notification handler code here
	
	/////得到DataGrid中表示各列信息的类
	CColumns cols = m_DataGrid.GetColumns();

	//此参数用来得到具体的某一列
	VARIANT index;

	//值为index
	index.intVal = ColIndex;

	//类型为整型
	index.vt = VT_I4;

    ///得到单击列
	CColumn col = cols.GetItem(index);
	
	////得到此列的名字
	m_strTableName = col.GetCaption();

	//排序必须是在已经建立连接的情况下
	if(m_Connection != NULL)
	{
		////排序还必须在表名已经存在的情况下
		if(m_strTableName.GetLength()!=0)
		{
			////得到查询语句的长度，后面会用到
			int nOriginalLength = m_strSQL.GetLength();

			//构造新的查询语句
			CString strTmp = "order by ["+m_strTableName+"]";
			if(isDesc)
			{
				strTmp = strTmp + "desc";
				isDesc = FALSE;
			}
			else
			{
				isDesc = true;
			}
			m_strSQL += strTmp;

			///将变量值保存到控件中
			UpdateData(false);
			OnExecute();

			///恢复原查询语句，这样做是为了再单击别的列，可以对别的列进行排序
			m_strSQL.Delete(nOriginalLength,strTmp.GetLength());
		}
	}
}