Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005 C#
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)
 
________________________________________________________________
Posted 29-Jan-13 2:52am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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[^]
  Permalink  
Comments
Mantu Singh at 29-Jan-13 8:09am
   
Thanks a lot sir!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,142
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 29 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100