Using SQL Server Distributed Management object (SQL-DMO) in VB. NET)

Reference http://dotnet.aspx.cc/ShowDetail.aspx? BCEAADFB-CFF3-4804-B3B3-6C7D6488982B #

We used to obtain detailed information about SQL Server Multiple times in previous applications. In the past, we had to use APIs and inefficient ADO calls. Now, we have a new method, SQLDMO (SQL Distributed Management Objects, SQL Distributed Management object), although it is not currently known and used, however, SQLDMO provides many powerful functions that are similar to those that use code to obtain information from SQL Server. For example, we will only explain how to obtain the SQL Server list on the local network, how to connect each SQL Server and obtain the list of tables, stored procedures, and views in the Server.

The SQLDMO object comes from SQLDMO. dll. SQLDMO. dll is released along with SQL Server2000. SQLDMO. dll itself is a COM Object. Therefore, it must be referenced in your. NET project first. The VS. NET integrated development environment will create all necessary COM encapsulation. Note: If you use the "using SQLDMO;" statement to reference it, you will get an error message. To make it work properly in your application, you must reference it as follows:

After referencing the COM object, you can easily use it.

All operations in this example use one or more of the following objects:

SQLDMO. Application <br/> SQLDMO. SQLServer <br/> SQLDMO. Database <br/> SQLDMO. NameList <br/>

Many objects such as data backup and recovery can be used, but as an example, we will try to make it as simple as possible, so that you can easily enter the world of SQLDMO to browse its convenience.

Listing SQL servers in the local network is relatively simple. First, you need to reference sqldmo. application object. You must create an sqldmo. application. the Return Value of the listavailablesqlservers () method is sqldmo. namelist, sqldmo. namelist is the COM set of server names.

Remember: Before you get used to calling COM objects, it is always terrible to call COM objects, but it will be good if you get used to it. The following is a sample code that fills in the drop-down list box using the list of SQL servers that can be used locally.

'Obtain the list of SQL servers available in the local network.

Me. cboxser. Items. Clear ()

Dim sqlapp as new sqldmo. applicationclass
Dim sqlservers as sqldmo. namelist = sqlapp. listavailablesqlservers

Dim I as int16 = 0
Dim OBJ as object

For I = 0 to sqlservers. Count-1
OBJ = sqlservers. Item (I)
If not obj is nothing then
Me. cboxser. Items. Add (OBJ)
End if
Next

As mentioned above, isn't it very simple? Remember: the first project in the COM set is 1, not 0.

Connecting to the database and getting a list of all databases is also quite simple. The following code uses the SQL Server selected in the drop-down list box above and connects to the server (using the username and password entered in the text box) to generate a drop-down list box for the Database List on the server.

 

'// Obtain the list of all databases on the specified SQL Server

Dim sqlapp as new sqldmo. applicationclass
Dim sqlser as new sqldmo. sqlserverclass

'Srv.connect(this.cboservers.selecteditem.tostring(,,this.txtuser.text,this.txt Password. Text );

SqlSer. Connect (Me. CBoxSer. SelectedItem. ToString, Me. TxtName. Text, Me. TxtPwd. Text)

Dim db As SQLDMO. Database

Me. CBoxDaseBase. Items. Clear ()
For Each db In sqlSer. Databases
Me. CBoxDaseBase. Items. Add (db. Name)
Next