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:
- (IDE-wise) Open/Save/Drag-and-Drop
- (IDE-wise) query pane and result pane
- (MS SQL 2000 & 2005) Object browser for system objects (Scalar function, Stored procedure, System table, Table function, Trigger, User table, View)
- (MS SQL 2000 & 2005) Object browser for security (Users, Roles, Schemas)
- (MS SQL 2000 & 2005) Object browser for constraints, columns, parameters (Columns, Keys, Constraints, Triggers, Indexes, Parameters)
- (MS SQL 2000 & 2005) Dependencies viewer for objects (Scalar function, Stored procedure, System table, Table function, Trigger, User table, View)
- (MS SQL 2000 & 2005) Object scripting for non-encrypted objects (Scalar function, Stored procedure, Table function, Trigger, View)
- (MySQL 5.0) Object browser for system objects (Scalar function, Stored procedure, System table, Trigger, User table, View)
- (MySQL 5.0) Object browser for security (Users, Privilege)
- (MySQL 5.0) Object browser for constraints, columns (Columns, Keys, Triggers)
- (MySQL 5.0) Object scripting for non-encrypted objects (Scalar function, Stored procedure, Trigger, View)
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.
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.
private void fnFindWindows(string strDBType, string strConnString, string strDataProvider)
{
_toolWindows.Clear();
foreach (Type t in Assembly.GetExecutingAssembly().GetTypes())
{
if (t.GetInterface("IToolWindow") != null)
{
if(t.Name.ToUpper().Equals(strDBType.ToUpper()))
{
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 }));
}
}
}
}
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)
{
case SYSOBJ_USRTBL:
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:
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:
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(""))
{
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_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:
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
