Mini SQL Query

I have finally completed the connection definition text to XML conversion for Mini SQL Query. The raw carrot (^) delimited line of text describing a connection string was the source of many email requests! Years ago the thing used to simply be the actual connection string (because they were all for MSSQL databases), then I expanded it to include a provider type and then when I decided to go public I thought a "named connection" string would be nice, appending another delimited field was the quick path...

That's all gone (well, is obsolete) in the latest source, I still need to update the GUI a bit but there is an automatic upgrade included for your convenience :-)

I am in the middle of some major refactoring, mainly from a structural point of view. The interfaces etc should not change but some of the plug-ins have moved. Go to the latest source for the updates if you wish, there are a few extras in there now compared to the official release…

see… http://minisqlquery.codeplex.com/SourceControl/ListDownloadableCommits.aspx

PK :)

I had some more questions regarding Mini SQL Query recently, this time via http://minisqlquery.codeplex.com/Thread/List.aspx (most have come come via email to date)

I thought I would post the response too just as a general update on activities…

----

Thanks for the detailed feedback and questions. I’ll answer in order of difficulty/priority:

Status

The status bar stuff is there – I think I just forgot about making good use of it - Ahem! I know that the lack of knowing that you are connected has been an issue in the past.

Query Execution

Background execution of the query has been on the cards for a long time just not a high priority (again, this is use style driven, i.e. most of my queries are against development tables etc). The ability to cancel the query it falls in the same category, it's been a rare need to date but I will look at adding it as part of the background runner enhancement.

Regarding events on completion etc, that’s easy to add. I would just create an event for Before/After query execution with reference to the executing editor (in turn giving access to the query and results). That would probably be raised via the service manager or host window etc. I’ll have a look.

Connections

I can see where you are coming from. Personally almost all my connections use SSPI so I don’t have the password save problem. Probably the easiest way to do it would be a “don’t save the password” option for each connection definition.

To be honest the connection stuff has always been an issue. I would class it as “passable” but definitely not a good interface – it’s just really basic and gets me by. I think I did it in my “anti-XML” phase. I am over that not but just have not revisited the problem!

Minimise/Notify Icon

Fairly easy to do but probably won’t make my to-do list. As always a patch is welcome if you are willing (failing that a straight forward snippet of code etc)

Current Activities

I am actually in the middle of refactoring the projects together as some things have changed over time (and the fact that the app is now open source). I will look at these tasks above in the order above but there is no timeframe to it – again, if you have changes you are willing to make let me know. On that, just let me know if you are working on an area and I’ll stay clear of it for a bit! I have had really useful contributions in the past and have put most in but some haven't made it but only because I was too busy at the time etc...

PK :-)

The sub-title should probably be – Adding a non-standard database provider to Mini SQL Query...

If you are familiar with the generic approach of my query tool it would probably come as no surprise that adding support for non-standard providers is easy (when I say non-standard I simply mean one that is not installed by default with the .net framework).

I say easy because I simply run everything off a current provider/connection. There are almost no provider specific tweaks in the code, so if a provider supports the get schema methods and the core creation of commands, plus the execution of a command you can use it with Mini SQL query. I get the list of providers via a call to the System.Data.Common.DbProviderFactories.GetFactoryClasses() method. Here is a snippet that just gets the “names” of the providers:

namespace MiniSqlQuery.Core

{

       /// <summary>

       /// Some basic helper functions.

       /// </summary>

       public static class Utility

       {

              /// <summary>

              /// Returns an array of SQL provider types supported by the current platform.

              /// </summary>

              /// <returns>An array of SQL provider types.</returns>

              public static string[] GetSqlProviderNames()

              {

                     DataTable providers = DbProviderFactories.GetFactoryClasses();

                     List<string> providerNames = new List<string>();

                     foreach (DataRow row in providers.Rows)

                     {

                           providerNames.Add(row["InvariantName"].ToString());

                     }

                     return providerNames.ToArray();

              }

I have been using Pheonix Software’s implementation of a managed SQLite provider. I have been using http://sourceforge.net/project/showfiles.php?group_id=132486...

If you install the provider SQLite will show up with no further effort. You can also add the provider to the DLL search path of Mini SQL Query and update the “MiniSqlQuery.exe.config” file. Here is a template, just add the “system.data” section at the bottom (and ensure the “System.Data.SQLite.dll” is in the application path!):

<configuration>

  <system.data>

    <DbProviderFactories>

      <remove invariant="System.Data.SQLite"/>

      <add name="SQLite Data Provider" invariant="System.Data.SQLite"

           description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

    </DbProviderFactories>

  </system.data>

</configuration>

For example:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <configSections>

       ...

    </configSections>

        ...

    </applicationSettings>

    <system.data>

      <DbProviderFactories>

        <remove invariant="System.Data.SQLite"/>

        <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

      </DbProviderFactories>

    </system.data>

</configuration>

See the SQLite help docs for more details on usage, the GAC, versioning etc.

You can use that process for any third party provider....

“Tommy T” (http://www.tommyt.se/) has created an export plug-in for Mini SQL Query, see http://www.tommyt.se/post/Export-Plugin-for-MiniSqlQuery.aspx. I pretty much just formatted it with Resharper, tweaked a bit and uploaded it to CodePlex  ;-)

I started a “contrib” folder in the source tree and will if anyone else has similar plugins let me know and I will add them to the build (if you wish!)

One of the things I find really handy about Mini SQL Query is that if I need something I can quickly write or extend a plug-in. That’s how the “paste around” extension came about. I wanted to be able to copy two distinct bits of text and paste the two pieces around another piece of selected text...

The sub-title should probably be – Adding a non-standard database provider to Mini SQL Query...

If you are familiar with the generic approach of my query tool it would probably come as no surprise that adding support for non-standard providers is easy (when I say non-standard I simply mean one that is not installed by default with the .net framework).

I say easy because I simply run everything off a current provider/connection. There are almost no provider specific tweaks in the code, so if a provider supports the get schema methods and the core creation of commands, plus the execution of a command you can use it with Mini SQL query. I get the list of providers via a call to the System.Data.Common.DbProviderFactories.GetFactoryClasses() method. Here is a snippet that just gets the “names” of the providers:

namespace MiniSqlQuery.Core

{

       /// <summary>

       /// Some basic helper functions.

       /// </summary>

       public static class Utility

       {

              /// <summary>

              /// Returns an array of SQL provider types supported by the current platform.

              /// </summary>

              /// <returns>An array of SQL provider types.</returns>

              public static string[] GetSqlProviderNames()

              {

                     DataTable providers = DbProviderFactories.GetFactoryClasses();

                     List<string> providerNames = new List<string>();

                     foreach (DataRow row in providers.Rows)

                     {

                           providerNames.Add(row["InvariantName"].ToString());

                     }

                     return providerNames.ToArray();

              }

I have been using Pheonix Software’s implementation of a managed SQLite provider. I have been using http://sourceforge.net/project/showfiles.php?group_id=132486...

If you install the provider SQLite will show up with no further effort. You can also add the provider to the DLL search path of Mini SQL Query and update the “MiniSqlQuery.exe.config” file. Here is a template, just add the “system.data” section at the bottom (and ensure the “System.Data.SQLite.dll” is in the application path!):

<configuration>

  <system.data>

    <DbProviderFactories>

      <remove invariant="System.Data.SQLite"/>

      <add name="SQLite Data Provider" invariant="System.Data.SQLite"

           description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

    </DbProviderFactories>

  </system.data>

</configuration>

For example:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <configSections>

       ...

    </configSections>

        ...

    </applicationSettings>

    <system.data>

      <DbProviderFactories>

        <remove invariant="System.Data.SQLite"/>

        <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />

      </DbProviderFactories>

    </system.data>

</configuration>

See the SQLite help docs for more details on usage, the GAC, versioning etc.

You can use that process for any third party provider....

“Tommy T” (http://www.tommyt.se/) has created an export plug-in for Mini SQL Query, see http://www.tommyt.se/post/Export-Plugin-for-MiniSqlQuery.aspx. I pretty much just formatted it with Resharper, tweaked a bit and uploaded it to CodePlex  ;-)

I started a “contrib” folder in the source tree and will if anyone else has similar plugins let me know and I will add them to the build (if you wish!)

One of the things I find really handy about Mini SQL Query is that if I need something I can quickly write or extend a plug-in. That’s how the “paste around” extension came about. I wanted to be able to copy two distinct bits of text and paste the two pieces around another piece of selected text...

This is a "Preview release" of Mini SQL Query prior to version 1.0. It includes all updates since the "final beta" release. The main thing left to do is actually add this code to the CodePlex repository. I am looking at using SVN Bridge for that. For now get the source code from the ZIP distributions on CodePlex...

Modified app to use Castle Windsor for dependency injection

The core interfaces used by the application are now created via the Windsor container.  If you are not familiar with this library become so! It is extremely useful. The configuration of components has also been externalized in the "Configuration.xml" file – this make enhancements extremely flexible.

I have not as yet abstracted the schema engine which I would like to do so that it is easily swapped out (the current implementation is pretty basic but very generic!)

General refactoring around core interfaces

I have done a fair bit of tweaking and renaming. Some interfaces have had base interfaces abstracted to increase reusability.

Added allot of hopefully useful commenting

That pretty much says it all – the one thing I would like to see is more “example” code but the source is there so I am not worried.

Implemented simple text searching abilities

This is one of the main things I felt the tool was missing. It's just a basic looping find but it suits me. I have not added "replace" functionality yet but you can achieve this by hitting F3 and “pasting” your replace text! I class that as agile/YAGNI ;-)

Added a "search tools" plug-in under the SDK as an example and find implementation

The search provider and “go to line” implementation were created off the plug-in template as an example and included into the core distribution.

Enhanced the connection strings editor to make use of the providers connection string builder

This was added soon after the final beta release and since then tweaked. It makes use of the connection string builder class provided by each database provider which worked out really well just with the property grid. If the provider does not have an implementation a very generic class is used.

More to do?

The only remaining things that I would really like to see are the display of SQL messages – these are different for each provider so it's just a time thing – not hard at all.

The editor itself does not support batching, e.g. the "GO" syntax. It just means a few changes around the execution of the query and collection of results.

A plain text view of the results would be nice. Currently it's standard data grid only – just the "out of the box" data grid rendering of a dataset.

Go get it - http://www.codeplex.com/MiniSqlQuery/Release/ProjectReleases.aspx?ReleaseId=14424

PK :-)

Because it was one of the most requested missing features I put together an enhancement to the (very simple) connection string manager form.

It makes use of the db providers "connection string builder" (if implemented) and gives you a very functional property window to work with:

To edit a line put the cursor on it, to add a new one put the cursor on a blank line...

You can apply the pach by replacing the DLL - see the release on codeplex - http://www.codeplex.com/MiniSqlQuery/Release/ProjectReleases.aspx?ReleaseId=13772

The source is there too if you are playing around with it allready...

PK  :-)

Just getting organised but see:

  http://www.codeplex.com/MiniSqlQuery

The "final beta" release is here:

  http://www.codeplex.com/MiniSqlQuery/Release/ProjectReleases.aspx?ReleaseId=13684

I have some more cleaning up to do but next week should be release v1.0  :-)

--- 

I spent some time cleaning the code up etc and making it more of an open source project (than a personal tools one). Note that the format of the connection file has changed...

The Source

Note that the source code requires .net 3.5 for building etc but the core components of tool itself targets .net v2. This is mainly so that within the tests etc you can take advantage of the latest language features (and libraries such as *MoQ*). Note also that there is nothing stoping you from creating plugins that target .net v3.5 - the user simply needs the runtime installed.

The SDK and Plug-Ins

See my blog for some posts about the tool - http://www.pksoftware.net/devblog/category/Mini-SQL-Query.aspx.

See http://www.pksoftware.net/devblog/page/Creating-a-Mini-SQL-Plugin.aspx for plugin creation details for now.

If you have suggestions etc please make them. I still need to upload the source code properly... see the ZIP on CodePlex for now (requires VS.NET 2008 express or greater, also works with SharpDevelop v3).

OK, I'll call it an SDK but for now it's just the query tool and ZIP file with the API help and a plugin project template for VS.NET 2008. I am also working on a tutorial page for making a plugin.

Mini SQL Query is at version 0.9.48 now (added some plugin base class helper stuff) and getting pretty close to a version 1 release. I have been getting good feedback and have a few things left to improve before I am happy to call this thing "version 1".

Mini SQL Query is one of those tools that I have had in my self coded toolbox for a long time now. Last year I almost released it to the public but a few things happened and the release sadly didn't. My apologies to those that were waiting, I had plenty of emails in the months to follow asking where the editor was!

Well, I finally uploaded the beta - see http://www.pksoftware.net/MiniSqlQuery/

Mini SQL Query hitting an MSSQL database.

Make sure you register so that when fixes or major releases are made you know – there is also the Mini SQL Query product RSS feed). Keep in mind its in no way intended as a replacement for Microsoft's "SQL Server Management Studio". I use it for making quick queries or updates to my databases. I find it particularly good for managing a remotely hosted database. I don't use it for modeling databases etc but I do plan on making a bunch of plugins to help with quickly putting together a NetTiers focused database model.

Mini SQL Query hitting an MS Access database with a table window floating.

The application itself it an exercise in minimalist coding. I made use of open source libraries such as the ICSharpTextEditor giving lots of edit functionality with little effort. I used Weifen Luo’s docking library which has been great (I need to track down the correct links etc will post later). The design employs a service model with commands which keeps code nicely separated and easily extendable (see the API docs on making plugins if you have the geekish urge to, there is also a tutorial on creating plugins now too). The testing is done with a combination of stubs and mocks (I use Rhino Mocks, just love the style...)

The whole application was actually coded with Microsoft Visual C# Express Edition. I wanted to see what a hobby developer IDE could come up with. What did I miss most? Addins like testdriven.net and resharper!

Some notes...

Multiple Connection Types

One of the main features is the fact that you can connect to pretty much any database so long as you know the Provider type and connection string. I have only had MSSQL and Access databases to test against so far but I might try and ressurect my Oracle instance just to check out the schema details.

Probaby the main usability thing to keep in mind is that if you change the provider type and/or the connection string you should hit the 'Refresh Database Connection' button/menu item. The 'Database Inspector' gets reloaded at this point too.

Database Inspector

The inspector window shows basic details that are taken from the DBConnection.GetSchema output. I have noticed that some of the types for the access databases are not actually defined and so come though as a number with a question mark (e.g. 130? with is actually a variable string) .

Settings Persistance

There is none yet! I have not worried about it to date but that will turn up in time.

Let me know what you think, PK  :-)

NOTE - Mini SQL Query has been released now - see http://www.pksoftware.net/devblog/post/Beta-Release-of-Mini-SQL-Query.aspx.

This is not a product release, but a notice of release (!) for my latest pet project, "Mini SQL Query".



What is Mini SQL Query?...

"Mini SQL Query from PK Software is a minimalist SQL query tool for multiple providers (MSSQL, Oracle, OLEDB, MS Access files etc). The goal of the Mini SQL Query tool is to allow a developer or trouble-shooter to quickly diagnose issues or make changes to a database using a tool with a small footprint, that is fast, expandable and easy to use."

• Multiple database type connections (e.g. MSSQL, Oracle, Access etc)
• Syntax Highlighting
• Object Inspector (Browse the tables, columns etc for the connection)
• Easy to utilize Plug-In system that has access to all the applications internals

namespace MiniSqlQuery.Plugin.Example

{

    /// <summary>

    /// This example command inserts the current connection string details into the editor text.

    /// </summary>

    public class DisplayConnectionCommand : CommandBase

    {

        public DisplayConnectionCommand(IServiceContainer services)

            : base(services, "Display Connection Example")

        {

        }

        public override void Execute()

        {

            IEditor editorService = this.ServiceManager.CurrentEditor;

            editorService.Query = string.Format(

                "-- Connection: {0}\r\n\r\n{1}",

                this.ServiceManager.DatabaseConfigurationManager.ConnectionString,

                editorService.Query);

        }

    }

}

Public Class MakeBusinessObjectFromResultsCommand

    Inherits CommandBase



    Sub New(ByVal services As IServiceContainer)

        MyBase.New(services, "Make BO from Results")

    End Sub



    Public Overrides Sub Execute()

        Dim editor As IEditor = Me.ServiceManager.CurrentEditor



        If Not editor.Result Is Nothing AndAlso _

         editor.Result.Tables.Count > 0 Then

            ' create some simple code gen using the results

            editor.Messages = GenerateClass(editor.Result.Tables(0))

        Else

            editor.Messages = "No results to generate code from."

        End If



    End Sub



    ''' <summary>

    ''' Given a DataTable (<paramref name="dt"/>), a basic VB.NET class is generated.

    ''' </summary>

    ''' <param name="dt">A DataTabel to generate a class from.</param>

    ''' <returns></returns>

    ''' <remarks></remarks>

    Private Function GenerateClass(ByVal dt As DataTable) As String

        Dim code As New Text.StringBuilder()

        Dim fieldName As String

        Dim typeName As String



        code.AppendFormat("Public Class {0}{1}", dt.TableName, vbCrLf)

        code.AppendLine()

        code.AppendFormat(" Public Sub New{0}", vbCrLf)

        code.AppendFormat(" End Sub{0}", vbCrLf)

        code.AppendLine()

        For Each column As DataColumn In dt.Columns

            fieldName = "_" + column.ColumnName

            typeName = column.DataType.FullName

            code.AppendFormat(" Private {0} As {1}{2}", fieldName, column.DataType.FullName, vbCrLf)

            code.AppendLine()

            code.AppendFormat(" Public Property {0}() As {1}{2}", column.ColumnName, typeName, vbCrLf)

            code.AppendFormat(" Get{0}", vbCrLf)

            code.AppendFormat(" Return {0}{1}", fieldName, vbCrLf)

            code.AppendFormat(" End Get{0}", vbCrLf)

            code.AppendFormat(" Set(ByVal value As {0}){1}", typeName, vbCrLf)

            code.AppendFormat(" {0} = value{1}", fieldName, vbCrLf)

            code.AppendFormat(" End Set{0}", vbCrLf)

            code.AppendFormat(" End Property{0}", vbCrLf)

            code.AppendLine()

        Next



        code.AppendFormat("End Class")



        Return code.ToString()

    End Function



End Class