c# - SQL Server CLR Int64 到 SQLInt64 指定的转换无效

Question

我正在尝试编写一个允许我在 SQL Server 上运行 WMI 查询的 CLR。​​

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Management;

public class WMIQuery
{
    [SqlFunction(FillRowMethodName = "FillRow")]
    public static IEnumerable InitMethod()
    {      
        ManagementScope scope = new ManagementScope();    
        scope = new ManagementScope(@"\\localhost\root\CIMV2");
        scope.Connect();    
        SelectQuery query = new SelectQuery("SELECT Name, Capacity, Freespace FROM Win32_Volume WHERE DriveType=3");
        ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, query);

        ManagementObjectCollection retObjectCollection = searcher.Get ( );    
        return retObjectCollection;
    }

    public static void FillRow(Object obj, out SqlString Name, out SqlInt64 Capacity, out SqlInt64 Freespace)
    {
        ManagementObject m = (ManagementObject)obj;           

        Name = new SqlString((string)m["name"]);
        Capacity = new SqlInt64((Int64)m["Capacity"]);
        Freespace = new SqlInt64((Int64)m["Freespace"]);
    }  
 }

运行该表值函数时出现以下错误:

An error occurred while getting new row from user defined Table Valued Function : System.InvalidCastException: Specified cast is not valid. System.InvalidCastException: at WMIQuery.FillRow(Object obj, SqlString& Name, SqlInt64& Capacity, SqlInt64& Freespace) .

我已经发现问题出在转换上:

Capacity = new SqlInt64((Int64)m["Capacity"]);
Freespace = new SqlInt64((Int64)m["Freespace"]);

我希望有人知道如何解决上述问题？

我测试这个 CLR 的代码是:

CREATE FUNCTION [dbo].[WMIQuery]()
RETURNS  TABLE (
    [Name] [nvarchar](4000) NULL,
    [Capacity] [bigint] NULL,
    [Freespace] [bigint] NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [MyFirstAssembly].[WMIQuery].[InitMethod]
GO


select * from WMIQuery()

Answer 1

您应该使用并检查该行和列是否具有可以转换为 Int64 的正确值。试试如何检查这个 Here .

转换前请做以下事情

bool success = Int64.TryParse(Convert.ToString(m["Capacity"]), out long number);
if (success)
{
   Capacity = new SqlInt64((Int64)m["Capacity"]);
}
else
{
   Capacity = 0;
}