C# 调用 PowerShell

最新推荐文章于 2022-05-06 22:30:19 发布

吉普赛的歌

最新推荐文章于 2022-05-06 22:30:19 发布

阅读量5.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： # SqlClr

本文链接：https://blog.csdn.net/yenange/article/details/51271283

SqlClr 专栏收录该内容

6 篇文章

订阅专栏

本文介绍如何使用C#语言调用PowerShell执行脚本并获取结果，提供了具体实现代码，包括创建运行空间、执行脚本等步骤，并尝试将其封装为SQL CLR函数时遇到的问题及异常。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

C# 调用 PowerShell ，亲测可用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Management.Automation.Runspaces;
using System.Management.Automation;
using System.Collections.ObjectModel;

namespace ConsoleApplication30
{
    //注：要添加引用System.Management.Automation.dll； 
    //    此DLL在C:\Program Files (x86)\Reference Assemblies\Microsoft\WindowsPowerShell\v1.0
    //本人 win7 升级 win10 不知道为什么没有， 直接在 win2008 下复制了一个过来放 debug 下面。
    class Program
    {
        static void Main(string[] args)
        {
            string script = @"$s=quser
$s";
            string r = RunScript(script);
            Console.WriteLine(r);
            Console.WriteLine("End");
            Console.Read();
        }
        
        private static string RunScript(string scriptText)
        {
            // create Powershell runspace
            Runspace runspace = RunspaceFactory.CreateRunspace();
            // open it
            runspace.Open();
            // create a pipeline and feed it the script text
            Pipeline pipeline = runspace.CreatePipeline();
            pipeline.Commands.AddScript(scriptText);
            pipeline.Commands.Add("Out-String");

            // execute the script
            Collection<PSObject> results = pipeline.Invoke();
            // close the runspace
            runspace.Close();

            // convert the script result into a single string
            StringBuilder stringBuilder = new StringBuilder();
            foreach (PSObject obj in results)
            {
                stringBuilder.AppendLine(obj.ToString());
            }
            return stringBuilder.ToString();
        }

        public void RunScript(List<string> scripts)
        {
            try
            {
                Runspace runspace = RunspaceFactory.CreateRunspace();
                runspace.Open();
                Pipeline pipeline = runspace.CreatePipeline();
                foreach (var scr in scripts)
                {
                    pipeline.Commands.AddScript(scr);
                }
                //返回结果   
                var results = pipeline.Invoke();
                runspace.Close();

            }
            catch (Exception e)
            {
                Console.WriteLine(DateTime.Now.ToString() + "日志记录:执行ps命令异常：" + e.Message);
            }
        }
    }
}

将其做成 sqlclr 有问题：

using System;
using System.Collections.Generic;
using System.Text;
using System.Diagnostics;
using System.Management;
using SqlClrDBA.Model;

namespace SqlClrDBA.WindowsLog
{
    public class Proccess
    {
        public static List<WinLog> ReadWindowsLog2(string log, bool onlyError, string sources, string contains, DateTime beginTime, DateTime endTime)
        {
            List<WinLog> logList = new List<WinLog>();
            StringBuilder sb = new StringBuilder(string.Format("SELECT * FROM Win32_NTLogEvent WHERE Logfile = '{0}' ", log));
            if (onlyError) 
            {
                sb.Append(" and EventType=1 ");
            }
            if (!string.IsNullOrEmpty(sources))
            {
                string[] sourceArr = sources.Split(',');
                int i = 0;
                sb.Append( " and ( " );
                foreach (string source in sourceArr) 
                {
                    sb.AppendFormat(" SourceName='{0}' {1} ", source, i==sourceArr.Length-1 ? string.Empty : "or" );
                }
                sb.Append( " ) " );
            }
            if (beginTime.Year > 1900)
            {
                sb.AppendFormat(" TimeGenerated>'{0:yyyy-MM-dd HH:mm:ss}' ", beginTime);
            }
            if (endTime.Year > 1900)
            {
                sb.AppendFormat(" TimeGenerated<='{0:yyyy-MM-dd HH:mm:ss}' ", endTime);
            }

            ConnectionOptions options = new ConnectionOptions();
            options.Impersonation = ImpersonationLevel.Impersonate;
            options.EnablePrivileges = true;

            ManagementScope scope = new ManagementScope("\\\\127.0.0.1\\root\\cimv2", options);
            scope.Connect();
            ObjectQuery query = new ObjectQuery(sb.ToString());
            ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);
            ManagementObjectCollection queryCollection = searcher.Get();
            foreach (ManagementObject m in queryCollection)
            {
                WinLog winLog = new WinLog();
                winLog.Type = Convert.ToString(m["Type"]);
                winLog.TimeGenerated = Convert.ToDateTime(m["TimeGenerated"]);
                winLog.ComputerName = Convert.ToString(m["ComputerName"]);
                winLog.SourceName = Convert.ToString(m["SourceName"]);
                winLog.EventIdentifier = Convert.ToInt32(m["EventIdentifier"]);
                winLog.User = Convert.ToString(m["User"]);
                winLog.Message = Convert.ToString(m["Message"]);
                logList.Add(winLog);
            }
            return logList;
        }

        public static List<EventLogEntry> ReadWindowsLog(string log, bool onlyError, string sources, string contains, DateTime beginTime, DateTime endTime)
        {
            string[] sourceArr = sources.Split(',');
            EventLog myLog = new EventLog(log);
            EventLogEntryCollection entCol = myLog.Entries;
            List<EventLogEntry> oldList = new List<EventLogEntry>() { };
            foreach (EventLogEntry item in entCol)
            {
                oldList.Add(item);
            }
            List<EventLogEntry> list = oldList.FindAll(
                p =>
                p.TimeGenerated > beginTime
                &&
                (endTime.Year <= 1900 || p.TimeGenerated <= endTime)
                &&
                (!onlyError || p.EntryType == EventLogEntryType.Error)
                &&
                (sources.Contains(p.Source))
                &&
                (string.IsNullOrEmpty(contains) || p.Message.Contains(contains))
            );
            return list;
        }
    }
}

调用：

        /// <summary>
        /// 执行字符串，输出结果（主要供select）
        /// </summary>
        /// <param name="str"></param>
        [SqlProcedure]
        [SecurityPermission(SecurityAction.Assert)]
        public static void QuerySQL(string str)
        {
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlCommand cmd = new SqlCommand(str, conn);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                SqlContext.Pipe.Send(reader);
            }
        }

        /// <summary>
        /// 执行PowerShell
        /// </summary>
        /// <param name="script"></param>
        [SqlProcedure]
        [SecurityPermission(SecurityAction.Assert)]
        public static void Proc_DBA_PowerShell(string script)
        {
         
            string r = SqlClrDBA.PowerShell.Proccess.RunScript(script);
            r = string.Format("select '{0}' as result", r.Replace("'","''"));
            QuerySQL(r);
        }

异常：

消息 6522，级别 16，状态 1，过程 Proc_DBA_PowerShell，第 0 行
在执行用户定义例程或聚合“Proc_DBA_PowerShell”期间出现 .NET Framework 错误:
System.ArgumentException: 路径的形式不合法。
System.ArgumentException:
在 System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPathLength, Boolean expandShortPaths)
在 System.IO.Path.GetFullPath(String path)
在 System.Diagnostics.FileVersionInfo.GetFullPathWithAssert(String fileName)
在 System.Diagnostics.FileVersionInfo.GetVersionInfo(String fileName)
在 System.Management.Automation.PSVersionInfo.GetPSVersionTable()
在 System.Management.Automation.Runspaces.RunspaceFactory.CreateRunspace()
在 SqlClrDBA.PowerShell.Proccess.RunScript(String scriptText)
在 SqlClrDBA.SqlCLR.Proc_DBA_PowerShell(String script, String password)
。