Towards an efficient interface between application and database

  • Phil Factor

    SSC-Insane

    Points: 20084

    Comments posted to this topic are about the item Towards an efficient interface between application and database

    Best wishes,
    Phil Factor
    Simple Talk

  • tsalha

    SSC Rookie

    Points: 43

    Hi Phil,

    what kind of connector are you using to communicate with the SQL database when writing c#?

    System.Data.SqlClient?

    I am asking, because I am wondering how you are calling the stored procedures from your code. I guess you are pasting the JSON objects to an nvarchar procedure parameter via deserialization?

    Cheers, Tarek

  • Phil Factor

    SSC-Insane

    Points: 20084

    tsalha  Yes, I'm using System.Data.SqlClient generally for this. I use a temporary stored procedure on the connection, (create or edit) and then execute it with parameters. I usually just have two parameters, one for the input JSON and one for the output. You can get away with one input parameter. One can either use an output parameter to get the JSON return, or define a variable in code for the output parameter, and  simply do a SELECT on the output of the stored procedure, catching the output as a return from the ExecuteScalar().

     

    Best wishes,
    Phil Factor
    Simple Talk

  • Steve Collins

    SSC Eights!

    Points: 889

    Thanks Phil I'm a big fan of your articles.  I completely agree JSON can be a key element of efficient application data access.  As far as data validation goes tho...  imo and afaik Sql Server's current JSON features set is quite complete.  Of course, this could be legitimately disagreed with and for sure I'm extremely interested in counterarguments.

    Are the following validation steps [applied in this order] sufficient to reasonably ensure data integrity?  App sends json document to SQL then:

    1) is the JSON document valid? -- ISJSON() function --> if yes, proceed to deserialize and parse the document

    2) are the correct data elements present? -- STRICT optional parameter(s) to Json parse functions --> parse data elements

    3) can the data be persisted? -- DB Engine --> DB Engine DML

    Whether or not the document is valid compared to its own schema doesn't really enter the picture.  It would be extra information.  Are the others listed "Python, Go, Java, PHP, .NET. Even MongoDB.." transaction engines?   Maybe Mongo I don't know.

    If Sql Server could generate a JSON schema for any query that uses FOR JSON that would be amazing.  I still refer to (and use some modified code from) this article you wrote about 2 years ago:

    https://www.red-gate.com/simple-talk/blogs/producing-data-and-schemas-in-json-array-of-array-format/

    Microsoft could expand on this and make the functionality built in.  That would be great schema support I'd love to see.

  • Phil Factor

    SSC-Insane

    Points: 20084

    scdecade

    Yes, IsJSON() is a good start. The 'Strict' option is an excellent precaution against Typos, but neither of these tackle the issues of how your data should behave.  In a database, you will be dealing with special datatypes that are merely based on SQL Server's built-in datatypes. When you specify and integer for a month, for example and it has to be constrained between 1 and 12, it isn't sufficient to know that it is an integer.  A filename mustn't have certain characters in it and so on. A credit card must conform to Luhn's algorithm. Many columns will be have values that are based on enumerations. (e.g. Spring, Summer, Autumn, Winter).  If you are reading data from a table and that table has constraints on it you can be confident that your data is clean. When you are processing invoices, you will know that they are dated in a very narrow range of dates, and are unlikely to date from Roman times. The invoice amount will have certain rules. You wouldn't want to get that wrong.  If you are reading from JSON, any basic security will lead you to do a number of check constraints

    I produced a procedure for generating a JSON Schema from SQL Server, here: https://www.red-gate.com/simple-talk/sql/t-sql-programming/transferring-data-with-json-in-sql-server/. This is just the start because it is impossible to map all SQL constraints directly to a JSON schema. It really just checks the datatypes rather than let the stored procedure error-out. Because JSON Schema allows regexes, you can do a fair amount but you soon reach the limits.

     

    Best wishes,
    Phil Factor
    Simple Talk

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply