使用C＃获取本地SQL Server实例 [英] Get local SQL server instances using C#

问题描述

我尝试使用以下方法

i tried using following method

DataTable dataTable = SmoApplication.EnumAvailableSqlServers(true);

            foreach (DataRow dataRow in dataTable.Rows)
            {
                Console.WriteLine(dataRow["Instance"] as string);
            }

数据表中没有实例名称，但只有系统名称。

there are no instance names in datatable but there are only system names.

DataTable dt = SqlDataSourceEnumerator.Instance.GetDataSources();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                foreach (System.Data.DataColumn col in dt.Columns)
                {
                   Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
                }
            }

与上面相同，

same as above,

there are no instance names in datatable but there are only system names.

i尝试使用托管计算机，但我在win-8中抛出异常



我的要求是检查系统中是否已经存在SqlExpress的实例，如果不存在，则安装带有Sqlexpress实例的sql server。



我尝试了什么：

i tried using Managed computer also but i throws exception in win-8

My requirement is check if there is an instnace with SqlExpress already in system, if its not there then install sql server with Sqlexpress Instance.

What I have tried:

i tried using Managed computer also but i throws exception in win-8

My requirement is to check if there is an instance with name SqlExpress already in system, if its not there then install sql server with Sqlexpress Instance.

推荐答案

EnumAvailableSqlServers 和 SqlDataSourceEnumerator 只会在SQL Server浏览器中找到命名实例服务正在运行。



Manag edComputer 只能找到在SQL Server Management Studio中注册的服务器。



如果你只是在当前计算机上寻找服务器，你可以从注册表中读取信息。密钥 HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft / Microsoft Microsoft SQL Server 具有名为 InstalledInstances 的值，其中包含本地计算机上安装的SQL Server实例：

EnumAvailableSqlServers and SqlDataSourceEnumerator will only find named instances if the SQL Server Browser services is running.

ManagedComputer will only find servers registered in SQL Server Management Studio.

If you're just looking for servers on the current computer, you can read the information from the registry. The key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server has a value called InstalledInstances which contains the name of the SQL Server instances installed on the local computer:

public static class SqlHelper
{
    public static IEnumerable<string> ListLocalSqlInstances()
    {
        if (Environment.Is64BitOperatingSystem)
        {
            using (var hive = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry64))
            {
                foreach (string item in ListLocalSqlInstances(hive))
                {
                    yield return item;
                }
            }
            
            using (var hive = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32))
            {
                foreach (string item in ListLocalSqlInstances(hive))
                {
                    yield return item;
                }
            }
        }
        else
        {
            foreach (string item in ListLocalSqlInstances(Registry.LocalMachine))
            {
                yield return item;
            }
        }
    }
    
    private static IEnumerable<string> ListLocalSqlInstances(RegistryKey hive)
    {
        const string keyName = @"Software\Microsoft\Microsoft SQL Server";
        const string valueName = "InstalledInstances";
        const string defaultName = "MSSQLSERVER";
        
        using (var key = hive.OpenSubKey(keyName, false))
        {
            if (key == null) return Enumerable.Empty<string>();
            
            var value = key.GetValue(valueName) as string[];
            if (value == null) return Enumerable.Empty<string>();
            
            for (int index = 0; index < value.Length; index++)
            {
                if (string.Equals(value[index], defaultName, StringComparison.OrdinalIgnoreCase))
                {
                    value[index] = ".";
                }
                else
                {
                    value[index] = @".\" + value[index];
                }
            }
            
            return value;
        }
    }
}

这篇关于使用C＃获取本地SQL Server实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！