server|sqlserver| Data | database
In the. Net framework, it is easy to invoke COM components, and sometimes we need to get a list of service instances running on a SQL Server and a database listing on an instance. This work can be done easily with the Microsoft.SQLDMO.Object component:
First how to find Microsoft.SQLDMO.Object
1. How can I use the SQLDMO component in your project?
Menu-item-Add Reference-com-microsoft.sqldmo.object
2. Write the function as a class:
1using System;
2using System.Collections;
3using System.Collections.Specialized;
4
5namespace Jillzhang
6{
7/**////<summary>
8///Summary description for Sqlinfo.
9///</summary>
Ten public class Sqlinfo
11 {
12 member variable #region member variable
private string _uid= "";
private string _pwd= "";
Private StringCollection _serverlist=new stringcollection ();
Private Hashtable _databaselist=new Hashtable ();
#endregion
18
19 constructor #region Constructor function
Public Sqlinfo ()
21 {
This._serverlist=getsqlinstances ();
if (this._serverlist.count>0)
24 {
-foreach (String item in This._serverlist)
26 {
This._databaselist.add (item,this. Getalldatabases (item));
28}
29}
30}
To public sqlinfo (string uid,string pwd)
32 {
This._uid=uid;
This._pwd=pwd;
This._serverlist=getsqlinstances ();
if (this._serverlist.count>0)
37 {
-foreach (String item in This._serverlist)
39 {
This._databaselist.add (item,this. Getalldatabases (item));
41}
42}
43}
#endregion
45
46 Public Properties #region Public properties
/**////<summary>
48///Service List
///</summary>
Public StringCollection ServerList
51 {
Get
53 {
The return _serverlist;
55}
56}
/**////<summary>
58///User name for login to SQL Server
///</summary>
The public string Uid
61 {
Get
63 {
return _uid;
65}
Set
67 {
_uid=value;
69}
70}
/**////<summary>
72///Password for login
///</summary>
PWD public string
75 {
A Get
77 {
_pwd return;
79}
-Set
81 {
_pwd=value;
83}
84}
#endregion
86
87 Event Method #region Event method
/**////<summary>
89///Get Service list
///</summary>
///<returns></returns>
System.Collections.Specialized.StringCollection public static getsqlinstances ()
93 {
94//Declare a string list for listing information
System.Collections.Specialized.StringCollection instaces= New System.Collections.Specialized.StringCollectio N ();
Try
97 {
98//The following code is implemented by invoking the SQLDMO component to obtain a list of services
SQLDMO. Application sqlapplication= new SQLDMO. ApplicationClass ();
SQLDMO. NameList sqlserverintances=sqlapplication.listavailablesqlservers ();
for (int i=0;i<sqlserverintances.count;i++)
102 {
Object Svr=sqlserverintances.item (i+1);//index starting from 1
(Svr!=null)
105 {
The instaces. Add (SVR. ToString ());
107}
108}
109 return instaces;
110}
A catch
112 {
113 throw new Exception ("Failed to get the server list information, please see if your SQL Server is running!") ");
114}
115}
116
117/**////<summary>
118///Get database list on SQL Server 20,001 server instance
119///</summary>
///<param name= "Server" > Service name </param>
121///<param name= "UID" > Login user </param>
122///<param name= "pwd" > Login password </param>
123///<returns> Database list </returns>
124 public static System.Collections.Specialized.StringCollection getalldatabases (string server,string uid,string p wd
125 {
126 System.Collections.Specialized.StringCollection databases= New System.Collections.Specialized.StringCollect Ion ();
127 try
128 {
129 SQLDMO. SQL Server SQL Server =new SQLDMO. Sqlserverclass ();
130 Sqlserver.connect (SERVER,UID,PWD);
131 foreach (SQLDMO. Database DB in SQLServer.Databases)
132 {
The IF (db). Name!=null)
134 {
135 databases. ADD (db. Name);
136}
137}
138 return databases;
139}
140 Catch
141 {
throw new Exception ("Failed to get the service" +server+ "Database list, you may have the server did not start or you have a username or password error");
143}
144}
145 Public System.Collections.Specialized.StringCollection getalldatabases (string server)
146 {
147 return getalldatabases (SERVER,THIS._UID,THIS._PWD);
148}
149 #endregion
150
151}
152}
153