Web API to return result from SQL Database

Question

I am trying to create a Web API which will get an input and tries to run Select query in SQL Database. The table in the DB has three fields but I need only two fields in the response.Below is the code I am trying

public async Task<IHttpActionResult> Get(string LabName){
string strcon = ConfigurationManager.ConnectionStrings["CDBConnection"].ConnectionString;
SqlConnection DbConnection = new SqlConnection(strcon);
SqlDataReader reader = null;
DbConnection.Open();

using (SqlCommand command = new SqlCommand("SELECT Acc, CSeq FROM [dbo].[c_Account] WHERE Acc = @AccName", DbConnection))
{
    command.Parameters.Add(new SqlParameter("AccName", LabName));
    reader = command.ExecuteReader();
    List<QueryResult> qresults = new List<QueryResult>();

    while (reader.Read())
    {
        QueryResult qr = new QueryResult();
        qr.AccountID = reader["AccountID"].ToString();
        qr.CounterSeq = ((int)reader["CounterSeq"]).ToString("000");
        qresults.Add(qr);
    }
    DbConnection.Close();
    //build json result
    return Ok(qresults);
}
}

The issue here is it pulls all the three fields in the response as

[{"Acc":"xyz","CSeq":"123","Year":null}]

But just expecting the response as

[{"Acc":"xyz","CSeq":"123"}]

Also is it possible to return the response something like The CSeq associated with Acc xyz is 123

Where is the rest of your code? The code that is responsible for creating the JSON (what you're asking about) is not here. — Robert Harvey
– Robert Harvey, Commented Jan 31, 2017 at 18:45
@RobertHarvey - its in a web api method that returns an OkNegotiatedContentResult<T> (via method Ok) so the caller determines the content type with the http header content-type. Web api (with the default configuration) will automatically generate a json result from the passed in model using json.net (another default) as the serializer. — Igor
– Igor, Commented Jan 31, 2017 at 18:47

Igor · Accepted Answer · 2017-01-31 18:42:03Z

Modify your QueryResult model and add JsonIgnore to the property Year.

public class QueryResult {
    [JsonIgnore]
    public int? Year {get;set;} // guessed the type
    // the rest of your properties in the model
}

I assumed you are using json.net (the default) serializer for your web api project.

Also is it possible to return the response something like The CSeq associated with Acc xyz is 123

You would have to add a string property to your model that is being returned and format that text yourself. There is nothing built into the web api framework that creates human readable text out of thin air. It really should be a presentation layer function built into your html/script and not anything on the server though.

Collectives™ on Stack Overflow

Web API to return result from SQL Database

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related