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();
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)
{
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