我用C#写SQLServer 2005的储存过程没有问题,但写函数时,如果访问了数据库则函数报错,大家是怎么写的?
我的函数如下:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetNameById(SqlInt32 p_Id)
{
string strSql = "select name from MyTableName where Id=" + p_Id.ToString();
string strResult = "";
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(strSql, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
strResult = reader[0].ToString();
reader.Close();
}
}
conn.Close();
}
return new SqlString(strResult);
}当我用select dbo.GetNameById(1)进行查询时,返回如下错误:
消息 6522,级别 16,状态 2,第 1 行
在执行用户定义例程或聚合 "GetNameById" 期间出现 .NET Framework 错误:
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not ed 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.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
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.GetNameById(SqlInt32 p_Id)
。如果放在储存过程中则没有问题,该怎么办呢?写函数的目的是想放在sql 语句中使用
我的函数如下:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetNameById(SqlInt32 p_Id)
{
string strSql = "select name from MyTableName where Id=" + p_Id.ToString();
string strResult = "";
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(strSql, conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
strResult = reader[0].ToString();
reader.Close();
}
}
conn.Close();
}
return new SqlString(strResult);
}当我用select dbo.GetNameById(1)进行查询时,返回如下错误:
消息 6522,级别 16,状态 2,第 1 行
在执行用户定义例程或聚合 "GetNameById" 期间出现 .NET Framework 错误:
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not ed 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.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
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.GetNameById(SqlInt32 p_Id)
。如果放在储存过程中则没有问题,该怎么办呢?写函数的目的是想放在sql 语句中使用
说明了原因
SqlConnection("context connection=true"))
找个对吗
context好像有问题 从exception看
呵呵
System.Data.SqlClient.SqlConnection Conn = new System.Data.SqlClient.SqlConnection(strConnection);
try
{
SqlCommand cmd = Conn.CreateCommand();
cmd.CommandText = string.Format("SELECT LastName,FirstName FROM Employees WHERE EmployeeId=1");
Conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
MessageBox.Show(ds.Tables[0].Rows[0][0].ToString()); MessageBox.Show("成功");
}
catch (Exception)
{
MessageBox.Show("出错,不能连接服务器!"); }
finally
{
Conn.Close();
}
以上程序是连接成功的 对了 我用的windows身份连接的