Browsing MS SQL Server Desktop Engine using Information Schema

• Download demo project - 11 Kb
• Download source - 15 Kb

Introduction

The NET Framework SDK QuickStarts tutorials illustrate how to manipulate a SQL Server database using SQL and ADO.NET. In order to use these samples you have to install MSDE (Microsoft SQL Server 2000 Desktop Engine ) The engine comes with "OSQL" console command query tool. In demo you will find GUI query tool for MSDE . There is another article that deals with GUI such as MSDE-GUI. My article shows how to browse property of database or SQL server using command query and information schema catalog. Some of user interface for the tool is borrowed from Visual Interdev in a database connection project. The demo shows tree view with node holding a database and their tables/fields, views and stored procedures. The user interface uses DataGrid for query results while the database manipulation is done with SqlConnection, SqlCommand and SqlDataReader.

Hardware & Software Environments

I installed .NET Framework 1.1 and MSDE on Windows Me. I use free IDE from SharpDevelop and I am very glad they made it. That is why you will not see familiar icon for VS.NET in zip. And bit of advice, do not forget to write your own admin password when you install MSDE with the following command:

Setup.exe /qb+ INSTANCENAME=NetSDK
DISABLENETWORKPROTOCOLS=1 SAPWD=<YOUR SA PASSWORD> 

If you do not write your password you will not be able to access your server and you will have to reinstall MSDE.

Design

The design goal is to minimize calls to the database, plus to implement visitor pattern. The database holds list of tables, stored procedures, views, permissions etc. These properties of the database are classes in the class diagram below except permission property. The database class (CMDatabase) opens and holds a connection as well as arrays of tables, views and stored procedures. All database calls are in this class except that tables populate fields into own array. In this way browsing property does not required further access to database. Visitor pattern allows related query methods put in single class as shown below. This diagram is for illustration purpose and does not include all classes, their methods and members.

Classes and Relationship

Visitor Pattern

// MainForm
CView v = (CView) node.Tag;
CQueryBuilderVisitor q = new CQueryBuilderVisitor(); 
v.accept(q);
txtQuery.Text = q.GetQuery();

//CQueryBuilderVisitor
public class CQueryBuilderVisitor : CVisitor
{
  public override void visit(CView v) {
  m_query =v.GetDefinition();
  } 

  public override void visit(CTable t) {
  m_query = " SELECT * FROM [" + t.GetName() + "]";
  } 

// etc...


// CView
public void accept(CVisitor v) { 
v.visit(this);
}

//CVisitor
public abstract class CVisitor
{
public abstract void visit(CField f);
public abstract void visit(CTable t);
public abstract void visit(CView v);
public abstract void visit(CStoredproc sp);
}

Using Demo

GUI image shows in enough details how to use the tool. When the program starts, it adds in text area self explanation connection string where you add your own server name, user name etc. When you finish your connection string, click on connect to connect to server. The program opens connection and browses schema for properties. Then, it listed them into tree view. You can select a node in the tree to create query or open definition for the view or stored procedures. To execute your query click ! button.

Implementation

There are many ways to explore MS SQL. Using information schema property listed below and SqlCommand query you can lists almost everything that you need to know about your databases in SQL server. The list looks like this:

For example: to query views in Northwind database we can use the following command:

SELECT TABLE_NAME,VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
  WHERE TABLE_CATALOG='Northwind' AND TABLE_NAME NOT IN 
  (SELECT NAME FROM MASTER.DBO.sysobjects)

Disclaimer

The idea for install .NET and free IDE on 98/ME comes from someone who wrote article here "How to install NET SDK on 98". The credit goes to him.