.NET SQL Server Codespace Configuration

readme.md

.NET SQL Server Codespace Configuration

File Hierarchy:

• .devcontainer
  • mssql
    • installSQLtools.sh
    • postCreateCommand.sh
    • setup.sql
  • devcontainer.json
  • docker-compose.yml
  • Dockerfile

devcontainer.json

// For format details, see https://aka.ms/devcontainer.json. For config options, see the
// README at: https://github.com/devcontainers/templates/tree/main/src/dotnet-mssql
{
	"name": "C# (.NET) and MS SQL",
	"dockerComposeFile": "docker-compose.yml",
	"service": "app",
	"workspaceFolder": "/workspaces/${localWorkspaceFolderBasename}",

	// Features to add to the dev container. More info: https://containers.dev/features.
	// "features": {},

	// Configure tool-specific properties.
	"customizations": {
		// Configure properties specific to VS Code.
		"vscode": {
			// Set *default* container specific settings.json values on container create.
			"settings": { 
				"mssql.connections": [
					{
						"server": "localhost,1433",
						"database": "",
						"authenticationType": "SqlLogin",
						"user": "sa",
						"password": "P@ssw0rd",
						"emptyPasswordInput": false,
						"savePassword": false,
						"profileName": "mssql-container"
					}
				]
			},
			
			// Add the IDs of extensions you want installed when the container is created.
			"extensions": [
				"ms-dotnettools.csdevkit",
				"ms-mssql.mssql",
				"PKief.material-icon-theme"
			]
		}
	},
	"forwardPorts": [1433,5001,5002],
	"portsAttributes": {
		"5001": {
			"protocol": "http"
		},
		"5002": {
			"protocol": "http"
		}
	},
	// postCreateCommand.sh parameters: $1=SA password, $2=dacpac path, $3=sql script(s) path
	"postCreateCommand": "bash .devcontainer/mssql/postCreateCommand.sh 'P@ssw0rd' './bin/Debug/' './.devcontainer/mssql/'",
	"features": {
		"ghcr.io/devcontainers/features/dotnet:1": {
			"installUsingApt": true,
			"version": "latest"
		}
	}

	// Uncomment to connect as root instead. More info: https://aka.ms/dev-containers-non-root.
	// "remoteUser": "root"
}

docker-compose.yml

version: '3'

services:
  app:
    build: 
      context: .
      dockerfile: Dockerfile

    volumes:
      - ../..:/workspaces:cached

    # Overrides default command so things don't shut down after the process ends.
    command: sleep infinity

    # Runs app on the same network as the database container, allows "forwardPorts" in devcontainer.json function.
    network_mode: service:db

    # Uncomment to connect as root instead. More info: https://aka.ms/dev-containers-non-root.
    # user: root

    # Use "forwardPorts" in **devcontainer.json** to forward an app port locally. 
    # (Adding the "ports" property to this file will not forward from a Codespace.)

  db:
    image: mcr.microsoft.com/mssql/server:2019-latest
    restart: unless-stopped
    environment:
      SA_PASSWORD: P@ssw0rd
      ACCEPT_EULA: Y

    # Add "forwardPorts": ["1433"] to **devcontainer.json** to forward MSSQL locally.
    # (Adding the "ports" property to this file will not forward from a Codespace.)

Dockerfile

FROM mcr.microsoft.com/devcontainers/dotnet:0-7.0

# Install SQL Tools: SQLPackage and sqlcmd
COPY mssql/installSQLtools.sh installSQLtools.sh
RUN bash ./installSQLtools.sh \
     && apt-get clean -y && rm -rf /var/lib/apt/lists/* /tmp/library-scripts

# [Optional] Uncomment this section to install additional OS packages.
# RUN apt-get update && export DEBIAN_FRONTEND=noninteractive \
#     && apt-get -y install --no-install-recommends <your-package-list-here>

# [Optional] Uncomment this line to install global node packages.
# RUN su vscode -c "source /usr/local/share/nvm/nvm.sh && npm install -g <your-package-here>" 2>&1

installSQLtools.sh

#!/bin/bash
echo "Installing mssql-tools"
curl -sSL https://packages.microsoft.com/keys/microsoft.asc | (OUT=$(apt-key add - 2>&1) || echo $OUT)
DISTRO=$(lsb_release -is | tr '[:upper:]' '[:lower:]')
CODENAME=$(lsb_release -cs)
echo "deb [arch=amd64] https://packages.microsoft.com/repos/microsoft-${DISTRO}-${CODENAME}-prod ${CODENAME} main" > /etc/apt/sources.list.d/microsoft.list
apt-get update
ACCEPT_EULA=Y apt-get -y install unixodbc-dev msodbcsql17 libunwind8 mssql-tools

echo "Installing sqlpackage"
curl -sSL -o sqlpackage.zip "https://aka.ms/sqlpackage-linux"
mkdir /opt/sqlpackage
unzip sqlpackage.zip -d /opt/sqlpackage 
rm sqlpackage.zip
chmod a+x /opt/sqlpackage/sqlpackage

postCreateCommand.sh

#!/bin/bash
dacpac="false"
sqlfiles="false"
SApassword=$1
dacpath=$2
sqlpath=$3

echo "SELECT * FROM SYS.DATABASES" | dd of=testsqlconnection.sql
for i in {1..60};
do
    /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SApassword -d master -i testsqlconnection.sql > /dev/null
    if [ $? -eq 0 ]
    then
        echo "SQL server ready"
        break
    else
        echo "Not ready yet..."
        sleep 1
    fi
done
rm testsqlconnection.sql

for f in $dacpath/*
do
    if [ $f == $dacpath/*".dacpac" ]
    then
        dacpac="true"
        echo "Found dacpac $f"
    fi
done

for f in $sqlpath/*
do
    if [ $f == $sqlpath/*".sql" ]
    then
        sqlfiles="true"
        echo "Found SQL file $f"
    fi
done

if [ $sqlfiles == "true" ]
then
    for f in $sqlpath/*
    do
        if [ $f == $sqlpath/*".sql" ]
        then
            echo "Executing $f"
            /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SApassword -d master -i $f
        fi
    done
fi

if [ $dacpac == "true" ] 
then
    for f in $dacpath/*
    do
        if [ $f == $dacpath/*".dacpac" ]
        then
            dbname=$(basename $f ".dacpac")
            echo "Deploying dacpac $f"
            /opt/sqlpackage/sqlpackage /Action:Publish /SourceFile:$f /TargetServerName:localhost /TargetDatabaseName:$dbname /TargetUser:sa /TargetPassword:$SApassword
        fi
    done
fi

setup.sql

CREATE DATABASE ApplicationDB;
GO

sql.md

Codespaces SQL Configuration

1. F1 -> Codespaces: Add Dev Container Configuration Files...

2. Create a new configuration...

3. C# and MS SQL

4. Dotnet CLI

devcontainer.json Modifications

{
    "customizations": {
        "vscode": {
            "extensions": [
	        "ms-dotnettools.vscode-dotnet-runtime",
		"Angular.ng-template",
		"ms-dotnettools.csdevkit",
		"ms-mssql.mssql",
		"PKief.material-icon-theme",
		"ms-vscode.powershell",
		"rangav.vscode-thunder-client",
		"spmeesseman.vscode-taskexplorer"
            ],
        }
    },
    "forwardPorts": [1433]
}

Connection Strings

"ConnectionStrings": {
    "App": "Server=localhost,1433;Encrypt=Mandatory;TrustServerCertificate=True;User=sa;Password=P@ssw0rd;Database={db}"
}

Example

"ConnectionStrings": {
    "App": "Server=localhost,1433;Encrypt=Mandatory;TrustServerCertificate=True;User=sa;Password=P@ssw0rd;Database=distributed-proposals"
}