How to get SQL Server 2000 instance list and run a database list on an instance

Source: Internet
Author: User
Tags foreach constructor count return variable
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



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.