In this article, you will find a ready to use database explorer for any kind of database using ODBC driver.
We often need a tool to handle and explore a database, so I present here a basic and simple client for a database. Yes, there are several database clients, but they are customized to a certain database type. And they are pretty big and they often need to be installed. This one is portable, and it's working with every database type accepting one condition: to have an ODBC driver. Once you have it, it is easy to create a database source. I'll expose here these steps.
Open ODBC client:
Finish, and done. You can test it if you want. If you successfully connected to datasource, then you'll be able to connect the
DatabaseExplorer to this data source, using menu Edit->Datasource:
Double-click on DSN edit:
If you hit OK button, then you'll notice a message to the application status bar:
You can do the same for user level.
I put here a list of sites from where you can download few of important ODBC drivers:
SQL Server: https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15
The heavyweight class in this client is an extension of
CDatabaseExt) used in this tip. A short presentation of these classes:
CDatabaseEx has a few extra methods for composing a connection string:
Set Data Source Name,
Set Password, etc. These are useful when there is a need to setup a connection string dynamically. These values could be read / write in registry or from / in memory using
This class could be put in place transactions if you need by call
This object could execute SQL statements one by one or in bulk by using one of the
CDatabaseEx::Execute methods. There is available a possibility to open a
The other one,
CDatabaseExt has helper methods to retrieve data from the database in several options:
CStringArray, array of
CDBVariant, vector of
std::string, using or not function pointers to formatting data.
Using the Application
So, the client promises to connect to any kind of database, and doing this at document level, this means that you can connect to different ODBC sources simultaneously. You could have an open document connected to a Microsoft SQL Server, and another document opened with a MySQL ODBC source. Or, several open documents opened with same ODBC sources. It is up to you. As I said before, you can choose the ODBC data source from menu Edit -> Datasource. Here, you can choose the existing ODBC sources as machine level or user level. On a simple double-click on DSN edit control, you'll have listed all ODBC sources. It is enough to choose the datasource name and hit OK and you'll have all databases listed in Database Panel.
The interface is pretty simple: the main view is a list view for listing data. There are also three panels. The first one is the Database Panel. Here, you can find all databases from the ODBC data source. You can touch it by using mouse, View menu item, or using Ctrl+E accelerator. In this panel is listed all databases from the current ODBC data source. Under every database item, you'll find all tables within. This is done by
CDatabaseExplorerDoc::PopulateDatabasePanel method, here I need to find a
select statement to get all databases (and all tables within) for all kind of databases. Feel free to contribute with these SQL statements if you detain Oracle, MySQL, Informix, Postgres, MariaDB, and so on. I do have Microsoft SQL Server only.
When you select a database in this panel means that you have selected that database for working, and this event is listed in Message Panel. If you double-click on a table item, then you'll have it listed on the list view. Any event is listed in the Message Panel. What happens if your table has too many rows ? I'll explain later.
Message Panel: It is a panel that logs several things from this client: timestamp of every SQL statement run, the error message if something bad has happened, or, if not, how much time SQL took. Also, there is a menu option, Edit->Log Populate List which enable logging for loading list view time. This one is optional. You can un-check it if you are not interested in knowing the list view loading time.
Another panel is Query Panel. In this panel, you can type your own SQL. Here, you can do all operations that could be done in a rich edit control, including an un-numbered undo - redo operations. There are few keyboard accelerators:
- Ctrl+Z - Undo
- Ctrl+Y - Redo
- Ctrl+A - Select All
- Ctrl+L - Select Line - this feature is pretty useful because the application would take into account a text selection if there is a text selection in this panel. So, you can have multiple SQL statements and if you want to run just one of them, you need to select what you want from all statements (one line or more lines) and hit F5.
All these panels could be moved, docked or hidden as your wish, but they cannot be closed because you'll need all of them.
Let's take an example of how to use this database client. Open the application, and choose File->New.
Then, in Query Panel, we try to create a new database:
create database exploatare
After we run Edit->Run (or F5), we see the result:
If we try a simple
We got an error: Invalid object name '
t_title'. Why? Because we didn't select a database. As soon as we select the database that contains
t_title table and hit F5, we see the outcome:
It is pretty sure that you have tables (or SQL statements) that has huge numbers of items. For these cases, I designed a virtual loading mode for the list view, feature available on View->Virtual Mode menu. Once you check it, the list view is loading from the cache, not directly from the database. A small notice here, even so, if you have half million items or more and you scroll the list from the first one to the last one, the loading cache time could last a little bit longer, this loading time is logged in the Message Panel if you need it.
Let's take a little example in non-virtual mode:
As you see, populating the list took almost 7 seconds for 10476 rows. Let's switch to loading list in virtual mode:
As we see, populating list took only 2 milliseconds. Not bad.
Let's try a
SQL SELECT with ~500000 lines, in non-virtual mode:
In this case, the list view has been populated in 3:53 minutes. A little bit longer. Let's try the same SQL
SELECT in virtual mode:
listview has been populated in 2 milliseconds. Much better.
This is a first version. The plan for the next version is to add new details for the table fields, to improve the Query Panel, and other features. Feel free to criticize, correct, improve this project, and if have a little spare time consider to contribute on this project, which could be found on github either: https://github.com/flaviu22/DatabaseExplorer (especially to completing loading database statements).
The application could export the list content (query result) into a csv file by File->Save or File->SaveAs, this feature is available in non-virtual mode only.
- 8th August, 2021: First release
- 1 Feb 2022: Added support for Oracle, SQLite, MySQL, PostgreSQL. Add Export to CSV feature, updated source code for CDatabaseEx and CDatabaseExt
- 7 Feb 2022: Updated GetDataSourceType for SQL Server
- 8 Feb 2022: Updated PopulateList from CDocument - use CDBVariant instead of CString, Rename GetDataAsCString to ConvertToCString
- 10 Feb 2022: Solved small bugs
- 14 Feb 2022: Solved SQL Server Authentication mode
- 15 March 2022: Added tabs state saving feature
- 8 Aug 2022: Embedded helper classes inside CDataSourceDlg
- 15 Aug 2022: Updated CDatabaseExt class
- 23 Aug 2022: Formatted datetime type, Word wrap for Query Pane, preserve tabs order
- 1 Nov 2022: Display column index
- 12 Dec 2022: Display notification for database disconnecting
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.