Hi all I have the following CLR stored procedure :
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub sssGetActiveRepositoryByTitle( _
ByVal title As String)
' Add your code here
Using conn As New SqlConnection("context connection=true")
Dim objCommand As New SqlCommand()
Dim TitleParam As New SqlParameter("@.Title", SqlDbType.VarChar, 100)
TitleParam.Value = title
objCommand.Connection = conn
conn.Open()
'build the delete command
objCommand.CommandText = _
"select * from sstRepository where IsActive = 1 and Title =" & TitleParam.Value.ToString
SqlContext.Pipe.ExecuteAndSend(objCommand)
conn.Close()
End Using
End Sub
Now I have a windows service in my data layer that needs to access this stored procedure and convert it into a dataaset to pass to the client application :
Imports System.Data.SqlClient
Imports NBS.SURVEYSDATABASEservice.DBMS
Public Class clsClient
' it inherits the stored procedures from the DBMS class which is the name
'of the CLR dll
Inherits StoredProcedures
Public Function GetClientByVirtualPath(ByVal pstrVirtualPath As String) As DataSet
Try
Dim i As SqlDataReader
'parameters are stored in an array (zero based) for use in the base class
Dim parmArrSqlParms(0) As SqlClient.SqlParameter
' Dim fff As Int32
i = sdsGetClientByVirtualPath(pstrVirtualPath)
' Return MyBase.RunProcedure("dbo.sdsGetClientByVirtualPath", parmArrSqlParms)
Catch ex As Exception
'log the error
'cLogger.LogMessage("ACME", "SampleApplication", Logger.EntryTypes.RunError, System.Environment.MachineName, "clsDemoClass.SelectAllCompanies", ex.Message)
'raise the error to the caller for handling
Throw ex
End Try
End Function
I've tried a bunch of different things to no avail the error I keep getting trying to access the sqlpipe resulsts is " this expressions does not return any values"
any ideas ? I am basically converting around TSQL 50 stored procs into managed CLR code and the CLR funtions are created but I am really having problems accessing the resuluts on the client end .
Help please !
Before moving 50 TSQL stored proc into managed code, have you considered our TSQL vs CLR guidelines located here?
