Most of us developing our programs by using MSSQL Server, and it is a necessary for us to make SQL Server selectable in the Options Dialog. But here the problem is how we can find the SQL Servers in the network ?
There are two different way we can use for searching the network for the available Sql Servers.
1 - There is a class in the .NET Framework which name is
SqlDataSourceEnumerator in the
System.Data.Sql namespace. We can use that class, and get the sql servers easly:
| DataTable sqls = SqlDataSourceEnumerator.Instance.GetDataSources(); |
In this method we can only get the Sql Server machines on a network. But in the second method we can also search the Terminal machines, server machines etc.
2 - We can use
NetServerEnum function in the
netapi32.dll. The following code demonstrates us how we can find SQL Servers in a network...
|
public class NativeMethods
{
public const uint ERROR_SUCCESS = 0;
public const uint ERROR_MORE_DATA = 234;
[DllImport("netapi32.dll", EntryPoint = "NetServerEnum")]
public static extern int NetServerEnum(
[MarshalAs(UnmanagedType.LPWStr)]
string servername,
int level,
out IntPtr bufptr,
int prefmaxlen,
ref int entriesread,
ref int totalentries,
SV_101_TYPES servertype,
[MarshalAs(UnmanagedType.LPWStr)]
string domain,
int resume_handle);
[DllImport("netapi32.dll", EntryPoint = "NetApiBufferFree")]
public static extern int NetApiBufferFree(IntPtr buffer);
[StructLayout(LayoutKind.Sequential)]
public struct SERVER_INFO_101
{
[MarshalAs(UnmanagedType.U4)]
public PLATFORM_ID sv101_platform_id;
[MarshalAs(UnmanagedType.LPWStr)]
public string sv101_name;
[MarshalAs(UnmanagedType.U4)]
public uint sv101_version_major;
[MarshalAs(UnmanagedType.U4)]
public uint sv101_version_minor;
[MarshalAs(UnmanagedType.U4)]
public SV_101_TYPES sv101_type;
[MarshalAs(UnmanagedType.LPWStr)]
public string sv101_comment;
}
public enum SV_101_TYPES : uint
{
SV_TYPE_WORKSTATION = 0x00000001,
SV_TYPE_SERVER = 0x00000002,
SV_TYPE_SQLSERVER = 0x00000004,
SV_TYPE_DOMAIN_CTRL = 0x00000008,
SV_TYPE_DOMAIN_BAKCTRL = 0x00000010,
SV_TYPE_TIME_SOURCE = 0x00000020,
SV_TYPE_AFP = 0x00000040,
SV_TYPE_NOVELL = 0x00000080,
SV_TYPE_DOMAIN_MEMBER = 0x00000100,
SV_TYPE_PRINTQ_SERVER = 0x00000200,
SV_TYPE_DIALIN_SERVER = 0x00000400,
SV_TYPE_XENIX_SERVER = 0x00000800,
SV_TYPE_SERVER_UNIX = 0x00000800,
SV_TYPE_NT = 0x00001000,
SV_TYPE_WFW = 0x00002000,
SV_TYPE_SERVER_MFPN = 0x00004000,
SV_TYPE_SERVER_NT = 0x00008000,
SV_TYPE_POTENTIAL_BROWSER = 0x00010000,
SV_TYPE_BACKUP_BROWSER = 0x00020000,
SV_TYPE_MASTER_BROWSER = 0x00040000,
SV_TYPE_DOMAIN_MASTER = 0x00080000,
SV_TYPE_SERVER_OSF = 0x00100000,
SV_TYPE_SERVER_VMS = 0x00200000,
SV_TYPE_WINDOWS = 0x00400000,
SV_TYPE_DFS = 0x00800000,
SV_TYPE_CLUSTER_NT = 0x01000000,
SV_TYPE_TERMINALSERVER = 0x02000000,
SV_TYPE_CLUSTER_VS_NT = 0x04000000,
SV_TYPE_DCE = 0x10000000,
SV_TYPE_ALTERNATE_XPORT = 0x20000000,
SV_TYPE_LOCAL_LIST_ONLY = 0x40000000,
SV_TYPE_DOMAIN_ENUM = 0x80000000,
SV_TYPE_ALL = 0xFFFFFFFF
}
public enum PLATFORM_ID : uint
{
PLATFORM_ID_DOS = 300,
PLATFORM_ID_OS2 = 400,
PLATFORM_ID_NT = 500,
PLATFORM_ID_OSF = 600,
PLATFORM_ID_VMS = 700
}
}
|
We can use the following native methods as follows;
|
public List<string> GetSqlServers()
{
List<string> servers = new List<string>();
int readed = 0;
int total = 0;
do
{
IntPtr buffer;
NativeMethods.SERVER_INFO_101 server;
int retVal = NativeMethods.NetServerEnum(null, 101, out buffer, -1,
ref readed, ref total,
NativeMethods.SV_101_TYPES.SV_TYPE_SQLSERVER, null, 0);
if (retVal == NativeMethods.ERROR_SUCCESS ||
retVal == NativeMethods.ERROR_MORE_DATA ||
readed > 0)
{
int handle = buffer.ToInt32();
for (int i = 0; i < readed; i++)
{
server = (NativeMethods.SERVER_INFO_101)Marshal.PtrToStructure(
new IntPtr(handle),
typeof(NativeMethods.SERVER_INFO_101));
handle += Marshal.SizeOf(server);
servers.Add(server.sv101_name);
}
}
NativeMethods.NetApiBufferFree(buffer);
}
while (readed < total && readed != 0);
return servers;
}
|
In this example if you call the
GetSqlServers method, this will return you the list of the SQL Servers on the network.
Not only for the SQL Servers also you can find any other group of computers by using
NetServerEnum method. The
SV_101_TYPES enumeration identifies the type of the computer, which you can search for. If you use another value of this enum in
GetSqlServers method, you will get the computers list of that kind.
For example; if you are searching for
Teminal Servers on the network, use
SV_TYPE_TERMINALSERVER while calling
NetServerEnum method in the
GetSqlServers method. This will returns you the Terminal Servers on the network.