CODEWITHSUNDEEP
javascriptsql-server
powdercakez
powdercakez

Reputation: 11

Return multiple values from stored procedure

The idea is that each subject has multiple topics, and when I call the function getTopicsForSubject() in order to get this data to a website page, it returns only 1 of the records from the table. I'm testing this using console.log(response) in the JavaScript file to see what is being passed in from the stored procedure/api connection. I'm thinking I need to read what's being passed by the stored procedure as if it were an array, although I'm not too sure how this is done.

Stored Procedure:

USE [Capstone]
GO
/****** Object:  StoredProcedure [dbo].[getTopicsForSubject]    Script Date: 2/21/2021 11:30:03 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getTopicsForSubject]
    @SubjectID int
AS
BEGIN
    select  *
    from    Topic
    where   SubjectID = @SubjectID
    return; 
END

API Code

private static string ExecuteSPGetSubjectsForTopic(string queryString, string subjectID)
{
    string json = "";
    string connectionString = ConfigurationManager.AppSettings["dbconn"].ToString();

    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();

        // 1.  create a command object identifying the stored procedure
        SqlCommand cmd = new SqlCommand(queryString, conn);

        // 2. set the command object so it knows to execute a stored procedure
        cmd.CommandType = CommandType.StoredProcedure;

        // 3. add parameter to command, which will be passed to the stored procedure
        cmd.Parameters.Add(new SqlParameter("@SubjectID", subjectID));

        // execute the command
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            // iterate through results, printing each to console
            while (rdr.Read())
            {                    
                json = (string)rdr[0].ToString() + "|" + (string)rdr[1].ToString()+ "|" + (string)rdr[2].ToString() + "|" + (string)rdr[3].ToString();
            }
        }
    }

    return json;
}

JavaScript Code

function getTopicsForSubject()
{       
    var postObj = {
        subjectID: localStorage.getItem('myFutureCurrentSubject')
    };
    console.log(postObj);

    var req = new XMLHttpRequest();
    req.open('POST', 'https://localhost:44303/api/JSON/getTopicsForSubject', true);
    req.setRequestHeader('Content-Type', 'application/json');
    
    req.onreadystatechange = function() { // Call a function when the state changes.
        if (this.readyState === XMLHttpRequest.DONE && this.status === 200) {               
            console.log(req.response);                      
        }
    }
    
    req.send(JSON.stringify(postObj));
    
    return false;       
}

Upvotes: 0

Views: 493

Answers (1)

Arib Yousuf
Arib Yousuf

Reputation: 1285

You're reinitializing your JSON variable each time when reading a row. Try this:

json += (string)rdr[0].ToString() + "|" + (string)rdr[1].ToString()+ "|" + (string)rdr[2].ToString() + "|" + (string)rdr[3].ToString();

This is not the right way to return data. In JS you will still get this as a string and then parse it like this to get the actual values:

var array = req.response.split('|');
for (var i = 0; i < array.length; i++) {
    console.log(array[i]);
}

I would suggest you use a proper way to handle this data by return an HTTP response from API instead of a string. E.g. create a list and then populate it while reading from the reader and return it. Try this:

 List<object[]> topics = new List<object[]>();
 while (rdr.Read())
 {
    object[] row = new object[rdr.FieldCount];
    for (int i = 0; i < rdr.FieldCount; i++)
    {
        row[i] = rdr[i];
    }
    topics.Add(row);
 }
 return Ok(new { Data = topics });

Upvotes: 1

Related Questions