.net下遍历数据库服务器的几种方法

今天写一个代码生成工具，所以整理一个类，写了一些遍历数据服务器地址的方法，希望对大家有用，有个问题就是我在win2003下调用SQLDMO 时会出错，没有仔细去网上查原因。里面用到很多api，有关api的具体调用，请参照msdn.
目前取得数据库server的方法有以下几种：
1、获取DSN（ODBC）
2、SQLDMO获取
3、注册表获取（local）
4、用api获取NetServerEnum



 1 using System;
  2 using System.Text;
  3 using System.Collections;
  4 using System.Runtime.InteropServices;
  5 using System.Data.SqlClient;
  6 using System.Web.UI.WebControls;
  7 using System.Security;
  8 using System.Diagnostics;
  9 
 10 using SQLDMO;
 11 
 12 namespace NetCode
 13 {   
 14     /// <summary>
 15     /// Summary description for GetServer.
 16     /// </summary>
 17     /// <summary>
 18     /// DSN class, that contains all DSN installed on the system.
 19     /// </summary>
 20     
 21     public class GetServer
 22     {
 23         
 24         private const Int64 SQL_SUCCESS = 0;    // ODBC sucess
 25         private const Int64 SQL_ERROR = -1;     // ODBC error
 26         private const Int32 SQL_FETCH_NEXT = 1; // ODBC Move Next
 27   
 28             
 29         /// <summary>
 30         /// ODBC WinAPI functions to retrieve the DSNs.
 31         /// </summary>
 32         [DllImport("ODBC32.DLL", EntryPoint="SQLDataSources",
 33              SetLastError=true,
 34              CharSet=CharSet.Ansi, ExactSpelling=true,
 35              CallingConvention=CallingConvention.StdCall)]
 36         public static extern short SQLDataSources(IntPtr EnvironmentHandle,
 37             Int16 Direction, StringBuilder ServerName,
 38             Int16 BufferLength1, ref Int16 NameLength1Ptr, StringBuilder
 39             Description,
 40             Int16 BufferLength2, ref Int16 NameLength2Ptr);
 41 
 42         [DllImport("ODBC32.DLL", EntryPoint="SQLAllocEnv",
 43              SetLastError=true,
 44              CharSet=CharSet.Ansi, ExactSpelling=true,
 45              CallingConvention=CallingConvention.StdCall)]
 46         private static extern Int32 SQLAllocEnv(ref IntPtr env);
 47 
 48         [DllImport("ODBC32.DLL", EntryPoint="SQLFreeEnv", SetLastError=true,
 49              CharSet=CharSet.Ansi, ExactSpelling=true,
 50              CallingConvention=CallingConvention.StdCall)]
 51         private static extern Int32 SQLFreeEnv(IntPtr hEnv);
 52         /// <summary>
 53         /// get ServerName 
 54         /// </summary>
 55         
 56             [DllImport("netapi32.dll",EntryPoint="NetApiBufferFree")]
 57             private static extern UInt32 NetApiBufferFree( [MarshalAs(UnmanagedType.U4)]UInt32 buffer);
 58         
 59         /// <summary>
 60         /// retrieves current configuration information for the specified server
 61         /// </summary>
 62         [DllImport("netapi32.dll",EntryPoint="NetServerGetInfo")]
 63         private static extern UInt32 NetServerGetInfo( [MarshalAs(UnmanagedType.LPWStr)]string servername, 
 64             [MarshalAs(UnmanagedType.U4)]UInt32 level, 
 65             ref UInt32 bufptr);
 66         /// <summary>
 67         /// enumerate servers
 68         /// </summary>
 69         [DllImport("netapi32.dll",EntryPoint="NetServerEnum")]
 70         private static extern UInt32 NetServerEnum( [MarshalAs(UnmanagedType.LPWStr)]string servername, 
 71             [MarshalAs(UnmanagedType.U4)]UInt32 level, 
 72             ref UInt32 bufptr, 
 73             Int32 prefmaxlen,
 74             ref UInt32 entriesread, 
 75             ref UInt32 totalentries,
 76             [MarshalAs(UnmanagedType.U4)]UInt32 servertype,
 77             [MarshalAs(UnmanagedType.LPWStr)]string domain,
 78             UInt32 resume_handle);
 79         //enum for server types
 80         /// <summary>
 81         /// list of all available server types
 82         /// </summary>
 83         public struct SV_101_TYPES
 84         {
 85             public const UInt32 SV_TYPE_WORKSTATION       =  0x00000001;
 86             public const UInt32 SV_TYPE_SERVER            =  0x00000002;
 87             public const UInt32 SV_TYPE_SQLSERVER         =  0x00000004;
 88             public const UInt32 SV_TYPE_DOMAIN_CTRL       =  0x00000008;
 89             public const UInt32 SV_TYPE_DOMAIN_BAKCTRL    =  0x00000010;
 90             public const UInt32 SV_TYPE_TIME_SOURCE       =  0x00000020;
 91             public const UInt32 SV_TYPE_AFP               =  0x00000040;
 92             public const UInt32 SV_TYPE_NOVELL            =  0x00000080;
 93             public const UInt32 SV_TYPE_DOMAIN_MEMBER     =  0x00000100;
 94             public const UInt32 SV_TYPE_PRINTQ_SERVER     =  0x00000200;
 95             public const UInt32 SV_TYPE_DIALIN_SERVER     =  0x00000400;
 96             public const UInt32 SV_TYPE_XENIX_SERVER      =  0x00000800;
 97             public const UInt32 SV_TYPE_SERVER_UNIX       =  SV_TYPE_XENIX_SERVER;
 98             public const UInt32 SV_TYPE_NT                =  0x00001000;
 99             public const UInt32 SV_TYPE_WFW               =  0x00002000;
100             public const UInt32 SV_TYPE_SERVER_MFPN       =  0x00004000;
101             public const UInt32 SV_TYPE_SERVER_NT         =  0x00008000;
102             public const UInt32 SV_TYPE_POTENTIAL_BROWSER =  0x00010000;
103             public const UInt32 SV_TYPE_BACKUP_BROWSER    =  0x00020000;
104             public const UInt32 SV_TYPE_MASTER_BROWSER    =  0x00040000;
105             public const UInt32 SV_TYPE_DOMAIN_MASTER     =  0x00080000;
106             public const UInt32 SV_TYPE_SERVER_OSF        =  0x00100000;
107             public const UInt32 SV_TYPE_SERVER_VMS        =  0x00200000;
108             public const UInt32 SV_TYPE_WINDOWS           =  0x00400000; /* Windows95 and above */
109             public const UInt32 SV_TYPE_DFS               =  0x00800000; /* Root of a DFS tree */
110             public const UInt32 SV_TYPE_CLUSTER_NT        =  0x01000000; /* NT Cluster */
111             public const UInt32 SV_TYPE_TERMINALSERVER    =  0x02000000; /* Terminal Server(Hydra) */
112             public const UInt32 SV_TYPE_CLUSTER_VS_NT     =  0x04000000; /* NT Cluster Virtual Server Name */
113             public const UInt32 SV_TYPE_DCE               =  0x10000000; /* IBM DSS (Directory and Security Services) or equivalent */
114             public const UInt32 SV_TYPE_ALTERNATE_XPORT   =  0x20000000; /* return list for alternate transport */
115             public const UInt32 SV_TYPE_LOCAL_LIST_ONLY   =  0x40000000; /* Return local list only */
116             public const UInt32 SV_TYPE_DOMAIN_ENUM       =  0x80000000;
117             public const UInt32 SV_TYPE_ALL               =  0xFFFFFFFF;  /* handy for NetServerEnum2 */
118 
119         }
120 
121         //declare the structures to hold info
122                                        /// <summary>
123                                        /// Structure for the SERVER_INFO_101
124                                        /// </summary>
125                                        [StructLayout(LayoutKind.Sequential)]
126          public struct SERVER_INFO_101
127                                        {
128                                            [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv101_platform_id;
129                                            [MarshalAs(System.Runtime.InteropServices.UnmanagedType.LPWStr)]    public string   sv101_name;
130                                            [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv101_version_major;
131                                            [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv101_version_minor;
132                                            [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv101_type;
133                                            [MarshalAs(System.Runtime.InteropServices.UnmanagedType.LPWStr)]    public string   sv101_comment;
134                                        } // end of struct SERVER_INFO_101
135 
136         /// <summary>
137         /// Structure for the SERVER_INFO_102
138         /// </summary>
139         [StructLayout(LayoutKind.Sequential)]
140             public struct SERVER_INFO_102
141         {
142             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv102_platform_id;
143             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.LPWStr)]    public string   sv102_name;
144             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv102_version_major;
145             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv102_version_minor;
146             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv102_type;
147             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.LPWStr)]    public string   sv102_comment;
148             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv102_users;
149             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv102_disc;
150             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.Bool)]        public bool        sv102_hidden;
151             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv102_announce;
152             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv102_anndelta;
153             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.U4)]        public UInt32    sv102_licenses;
154 
155             [MarshalAs(System.Runtime.InteropServices.UnmanagedType.LPWStr)]    public string   sv102_userpath;
156 
157         } // end of struct SERVER_INFO_102
158         //length of data to be returned
159         //we'll stick to all data here
160         public struct PREF_LENGTH
161         {
162             public const Int32  MAX_PREFERRED_LENGTH = -1; 
163         }
164 
165         //here's some possible error codes
166         public struct NERR
167         {
168             public const UInt32  NERR_Success                    =  0;       /* Success */
169             public const UInt32  ERROR_MORE_DATA                =  234;    // dderror
170             public const UInt32  ERROR_NO_BROWSER_SERVERS_FOUND =  6118;
171             /// <summary>
172             /// The system call level is not correct.
173             /// </summary>
174             public const UInt32  ERROR_INVALID_LEVEL            = 124;
175             /// <summary>
176             /// Access is denied.
177             /// </summary>
178             public const UInt32  ERROR_ACCESS_DENIED            = 5;
179             /// <summary>
180             /// The parameter is incorrect.
181             /// </summary>
182             public const UInt32  ERROR_INVALID_PARAMETER        = 87;
183             /// <summary>
184             /// Not enough storage is available to process this command.
185             /// </summary>
186             public const UInt32  ERROR_NOT_ENOUGH_MEMORY        = 8;
187             /// <summary>
188             /// The network is busy.
189             /// </summary>
190             public const UInt32  ERROR_NETWORK_BUSY                = 54;
191             /// <summary>
192             /// The network path was not found.
193             /// </summary>
194             public const UInt32  ERROR_BAD_NETPATH                = 53;
195         }
196         
197 
198         /// <summary>
199         /// Gets all DSN installed on the system.
200         /// </summary>
201         public static IList DSN_GetDataSourceNames()
202         {
203             ArrayList dsnList = new ArrayList();
204 
205             Int16 RetCode, DSNLen = 0, DrvLen = 0;
206             StringBuilder DSNItem, DRVItem;
207             String DSN;
208             IntPtr hEnv = IntPtr.Zero;            
209 
210             if (SQLAllocEnv(ref hEnv) != SQL_ERROR)
211             {
212                 do
213                 {
214                     DSNItem = new StringBuilder(1024);
215                     DRVItem = new StringBuilder(1024);
216 
217                     RetCode = SQLDataSources(hEnv, SQL_FETCH_NEXT, DSNItem, 1024, ref DSNLen, DRVItem, 1024, ref DrvLen);
218 
219                     if (DSNItem != null)
220                     {
221                         DSNItem.Length = DSNLen;
222                         DSN = DSNItem.ToString();
223 
224                         if (DSN.IndexOf("\0") == 0) 
225                             continue;
226 
227                         if (DSN.Length > 0) 
228                         {
229                             dsnList.Add(DSN);
230                         }                            
231                     }
232                 }
233 
234                 while (RetCode == SQL_SUCCESS);
235                 SQLFreeEnv(hEnv);
236             }
237 
238             dsnList.Sort();
239 
240             return dsnList;
241         }
242     //using SQLDMO to getdataserver
243         public static IList SQLDMO_GetDataServer()
244         {
245             
246                 SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
247                 SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
248         
249                 ArrayList dataList = new ArrayList();
250                 if(sqlServers!=null)
251                 {
252                     foreach(string name in sqlServers)
253                         dataList.Add(name);
254                 
255             
256                 }
257                 else 
258                 {
259         
260             
261                     string serverName=GetServerFromReg();
262                     if(serverName!=null)
263                         dataList.Add(serverName);
264 
265                 }
266             
267             
268                 return dataList;
269             
270             
271         }
//reading RegistryKey
272         private static string GetServerFromReg()
273         {
274     
275             Microsoft.Win32.RegistryKey rk =Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
276             String[] instances = (String[])rk.GetValue("InstalledInstances");
277             String name = "";
278             if (instances.Length > 0)
279             {
280                 foreach (String element in instances)
281                 {
282                     
283                     //only add if it doesn't exist
284                     if (element == "MSSQLSERVER")
285                         name = System.Environment.MachineName;
286                     else
287                         name = System.Environment.MachineName + @"\" + element;
288
291                 }
292                 
293             }
294             return name;
295         }
//using api to getserver
296         public static IList GetNetServerEnum(string domainName)
297         {
298             ArrayList arServer=new ArrayList();
299             SERVER_INFO_101 si;
300 
301             //buffer
302             UInt32 ppSVINFO = 0;
303             UInt32 etriesread = 0;
304             UInt32 totalentries = 0;
305 
306             
307             
308                 if( NetServerEnum(null, 
309                     (UInt32)101, 
310                     ref ppSVINFO, 
311                     PREF_LENGTH.MAX_PREFERRED_LENGTH, 
312                     ref etriesread, 
313                     ref totalentries, 
314                     SV_101_TYPES.SV_TYPE_SQLSERVER | SV_101_TYPES.SV_TYPE_DOMAIN_CTRL, 
315                     domainName.Trim().Length>0?domainName:null,
316                     (UInt32)0) == 0 )
317                 {
318                     //get the pointer to the data returned
319                     IntPtr ppr = new IntPtr( ppSVINFO );
320 
321                     Int32 ptr = ppr.ToInt32();
322 
323                     //loop thru the records returned
324                     for(int i = 0; i < etriesread; i++)
325                     {
326                         //get data into SERVER_INFO struct
327                         si = (SERVER_INFO_101)Marshal.PtrToStructure( new IntPtr(ptr), typeof(SERVER_INFO_101) );
328 
329                         //get the data here,
330                         //si.sv101_name, si.sv101_platform_id, //si.sv101_type etc - you get the point
331                         arServer.Add(si.sv101_name);
332                         //increment the pointer                    
333                         ptr += Marshal.SizeOf( si );
334 
335                     }
336                  return arServer;
337                 }
338                 
339                 //clean up
340                 NetApiBufferFree(ppSVINFO);
341                  return arServer;
342             
343         
344         }
345     }
346 }
347 
348