DbPlus (ADO.NET) v 2005.09.05 Beta

A User Interface for interactive SQL

Pre-requisite

Microsoft .NET Framework v1.1 or later installed.

Installation

At present, there's only one option for installation.

DbP lus.zip
(39 Kb)

Unpack the contents in a directory of your choice. See to that connections.xml and providers.xml is in the same directory as DbPlus.exe.

Start DbPlus.exe.

This is how DbPlus works

1. Choose the type of Connection you want with the Combo-box [Connection].

2a. Fill in "username" and "password" and click on [Login] if the connection needs this information. (Which Connections you can choose from is determined of the properties set in the file "Connections.xml", which contains some "default configurations" which can be useful for the configuration of further connections )

DbPlus picture

2b. If instead you want to run against a Microsoft Access/Jet-databasr, you choose that alternative in [Connection] and fill in the path to the mdb-file in the field "Database".

DbPlus picture

Instead of filling the path by hand, you can use the File Dialogue with [>>], and choose an mdb-file that way:

DbPlus picture

3. Now you come to the main window:

DbPlus picture

If you got a Connection without error, you'll see in one of the buttons in the toolbar, otherwise it will show . Which Connection you have, will be shown at the bottom of the window, but for security, your username and password will of course not be shown.

4. To execute an SQL statement, you write it into the text field under the tab [SQL Statement] and click on the button .

The text field will give "syntax highlight" for keyword in PL/SQL and T-SQL.

Depending on if your command gives a RecordSet or not, the response will look differently. If it's an ordinary query, the result will be shown under the tab [Result], where you'll see the result of your query (you can change the size of the window and the width of columns simply by dragging the edges).

DbPlus picture

If your query goes wrong, or if the statement isn't a query, you'll be moved to the tab [Messages]:

DbPlus picture

Under the tab [Table Info] you'll find descriptions of tables and views in your "account":

DbPlus picture

With a "double click" on the field left of a specific table, you'll bring up a window with more information about that table.

DbPlus picture

Configuration tabs

Providers

Under the tab [Providers] you can add, delete or change the properties for the "Providers" you can choose.

DbPlus picture

This information can also be altered "manually" by editing the file providers.xml.

Provider name	This will be shown as an identifier in the Combo box under the Connections tab. To the left in this dialogue, you'll see the configured "Provider names".
Assembly	Name of the provider's assembly, normally the name of the dll, though without the suffix dll. If the dll:en is in the "gac", the assembly cache, you'll also need to provide Version, Culture and PublicKeyToken. Hopefully I've already provided the correct information from start for the most common providers.
Adapter	In each provider there's a sub class to the class DbDataAdapter (usually the name simply ends with DataAdapter)
Provider Types	In each provider there's an Enum for the data types used in the database. (usually the name simply ends with Type or Types)
Connection class	In each provider there's a class implementing the interface IDbConnection. (usually the name simply ends with Connection)

Save	Saves changes you've made on the information you've currently have in view. NB! If you have changed "Provider name" there will be created a new Provider configuration. To remove the old one, mark it and click [Delete]
Delete	Deletes all information on the marked Provider configuration!

Connections

Under the tab [Connections] you can add, remove or change the configuration of which "Connections" you can choose.

DbPlus picture

This information can also be altered "manually" by editing the file connections.xml.

Label

This is shown in the Combo box in the Login dialogue. To the left you see the labels for all configurations.

Provider

Which provider to use for this Connection. (see information above on [Providers] )

Connection String

The "url" for a connection via ADO.NET to a specific database.

There's also three "exchange strings", which you can use to make the configuration more flexible:

#username#	This string is at connection exchanged for what you provide at ”username” in the Login dialogue.
#password#	This string is at connection exchanged for what you provide at ”password” in the Login dialogue.
#dbfile#	This string is at connection exchanged for what you provide at ”database” in the Login dialogue.

Save

Saves changes you've made on the information you've currently have in view. NB! If you have changed "Label" there will be created a new Connection configuration. To remove the old one, mark it and click [Delete]

Delete

Deletes all information on the marked Connection configuration!

Buttons in the toolbar

	Execute an SQL command.
	All your SQL commands during a session will be saved in a "history". With this button you can recall a previously made command...
	...and scroll forward through them. Note that they are not executed automatically, but just retrieved.
	Send the command "ROLLBACK" on all updates you've made since the last "COMMIT".
	Send the command "COMMIT" for all updates you've made.
eller	This button shows whether you've got an open connection or not. If you click on this button the Login dialogue will show, for you to reopen or change to another connection.
	Not yet implemented!

Known bugs

"Syntax highlight" under the tab [SQL Statement] is missing some keyword and is extended only to PL/SQL (Oracle's dialect - a superset to SQL-92) and to some degree to T-SQL (Microsoft SQL Server).

MS Access doesn't support all functions necessary to retrieve all of the the information under the tab [Table Info].

Certain commands (e.g. Commit and Rollback) gives "ugly" printouts in [Message] if no Connection is open...

This version assumes that all statements (queries, etc) are formed in a way that they can be sent through ADO.NET, i.e. usually without a semi colon in the end...

Some providers don't support transactions...

Possible future work

Information under [Table Info] will be developed further, to suit even more databases genrically (at least to adapt to SQL-99). Possibly it will be formatted as a "CREATE TABLE...", to be "cut and pasted" to be used in other databases.

There's already the possibility to run "scripts" though limited in this version of DbPlus. I've e.g. tried to create stored procedures on an SQL Server with success . NB, it's not "fail safe" and definitely not tested on that many databases...

Why DbPlus in C#?

As a lecturer in university courses I have used many different tools for the students, e.g. SQL*Plus from Oracle for "interactive SQL" to create tables and make queries.

Either the tools we used was "locked into" a specific DBMS, or it was too complex for the students. The study time is more precious than to learn a specific tool rather than to learn the contents of the course.
The students have to an increasing extent chosen to make their assignments at home (broadband), which forced them to install unnecessarily complex tools.

I thought of the possibility to create some tool that could work in a similar way as e.g. SQL*Plus, but towards several DBMS, and with a simpler installation.

I made some prototypes that I used in programming classes as examples on what could be done in Java and C#. Until now ADO.NET wasn't suitable, as we used Oracle, and it was necessary to install the Oracle SQL-client to make it work with ADO.NET.

This is a first rewrite of the Java version we used, and still contains some constructions that are more "Java" than ".NET", but in principle the two versions are identical. The basic principle is to install a suitable ADO.NET-provider in the assembly catalogue (though sometimes with a special installation program) and then fill in some information about the provider and a connection.

www.abelli.se