最近用ASP.net 2.0 + SQL Server做一个网页表单的提交,使用C#编写存储过程来完成向SQL Server数据库表中插入记录的操作。在调用这个存储过程时,出现了关于存储权限的一个异常。下面详述异常产生的过程和解决方案。
1.操作步骤:
1)使用ASP.net 2.0,用C#写了一个存储过程,对数据库test中的一个表进行操作,代码如下:
public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Submit(string strAnswer)
{
using (SqlConnection connection = new SqlConnection("Data Source=WS;Initial Catalog=test;Integrated Security=False;Trusted_Connection=yes;"))
{
connection.Open(); // ***执行到这一步出现异常,详见下文所述***
String cmdTxt = "INSERT INTO dbo.Response_SCL90 VALUES(" + strAnswer + ")";
SqlCommand command = new SqlCommand(cmdTxt, connection);
SqlDataReader reader = command.ExecuteReader();
}
}
}
2)编译生成了Submit_Answer.dll,接着,在SQL Server中注册这个dll,并创建存储过程,SQL脚本如下:
CREATE ASSEMBLY Submit_Answer
FROM 'D:/study/C#/测评系统/WebSite1/StoredProcedure/Submit_Answer/bin/Debug/Submit_Answer.dll';
GO
CREATE PROCEDURE dbo.Submit_Answer
(
@strAnswer nvarchar(256)
)
WITH EXECUTE AS OWNER
AS
EXTERNAL NAME Submit_Answer.StoredProcedures.Submit
GO
3)最后,在.net中调用这个存储过程,代码如下:
SqlConnection connection = new SqlConnection("Data Source=WS;Initial Catalog=test;Integrated Security=False;Trusted_Connection=yes;");
String cmdTxt = "dbo.Submit_Answer";
SqlCommand command = new SqlCommand(cmdTxt, connection);
command.CommandType = CommandType.StoredProcedure;
1.操作步骤:
1)使用ASP.net 2.0,用C#写了一个存储过程,对数据库test中的一个表进行操作,代码如下:
public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Submit(string strAnswer)
{
using (SqlConnection connection = new SqlConnection("Data Source=WS;Initial Catalog=test;Integrated Security=False;Trusted_Connection=yes;"))
{
connection.Open(); // ***执行到这一步出现异常,详见下文所述***
String cmdTxt = "INSERT INTO dbo.Response_SCL90 VALUES(" + strAnswer + ")";
SqlCommand command = new SqlCommand(cmdTxt, connection);
SqlDataReader reader = command.ExecuteReader();
}
}
}
2)编译生成了Submit_Answer.dll,接着,在SQL Server中注册这个dll,并创建存储过程,SQL脚本如下:
CREATE ASSEMBLY Submit_Answer
FROM 'D:/study/C#/测评系统/WebSite1/StoredProcedure/Submit_Answer/bin/Debug/Submit_Answer.dll';
GO
CREATE PROCEDURE dbo.Submit_Answer
(
@strAnswer nvarchar(256)
)
WITH EXECUTE AS OWNER
AS
EXTERNAL NAME Submit_Answer.StoredProcedures.Submit
GO
3)最后,在.net中调用这个存储过程,代码如下:
SqlConnection connection = new SqlConnection("Data Source=WS;Initial Catalog=test;Integrated Security=False;Trusted_Connection=yes;");
String cmdTxt = "dbo.Submit_Answer";
SqlCommand command = new SqlCommand(cmdTxt, connection);
command.CommandType = CommandType.StoredProcedure;