SQL Server CLR Int64 to SQLInt64 Specified cast is not valid

Question

I am trying to write a CLR that allows me to run a WMI Query on a SQL Server.

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"]);
    }  
 }

When running that table valued function i get the following error:

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) .

I already found out that the problem is the the conversion:

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

I hope that someone has an idea how to solve the above problem?

My code to test this CLR is:

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

You should use and check whether that row and column has proper values which can be converted to the Int64 or not. Try how to check this Here.

Please do the following before casting

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