CODEWITHSUNDEEP
sql-serverasp.net-mvct-sqlc#-4.0entity-framework-6
Khan
Khan

Reputation: 271

Stored Procedure returns no columns, only integer

I have a problem with the entitry framework 6.0.0.0 when I import the stored procedure no matter what I do it does not return the result set, instead it returns only integer or set to none. below is the sp. 

alter proc spGetProd
@Prod nvarchar(500)
as
begin
SET FMTONLY OFF
IF OBJECT_ID('tempdb..##PRODUCTTABLE') IS NOT NULL DROP TABLE ##PRODUCTTABLE
DECLARE @MYQUERY nvarchar(MAX), @my_Div nvarchar(500);
set @my_Div = REPLACE(@Prod, ',', ''',''');

SET @MYQUERY = 'SELECT DISTINCT [GNo],[GName]    
into ##PRODUCTTABLE FROM ABC
where Div IN ('''+@my_Div+''') 
order by GNo'

EXEC (@MYQUERY)
SELECT GNo, GName FROM ##PRODUCTTABLE;
drop table  ##PRODUCTTABLE;
end

No matter what I do whether I set SET FMTONLY OFF / ON, NO WORK. I did it long back and it worked for one time only, when I set SET FMTONLY OFF and then removed it and it worked for that sp but for other Stored Procedures its not working. Even if I get the result set from select * from ##PRODUCTTABLE; or specify the columns like above. Here is my Action in MVC.

public JsonResult GetData()
        {
         var allProducts = gentity.spGetProd("AB"); //return type shows as int.
         return Json(allProducts, JsonRequestBehavior.AllowGet);
       }

Is there any other workaround because my app is mostly dependent on the stored procedures which usually return data from temp tables like above.

Upvotes: 1

Views: 2198

Answers (3)

Gert Arnold
Gert Arnold

Reputation: 109099

EF can't derive the structure of the result set from the stored procedure, because it is composed dynamically. But you can fix that and at the same time simplify the stored procedure.

First, create a Split function, for example, this one. Then use it in your stored procedure:

...
BEGIN
    SELECT DISTINCT [GNo],[GName]    
    FROM ABC
    JOIN dbo.Split(@Prod) AS prod ON prod.Name = ABC.Div 
    order by GNo
END

Now EF will be able to infer the returned columns from the SELECT statement.

Upvotes: 2

Igor Micev
Igor Micev

Reputation: 1652

The problem is in your stored procedure. Your problem is very simple and you're making the stuff complex.

Just create this function in your database(s).

CREATE FUNCTION dbo.SplitStrings
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );
GO

and now your stored procedure should look like 

ALTER PROC spGetProd
    @Prod nvarchar(500)
AS
BEGIN
 SET NOCOUNT ON;
 SELECT DISTINCT [GNo],[GName]    
 FROM ABC
 WHERE Div IN (SELECT Item FROM dbo.SplitStrings(@Prod,','))
 ORDER BY GNo;
END

No temp tables, no extra variables, no EXEC, no objects dropping. Just a clear select statement for your EF.

Upvotes: 1

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

It is not clear how you actually execute your procedure (dynamically or you use some kind of mapping). If I remember correctly, I had some problems related to resultset structure (schema) in SQL Server 2012 and I had to use WITH RESULT SETS to explicitly tell the expected resultset structure.

In Entity Framework this can be accomplished by this:

var query = "EXECUTE spGetProd @Prod WITH RESULT SETS ((GNo INT, GName NVARCHAR(1024)))";
var result = context.Database.SqlQuery<EntityType>(sql).ToList();

Edit after feedback

If result set structure is dynamic, I do not think Entity Framework is appropriate here. Instead, get the results into a DataSet.

If the maximal structure in known, you can transfer DataSet information into a list of objects:

public static DataTable ConvertTo<T>(IList<T> list)
{
    DataTable table = CreateTable<T>();
    Type entityType = typeof(T);
    PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

    foreach (T item in list)
    {
        DataRow row = table.NewRow();

        foreach (PropertyDescriptor prop in properties)
        {
            row[prop.Name] = prop.GetValue(item);
        }

        table.Rows.Add(row);
    }

    return table;
}

Upvotes: 0

Related Questions