In. in the. NET Framework, the COM component can be conveniently called. Sometimes we need to obtain the list of service instances running on a SQL Server and the list of databases on an instance, through Microsoft. sqldmo. the object component can easily complete this task:
First, how to find Microsoft. sqldmo. Object
1. How can I use sqldmo components in your project?
Menu-project-add reference-COM-Microsoft.SQLDMO.Object
2. Write this function as a class:
1 using system;
2 using system. collections;
3 using system. Collections. Specialized;
4
5 namespace jillzhang
6 {
7/*** // <summary>
8 // summary description for sqlinfo.
9 /// </Summary>
10 public class sqlinfo
11 {
12 member variables # Region member variables
13 private string _ uid = "";
14 private string _ Pwd = "";
15 private stringcollection _ serverlist = new stringcollection ();
16 private hashtable _ databaselist = new hashtable ();
17 # endregion
18
19. constructor # region Constructor
20 public sqlinfo ()
21 {
22 This. _ serverlist = getsqlinstances ();
23 if (this. _ serverlist. Count> 0)
24 {
25 foreach (string item in this. _ serverlist)
26 {
27 This. _ databaselist. Add (item, this. getalldatabases (item ));
28}
29}
30}
31 public sqlinfo (string uid, string PWD)
32 {
33 this. _ uid = uid;
34 this. _ Pwd = PWD;
35 This. _ serverlist = getsqlinstances ();
36 IF (this. _ serverlist. Count> 0)
37 {
38 foreach (string item in this. _ serverlist)
39 {
40 this. _ databaselist. Add (item, this. getalldatabases (item ));
41}
42}
43}
44 # endregion
45
46 Public attributes # Region Public attributes
47/*** // <summary>
48 // service list
49 // </Summary>
50 public stringcollection serverlist
51 {
52 get
53 {
54 return _ serverlist;
55}
56}
57/** // <summary>
58 // username used to log on to SQL Server
59 /// </Summary>
60 public string uid
61 {
62 get
63 {
64 return _ uid;
65}
66 set
67 {
68 _ uid = value;
69}
70}
71/*** // <summary>
72 // used for Logon Password
73 /// </Summary>
74 public string pwd
75 {
76 get
77 {
78 return _ PWD;
79}
80 set
81 {
82 _ Pwd = value;
83}
84}
85 # endregion
86
87 event method # region event Method
88/** // <summary>
89 // obtain the service list
90 /// </Summary>
91 /// <returns> </returns>
92 public static system. Collections. Specialized. stringcollection getsqlinstances ()
93 {
94 // declare a string linked list to store list information
95 system. Collections. Specialized. stringcollection instaces = new system. Collections. Specialized. stringcollection ();
96 try
97 {
98 // below Code Obtain the service list by calling the sqldmo component.
99 sqldmo. Application sqlapplication = new sqldmo. applicationclass ();
100 sqldmo. namelist sqlserverintances = sqlapplication. listavailablesqlservers ();
101 For (INT I = 0; I <sqlserverintances. Count; I ++)
102 {
103 object SVR = sqlserverintances. Item (I + 1); // The index starts from 1.
104 If (SVR! = NULL)
105 {
106 instaces. Add (SVR. tostring ());
107}
108}
109 return instaces;
110}
111 catch
112 {
113 throw new exception ("failed to get the server list information. Please check whether your SQL server is running! ");
114}
115}
116
117/*** // <summary>
118 // obtain the Database List on a server instance of sqlserver 2000
119 /// </Summary>
120 /// <Param name = "server"> service name </param>
121 /// <Param name = "uid"> logon user </param>
122 /// <Param name = "PWD"> logon password </param>
123 /// <returns> database list </returns>
124 public static system. Collections. Specialized. stringcollection getalldatabases (string server, string uid, string PWD)
125 {
126 system. Collections. Specialized. stringcollection databases = new system. Collections. Specialized. stringcollection ();
127 try
128 {
129 sqldmo. sqlserver = new sqldmo. sqlserverclass ();
130 sqlserver. Connect (server, uid, PWD );
131 foreach (sqldmo. Database dB in sqlserver. databases)
132 {
133 If (db. Name! = NULL)
134 {
135 databases. Add (db. Name );
136}
137}
138 return databases;
139}
140 catch
141 {
142 throw new exception ("failed to get service" + SERVER + ", the Database List is displayed. You may not have to start the server or your username or password is incorrect ");
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}