cancel
Showing results for 
Search instead for 
Did you mean: 

Generating resultset from CLR

Former Member
0 Kudos
4,338

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

MCMartin
Participant
0 Kudos

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

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member

hello,

we managed it to find the answer to this question.

  1. 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();
    }
    

    }

  2. 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!

Michael_Fischer
Explorer
0 Kudos

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.

VolkerBarth
Contributor
0 Kudos

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:)

Michael_Fischer
Explorer

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.