DbWebApi

-- -- -- -- -- -- -- -- -- -- -- -- Extension to ASP.NET Web API (RESTful)

What is it?

With DbWebApi you can access SQL Server or Oracle package stored procedures in a managed way out of the box (like http://BaseUrl/fully_qualified_name_of_stored_procedure/mediatype) from any http client, get the results as JSON, BSON, XML, CSV, Excel xlsx, JSONP, or any text generated by Razor dynamic templating. For examples,

SQL Server:

• http://dbwebapi.dev.com/sqldev/TestDb.dbo.your_sp/json
• http://dbwebapi.dev.com/sqldev/TestDb.dbo.your_sp/bson
• http://dbwebapi.dev.com/sqldev/TestDb.dbo.your_sp/jsonp?callback=jsFunc1
• http://dbwebapi.dev.com/sqldev/TestDb.dbo.your_sp/xml
• http://dbwebapi.dev.com/sqldev/TestDb.dbo.your_sp/xlsx?filename=Rpt2015
• http://dbwebapi.dev.com/sqldev/TestDb.dbo.your_sp/csv?resultset=0&filename=Rpt2015
• http://dbwebapi.dev.com/sqldev/TestDb.dbo.your_sp/razor?RazorTemplate=outTemplateSpParameter

Oracle:

• http://dbwebapi.dev.com/oradev/test_schema.prj_package.your_sp/json
• http://dbwebapi.dev.com/oradev/test_schema.prj_package.your_sp/bson
• http://dbwebapi.dev.com/oradev/test_schema.prj_package.your_sp/jsonp?callback=jsFunc1
• http://dbwebapi.dev.com/oradev/test_schema.prj_package.your_sp/xml
• http://dbwebapi.dev.com/oradev/test_schema.prj_package.your_sp/xlsx?filename=Rpt2015
• http://dbwebapi.dev.com/oradev/test_schema.prj_package.your_sp/csv?resultset=0&filename=Rpt2015
• http://dbwebapi.dev.com/sqldev/test_schema.prj_package.your_sp/razor?RazorTemplate=outTemplateSpParameter

The input parameters of your stored procedure can be supplied in URL query-string or in request body by JSON (recommended), XML or multipart/form-data. Oracle PL/SQL Associative Array Parameter (Bulk Bind - for bulk insert, bulk update) and SQL Server Table-Valued Parameter are natively supported for high-performance.

DbWebApi handles type inferencing, for example, if a client passes in a Base64 string and your stored procedure expects to receive a binary input, the Base64 string is automatically decoded into the binary parameter.

If the client further wraps a batch of parameter sets into an array as the HTTP request body, the server will sequentially call the stored procedure by each parameter set in the array, and wrap all the result sets in a more outer array before return to the client.

---

Contents:

• Overview
• What are the benefits of DbWebApi?
• Usage - server side
  • ApiController
  • Web.config
  • HTTP Request
    • Url
    • Input Parameters
      • Simple Parameters
      • Array of Parameter Sets
      • PL/SQL Associative Array Parameters
      • Table-Valued Parameters
      • Summary of Input Parameters and Execution Modes
    • Accept Response MediaType
      • JSON
      • BSON (only available to targetFramework="4.5" or higher - ASP.NET Web API 2)
      • JSONP
      • XML
      • Excel .xlsx
      • CSV
      • Razor Templating
      • Other MediaTypes
        • Control in low level
        • Control in high level
  • HTTP Response
    • Response data internal structure
    • Response body formats
      • application/json, text/json
      • application/xml, text/xml
      • text/csv
    • Property Naming Convention
    • Exceptions
    • Permission Control
    • UserName
  • Performance
  • Bulk Manipulation
    • Quasi Bulk (BulkExecute action)
    • Thorough Bulk - Table-Valued Parameters or PL/SQL Associative Array Parameters
• Clients
  • Swagger UI
  • .Net Client
  • JavaScript Client
    • Cross-domain
      • CORS
      • JSONP
  • AngularJS Client
  • Angular 6+ Client
  • Python(3+) Client
  • PowerShell Client
    • Bulk Data Post Back
      • Hundreds or less
      • Thousands or more
        • Table-Valued Parameters
        • PL/SQL Associative Array Parameters
  • Windows Command Line Client
  • Power Query Client - Power BI
• Restrictions
• NuGet
  • Server packages
  • Clients
• SymbolSource
• Examples

---

Overview

DbWebApi is a .Net library that implement an entirely generic Web API for HTTP clients to call database (Oracle & SQL Server) stored procedures or functions out-of-the-box without any configuration or extra coding, the http response JSON or XML will have all Result Sets, Output Parameters and Return Value. If client request a CSV format (accept: text/csv), the http response will transmit one result set as a CSV stream for large amounts of data. DbWebApi also supports xlsx (Excel 2007/2010) format response for multiple resultsets (each resultset presents as an Excel worksheet). While being regarded as a gateway service, DbWebApi reflects in two directions: Data Access Gateway and Media Format Gateway.

In other words, DbWebApi provides an alternative way to implement your Web APIs by implementing some stored procedures or functions in database. The DbWebApi will expose these stored procedures or functions as Web APIs straight away.

In essence, DbWebApi is still a ASP.NET Web API instead of a naked tunnel for database. It just be generic, and provides a few extension methods to your ASP.NET Web API services.

• Security:
  The security of DbWebApi is entirely dependent on what you can do in ASP.NET Web API. What security you did for your existing Web API services, should still apply in the DbWebApi. For information about access control, please see the [Permission Control] section later in this wiki.
  Some people may concern about the name of some stored procedures being exposed to the public. Hereby, it is necessary to clarify that all the exposed names of stored procedures are essentially some names of public services. No matter how hard the service provider try to hide/disguise the name of service function, as long as a service function is a businesses need for the service consumers to invoke, the service consumers always can get the real intention of the service according to its effect. Hiding/disguising service name make no contribution to improve security, it's fundamentally different from hiding any piece of credential information.

• Data Contract:
  Since there is no setup at all, the domain entities returned from DbWebApi simply reflect the result sets returned from your stored procedure. So the data contract is driven by your stored procedure.
  To isolate the downstream consumers from the source raw schemas, you can slimly achieve the isolation in your stored procedure only once, or do some data transportation once after DbWebApi.
  Actually, the contract transformation can be done in any one node of the intermediate links of your data flow. Just to keep the isolation simple, and reduce dogmatic data-isolation repeated in multiple links of a closed process chain over and over again.

What are the benefits of DbWebApi?

• The underlying tenet:
  Less coding, less configuration, less deployment, less maintenance.
  The conciseness of using DbWebApi is down-to-earth for hands-on developers, to access database stored procedures or functions is completely coding-free and configuration-free. Don't need to explicitly specify any metadata about database objects (such as parameters type, size, direction... or columns attributes) by coding or configuration, don't need to write any controller for handling new data models from database, don't need to write any method for calling new stored procedures or functions ... No more dazzling The Emperor's New Services(Clothes) to test(fitting), deploy or maintain.
• In database applications area, there are a large number of scenarios without substantial logic in data access web services, however they wasted a lot of our efforts on very boring data-moving coding or configurations, we've had enough of it. Since now on, most of thus repetitive works can be dumped onto DbWebApi. Let the application developer focus on the important thing, which is building the functionality that's needed, instead of focusing on all the plumbing underneath it.
• Unlike WCF Data Services or other similar web services, DbWebApi has no design time within the service itself. In terms of the overall system, stored procedures design has already undertaken the corresponding part of contract design when stored procedure based