Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / C++

Database Explorer

Rate me:
Please Sign up or sign in to vote.
5.00/5 (16 votes)
1 Feb 2022CPOL7 min read 18.5K   880   28   24
A basic database explorer using ODBC
In this article, you will find a ready to use database explorer for any kind of database using ODBC driver.

Image 1

Introduction

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:

Image 2

Image 3

Image 4

Image 5

Image 6

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:

Image 7

Double-click on DSN edit:

Image 8

If you hit OK button, then you'll notice a message to the application status bar:

Image 9

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 Serverhttps://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

Oraclehttps://www.oracle.com/database/technologies/instant-client/microsoft-windows-32-downloads.html

SQLitehttp://www.ch-werner.de/sqliteodbc/

MySQLhttps://dev.mysql.com/downloads/connector/odbc/

PostgreSQLhttps://www.devart.com/odbc/postgresql/

Background

The heavyweight class in this client is an extension of CDatabase, named CDatabaseEx (and CDatabaseExt) used in this tip. A short presentation of these classes:

CDatabaseEx has a few extra methods for composing a connection string: Get/Set Data Source Name, Get/Set Server, Get/Set Driver, Get/Set Database, Get/Set User, Get/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 GetReadConectionDataFromRegistry / SetReadConectionDataFromRegistry.

This class could be put in place transactions if you need by call SetUseTransaction / GetUseTransaction,
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 CRecordset using OpenRecordset(V) method(s).

The other one, CDatabaseExt has helper methods to retrieve data from the database in several options:
CStringArray, array of CDBVariant, vector of CString or CDBVariant, or 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.

Image 10

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.

Image 11

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:

Image 12

If we try a simple select:

Image 13

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:

Image 14

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:

Image 15

As you see, populating the list took almost 7 seconds for 10476 rows. Let's switch to loading list in virtual mode:

Image 16

Image 17

As we see, populating list took only 2 milliseconds. Not bad.

Let's try a SQL SELECT with ~500000 lines, in non-virtual mode:

Image 18

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:

Image 19

Now, the 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.

Enjoy it!

History

  • 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
  • 4 Apr 2023: Disconnect all databases on time change
  • 9 Apr 2023: Avoid disconnecting database multiple times
  • 14 Apr 2023: Check database is open before trying to close it
  • 27 Apr 2023: Show panels before focus

License

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


Written By
Romania Romania
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionDid not compile in VS2019 - Solution Pin
Sabre_Blade4-May-22 5:07
Sabre_Blade4-May-22 5:07 
AnswerRe: Did not compile in VS2019 - Solution Pin
_Flaviu8-Aug-22 1:14
_Flaviu8-Aug-22 1:14 
QuestionUnable to connect a datasource Pin
Member 43473014-Feb-22 1:01
Member 43473014-Feb-22 1:01 
AnswerRe: Unable to connect a datasource Pin
_Flaviu5-Feb-22 21:34
_Flaviu5-Feb-22 21:34 
GeneralRe: Unable to connect a datasource Pin
Member 43473016-Feb-22 19:29
Member 43473016-Feb-22 19:29 
GeneralRe: Unable to connect a datasource Pin
_Flaviu6-Feb-22 21:31
_Flaviu6-Feb-22 21:31 
GeneralRe: Unable to connect a datasource Pin
_Flaviu6-Feb-22 21:56
_Flaviu6-Feb-22 21:56 
GeneralRe: Unable to connect a datasource Pin
Member 43473018-Feb-22 1:49
Member 43473018-Feb-22 1:49 
GeneralRe: Unable to connect a datasource Pin
_Flaviu8-Feb-22 2:00
_Flaviu8-Feb-22 2:00 
GeneralRe: Unable to connect a datasource Pin
Member 43473018-Feb-22 4:01
Member 43473018-Feb-22 4:01 
GeneralRe: Unable to connect a datasource Pin
_Flaviu8-Feb-22 5:28
_Flaviu8-Feb-22 5:28 
Let me do some researches. First, I need to replicate your state.
GeneralRe: Unable to connect a datasource Pin
Member 43473018-Feb-22 5:40
Member 43473018-Feb-22 5:40 
GeneralRe: Unable to connect a datasource Pin
_Flaviu14-Feb-22 5:23
_Flaviu14-Feb-22 5:23 
GeneralRe: Unable to connect a datasource Pin
_Flaviu14-Feb-22 6:18
_Flaviu14-Feb-22 6:18 
GeneralRe: Unable to connect a datasource Pin
Member 434730116-Feb-22 11:59
Member 434730116-Feb-22 11:59 
GeneralRe: Unable to connect a datasource Pin
_Flaviu19-Feb-22 22:32
_Flaviu19-Feb-22 22:32 
GeneralMy vote of 5 Pin
Shao Voon Wong16-Aug-21 15:14
mvaShao Voon Wong16-Aug-21 15:14 
GeneralRe: My vote of 5 Pin
_Flaviu25-Sep-21 21:23
_Flaviu25-Sep-21 21:23 
GeneralMy vote of 5 Pin
Ștefan-Mihai MOGA12-Aug-21 22:03
professionalȘtefan-Mihai MOGA12-Aug-21 22:03 
GeneralRe: My vote of 5 Pin
_Flaviu25-Sep-21 21:22
_Flaviu25-Sep-21 21:22 
Questionexcellent! Pin
Southmountain8-Aug-21 11:35
Southmountain8-Aug-21 11:35 
AnswerRe: excellent! Pin
_Flaviu25-Sep-21 21:22
_Flaviu25-Sep-21 21:22 
PraiseMy vote of 5 Pin
Michael Haephrati8-Aug-21 7:51
professionalMichael Haephrati8-Aug-21 7:51 
GeneralRe: My vote of 5 Pin
_Flaviu25-Sep-21 21:21
_Flaviu25-Sep-21 21:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.