locked
CLR C#.NET User defined function RRS feed

  • Question

  • User-1597471785 posted

    Hi,

    I'm new to CLR and was playing around with Visual Studio and SQL server.  

    My questions

    1. If I have multiple tables and CLR created in C#.NET with a function called SPLICE(string s) and return the splice string.  What is the best way to read the tables in the database in C#.NET and manipulate the data?
    2. Is there a way to read database table from CLR c#.net codes and store in a variables? How?

    Sample CLR:

     [SqlFunction()]
        public static SqlString SPSPLICE(string s)
            //connect to db and retrieve data from different db tables? the old way or is there a db reference to configure and utility to read the tables?
    
            return splicestring;
        }

    Wednesday, June 10, 2015 12:17 AM

Answers

  • User1534684113 posted

    CLR C#.NET User defined function

    Thanks for your post.
    As per this case, you can refer to sample code like below:

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read,
    IsDeterministic=true,SystemDataAccess=SystemDataAccessKind.None,IsPrecise=true)]
    public static SqlInt32 CLRTripFromTbl1(SqlInt32 Value)
    {
        SqlInt32 Result;
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT Triple FROM dbo.Triples WHERE Value = @Value;", conn);
            cmd.Parameters.Add(new SqlParameter("Value", Value));
            Result = (SqlInt32)((int)cmd.ExecuteScalar());
        }
        return Result;
    }

    For more information, Click here to refer about T-SQL User-Defined Functions with samples.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 11, 2015 9:28 PM

All replies

  • User1534684113 posted

    CLR C#.NET User defined function

    Thanks for your post. Create a SQL user-defined function by adding a User-Defined Function to a SQL Server project. After successful deployment, the user-defined function can be called and executed.

    For more information, Click here to refer about How to: Create and Run a CLR SQL Server User-Defined Function.

    Wednesday, June 10, 2015 10:08 PM
  • User-1597471785 posted

    Thanks but please read my questions.

    Wednesday, June 10, 2015 11:34 PM
  • User1534684113 posted

    CLR C#.NET User defined function

    Thanks for your post.
    As per this case, you can refer to sample code like below:

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read,
    IsDeterministic=true,SystemDataAccess=SystemDataAccessKind.None,IsPrecise=true)]
    public static SqlInt32 CLRTripFromTbl1(SqlInt32 Value)
    {
        SqlInt32 Result;
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT Triple FROM dbo.Triples WHERE Value = @Value;", conn);
            cmd.Parameters.Add(new SqlParameter("Value", Value));
            Result = (SqlInt32)((int)cmd.ExecuteScalar());
        }
        return Result;
    }

    For more information, Click here to refer about T-SQL User-Defined Functions with samples.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 11, 2015 9:28 PM