Generating resultset from CLR

Hello,

i am looking for a solution to convert a list or any kind of DataSet into an dynamic result for stored procedure in SQLAnywhere 12.

Until today I even didn't managed it to make the sample examples work.

Best Regards

asked 11 May '12, 06:55

DavidSteiman
61●2●2●7
accept rate: 0%

edited 15 Mar '13, 19:52

Mark Culp
24.9k●10●141●297

You mean the resultset for an external stored procedure which is written in .net?

(11 May '12, 10:58) Martin

Replies hidden

exactly

the aim is to generate any kind of data in c#, then pass it to the database as an result.

I found only examples of ResulSet, which were fetched inside of c#.....like i call procedure "A" and in the implementation of "A" stands an SQL Query. Beside the fact, that I didn't managed it to make it work, the samples aren't that what I'm looking for.

I believe, there must be a standard way of generating results in C#.....hope someone could help me.

(11 May '12, 11:16) DavidSteiman

flat view

2 Answers:

active answers oldest answers newest answers popular answers

hello,

we managed it to find the answer to this question.

Step: Making the the class

public class Rtest {

public static void GetRows(IDataReader[] readers)
{

    DataTable table = new DataTable("Table");

    table.Columns.Add("c1");
    table.Columns.Add("c2");

    //Add rows

    for(int i = 0; i<=100;i++)
    {
        table.Rows.Add(i*2,i*3);
    }

    DataSet set = new DataSet("Result");
    set.Tables.Add(table);

    readers[0] = set.CreateDataReader();
}

}

Step: Create the procedure:

CREATE PROCEDURE CLR_RESULT() RESULT (c1 int, c2 int) DYNAMIC RESULT SETS 1 EXTERNAL NAME 'C:CLRRtest.dll::Rtest.GetRows( IDataReader[] ) ' LANGUAGE CLR;

and this is it!

permanent link

answered 14 May '12, 12:29

DavidSteiman
61●2●2●7
accept rate: 0%

flat view

I don't think the example works because of the missing data types in the column descriptions. See this post for a working example on SQLA 16.

permanent link

answered 17 Jun '15, 06:50

Michael Fischer
670●11●15●27
accept rate: 25%

Hm, I'd think the data type of the DataTable.Columns will default to string unless specified otherwise, and SQL Anywhere should be able to convert the real contents to int (as specified in the CREATE PROCEDURE result set). - That's just guesswork on my part:)

(17 Jun '15, 07:22) Volker Barth

Replies hidden

It works in some situations and crashes in others. It might depend on the actual .NET Type of the DataReader column, the expected data type at SQLA, the .NET framework version used and probably moon phase and humidity. I never found out the exact combinations when it worked and when not. Always providing correct data types seems to have solved the issue for me.

(17 Jun '15, 13:49) Michael Fischer

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-12 ×438
stored-procedure ×125
result-set ×20
clr ×19

question asked: 11 May '12, 06:55

question was seen: 4,021 times

last updated: 17 Jun '15, 13:49

SAP SQL Anywhere

Generating resultset from CLR

Follow this question

Related questions