Click here to Skip to main content
Click here to Skip to main content

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

, 20 Apr 2008 Ms-PL
Rate this:
Please Sign up or sign in to vote.
An article on a SQL IDE for different type of RDBMS databases
SQLMgtConsole_MSSQL2005.jpg

Introduction

Since MSSQL is not holding the lion 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 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 museum. These wars were all history now but the database war is stil continue.

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 still another problem. For my personal point of view (please do not argue with me in the comment, 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 long)

Purpose to create this? Haha, like any other developers: I feel there's no other tool fulfill my needs, so I created it. Kidding. Smile | :) 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 include 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)

SQLMgtConsole_MySQL5.jpg

dependency.jpg

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 additonal way to manipulate it, so what I implemented might not be 100% match with SSMS; Use at your own risk). Another ways is through System Tables. Microsoft use 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 type of RDBMS database 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.

login.jpg

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

    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:

    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:

    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 provider in one IDE. Great, huh?

Points of Interest

Lots! Integration with Oracle, PostgreSQL, ..., Exception occcurs 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 on database, any constructive comment and recommendation are welcomed!

You can always get 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)

Share

About the Author

gan.gary
Software Developer
Malaysia Malaysia
My blog: http://start-coding.blogspot.com/

Comments and Discussions

 
Generalimproved text support Pinmemberkeslavi22-Apr-08 8:43 
GeneralRe: improved text support Pinmembergan.gary22-Apr-08 14:56 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411023.1 | Last Updated 20 Apr 2008
Article Copyright 2008 by gan.gary
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid