1

I'm trying to figure out how to obtain the results of a SQL Server stored procedure using Codefluent. I have a well developed CF project, but there is one very complicated stored procedure that I don't want to monkey around with.

Basically, the procedure takes a series of input parameters and returns rows from a table. From this standpoint it is fairly simple, though the underlying T-SQL is not. The column names returned are not the original column names in the underlying tables which is why I am also looking to use a raw CF procedure.

I've tried a variety of approaches. I created a view and a raw procedure - (the view inferred model seemed to like this one)

<cf:view autoLightweight="true" name="myLWview">
 <cf:viewProperty name="field1" />
 <cf:viewProperty name="field2" />
  ...
 <cf:viewProperty name="fieldn" />
</cf:view>
<cf:method name="GetLog" returnTypeName="list(of myLWview)">
 <cf:body text="RAW (parameter1, parameter2, ..., parametern)" rawText="select [field1], [field2], ... , [fieldn] from mySQLprocedure" language="tsql" />
</cf:method>

This fails on the ExecuteReader call.

I'm sure it is something obvious I'm missing. Any advice would be greatly appreciated. Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Greg Delaney
  • 81
  • 11
  • The following knowledge base article may prove useful https://softfluent.com/product/codefluent-entities/knowledge-center/exposing-legacy-stored-procedures-as-entities – Dave Apr 12 '17 at 15:09

3 Answers3

1

There are mainly two different ways to map your stored procedure. If the return columns are pretty closed to the property of an entities, you can use a raw view. Otherwise, you can use a lightweight entity such as:

<CityAddress lightweight="true">
  <CityName persistentName="Address_CityName" />

  <cf:method name="LoadAllCities" body="load() raw">
    SELECT $Address::CityName$ FROM $Address$
  </cf:method>
</CityAddress>

As a side note, you can also map the stored procedure to a DataSet:

<cf:method name="Custom" returnTypeName="System.Data.DataSet">
  <cf:body text="RAW" rawText="SELECT $Customer{Columns}$ FROM $Customer$" language="tsql" />
</cf:method>

EDIT

So, you already have the stored procedure in the database and you want to call it. The idea is still the same: create a lightweight entity and a method. However, you must indicate you don't want the SQL producer to generate the procedure.

<CityAddress lightweight="true">
  <CityName persistentName="Address_CityName" />

  <cf:method name="LoadAllCities" 
             body="load(string param1, int param2) raw" 
             cfps:produce="false"
             persistenceName="mySQLprocedure" />
</CityAddress>
meziantou
  • 20,589
  • 7
  • 64
  • 83
  • Thank you for the reply. – Greg Delaney Apr 12 '17 at 14:09
  • This approach creates another stored procedure which cannot call the original stored procedure. The stored procedure from which I am trying to obtain results from has joins to multiple tables, some of which are not a part of the CF model. Also, the procedures Select [fieldname]s do not map to CF entity property names. I would just like to call a stored procedure with a few input parameters and have it return either a view or non-persistent entity collection or list with property names that match the stored procedures property names. – Greg Delaney Apr 12 '17 at 14:16
  • Thank Meziantou. I've changed the CF model as you suggested, but when I build the model I receive this error: Model is invalid CF0286: Lightweight entity 'CityAddress' can only declare methods with raw bodies. Method 'LoadAllCities' has an invalid body. – Greg Delaney Apr 12 '17 at 16:38
  • I can't test right now, but I think you must add a dummy raw body `rawText="SELECT 1"` – meziantou Apr 12 '17 at 16:50
  • After some trial and error, this works: – Greg Delaney Apr 12 '17 at 17:08
0

If the core of your problem is the stored procedure returns different field names than the underlying table of your entity and therefore will not map correctly, you can overcome that using a table variable inside a raw method.

The raw method would contain the following:

-- Create table variable that matches field names of your entity's table 
declare @customerTableVar as table
(
 Customer_Id int,
 Customer_Name nvarchar(128)
)

-- Populate table variable using existing stored procedure
INSERT INTO @customerTableVar
EXEC ('sp_ExistingGetCustomerStoredProcedure')

-- Outputs the results of the table variable.
SELECT * FROM @customerTableVar
Dave
  • 649
  • 5
  • 13
0

Thanks to Mexiantou and Dave, this is the CF model (pattern) I created to obtain results from an MS-SQL stored procedure that is/was not created by the CF modeler.

First, I created a CF lightweight entity with the FiledNames returned by the SQL query (field1...field-N).

<cf:entity lightweight="true" name="myLWentity">
  <cf:property name="field1" />
  <cf:property name="field2" />
  ...
  <cf:property name="fieldn" />

Then I created a CF:method with CF:body and CF:parameter sections.

 <cf:method name="LoadmyLWentity" cfps:produce="false" 
     persistenceName="mySQLprocedure" >
  <cf:body text="LOAD  RAW" rawText="Select * from  [dbo].ThisDoesntSeemToMatter]" language="tsql" />
        <cf:parameter typeName="..." name="parameter1" />
        <cf:parameter typeName="..." name="parameter2" />
                ...
        <cf:parameter typeName="..." name="parametern" />   
 </cf:method>
</cf:entity>

One thing to note: The raw text ("Select * from ...") procedure name seems to be ignored so it can be anything. Instead it uses the CF:persistenceName which is the name of the stored procedure that was not created by the modeler (notice the cfps:produce="false").

The result from calling myLWentity.LoadmyLWentity is a list(of myLWentity) which is returned by mySQLprocedure.

Greg Delaney
  • 81
  • 11