Moving Data To And From SQL Server 2005, With Manipulation In C#

Hi

I am wishing to
* take a table of data into a C# CLR procedure and store it in an array
* take a second table of data into this procedure row by row, and return a row (into a third table) for each row (and this returned row is based on the data in the array and the data in this row).

I am new to CLR programming, and pulling my hair out at the moment. I€™m sure that what I€™m trying to do is simple, but I am not making any progress with the on-line help and error messages L. I have now described what I am trying to do in more detail, firstly in English, and then in a T-SQL implementation that works (for this simple example). I€™m looking for the C# CLR code to be returned €“ containing preferably two parts:
* the C# code and
* the CLR code to €˜make it live€™
Since I am not sure where my coding is going wrong. (I think it should be possible to read in the one table, and then loop through thte second table, calculating and returning the necessary output as you do the calculations...

Problem in English
Consider a situation where there are three tables: DATATABLE, PARAMETERTABLE and RESULTSTABLE.

For each row in PARAMETERTABLE, I will calculate a row for RESULTSTABLE based on calculations involving all the entries form DATATABLE.

I am wishing to do this in a C# CLR for performance reasons, and because the functions I will be using will be significantly more complex, and recursively built up.

Consider the following table structures
DATATABLE €“ [i int, datavalue real]
[1,12.5]
[2.10]
[3.14]
[4,17.5]
(where numberofrows is 4)
PARAMETERTABLE [parametera real, paramaterb real]
[10,2]
[11.7,1.1]
RESULTSTABLE [resultvalue real]
[20.5]
[18.26]

The values of the results table are calculated as the
sum (for i = 1 to numberofrows)
of
(Parameter1+i*parameterb-datavalue)^2
Which leads to the values shown.

T-SQL Implementation

-- Set up database and tables to use in example

USE master
GO

CREATE DATABASE QuestionDatabase
GO

sp_configure 'clr enabled', 1
GO

USE QuestionDatabase
GO

RECONFIGURE
GO

CREATE TABLE DataTable (i INT NOT NULL, DataValue REAL NOT NULL)
GO

CREATE TABLE ParameterTable (ParameterNumber INT NOT NULL, ParameterA REAL NOT NULL, ParameterB REAL NOT NULL)
GO

CREATE TABLE ResultsTable (ParameterNumber INT NOT NULL, ResultsValue REAL NOT NULL)
GO

--Initialise the Tables

INSERT INTO DataTable (i, DataValue) VALUES (1,12.5)
INSERT INTO DataTable (i, DataValue) VALUES (2,10)
INSERT INTO DataTable (i, DataValue) VALUES (3,14)
INSERT INTO DataTable (i, DataValue) VALUES (4,17.5)

INSERT INTO ParameterTable (ParameterNumber, ParameterA, ParameterB) VALUES (1, 10, 2)
INSERT INTO ParameterTable (ParameterNumber, ParameterA, ParameterB) VALUES (2, 11.7, 1.1)

-- The TSQL to be rewritten in C#, to produce the Output as hoped

INSERT INTO ResultsTable (ParameterNumber, ResultsValue)
SELECT ParameterNumber, SUM((parametera+i*parameterb-datavalue)*(parametera+i*parameterb-datavalue)) AS b
FROM DataTable
CROSS JOIN
ParameterTable
GROUP BY ParameterNumber

-- Output as hoped

SELECT * FROM DataTable
SELECT * FROM ParameterTable
SELECT * FROM ResultsTable

-- which produced

1 12.5
2 10
3 14
4 17.5

1 10 2
2 11.7 1.1

1 20.5
2 18.26

-- but I hope to do the same with something like:

CREATE ASSEMBLY *** FROM 'C:***.dll'

CREATE PROCEDURE GenerateResultsInCLR(@i int, @r1 real, @r2 real)
RETURNS TABLE (i int, r real)
EXTERNAL NAME ***.***.***

EXEC GenerateResultsInCLR

This is a simple example, that can be easily written in T-SQL. I am looking to develop things that are recursive in nature, which makes them unsuited to T-SQL, unless one is using cursors, but this becomes very slow when the parameter table has 1m records, and the data table 100k records. This is why the datatable must be read in once, and manipulated many times, and the manipulation will need to be in the form of a loop.

Thanks very much for your help.

Go well

Greg