locked
json api datatype in request response msg RRS feed

  • Question

  • User-583959464 posted

    I am using asp.net core to develop api frameworks to call stored procedure and then return result to caller. I am asking the parameter data type in api world.

    for content type, what is the choice that the caller can define? is that bounded by the program. for the content type application/json

    do I need to provide the data type to client ? as it is calling stored procedure,  

    And I can find that there are input and output with data type on program , should I need to provide that data type to client if the content type is application/json in respond message?

    another question,

    can I define LONG data type in api program  for scope_identity() value in SQL server?

    Tuesday, March 2, 2021 4:24 PM

All replies

  • User753101303 posted

    Hi,

    It could be best to ask each question in a separate thread so that it is easier to follow up as needed on each question separately.

    1) the content type allows to tell what is your payload. If configured for this you could for example use the same service using either json or xml (and the API could respond with json or XML).

    2) unclear. If you mean the content type it is used as well to tell the client what is the payload (xml, json, html, an image etc). Or this is some other question but i'm not sure how it related to stored procedure. If you have a problem tell what it is.

    3) unclear as well. See https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Type and https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/accept. I should be done for you. Once again if you have a problem it's often much better to just discuss directly about it rather than asking an obscur question about how things are supposed to work.

    4) Yes, scope_identity() must be able to use the biggest value of a type on which you could use "identity" - checking it  actually returns a numeric(38,0) value
    Still it seems to make sense to match what you are using ie if the identity value in your table is an int, it makes sense to use an int to return this value rather than a bigint.

    Have maybe a look at https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-web-api?view=aspnetcore-5.0&tabs=visual-studio ?

    Tuesday, March 2, 2021 5:50 PM
  • User-583959464 posted

    Hi,

    It could be best to ask each question in a separate thread so that it is easier to follow up as needed on each question separately.

    1) the content type allows to tell what is your payload. If configured for this you could for example use the same service using either json or xml (and the API could respond with json or XML).

    2) unclear. If you mean the content type it is used as well to tell the client what is the payload (xml, json, html, an image etc). Or this is some other question but i'm not sure how it related to stored procedure. If you have a problem tell what it is.

    3) unclear as well. See https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Content-Type and https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/accept. I should be done for you. Once again if you have a problem it's often much better to just discuss directly about it rather than asking an obscur question about how things are supposed to work.

    4) Yes, scope_identity() must be able to use the biggest value of a type on which you could use "identity" - checking it  actually returns a numeric(38,0) value
    Still it seems to make sense to match what you are using ie if the identity value in your table is an int, it makes sense to use an int to return this value rather than a bigint.

    Have maybe a look at https://docs.microsoft.com/en-us/aspnet/core/tutorials/first-web-api?view=aspnetcore-5.0&tabs=visual-studio ?

    if one of the output of stored procedure will return the scope_identity () value, then should I set the data type to LONG in for this output value in asp.net core framework ? and should I tell the caller that the data type of that field is LONG in API document?

    Wednesday, March 3, 2021 4:02 AM
  • User753101303 posted

    I meant I would just use the type that is matching my column type ie :

    • if using INT IDENTITY I would use an int
    • If using BIGINT IDENTITY I would use a long

    SCOPE_IDENTITY must be able to return the biggest value an identity column could generate but if using INT IDENTITY it will return a value that can be stored inside an int variable.

    Wednesday, March 3, 2021 8:21 AM
  • User1686398519 posted

    Hi 20141113, 

    1. 20141113

      content type, what is the choice that the caller can define

      20141113

      is that bounded by the program

      20141113

      do I need to provide the data type to client

      20141113

      provide that data type to client if the content type is application/json in respond message?
      1. Web API can handle request and response data in different formats, but by default, Web API can handle JSON and XML data.
      2. You can specify the data format by specifying the Media Type.
        • client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
      3. You can click this link to learn more.
    2. 20141113

      I define LONG data type in api program  for scope_identity() value in SQL server?
      1. I suggest you can return a string type, and then convert the value to a long type.
      2. I wrote an example based on your needs, you can refer to it.
        1. API
          •     [Route("api/[controller]")]
                [ApiController]
                public class TestAPIController : ControllerBase
                {
                    public DailyCoreMVCDemoContext _db;
                    public TestAPIController(DailyCoreMVCDemoContext db)
                    {
                        _db = db;
                    }
                    [HttpGet("testStoredProcedure")]
                    public string testStoredProcedure(int test1,string test2)
                    {
                        var connectionstring = "Server=(localdb)\\mssqllocaldb;Database=DailyCoreMVCDemoContext;Trusted_Connection=True;MultipleActiveResultSets=true";
                        SqlConnection sqlConnection = new SqlConnection(connectionstring);
                        sqlConnection.Open();
                        var command = new SqlCommand("Test", sqlConnection);
                        command.Parameters.AddWithValue("@value", test1);
                        command.Parameters.AddWithValue("@time", test2);
                        command.Parameters.AddWithValue("@SCOPE_IDENTITY", 0).Direction = ParameterDirection.Output;
                        command.CommandType = CommandType.StoredProcedure;
                        command.ExecuteNonQuery();
                        string id = command.Parameters["@SCOPE_IDENTITY"].Value.ToString();
                        return id;
                    }
                }
        2. Stored procedure
          • CREATE PROCEDURE [dbo].[Test]
            	@value int,
            	@time nvarchar(50),
            	@SCOPE_IDENTITY int output
            AS
            BEGIN
            INSERT INTO dbo.TestUsers(value,Time)
            VALUES (@value,@time);
            SELECT @SCOPE_IDENTITY=SCOPE_IDENTITY();  
            END
        3. Request
          •     public class Test1Controller : Controller
                {
                    private readonly IHttpClientFactory _clientFactory;
                    public CustomModelController(IHttpClientFactory clientFactory)
                    {
                        _clientFactory = clientFactory;
                    }
                    public async Task<IActionResult> Index()
                    {
                        var request = new HttpRequestMessage(HttpMethod.Get,
                           "https://localhost:44318/api/TestAPI/testStoredProcedure?test1=2&test2=testdata");
                        var client = _clientFactory.CreateClient();
            var response = await client.SendAsync(request); if (response.IsSuccessStatusCode) { var data = await response.Content.ReadAsStringAsync(); var id = long.Parse(String.IsNullOrEmpty(data)?"0":data); } return View(); } }
        4. Here is the result. 

    Best Regards,

    YihuiSun

    Wednesday, March 3, 2021 10:20 AM
  • User-474980206 posted

    you should try not use a long with json. the number datatype in json is defined as a 64 bit float (with only 54 bits for the mantissa). as the json is really only a string, the producers and consumers can decide to pass arbitrarily  precision numbers. 

    so if you use a long as a number in your api, you will need to document it well otherwise the value may change due to precision.

     

    Wednesday, March 3, 2021 4:27 PM
  • User-583959464 posted

    you should try not use a long with json. the number datatype in json is defined as a 64 bit float (with only 54 bits for the mantissa). as the json is really only a string, the producers and consumers can decide to pass arbitrarily  precision numbers. 

    so if you use a long as a number in your api, you will need to document it well otherwise the value may change due to precision.

     

    do you mean my asp.net coding defined output parameter data type as LONG, even I used postman and set header content to 'appication/json' , I still need to document that parameter data type to LONG to the api caller, right?

    Thursday, March 4, 2021 3:59 AM
  • User753101303 posted

    Still unclear. application/json just tells your payload follows this syntax : https://www.json.org/json-en.html

    It doesn't tell anything about each particular field found in this json document. If a value really needs to be a long provide a sample. For example the "paste json as a class" In Visual Studio with:

    {
    "a":3000000000,
    "b":300000000 
    }
    will use a long for the a property (as it it pastr int.MaxValue) and an int for the b property.  Else most developers would expect an int and it will fail if suddenly you return them a long.

    Now it seems you were talking about identity values for which a BIGINT IDENTITY seems overkill in most cases. With an INT IDENTITY you are go for creating a row each second during 68 years

    Thursday, March 4, 2021 1:59 PM
  • User-474980206 posted

    do you mean my asp.net coding defined output parameter data type as LONG, even I used postman and set header content to 'appication/json' , I still need to document that parameter data type to LONG to the api caller, right?

    yes. If you used JavaScript to call the api, it would load it into a 64 bit float and could lose digits. I map longs to json strings to avoid the issue.

    Thursday, March 4, 2021 3:50 PM