VenturaSQL

The 3-tier SQL framework for C# projects.

VenturaSQL is an integrated system that uses web API with binary data transfer to get database data to any client running C# code. Recordsets with modification tracking and a client API provide a rich data-access experience.

The VenturaSQL system is highly recommended for rapidly building rich browser-based apps with Blazor WebAssembly and stand-alone Windows/Android/ios apps with Blazor Desktop and/or .NET MAUI

As a programmer, I want to focus on the business process, and not programming technicalities. I originally developed VenturaSQL to help me with that focus.

• Easy to set up.
• Really, really fast.
• One simple Web API controller that only needs to be set up once.
• There is no data API to define. Just enter SQL statements in the editor and click Generate.
• Client recordsets send only modified data back to the server.
• Well designed client API for advanced functionality.
• Data is packed and transmitted over HTTP in binary format.

Here is a demo of a Blazor WebAssembly app using VenturaSQL for data access.

Where an ORM binds to columns properties dynamically, VenturaSQL is static and the mapping is already done at compile time.

If you already have an ORM up and running, you can add VenturaSQL to the same application and use them side by side. The VenturaSQL runtime DLL is less than 100KB, and easy to uninstall if needed.

VenturaSQL can use any ADO.NET data provider, but currently the only tested providers are:

• System.Data.SqlClient (Microsoft SQL Server)
• System.Data.SQLite (SQLite.org)
• PostgreSQL (Npgsql)

The VenturaSQL system

VenturaSQL has three parts:

• The small NuGet package VenturaSQL.NETStandard for the client runtime.
• The tiny NuGet package VenturaSQL.AspNetCore.Server to process incoming requests in the ASP.NET Core middle-tier.
• The VenturaSQLStudio WPF app that connects to your database and generates recordset source code for both client and server C# projects.

The runtime DLL is 95KB, and the middle-tier DLL is 13KB. VenturaSQL is lightweight, does not use reflection and is very fast.

VenturaSQL Studio

The VenturaSQL Studio WPF app generates recordset classes based on SQL statements you enter in the editor. The generated recordsets are automatically injected into server and client C# projects. Recordsets are generated by clicking the Generate button. When a SQL statement is changed, simply click the Generate button again to re-generate modified recordsets.

Basic recordsets with SQL statements for retrieving rows by primary key(s) are automatically created.

Web API

A single static Web API controller with a POST method needs to be added to the ASP.NET Core project.

    [ApiController]
    public class VenturaSqlController : ControllerBase
    {
        [Route("api/venturasql")]
        [HttpPost]
        public Task Index(byte[] requestData)
        {
            var processor = new VenturaSqlServerEngine();
            processor.RequestData = requestData;
            processor.CallBacks.LookupAdoConnector = LookupAdoConnector;
            processor.Exec();
            return Response.Body.WriteAsync(processor.ResponseBuffer, 0, processor.ResponseLength);
        }

        private AdoConnector LookupAdoConnector(string requestedName)
        {
            return new AdoConnector(SqlClientFactory.Instance, "Server=tcp:xxx,1433;Initial Catalog=VanArsdel;User ID=yyy;Password=zzz;");
        }

Client code

The VenturaSQL C# recordsets running in the browser are optimized for speed and have built in change tracking. Only modified data is transmitted back to the server.

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

var rs = new PriKey_CountryCodes_Recordset();

rs.ExecSql("us");

if (rs.RecordCount != 0)
{
    MessageBox.Show($"Country code 'us' already exists.");
    return;
}

rs.Append();
rs.CountryCodeID = "us";
rs.Name = "United States";
rs.SaveChanges();

Multiple recordsets can be bundled into a single HTTP round-trip and database transaction (commit and rollback) with the Transactional class.

Transactional.SaveChanges(customers, orders, invoices);
Transactional.ExecSql(customers, orders, invoices);

Installing VenturaSQL Studio

Download and run the VenturaSQL Studio installer. The installer comes with ready to run template projects.

or

Download this GitHub repository and open the solution and run the VenturaSQLStudio project. This GitHub repository does not include the template projects. You can download the templates from the Templates-VenturaSQLStudio repository.

See it run on your PC in minutes

The easiest way to get started with VenturaSQL is to run the installer, create a new project with the Blazor WebAssembly template and run it. Open the Getting started with VenturaSQL article for illustrated instructions.

Resources

• Online documentation: https://docs.sysdev.nl
• How to add VenturaSQL to existing projects: https://docs.sysdev.nl/add-to-existing.html
• Frank's blog: https://sysdev.nl
• VenturaSQL home page: https://sysdev.nl/info-venturasql/
• Download the latest version: https://dotnet.sysdev.nl/venturasql

Advanced Features

• In desktop apps, a recordset can also connect to a database server directly.
• A recordset can hold multiple resultsets.
• Updating multiple tables using multiple recordsets can easily be bundled into a single database transaction (for rollback) using the Transactional.SaveChanges() method.
• SQL input and output parameters.
• Calculated columns.
• Column properties (optionally) generate the data binding events INotifyPropertyChanged and INotifyCollectionChanged.
• VenturaSQL Studio has a code snippet generator, for example for creating viewmodels.