Skip to content

Nano.Data.SqlServer

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

Nano.Data.SqlServer

README.md

Nano.Data.SqlServer

Build and Deploy NuGet NuGet

Sql Server data provider for Nano applications._

Table of Contents

Summary

Data Provider implementation for Sql Server data access.

📖 Learn more about Nano Data. 📖 Learn more about Nano Azure Sql Server.

Try it out yourself using the Api.Data.SqlServer, or Console.Data.SqlServer example.

Registration

Install the Nano.Data.SqlServer NuGet package.

dotnet add package Nano.Data.SqlServer;

Register the SqlServerProvider provider during application startup in the ConfigureServices(...) method.

    .ConfigureServices(services =>
    {
        services
            .AddNanoData<SqlServerProvider, TContext>();
    })

A BaseDbContext and BaseDbContextFactory must also be implemented and used as TContext, and also an initial migration added.

dotnet ef migrations add Initial --project {project-name}

Configuration

Add the data configuration to appsettings.json.

"Data": {
  "BatchSize": 25,
  "BulkBatchSize": 500,
  "BulkBatchDelay": 1000,
  "QueryRetryCount": 0,
  "UseLazyLoading": false,
  "StartupAction": "None",
  "UseSensitiveDataLogging": false,
  "QuerySplittingBehavior": "SingleQuery",
  "DefaultCollation": null,
  "ConnectionString": null,
  "Repository": {
    "UseAutoSave": false,
    "QueryIncludeDepth": 4
  },
  "Identity": null,
  "ConnectionPool": null,
  "HealthCheck": {
    "UnhealthyStatus": "Unhealthy"
  }
}

...and appsettings.Development.json

"Data": {
  "UseMigrateDatabase": true,
  "ConnectionString": "Server=host.docker.internal,1433;Database=nanoDb;User Id=sa;Password=myPassword_123;Encrypt=False;"
}

Docker Compose

Add Sql Server as a service dependency in docker-compose.yml.

services:
  {service-name}:
    depends_on:
      - database

  database:
    image: mcr.microsoft.com/mssql/server:2022-latest
    ports:
      - 1433:1433
    networks:
      - network
    environment:
      SA_PASSWORD: myPassword_123
      ACCEPT_EULA: Y
      MSSQL_PID: Developer

Kubernetes

Add the %SERVICE_NAME%-secret for the connectionstring to the deployment.yaml.

spec:
  template:
    spec:
      containers:
        env:
        - name: Data__ConnectionString
          valueFrom:
            secretKeyRef:
              name: %SERVICE_NAME%-data-secret
              key: data-connectionstring

GitHub Actions

Add the following environment variables to the buid-and-deply.yml.

env:
  DATA_HOST: ${{ github.ref == 'refs/heads/master' && secrets.PRODUCTION_SQLSERVER_HOST || secrets.STAGING_SQLSERVER_HOST }}
  DATA_NAME: nanoDb
  DATA_USER: api-data-sqlserver-user
  DATA_PASSWORD: ${{ github.ref == 'refs/heads/master' && secrets.PRODUCTION_SQLSERVER_NANO_DB_PASSWORD || secrets.STAGING_SQLSERVER_NANO_DB_PASSWORD }}
  DATA_ADMIN_USER: ${{ github.ref == 'refs/heads/master' && secrets.PRODUCTION_SQLSERVER_ADMIN_USER || secrets.STAGING_SQLSERVER_ADMIN_USER }}
  DATA_ADMIN_PASSWORD: ${{ github.ref == 'refs/heads/master' && secrets.PRODUCTION_SQLSERVER_ADMIN_PASSWORD || secrets.STAGING_SQLSERVER_ADMIN_PASSWORD }}
  DATA_CONNECTIONSTRING: Server=${{ env.DATA_HOST }},${{ vars.DATA_SQLSERVER_PORT }};Database=${{ env.DATA_NAME }};User Id=${{ env.DATA_USER }};Password=${{ env.DATA_PASSWORD }};
  DATA_MIGRATION_CONNECTIONSTRING: Server=${{ env.DATA_HOST }},${{ vars.DATA_SQLSERVER_PORT }};Database=${{ env.DATA_NAME }};User Id=${{ env.DATA_ADMIN_USER }};Password=${{ env.DATA_ADMIN_PASSWORD }};

Additionally, this step has been added to ensure database migrations are applied, and the application database user has been created before the application is deployed.

- name: Database Migration
  shell: pwsh
  run: |
    dotnet ef database update `
      --no-build `
      --startup-project $env:APP_NAME `
      --connection "$env:DATA_MIGRATION_CONNECTIONSTRING" `;

    if ($LastExitCode -ne 0)
    { 
        throw "error";
    };
         
    sudo apt-get update
    sudo apt-get install -y mssql-tools unixodbc-dev

    $loginExists = sqlcmd `
        -S "$env:DATA_HOST,$env:DATA_SQLSERVER_PORT" `
        -U $env:DATA_ADMIN_USER `
        -P $env:DATA_ADMIN_PASSWORD `
        -d master `
        -h -1 `
        -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM sys.server_principals WHERE name = '$env:DATA_USER';"

    if ($loginExists -eq 0)
    {
        sqlcmd `
            -S "$env:DATA_HOST,$env:DATA_SQLSERVER_PORT" `
            -U $env:DATA_ADMIN_USER `
            -P $env:DATA_ADMIN_PASSWORD `
            -d master `
            -Q "CREATE LOGIN [$env:DATA_USER] WITH PASSWORD = '$env:DATA_PASSWORD';"
    }

    $userExists = sqlcmd `
        -S "$env:DATA_HOST,$env:DATA_SQLSERVER_PORT" `
        -U $env:DATA_ADMIN_USER `
        -P $env:DATA_ADMIN_PASSWORD `
        -d $env:DATA_NAME `
        -h -1 `
        -Q "SET NOCOUNT ON; SELECT COUNT(*) FROM sys.database_principals WHERE name = '$env:DATA_USER';"

   if ($userExists -eq 0)
   {
       sqlcmd `
           -S "$env:DATA_HOST,$env:DATA_SQLSERVER_PORT" `
           -U $env:DATA_ADMIN_USER `
           -P $env:DATA_ADMIN_PASSWORD `
           -d $env:DATA_NAME `
           -Q "CREATE USER [$env:DATA_USER] FOR LOGIN [$env:DATA_USER];
               ALTER ROLE db_datareader ADD MEMBER [$env:DATA_USER];
               ALTER ROLE db_datawriter ADD MEMBER [$env:DATA_USER];"
   }

Last, the application connectionstring must be added in a secret in Kubernetes in the Kubernetes Deploy step.

sudo kubectl create secret generic $env:SERVICE_NAME-data-secret ` --from-literal=data-connectionstring=$env:DATA_CONNECTIONSTRING --save-config --dry-run=client -o yaml | sudo kubectl apply -f -;
if ($LastExitCode -ne 0)
{ 
    throw "error";
};