Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / desktop / MFC

Windows Application to View SQLite3 Database

3.67/5 (6 votes)
3 Jul 2008CPOL 1   1.3K  
An MDI application based on MFC to open SQLite3 Database and show all tables in different views.

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:

C++
void CSQLiteViewerView::OnInitialUpdate()

and

C++
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:

C++
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;
}

History

  • 4th July, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)