0

I have a stored procedure on my sql database which expects a parameter and with that parameter it returns data from a view.

My stored procedure:

create procedure find_student @id varchar(6)
as
begin
select name,lastname,birthday,email,phone,city,country,university,study_year from v_scholarship 
where id = @id
end

My api controller:

    [HttpGet]
    public IHttpActionResult MyId(string id)
    {

        string config = ConfigurationManager.ConnectionStrings["mycon"].ConnectionString;
        using (SqlConnection sqlcon = new SqlConnection(config))
        {
            SqlCommand cmd = new SqlCommand("find_student", sqlcon);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", id);
            sqlcon.Open();
            cmd.ExecuteNonQuery();
            sqlcon.Close();
            return Ok();
        }

    }

It doesn't return any error it just returns blank. I think it is because I am not including anything at return OK() but I don't know which attribute to put in those brackets.Thank you.

EDIT:

I tried all the answers and then I found something else. Leaving this here just for the one's that may need it in the future.

[HttpGet]
public IHttpActionResult MyId(string id)
{
    schoolEntity = myenitity = new schoolEntity();
    var result = myenitity.find_student(id).ToList();
    return Ok(result);
}

Thank you to everyone for your time, effort and patience. I really appreciate it.

Improve this question
6
  • 1
    The data access library is ADO.NET, not EF nor ASP.NET Web API. EF is an ORM that uses ADO.NET to execute queries. As for the code you posted, it works exactly as written. return Ok(); returns an empty response. The code even uses ExecuteNonQuery, which means the query won't return any results. What are you trying to do? Since you used the EF tag why not actually use Entity Framework? Or a micro-ORM like Dapperr? Commented Aug 30, 2021 at 11:54
  • The controller where I am trying this code is in an web api. I want to return the data that this method will get from database when it's executed. When it's done, It is supposed to show the data in xml format ( as far as I know, the data in web api's can be read in xml/json format only ). Commented Aug 30, 2021 at 11:58
  • 1
    And cmd.ExecuteNonQuery(); doesn't return the result of the query in the sproc, and you don't assign the returned result to any variable. This code looks far too tentative. Using plain ADO.Net isn't the best option anyway. With Dapper it's almost trivial to get data from a stored procedure. I'd explore that path. Commented Aug 30, 2021 at 11:59
  • Okay I may have a lot of bugs and errors here and misunderstandings because I'm new into all this. But can you show me the right way I can get the actual data from a stored procedure in an web api using or not using entity framework. Commented Aug 30, 2021 at 12:01
  • consultwithgriff.com/dapper-stored-procedures Commented Aug 30, 2021 at 12:05

3 Answers 3

Reset to default
1

To get data you need a datareader and data structure to load. Create a Student class

class Student
{
    public string name {get;set;} 
    public string lastname {get;set;}
    ...
}

and replace

 cmd.ExecuteNonQuery();

with

var studenList= new List<Student>();

SqlDataReader reader = cmd.ExecuteReader();  
 while (reader.Read())  
  {  
                var student=new Student();
             student.name=reader["name"].ToString();
             student.lastname=reader["lastname"].ToString();
                ... and so on
            
             studentList.Add(student);
             
    }  
  
            //Release resources  
            reader.Close();  
            conn.Close();
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

You need a using block
@Charlieface Thank you. it is just a hint.
1

The question's code doesn't retrieve the query's results or return anything to the caller. In fact, ExecuteNonQuery explicitly says that the SQL command won't return any results.

Even if this changes to produce results with ExecuteReader, the results will have to be mapped to actual objects.

The code can be simplified if a library like Dapper is used to construct the command in the background, execute it and map the results:

using (SqlConnection sqlcon = new SqlConnection(config))
{
    var student = sqlcon.Query("find_student", new {id = someId},
        commandType: CommandType.StoredProcedure)
                        .SingleOrDefault();
    return Ok(student);
}

That's it. Dapper takes care of opening and closing the connection as needed. Behind the scenes it creates an sp_executesql call that calls the stored procedure passing the parameters by name. The results in this particular case are returned as objects that implement IDictionary<string,object>, so they can be serialized to XML or JSON.

Actually serializing the response to XML or JSON is ASP.NET Web API's job, not the action's. Web API will serialize the response into whatever format the caller's Accept-Content-Type header requests.

It's far more common to use Dapper with a strongly typed result class, eg Student :

class Student
{
    public string name {get;set;} 
    public string lastname {get;set;}
    ...
}

...
var students = sqlcon.Query<Student>("find_student_by_course", 
    new { course= "abc"},
    commandType: CommandType.StoredProcedure);
foreach(var student in students)
{
...
}
Improve this answer

7 Comments

Thank you for all this information. How can I put my parameter that I give it from url to this new {id = 1}, I don't want a static parameter.
Just pass the parameter or variable
I have an error now which says sqlcon.Query --- SqlConnection does not contain a definition for "Query".
Did you add Dapper? Did you check the link I provided?
Yes I added Diapper. Started using it. But now it's not getting my parameter. It is still saying that "Stored procedure (find_student) expects a parameter (id) which was not supplied". What does the first id mean at the new { id = someid } ?
|
0

Here I have connected by my database (Microsoft SQL server) using ADO.Net Entity framework and I have created the stored procedure "MemberShoppingCart" which filter the record based on the member id and return the product ordered by particular member. Here I have passed memberId as parameter to my stored procedure.

   public IHttpActionResult GetInvoiceDetailsById(int id)
    {
        try
        {
            Tbl_Members member = new Tbl_Members();
            List<InvoiceDetails> cd = _unitofwork.GetRepositoryInstance<InvoiceDetails>().GetResultBySqlprocedure("MemberShoppingCart @memberId", new SqlParameter("memberId", System.Data.SqlDbType.Int) { Value = id }).ToList();
            return Ok(cd);
        }

        catch 
        {
            return StatusCode(HttpStatusCode.NoContent);
        }

    }

This is the similar way to pass the parameter to the stored procedure in ASP .NET Web API and get the results.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.