T-sql C# CLR Error

I have created CLR using C# and i have added to successfully to database to my CLR.
But when i'm try to run error message is showin

Error

Msg 6522, Level 16, State 1, Procedure SendOrdersAPI, Line 9
A .NET Framework error occurred during execution of user-defined routine or aggregate "SendOrdersAPI":
System.InvalidOperationException: Invalid attempt to read when no data is present.
System.InvalidOperationException:
   at System.Data.SqlClient.SqlDataReader.CheckDataIsReady(Int32 columnIndex, Boolean allowPartiallyReadColumn, Boolean permitAsync, String methodName)
   at System.Data.SqlClient.SqlDataReader.TryReadColumn(Int32 i, Boolean setTimeout, Boolean allowPartiallyReadColumn)
   at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
   at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
   at ManCLR.dbConnection.username()
   at ManCLR.SendOrderOMS.SendOrdersTOAPI(String OrderNumber, Int32 TotalNumberLine)
   at ManCLR.DenManMain.sendOMS(String OrderNumber, String username)

CREATE PROCEDURE SendOrdersAPI
(
@OrderNumber nvarchar(MAX),
@username nvarchar(MAx)
)
AS 
EXTERNAL NAME ManCLR.[ManCLR.ManMain].sendAPI
GO

EXEC SendOrdersAPI  'Test123','sa'

Open in new window


And also i used following commands in the Sql server to add assembly

CREATE ASSEMBLY SMdiagnostics AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\SMdiagnostics.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.Web] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll' WITH permission_set = unsafe
--CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [System.IdentityModel.Selectors] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.IdentityModel.Selectors.dll' with permission_set = unsafe
CREATE ASSEMBLY [System.Messaging] AUTHORIZATION dbo FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Messaging.dll' WITH permission_set = unsafe
CREATE ASSEMBLY [Microsoft.Transactions.Bridge] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Microsoft.Transactions.Bridge.dll' with permission_set = unsafe

Please attached Assembly Details form

select * from sys.assemblies

if anyone can let me know how to solve much appreciated.
Many Thanks
Assembly.xlsx
LVL 2
lankapalaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
This error means you have no data available. You may read this article: https://blogs.msdn.microsoft.com/spike/2008/07/25/invalid-attempt-to-read-when-no-data-is-present/ to find more details for such error.

You should test your C# code outside SQL Server first. Then you have to test it under the user which is used to run SQL Server service and then you are safe to implement it as CLR SP in SQL Server.

I suppose you are not using the C# code to read data from the SQL Server on which is the SP implemented as it would be rather contraproductive.
0
lankapalaAuthor Commented:
My program is working in perfectly in the C# but when it's transferred to CLR (created  complete different project using C# CLR) not working.
C# reader is working perfectly in the console application.
0
pcelbaCommented:
Create some much simpler CLR SP which reads data and returns just the number of rows read. If this will work for you then you may go to the more complex next step.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

lankapalaAuthor Commented:
Yes,Thank you . i knew that for the test.But i 'm looking for some quick technical help where i miss anything.
I think i missed the SqlContext.Pipe.Send
so i'm working on that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
Great you've fixed the problem. Was it caused by the missed SqlContext.Pipe.Send?
0
lankapalaAuthor Commented:
Yes Reader need to pass
Like this
   SqlContext.Pipe.Send(reader);
0
lankapalaAuthor Commented:
I found the issue and resolved
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.