How to get JSON response for column names in database table

Question

I want to write a method in my controller class on the API side to return a list of all the column names. How would the method look like and what will it return? And how will I retrieve this 'list' on the client side?

private CoreGradingDBEntities db = new CoreGradingDBEntities();

// GET: api/
public IQueryable<Account> GetAccounts()
{
  return db.Accounts;
}


//Get field names
//What is the response type
public IHttpActionResult GetFieldNames()
{
  //Query goes here, gets executed and returns all column names?
}

// GET: api/Accounts/5
[ResponseType(typeof(Account))]
public IHttpActionResult GetAccount(string id)
{
    Account account = db.Accounts.Find(id);
    if (account == null)
    {
         return NotFound();
    }
    return Ok(account);
}

A query that returns column names that works that I want to execute and get as a HTTP response.

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'Account'

ORDER BY ORDINAL_POSITION

Answer 1

There are two ways to accomplish this task:

• The first one is to call SQL query from your code directly by using either a .NET data provider or by adding this query to your EF model.

• Another one is to use reflection to access the Account type properties:

  typeof(Account).GetProperties(BindingFlags.Public|BindingFlags.Instance)
  
  

Answer 2

Answer

So I have added a new controller class which is a custom class not dependent on the DB. I have also added a new route to the WebApiConfig.cs file. As well as a new "template" class. Here is what I have.

WebApiConfig.cs (added code)

Note that the id is required. I will use this for the table name I wish to get the field names from.

config.Routes.MapHttpRoute(
name: "FieldValues",
routeTemplate: "api/FieldValues/id",
defaults: new { controller = "FieldValues"}
);

FieldValues.cs (new class)

    public class FieldValues
    {
        public string COLUMN_NAME { get; set; } //this "variables" name is same as in query. Note: if the set is not present nulls would be returned
    }

FieldValuesController.cs (new controller)

    public class FieldValuesController : ApiController
    {
        private CoreGradingDBEntities db = new CoreGradingDBEntities();

        [Route("api/FieldValues/id")]
        public IList<FieldValues> Get(string id)
        {
            var response = db.Database.SqlQuery<FieldValues>("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + id + "' ORDER BY ORDINAL_POSITION").ToList();
            return response;
        }
    }

JSON Response

[{"$id":"1","COLUMN_NAME":"ID"},{"$id":"2","COLUMN_NAME":"DepartmentID"},{"$id":"3","COLUMN_NAME":"PermissionID"},{"$id":"4","COLUMN_NAME":"Username"},{"$id":"5","COLUMN_NAME":"PasswordHash"},{"$id":"6","COLUMN_NAME":"PasswordResetToken"},{"$id":"7","COLUMN_NAME":"TokenValid"},{"$id":"8","COLUMN_NAME":"Email"},{"$id":"9","COLUMN_NAME":"EmailConfirmed"},{"$id":"10","COLUMN_NAME":"PhoneNumber"},{"$id":"11","COLUMN_NAME":"PhoneNumberConfirmed"},{"$id":"12","COLUMN_NAME":"TwoFactorEnabled"},{"$id":"13","COLUMN_NAME":"AccessFailedCount"},{"$id":"14","COLUMN_NAME":"LocoutEndDate"},{"$id":"15","COLUMN_NAME":"LockoutEnabled"}]

GET request

api/FieldValues/id?id={id}

Uses

api/FieldValues/id?id=Account //the 'Account' can be replaced by your table name you wish to get the values from