ODataDB

ODataDB

Getting Started

ODataDB is an ASP.NET application that allows working with databases via OData.

Accordingly, users may use OData clients to get and edit database data over the Internet.

For example, users may use Microsoft Excel with the SaveToDB add-in installed.

ODataDB brings huge advantages for database developers as the applications are configured on the server side.
Moreover, database developers may create full-featured tree-tier applications using database development skills only.

ODataDB allows implementing OData web services without creating Entity Framework models using Visual Studio.

Core principles:

  • ODataDB is a ready-to-use solution.
    Just configure an application on IIS or IIS Express and configure database connection strings.
  • ODataDB connects to databases under user credentials.
    So users may get and update only permitted data.
  • ODataDB publishes tables automatically.
    The application shows the same objects as for direct connections using IDE.
  • The applications are configured using database configuration objects.
  • ODataDB supports several major databases from the box.
  • ODataDB supports client connections with OData versions 1.0-4.0.

ODataDB supports:

  • Microsoft SQL Server
  • Microsoft SQL Server Compact Edition
  • Oracle Database
  • IBM DB2
  • MySQL and MariaDB
  • NuoDB
  • PostgreSQL

 

You may start learning about ODataDB on the following topics:

Change History

Version 1.5, December 20, 2016

Changes:

  • Updated documentation.
  • Fixed minor bugs.

Version 1.4, September 28, 2015

New Features:

  • Support of the lambda operators any and all on navigation- and collection-valued properties.
  • Support of /$value on media entities and individual properties.
  • Support of aliases in $filter expressions.

Version 1.3, September 17, 2015

New Features:

  • Support of $select.
  • Support of eq and ne filter operations on properties of entities.
  • Support of additional filter operations.
  • Support of the canonical functions.
  • Support of $filter on expanded entities.
  • Support the /$count segment on navigation and collection properties.

Version 1.2, September 7, 2015

New Features:

  • Metadata includes navigation properties.
  • Support of $expand.

Fixed Bugs:

  • Getting data from Oracle Database.

Version 1.1, August 20, 2015

New Features:

  • Support of server-driven paging.
    Set the page size in the MaxPageSize app setting in web.config.
  • Support of the odata.maxpagesize header.

Version 1.0, August 5, 2015

New Features:

  • CRUD operations for database tables.
  • Support of a minimal set of OData service operations.
  • Support of a service document at the service root.
  • Support of metadata at $metadata.
  • Support of batch requests.
  • Support of $top and $skip.
  • Support of basic and Windows authentication.

Supported OData Versions:

  • 4.0
  • 1.0-3.0

Supported Database Platforms:

  • Microsoft SQL Server
  • Microsoft SQL Server Compact Edition
  • Oracle Database
  • IBM DB2
  • MySQL and MariaDB
  • NuoDB
  • PostgreSQL

OData Service Conformance Level

ODataDB partially supports OData Version 4.0 and OData Versions 1.0-3.0.

See the latest specifications of OData protocols at

http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part1-protocol.html
http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html
http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part3-csdl.html

https://msdn.microsoft.com/en-us/library/dd541188.aspx

The table below shows the status of supporting specification conventions.

Requirement4.03.0
OData Minimal Conformance Level
1. MUST publish a service document at the service root (section 11.1.1)
2. MUST return data according to at least one of the OData defined formats (section 7)
3. MUST support server-driven paging when returning partial results (section 11.2.5.7)
4. MUST return the appropriate OData-Version header (section 8.1.5)
5. MUST conform to the semantics the following headers, or fail the request
    5.1. Accept (section 8.2.1)
    5.2. OData-MaxVersion (section 8.2.7)
6. MUST follow OData guidelines for extensibility (section 6 and all subsections)
7. MUST successfully parse the request according to [OData-ABNF] for any supported system query string options and either follow the specification or return 501 Not Implemented (section 9.3.1) for any unsupported functionality (section 11.2.1)
8. MUST expose only data types defined in [OData-CSDL]
9. MUST NOT require clients to understand any metadata or instance annotations (section 6.4), custom headers (section 6.5), or custom content (section 6.2) in the payload in order to correctly consume the service
10. MUST NOT violate any OData update semantics (section 11.4 and all subsections)
11. MUST NOT violate any other OData-defined semantics
12. SHOULD support $expand (section 11.2.4.2)
13. MAY publish metadata at $metadata according to [OData-CSDL] (section 11.1.2)
Updatable OData Service
14. MUST include edit links (explicitly or implicitly) for all updatable or deletable resources according to [OData-Atom] and [OData-JSON]
15. MUST support POST of new entities to insertable entity sets (section 11.4.1.5 and 11.4.2.1)
16. MUST support POST of new related entities to updatable navigation properties (section 11.4.6.1)xx
17. MUST support POST to $ref to add an existing entity to an updatable related collection (section 11.4.6.1)xx
18. MUST support PUT to $ref to set an existing single updatable related entity (section 11.4.6.3)xx
19. MUST support PATCH to all edit URLs for updatable resources (section 11.4.3)
20. MUST support DELETE to all edit URLs for deletable resources (section 11.4.5)
21. MUST support DELETE to $ref to remove an entity from an updatable navigation property (section 11.4.6.2)xx
22. MUST support if-match header in update/delete of any resources returned with an ETag (section 11.4.1.1)xx
23. MUST return a Location header with the edit URL or read URL of a created resource (section 11.4.1.5)
24. MUST include the OData-EntityId header in response to any POST/PATCH that returns 204 No Content (Section 8.3.3)
25. MUST support Upserts (section 11.4.4)
26. SHOULD support PUT and PATCH to an individual primitive (section 11.4.9.1) or complex (section 11.4.9.3) property (respectively)xx
27. SHOULD support DELETE to set an individual property to null (section 11.4.9.2)xx
28. SHOULD support deep inserts (section 11.4.2.2)xx
Requirement4.03.0
OData Intermediate Conformance Level
1. MUST conform to the OData Minimal Conformance Level
2. MUST successfully parse the [OData-ABNF] and either follow the specification or return 501 Not Implemented for any unsupported functionality (section 9.3.1)
3. MUST support $select (section11.2.4.1)
4. MUST support casting to a derived type according to [OData URL] if derived types are present in the model
5. MUST support $top (section 11.2.5.3)
6. MUST support /$value on media entities (section 4.10. in [OData URL]) and individual properties (section 11.2.3.1)
7. MUST support $filter (section 11.2.5.1)
    7.1. MUST support eq, ne filter operations on properties of entities in the requested entity set (section 11.2.5.1.1)
    7.2. MUST support aliases in $filter expressions (section 11.2.5.1.3)
    7.3. SHOULD support additional filter operations (section 11.2.5.1.1) and MUST return 501 Not Implemented for any unsupported filter operations (section 9.3.1)
    7.4. SHOULD support the canonical functions (section 11.2.5.1.2) and MUST return 501 Not Implemented for any unsupported canonical functions (section 9.3.1)
    7.5. SHOULD support $filter on expanded entities (section 11.2.4.2.1)
8. SHOULD publish metadata at $metadata according to [OData-CSDL] (section 11.1.2)
9. SHOULD support the [OData-JSON] format
10. SHOULD consider supporting basic authentication as specified in [RFC2617] over HTTPS for the highest level of interoperability with generic clients
11. SHOULD support the $search system query option (section 11.2.5.6)xx
12. SHOULD support the $skip system query option (section 11.2.5.4)
13. SHOULD support the $count system query option (section 11.2.5.5)
14. SHOULD support $expand (section 11.2.4.2)
15. SHOULD support the lambda operators any and all on navigation- and collection-valued properties (section 5.1.1.5 in [OData URL])
16. SHOULD support the /$count segment on navigation and collection properties (section 11.2.9)
17. SHOULD support $orderby asc and desc on individual properties (section 11.2.5.2)
Requirement4.03.0
OData Advanced Conformance Level
1. MUST conform to at least the OData Intermediate Conformance Level
2. MUST publish metadata at $metadata according to [OData-CSDL] (section 11.1.2)
3. MUST support the [OData-JSON] format
4. MUST support the /$count segment on navigation and collection properties (section 11.2.9)
5. MUST support the lambda operators any and all on navigation- and collection-valued properties (section 5.1.1.5 in [OData URL])
6. MUST support the $skip system query option (section 11.2.5.4)
7. MUST support the $count system query option (section 11.2.5.5)
8. MUST support $orderby asc and desc on individual properties (section 11.2.5.2)
9. MUST support $expand (section 11.2.4.2)
    9.1. MUST support returning references for expanded properties (section 11.2.4.2)xx
    9.2. MUST support $filter on expanded entities (section 11.2.4.2.1)
    9.3. MUST support cast segment in expand with derived types (section 11.2.4.2.1)xx
    9.4. SHOULD support $orderby asc and desc on individual properties (section 11.2.4.2.1)xx
    9.5. SHOULD support the $count system query option for expanded properties (section 11.2.4.2.1)
    9.6. SHOULD support $top and $skip on expanded properties (section 11.2.4.2.1)xx
    9.7. SHOULD support $search on expanded properties (section 11.2.4.2.1)xx
    9.8. SHOULD support $levels for recursive expand (section 11.2.4.2.1.1)xx
10. MUST support the $search system query option (section 11.2.5.6)xx
11. MUST support batch requests (section11.7 and all subsections)
12. MUST support the resource path conventions defined in [OData URL]xx
13. SHOULD support Asynchronous operations (section 8.2.8.8)xx
14. SHOULD support Delta change tracking (section 8.2.8.6)xx
15. SHOULD support cross-join queries defined in [OData URL]xx
16. SHOULD support a conforming OData service interface over metadata (section 11.1.3)xx

Edition Comparison

All the ODataDB editions have the same installation media and features.

You may change the licensed edition using the registration procedure.

FeatureUnregisteredExpressEnterprise
20 Users200 UsersUnlimited
Features
All supported OData features
All supported database platforms
Basic authentication
Maximum number of connected users1520200Unlimited
Licensing
Commercial use

System Requirements

Supported Architectures:

  • x86
  • x64

Supported Operating Systems:

  • Windows XP SP3, Vista SP1, 7, 8, 8.1, 10
  • Windows Server 2003 SP3, 2008, 2008 R2, 2012, 2012 R2, 2016

Supported Versions of Internet Information Server (IIS):

  • IIS 7.5, 8.0
  • IIS Express 7.5, 8.0

Supported Versions of Microsoft SQL Server:

  • Microsoft SQL Server 2005, 2008, 2008 R2
  • Microsoft SQL Server 2012, 2014 including Express LocalDB
  • Microsoft Azure SQL Database

Supported Versions of Microsoft SQL Server Compact:

  • Microsoft SQL Server Compact 3.5, 4.0

Supported Versions of Oracle Database:

  • Oracle Database 10g Release 1, Release 2
  • Oracle Database 11g Release 1, Release 2
  • Oracle Database 12c Release 1

Supported Versions of IBM DB2:

  • IBM DB2 9.5, 9.7, 9.8, 10.1, 10.5, 11.1

IBM DB2 .NET Provider, IBM DB2 OLE DB Provider or IBM DB2 ODBC driver installed is required.

Supported Versions of MySQL:

  • MySQL 5.0, 5.1, 5.2, 5.5, 5.6, 5.7

Supported Versions of SkySQL MariaDB:

  • MariaDB 5.1, 5.2, 5.3, 5.5, 10.0, 10.1, 10.2

All application features for MySQL are completely compatible with MariaDB.
ADO.Net Driver for MySQL and MySQL ODBC drivers can be used to connect to MariaDB.
MariaDB ODBC Driver 1.0 is not supported.

Supported Versions of NuoDB:

  • NuoDB 2.0.4, 2.2

Supported Versions of PostgreSQL:

  • PostgreSQL 9.0, 9.1, 9.2, 9.3, 9.4

End-User License Agreement

This End-User License Agreement (EULA) is a legal agreement between you (either an individual or a single entity) and Gartle Technology Corporation for any ODataDB software, use examples and documentation (Software) that accompany this EULA.

YOU AGREE TO BE BOUND BY THE TERMS OF THIS EULA BY INSTALLING, COPYING, OR OTHERWISE USING THE SOFTWARE.

IF YOU DO NOT AGREE, DO NOT INSTALL, COPY, OR USE THE SOFTWARE.

Your licensing of Software is in accordance with the terms of the EULA in effect at the time of such licensing. By licensing Software, you accept and agree to the EULA in effect at such time.

  1. GRANT OF LICENSE: Gartle Technology Corporation grants you the following rights provided that you comply with all terms and conditions of this EULA:
    1. Installation and Use: You may install, use, access, display and run one (1) copy of the Software per license, on a non-exclusive basis and without right of sublicense or concurrent use, on a single computer or machine, such as a workstation.
      Software has several editions with different features and limits described at the official website www.odatadb.com.
      You may use the ODataDB Express edition for free. You may purchase and register the ODataDB Enterprise edition.
    2. Software Transfer: You may transfer the Software to a different internal workstation or user so long as you have purchased a License for each such workstation or user. You may not, however, transfer the Software to a Third Party.
    3. Use of Examples: You may install, access, modify and use Software examples, including SQL codes and batch files, for your private or company-internal purposes.
  2. LIMITATIONS: You may not use, copy, modify, display, rent, lease, loan, transfer, distribute, download, merge, or make any translation or derivative work of the Software, except as expressly provided herein. You may not reverse engineer, decompile, or disassemble the Software, except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation.
  3. INTELLECTUAL PROPERTY RIGHTS AND CONFIDENTIALITY: The Software, including methods, processes and/or techniques utilized therein, is owned by, proprietary to and valuable trade secrets of Gartle Technology Corporation and is protected by Russian Federation copyright law and international treaties. You agree to take no actions that impair or infringe Gartle Technology Corporation's intellectual property rights in the Software. You agree not to remove, efface or obscure any copyright notices, other proprietary markings or confidentiality legends placed upon or contained within the Software.
  4. DISCLAIMER OF WARRANTIES: Gartle Technology Corporation disclaims all warranties concerning the Software and Services (if any), express, implied, or statutory, including without limitation, any warranties, duties or conditions of merchantability or fitness for a particular purpose, warranties of reliability or availability, of accuracy or completeness of responses, of results, of workmanlike effort, of lack of viruses, and of lack of negligence, all with regard to the Software, and the provision of or failure to provide support or other services, information, software, and related content through the Software or otherwise arising out of the use of the Software. Gartle Technology Corporation does not warrant that the Software will operate in combination with other software products selected by you, or that the Software will operate uninterrupted or error-free. Additionally, Gartle Technology Corporation and its suppliers provide the Software and Services AS IS AND WITH ALL FAULTS. THERE IS NO WARRANTY OR CONDITION OF TITLE, QUIET ENJOYMENT, QUIET POSSESSION, CORRESPONDENCE TO DESCRIPTION, OR NONINFRINGEMENT WITH REGARD TO THE SOFTWARE.
  5. NO LIABILITY: TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, IN NO EVENT SHALL GARTLE TECHNOLOGY CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY SPECIAL, INCIDENTAL, PUNITIVE, INDIRECT, OR CONSEQUENTIAL DAMAGES WHATSOEVER (INCLUDING, BUT NOT LIMITED TO, DAMAGES FOR LOSS OF PROFITS OR CONFIDENTIAL OR OTHER INFORMATION, FOR LOSS OF DATA, FOR BUSINESS INTERRUPTION, FOR PERSONAL INJURY, FOR LOSS OF PRIVACY, FOR FAILURE TO MEET ANY DUTY INCLUDING OF GOOD FAITH OR OF REASONABLE CARE, FOR NEGLIGENCE, AND FOR ANY OTHER PECUNIARY OR OTHER LOSS WHATSOEVER) ARISING OUT OF OR IN ANY WAY RELATED TO THE USE OF OR INABILITY TO USE THE SOFTWARE, THE PROVISION OF OR FAILURE TO PROVIDE SUPPORT OR OTHER SERVICES, INFORMATION, SOFTWARE, AND RELATED CONTENT THROUGH THE SOFTWARE OR OTHERWISE ARISING OUT OF THE USE OF THE SOFTWARE, OR OTHERWISE UNDER OR IN CONNECTION WITH ANY PROVISION OF THIS EULA, EVEN IN THE EVENT OF THE FAULT, TORT (INCLUDING NEGLIGENCE), MISREPRESENTATION, STRICT LIABILITY, BREACH OF CONTRACT OF GARTLE TECHNOLOGY CORPORATION OR ANY SUPPLIER, AND EVEN IF GARTLE TECHNOLOGY CORPORATION OR ANY SUPPLIER HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
  6. LIMITATION ON REMEDIES: Within the first thirty (30) days after your receipt of the Software, should you encounter and report to Gartle Technology Corporation within such time period a reproducible error that causes the Software not to perform in all material respects as set forth in the Software documentation, then Gartle Technology Corporation will, at its sole discretion, either: a) resolve the error or malfunction, and modify or replace the Software (if deemed necessary by Gartle Technology Corporation); or b) allow you to terminate this EULA with respect to the non-conforming Software and, upon your return of the Software to Gartle Technology Corporation, Gartle Technology Corporation shall provide you with the lesser of a) the actual damages incurred by you; or b) the amount you paid for the non-conforming Software. The remedies described in this section shall be your sole and exclusive remedies under this EULA.
  7. GENERAL PROVISIONS
    1. Reservation of Rights and Ownership: Gartle Technology Corporation reserves all rights not expressly granted to you in this EULA. The Software is licensed, not sold.
    2. Consent to Use of Data: You agree that Gartle Technology Corporation and its affiliates may collect and use technical information gathered as part of the Software support services provided to you, if any, related to the Software. Gartle Technology Corporation may use this information solely to improve Gartle Technology Corporation products or to provide customized services or technologies to you and will not disclose this information in a form that personally identifies you.
    3. Links to Third Party Sites: We may link to third-party sites through the use of the Software. The third-party sites are not under the control of Gartle Technology Corporation, and Gartle Technology Corporation is not responsible for the contents of any third-party sites, any links contained in third-party sites, or any changes or updates to third-party sites. Gartle Technology Corporation is not responsible for webcasting or any other form of transmission received from any third-party sites. Gartle Technology Corporation is providing these links to third-party sites to you only as a convenience, and the inclusion of any link does not imply an endorsement by Gartle Technology Corporation of the third-party site.
    4. Additional Software/Services: This EULA applies to updates, supplements, add-on components, or Internet-based services components, of the Software that Gartle Technology Corporation may provide to you or make available to you after the date you obtain your initial copy of the Software, unless Gartle Technology Corporation provides other terms along with the update, supplement, add-on component, or Internet-based services component. Gartle Technology Corporation reserves the right to discontinue any Internet-based services provided to you or made available to you through the use of the Software.
    5. Upgrades: To use Software identified as an upgrade, you must first be licensed for the software identified by Gartle Technology Corporation as eligible for the upgrade. After upgrading, you may no longer use the software that formed the basis for your upgrade eligibility.
    6. Applicable Law: This EULA is governed by the laws of the Russian Federation. Any legal action or proceeding relating to this EULA shall be instituted in a court of arbitration in the Moscow City, Russian Federation. Gartle Technology Corporation and you agree to submit to the jurisdiction of, and agree that venue is proper in, these courts in any such action or proceeding. The prevailing party in any action to enforce this EULA will be entitled to recover its attorney fees and costs in connection with such action.
    7. Waiver: The failure of either party to enforce any of the terms of this EULA shall not be construed as a waiver of future enforcement of that or any other term.
    8. Entire Agreement and Severability: This EULA (including any addendum or amendment to this EULA which is included with the Software) is the entire agreement between you and Gartle Technology Corporation relating to the Software and the support services (if any) and it supersedes all prior or contemporaneous oral or written communications, proposals and representations with respect to the Software or any other subject matter covered by this EULA. To the extent the terms of any Gartle Technology Corporation policies or programs for support services conflict with the terms of this EULA, the terms of this EULA shall control. If any provision of this EULA is held to be void, invalid, unenforceable or illegal, the other provisions shall continue in full force and effect.
    9. Termination: Without prejudice to any other rights, Gartle Technology Corporation may terminate this EULA if you fail to comply with the terms and conditions of this EULA. In such event, you must destroy all copies of the Software and all of its component parts and you will not be entitled to any refund of monies.

Using ODataDB

Quick Steps

To quickly test using OData, you have to complete the following two steps:

  1. Install Internet Information Server (IIS) or Internet Information Server Express.
  2. Install the ODataDB application to run under IIS.

These steps are described in the following topic:

ODataDB includes the preconfigured Northwind example database with an empty password.

So, you may try the following URLs after completing the steps:

  • http://localhost:40440/odatadb.svc/v4/NorthwindCE/default/
  • http://localhost:40440/odatadb.svc/v4/NorthwindCE/default/$metadata
  • http://localhost:40440/odatadb.svc/v4/NorthwindCE/default/Customers

You may connect to your data services using any browser or universal OData client.

For example, you may try the following HTML pages:

  • http://localhost:40440/examples/table.htm
  • http://localhost:40440/examples/table.htm?url=http%3A%2F%2Flocalhost%3A40440%2Fodatadb.svc%2Fv4%2FNorthwindCE%2Fdefault%2FCustomers

In the table.htm page you have to specify the URL to get OData service data.

The second URL already includes the URL to get customers.

Note that you may get and edit data in Microsoft Excel with the SaveToDB add-in installed.

Next Steps

After successful initial steps you may:

  1. Configure connections to your databases.
  2. Connect to your databases via Internet or Intranet using OData clients.
  3. Implement applications connected to your databases via OData (using C#, VB, JavaScript, etc.).

Please read these topics first:

You may check currently supported OData features here:

The current ODataDB version supports working with databases tables only.
We plan to implement working with views and stored procedures, and configuring the services on the server side using database objects.

Going into Production

Please contact us if you plan to use ODataDB in the production.

You may read about ODataDB editions and registering the product here:

Concept

ODataDB allows getting and updating database table data via OData:

ODataDB Core Idea

ODataDB is a regular ASP.NET application hosted under Internet Information Server (IIS).

ODataDB reads database metadata under user credentials and publishes user specific service and metadata documents.

Users may select and update data. ODataDB translates client queries to database CRUD operations.

To make a database available through OData, just add a connection string to the web.config file.

For example, the web.config file contains the following connection strings named as AzureDemo50 and NorthwindCE:

<connectionStrings>
  <add name="AzureDemo50" connectionString="Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=AzureDemo50;Integrated Security=SSPI"
    providerName="System.Data.OleDb" />
  <add name="NorthwindCE" connectionString="Data Source=C:\ODataDB\DB\Northwind.sdf"
    providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>

Also, for example, the ODataDB application is configured with the service URL http://localhost:40440/odatadb.svc/.

In this case, ODataDB creates two groups of OData services via URLs:

  • http://localhost:40440/odatadb.svc/v4/AzureDemo50/default/
  • http://localhost:40440/odatadb.svc/v3/AzureDemo50/default/
  • http://localhost:40440/odatadb.svc/v2/AzureDemo50/default/
  • http://localhost:40440/odatadb.svc/v1/AzureDemo50/default/
  • http://localhost:40440/odatadb.svc/v4/NorthwindCE/default/
  • http://localhost:40440/odatadb.svc/v3/NorthwindCE/default/
  • http://localhost:40440/odatadb.svc/v2/NorthwindCE/default/
  • http://localhost:40440/odatadb.svc/v1/NorthwindCE/default/

For example, you may use the following URL to get the service metadata:

  • http://localhost:40440/odatadb.svc/v4/NorthwindCE/default/$metadata

and the following URL to get customers:

  • http://localhost:40440/odatadb.svc/v4/NorthwindCE/default/Customers

See the latest specifications of OData protocols at

http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part1-protocol.html

https://msdn.microsoft.com/en-us/library/dd541188.aspx

Service URLs

The formal service URL format is:

<service root>/<version>/<connection string name>/<service name>/ 

where <version> is v1, v2, v3, or v4, and <service name> must be "default" for the current ODataDB implementation. 

Connecting to Databases

ODataDB should connect to databases under user credentials. This ensures that users may get and update only permitted data.

If a database supports integrated security, you may configure a connection string with integrated security and turn on Windows authentication for the ODataDB application in IIS.

In this case, ODataDB impersonates connected users and connects to a database using the integrated security.

If the basic authentication is configured, ODataDB changes the connection string to the provided username and password, and connects under the user login instead of integrated security.

 

Installing ODataDB

ODataDB is a regular ASP.NET application and may be installed under IIS or IIS Express.

After installing ODataDB edit connection strings in the web.config file for databases available through OData.

Installing under IIS Express

  1. Download and install IIS Express.
    IIS Express 8.0: http://www.microsoft.com/en-us/download/details.aspx?id=34679
    IIS Express 7.5: http://www.microsoft.com/en-us/download/details.aspx?id=1038
  2. Unzip the downloaded ODataDB package to any folder on a local drive.
    For example, you may unzip the package to the C:\ODataDB folder.
  3. Go to the odatadb subfolder and run the start-under-iis-express.cmd batch file.

Then open the test URL: http://localhost:40440/odatadb.svc/NorthwindCE/default/$metadata

You may add start-under-iis-express.cmd to the Startup folder to run IIS Express and ODataDB automatically when Windows starts.

Installing under IIS

  1. Open the Internet Information Services (IIS) Manager.
  2. Right-click Sites and click Add Web Site...
  3. Provide a name and path for the new site.
    Ensure that "HTTP" is selected as the type.

Then open the test URL like http://localhost/odatadb/odatadb.svc/NorthwindCE/default/$metadata

Configuring Connections

ODataDB allows connecting to multiple databases using named connection strings in the web.config file.

For example, if the web.config file contains the following connection strings:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="AzureDemo50" connectionString="Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=AzureDemo50;Integrated Security=SSPI"
      providerName="System.Data.OleDb" />
    <add name="NorthwindCE" connectionString="Data Source=C:\ODataDB\DB\Northwind.sdf"
      providerName="System.Data.SqlServerCe.4.0" />
  </connectionStrings>
</configuration>

ODataDB will create two OData services with the following URLs:

  • http://localhost:40440/odatadb.svc/AzureDemo50/default/
  • http://localhost:40440/odatadb.svc/NorthwindCE/default/

See details in the Web.config Files topic.

Web.config Files

ODataDB reads configuration from web.config files.

There are two main sections:

  • connectionStrings
  • and DbProviderFactories.

Connection Strings

Here is an example of the connectionStrings section:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="AzureDemo50" connectionString="Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=AzureDemo50;Integrated Security=SSPI"
      providerName="System.Data.OleDb" />
    <add name="NorthwindCE" connectionString="Data Source=~\App-Data\Northwind.sdf"
      providerName="System.Data.SqlServerCe.4.0" />
  </connectionStrings>
</configuration>

Accordingly, ODataDB publishes databases via URLs like:

  • http://localhost:40440/odatadb.svc/v4/AzureDemo50/default/
  • http://localhost:40440/odatadb.svc/v4/NorthwindCE/default/

You may edit connection strings in visual mode using gConnectionManager.

Important! Create connection strings with integrated security or erase real passwords after creating connection strings.

DbProviderFactories

The web.config files can also contain database provider configurations if the providers are located in the bin subdirectory.

For example, ODataDB includes the MySQL .NET provider that is specified in the DbProviderFactories section:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.data>
    <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient"/>
      <add name="MySQL Data Provider"
          invariant="MySql.Data.MySqlClient"
          description=".Net Framework Data Provider for MySQL"
          type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.8.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
</configuration>

By default, ODataDB includes .NET database providers for:

  • Microsoft SQL Server
  • Microsoft SQL Server Compact
  • Oracle Database
  • MySQL
  • PostgreSQL
  • NuoDB

 

gConnectionManager

gConnectionManager allows editing connection strings in web.config files.

Start gConnectionManager.exe and open the desired web.config file using the File, Open menu item.

Here is a configuration file example:

Connection Manager

The file contains two connection strings: AzureDemo50 and NorthwindCE.

Here is an example of selecting a connection provider:

Connection Manager - Selecting database connection provider

Here is an example of connecting to a database:

Connection Manager - Connecting to database

Authentication

ODataDB supports the following authentication methods:

  • Basic authentication.
  • Windows authentication.

The basic authentication is used by default.

Follow Internet Information Server instructions to turn on Windows authentication.

Basic Authentication

In this mode, ODataDB requires a username and a password.

Then ODataDB changes database connection string to the actual username and password and tries to connect to a database.

If the connection is successful, ODataDB performs the required action.

Otherwise, ODataDB returns the HTTP error 401 "Unauthorized".

Windows Authentication

In this mode, ODataDB impersonates the active user and makes a request to a database using the connection string specified in the web.config file as is.

Be sure that you specify the connection string with the integrated security.

If a user has no permissions to connect to a database, ODataDB returns the HTTP error 401 "Unauthorized",
and the user may specify a username and a password to connect to the database with another name.

Product Registration

ODataDB has several editions.

The registration is required to register the ODataDB Express or Enterprise editions.

To start registering, run the Register-ODataDB.cmd file from the setup folder.

A product code is required to register the ODataDB Enterprise edition. The product code is sent by email after purchasing.

Selecting Edition

Select an edition and fill in the product code for the Personal or Enterprise edition.

ODataDB Registration - Select Edition

Select Edition

Licensee Data

Please complete the registration form carefully.

Select an edition and fill in the product code for the Personal or Enterprise edition.

The Next button is enabled when all the required fields are filled.

Don't forget to check the required field about the personal data use.

ODataDB Registration - Fill personal data

Fill personal data

Online Registration

This step allows you to check your email address and to pause before the final step.

If the licensee data is valid, click Next.

You may return to the previous step using the Back button.

After clicking the Next button, the ODataDB connects to the registration server.

ODataDB Registration - Check online data

Check online data

If the connection is successful, the final step screen is shown.

If any error occurred during connection, you might try to register the product later or try to register the product by email.

You may set the registration for all users of the computer. This action requires administrator privileges.

Click Finish.

ODataDB Registration - Final step

Final step

Registration by Email

If you choose the Register by email option on the Licensee Data step, the following screen is displayed:

The Next button starts the default email program, creates a registration email, and activates the next step.
Don't forget to send the email.

ODataDB Registration - Check email data

Check email data

If starting of the email program has failed, you may create the registration email manually using the shown registration data.
Please carefully copy the To, Subject, and the Message fields.

The registration server sends the reply in a couple of seconds; but you may close the dialog box and open it again, in the same step.

Please copy the activation code from the received registration email and paste it into the Activation code field.

The Next button is enabled when the activation code is valid.

Click Next to continue.

ODataDB Registration - Activation

Activation

The ODataDB checks the registration data and confirms the registration.

You may set the registration for all users of the computer. This action requires administrator privileges.

Click Finish.

ODataDB Registration - Final step

Final step