Add queries

Each query entry produces one generated command method.

SQL text

Method Name    GetAlbumsByArtist
Command Type   SQL Query

SELECT AlbumId AS Id, Title FROM albums WHERE ArtistId = @artistId

{
  "MethodName": "GetAlbumsByArtist",
  "SQLQuery": "SELECT AlbumId AS Id, Title FROM albums WHERE ArtistId = @artistId"
}

CodeGen inspects the command against the configured database to discover inputs and returned columns.

Stored procedure

Method Name             ArchiveInvoices
Command Type            Stored procedure
Stored Procedure Name   dbo.ArchiveInvoices

{
  "MethodName": "ArchiveInvoices",
  "StoredProcName": "dbo.ArchiveInvoices"
}

SQL Server and PostgreSQL expose stored procedure entries through the configured database connection.

SQL file

Method Name    GetOpenInvoices
Command Type   SQL File
SQL File       Sql/GetOpenInvoices.sql

{
  "MethodName": "GetOpenInvoices",
  "SQLFile": "Sql/GetOpenInvoices.sql"
}

A file inside the project is stored as a project relative path. A file outside the project is stored as an absolute path.

C:\Projects\MyApp\Sql\GetOpenInvoices.sql
    -> Sql/GetOpenInvoices.sql

D:\SharedSql\GetOpenInvoices.sql
    -> D:\SharedSql\GetOpenInvoices.sql

A relative file is copied to build and publish output at the same relative path. An absolute file stays absolute and is not copied.

The generated command keeps the configured file reference.

SQL files at runtime

Result name

public partial record GetAlbumsByArtistResult(int Id, string Title);

A configured result set name changes that generated type name.

Method Name       GetAlbumsByArtist
Result Set Name   AlbumRow

public partial record AlbumRow(int Id, string Title);

Parameter correction

Name          @cutoff
Type          datetime2
Is Nullable   false

{
  "MethodName": "ArchiveInvoices",
  "StoredProcName": "dbo.ArchiveInvoices",
  "Parameters": [
    {
      "Name": "@cutoff",
      "Type": "datetime2",
      "IsNullable": false
    }
  ]
}

A correction updates metadata for the named discovered parameter.

PostgreSQL positional parameters

SELECT title FROM album WHERE artist_id = $1

The generated C# argument uses a valid name such as p1. The parameter is added in positional order.

Generated PostgreSQL command

SQLite untyped parameter

{
  "Name": "$id",
  "Type": "integer",
  "IsNullable": false
}

Without a correction, an unresolved SQLite query parameter stays object? and the provider infers its runtime type.

Generated SQLite command

Remove a query

Delete query
Refresh configuration
Generated method is removed

Refresh generated code