How to design Get method to get data from different tables through API

Question

I have designed an API to get data from different tables using GET method requests. How to design Get method to get data from different tables?

Here is my controller:

namespace WebAPIDemo.Controllers
 {    
  public class ValuesController : ApiController
  {
    static public IEnumerable<Product> GetProducts()
    {
        IList<Product> product = new List<Product>();

        SqlConnection conn = new SqlConnection();
        Product productInfo = new Product();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
        string sqlText = "select * from Products";
        SqlCommand sqlCmd = new SqlCommand(sqlText, conn);
        conn.Open();
        SqlDataReader reader = sqlCmd.ExecuteReader();
        while (reader.Read())
        {
            productInfo.productName = reader[0].ToString();
            productInfo.brandName = reader[1].ToString();
            productInfo.manufacturingYear= reader[2].ToString();
            product.Add(productInfo);

        }
        conn.Close();

        return product;

    }

    static public IEnumerable<Customer> GetCustomer()
    {
        IList<Customer> customer = new List<Customer>();

        SqlConnection conn = new SqlConnection();
        Customer customerProfile = new Customer();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString;
        string sqlText = "select * from customerProfile";
        SqlCommand sqlCmd = new SqlCommand(sqlText, conn);            
        conn.Open();
        SqlDataReader reader = sqlCmd.ExecuteReader();
        while (reader.Read())
        {
            customerProfile.customerFirstName = reader[0].ToString();
            customerProfile.customerLastName = reader[1].ToString();

            customer.Add(customerProfile);


        }
        conn.Close();

        return customer;

    }

 public IEnumerable<Product> GetAllProducts()
    {
        return GetProducts();
    }

    public Product GetProduct(int id)
    {
        return GetProducts().ElementAt(id);


    }

    public Customer GetCustomer(int id)
    {
        return GetCustomer().ElementAt(id);
    }


}

}

The GetAllProducts method gives me the following output:

<ArrayOfProduct>
 <Product>
  <brandName>ParleG</brandName>
  <manufacturingYear>2018</manufacturingYear>
  <productName>Biscuits</productName>
 </Product>
 <Product>
  <brandName>BMW</brandName>
  <manufacturingYear>2018</manufacturingYear>
  <productName>Cars</productName>
 </Product>
</ArrayOfProduct>

the GetProduct method gives me the following output:

<Product>
 <brandName>BMW</brandName>
 <manufacturingYear>2018</manufacturingYear>
 <productName>Cars</productName>
</Product>

After that I designed GetCustomer method for customer table

public Customer GetCustomer(int id)
{            
    return GetCustomer().ElementAt(id);
}

it gives me the following error:

Multiple actions were found that match the request: Get on type WebAPIDemo.Controllers.ValuesController GetCustomer on type WebAPIDemo.Controllers.ValuesController.

What changes do I need to make to resolve this error and so that I get values from different tables through API.

This issue relates to the framework being able to determine which method to use based on the request type against the resources here. Are these all being defined on the same controller? is the WebApi? Are you familiar with or opposed to using Route Prefixes? Have you not defined `HttpGetAttribute` not `HttpPostAttribute` on any of these methods? — Brett Caswell, Oct 27 '19 at 06:19
Where do you wrote GetCustomer() for 'return GetCustomer().ElementAt(id);' — Ajoe, Oct 27 '19 at 06:41
I'm trying this from my localhost. Hence url is: http://localhost:54894/api/values/1 — A. F. M. Golam Kibria, Oct 27 '19 at 07:02
I am very new in designing api. I have defined all these on the same controller. I wrote the GetCustomer() in the "public class ValuesController : ApiController' class. — A. F. M. Golam Kibria, Oct 27 '19 at 07:59
As a novice I need to know the best way to design this scenario in a API — A. F. M. Golam Kibria, Oct 27 '19 at 08:19
Post the full controller code. I think you made some basic routing error we can't see — Caius Jard, Oct 27 '19 at 08:42

score 1 · Accepted Answer · answered Oct 27 '19 at 08:33

You seem to run into a technical issue here where the framework doesn't know which Api action to choose since there are two that match the route (like @Brett Caswell mentions in the comments). If you need both actions on the same controller (same endpoint/route) then you can use route mapping where your URL references a specific action in the path, in your case something like:

.../api/customer/GetCustomer/1

For more info on this see https://stackoverflow.com/a/12185249/2877982

Whether this is good design is another question. For me it makes sense to get a collection of something from the same controller which also gives me a single of that thing when I give it an id.

For me I name my methods that get collections of something so they have an S on the end — Caius Jard, Oct 27 '19 at 08:44
Yes, after defining different controller it is working fine. Thanks — A. F. M. Golam Kibria, Oct 28 '19 at 08:11

How to design Get method to get data from different tables through API

1 Answers1