| |
| Imagine that you are writing
an application that allows users to create various SQL Server
objects. Often applications that implement these kinds of interfaces,
execute some sort of dynamic SQL or use a more sophisticated
object library like SQL-DMO. But, would it not be nice if you
could present an interface that is similar to the one that Enterprise
Manager provides (for example, the database creation dialog
box)?? In fact, this is possible and it is very easy to do so.
The tool or API that allows you to invoke and display Enterprise
Manager user interfaces and dialog boxes is called SQL-NS
or SQL Namespace. |
| |
| SQL Namespace (SQL-NS) is a
set of COM interfaces that allow any COM enabled development
tool (like VB.NET or Visual Basic 6.0) to invoke wizards, property
sheets, dialog boxes and other SQL Server Enterprise Manager
components. This layer complements the SQL Distributed Management
Objects (SQL-DMO) layer of COM objects that was introduced in
Microsoft SQL Server™ version 6.0. SQL-NS is layered on
SQL-DMO. |
| |
| Availability of SQLNS |
| |
| All required SQL-NS components
are installed as part of a Microsoft® SQL Server™
2000 server installation. SQL-NS is implemented in a single
dynamic-link library (DLL). You may develop SQL-NS applications
on either a client or a server. When using a development tool
like VS.NET, all that is required to start and develop a SQLNS
application is just a reference to the SQLNS DLL file. The following
are some of the core objects available for SQLNS: |
| |
|
Directory Name |
File Name |
Description |
| SQL Server Folder\80\Tools\Binn |
Sqlns.dll |
DLL implementing SQL-NS objects. |
| SQL Server Folder\80\Tools\Binn |
Sqlns80.hlp |
SQL-NS help file used within the
development environment to provide context sensitive
help. |
| SQL Server Folder\80\Tools\Binn\Resources\xxxx |
Sqlns.rll |
Localized resource file. |
|
|
| |
| Thus, to develop SQLNS applications
on a client computer, the following registrations need to be
done. |
| |
- REGSVR32 sqlns.rll
- REGSVR32 sqlns.dll
|
| SQL-NS Object Model |
| |
| SQL-NS has a very simple object
model. The following figure shows the overall object model.
|
| |
 |
| |
| Programming against this object
model is quite simple and requires just a few simple steps: |
| |
| |
Identify the starting
point for SQL-NS. The starting point can be either
a SQL Server, or a server group, or the default root.
The following figure shows what these starting points
mean. |
| |
|
| |
 |
| |
|
| |
The starting points of "Server
Group" and "Default Root" get the connection
information of the servers from the Enterprise Manager
registration database itself. If you use the starting
point a "specific SQL Server", then you can
specify the connection string yourself. |
| |
|
| |
Once you have identified
the starting point, you can start walking down the child
items at that level using various SQL-NS identifiers.
For example, if you have decided that a specific SQL Server
is your starting point, you can then get at the databases
level and then walk down each database. The following
figure shows this level of detail. |
| |
|
| |
 |
| |
|
| |
Once you have reached the
specified level that you want, you can execute commands
at that level that will invoke the appropriate Enterprise
Manager user interface by getting a handle to the interface
object. SQL-NS exposes commands either by name or by ID.
The following figure shows the possible commands at the
database level of "master". |
|
| |
 |
| |
| Note that not all commands exposed
by Enterprise Manager are available to SQL-NS. This is because,
Enterprise Manager can expose its own features that are specific
to itself and not available for other API's. |
| |
| SQL-NS Sample Programs |
| |
| The best way to learn SQL-NS
is to try out various samples that exemplify the object model
and its flexibility. The following set of samples are aimed
at helping you get the best out of SQL-NS. In each of the samples,
you will see how we freeze the staring point and then navigate
down the hierarchy. Before to get into the list of samples,
all the sample .NET applications have a reference to the
SQLNS object library. The following figure shows the reference
dialog box. |
| |
 |
| |
| Sample - 1 |
| |
| The following example shows
how to specify the starting point of SQL-NS to a specific SQL
Server. |
| |
| Module
Module1 Sub
Main()
' Local variables used
by the program
Dim oSQLNS As SQLNS.SQLNamespace
Dim hWindow As Long
'
Instantiate the SQL-NS library and initialize it to a SQL
Server
oSQLNS = New SQLNS.SQLNamespace
Try
oSQLNS.Initialize("Sample
Application", SQLNSRootType.SQLNSRootType_Server, "Server=localhost;UID=sa;PWD=password",
hWindow)
Catch
ex As Exception
MsgBox("SQLNS
Error : " & ex.Message)
End Try
'
Wait for user input to close the application
Console.ReadLine()
End Sub
End Module |
| |
| Sample - 2 |
| |
| The following example shows
how to reach the "Databases" node for a specific SQL
Server. The databases node is the first node in Enterprise Manager
after a specific SQL Server. |
| |
| Module
Module1 Sub
Main()
' Local variables used
by the program
Dim oSQLNS As SQLNS.SQLNamespace
Dim hWindow As Long, hRootItem
As Long, hDatabases As Long
'
Instantiate the SQL-NS library and initialize it to a SQL
Server
oSQLNS = New SQLNS.SQLNamespace
Try
oSQLNS.Initialize("Sample
Application", SQLNSRootType.SQLNSRootType_Server, "Server=localhost;UID=sa;PWD=password",
hWindow)
'
Freeze on the starting point and get a handle to the databases
node
hRootItem
= oSQLNS.GetRootItem()
hDatabases
= oSQLNS.GetFirstChildItem(hRootItem, SQLNSObjectType.SQLNSOBJECTTYPE_DATABASES)
Catch
ex As Exception
MsgBox("SQLNS
Error : " & ex.Message)
End Try
' Wait for user input
to close the application
Console.ReadLine()
End Sub
End Module |
| |
| Sample - 3 |
| |
| Having reached the databases
node, let's see what all commands are available for us to execute.
This sample shows all the commands available at the databases
node. |
| |
| Module
Module1 Sub
Main()
' Local variables used
by the program
Dim oSQLNS As SQLNS.SQLNamespace
Dim oSQLNSObj As SQLNS.SQLNamespaceObject
Dim oCommand As SQLNS.SQLNamespaceCommand
Dim hWindow As Long, hRootItem
As Long, hDatabases As Long
Dim strResult As String
'
Instantiate the SQL-NS library and initialize it to a SQL
Server
oSQLNS = New SQLNS.SQLNamespace
Try
oSQLNS.Initialize("Sample
Application", SQLNSRootType.SQLNSRootType_Server, "Server=localhost;UID=sa;PWD=password",
hWindow)
'
Freeze on the starting point and get a handle to the databases
node
hRootItem
= oSQLNS.GetRootItem()
hDatabases
= oSQLNS.GetFirstChildItem(hRootItem, SQLNSObjectType.SQLNSOBJECTTYPE_DATABASES)
'
Get a reference to the enterprise manager user interface
' and
print all the commands available at that level
oSQLNSObj
= oSQLNS.GetSQLNamespaceObject(hDatabases)
strResult
= "Command List:" & vbCrLf
For
Each oCommand In oSQLNSObj.Commands
strResult
= strResult & oCommand.Name & vbCrLf
Next
MsgBox(strResult)
Catch
ex As Exception
MsgBox("SQLNS
Error : " & ex.Message)
End Try
'
Wait for user input to close the application
Console.ReadLine()
End Sub
End Module |
| |
| When you execute the program,
you get an output message box that shows New Database. This
indicates that this is the only command available at this level. |
| |
| Sample - 4 |
| |
| Now that we have seen the commands
available, let's execute one of them. The following sample executes
the "New Database" command at the databases level.
|
| |
| Module
Module1 Sub
Main()
' Local variables used
by the program
Dim oSQLNS As SQLNS.SQLNamespace
Dim oSQLNSObj As SQLNS.SQLNamespaceObject
Dim oCommand As SQLNS.SQLNamespaceCommand
Dim hWindow As Long, hRootItem
As Long, hDatabases As Long
Dim strResult As String
'
Instantiate the SQL-NS library and initialize it to a SQL
Server
oSQLNS = New SQLNS.SQLNamespace
Try
oSQLNS.Initialize("Sample
Application", SQLNSRootType.SQLNSRootType_Server, "Server=localhost;UID=sa;PWD=password",
hWindow)
'
Freeze on the starting point and get a handle to the databases
node
hRootItem
= oSQLNS.GetRootItem()
hDatabases
= oSQLNS.GetFirstChildItem(hRootItem, SQLNSObjectType.SQLNSOBJECTTYPE_DATABASES)
'
Get a reference to the enterprise manager user interface
' and
execute the **New Database** command. The command will show
the dialog
' box
in a modal form
oSQLNSObj
= oSQLNS.GetSQLNamespaceObject(hDatabases)
oSQLNSObj.ExecuteCommandByName("New
Database", hDatabases, SQLNSModality.SQLNamespace_PreferModal)
Catch
ex As Exception
MsgBox("SQLNS
Error : " & ex.Message)
End Try
'
Wait for user input to close the application
Console.ReadLine()
End Sub
End Module
|
| |
| When you execute this program,
you will see the "Database Creation" dialog box as
shown in the following figure. You can enter the relevant information
into this dialog box and the database will be created. You will
now begin to realize the amount of power that an application
written using SQL-NS can provide you :-) |
| |
 |
| |
| Sample - 5 |
| |
| Having seen what we can do at
the databases node, let's navigate a step further down and give
the facility to create a new stored procedure at the "pubs"
database level. The following code shows how you can do this. |
| |
| Module
Module1 Sub
Main()
' Local variables used
by the program
Dim oSQLNS As SQLNS.SQLNamespace
Dim oSQLNSObj As SQLNS.SQLNamespaceObject
Dim oCommand As SQLNS.SQLNamespaceCommand
Dim hWindow As Long, hRootItem
As Long, hDatabases As Long, hDatabase As Long, hProcedures
As Long
Dim strResult As String
'
Instantiate the SQL-NS library and initialize it to a SQL
Server
oSQLNS = New SQLNS.SQLNamespace
Try
oSQLNS.Initialize("Sample
Application", SQLNSRootType.SQLNSRootType_Server, "Server=localhost;UID=sa;PWD=password",
hWindow)
'
Freeze on the starting point and get a handle to the databases
node.
' After
this navigate to the "pubs" database
hRootItem
= oSQLNS.GetRootItem()
hDatabases
= oSQLNS.GetFirstChildItem(hRootItem, SQLNSObjectType.SQLNSOBJECTTYPE_DATABASES)
hDatabase
= oSQLNS.GetFirstChildItem(hDatabases, SQLNSObjectType.SQLNSOBJECTTYPE_DATABASE,
"pubs")
'
Having reached the "pubs" database, navigate to
the "stored procedures" node
hProcedures
= oSQLNS.GetFirstChildItem(hDatabase, SQLNSObjectType.SQLNSOBJECTTYPE_DATABASE_SPS)
'
Get a reference to the enterprise manager user interface
' and
execute the **New Stored Procedure** command. The command
will show the dialog
' box
in a modal form
oSQLNSObj
= oSQLNS.GetSQLNamespaceObject(hProcedures)
oSQLNSObj.ExecuteCommandByName("New
Stored Procedure", hProcedures, SQLNSModality.SQLNamespace_PreferModal)
Catch
ex As Exception
MsgBox("SQLNS
Error : " & ex.Message)
End Try
'
Wait for user input to close the application
Console.ReadLine()
End Sub
End Module
|
| |
| The following figure shows the
output that you will see from the sample. |
| |
 |
| |
| Again, you see the amount of
flexibility that this feature can give you. With just some lines
of code, you are able to tap into all the features and capabilities
provided by Enterprise Manager. For example, in the above interface,
you get all capabilities to create and check stored procedures.
The procedure that you save here will be saved in the pubs database,
since we connected to that. Well, that's an amazing amount of
flexibility!! |
| |
| Conclusion |
| |
| In this article, we have seen
how to leverage the capabilities of SQL-NS to provide rich capabilities
to client applications leveraging the existing interfaces of
Enterprise Manager. The applications of a such a powerful library
are many and I leave it to the readers to play around with SQL-NS.
You can refer to SQL Server Books Online for more information
on the various levels that you can reach in Enterprise Manager.
Have fun!! |
| |
| For more questions, you
can mail me at srisamp@hotmail.com
or visit my web site at http://www32.brinkster.com/srisamp.
|
| |
