Loop through a table in a CLR UDF C#

Question

I need to write a CLR UDF that reads data from a table and loops through it, but most important storing data in double arrays (the table has only double values), afterwards I will use a math library to compute some things...

I have been searching but I found examples that connect to database, I want to make a .dll with C# code, and call it from a stored proc.

An example I have found is this, but how would be the steps to make a dll instead connecting to db, And store double values in array?

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void CLR_StoredProcedure3()
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Context Connection=true";

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;

    }  
}

Does this question replace the one you've posted a bit earlier today? — stakx - no longer contributing, Aug 10 '11 at 21:55

score 2 · Accepted Answer · answered Aug 10 '11 at 22:42

2

The most efficient way I think is to do it in two steps:

int count;
using (SqlCommand cmdCount = conn.CreateCommand())
{
    cmdCount.CommandText = "SELECT COUNT(*) FROM [MyTable]";
    count = (int)cmdCount.ExecuteScalar();
}

// knowing the number of rows we can efficiently allocate the array
double[] values = new double[count];

using (SqlCommand cmdLoad = conn.CreateCommand())
{
    cmdLoad.CommandText = "SELECT * FROM [MyTable]";

    using(SqlDataReader reader = cmdLoad.ExecuteReader())
    {
        int col = reader.GetOrdinal("MyColumnName");
        for(int i = 0; i < count && reader.Read(); i++)
        {
            values[i] = reader.GetDouble(col);
        }
    }
}

// do more processing on values[] here

answered Aug 10 '11 at 22:42

Serguei

2,910
3
24
34

cool solution, only one question, is there a way to avoid connecting using that `conn.CreateCommand` or is it mandatory to have that, if so, what would be the code to that? `SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Context Connection=true"; SqlCommand cmd = new SqlCommand(); cmd.Connection = conn;` would be enough? – edgarmtze Aug 11 '11 at 04:00
Yeah you can just instantiate a `SqlCommand` and set the `Connection` property too. I prefer using the factory method (CreateCommand) because it's only 1 line and *potentially* some other implementation of IDbConnection may need to do some additional initialization of command objects (which is not the case in the context of SQL CLR code so you're safe). – Serguei Aug 11 '11 at 18:50

Loop through a table in a CLR UDF C#

1 Answers1