Concepts

Data Operations

Data Operations

GPALDatabase: Querying and Writing SQL

Connecting

GPAL.Database starts the chain. WithConnectionString takes a full connection string directly, or WithServerName, WithDatabaseName, WithUsername, and WithPassword build one up piece by piece. WithDatabaseType(DatabaseType.SQLServer) says which database this is. SQLServer is the only one GPAL speaks, and it is what generates the SQL when you use the higher-level Create/Read/Update/Delete actions rather than a raw WithSQLCommand.

var db = GPAL.Database

.WithDatabaseType(DatabaseType.SQLServer)

.WithServerName("sql01")

.WithDatabaseName("Orders")

.WithUsername("reporting")

.WithPassword("...")

.ToGPALObject();


// Or build the connection string yourself

var db2 = GPAL.Database

.WithConnectionString("Server=sql01;Database=Orders;...")

.ToGPALObject();


// how long a command may run, and letting the connection go when the work is done

using (IGPALDatabase db3 = GPAL.Database

.WithConnectionString("Server=sql01;Database=Orders;...")

.WithCommandTimeout(300) // five minutes, rather than the default thirty seconds

.ToGPALObject())

{

db3.WithSQLCommand("select count(*) from Orders where Shipped is null");


db3

.Read

.SaveTo(out string waiting); // one value, no grid built around it


GPAL.PublishSimpleEvent(GPALEventType.NOTICE, $"[{waiting}] orders are waiting");

}


// or hand the connection back without disposing, and keep using the same object

db.Close();

NOTE

A connection opens on the first action and stays open, so a workflow holding a database is holding a connection. Close() hands it back, rolling back a transaction still open on it, and the database works afterwards: the next action opens a new connection from the same settings. GPALDatabase is IDisposable for the same job, so it can be held in a using block. WithCommandTimeout(seconds) says how long a command may run. Nothing said leaves the provider's own default, which is thirty seconds for SQL Server, and a report or a wide update often needs more. Zero is SQL Server's word for no limit. A read that returns one value has its own SaveTo. SaveTo(out string) runs the query and takes the first column of the first row, without building a grid around it, and hands back null when the query returned no rows or returned null.

Raw SQL vs Create/Read/Update/Delete

Two paths are available for executing SQL. You can supply a raw SQL string directly and execute it -- the simplest option when you already know exactly what you want to run, including stored procedures. Or you can use the built-in Create, Read, Update, and Delete operations, which accept parameterized SQL, bind values from a grid or individual parameters, and return results into a grid or data table. Both paths end with the same Execute call and return a row count.

// Raw SQL command

int rowCount;

GPAL.Database

.WithConnectionString("...")

.WithSQLCommand("DELETE FROM Orders WHERE Status = 'Cancelled'")

.ToGPALObject()

.Execute(out rowCount);


// Parameterized read into a grid

IGPALGrid<string> results;

GPAL.Database

.WithConnectionString("...")

.WithReadAs("SELECT Id, Total FROM Orders WHERE CustomerId = @CustomerId")

.ToGPALObject()

.WithParameter("42")

.Read

.SaveTo(out results);


// Insert rows from a grid built elsewhere in the workflow

GPAL.Database

.WithConnectionString("...")

.WithCreateAs("INSERT INTO Orders (CustomerId, Total) VALUES (@CustomerId, @Total)")

.ToGPALObject()

.Create

.Using(ordersGrid)

.Execute(out rowCount);

Declaring Typed Parameters

A parameterized command needs to know the type of each @name it uses, and that is what the parameter methods declare. Integers, strings, decimals, datetimes, and table-valued parameters are all covered, with length, precision, and scale set as follow-on calls after the type. The declarations line up in order with the columns of the grid handed to Using, so a single chain writes every row in the grid. GPAL does not create tables. It talks to the ones already there.

GPAL.Database

.WithConnectionString("...")

.WithCreateAs("INSERT INTO Orders (CustomerId, Total, PlacedOn, Status) "

+ "VALUES (@CustomerId, @Total, @PlacedOn, @Status)")

.WithIntegerParameter("CustomerId")

.WithDecimalParameter("Total").WithParameterPrecision(10).WithParameterScale(2)

.WithDatetimeParameter("PlacedOn")

.WithVarCharParameter("Status").WithParameterLength(20)

.ToGPALObject()

.Create

.Using(ordersGrid)

.Execute(out int rowCount);

NOTE

There is no boolean or bit type in GPAL. The recommended pattern is a nullable Datetime column: NULL represents false, meaning the event has not occurred, and a set date represents true. It carries the exact timestamp for free. A DeletedOn column tells you whether a record was deleted and when, at no added cost over a simple flag. Declare it with WithDatetimeParameter and allow nulls in the table.

On this page

Data Operations
GPALDatabase: Querying and Writing SQL
  • Connecting
  • Raw SQL vs Create/Read/Update/Delete
  • Declaring Typed Parameters
    • 💬 Ask GPAL