Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

A SQL Management Console for MSSQL 2000 & 2005, MySQL 5.0,...

Rate me:
Please Sign up or sign in to vote.
4.86/5 (14 votes)
20 Apr 2008Ms-PL4 min read 68.2K   2.5K   68   7
An article on a SQL IDE for different type of RDBMS databases

SQLManagementConsole/sqlmgtconsole_mssql2005.jpg

Introduction

Since MSSQL is not holding the lion's share of the market yet, integration/interaction with other RDBMS like PL/Oracle, MySQL, FireBird, DB2, is inevitable. With the class System.Data.Common in ADO .NET 2.0, different data providers can write the assemblies, so that .NET application can communicate with them easily, they termed this as connector.

Background

When I first got involved in a project that needed to interface with MySQL, FireBird, Oracle, OleDB, ODBC,..., I was totally lost. There're so many of them. It was like old times when different vendors were fighting for market share in OS war, C++ IDE war. Borland C++ was my first programming IDE, and now it's in the museum. These wars were all history now but the database war still continues.

For each data provider, vendors provide the GUI query console for user to interact with the data visually. Though most of them come with no cost, to use them comfortably is still another problem. From my personal point of view (please do not argue with me in the comments, this is only my opinion), SSMS (even Express Ed.) is the best management console of them all (I have been using MySQL Query Browser and an InterBase-like FireBird query browser, though maybe not for long).

Purpose to create this? Haha, like any other developers: I feel there's no other tool that fulfills my needs, so I created it. Kidding. :) The main purpose is to prepare MSSQL users a familiar IDE/management console for different databases. And one day, I hope my application can be on par with the tools like Red-Gate and myLittleAdmin.

Features

The features included in this version are:

  1. (IDE-wise) Open/Save/Drag-and-Drop
  2. (IDE-wise) query pane and result pane
  3. (MS SQL 2000 & 2005) Object browser for system objects (Scalar function, Stored procedure, System table, Table function, Trigger, User table, View)
  4. (MS SQL 2000 & 2005) Object browser for security (Users, Roles, Schemas)
  5. (MS SQL 2000 & 2005) Object browser for constraints, columns, parameters (Columns, Keys, Constraints, Triggers, Indexes, Parameters)
  6. (MS SQL 2000 & 2005) Dependencies viewer for objects (Scalar function, Stored procedure, System table, Table function, Trigger, User table, View)
  7. (MS SQL 2000 & 2005) Object scripting for non-encrypted objects (Scalar function, Stored procedure, Table function, Trigger, View)
  8. (MySQL 5.0) Object browser for system objects (Scalar function, Stored procedure, System table, Trigger, User table, View)
  9. (MySQL 5.0) Object browser for security (Users, Privilege)
  10. (MySQL 5.0) Object browser for constraints, columns (Columns, Keys, Triggers)
  11. (MySQL 5.0) Object scripting for non-encrypted objects (Scalar function, Stored procedure, Trigger, View)

SQLManagementConsole/sqlmgtconsole_mysql5.jpg

SQLManagementConsole/dependency.jpg

SQLManagementConsole/scripting.jpg

Using the Code

By using the information schema view in the database, you can easily browse to any objects in the MS SQL. (Seems Microsoft has additional way to manipulate it, so what I implemented might not be a 100% match with SSMS; Use at your own risk). Another ways is through System Tables. Microsoft uses system tables to manipulate the system stored procedures in their SSMS, you can even open (in SSMS, it's located at Object explorer: Programmability>Stored Procedures>System Stored Procedures) and see how it works.

To integrate several types of RDBMS databases into one IDE, the interface class is used so that each type of database is totally encapsulated from each other, yet they still have common signatures for IDE.

SQLManagementConsole/login.jpg

After gathering user input (or reading from app.config file), useful information like connection string and class name will be constructed and used for interface initialization.

C#
private void fnFindWindows(string strDBType, string strConnString, string strDataProvider)
    {
        // Clear out the collection
        _toolWindows.Clear();

        // Use reflection to look for types
        foreach (Type t in Assembly.GetExecutingAssembly().GetTypes())
        {
            if (t.GetInterface("IToolWindow") != null)
            {
                // load one database type per each session
                if(t.Name.ToUpper().Equals(strDBType.ToUpper()))
                {
                    // Try the empty constructor
                    if (t.GetConstructor(new Type[0]) != null)
                    {
                        _toolWindows.Add((IToolWindow)Activator.CreateInstance(t));
                    }
                    else
                    {
                        _toolWindows.Add((IToolWindow)Activator.CreateInstance(t, new object[]
                              { this.rtbQueryConsole, this.tscbDBList,
                                strConnString, strDataProvider }));
                    }
                }
            }
        }

        // Add toolwindows to each ToolWindowHost
        foreach (Control c in this.Controls)
        {
            ToolWindowHost host = c as ToolWindowHost;
            if (host != null)
            {
                foreach (IToolWindow tw in _toolWindows)
                {
                    host.AddToolWindow(tw);
                    host.ShowToolWindow(tw);
                    host.Dock = DockStyle.Left;
                }
            }
        }
    }

When constructing the MSSQL object broswer; what you need will be something like this:

C#
private void fnChannelSysObj(TreeNode tnBaseNode, bool bRefresh)
    {
        string strXType = "";
        switch (tnBaseNode.Text)
        {
            // Need qualified name; New database in MSSQL 2005 needs to use "INFORMATION_SCHEMA"
            case SYSOBJ_USRTBL:
                // ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2000.en/tsqlref/ts_ia-iz_56lv.htm
                fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
                                    "Select * from INFORMATION_SCHEMA.TABLES ",
                                    new string[] { "TABLE_SCHEMA", "TABLE_NAME" },
                                    IconType.SysObject, false);
                break;
            case SYSOBJ_VIEW:
            ...
            //----------------------------------------------------------------
            case SYSOBJ_FUNC:
                strXType = "'FN'";
                break;
            ...
            //----------------------------------------------------------------
            case SECURITY_USER:
                // ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/tsqlref9/html/
                // 5f0e6a8d-c983-44f6-97e9-aab5bff67d18.htm
                fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
                                    "Select * from dbo.sysusers"
                                    + " where isntuser = 1 or issqluser = 1",
                                    new string[] { "name" }, IconType.SysObject, true);
                break;
            ...
            case SECURITY_SCHE:
                // ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/tsqlref9/html/
                // 69617642-0f54-4b25-b62f-5f39c8909601.htm
                fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
                                    "Select * from INFORMATION_SCHEMA.SCHEMATA"
                                    + " where CATALOG_NAME = '" + tnBaseNode.Parent.Text + "'",
                                    new string[] { "SCHEMA_NAME" }, IconType.SysObject, true);
                break;
            default:
                break;
        }

        if (!strXType.Trim().Equals(""))
        {
            // use qualified name; use "dbo.sysobjects"
            // instead of "sys.sysobjects" for backward compatibility
            // ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/tsqlref9/html/
            // 44fdc387-67b0-4139-8bf5-ed26cf640cd1.htm
            fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
                                "Select name from dbo.sysobjects"
                                + " WHERE xtype in (" + strXType + ") order by name",
                                new string[] { "name" }, IconType.SysObject, false);
        }
    }

While constructing the MySQL object broswer; what you need will be quite similar:

C#
private void fnChannelSysObj(TreeNode tnBaseNode, bool bRefresh)
    {
        switch (tnBaseNode.Text)
        {
            case SYSOBJ_USRTBL:
                fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
                                    "Select * from INFORMATION_SCHEMA.TABLES " +
                                    " where Table_Schema = '" + tnBaseNode.Parent.Text +
                                    "' and Table_Type in ('Base Table')",
                                    new string[] { "TABLE_NAME" },
                                    IconType.SysObject, false);
                break;
            ...
            //----------------------------------------------------------------
            case SYSOBJ_FUNC:
                fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
                                    "Select * from INFORMATION_SCHEMA.ROUTINES " +
                                    " where ROUTINE_Schema = '" + tnBaseNode.Parent.Text +
                                    "' and ROUTINE_Type = 'FUNCTION'",
                                    new string[] { "SPECIFIC_NAME" },
                                    IconType.SysObject, false);
                break;
            ...
            case SYSOBJ_SYSTBL:
                fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
                                    "Select * from INFORMATION_SCHEMA.TABLES " +
                                    " where Table_Schema = '" + tnBaseNode.Parent.Text +
                                    "' and Table_Type in ('SYSTEM VIEW')",
                                    new string[] { "TABLE_NAME" },
                                    IconType.SysObject, false);
                break;
            //case SYSOBJ_TBLFUNC:
            //    strXType = "'TF'";
            //    break;
            case SYSOBJ_TRIG:
                fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
                                    "Select * from INFORMATION_SCHEMA.TRIGGERS " +
                                    " where TRIGGER_Schema = '" + tnBaseNode.Parent.Text + "' ",
                                    new string[] { "TRIGGER_NAME" },
                                    IconType.SysObject, false);
                break;
            //----------------------------------------------------------------
            case SECURITY_USER:
                // assume root user have the access of all tables
                fnAddSysObj_SysCol(tnBaseNode, tnBaseNode.Parent.Text, bRefresh,
                                    "Select GRANTEE from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES " +
                                    "where TABLE_SCHEMA = '" + tnBaseNode.Parent.Text + "' " +
                                    "group by GRANTEE " +
                                    "UNION " +
                                    "SELECT CONCAT('''', usr.User, _
                                    '''@''',  usr.Host, '''') as GRANTEE " +
                                    "FROM mysql.user usr where User = 'root'",
                                    new string[] { "GRANTEE" }, IconType.SysObject, false);
                break;
            default:
                break;
        }
    }

And rest of the job is just normal Windows programming and you get all data providers in one IDE. Great, huh?

Points of Interest

Lots! Integration with Oracle, PostgreSQL, ..., Exception occurs on datatype timestamp, datatype bit is displayed as checkbox,..., Execution plan, decryption on sproc & function, databases comparison, .... That's why this version is version 0.1.

Since I am not an expert in databases, any constructive comments and recommendations are welcome!

You can always get the latest build from my blog too.

History

  • 2008-04-21: Version 0.1 release

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)


Written By
Software Developer
Malaysia Malaysia
My blog: http://start-coding.blogspot.com/

Comments and Discussions

 
GeneralMy vote of 5 Pin
Kanasz Robert26-Sep-12 7:33
professionalKanasz Robert26-Sep-12 7:33 
GeneralAlmost ideal... Pin
stain356525-Feb-11 2:02
stain356525-Feb-11 2:02 
GeneralRe: Almost ideal... Pin
gan.gary25-Feb-11 4:07
gan.gary25-Feb-11 4:07 
Generalimproved text support Pin
keslavi22-Apr-08 7:43
keslavi22-Apr-08 7:43 
very nice work! take a look at a project called SciDe. it uses scite and can provide your text with intellegent highlighting/coloring. I use it in my own sql editor, which is connecting to sql, oracle, and db2.
Big Grin | :-D
GeneralRe: improved text support Pin
gan.gary22-Apr-08 13:56
gan.gary22-Apr-08 13:56 
GeneralImprovement idea(s) Pin
Guillaume Leparmentier20-Apr-08 22:44
Guillaume Leparmentier20-Apr-08 22:44 
GeneralRe: Improvement idea(s) Pin
gan.gary21-Apr-08 0:32
gan.gary21-Apr-08 0:32 

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.