Making the case for VenturaSQL

VenturaSQL is open source and the source code is on GitHub.

VenturaSQL started as an in-house tool in the year 2000, then written in VB6 and using XML for data transfer. In 2015 I developed a new version from scratch in C#. While programming, I had the maybe not so luminous idea to expand the tool into a standard component that I could sell to developers on the internet. A standard component must be foolproof. It takes time to polish and improve the design and perfect the API. This stretched development time by factor 4.

I failed miserably in my attempt to market it online. Developers downloading the trial gave positive feedback, but it resulted only in meager sales. I have expanded and perfected VenturaSQL since. I know that VenturaSQL is one of the best developer-productivity tools around, especially for small and medium-sized businesses.

What I ignored while developing the C# version of VenturaSQL is that the industry already moved to developing front-ends running in the browser using JavaScript with Web API. For VenturaSQL the front-end must be able to run C# code as the generated recordset classes are C#. With JavaScript lacking strong-typing, it was of no use to make VenturaSQL Studio generate JavaScript code.

What I really like is that developers can focus on solving business problems in software: integrate planning, tracking orders, managing inventory, sales, marketing, finance, human resources, increasing efficiency and helping the business scale.

Instead it is very common for programmers spending countless months on the technical aspects of software development, caused by development tools where user-interface and database access are programmed on a too-low level.

Programming on a too-low level is very much the case when the choice is made that the new application must run in the browser. For writing business software, the popular frameworks Angular, React and Vue are not productive at all.

In May 2020 Microsoft released the production-ready version of Blazor WebAssembly. Now all browsers can run cross-platform C# code. The VenturaSQL client was already a .Net standard 2.0 library. To make it compatible with Blazor, I only had to add functionality to make it request a HttpClient instance from Blazor. It was up and running in an hour.

Blazor WebAssembly not only gives VenturaSQL a new lease on life. Blazor WebAssembly makes it possible to write business software as a rich browser-based app in a highly productive way. It is similar to writing a desktop app.

Another positive for VenturaSQL is the current trend to move away from dynamic binding (with reflection) to generated source code. This is what VenturaSQL has been doing for years.

I am currently developing Kenova, a framework for rapidly developing business apps for the browser. A demo is on blazordemo.com

VenturaSQL vs ORM

Is VenturaSQL better than an ORM with POCO (Entity) classes? It depends on what you want to achieve.

/* This is a POCO (Plain Old C# Object) / Entity class. A class with only properties or fields */

public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string City { get; set; }

}

An ORM with POCO classes is implemented as follows:

  • The browser serializes request data to JSON format.
  • JSON and/or URL parameters are transmitted to the server in a Http request.
  • The ASP.NET Core server receives the request, deserializes the JSON, and calls a Web API controller.
  • Entity Framework, Dapper or another ORM is used to retrieve and update database data.
  • ASP.NET Core serializes the data to JSON and sends it back to the client in the response.
  • JavaScript deserializes JSON into an Array or other kind of list of POCO classes.

What is great about an object mapper is that it is extremely flexible. You can add and remove properties from POCO (Entity) classes between builds without any problems. The ORM will only set POCO class properties it recognizes. When mapping is not possible these properties are ignored.

Web API with ORM and JSON is perfect for exchanging data with parties outside your organization.

However, in business apps, Web API is mostly used for simply retrieving and updating table data. The relationship between POCO classes and table columns is 1-on-1. When you look at the code inside your controllers, and they mostly contain the same code, there is probably only 1-on-1 table-column to POCO-column going on. In this scenario, VenturaSQL is the much more developer-friendly and faster-at-runtime solution.

VenturaSQL injects recordset classes into the Blazor WebAssembly project. A recordset has intelligence, where a list with POCO classes is stone age. It is simply too primitive.

A recordset is a collection of records. A VenturaSQL record is a POCO class on steroids, it keeps tracks of many things:

  • Is the record existing, new or marked for deletion?
  • The original value of columns. When a column value is changed, and then changed back to the original value, it returns to unmodified status.
  • Is the column modified or not?

Using the described bookkeeping:

  • All new, modified and deleted records are transmitted back to the server in a single Http request.
  • Only modified column data is transmitted to the server.

VenturaSQL packs the data transmitted and received into binary format. Record properties have a 1-on-1 relationship with table columns, object mapping (using .NET reflection) is not used. That is why VenturaSQL is so fast.

Editing a recordset definition with VenturaSQL Studio:

Retrieving data in Blazor WebAssembly or other client:

VenturaSqlConfig.DefaultConnector = new HttpConnector("DefaultConnector", "api/venturasql");

var rs = new PriKey_Customers_Recordset();

await rs.ExecSqlAsync(CustomerID); // Sends the Http request to Web API and processes the response

if (rs.RecordCount != 0)
{
    FirstName = rs.FirstName;
    LastName = rs.LastName;
    City = rs.City;
}

Saving modified and new data:

if (Mode == PageMode.New)
    rs.Append();

rs.FirstName = FirstName;
rs.LastName = LastName;
rs.City = City;

await rs.SaveChangesAsync();

There is only one controller method for the VenturaSQL Web API, and the controller is static:

    [ApiController]
    [Authorize]
    public class VenturaSqlController : ControllerBase
    {
        [Route("api/venturasql")]
        [HttpPost]
        public async Task<IActionResult> Index(byte[] requestData)
        {
            var processor = new VenturaSqlServerEngine();
            processor.CallBacks.LookupAdoConnector = () => new AdoConnector(SqlClientFactory.Instance, 
                     "Server=tcp:sysdev.nl,1433;Initial Catalog=AdventureWorks;...");
            await processor.ExecAsync(requestData);
            Response.ContentType = "application/octet-stream";
            await Response.Body.WriteAsync(processor.ResponseBuffer, 0, processor.ResponseLength);
            return Ok();
        }
    }

See it run on your PC in minutes

The easiest way to get started with VenturaSQL is to download and run the installer. Go to the Getting started with VenturaSQL page for detailed instructions.

Leave a Reply

Your email address will not be published. Required fields are marked *