代码之家  ›  专栏  ›  技术社区  ›  Jeff

在x64上使用WMI创建SQL Server别名

  •  1
  • Jeff  · 技术社区  · 14 年前

    我有一个小应用程序,可以自动为一些服务器创建必要的SQL Server别名条目。大部分代码如下所示:

            private static void SetAlias(string aliasName, string server, string protocol, int? port)
            {
                var scope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement10");
                try
                {
                    scope.Connect();
    
                }
                catch
                {
                    scope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
                }
                var clientAlias = new ManagementClass(scope, new ManagementPath("SqlServerAlias"), null);
                clientAlias.Get();
    
                foreach (ManagementObject existingAlias in clientAlias.GetInstances())
                {
                    existingAlias.Get();
                    if (String.Equals((String)existingAlias.GetPropertyValue("AliasName"), aliasName))
                    {
                        UpdateAlias(existingAlias, aliasName, server, protocol, port);
                        return;
                    }
                }
    
                // create new
                ManagementObject newAlias = clientAlias.CreateInstance();
                UpdateAlias(newAlias, aliasName, server, protocol, port);
                newAlias.Put();
            }
    
            private static void UpdateAlias(ManagementObject alias, string aliasName, string server, string protocol, int? port)
            {
                alias.SetPropertyValue("AliasName", aliasName);
                alias.SetPropertyValue("ServerName", server);
                alias.SetPropertyValue("ProtocolName", protocol);
                alias.SetPropertyValue("ConnectionString", port != null ? port.ToString() : string.Empty);
            }
    

    这将正确地在32位操作系统上创建所需的条目,但是在x64操作系统上,我需要将别名也添加到64位SQL Server客户端配置中。

    你知道怎么做吗?

    谢谢。

    2 回复  |  直到 14 年前
        1
  •  3
  •   James Manning    14 年前

    我将保留注册表答案,因为它是可行的,但是您可以使用ConnectionOptions上的上下文来指定arch(int、32或64)

    从64位访问这两个文件的示例:

        static void Main(string[] args)
        {
            var options = new ConnectionOptions();
    
            if (Environment.Is64BitOperatingSystem && Environment.Is64BitProcess == false)
            {
                Console.WriteLine("Please build as AnyCPU or x64");
                return;
            }
    
            // default behavior, should be 64-bit WMI provider
            Console.WriteLine("Print 64-bit aliases");
            PrintAliases(options);
    
            // specify the 32-bit arch
            Console.WriteLine("Print 32-bit aliases");
            options.Context.Add("__ProviderArchitecture", 32);
            PrintAliases(options);
        }
    
        private static void PrintAliases(ConnectionOptions options)
        {
            var scope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement10", options);
            try
            {
                scope.Connect();
            }
            catch
            {
                scope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
            }
            var clientAlias = new ManagementClass(scope, new ManagementPath("SqlServerAlias"), null);
            clientAlias.Get();
    
            foreach (ManagementObject existingAlias in clientAlias.GetInstances())
            {
                existingAlias.Get();
                var propertiesToRead = new[] { "AliasName", "ServerName", "ProtocolName", "ConnectionString" };
                foreach (var propertyToRead  in propertiesToRead)
                {
                    Console.WriteLine("Property {0} = {1}", propertyToRead, existingAlias.GetPropertyValue(propertyToRead));
                }
            }
        }
    

    一个从32位同时访问这两个的示例(注意:当然,不管进程位是多少,都可以将arch强制为32位和64位)

    class Program
    {
        static void Main(string[] args)
        {
            var options = new ConnectionOptions();
    
            if (Environment.Is64BitProcess)
            {
                Console.WriteLine("Please run this sample as 32-bit");
                return;
            }
    
            // default behavior, should be 32-bit WMI provider since we build as x86
            Console.WriteLine("Print 32-bit aliases");
            PrintAliases(options);
    
            // also prints 32-bit aliases
            options.Context.Add("__ProviderArchitecture", 32);
            PrintAliases(options);
    
            // specify the 64-bit arch
            if (Environment.Is64BitOperatingSystem)
            {
                Console.WriteLine("Print 64-bit aliases");
                options.Context.Add("__ProviderArchitecture", 64);
                PrintAliases(options);
            }
        }
    
        private static void PrintAliases(ConnectionOptions options)
        {
            var scope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement10", options);
            try
            {
                scope.Connect();
            }
            catch
            {
                scope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
            }
            var clientAlias = new ManagementClass(scope, new ManagementPath("SqlServerAlias"), null);
            clientAlias.Get();
    
            foreach (ManagementObject existingAlias in clientAlias.GetInstances())
            {
                existingAlias.Get();
                var propertiesToRead = new[] { "AliasName", "ServerName", "ProtocolName", "ConnectionString" };
                foreach (var propertyToRead  in propertiesToRead)
                {
                    Console.WriteLine("Property {0} = {1}", propertyToRead, existingAlias.GetPropertyValue(propertyToRead));
                }
            }
        }
    
        2
  •  1
  •   James Manning    14 年前

    当我上一次研究这个问题时,客户机别名只是保存在注册表(HKEY\ U LOCAL\ U MACHINE\SOFTWARE\Microsoft\MSSQLServer\client\ConnectTo)中,所以最简单的方法是在x64上运行时写入WoW(HKEY\ U LOCAL\ U MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\client\ConnectTo)和“正常”位置。请注意,如果作为32位进程运行,则在编写64位版本时需要p/invoke或(如果在.NET4上)指定64位视图。