locked
Using System.Numerics BigInteger inside a SQLCLR function. RRS feed

  • Question

  • I'm using VS 2010.  I created a Visual C# SQL CLR Database project and created a SqlFunction.

    I tried to use System.Numerics and I get an error

    The type or namespace name 'Numerics' does not exist in the namespace 'System' (are you missing an assembly reference?)

    But when I try to add the reference I only get a list of SQL Server references which doesn't include System.Numerics.

    Is there any way I can use System.Numerics.BigInteger in my SQLCLR function?
    The target server is SQL 2012.

    Thanks.


    Thursday, February 20, 2014 8:39 PM

Answers

  • Use Visual Studio 2013, or SSDT for Visual Studio 2010.  You must target .NET 4.0 to use System.Numerics, so you need newer tools.

    This works for me on VS 2013, which has SSDT built-in.  Just add a reference to System.Numerics and go.

    //------------------------------------------------------------------------------
    // <copyright file="CSSqlFunction.cs" company="Microsoft">
    //     Copyright (c) Microsoft Corporation.  All rights reserved.
    // </copyright>
    //------------------------------------------------------------------------------
    using System.Numerics;
    
    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static int Combinations( int n, int p)
        {
          return (int)(Factorial(n) / ( Factorial(n-p) * Factorial(p) ));
        }
    
        [Microsoft.SqlServer.Server.SqlFunction]
        public static int Permutations( int n, int p)
        {
          return (int)( Factorial(n) / Factorial(n-p) );
        }
    
        static BigInteger Factorial(int n)
        {
          BigInteger nFactorial = n;
          for (int i = 2; i < n; i++)
          {
            nFactorial = BigInteger.Multiply(nFactorial, i);
          }
          return nFactorial;
    
        }
    }

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, February 20, 2014 8:59 PM

All replies

  • Use Visual Studio 2013, or SSDT for Visual Studio 2010.  You must target .NET 4.0 to use System.Numerics, so you need newer tools.

    This works for me on VS 2013, which has SSDT built-in.  Just add a reference to System.Numerics and go.

    //------------------------------------------------------------------------------
    // <copyright file="CSSqlFunction.cs" company="Microsoft">
    //     Copyright (c) Microsoft Corporation.  All rights reserved.
    // </copyright>
    //------------------------------------------------------------------------------
    using System.Numerics;
    
    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static int Combinations( int n, int p)
        {
          return (int)(Factorial(n) / ( Factorial(n-p) * Factorial(p) ));
        }
    
        [Microsoft.SqlServer.Server.SqlFunction]
        public static int Permutations( int n, int p)
        {
          return (int)( Factorial(n) / Factorial(n-p) );
        }
    
        static BigInteger Factorial(int n)
        {
          BigInteger nFactorial = n;
          for (int i = 2; i < n; i++)
          {
            nFactorial = BigInteger.Multiply(nFactorial, i);
          }
          return nFactorial;
    
        }
    }

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, February 20, 2014 8:59 PM
  • Thanks, I discovered it works for me too.  I just edited the .csproj file like this


      <ItemGroup>
        <Reference Include="System" />
        <Reference Include="System.Data" />
        <Reference Include="System.Numerics" />  <<<<-------------------
        <Reference Include="System.XML" />
      </ItemGroup>

    Somehow I couldn't do this directly in VS.

    Gudmundur Karlsson

    Thursday, February 20, 2014 9:11 PM