(Migrated from CodePlex) DbWebApi is a .Net library that implement an entirely generic Web API (RESTful) for HTTP clients to call database (Oracle & SQL Server) stored procedures or functions in a managed way out-of-the-box without any configuration or coding.
MIT License
-- -- -- -- -- -- -- -- -- -- -- -- Extension to ASP.NET Web API (RESTful)
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.
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.
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.
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.
"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>
As registered in your WebApiConfig Routes (e.g. http://BaseUrl/Your.StoredProcedure.FullyQualifiedName)
DbWebApi takes advantages of the Parameter-Binding mechanism in ASP.NET Web API. Current implementation is self-adaptive to four kinds of media types:
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:
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
}
The service execution mode is determined by the input payload from client's HTTP request body.
PROCEDURE SINGLY_INS_UPD ( inPartitionId PLS_INTEGER, inItemId PLS_INTEGER, inItemName VARCHAR2, inItemDate DATE );
END MY_PCK;
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;
CREATE PROCEDURE dbo.my_pck_Bulk_Ins_Upd ( @inPartitionId int, @inTvpItems dbo.ItemTableType READONLY ) AS ...
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)
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)
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) )
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)
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).
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).
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)
Other MediaTypes To support new MediaType, you can:
public static void RegisterDbWebApi(this HttpConfiguration config)
{
config.AddFormatPlug(new CsvFormatPlug());
config.AddFormatPlug(new XlsxFormatPlug());
}
public class StoredProcedureResponse
{
public IList<IList<BindableDynamicObject>> ResultSets { get; set; }
public BindableDynamicObject OutputParameters { get; set; }
public object ReturnValue { get; set; }
}
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.
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
XmlAsAttribute
<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>
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.
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.
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:
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:
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.
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.
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
}
}
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,
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.
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>
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.
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.
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:
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.
There are several ways to get around this uncomfortable issue:
web.config
level and the web API authorization filter level. ...
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.
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);
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;
});
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.
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.
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"
$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.
$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.
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 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.
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.
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.
Please refer to example projects - MyDbWebApi in https://github.com/DataBooster/DbWebApi/tree/master/Server/Sample
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:
<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>
namespace MyDbWebApi.Controllers
{
[DbWebApiAuthorize]
public class DbWebApiController : ApiController
{
...
}
}
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.