"Best" of two worlds? CLR-Stored Procedure Mapping

Jul 4, 2008 at 7:06 AM
Hi,

I want to use Stored Procedures written in an assembly. Now the Stored procedures are registered in database and works fine.
Actually i can choose them in Recipe.

But:
there is nothing i can map my business entity to.
My SP looks like:
In Code:

[Microsoft.SqlServer.Server.

SqlProcedure]
public static void MySP(SqlInt32 Intvalue, out SqlBoolean outparam)
{
using (SqlConnection conn = new SqlConnection("Context Connection=true"))
{
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText =
...SQL Statement
SqlPipe pipe = SqlContext.Pipe;
using (SqlDataReader reader = cmd.ExecuteReader())
{
outparam = reader.HasRows;
pipe.Send(reader);
}
}
}

In database:

 

CREATE

PROCEDURE [dbo].[MySP]
@Intvalue [int]
,
@outparam [bit] OUTPUT
WITH
EXECUTE AS CALLER
AS
EXTERNAL
NAME [MyFirstAssembly].[StoredProcedures].[MySP]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'MySP'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'MySP.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'MySP'
GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=46 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'MySP'

Can anybody give me a hint whats wrong, respectivley what are exactly the requirements for the SP to be used for data access this way? 

Thx

Klaus

 

Coordinator
Jul 7, 2008 at 12:26 AM
Hi Klaus,

The Repository Factory only supports business entities that map to a database table, and it generates stored procedures for CRUD operations on that table. Although you can see a list of all available stored procedures, you can only use those that are for CRUD operations on a particular table: create a new record, read record(s), update a record, or delete a record. And the business entity must match the database table.

In your case, it looks like that your stored procedure is not executing CRUD operations on a particular table (please correct me if I am wrong!). If so, then it is not supported.

Thanks,
Luis
Jul 7, 2008 at 6:56 AM
Edited Jul 7, 2008 at 9:28 AM
Hallo Luis,

actually it is a select  on a table. Sorry for that but i didn't think the select-statement of a particular importance:

I have a table a fitting business entitiy and a stored procedure to get values out of the table written in an assembly.

Meanwhile I found a workaround:
As to me it looks as if the repository factory needs sth. to parse, i have written a dummy stored procedure which have the needed input parameters and the wanted columns in a select statement.
Nexrt I run the wizard with the dummy.
At last I replaced the name of the dummy stored procedure with the one of the assembly-based stored procedure and voilá it works. 

thanks

Klaus