Concepts

Data Operations

Data Operations

GPALDatabase: Querying and Writing SQL

GPALDatabase wraps a connection to SQL Server, MySQL, or PostGres behind the same fluent settings-then-action pattern as the rest of GPAL, whether you're running a raw SQL command or building a parameterized Create/Read/Update/Delete.

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 | MySQL | PostGres) tells GPAL which dialect to generate SQL for 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();

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'")

.Execute(out rowCount);

// Parameterized read into a grid

IGPALGrid<string> results;

GPAL.Database

.WithConnectionString("...")

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

.Read

.WithParameter("42")

.IntoGrid(out results)

.Execute(out rowCount);

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

GPAL.Database

.WithConnectionString("...")

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

.Create

.Using(ordersGrid)

.Execute(out rowCount);

Defining Table Schemas

When creating a table rather than querying one, GPALDatabase lets you describe each column by name and type in a fluent chain -- integers, strings, decimals, datetimes, and table-valued columns are all covered. Length, precision, and scale are set as follow-on calls after the column type. A row count cap is available to limit how many rows a result set returns.

GPAL.Database

.WithConnectionString("...")

.WithTableName("Orders")

.WithIntegerColumn("Id")

.WithIntegerColumn("CustomerId")

.WithDecimalColumn("Total").WithColumnPrecision(10).WithColumnScale(2)

.WithDatetimeColumn("PlacedOn")

.WithVarCharColumn("Status").WithColumnLength(20)

.ToGPALObject();

TIP

There is no dedicated boolean/bit column type in GPAL. The recommended pattern is a nullable Datetime column: NULL represents false (the event has not occurred) and a set date represents true. And also carries the exact timestamp it was set. A deleted_on column tells you whether a record was deleted and when, at no added cost over a simple flag. Use WithDatetimeColumn and allow nulls in your schema.

On this page

Data Operations
GPALDatabase: Querying and Writing SQL
  • Connecting
  • Raw SQL vs Create/Read/Update/Delete
  • Defining Table Schemas