Cannot save a stored procedure into an array

Question

I'm using asp.net to process a SQL query that returns a column from some table. Normally what I'd do is set a variable equal to the stored procedure function call and add .ToArray() at the end, which is what I want to do here but I'm getting an error message int does not contain a definition for toarray...

I'm confused because I followed the same syntax that I used in another part of the program for a similar thing. It worked fine before but I can't figure out why it wants to fight with me now.

Here's my SQL:

IF OBJECT_ID('#temp') IS NOT NULL
    BEGIN
        DROP TABLE #temp
    END

--Create temp table to store data
CREATE TABLE #temp 
(
EventID nvarchar(50),
RunDate date,
SectionCode nvarchar(50),
SectionMapCode nvarchar(50),
DispSort int,
Capacity int,
Attendance int,
PctCap int,
HeatColor nvarchar(50)
)

DECLARE @runDate date = GETDATE()

INSERT #temp  Exec GamedayReporting.dbo.uspGetEventKillSheetDetailedReport @EventID, @runDate;

select Capacity from #temp;

This returns exactly what I want in SQL but when I call it in my Controller I get the error I posted above.

Here's my C# code:

 public ActionResult Dropdown()
    {
        // add your code after post method is done
        var selectedOption = Request["eventId"];
        var date = DateTime.Today;
        var myQuery = db.uspGetEventKillSheetDetailedReport(selectedOption, date).ToArray();
        ViewData["query"] = myQuery;
        System.Diagnostics.Debug.WriteLine(myQuery);
        TempData["option"] = selectedOption;
        return RedirectToAction("Map");
    }

    public ActionResult Map()
    {
        var secAttendance = db.uspGetSectionAttendance("option").ToArray();
        var secCapacity = db.uspGetSecCapacity("option");
        var secMapCode = db.uspGetSectionMapCode("option");



}
    public JsonResult GetDropdownList()
    {


        var ids = db.uspGetAllEventIds().ToArray();
        ViewData["ids"] = db.uspGetAllEventIds().ToArray();

        return Json(new { data = ids }, JsonRequestBehavior.AllowGet);
    }

So Dropdown() and GetDropdownList() work fine, but I'm getting the problem with Map().

Basically I want to take the column returned from my SP and store it into an array but it won't let me. Anybody able to help me work through this?

Update

I changed .ToArray() to .toString().toArray(), which got me past the compiler error, but upon logging it into the console I found it was returning char instead of string. So I changed the whole line to

string secAttendance = new string(db.uspGetSectionAttendance("option").ToString().ToArray());

and output the result into the console and found it returns 0.

0 comes from the Return Value in SQL, which I don't understand. It will fetch the correct column but will not send the correct data to ASP.

Here's a screenshot of the output from my SQL:

`int does not contain a definition for toarray` Error message said it all; you cannot call `ToArray()`. How to plan to use the end result? — Win, Jul 12 '17 at 17:35
@Win I'm going to store the whole column from the SP into an array, then send the array to my View for use in functions — Luke Danger Kozorosky, Jul 12 '17 at 17:41

score 0 · Answer 1 · answered Jul 12 '17 at 17:33

0

The error is correct. There must be only one row, one column value in the output. You are selecting

select Capacity from #temp;

which returns a single value as an int. It cannot be directly cast into an array. Instead, if you want an array, you can create a blank

Array<int> a = new Array<int>[1]

and then push this output to that array.

answered Jul 12 '17 at 17:33

Amit Kumar Singh

4,393
2
9
22

Wouldn't that just give me one value? I need to have the whole column – Luke Danger Kozorosky Jul 12 '17 at 17:35
In that case, you can take the value in a separate variable, check if its type is an array. If not, create an array. If it already is an array, you are good. In other words, you can check the length of output received. – Amit Kumar Singh Jul 12 '17 at 17:36
Hmm. C# wants me to define a class for Array before I can try this solution. Doesn't seem right. – Luke Danger Kozorosky Jul 12 '17 at 17:40
No, it does not. This looks like entity framework code. Kindly check the return type of db.uspGetSectionAttendance_Result in the generated file. You can open entity framework file into a notepad to check it for stored procedures. So, when you might have added stored procedure, entity framework might have inferred the return type as int. – Amit Kumar Singh Jul 12 '17 at 17:43
Yes it is returning `public virtual int`. Should I parse it as a string before storing it into an array, then parse it back to an int? That seems rather redundant. Since I'm getting the data from an existing database I can't exactly change the data type of the whole column – Luke Danger Kozorosky Jul 12 '17 at 17:50
This answer is no good and has many errors in it. 1) `The error is correct. There must be only one row, one column value in the output. ` this is not correct. 2) `select Capacity from #temp;` does not return a single value. – CodingYoshi Jul 12 '17 at 17:51
Next points are suggestion. I am not coding it right now. What you can do is, change the public virtual int to public virtual int[] in the cs file, and similar change in the return type for the procedure in the EF xml file. See, if that works. Alternatively, you can change last line of your procedure to "SELECT top 4 number FROM master.dbo.spt_values WHERE number > 0", remove the procedure from entity framework file, and add again. Then check if it is inferring it now as array, and then again change sql script in the db to original last line. – Amit Kumar Singh Jul 12 '17 at 18:09
@Amit I just did `.ToString().ToArray()` which worked fine. Like I said before, it seems kind of redundant because I'm just going to parse them back to `int` in JavaScript. Can C# really not handle an integer array??? – Luke Danger Kozorosky Jul 12 '17 at 18:20
ok, even if old workaround, it will work. It's not a problem with C#. It is a problem with the way entity framework is inferring the output of your SP while adding it. – Amit Kumar Singh Jul 12 '17 at 18:21
Still giving problems. After converting to string then to array, I output the value in the console and found that it is returning 0, which is the return value of the stored procedure. I compared this with my other stored procedures that work and they have the same 0 return value. So Essentially my SQL is fetching the correct data but not returning it to ASP. Any ideas? – Luke Danger Kozorosky Jul 12 '17 at 18:47
Are you sure your #temp is getting populated correctly. If yes, try this https://stackoverflow.com/questions/16593473/ef-cant-infer-return-schema-from-stored-procedure-selecting-from-a-temp-table , and may be a look at this. https://stackoverflow.com/questions/3047751/problem-with-entity-framework-4-complex-types-storedprocs-and-temp-tables?noredirect=1&lq=1 – Amit Kumar Singh Jul 12 '17 at 19:00

Cannot save a stored procedure into an array

1 Answers1