CLR TVF -> Using Datareader...

Hi
I'm trying to create a CLR-TVF which should do some stuff (in my sample it's
just getting the syscolumns name column for the database _ODS).
I’ve got this error:
An error occurred while getting new row from user defined Table Valued
Function :
System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
data from FillRow method of a Table Valued Function, or is a UDT validation
method.
Question: Is it really not possible to make a sql-query within the C#-Part
of the CLR? I know, I could easily do it using a StoredProc…but I need a T
VF.
Thanks for your help…
Here is the code:
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)" )]
public static IEnumerable LoadSysColumns(string str2)
{
return str2;
}
public static void FillRow2(object row,
out string str2)
{
// creating a connection using the current sqlserver-context
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from _ODS.dbo.SYSCOLUMNS";
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Read();
str2 = sqlDataReader.GetValue(0).ToString();
}
}"Dominic" <Dominic@.discussions.microsoft.com> wrote in message
news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Hi
> I'm trying to create a CLR-TVF which should do some stuff (in my sample
> it's
> just getting the syscolumns name column for the database _ODS).
> I've got this error:
> An error occurred while getting new row from user defined Table Valued
> Function :
> System.InvalidOperationException: Data access is not allowed in this
> context. Either the context is a function or method not marked with
> DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
> data from FillRow method of a Table Valued Function, or is a UDT
> validation
> method.
> Question: Is it really not possible to make a sql-query within the C#-Part
> of the CLR? I know, I could easily do it using a StoredProc.but I need a
> TVF.
> Thanks for your help.
> Here is the code:
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)" )]
> public static IEnumerable LoadSysColumns(string str2)
> {
> return str2;
> }
> public static void FillRow2(object row,
> out string str2)
> {
> // creating a connection using the current sqlserver-context
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from
> _ODS.dbo.SYSCOLUMNS";
> SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
> sqlDataReader.Read();
> str2 = sqlDataReader.GetValue(0).ToString();
> }
> }
>
Any data access should be in the LoadSysColumns method, not in the
FillRowMethod. In LoadSysColumns just fill a List<string> and return that.
SQL will enumerate it and pass each member to your FillRowMethod, EG:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)")]
public static IEnumerable LoadSysColumns()
{
List<string> names = new List<string>();
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
while (sqlDataReader.Read())
{
names.Add(sqlDataReader.GetValue(0).ToString());
}
}
return names;
}
public static void FillRow2(object row, out string str2)
{
str2 = (string)row;
}
};
David|||Thanks David...works fine!
"David Browne" wrote:

> "Dominic" <Dominic@.discussions.microsoft.com> wrote in message
> news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Any data access should be in the LoadSysColumns method, not in the
> FillRowMethod. In LoadSysColumns just fill a List<string> and return that
.
> SQL will enumerate it and pass each member to your FillRowMethod, EG:
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Collections;
> using System.Collections.Generic;
> public partial class UserDefinedFunctions
> {
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.None,
> SystemDataAccess = SystemDataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)")]
> public static IEnumerable LoadSysColumns()
> {
> List<string> names = new List<string>();
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
> using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
> while (sqlDataReader.Read())
> {
> names.Add(sqlDataReader.GetValue(0).ToString());
> }
> }
> return names;
> }
> public static void FillRow2(object row, out string str2)
> {
> str2 = (string)row;
> }
> };
>
> David
>
>