今天看了下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);
}
}
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.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or 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()
。
从用户定义的表值函数获取新行时出错:
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.
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;
}
}
}
}
{
// 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);
}
}
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);
}
}
执行查询,预期结果出现....
