How to list available database services (SQLSewrver or Oracle or MySQL or PostgreSQL, etc.) on a network in C #?

Question

How to list available database services (SQL Server or Oracle or MySQL or PostgreSQL, etc.) on a network using C #?

I also need to find their port numbers and service instance names.

For example:

class Server { #region DLL Inports [DllImport("odbc32.dll")] private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle); [DllImport("odbc32.dll")] private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength); [DllImport("odbc32.dll")] private static extern short SQLFreeHandle(short hType, IntPtr handle); [DllImport("odbc32.dll", CharSet = CharSet.Ansi)] private static extern short SQLBrowseConnect(IntPtr hconn, StringBuilder inString, short inStringLength, StringBuilder outString, short outStringLength, out short outLengthNeeded); #endregion #region Constants private const short SQL_HANDLE_ENV = 1; private const short SQL_HANDLE_DBC = 2; private const int SQL_ATTR_ODBC_VERSION = 200; private const int SQL_OV_ODBC3 = 3; private const short SQL_SUCCESS = 0; private const short SQL_NEED_DATA = 99; private const short DEFAULT_RESULT_SIZE = 1024; private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER"; #endregion #region static string[] GetServers() public static string[] GetNames() { string[] retval = null; string txt = string.Empty; IntPtr henv = IntPtr.Zero; IntPtr hconn = IntPtr.Zero; StringBuilder inString = new StringBuilder(SQL_DRIVER_STR); StringBuilder outString = new StringBuilder(DEFAULT_RESULT_SIZE); short inStringLength = (short)inString.Length; short lenNeeded = 0; try { if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv)) { if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0)) { if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn)) { if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString, DEFAULT_RESULT_SIZE, out lenNeeded)) { if (DEFAULT_RESULT_SIZE < lenNeeded) { outString.Capacity = lenNeeded; if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString, lenNeeded, out lenNeeded)) { throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver."); } } txt = outString.ToString(); int start = txt.IndexOf("{") + 1; int len = txt.IndexOf("}") - start; if ((start > 0) && (len > 0)) { txt = txt.Substring(start, len); } else { txt = string.Empty; } } } } } } catch (Exception ex) { //Throw away any error if we are not in debug mode //#if (DEBUG) //MessageBox.Show(ex.Message,"Acquire SQL Servier List Error"); //#endif txt = string.Empty; throw ex; } finally { if (hconn != IntPtr.Zero) { SQLFreeHandle(SQL_HANDLE_DBC, hconn); } if (henv != IntPtr.Zero) { SQLFreeHandle(SQL_HANDLE_ENV, hconn); } } if (txt.Length > 0) { retval = txt.Split(",".ToCharArray()); } return retval; } #endregion } 

This code only works for SqlServers and prior to WindowsXP.

It does not work for other DBMS and Win7 servers.

I need a canonical solution.

Answer 1

You can use the SqlDataSourceEnumerator class to do this. Keep in mind that this is MS SQL Server ...

 var results = SqlDataSourceEnumerator.Instance.GetDataSources(); foreach (var row in results.Rows) { Console.WriteLine("{0}\{1}", row["ServerName"], row["InstanceName"]); } 

See the link for more information.

Answer 2

Does it work on any RDBMS server?

Your answer will always be no here. Each RDBMS allows you to configure a custom port - MySQL can be on 1433 or 1434 or 99999. Each RDBMS reacts differently than other RDBMS and even sometimes from previous versions ... You will need to check each network port on each computer for each RDBMS (and each version of this DBMS, if they often change the lines of answers), and HOPE them they are configured with standard plaintext answers instead of being encrypted or something else. This is the core network - first you show a range of IP addresses, then you can try appscan on the active ports that you found to see how they respond to different requests, then you use this information to say that “these IP addresses have applications that appear to be databases on the following ports "- you still won’t get things like the name of the MS instance without going through the appropriate channels (as indicated above with code examples).

It’s best to understand where to start — perhaps http://www.nmap.org

Answer 3

Combine things said in previous answers / comments:

Contact your system administrator because this is not possible. You will need to consider each version of each (R) DBMS there (or at least the latest).

Answer 4

In addition to other answers:

I found Oracle.DataAccess.Client.OracleDataSourceEnumerator in Oracle.DataAccess dll ( Oracle.DataAccess.client )

By adding Oracle.managment.omo.dll and DataAccess.dll , you can manipulate Oracle objects such as sql; management.SMO in MS SQLSERVER

Answer 5

Resources: http://msdn.microsoft.com/en-us/library/ms162169.aspx http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

Would you like to look at SMO. This requires that each client has installed Sql and CLR management objects. Below is a snippet of code from one of my working applications that does just that.

 private void OnClicked_RefreshDataSources(object sender, EventArgs e) { Cursor = Cursors.WaitCursor; DataTable dt = SmoApplication.EnumAvailableSqlServers(false); uxDataSource.Items.Clear(); foreach (DataRow row in dt.Rows) { uxDataSource.Items.Add(row["Name"]); } if (dt.Rows.Count > 0) { uxDataSource.SelectedIndex = 0; } Cursor = Cursors.Default; } private void OnSelectedIndexChanged_PopulateDatabases(object sender, EventArgs e) { ConnectionString.DataSource = uxDataSource.SelectedItem.ToString(); Server server = new Server(uxDataSource.SelectedItem.ToString()); server.ConnectionContext.LoginSecure = false; server.ConnectionContext.Login = Program.DesktopService.AccountName; uxInitialCatalog.Items.Clear(); try { foreach (Database db in server.Databases) { uxInitialCatalog.Items.Add(db.Name); } if (server.Databases.Count > 0) { uxInitialCatalog.SelectedIndex = 0; } } catch { MessageBox.Show("You do not have access to this server.", "Sql Connection", MessageBoxButtons.OK, MessageBoxIcon.Warning); uxInitialCatalog.Items.Clear(); } }