how can i use System.Data.Sql.SqlDataSourceEnumerator class to know available sql datasources...?

Question

how can i use System.Data.Sql.SqlDataSourceEnumerator class to know about available sql datasources...? because while i am creating connection to sql server if sql server is not ready we will get exception… so first i want to know is sql server is ready to accept request or not… how to know it….

Answer 1

Currently SqlDataSourceEnumerator isn't available in .NetCore or .Net5 either and whilst not a direct replacement for SqlDataSourceEnumerator you could try a Udp solution.

This repo is targetted at .Net5, but the code should work just fine on .NetCore flavours.

https://github.com/mrsquish/SqlBrowserClient

Answer 2

So, according to the following references:

http://social.msdn.microsoft.com/forums/en-US/sqlsmoanddmo/thread/49ba019f-e8b5-457c-80ea-fac5febb9d3d/

http://connect.microsoft.com/SQLServer/feedback/details/146323/enumavailablesqlservers-or-sqldatasourceenumerator-incorrect-list-of-available-databases

http://blogs.msdn.com/b/sushilc/archive/2004/10/14/242395.aspx

http://sqlblogcasts.com/blogs/jonsayce/archive/2008/02/10/programatically-listing-sql-servers.aspx

GetDataSources() is not a perfect method, meaning, it may not list all the available data sources on first try. In fact, I found that it also does not list all of your local sources.

For my purposes, I had some time between when the program started and when I needed to get the list of available sources, both on the network AND local. So, I put the code in a thread that goes on forever collecting all the sources. Here it is below. If you take out the while loop, you can call it manually as many times as you'd like.

        private List<string> sqlInstances = new List<string>();
        private void collectInstances()
        {
            while (true)
            {
                System.Data.Sql.SqlDataSourceEnumerator instance = System.Data.Sql.SqlDataSourceEnumerator.Instance;
                System.Data.DataTable dataTable = instance.GetDataSources();
                foreach (DataRow row in dataTable.Rows)
                {
                    string instanceName = String.Format(@"{0}\{1}", row["ServerName"].ToString(), row["InstanceName"].ToString());

                    //Do not add the local instance, we will add it in the next section. Otherwise, duplicated!
                    if (!sqlInstances.Contains(instanceName) && !instanceName.Contains(Environment.MachineName))
                    {
                        sqlInstances.Add(instanceName);
                    }
                }

                /*
                 * For some reason, GetDataSources() does not get local instances. So using code from here to get them
                 * http://stackoverflow.com/questions/6824188/sqldatasourceenumerator-instance-getdatasources-does-not-locate-local-sql-serv
                 */
                List<string> lclInstances = GetLocalSqlServerInstanceNames();
                foreach (var lclInstance in lclInstances)
                {
                    string instanceName = String.Format(@"{0}\{1}", Environment.MachineName, lclInstance);
                    if (!sqlInstances.Contains(instanceName)) sqlInstances.Add(instanceName);
                }
                sqlInstances.Sort();
            }
        }

        //Got code from: http://stackoverflow.com/questions/6824188/sqldatasourceenumerator-instance-getdatasources-does-not-locate-local-sql-serv
        /// <summary>
        ///  get local sql server instance names from registry, search both WOW64 and WOW3264 hives
        /// </summary>
        /// <returns>a list of local sql server instance names</returns>
        public static List<string> GetLocalSqlServerInstanceNames()
        {
            RegistryValueDataReader registryValueDataReader = new RegistryValueDataReader();

            string[] instances64Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow64,
                                                                                    Registry.LocalMachine,
                                                                                    @"SOFTWARE\Microsoft\Microsoft SQL Server",
                                                                                    "InstalledInstances");

            string[] instances32Bit = registryValueDataReader.ReadRegistryValueData(RegistryHive.Wow6432,
                                                                                    Registry.LocalMachine,
                                                                                    @"SOFTWARE\Microsoft\Microsoft SQL Server",
                                                                                    "InstalledInstances");

            //FormatLocalSqlInstanceNames(ref instances64Bit);
            //FormatLocalSqlInstanceNames(ref instances32Bit);

            List<string> localInstanceNames = new List<string>(instances64Bit);
            foreach (var item in instances32Bit)
            {
                if (!localInstanceNames.Contains(item)) localInstanceNames.Add(item);
            }

            //localInstanceNames = localInstanceNames.Union(instances32Bit).ToList();

            return localInstanceNames;
        }

Answer 3

GetDataSources() may help you, have you tried it?

SqlDataSourceEnumerator.GetDataSources Method