Return a custom message from SQL Server stored procedure to Web API C#

Question

I have this API, which calls a stored procedure to insert a value and it has few PRINT statements in the stored procedure

public class ConfirmJobController : ApiController
{
    [HttpPost]
    public IHttpActionResult Post(JobConfirmation confirmation)
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
        {
            string outputMessage;

            con.Open();
            SqlCommand cmd = new SqlCommand("usp_PhoneApp_ConfirmBooking", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@p_jobId", confirmation.JobID);
            cmd.Parameters.AddWithValue("@p_jobStatus", confirmation.JobStatus);
            cmd.Parameters.AddWithValue("@p_createdDate", confirmation.CreatedDate);

            cmd.ExecuteNonQuery();

            con.InfoMessage += delegate (object sender1, SqlInfoMessageEventArgs e1)
            {
                outputMessage = e1.Message;
            };

            return outputMessage;

        }
    }
}

I would like get custom message from the stored procedure to the return value of the API. How can I do this?

currently the line return outputMessage gives an error with "use of unassigned variable and cannot implicitly convert string to IHttpActionResult"

Any help is greatly appreciated.

These are two very unrelated questions. First one is a duplicate of [What does “Use of unassigned local variable” mean?](http://stackoverflow.com/q/5710485/11683), the second is a duplicate of [Return a String in Web API 2](http://stackoverflow.com/q/35344226/11683) — GSerg, May 05 '17 at 09:27
you are only assigning the messages in the callback, therefor, it is not assigned, the compiler correctly identifies that it will only get assigned after the eventhandler gets hit. Also, string is not IHttpActionResult, however you could return (after receiving the message correctly) `Ok( outputMessage )` — Icepickle, May 05 '17 at 09:28
Also, mixing controllers with data access like that simply is not done, if you ever need the same functionality again, it will lead to code duplication and code maintenance 'hell' — Icepickle, May 05 '17 at 09:30
@Icepickle: while I agree with you in principle, I don't think we should be overly draconic about it. I can easily see myself doing the same thing for a minor throwaway or test project, and refactoring if/when the project grows and I need more structure. But yes, I usually also have services. — Alex Paven, May 05 '17 at 09:38
@AlexPaven Though I agree that for small projects it makes no sense for overly complex structures, it simply looks to me that many things could be reused already for other requests (like building up the connection), I was only pointing it out :) Didn't want to be draconic :) — Icepickle, May 05 '17 at 09:48
@Icepickle Thanks for the response, do you have a sample code by any chance. I understand both the issues, I just can't get my head around to come up with the code. — kas_miyulu, May 05 '17 at 09:52

score 1 · Accepted Answer · answered May 05 '17 at 09:35

1

To answer only the question in the title, you could add an output parameter to the stored procedure, that's what I usually use.

https://technet.microsoft.com/en-us/library/ms187004.aspx

The other issues are simply a matter of returning the correct type that the method declares. Return a ContentResult or some other object that implements the declared interface.

answered May 05 '17 at 09:35

Alex Paven

5,539
2
21
35

Thanks Alex, So you mean use the custom messages in the form of output parameters instead of PRINT statements and assign the output parameter from C# to the retutn ok(output_parameter) ? – kas_miyulu May 05 '17 at 09:59
1

Yes, you can read output parameters in ADO.Net by adding parameters defined as output to the command and reading their value after the command executes. The way you did things might work, I just never did it that way. – Alex Paven May 05 '17 at 10:12
I'm working on that way now. So there is no other way that I can get the SqlinfoMesssage to API return Ok()? – kas_miyulu May 05 '17 at 10:20
Like I said the SqlInfoMessage way could work. Simply initialize outputMessage to an empty string or null when you declare it, that should keep the compiler happy. Then you can return the outputMessage using Ok() and it should work, assuming SqlInfoMessage does what it's supposed to do (like I said I never used it). – Alex Paven May 05 '17 at 10:22

score 0 · Answer 2 · edited May 07 '17 at 11:00

0

You can make use of ExecuteNonQuery to return int value, method looks like inserting data via stored procedure. Alternately you can also make use out parameter in stored procedure for any specific success message.

[HttpPost]
public IHttpActionResult Post(JobConfirmation confirmation)
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        string outputMessage;

        con.Open();
        SqlCommand cmd = new SqlCommand("usp_PhoneApp_ConfirmBooking", con);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@p_jobId", confirmation.JobID);
        cmd.Parameters.AddWithValue("@p_jobStatus", confirmation.JobStatus);
        cmd.Parameters.AddWithValue("@p_createdDate", confirmation.CreatedDate);

        if(cmd.ExecuteNonQuery() > 0)
           outputMessage = "Success";
        else
           outputMessage = "Failed";

        return Ok(outputMessage);
    }
}

edited May 07 '17 at 11:00

marc_s

732,580
175
1,330
1,459

answered May 05 '17 at 09:45

Ankush Madankar

3,689
4
40
74

Thanks for the reply. But I want to return the custom messages from my Stored Procedure :( – kas_miyulu May 05 '17 at 09:54
How is your store procedure looks like? Does it have output parameter or message return from it in table? – Ankush Madankar May 05 '17 at 09:57
I have IF v_user > 0 PRINT 'User Exist' ELSE PRINT 'New User' – kas_miyulu May 05 '17 at 10:01
Add your store procedure in your question, replace actual table names. Looks like you required to have output parameter in store procedure – Ankush Madankar May 05 '17 at 10:03

Return a custom message from SQL Server stored procedure to Web API C#

2 Answers2