Last updated on 11 November 2021.
The source code for VenturaSQL 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 in C# from scratch. For a while I sold the tool as a standard-component to developers through my website.
Throughout the years I have maintained, expanded and perfected VenturaSQL.
The industry standard for line of business software is front-ends running in the browser using JavaScript with a Web API. VenturaSQL never supported this, as the front-end must be able to run C# code for running the generated recordset classes. This made VenturaSQL a niche tool, until Microsoft released Blazor WebAssembly.
Blazor WebAssembly
In May 2020 Microsoft released Blazor WebAssembly. Now all browsers can run cross-platform C# code. The VenturaSQL client is a .NET Standard 2.0 library and is compatible with Blazor.
Blazor WebAssembly makes it possible to rapidly write a rich browser-based app in a highly productive way. It is similar to writing a desktop program. This is exactly what VenturaSQL was designed for.
VenturaSQL is a productivity tool
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 application programmers spending countless months on the technical aspects of software development, due to development tools where user-interface and database access are programmed on a too-low level. For writing business software, the popular frameworks Angular, React and Vue are not productive at all.
VenturaSQL takes care of database access. Enter SQL statements in VenturaSQL Studio, click the Generate button, and the server and client are database access are generated automatically as C# code.
Another positive for VenturaSQL is the current shift away from dynamic binding (with reflection) towards generated source code. This is what VenturaSQL has been doing for years, VenturaSQL does not use reflection. Everything is hard coded.
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 in most cases. 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 is a collection of records. A recordset has intelligence. A VenturaSQL recordset is a POCO class on steroids, it keeps tracks of many things:
- Is a 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.
