Flowsy.Db.Conventions
1.1.0
dotnet add package Flowsy.Db.Conventions --version 1.1.0
NuGet\Install-Package Flowsy.Db.Conventions -Version 1.1.0
<PackageReference Include="Flowsy.Db.Conventions" Version="1.1.0" />
paket add Flowsy.Db.Conventions --version 1.1.0
#r "nuget: Flowsy.Db.Conventions, 1.1.0"
// Install Flowsy.Db.Conventions as a Cake Addin #addin nuget:?package=Flowsy.Db.Conventions&version=1.1.0 // Install Flowsy.Db.Conventions as a Cake Tool #tool nuget:?package=Flowsy.Db.Conventions&version=1.1.0
Flowsy Db Conventions
This package is a wrapper for Dapper's extension methods on the IDbConnection
interface,
but with a focus on naming and formatting conventions for database objects like tables, columns, routines and parameters.
Behind the concepts and data structures of this package is the philosophy of team collaboration under a series of conventions that allow everybody to play by the same rules when it comes to naming database objects.
By always following the same conventions, you can make your code more readable and easy to maintain.
For instance, you and your teammates could define the following conventions for database object names:
Object Type | Style | Prefix | Suffix | Example |
---|---|---|---|---|
Table | lower_snake_case | None | None | tbl_user |
Column | lower_snake_case | None | None | user_id |
Routine | lower_snake_case | fn_ | None | fn_users_by_city |
Parameter | lower_snake_case | p_ | None | p_city_name |
Once you all agree on the conventions, you can configure them in a single place and use the extension methods provided by this package to interact with the database.
Defining Conventions
Given the following class and enum type:
namespace MyApp.Domain;
// Assuming you have a table named "user"
// with columns "user_id", "forename", "surname", "email_address", "city_name" and "status"
public class User
{
public Guid UserId { get; set; }
public string Forename { get; set; }
public string Surname { get; set; }
public string EmailAddress { get; set; }
public string CityName { get; set; }
public UserStatus Status { get; set; }
// other properties...
}
public enum UserStatus
{
Active,
Inactive
}
You could define the conventions for the database objects like this:
using System.Text.Json;
using Flowsy.Core;
using Flowsy.Db.Conventions;
var entityTypes = System.Reflection.Assembly.GetExecutingAssembly().GetTypes()
.Where(t => t.Namespace == "MyApp.Domain")
.ToArray()
// Note: This example assumes the underlying database supports sotred functions
DbConventionSet.Default
.ForProvider(DbProvider.PostgreSql)
.ForConnections("MyConnection")
.ForSchemas("public")
.ForTables(CaseStyle.LowerSnakeCase) // lower_snake_case with no prefix or suffix
.ForColumns(CaseStyle.LowerSnakeCase, entityTypes) // lower_snake_case with no prefix or suffix
.ForRoutines(
DbRoutineType.StoredFunction, // use stored functions (you can also use DbRoutineType.StoredProcedure)
CaseStyle.LowerSnakeCase, // use lower_snake_case for routine names
"fn_" // use a "fn_" prefix for routine names
)
.ForParameters(
CaseStyle.LowerSnakeCase, // use lower_snake_case for parameter names
"p_", // use a "p_" prefix for parameter names
useNamedParameters: true // use named parameters in queries (
)
.ForEnums(DbEnumFormat.Name, CaseStyle.PascalCase, mapping: new Dictionary<string, string>
{
[typeof(UserStatus)] = "security.user_status" // map C# enum types to database types
})
.ForDateTimeOffsets(DbDateTimeOffsetFormat.Utc)
.ForPagination(500)
.ForJson(new JsonSerializerOptions());
// You can also clone the default conventions and override the rules you want to change
var customConventions = DbConventionSet.Default.Clone()
.ForTables(CaseStyle.UpperSnakeCase) // use UPPER_SNAKE_CASE for table names with no prefix or suffix
.ForColumns(CaseStyle.UpperSnakeCase) // use UPPER_SNAKE_CASE for table names with no prefix or suffix
.ForRoutines(DbRoutineType.StoredProcedure, CaseStyle.UpperSnakeCase, "SP_") // use stored procedures, UPPER_SNAKE_CASE and a "SP_" prefix for routines
;
Executing Queries
Given the previous configuration, you could execute a query like this:
using Flowsy.Db.Conventions.Extensions;
using MyApp.Domain.User;
// GetConnection is a fictitious method to get an instance of IDbConnection
using var connection = GetConnection();
connection.Open();
var users = await connection.QueryAsync<User>(
"UsersByCity", // simple function name translated to: select * from fn_users_by_city(p_city_name => @p_city_name, p_status => @p_status)
new
{
CityName = "New York", // translated to "@p_city_name"
Status = UserStatus.Active // parameter name translated to "@p_status" and value to "Active" (the enum value name)
},
DbConventionSet.Default, // use the default conventions or pass another DbConventionSet instance with custom conventions
CancellationToken.None
);
// users will be a collection of User objects holding the results of the query
Which would result in the invokation of the following function in the database:
create or replace function public.fn_users_by_city(
p_city_name varchar,
p_status varchar
) returns table (
user_id uuid,
forename varchar,
surname varchar,
email_address varchar,
city_name varchar,
status public.user_status
) as
$$
begin
return query
select
user_id, -- will be mapped to a property named UserId
forename, -- will be mapped to a property named Forename
surname, -- will be mapped to a property named Surname
email_address, -- will be mapped to a property named EmailAddress
city_name, -- will be mapped to a property named CityName
status -- will be mapped to a property named Status
from public.user
where
city_name = p_city_name and
status = p_status::public.user_status -- assuming user_status is an enum type
;
end;
$$ language plpgsql;
The QueryAsync method will automatically map the name of the function and parameters according to the conventions defined in the DbConventionSet
instance.
Take a look at the different extension methods provided by this package on the IDbConnection
interface to see how you can interact with the database using the conventions you defined.
Besides, you can use a DbConventionSet
instance to apply your conventions to specific names to build queries manually:
var conventions = DbConventionSet.Default.Clone();
// Customize the conventions for a specific scenario
conventions
.ForTables(CaseStyle.UpperSnakeCase) // use UPPER_SNAKE_CASE for table names with no prefix or suffix
.ForColumns(CaseStyle.UpperSnakeCase) // use UPPER_SNAKE_CASE for table names with no prefix or suffix
.ForRoutines(DbRoutineType.StoredProcedure, CaseStyle.UpperSnakeCase, "SP_") // use stored procedures, UPPER_SNAKE_CASE and a "SP_" prefix for routines
;
// You can pass a single string or an array of strings to the Apply method
var tableNames = conventions.Tables.Apply("Customer", "PurchaseOrder")
// tableNames will be a IEnumerable<string> with the values: "CUSTOMER", "PURCHASE_ORDER"
var columnNames = conventions.Columns.Apply("PurchaseOrderId", "CustomerId", "CreationUserEmail");
// columnNames will be a IEnumerable<string> with the values: "PURCHASE_ORDER_ID", "CUSTOMER_ID", "CREATION_USER_EMAIL"
var routineName = conventions.Routines.Apply("GetPurchaseOrdersByCustomer");
// routineName will be SP_GET_PURCHASE_ORDERS_BY_CUSTOMER
Extension Methods
Besides the QueryAsync method mentioned above, this package provides the following extension methods on the IDbConnection
interface,
all of them with overloads that allow you to pass a DbConventionSet
instance to override the default conventions for a single query execution:
- Execute: Executes a query and returns the number of affected rows.
- ExecuteAsync: Asynchronously executes a query and returns the number of affected rows.
- Query: Executes a query and returns a collection of objects of the specified type.
- QueryAsync: Asynchronously executes a query and returns a collection of objects of the specified type.
- QueryFirst: Executes a query and returns the first result or throws an exception if none is found. The result is mapped to the specified type.
- QueryFirstAsync: Asynchronously executes a query and returns the first result or throws an exception if none is found. The result is mapped to the specified type.
- QueryFirstOrDefault: Executes a query and returns the first result or the default value if none is found. The result is mapped to the specified type.
- QueryFirstOrDefaultAsync: Asynchronously executes a query and returns the first result or the default value if none is found. The result is mapped to the specified type.
- QueryMultiple: Executes a query and returns multiple result sets.
- QueryMultipleAsync: Asynchronously executes a query and returns multiple result sets.
- QuerySingle: Executes a query and returns a single result or throws an exception if none or more than one is found. The result is mapped to the specified type.
- QuerySingleAsync: Asynchronously executes a query and returns a single result or throws an exception if none or more than one is found. The result is mapped to the specified type.
- QuerySingleOrDefault: Executes a query and returns a single result or the default value if none or more than one is found. The result is mapped to the specified type.
- QuerySingleOrDefaultAsync: Asynchronously executes a query and returns a single result or the default value if none or more than one is found. The result is mapped to the specified type.
Connection Factory
The DbConnectionFactory
class implements the IDbConnectionFactory
interface and provides a way to create instances of IDbConnection
and manage their lifecycle.
The following code snippets show how to use the DbConnectionFactory
class in your applications.
Defining Connection Options
In this example, we define the connection options for two databases in the appsettings.json file.
Each database is identified by a key (MyDatabase1 and MyDatabase2), and the connection options include the provider invariant name and the connection string.
The key will be used to identify the connection options when asking IDbConnectionFactory
for a connection.
appsettings.json
{
"Databases": {
"MyDatabase1": {
"ProviderInvariantName": "Npgsql",
"ConnectionString": "Server=pg.example.com;Database=my_database1;User Id=myuser;Password=mypassword;"
},
"MyDatabase2": {
"ProviderInvariantName": "Microsoft.Data.SqlClient",
"ConnectionString": "Server=mssql.example.com;Database=my_database2;User Id=myuser;Password=mypassword;"
}
}
}
Manually Creating Connection Factories
When you manually create instances of the DbConnectionFactory
class, you must make sure to dispose of them when they are no longer needed.
Load Settings
using Flowsy.Db.Conventions;
using Microsoft.Extensions.Configuration;
public static class DatabaseSettings
{
public static IDictionary<string, DbConnectionOptions> LoadConnectionOptions()
{
IConfiguration configuration = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();
// The GetConnectionOptions method is an extension method provided by the Flowsy.Db.Conventions package.
// It allows you to load connection options from the configuration file given they are stored in the previously shown format.
// The argument passed to the GetConnectionOptions method is the key of the configuration section that holds the connection options.
return configuration.GetConnectionOptions("Databases");
}
}
Use the Settings to Create a Connection Factory
using Flowsy.Db.Conventions;
using Microsoft.Data.SqlClient;
using Npgsql;
public class SomeDataService : IDisposable
{
private static bool providersRegistered;
private readonly DbConnectionFactory _connectionFactory;
public SomeDataService()
{
if (!providersRegistered)
{
// Register database provider factories required by the application
DbProviderFactories.RegisterFactory("Npgsql", NpgsqlFactory.Instance);
DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance);
providersRegistered = true;
}
// Load connection options from appsettings.json and create an instance of DbConnectionFactory
var connectionOptions = DatabaseSettings.LoadConnectionOptions();
var connectionFactoryOptions = new DbConnectionFactoryOptions(connectionOptions);
_connectionFactory = new DbConnectionFactory(connectionFactoryOptions);
}
public void Dispose()
{
// Dispose of the connection factory when it is no longer needed, so it can release the created connections.
_connectionFactory.Dispose();
}
public async Task<IEnumerable<SomeEntity>> GetSomeEntitiesAsync(CancellationToken cancellationToken)
{
// Get an instance of IDbConnection
// Do not dispose the connection, the connection factory will handle disposal of connections obtained by calling the GetConnection method.
var connection = connectionFactory.GetConnection("MyDatabase1");
// Execute a query
return await connection.QueryAsync<SomeData>(
"some_stored_routine",
new
{
SomeParameter = "some_value"
},
DbConventionSet.Default,
cancellationToken
);
// The connectionFactory instance will be disposed when the the SomeDataService instance is disposed and will release all the connections created by it.
}
}
Using Dependency Injection
To facilitate the management of database connections, you can register the DbConnectionFactory
class as a scoped service in the dependency injection container of your application.
For instance, in an ASP.NET application, you could register the DbConnectionFactory
like this:
Program.cs
using Flowsy.Db.Conventions;
using Microsoft.Data.SqlClient;
using Npgsql;
using System.Data.Common;
// More namespaces...
var builder = WebApplication.CreateBuilder(args);
// Register services
// ...
// Register database provider factories required by the application
DbProviderFactories.RegisterFactory("Npgsql", NpgsqlFactory.Instance);
DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance);
builder.Services.AddConnectionFactory((factoryOptions, serviceProvider) =>
{
// Obtain the configuration service
var configuration = serviceProvider.GetRequiredService<IConfiguration>();
// Get connection options from configuration
var connectionOptions = configuration.GetConnectionOptions("Databases");
// Set the connection options in the factory options
// Each key of the connectionOptions dictionary will be used to identify
// the connection options when asking the IDbConnectionFactory for a connection.
factoryOptions.ConnectionOptions = connectionOptions;
});
var app = builder.Build();
// Activate services
app.Run();
Data Access Layer
// This class is a service that depends on the IDbConnectionFactory registered in the dependency injection container.
// The IDbConnectionFactory instance will exist in the context of a given user request, so it will be available to other scoped and transient services.
public class SomeDataService
{
private readonly IDbConnectionFactory _connectionFactory;
public SomeDataService(IDbConnectionFactory connectionFactory)
{
_connectionFactory = connectionFactory;
}
public async Task<IEnumerable<SomeEntity>> GetSomeEntitiesAsync(CancellationToken cancellationToken)
{
// Do not dispose the connection, the IDbConnectionFactory service will handle disposal of connections obtained by calling the GetConnection method.
var connection = _connectionFactory.GetConnection("MyDatabase1");
return await connection.QueryAsync<SomeData>(
"some_stored_routine",
new
{
SomeParameter = "some_value"
},
DbConventionSet.Default,
cancellationToken
);
}
}
As you can see, by using dependency injection you can avoid the need to manually create and dispose of instances of the DbConnectionFactory
class,
you just need to inject the IDbConnectionFactory
instance into the services that need to interact with the database.
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.1 is compatible. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.1
- Dapper (>= 2.1.35)
- Evolve (>= 3.2.0)
- Flowsy.Core (>= 5.0.0)
- Flowsy.Db.Abstractions (>= 3.1.1)
- Microsoft.Extensions.Configuration.Abstractions (>= 8.0.0)
- Microsoft.Extensions.DependencyInjection.Abstractions (>= 8.0.2)
- Microsoft.Extensions.Logging.Abstractions (>= 8.0.1)
- Microsoft.Extensions.Options (>= 8.0.2)
- System.Text.Json (>= 8.0.5)
NuGet packages (1)
Showing the top 1 NuGet packages that depend on Flowsy.Db.Conventions:
Package | Downloads |
---|---|
Flowsy.Db.Repository.Sql
Implementations of data repositories and related operations in the context of a unit of work using SQL databases. |
GitHub repositories
This package is not used by any popular GitHub repositories.