Managed function problem in SQL 2005

See more:

SQL-Server-2005

C#

SQL-Server

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();
        }
    }
}

------------------------------

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

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)

________________________________________________________________