今天看了下SQL SERVER 2005 CLR  Integration (SQL SERVER 2005 CLR 集成),如是尝试写个例子看看:

仅仅将一个表的数据通过C#写的方法读出来,代码如下:

[SqlFunction(Name = "f_test", FillRowMethodName = "fillRows", TableDefinition = "a int,b int,c nvarchar(5)")]
    
public static IEnumerable GetData()
    {
        
using (SqlConnection connection = new SqlConnection("context connection=true;"))
        {
            connection.Open();
            SqlCommand cmd 
= new SqlCommand("select id,t_id,t_name from temp", connection);
            SqlDataReader datareader 
= cmd.ExecuteReader();
            
while (datareader.Read())
               
yield return datareader;
        }
    }

    
static void fillRows(object obj, ref SqlInt32 a, ref SqlInt32 b, ref SqlString c)
    {
        
if (null != obj)
        {
            SqlDataReader reader 
= (SqlDataReader)obj;
            a 
= reader.GetSqlInt32(0);
            b 
= reader.GetSqlInt32(1);
            c 
= reader.GetSqlString(2);
        }
    }

编译后,直接部署,执行

select * from dbo.f_test()

期待的结果没出现,报错了:

消息 6260,级别 16,状态 1,第 1 行
从用户定义的表值函数获取新行时出错: 
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.Readis a callback to obtain data from FillRow method of a Table Valued Functionor is a UDT validation method.
System.InvalidOperationException: 
   at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
   at System.Data.SqlServer.Internal.ClrLevelContext.GetDatabase(SmiEventSink sink, Int32
* pcbLen, IntPtr* ppwsName)
   at Microsoft.SqlServer.Server.InProcConnection.GetCurrentDatabase(SmiEventSink eventSink)
   at System.Data.SqlClient.SqlInternalConnectionSmi.Activate()
   at System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString options, Object providerInfo, DbConnection owningConnection)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.
Open()
   at UserDefinedFunctions.
<GetData>d__0.MoveNext()

通过提示,修改SqlFunction属性为:

[SqlFunction(DataAccess = DataAccessKind.Read, Name = "f_test", FillRowMethodName = "fillRows", TableDefinition = "a int,b int,c nvarchar(5)")]

[SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read, Name = "f_test", FillRowMethodName = "fillRows", TableDefinition = "a int,b int,c nvarchar(5)")]

重新部署,执行查询,依然报那个错.......仔细看了下错误详情,里面有:UserDefinedFunctions.<GetData>d__0.MoveNext(),我决定使用Reflector.exe看看编译后究竟是啥玩意,反编译后的东西为:

public class UserDefinedFunctions
{
    
// Methods
    private static void fillRows(object obj, ref SqlInt32 a, ref SqlInt32 b, ref SqlString c)
    {
        
if (null != obj)
        {
            SqlDataReader reader 
= (SqlDataReader) obj;
            a 
= reader.GetSqlInt32(0);
            b 
= reader.GetSqlInt32(1);
            c 
= reader.GetSqlString(2);
        }
    }

    [SqlFunction(DataAccess
=DataAccessKind.Read, Name="f_test", FillRowMethodName="fillRows", TableDefinition="a int,b int,c nvarchar(5)")]
    
public static IEnumerable GetData()
    {
        
return new <GetData>d__0(-2);
    }

    
// Nested Types
    [CompilerGenerated]
    
private sealed class <GetData>d__0 : IEnumerable<object>, IEnumerable, IEnumerator<object>, IEnumerator, IDisposable
    {
        
// Fields
        private int <>1__state;
        
private object <>2__current;
        
public SqlCommand <cmd>5__2;
        
public SqlConnection <connection>5__1;
        
public SqlDataReader <datareader>5__3;

        
// Methods
        [DebuggerHidden]
        
public <GetData>d__0(int <>1__state)
        {
            
this.<>1__state = <>1__state;
        }

        
private bool MoveNext()
        {
            
try
            {
                
switch (this.<>1__state)
                {
                    
case 0:
                        
this.<>1__state = -1;
                        
this.<connection>5__1 = new SqlConnection("context connection=true;");
                        
this.<>1__state = 1;
                        
this.<connection>5__1.Open();
                        
this.<cmd>5__2 = new SqlCommand("select id,t_id,t_name from temp"this.<connection>5__1);
                        
this.<datareader>5__3 = this.<cmd>5__2.ExecuteReader();
                        
while (this.<datareader>5__3.Read())
                        {
                            
this.<>2__current = this.<datareader>5__3;
                            
this.<>1__state = 2;
                            
return true;
                        Label_0090:
                            
this.<>1__state = 1;
                        }
                        
this.<>1__state = -1;
                        
if (this.<connection>5__1 != null)
                        {
                            
this.<connection>5__1.Dispose();
                        }
                        
break;

                    
case 2:
                        
goto Label_0090;
                }
                
return false;
            }
            fault
            {
                ((IDisposable) 
this).Dispose();
            }
        }

        [DebuggerHidden]
        IEnumerator
<object> IEnumerable<object>.GetEnumerator()
        {
            
if (Interlocked.CompareExchange(ref this.<>1__state, 0-2== -2)
            {
                
return this;
            }
            
return new UserDefinedFunctions.<GetData>d__0(0);
        }

        [DebuggerHidden]
        IEnumerator IEnumerable.GetEnumerator()
        {
            
return this.System.Collections.Generic.IEnumerable<System.Object>.GetEnumerator();
        }

        [DebuggerHidden]
        
void IEnumerator.Reset()
        {
            
throw new NotSupportedException();
        }

        
void IDisposable.Dispose()
        {
            
switch (this.<>1__state)
            {
                
case 1:
                
case 2:
                    
break;

                
default:
                    
break;
                    
try
                    {
                    }
                    
finally
                    {
                        
this.<>1__state = -1;
                        
if (this.<connection>5__1 != null)
                        {
                            
this.<connection>5__1.Dispose();
                        }
                    }
                    
break;
            }
        }

        
// Properties
        object IEnumerator<object>.Current
        {
            [DebuggerHidden]
            
get
            {
                
return this.<>2__current;
            }
        }

        
object IEnumerator.Current
        {
            [DebuggerHidden]
            
get
            {
                
return this.<>2__current;
            }
        }
    }
}

看到这写代码,恍然大悟.....编译器将实际访问数据库的代码移到它自己生成的 MoveNext 方法了,而这个方法并没有SqlFunctionAttribute声明,所以报ata access is not allowed in this context.错了.

将代码修改如下:

[SqlFunction(DataAccess = DataAccessKind.Read, Name = "f_test", FillRowMethodName = "fillRows", TableDefinition = "a int,b int,c nvarchar(5)")]
    
public static IEnumerable GetData()
    {
        IList
<Item> items = new List<Item>();

        
using (SqlConnection connection = new SqlConnection("context connection=true;"))
        {
            connection.Open();
            SqlCommand cmd 
= new SqlCommand("select id,t_id,t_name from temp", connection);
            SqlDataReader datareader 
= cmd.ExecuteReader();
            
while (datareader.Read())
                items.Add(
new Item(datareader));
        }
        
return items;
    }

    
static void fillRows(object obj, ref SqlInt32 a, ref SqlInt32 b, ref SqlString c)
    {
        
if (null != obj)
        {
            Item item 
= (Item)obj;
            a 
= item.id;
            b 
= item.t_id;
            c 
= item.t_name;
        }
    }

    
struct Item
    {
        
public readonly SqlInt32 id;
        
public readonly SqlInt32 t_id;
        
public readonly SqlString t_name;
        
public Item(SqlDataReader reader)
        {
            
this.id = reader.GetSqlInt32(0);
            
this.t_id = reader.GetSqlInt32(1);
            
this.t_name = reader.GetSqlString(2);
        }
    }

执行查询,预期结果出现....

posted on 2009-06-14 15:05  空空儿  阅读(723)  评论(0编辑  收藏  举报