Windows Application to View SQLite3 Database

Download source - 868.41 KB

Introduction

This application opens SQLite3 database and show records in all tables. It uses both SQLite3 library and CppSQLite3 library. This is tested with VC6. It can be compiled with VC7 too.

Background

I'm interested in SQLite3, however I didn't find a sample application to view contents in the database with GUI on Windows. So I wrote this simple application to do it.

Using the Code

The major modifications are:

void CSQLiteViewerView::OnInitialUpdate()

and

BOOL CSQLiteViewerDoc::OnOpenDocument(LPCTSTR lpszPathName)

The first one adds data from table into list control. The second one creates a view for each table, and extra one view for tables' information.

Blocks of code should be set as style "Formatted" like this:

void CSQLiteViewerView::OnInitialUpdate()
{
	CListView::OnInitialUpdate();

	// TODO: You may populate your ListView with items by directly accessing
	//  its list control through a call to GetListCtrl().
	CChildFrame* pFrameWnd = (CChildFrame*)GetParentFrame();
	int nViewNo;

	ASSERT(pFrameWnd != NULL);
	nViewNo = pFrameWnd->m_nViewNo;

	if ( nViewNo < 0 )
	{
		CListCtrl& objListCtrl = GetListCtrl();
		CppSQLite3Table& objTable = GetDocument()->GetSQLiteMasterTable();
		int row, fld;

		for ( fld = 0; fld < objTable.numFields(); fld++ )
		{
			CString csFieldName(objTable.fieldName(fld));

			objListCtrl.InsertColumn(fld, csFieldName);
		}

		for ( row = 0; row < objTable.numRows(); row++ )
		{
			objTable.setRow(row);
			for ( fld = 0; fld < objTable.numFields(); fld++ )
			{
				CString csFieldName(objTable.fieldIsNull(fld) ? 
				_T("NULL") : (LPCTSTR)objTable.fieldValue(fld));

				if ( !fld )
					objListCtrl.InsertItem(row, csFieldName);
				else
					objListCtrl.SetItemText(row, fld, 
							csFieldName);
			}
		}

		for ( fld = 0; fld < objTable.numFields(); fld++ )
			objListCtrl.SetColumnWidth(fld, LVSCW_AUTOSIZE_USEHEADER);

		pFrameWnd->SetTitle(_T("Table Information"));
	}
	else
	{
		CListCtrl& objListCtrl = GetListCtrl();
		CppSQLite3DB& objDB = GetDocument()->GetSQLite3DB();
		CppSQLite3Table& objTable = GetDocument()->GetSQLiteMasterTable();
		CString csTableName;
		CString csSQL;
		CppSQLite3Query objQuery;
		CString csTitle;
		int row, fld;

		objTable.setRow(nViewNo);
		csTableName = objTable.fieldValue(2);

		csSQL.Format(_T("select * from %s;"), (LPCTSTR)csTableName);
		objQuery = objDB.execQuery(T2C(csSQL));

		for ( fld = 0; fld < objQuery.numFields(); fld++ )
		{
			CString csFieldName(objQuery.fieldName(fld));

			objListCtrl.InsertColumn(fld, csFieldName);
		}

		row = 0;
		while ( !objQuery.eof() )
		{
			for ( fld = 0; fld < objQuery.numFields(); fld++ )
			{
				CString csFieldValue(objQuery.fieldIsNull(fld) ? 
				_T("NULL") : (LPCTSTR)objQuery.fieldValue(fld));

				if ( !fld )
					objListCtrl.InsertItem(row, csFieldValue);
				else
					objListCtrl.SetItemText(row, fld, 
								csFieldValue);
			}

			objQuery.nextRow();
			row++;
		}

		for ( fld = 0; fld < objQuery.numFields(); fld++ )
			objListCtrl.SetColumnWidth(fld, LVSCW_AUTOSIZE_USEHEADER);
		
		csTitle.Format(_T("Table - %s"), (LPCTSTR)csTableName);
		pFrameWnd->SetTitle(csTitle);
	}
}

BOOL CSQLiteViewerDoc::OnOpenDocument(LPCTSTR lpszPathName)
{
	//return CDocument::OnOpenDocument(lpszPathName);

	POSITION pos;
	CDocTemplate *pDocTemplate;
	CChildFrame *pWndChild;
	int row;

	m_objDatabase.open(T2C(EncodeToUTF8(lpszPathName)));
	m_objSQLiteMaster = m_objDatabase.getTable
			("select * from sqlite_master where type='table';");

	pos = theApp.GetFirstDocTemplatePosition();
	pDocTemplate = theApp.GetNextDocTemplate(pos);
	if ( pDocTemplate )
	{
		for ( row = 0; row < m_objSQLiteMaster.numRows(); row++ )
		{
			pWndChild = (CChildFrame*)pDocTemplate->
						CreateNewFrame(this, NULL);
			ASSERT(pWndChild != NULL);
			pWndChild->m_nViewNo = row;
			pDocTemplate->InitialUpdateFrame(pWndChild, this);
		}
	}

	return TRUE;
}