SQL 2005中的托管函数问题 [英] Managed function problem in SQL 2005
问题描述
我需要在Sql Server中创建一个托管函数来从excel表导入数据
为此我创建了以下类库ClassLibrary1
I need to create a managed function in Sql Server to import data from an excel sheet
for this I have created the following class library ClassLibrary1
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace ClassLibrary1
{
public class Class1
{
public static string ImportData()
{
string msg = "";
DataSet ds = new DataSet();
try
{
OleDbConnection con = new OleDbConnection(String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\admin\\Desktop\\stud.xls;Persist Security Info=False;Extended Properties=""EXCEL 8.0; HDR=YES"""));
con.Open();
OleDbCommand cmd = new OleDbCommand("SELECT `SID`,`SNAME` FROM [Sheet1$]", con);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
con.Close();
msg=ExportData(ds);
}
catch (Exception ex)
{
msg = ex.Message;
}
return msg;
}
public static string ExportData(DataSet ddst)
{
SqlConnection con = new SqlConnection("Data Source=ENCORE;Initial Catalog=TEST;User Id=sa;Password=niit@123");
con.Open();
SqlCommand cmd;
DataRowCollection drc= ddst.Tables[0].Rows;
int i=0;
foreach (DataRow dr in drc)
{
cmd = new SqlCommand("insert into stud values(@1,@2)", con);
cmd.Parameters.AddWithValue("@1", dr[0]);
cmd.Parameters.AddWithValue("@2", dr[1]);
i+=cmd.ExecuteNonQuery();
}
return "rows imported "+i.ToString();
}
}
}
我现在可以在使用此代码的示例控制台应用程序中使用它
I can now use it in a sample console application using this code
using System;
using System.Collections.Generic;
using System.Text;
using ClassLibrary1;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
string msg= Class1.ImportData();
Console.WriteLine(msg);
Console.Read();
}
}
}
------------ ------------------
问题是虽然它在控制台应用程序中工作正常但同样的事情是在Sql server中没有按预期工作
返回错误的Sql代码如下： -
________________________________________________________________
------------------------------
The problem is it that while it works fine in console application the same thing does not work as expected in Sql server
The Sql code with error returned is as follows:-
________________________________________________________________
create database TEST
use test
create table stud
(
id varchar(50),
sname varchar(50)
)
select * from stud
alter database TEST set trustworthy on
SP_CONFIGURE CLR_ENABLED, 1
RECONFIGURE
create assembly MYAf
FROM ''E:\ClassLibrary1.dll''
with PERMISSION_SET=unsafe
create function IMPDTA()
RETURNS nvarchar(500)
AS
EXTERNAL NAME
MYAf.[ClassLibrary1.Class1].ImportData
truncate table stud
select ''RESULT''=dbo.IMPDTA() <pre></pre>
ERROR MESSAGE
结果
-------------------------------------- --------------------------------
在此上下文中不允许数据访问。上下文是没有用DataAccessKind.Read或SystemDataAccessKind.Read标记的函数或方法，是从表值函数的FillRow方法获取数据的回调，或者是UDT验证我
（1行受影响）
________________________________________________________________
ERROR MESSAGE
RESULT
----------------------------------------------------------------------
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 me
(1 row(s) affected)
________________________________________________________________
推荐答案
， con）;
OleDbDataAdapter da = new OleDbDataAdapter（cmd）;
da.Fill（ds）;
con.Close（）;
msg = ExportData（ds）;
}
catch（Exception ex）
{
msg = ex.Message;
}
return msg;
}
公共静态字符串ExportData（DataSet ddst）
{
SqlConnection con = new SqlConnection（Data Source = ENCORE; Initial Catalog = TEST;用户ID = sa;密码= niit @ 12 3）;
con.Open（）;
SqlCommand cmd;
DataRowCollection drc = ddst.Tables [0] .Rows;
int i = 0;
foreach（drc中的DataRow dr）
{
cmd = new SqlCommand（insert into stud values（@ 1，@ 2），con）;
cmd.Parameters.AddWithValue（@ 1，dr [0]）;
cmd.Parameters.AddWithValue（@ 2，dr [1]）;
i + = cmd.ExecuteNonQuery（）;
}
返回rows imported+ i.ToString（）;
}
}
}
", con); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(ds); con.Close(); msg=ExportData(ds); } catch (Exception ex) { msg = ex.Message; } return msg; } public static string ExportData(DataSet ddst) { SqlConnection con = new SqlConnection("Data Source=ENCORE;Initial Catalog=TEST;User Id=sa;Password=niit@123"); con.Open(); SqlCommand cmd; DataRowCollection drc= ddst.Tables[0].Rows; int i=0; foreach (DataRow dr in drc) { cmd = new SqlCommand("insert into stud values(@1,@2)", con); cmd.Parameters.AddWithValue("@1", dr[0]); cmd.Parameters.AddWithValue("@2", dr[1]); i+=cmd.ExecuteNonQuery(); } return "rows imported "+i.ToString(); } } }
我现在可以在使用此代码的示例控制台应用程序中使用它
I can now use it in a sample console application using this code
using System;
using System.Collections.Generic;
using System.Text;
using ClassLibrary1;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
string msg= Class1.ImportData();
Console.WriteLine(msg);
Console.Read();
}
}
}
------------ ------------------
问题是虽然它在控制台应用程序中工作正常但同样的事情是在Sql server中没有按预期工作
返回错误的Sql代码如下： -
________________________________________________________________
------------------------------
The problem is it that while it works fine in console application the same thing does not work as expected in Sql server
The Sql code with error returned is as follows:-
________________________________________________________________
create database TEST
use test
create table stud
(
id varchar(50),
sname varchar(50)
)
select * from stud
alter database TEST set trustworthy on
SP_CONFIGURE CLR_ENABLED, 1
RECONFIGURE
create assembly MYAf
FROM ''E:\ClassLibrary1.dll''
with PERMISSION_SET=unsafe
create function IMPDTA()
RETURNS nvarchar(500)
AS
EXTERNAL NAME
MYAf.[ClassLibrary1.Class1].ImportData
truncate table stud
select ''RESULT''=dbo.IMPDTA() <pre></pre>
ERROR MESSAGE
结果
-------------------------------------- --------------------------------
在此上下文中不允许数据访问。上下文是没有用DataAccessKind.Read或SystemDataAccessKind.Read标记的函数或方法，是从表值函数的FillRow方法获取数据的回调，或者是UDT验证我
（1行（s）受影响）
________________________________________________________________
ERROR MESSAGE
RESULT
----------------------------------------------------------------------
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 me
(1 row(s) affected)
________________________________________________________________
首先，相信消息： 上下文是未使用DataAccessKind.Read 标记的函数或方法。是吗？不，它不是。
http：/ /msdn.microsoft.com/en-us/library/ms131043(v=sql.90).aspx [ ^ ]
First of all, believe the message: the context is a function or method not marked with DataAccessKind.Read. Is it? No, it is not.
http://msdn.microsoft.com/en-us/library/ms131043(v=sql.90).aspx[^]
这篇关于SQL 2005中的托管函数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
