Web API Restful Service C# Perform SQL Query

Question

Working on a web api restful service.

I have a table called tests. The primary key is auto-increment. Each row contains a username and a test question. This DB is not really optimised, but it doesn't need to be.

Because the primary key is just an int to keep each row unique, there are many rows with duplicate usernames. This is fine.

I want to be able to return all rows with a matching username.

I want to be able to do it with a get request with the url: www.mywebsite.com/api/tests/{username}

The default controller methods in Visual Studio are only able to search by primary key to return one unique result.

This is something like what I want, but it doesn't work. 500 error. I'm not knowledgeable on debugging either, so point me in the right direction there if possible so I can provide more info.

    // GET: api/Tests/robert
    [ResponseType(typeof(Test))]
    public async Task<IHttpActionResult> GetTest(string id)
    {
        //This is the default approach that just searches by primary key
        //Test test = await db.Tests.FindAsync(id);

        /*This is roughly what I want. Compiles, but doesn't work.*/

        var query = "SELECT* WHERE id=" + id;
        Test test =  db.Tests.SqlQuery(query, id);
        if (test == null)
        {
            return NotFound();
        }

        return Ok(test);
    }

Let me know where I screwed up. I've been blundering over this for hours. I don't claim to be particularly good at any of this.

Rafa · Accepted Answer · 2016-05-30 18:12:27Z

1

Try declaring your method like this

[RoutePrefix("api")]
public class HisController : ApiController
{
    [HttpGet]
    public HttpResponseMessage Test(string name) {...}
}

Also, I would recommend You to use entity framework

[RoutePrefix("api")]
public class HisController : ApiController
{
    [HttpGet]
    public HttpResponseMessage Test(string name)
    {
        using (MyEntities bm = new MyEntities())
        {
            var usr = bm.Users.Where(u => u.Name == name ).ToList();

            if (usr.Count() > 0)
                return Request.CreateResponse(HttpStatusCode.OK, new
                {
                    Success = true
                        ,
                    Message = "Total users: " + usr.Count()
                        ,
                    Data = usr
                });

            return Request.CreateResponse(HttpStatusCode.NotFound, new { Success = false, Message = "No users found..." });
        }
    }
}

edited May 30, 2016 at 18:12

answered May 30, 2016 at 17:34

Rafa

4926 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

heartlandcoder Over a year ago

I would second the EF approach. The code from the OP is open to a SQL injection attack. You should never trust inputs and definitely not when you are sending them into a SQL query.

RizJa Over a year ago

He'll never hit this method with www.mywebsite.com/api/tests/{username} unless he changes the default bindings.. the RouteConfig needs to be updated if he wants to use this URL schema for accessing this method.

Rafa Over a year ago

Thats true, I assumed he had the right configuration for his class

[RoutePrefix("API")]     public class HisController : ApiController     {[HttpGet]  public HttpResponseMessage Test(string name) {...}}

Cammers Over a year ago

I already use EF actually. Sorry for not saying already. I changed default bindings already also. The problem is explicitly about performing the query. I simply do not know how to write it.

Cammers Over a year ago

Okay. Solved! It was just the line: var usr = bm.Users.Where(u => u.Name == name ).ToList(); That's all I needed. Thanks sdmon. I'm new to c# and some syntax looks scary to me.

Collectives™ on Stack Overflow

Web API Restful Service C# Perform SQL Query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related