Skip to content

Home

Jump to bottom Edit New page
Abel Cheng edited this page Jun 22, 2020 · 371 revisions

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

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 development was adopted as part of the whole development.
    It's unnecessary to repeat design for the same part of contract again. Repeated designs inevitably lead to redundant configuration, recompilation ... redeployment for every intermediate links.
  • DbWebApi can coexist within your existing ASP.NET Web API, as a supplementary service to reduce new boring manual works for most common of application scenarios.

Usage

As one of the quickest learning ways, https://github.com/DataBooster/DbWebApi/releases provides several examples of using the DbWebApi library. Download it, select one of the projects to configure the database connection, compile and deploy it to IIS server, then start your intuitive experience by following the registered URL route. You can come back to read the details later in this section when you have time.

ApiController:

Please reference the sample DbWebApiController.cs:

using System.Net.Http;
using System.Web.Http;
using DataBooster.DbWebApi;

namespace MyDbWebApi.Controllers
{
    [DbWebApiAuthorize]
    public class DbWebApiController : ApiController
    {
        /// <param name="sp">Stored Procedure's fully qualified name</param>
        /// <param name="allParameters">Auto-binding from the request body</param>
        [AcceptVerbs("GET", "POST", "PUT", "DELETE")]
        public HttpResponseMessage DynExecute(string sp, InputParameters allParameters)
        {
            // Supplement input parameters from URI query string.
            allParameters = InputParameters.SupplementFromQueryString(allParameters, Request);

            // The main entry point to call the DbWebApi.
            return this.DynExecuteDbApi(sp, allParameters);
        }
    }
}

That's all, DynExecute is the extension method to ApiController.
(It combines the Execute and BulkExecute methods internally, auto-detect a post request body, invoking BulkExecute if sets of input parameters are encapsulated in an arrray; or invoking Execute if input parameters are encapsulated in a single dictionary)
Detail in DbWebApiController.cs. And the sample WebApiConfig.cs demonstrates the Web API routing for this action.

Web.config

"DataBooster.DbWebApi.MainConnection" is the only one configuration item needs to be customized:

<connectionStrings>
  <add name="DataBooster.DbWebApi.MainConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=SAMPLEDB;Integrated Security=SSPI" />
</connectionStrings>

HTTP Request

Url:

As registered in your WebApiConfig Routes (e.g. http://BaseUrl/Your.StoredProcedure.FullyQualifiedName)

Input Parameters

DbWebApi takes advantages of the Parameter-Binding mechanism in ASP.NET Web API. Current implementation is self-adaptive to four kinds of media types:

  • JSON format request (Content-Type: application/json or text/json)
  • XML format request (Content-Type: application/xml or text/xml)
  • HTML Form request (Content-Type: application/x-www-form-urlencoded)
  • Multipart MIME request (Content-Type: multipart/form-data) - for file upload
Simple Parameters

Only required input-parameters of the stored procedure/function need to be specified in your request body as JSON format (Content-Type: application/json). Don't put parameter prefix ('@' or ':') in the JSON body.
For example, a Stored Procedure (in SQL Server):

ALTER PROCEDURE dbo.prj_GetRule
    @inRuleDate  datetime,
    @inRuleId    int,
    @inWeight    float(6) = 0.1,
    @outRuleDesc varchar(256) = NULL OUTPUT
AS  ...

The payload JSON should look like:

{
    "inRuleDate":"2015-02-03T00:00:00Z",
    "inRuleId":108
}

Parameter names are case-insensitive.

If you use XML request (content-Type: application/xml or text/xml), the message body should look like:

<AnyRootName>
  <inRuleDate>2015-02-03T00:00:00Z</inRuleDate>
  <inRuleId>108</inRuleId>
</AnyRootName>

or

<AnyRootName xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema">
  <inRuleDate i:type="x:dateTime">2015-02-03T00:00:00Z</inRuleDate>
  <inRuleId i:type="x:int">108</inRuleId>
</AnyRootName>

or

<AnyRootName xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/">
  <inRuleDate z:Type="System.DateTime" z:Assembly="0">2015-02-03T00:00:00Z</inRuleDate>
  <inRuleId z:Type="System.Int32" z:Assembly="0">108</inRuleId>
</AnyRootName>

or

<AnyName inRuleDate="2015-02-03T00:00:00Z" inRuleId="108" />

If you want to use HTML Form (although you are unlikely to do so), the form body can be:

<form id="form1" method="post" action="api/Your.StoredProcedure.FullyQualifiedName/json"
    enctype="application/x-www-form-urlencoded">
    <div><label for="inRuleDate">Input Rule Date</label></div>
    <div><input name="inRuleDate" type="text" /></div>
    <div><label for="inRuleId">Input Rule Id</label></div>
    <div><input name="inRuleId" type="text" /></div>
    <div><input type="submit" value="Submit" /></div>
</form>

If you need to upload some files into database, Multipart Form Data is a really simple way:

<form id="form1" method="post" action="api/Your.StoredProcedure.FullyQualifiedName/json"
    enctype="multipart/form-data">
    <div><label for="inRuleDate">Input Rule Date</label></div>
    <div><input name="inRuleDate" type="text" /></div>
    <div><label for="inRuleId">Input Rule Id</label></div>
    <div><input name="inRuleId" type="text" /></div>
    <div><label for="inImageData">Image File</label><input name="inImageData" type="file" /></div>
    <div><label for="inTextData">Text File</label><input name="inTextData" type="file" /></div>
    <div><input type="submit" value="Submit" /></div>
</form>

Notes:

  • For a binary type parameter of stored procedure, the uploaded binary data will be passed in straightforward without any transformation; (for above instance, "inImageData" parameter)
  • For a string(text) type parameter of stored procedure, the uploaded stream will be treated as UTF-8 encoding to be decoded back to a string, unless your file contains BOM (Byte Order Marks) - it will detect encoding from the BOM. Then pass the string into the stores procedure. (for above instance, "inTextData" parameter)

 

Array of Parameter Sets

To pass bulk of same structure data back to database, you can just encapsulate all sets of parameters into an array like:

[
  {
    "inRuleDate":"2015-02-03T00:00:00Z",
    "inRuleId":108
  },
  {
    "inRuleDate":"2015-02-04T00:00:00Z",
    "inRuleId":109
  },
  {
    "inRuleDate":"2015-02-05T00:00:00Z",
    "inRuleId":110
  },
  {
    "inRuleDate":"2015-02-06T00:00:00Z",
    "inRuleId":111
  }
]

For above example, the Web API server side will iteratively invoking database stored procedure dbo.prj_GetRule 4 times and the response body will be an array that contains the corresponding results of 4 times executions.
Notes:
BulkExecute reads bulk sets of parameters from the request message body only, it means only HTTP POST and PUT can be used to send BulkExecute request, and only JSON and XML are acceptable media types for bulk response. If this limitation does counteract its conveniences you gain, please consider using following alternatives.

  • PL/SQL Associative Array Parameters (Oracle):
    In Oracle database, you can use PL/SQL Associative Array Parameters (Bulk Binds) to reduce loop overhead for performance sake (avoid too many context switches between the PL/SQL and SQL engines). For example, in database side:

    NUMBER_EMPTY_ARRAY  DBMS_UTILITY.NUMBER_ARRAY;

PROCEDURE WRITE_BULK_DATA
(
    inGroupID       PLS_INTEGER,
    inItemValues    DBMS_UTILITY.NUMBER_ARRAY := NUMBER_EMPTY_ARRAY,
    RC1             OUT SYS_REFCURSOR
);

    The payload JSON should look like:

    {
    "inGroupID": 108,
    "inItemValues": [
                        0,
                        1,
                        0.618,
                        1001,
                        -3.1415926585
                    ]
}

    Tips:
    Oracle ODP.NET does not support binding an empty array to a PL/SQL Associative Array Parameter. To work around this limitation, simply declaring an empty associative array as the default value for the parameter. Because the underlying DataBooster library does NOT pass the empty array to database for that particular parameter at all, the database engine will then use the DEFAULT value (which is an empty associative array declared in your stored procedures package) for that parameter, as shown above.

  • Table-Valued Parameters (SQL Server 2008+):
    In SQL Server 2008 or later, Table-Valued Parameter provides an equifinality of Associative Array Bulk Binds, but the implementation styles have different looks. For example, in database side:

    CREATE TYPE dbo.CategoryTableType AS TABLE
    ( CategoryID int, Weight float(6), CategoryName nvarchar(50) )

CREATE PROCEDURE dbo.usp_UpdateCategories 
    (@inGroupID int, @inTvpCategories dbo.CategoryTableType READONLY)

    The payload JSON should look like:

    {
    "inGroupID": 108,
    "inTvpCategories": [
                           {
                               "CategoryID": 1,
                               "Weight": 0.15,
                               "CategoryName": "Peach Blossom"
                           },
                           {
                               "CategoryID": 2,
                               "Weight": 0.38,
                               "CategoryName": "Peony"
                           },
                           {
                               "CategoryID": 3,
                               "Weight": 0.26,
                               "CategoryName": "Tulip"
                           },
                           {
                               "CategoryID": 4,
                               "Weight": 0.06,
                               "CategoryName": "Cymbidium Orchis"
                           },
                           {
                               "CategoryID": 5,
                               "Weight": 0.18,
                               "CategoryName": "Water Lily"
                           }
                       ]
}

    Tips:
    Unlike outer parameters bind-by-name (as above exampled "inGroupID" and "inTvpCategories"), Inside of the Table-Valued Parameter SQL Server actually behaves bind-by-position. No matter what you name those internal columns (as above exampled "CategoryID", "Weight", "CategoryName"), it made no difference to SQL Server. Below JSON input would get the same results as above.

    {
    "inGroupID": 108,
    "inTvpCategories": [
                           {
                               "C01": 1,
                               "C02": 0.15,
                               "C03": "Peach Blossom"
                           },
                           {
                               "C01": 2,
                               "C02": 0.38,
                               "C03": "Peony"
                           },
                           {
                               "C01": 3,
                               "C02": 0.26,
                               "C03": "Tulip"
                           },
                           {
                               "C01": 4,
                               "C02": 0.06,
                               "C03": "Cymbidium Orchis"
                           },
                           {
                               "C01": 5,
                               "C02": 0.18,
                               "C03": "Water Lily"
                           }
                       ]
}

    According to this, you can control the order of properties in JSON Serialization by this sort of lazy way.
    Note:
    If you don't have any item in the "inTvpCategories", but you still want to execute the stored procedure dbo.usp_UpdateCategories with an empty table-value, please remove the whole "inTvpCategories" parameter from the JSON payload as below:

    {
    "inGroupID": 108
}

 

Summary of Input Parameters and Execution Modes

The service execution mode is determined by the input payload from client's HTTP request body.

  • When a HTTP client sends a GET request (without message body),
    DbWebApi works in single-execution mode only, and all input parameters are extracted from the URL query string;
  • When a HTTP client sends a POST (or PUT, DELETE) request,
    DbWebApi follows these rules:
Input Payload
(Sample JSON)		 Execution Mode Stored Procedure
(Examples)		 
{
  "inPartitionId": 108,
  "inItemId": 101,
  "inItemName": "Test String 1",
  "inItemDate": "2015-02-03T00:00:00Z"
}
Single Execution SQL Server: 
ALTER PROCEDURE dbo.my_pck_Singly_Ins_Upd
(
    @inPartitionId  int,
    @inItemId       int,
    @inItemName     varchar(256),
    @inItemDate     datetime
)   AS  ...
Or

Oracle: 
CREATE OR REPLACE PACKAGE SCHEMA.MY_PCK IS

PROCEDURE SINGLY_INS_UPD
(
inPartitionId  PLS_INTEGER,
inItemId       PLS_INTEGER,
inItemName     VARCHAR2,
inItemDate     DATE
);


END MY_PCK;

[
  {
    "inPartitionId": 108,
    "inItemId": 101,
    "inItemName": "Test String 1",
    "inItemDate": "2016-05-01T00:00:00Z"
  },
  {
    "inPartitionId": 108,
    "inItemId": 102,
    "inItemName": "Test String 2",
    "inItemDate": "2016-05-02T00:00:00Z"
  },
  {
    "inPartitionId": 108,
    "inItemId": 103,
    "inItemName": "Test String 3",
    "inItemDate": "2016-05-03T00:00:00Z"
  }
]
Bulk Execution 
{
  "inPartitionId": 108,
  "inItemIds": [
    101,
    102,
    103
  ],
  "inItemNames": [
    "Test String 1",
    "Test String 2",
    "Test String 3"
  ],
  "inItemDates": [
    "2016-05-01T00:00:00Z",
    "2016-05-02T00:00:00Z",
    "2016-05-03T00:00:00Z"
  ]
}
PL/SQL Associative Array
(Oracle Bulk Binding)		 Oracle: 
CREATE OR REPLACE PACKAGE SCHEMA.MY_PCK IS

TYPE NUMBER_ARRAY IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
TYPE STRING_ARRAY IS TABLE OF VARCHAR2(256) INDEX BY PLS_INTEGER;
TYPE DATE_ARRAY IS TABLE OF DATE INDEX BY PLS_INTEGER;


PROCEDURE BULK_INS_UPD
(
inPartitionId  PLS_INTEGER,
inItemIds      NUMBER_ARRAY,
inItemNames    STRING_ARRAY,
inItemDates    DATE_ARRAY
);


END MY_PCK;

{
  "inPartitionId": 108,
  "inTvpItems": [
    {
      "inItemId": 101,
      "inItemName": "Test String 1",
      "inItemDate": "2016-05-01T00:00:00Z"
    },
    {
      "inItemId": 102,
      "inItemName": "Test String 2",
      "inItemDate": "2016-05-02T00:00:00Z"
    },
    {
      "inItemId": 103,
      "inItemName": "Test String 3",
      "inItemDate": "2016-05-03T00:00:00Z"
    }
  ]
}
Table-Valued Parameter
(SQL Server 2008+)		 SQL Server 2008 or later: 
CREATE TYPE dbo.ItemTableType AS TABLE
(
    ItemId      int,
    ItemName    nvarchar(256),
    ItemDate    datetime
);

CREATE PROCEDURE dbo.my_pck_Bulk_Ins_Upd
(
@inPartitionId  int,
@inTvpItems     dbo.ItemTableType READONLY
)   AS ...

 

Accept Response MediaType:

  1. JSON (default)
    Specify in request header:
    Accept: application/json
    or
    Accept: text/json
    or specify in query string: ?format=json
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule?format=json)
    or specify in UriPathExtension which depends on your url routing
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule/json)

  2. BSON (only available to targetFramework="4.5" or higher - ASP.NET Web API 2)
    Specify in request header:
    Accept: application/bson
    or specify in query string: ?format=bson
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule?format=bson)
    or specify in UriPathExtension which depends on your url routing
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule/bson)

  3. JSONP
    QueryString must contain callback parameter (the name can be configured)
    and (
    Specify in request header:
    Accept: text/javascript
    or
    Accept: application/javascript
    or
    Accept: application/json-p
    or specify in query string: ?format=jsonp
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule?format=jsonp)
    or specify in UriPathExtension which depends on your url routing
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule/jsonp)
    )

  4. XML
    Specify in request header:
    Accept: application/xml
    or
    Accept: text/xml
    or specify in query string: ?format=xml
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule?format=xml)
    or specify in UriPathExtension which depends on your url routing
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule/xml)

  5. xlsx (Excel 2007 and later)
    Specify in request header:
    Accept: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
    or
    Accept: application/ms-excel
    or
    Accept: application/xlsx
    or specify in query string: ?format=xlsx
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule?format=xlsx)
    or specify in UriPathExtension which depends on your url routing
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule/xlsx)
    Notes: Since xlsx content presents as an attachment, so you can specify a filename for convenience by query string: FileName=[save_as] (default: [save_as].xlsx).

  6. CSV
    Specify in request header:
    Accept: text/csv
    or specify in query string: ?format=csv
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule?format=csv)
    or specify in UriPathExtension which depends on your url routing
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule/csv)
    Notes: CSV response will only return the first (or one specified zero indexed result set in query string: ResultSet=i) result set if your stored procedure has multiple result sets. Since CSV content presents as an attachment, so you can specify a filename for convenience by query string: FileName=[save_as] (default: [save_as].csv).

  7. Razor Templating
    Specify in request header:
    Accept: text/razor
    or
    Accept: application/razor
    or specify in query string: ?format=razor
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule?format=razor)
    or specify in UriPathExtension which depends on your url routing
    (e.g. http://BaseUrl/YourDatabase.dbo.prj_GetRule/razor)
    Notes: To send a Razor request, the template text must be provided in a conventionalized parameter {RazorTemplate=} in either json body of post request or query string of get request, if the template text is a output parameter name of the stored procedure, the string content of that output parameter will be used as the actual template text. Two optional parameters: {RazorEncoding=Raw|Html} (default is Raw) and {RazorLanguage=CSharp|VisualBasic} (default is CSharp).
    Model's Data: Inside Razor template, the @Model directive represents your strored procedure's result data. (E.g. @Model.OutputParameters.outSomeThing - is the value of output parameter outSomeThing, @Model.ResultSets[0][0].SomeProperty - is the value of Some_Property column of the first row of the first resultset)

  8. Other MediaTypes
    To support new MediaType, you can:

  • Control in low level (DbDataReader and writeStream), you can create a new class that implements the interface IFormatPlug, and register it in your HttpConfiguration. Just like following CSV and xlsx did:
    public static void RegisterDbWebApi(this HttpConfiguration config)
    {
        config.AddFormatPlug(new CsvFormatPlug());
        config.AddFormatPlug(new XlsxFormatPlug());
    }
  • Control in high level (start point: StoredProcedureResponse), you can create a new XyzMediaTypeFormatter class as classical tutorial shows.

HTTP Response

Response data internal structure

    public class StoredProcedureResponse
    {
        public IList<IList<BindableDynamicObject>> ResultSets { get; set; }
        public BindableDynamicObject OutputParameters { get; set; }
        public object ReturnValue { get; set; }
    }

Response body formats

application/json, text/json
Sample:  

{
  "ResultSets":
  [
    [
      {"COL_1":"2015-02-03T00:00:00","COL_2":3.14159,"COL_3":"Hello World1","COL_4":null, "COL_5":0},
      {"COL_1":"2015-02-02T00:00:00","COL_2":3.14159,"COL_3":null,"COL_4":1234567.800099, "COL_5":1},
      {"COL_1":"2015-02-01T00:00:00","COL_2":3.14159,"COL_3":"Hello World3","COL_4":null, "COL_5":2},
      {"COL_1":"2015-01-31T00:00:00","COL_2":3.14159,"COL_3":null,"COL_4":9876541.230091, "COL_5":3}
    ],
    [
      {"COL_A":100,"COL_B":"fooA","COL_C":0},
      {"COL_A":200,"COL_B":"fooB","COL_C":null},
      {"COL_A":300,"COL_B":"fooC","COL_C":1}
    ],
    [
       {"NOTE":"Test1 for the third result set"},
       {"NOTE":"Test2 for the third result set"}
    ]
  ],
  "OutputParameters":
  {
    "outRuleDesc":"This is a test output parameter value.",
    "outSumTotal":888888.88,
    "outRC1":null
  },
  "ReturnValue":0
}

For response to bulk execute request, each of such JSON object will be further encapsulated into an outer array.

application/xml, text/xml
Sample:

<StoredProcedureResponse xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" SerializePropertyAsAttribute="false" EmitNullValue="true" TypeSchema="Xsd">
  <ResultSets>
    <ResultSet>
      <Record>
        <COL_1 i:type="x:dateTime">2015-02-03T00:00:00</COL_1>
        <COL_2 i:type="x:decimal">3.14159</COL_2>
        <COL_3 i:type="x:string">Hello World1</COL_3>
        <COL_4 i:nil="true"/>
        <COL_5 i:type="x:int">0</COL_5>
      </Record>
      <Record>
        <COL_1 i:type="x:dateTime">2015-02-02T00:00:00</COL_1>
        <COL_2 i:type="x:decimal">3.14159</COL_2>
        <COL_3 i:nil="true"/>
        <COL_4 i:type="x:decimal">1234567.800099</COL_4>
        <COL_5 i:type="x:int">1</COL_5>
      </Record>
      <Record>
        <COL_1 i:type="x:dateTime">2015-02-01T00:00:00</COL_1>
        <COL_2 i:type="x:decimal">3.14159</COL_2>
        <COL_3 i:type="x:string">Hello World3</COL_3>
        <COL_4 i:nil="true"/>
        <COL_5 i:type="x:int">2</COL_5>
      </Record>
      <Record>
        <COL_1 i:type="x:dateTime">2015-01-31T00:00:00</COL_1>
        <COL_2 i:type="x:decimal">3.14159</COL_2>
        <COL_3 i:nil="true"/>
        <COL_4 i:type="x:decimal">9876541.230091</COL_4>
        <COL_5 i:type="x:int">3</COL_5>
      </Record>
    </ResultSet>
    <ResultSet>
      <Record>
        <COL_A i:type="x:int">100</COL_A>
        <COL_B i:type="x:string">fooA</COL_B>
        <COL_C i:type="x:int">0</COL_C>
      </Record>
      <Record>
        <COL_A i:type="x:int">200</COL_A>
        <COL_B i:type="x:string">fooB</COL_B>
        <COL_C i:nil="true"/>
      </Record>
      <Record>
        <COL_A i:type="x:int">300</COL_A>
        <COL_B i:type="x:string">fooC</COL_B>
        <COL_C i:type="x:int">1</COL_C>
      </Record>
    </ResultSet>
    <ResultSet>
      <Record>
        <NOTE i:type="x:string">Test1 for the third result set</NOTE>
      </Record>
      <Record>
        <NOTE i:type="x:string">Test2 for the third result set</NOTE>
      </Record>
    </ResultSet>
  </ResultSets>
  <OutputParameters>
    <outRuleDesc i:type="x:string">This is a test output parameter value.</outRuleDesc>
    <outSumTotal i:type="x:decimal">888888.88</outSumTotal>
    <outRC1 i:nil="true" />
  </OutputParameters>
  <ReturnValue i:nil="true" />
</StoredProcedureResponse>

For response to bulk execute request, each of such XML object will be further encapsulated into an outer array.

There are a few options can be applied in Url query string to control the XML style:

  • XmlNullValue

    • true: (default) Emit all null (DBNull) value properties (columns) into XML.
    • false: Do not emit any null (DBNull) value properties (columns) into XML.

  • XmlAsAttribute

    • false: (default) Serialize properties (columns) as XML elements.
    • true: Serialize properties (columns) as XML attributes, null (DBNull) value will be rendered as empty string if XmlNullValue=true.
      For above example stored procedure with XmlAsAttribute=true, the response becomes:
<StoredProcedureResponse xmlns:i="http://www.w3.org/2001/XMLSchema-instance" SerializePropertyAsAttribute="true" EmitNullValue="true" TypeSchema="None">
  <ResultSets>
    <ResultSet>
      <Record COL_1="2015-02-03T00:00:00" COL_2="3.14159" COL_3="Hello World1" COL_4="" COL_5="0" />
      <Record COL_1="2015-02-02T00:00:00" COL_2="3.14159" COL_3="" COL_4="1234567.800099" COL_5="1" />
      <Record COL_1="2015-02-01T00:00:00" COL_2="3.14159" COL_3="Hello World3" COL_4="" COL_5="2" />
      <Record COL_1="2015-01-31T00:00:00" COL_2="3.14159" COL_3="" COL_4="9876541.230091" COL_5="3" />
    </ResultSet>
    <ResultSet>
      <Record COL_A="100" COL_B="fooA" COL_C="0" />
      <Record COL_A="200" COL_B="fooB" COL_C="" />
      <Record COL_A="300" COL_B="fooC" COL_C="1" />
    </ResultSet>
    <ResultSet>
      <Record NOTE="Test1 for the third result set" />
      <Record NOTE="Test2 for the third result set" />
    </ResultSet>
  </ResultSets>
  <OutputParameters outRuleDesc="This is a test output parameter value." outSumTotal="888888.88" outRC1="" />
  <ReturnValue i:nil="true" />
</StoredProcedureResponse>
  • XmlTypeSchema (only available when XmlAsAttribute=false)
    • Xsd: (default) Emit XSD data type information (E.g. i:type="x:dateTime" ...) for each property(column)'s XML element.
    • Net: Emit .Net data type information (E.g. z:Type="System.DateTime" ...) for each property(column)'s XML element.
    • None: Do not emit any data type information in XML.

Above options only provide some simple controls on XML styles.
For other XML format controls, you may still need to further apply XDT or raw XSLT transformations ... even hard coding in client side.

 

text/csv
Sample:

COL_1,COL_2,COL_3,COL_4,COL_5
2015-02-03,3.14159,Hello World1,,0
2015-02-02,3.14159,,1234567.800099,1
2015-02-01,3.14159,Hello World3,,2
2015-01-31,3.14159,,9876541.230091,3

Notes:

JSON, XML and xlsx respones are constructed completely in Web API server before sending to the client, so you might encounter OutOfMemoryException if the client wants to receive huge amounts of data. However, JSON can be sufficient in most application scenarios with its simplicity. And after all, process data as close to where the data physically resides as possible, this is a basic principle of big data processing. (i.e. Simplifying the complexity as early as possible.)

For most of Web applications, the final data are for human eyes to read.

For some systems integration, CSV format is also widely used for data filling. It's mostly waste of human resources to design such SSIS packages one by one, and to maintain such encumbrances for ever. It's time for machine to do such mechanical process, let DbWebApi serve as the machine. No more mechanical designs, no more packages, no more configurations, no more deployments and no more maintenances. Let artificial complexities, dust to dust, nothing to nothing!

CSV respone emerges as text stream pushing to the client, it just use very little memory in Web API server to push a few text lines as long as their CSV rows have been constructed, so on and so forth, until all complete. So the server's memory is not a limitation of how many records can be handled.

Property Naming Convention

Database side may use a different naming convention other than .NET side or JavaScript side. For example, most Oracle works use underscores naming convention, like above output examples, from a .NET or JavaScript point of view, they could look really ugly. So DbWebApi provides 2 + None built-in naming convention resolvers:

  • PropertyNamingConvention.None
  • PropertyNamingConvention.PascalCase
  • PropertyNamingConvention.CamelCase

You can set the DefaultPropertyNamingConvention globally in your WebApiConfig:

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        ...
        DbWebApiOptions.DefaultPropertyNamingConvention =
            PropertyNamingConvention.PascalCase;
    }
}

You can also specify the output Property Naming Convention in Uri Query String of each individual request:

  • NamingCase=N (or None) ---------- As it is in database
  • NamingCase=P (or Pascal) -------- PascalCase
  • NamingCase=C (or Camel) -------- CamelCase

If you don't specify the NamingCase in later request, the global set before will back into effect.
Notes: The automatic naming conversion only applies to result sets' column-names. Input and output parameters remain the same as they are in database.

Exceptions

For JSON, XML and xlsx responses, detail exception will be encapsulated into HttpResponseMessage with HTTP 500 error status if the Web API service encounters any problems. For the verbosity of errors to show in client side, it depends on your IncludeErrorDetailPolicy in HttpConfiguration. However, because CSV respone uses a push stream, the client side will always receive a HTTP 200 OK header without Content-Length field. If the server side encounter any exception subsequently, it would simply interrupt the http connection and the client would get a Receive Failure without any detail exception message.

Permission Control

Access authorization is the only one thing you have to handle by yourself, and the approach depends on the granularity of control you want.
Control over the stored-procedure granularity is a simple and effective practice. The example project shows using an authorization filter [DbWebApiAuthorize] to restrict which user can execute which stored procedure, that should integrate with your own implementation of authorization checking.

    public class MyDbWebApiAuthorization : IDbWebApiAuthorization
    {
        public bool IsAuthorized(string userName, string storedProcedure, object state = null)
        {
            // TO DO, to integrate with your own authorization implementation
            return true;	// If allow permission
            return false;	// If deny permission
        }
    }

UserName

Recording current username is a common auditing requirement. Since the Web API never trust any self-identify username sent from client request data. So if a stored procedure requires the username as a parameter, the Web API should always replace that parameter sent from the client (or add that parameter if a client didn't send it) by the server side authentication. Any practical way as long as you think it's simple enough can be apply in your Web API implementation. For examples,

  • Make a naming convention for this special parameter in database within your enterprise, then the Web API always set (replace/add) this special parameter before pass the whole input parameters dictionary to ExecuteDbApi extension method. It won't hurt anything, because in its low level DataBooster will match stored procedure parameter names with the input parameters dictionary that you pass in, and discard non-matched parameters.
  • Or in a traditional way, create separate Controllers for those stored procedures individually, in their internal implementation set current username and then call the ExecuteDbApi extension method.
  • Or in a centralized table, register that which stored procedures which parameter require current UserName input, so that in the Web API can know when it need to replace/add which input parameter.
  • etc.

Performance

  • Connection Pool Tuning
    Facing with concurrent requests from different clients in different business contexts. DbWebApi server opens a new database connection per request. All requests are using the same connection string. So the Connection Pool Tuning is important to the performance of the whole responsiveness.

  • Load Balancing
    As a completely generic web service, DbWebApi makes the distributed deployment much simpler, every nodes in the distributed environment are equivalent. It is easier to apply any of today's existing web server load balance techniques.

  • For front-end applications and systems integration
    As a Web API, the target clients are still front-end applications mainly, plus some data formats transform for systems integration convenience.
    The performance overhead of each extra wrapper of network service (wrap one web service on top of another web service, and another one ... fussily) is always very expensive. For efficient custom data services development, it is recommended to use DataBooster Library - Extension to ADO.NET Data Provider directly for high-performance database access.

Bulk Manipulation

  • The BulkExecuteDbApi extension (BulkExecute action) is not a completely thorough bulk operation. It does performs the real bulk operation only between HTTP client and Web API server, it still performs a big loop calls to database from the Web API server. But it provides a convenient wrapper around every single call, and it is independent on specific database (Oracle or SQL Server).
  • If there are thousands of data rows or more data sets need to be passed back to database, it's well worth considering using Table-Valued Parameters (specific for SQL Server 2008+) or PL/SQL Associative Array Parameters (specific for Oracle database) in a single ExecuteDbApi (Execute action) call as mentioned before, they are completely thorough bulk operations.

Clients

Swagger UI

Swagger UI is a handy WebAPI documentation tool. Some examples of DbWebApi Swagger specifcation (swagger.json file) are provided in DbWebApi/Client/Swagger-UI/.
Notes: the templating symbols /*( ... )*/ are using in the example files to mark a section as replaceable.
Swagger Editor can be used to verify your customized Swagger specifcation file (swagger.json).
If your have complex data model, some JSON schema-generating tool (such as JSONSchema.Net) can be used as an aid.

Then, your Swagger UI URL may look like http://your_host/swagger-ui/index.html?url=specification_location/swagger.json or http://your_host/swagger-ui/?url=specification_location/swagger.json.

If your swagger.json files are placed in some older IIS, you might need to add .json file extension in IIS MIME Types or add a mimeMap in the web.config as below.

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <system.webServer>
    <staticContent>
      <remove fileExtension=".json" />
      <mimeMap fileExtension=".json" mimeType="application/json" />
    </staticContent>
    <defaultDocument>
      <files>
        <clear />
        <add value="index.html" />
      </files>
    </defaultDocument>
  </system.webServer>
</configuration>

.Net Client

DbWebApi Client .Net Library can be used to simplify the client call. See following sample:

using DataBooster.DbWebApi.Client;
DbWebApiClient client = new DbWebApiClient("http://dbwebapi.dev.com/oradev/");
//  client.HttpMethod = HttpMethod.Get;    // Default is POST

// Synchronous call. If need asynchronous call, please use ExecAsync(..) instead.
StoredProcedureResponse data = client.Exec("test_schema.prj_package.foo",
    new {
        inDate = new DateTime(2015, 3, 16)
        //, ... other input parameters, if any.
    });

The second argument of Exec(...) can be a dictionary that contains every parameters, or an anonymous type object that each property indicate a parameter name-value.
If an array of input parameter sets is passed into the second argument of Exec(...), the return will be an array -- StoredProcedureResponse[].

If you expect a stored procedure would be time-consuming, please set the HttpClient.Timeout to a sufficiently long time, such as:

client.HttpClient.Timeout = TimeSpan.FromMinutes(10);

All Exec... overloads will use HTTP POST method by default. You can change the default behavior to HTTP GET if need:

client.HttpMethod = HttpMethod.Get;

If you just need the response content stream (E.g. CSV, Excel xlsx or generated text) to be stored as a file or transfer forward to somewhere else on the network, see below example, replacing Exec() by ExecAsStream().

...
Stream stream = client.ExecAsStream("test_schema.prj_package.foo",
    new {
        inDate = new DateTime(2015, 3, 16)
        //, ... other input parameters, if any.
    });
using (FileStream file = File.Create(...))
{
    stream.CopyTo(file);
}

For more general purpose, ExecAsStream (or ExecAsStreamAsync), ExecAsJson (or ExecAsJsonAsync), ExecAsXml (or ExecAsXmlAsync) and ExecAsString (or ExecAsStringAsync) overloads can be used to invoke any REST API, not limited to DbWebApi.

By default, the DbWebApiClient uses Windows authentication for the convenience of intranet usage scenarios. Please see its constructor overrides for other options.

JavaScript Client

You can use jQuery.ajax easily to call the Web API, or you can use DbWebApi Client JavaScript Library to reduce repetitive coding.
Sample:

<script src="Scripts/jquery-2.1.3.js" type="text/javascript"></script>
<script src="Scripts/dbwebapi_client-1.0.8-alpha.js" type="text/javascript"></script>
<script type="text/javascript">
    ...
    $.postDb('http://dbwebapi.dev.com/oradev/test_schema.prj_package.foo',
             '{"inDate":"2015-03-10T00:00:00.000Z"}',
             function (data) {
                 // Bind data.ResultSets[0] with some contorls,
                 // or iterate through each JSON object in data.
             });
    ...
</script>

The second argument of $.postDb - inputJson can be either a JSON string or a plain object. If it's a plain object, it will be converted by JSON.stringify before sending to the server. Below sample is equivalent to above sample.

    ...
    var input = {
        inDate: $.utcDate(2015,03,10)
    };
    $.postDb('http://dbwebapi.dev.com/oradev/test_schema.prj_package.foo',
             input,
             function (data) {
                 ...
             });
    ...

If there is no input parameter to pass to the server, please put null in the second argument.
If an array of input parameter sets is passed into the second argument, the return data will be an array that contains the corresponding results of every iterative executions.
By default, the $.postDb sets the withCredentials property of the internal xhrFields object to true so it will pass the user credentials with cross-domain requests.
As the name implies, $.postDb uses HTTP POST to send a request;
Alternatively, $.getDb can be used for HTTP GET if need be. All input parameters are encapsulated into a special query string, and appended to the url for GET-requests.

Cross-domain
CORS

The sample server projects (.Net4.5/WebApi2 versions) in this repository have built-in support for CORS (Cross-Origin Resource Sharing). You can change the "CorsOrigins" item of appSettings in the Web.config if you want to specify particular Origins.

<configuration>
  <appSettings>
    <add key="CorsOrigins" value="*" />
    <add key="CorsSupportsCredentials" value="true" />
    <add key="CorsPreflightMaxAge" value="3600" />
  </appSettings>
</configuration>

Preflight Request 401 Issue with Windows Authentication

Today (until 2015) a very common usage scenario:

  • Only Integrated Windows Authentication is enabled on IIS (without anonymous authentication), and
  • The request body is JSON or XML media types - the Content-Type header is application/json, text/json, application/xml or text/xml.

Usually the CORS preflight will fail by a 401 unauthorized error (Access is denied due to invalid credentials) in this scenario. The root of this problem came from an awkward preflight rule in W3C specifications.

  • Since above case doesn't meet the exemption conditions for browser to skip the preflight request, so browser will start the additional preflight request;
  • Browser would never include user credentials in preflight request; (Even though both server side's SupportsCredentials and browser side's withCredentials have been set to be true, they only apply to subsequent actual cross-origin requests instead of the preflight request.)
  • IIS would return a 401 to any anonymous request, within the 401 response would have some Windows Authentication headers that expect the client to start an authentication handshake. However the browser still stubbornly believe that preflight request shouldn't include any user credential, so just gives up;
  • If the preflight request fails, the browser would never send the actual cross-origin request at all.

There are several ways to get around this uncomfortable issue:

  • Enable anonymous authentication at the IIS level, and disable anonymous authentication at the web.config level and the web API authorization filter level.
  • Use HTTP GET method to avoid sending application/json (or xml) Content-Type header - Browser never send any body in GET request.
    $.getDb(...) can encode input JSON object into a special parameter in query string.
    But there is a limitation on length of the URL, large data still requires the use of POST method, see the next ways then:
  • Attach cross-origin POST request in any one very lightweight GET request's callback function, as in the following example:
    ...
    var input = {
        inDate: $.utcDate(2015,03,10)
    };
    $.getDb('http://dbwebapi.dev.com/oradev/api/Misc/WhoAmI', null, function (me) {
        $.postDb('http://dbwebapi.dev.com/oradev/test_schema.prj_package.foo',
             input,
             function (data) {
                 ...
             });
    });
    ...

Here $.getDb('.../WhoAmI') acts as a bootstrapper, it makes the browser to start an authentication handshake in advance, once IIS authenticates the request, the default behavior of IIS will cache a token/ticket on the server for the connection, then the immediate preflight request on the same connection is not required to be authenticated again, so the preflight request will succeed, then the browser can continue the actual CORS request.

  • HTML Form request (Content-Type: application/x-www-form-urlencoded) and Multipart MIME request (Content-Type: multipart/form-data) can also be used to send a fair amount of data to DbWebApi without preflight request.
    The limitation of these two media types' requests is that, they can only carry input parameters for a single execution of DbWebApi on each call. If you need a bulk execution of DbWebApi on one call, only the former way (all sets of input parameters are further encapsulated in an outer JSON or XML array) can satisfy the use.

 

JSONP (Added: Server Lib v1.2.4, Client JS v1.0.8-alpha)

JSONP is a practicable way (although it seems a little rascal) to solve the cross-domain access puzzle before CORS is supported by all popular browsers.
Below example is a JSONP approach of above example,

    ...
    var input = {
        inDate: $.utcDate(2015,03,10)
    };
    $.jsonpDb('http://dbwebapi.dev.com/oradev/test_schema.prj_package.foo',
             input,
             function (data) {
                 ...
             });
    ...

Notes: since JSONP sends request by HTTP GET method, BulkExecute can not be used by JSONP.

The server side WebApiConfig.cs:

    config.RegisterDbWebApi();

which will include JSONP support by default. If you don't want to support JSONP, please specify supportJsonp to be false:

    config.RegisterDbWebApi(supportJsonp: false);

AngularJS Client

Using the built-in $http service is a straightforward way for AngularJS client to invoke the Web API. For example,

    ...
    return $http.post(spUrl, inputData, {withCredentials: true})
                .then(function(response){
                    return response.data.ResultSets; 
                });

Angular 6+ Client

If your project is Angular 6.0 or higher, the npm package dbwebapi-client can be used to simplify your http client coding.

> npm i dbwebapi-client

app.module.ts

import { DbwebapiClientModule } from 'dbwebapi-client';

@NgModule({
  declarations: [ // ...
  ],
  imports: [ // ...
    DbwebapiClientModule.forRoot()
  ],
  providers: [ // ...
  ],
  bootstrap: [AppComponent]
})
export class AppModule { }

Then your Angular service can inherit from DbWebApiClient class, as shown in the following example:

import { Injectable } from "@angular/core";
import { HttpClient } from "@angular/common/http";
import { Observable } from "rxjs";
import { map } from 'rxjs/operators';

import { DbWebApiClient } from 'dbwebapi-client';

@Injectable({ providedIn: 'root' })
export class MyDbWebApiService extends DbWebApiClient {
    constructor(_http: HttpClient) { super(_http, 'http://my-base-url-path.'); }

    invokeMyStoredProcedure(inParams: object): Observable<MyTypescriptModel> {
        return super.post('my_stored_procedure', inParams).pipe(map(data => new MyTypescriptModel(data.ResultSets)));
    }
}

The example method will return an Observable MyTypescriptModel instance if the constructor of MyTypescriptModel class transforms flat result sets to local hierarchical data model.

If you need to control the details of http options (such as: credentials, headers), you can use the property httpOptions to set it up.

Python(3+) Client

Specifically for JSON-request=>JSON-response with Windows single sign-on authentication, the PyPi package simple-rest-call can be leveraged to simplify your Python client.

PowerShell Client

In Windows PowerShell 3.0 or higher, Invoke-RestMethod cmdlet is readily available. See following sample:

$inpms = @{inDate = [DateTime]"2015-03-16"};
$response = Invoke-RestMethod -UseDefaultCredentials -Method Post -Uri "http://dbwebapi.dev.com/oradev/test_schema.prj_package.foo" -Body (ConvertTo-Json $inpms) -ContentType "application/json"

$response contains all the result data. In Powershell ISE, the IntelliSense can show you all its member properties.
If an array of input parameter sets is passed into the body content, the return $response will be an array that contains the corresponding results of every iterative executions.

If you want to save the response body stream (such as CSV or Excel xlsx) into a specified output file, please use -OutFile parameter,

Invoke-RestMethod -UseDefaultCredentials -Method Post -Uri "http://dbwebapi.dev.com/oradev/test_schema.prj_package.foo/xlsx" -Body (ConvertTo-Json $inpms) -ContentType "application/json" -OutFile "\\somewhere\somepath\filename.xlsx"
Bulk Data Post Back
  • If you only have hundreds of records or less to post back to database, you can just encapsulate all records into an array, let the Web API server side implicitly perform the BulkExecute action. Following example shows how to load data from a local CSV file and post back into database:
$impData = Import-Csv -Path "D:\Test\bulk-100s.csv";
Invoke-RestMethod -UseDefaultCredentials -Method Post -Uri "http://dbwebapi.test.net/oradev/test_schema.prj_package.write_row" -Body (ConvertTo-Json $impData) -ContentType "application/json";

Straightforwardly, any CSV columns that match the names in input parameters will be passed into the stored procedure.
By using PowerShell pipeline, if need, you can easily apply some data transformations, to do such as: column-parameter mapping or simple calculating by select cmdlet, data filtering by where cmdlet, some simple aggregation by group cmdlet, and data sorting by sort cmdlet, ... etc.

  • If you have thousands of records or more to post back to database, taking advantage of Table-Valued Parameters (SQL Server 2008+) or PL/SQL Associative Array Parameters (Oracle) will give you significantly better performance.
    • Table-Valued Parameters (SQL Server 2008+)
      Basically just further wrap the array of records into a single parameter, like the second line in following example (note that dbo.pck_bulk_write stored procedure is different from single row operation):
$csv = Import-Csv -Path "D:\Test\bulk-100s.csv";
$inpms = @{ tvpParam = $csv };
Invoke-RestMethod -UseDefaultCredentials -Method Post -Uri "http://dbwebapi.test.net/sqldev/dbo.pck_bulk_write" -Body (ConvertTo-Json $inpms) -ContentType "application/json";
$csv = Import-Csv -Path "D:\Test\bulk-100s.csv";
$inpms = @{inItemIds=[int[]]@(0) * $csv.Length; inItemNames=[string[]]@("") * $import.Length; inItemValues=[decimal[]]@(0) * $import.Length; inBatchComment="This is a test load."};
[int]$i = 0;
foreach ($item in $csv) {
    $inpms.inItemIds[$i] = $item.ItemId;
    $inpms.inItemNames[$i] = $item.ItemName;
    $inpms.inItemValues[$i] = $item.ItemValue;
    $i++;
}
Invoke-RestMethod -UseDefaultCredentials -Method Post -Uri "http://dbwebapi.test.net/oradev/test_schema.tst_package.bulk_write" -Body (ConvertTo-Json $inpms) -ContentType "application/json";

Tips:
Using PowerShell array for large dataset, better to initialize an array with explicit size (instead of dynamic array with subsequent appending elements), otherwise most of performance will be lost in highly frequent memory reallocation, data copying over and over again.
You may notice that Invoke-RestMethod takes many fixed arguments, to be lazier to type them all the time, you can import a convenient function Invoke-DbWebApi from DbWebApi-Client.psm1 to further clean your PowerShell scripts. As a shell, PowerShell is much better at describing what to do, rather than how to do. Each Cmdlet or external service focuses on how to do. So keep PowerShell scripts as clean as possible will benefit the whole process flow in a clear thread.

If you like to automatically match all Associative Array Parameters to the CSV Columns (by names), above example can be refactored into a generic function as follows:

Function LoadCsv-IntoOra {
    [CmdletBinding(SupportsShouldProcess)]
    Param (
        [Parameter(Mandatory, ValueFromPipeline)]
        [string]$CsvPath,
        [Parameter(Mandatory, ValueFromPipelineByPropertyName)]
        [Uri]$SpUri
    )

    $csv = Import-Csv -Path $CsvPath;

    If ($csv.Length -gt 0) {
        $plAAParams = [PSCustomObject]@{};

        Get-Member -InputObject $csv[0] -MemberType Properties | ForEach-Object {
            $inArray = @($null) * $csv.Length;

            for ([int]$i = 0; $i -lt $csv.Length; $i++) {
                $inArray[$i] = $csv[$i].($_.Name);
            }
            Add-Member -InputObject $plAAParams -MemberType NoteProperty -Name $_.Name -Value $inArray;
        }
        return Invoke-RestMethod -UseDefaultCredentials -Method Post -Uri $SpUri -Body (ConvertTo-Json $plAAParams) -ContentType "application/json";
    }
}

PowerShell is true powerful to do more solid work with less coding if being rationally utilized. Especially for back office system-integration applications, heterogeneous techniques across different systems can be leveraged by PowerShell's interoperability with consistent pipeline mechanism. It's also extremely handy to use PowerShell as a test/debug tool. In PowerShell, all data become visualized and extremely flexible to be quickly modified interactively.

Windows Command Line Client

On some occasions which running-performance are not critical, using a generic batch file to call the DbWebApi PowerShell Client in a single command line may still be an efficient development. Refer to Invoke-DbWebApi.bat.
For usage example,

Invoke-DbWebApi.bat -Uri "http://dbwebapi.dev.com/oradev/test_schema.prj_package.your_sp/xlsx" -Body "{'inId':108,'inDate':'2016-01-20T00:00:00Z'}" -OutFile "\\NFS\Shared Folder\Working Data\Rpt2015.xlsx"

Remarks
The Invoke-DbWebApi.bat requires Windows PowerShell 3.0 or higher.
To check what exact parameters will be sent to DbWebApi without really executing the stored procedure, you can append the -WhatIf -Verbose switches to above command-line.

To prevent a JSON string argument from being split into multiple broken arguments by Command Prompt, double quotes (") must be used to encapsulate the whole string, and leaving inside single quotes (') to JSON parser. For example:

-Body "{'inId': 108, 'inDate': '2016-01-20T00:00:00Z', 'inComment': 'Some Comment'}"

Because in Json.Net, single quotes around keys and values work in the same way as double quotes (although it is not a standard of JSON.org). But in Windows Command Prompt, only double-quoted string (contains spaces) can be recognized as a whole argument.

Alternatively, escaping every inside double quote character (") needs to be considered. For example:

-Body "{\"inId\": 108, \"inDate\": \"2016-01-20T00:00:00Z\", \"inComment\": \"Some Comment\"}"

Using -WhatIf -Verbose switches is an easy check.

Power Query Client

Power BI can use Power Query to invoke DbWebApi as simply as below example:

let
    Source = Json.Document(Web.Contents("http://dbwebapi.dev.com/oradev/test_schema.prj_package.your_sp/json?inDate=2016-04-22")),
    ResultSet1 = Table.FromRecords(Source[ResultSets]{0})
in
    ResultSet1

(In practical applications, above regular query would be made into a function with parameters.)

In many cases, some in-situ processes are programmed in stored procedures; Power BI needs to get the resultsets on demand. DbWebApi brings a convenient and secure way for Power Query to access stored procedures.

Especially for Oracle stored procedures, Power Query can not handle SYS_REFCURSOR. Without the DbWebApi, we mostly had to schedule the stored procedures to run and dump the resultsets into some physical tables at regular intervals. Then let Power Query get the result data from those tables. This might require assistance from the DBAs at your organization, for extra jobs, and grant appropriate database privilege on every individual tables. That’s too cumbersome!

Since Power Query doesn't currently support POST web request with windows authentication. However, windows authentication is a necessity in most intranet-enterprise scenarios, so we have to use GET web request with windows authentication for now. Fortunately, DbWebApi accepts input parameters from either POST body or URL query string. This provides an easy workaround in many situations.

 

Restrictions

NuGet

Server side

There are 4 NuGet packages for 4 differenct versions of ADO.NET providers:

For-Oracle versions always contain the support for SQL Server. To switch from Oracle to SQL Server, simply change the providerName and connectionString of connectionStrings "DataBooster.DbWebApi.MainConnection" in your web.config.
To switch above from one NuGet package to another NuGet Package, simply uninstall one and install another from NuGet Package Manager.

Clients

SymbolSource

Associated Symbols Packages with above NuGet Packages are also available in http://srv.symbolsource.org/pdb/Public.
To source step during debugging into the code of libraries, please see SymbolSource.org for detailed instructions.

Examples

Please refer to example projects - MyDbWebApi in https://github.com/DataBooster/DbWebApi/tree/master/Server/Sample

  • DbWebApi.SampleServers.sln for Visual Studio 2012 or later
  • DbWebApi.SampleServers.Net40.sln for Visual Studio 2010 with ASP.NET MVC 4 installed

Inside the solutions, both .Net45 branch and .Net40 branch are further divided into 4 projects for - SQL Server, Oracle (ODP.NET Managed, ODP.NET Unmanaged and DataDirect provider). You can keep one of them as needed and removed all the rest. Hopefully, base on the examples, it's easier to customize it as your own DbWebApi server.

If you are only interested in having your trial server setup quickly, you can download the released server side samples from https://github.com/DataBooster/DbWebApi/releases simplicity.

By default, the example server is configured for intranet environment:

Web.config

<configuration>
  <system.web>
    <authentication mode="Windows" />
  </system.web>
  <connectionStrings>
    <add name="DataBooster.DbWebApi.MainConnection" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=SAMPLEDB;Integrated Security=SSPI;Min Pool Size=8" />
  </connectionStrings>
</configuration>

DbWebApiController.cs

namespace MyDbWebApi.Controllers
{
    [DbWebApiAuthorize]
    public class DbWebApiController : ApiController
    {
        ...
    }
}

MyDbWebApiAuthorization.cs

namespace MyDbWebApi
{
    public class MyDbWebApiAuthorization : IDbWebApiAuthorization
    {
        public bool IsAuthorized(string userName, string storedProcedure, object state = null)
        {
            // TO DO, to implementate your own authorization logic
            return true;	// If allow permission
            return false;	// If deny permission
        }
    }
}

According to your own circumstances, above should be modified as needed, just like the most basic settings should be applied on an Empty ASP.NET Web API project.

Notes: the example web sites are just for hosting the DbWebApi, there is no default page in them, so you would see HTTP Error 403.14 when you open the nonexistent home page, that's normal.

Welcome all feedback through the Issues.

Add a custom footer
Add a custom sidebar
Clone this wiki locally