Monthly Archives: März 2024

SQL Server Datenbank Design -> Kroki ER Diagramm

https://kroki.io/examples.html#erd

DECLARE @FlatTable TABLE
(
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128),
    DataType NVARCHAR(128),
    [MaxLength] INT,
    [Precision] INT,
    Scale INT,
    Nullable BIT,
    [Identity] BIT,
    PrimaryKey BIT,
    ForeignKeyName NVARCHAR(128),
    ParentTable NVARCHAR(128),
    ParentColumn NVARCHAR(128),
    ReferencedTable NVARCHAR(128),
    ReferencedColumn NVARCHAR(128)
);


INSERT INTO @FlatTable
SELECT tbl.[name] AS [TableName],
       col.[name] AS [ColumnName],
       ty.[name] AS [DataType],
       col.[max_length] AS [MaxLength],
       col.[precision] AS [Precision],
       col.[scale] AS [Scale],
       col.[is_nullable] AS [Nullable],
       col.[is_identity] AS [Identity],
       CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],
       fk.name AS ForeignKeyName,
       OBJECT_NAME(fk.parent_object_id) AS ParentTable,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
       OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
       COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn
FROM       sys.tables tbl
INNER JOIN sys.columns col
        ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
        ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
           SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
           FROM   sys.index_columns ind
           INNER JOIN sys.key_constraints pks
                   ON pks.[parent_object_id] = ind.[object_id]
                  AND pks.[unique_index_id] = ind.[index_id]
           WHERE  pks.[type] = 'PK'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
LEFT JOIN 
    sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id
LEFT JOIN 
    sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id 


WHERE 1=1
--  tbl.name does not start with __
AND tbl.name != '__RefactorLog'


ORDER BY tbl.[name], CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) DESC, fk.name DESC, col.[name];


DECLARE @StuffedTable TABLE (TableName NVARCHAR(128), Columns NVARCHAR(MAX))
INSERT INTO @StuffedTable


SELECT 
    TableName,
STUFF
(
    (
        SELECT ',' + 
        CASE 
            WHEN PrimaryKey = 1 THEN '*' 
            WHEN ForeignKeyName IS NOT NULL THEN '+'            
            ELSE '' END +
        
        ColumnName + ' ' + [DataType] +
CASE 
WHEN [DataType] IN ('varchar','nvarchar') THEN ' (' + REPLACE(CONVERT(VARCHAR, [MaxLength]), '-1', 'max') + ')'
WHEN [DataType] = 'decimal' THEN ' (' + CONVERT(VARCHAR, [Precision]) + ',' + CONVERT(VARCHAR, [Scale]) + ')'
ELSE '' END


        FROM @FlatTable As T2
        WHERE T2.TableName = T1.TableName
        
        FOR XML PATH (''), TYPE
    ).value('.', 'nvarchar(max)')
, 1, 1, '') + ','


AS [Columns]
FROM @FlatTable As T1
GROUP BY TableName




DECLARE @ResultString NVARCHAR(MAX) = 
(SELECT 
STUFF
(
    (
        SELECT ',' + 
        '[' + TableName + ']' + ',' +
        Columns
        FROM @StuffedTable As T2        
        
        FOR XML PATH (''), TYPE
    ).value('.', 'nvarchar(max)')
, 1, 1, ''))


PRINT  
'```kroki' + CHAR(13) + CHAR(10) + 
'erd' + CHAR(13) + CHAR(10) + 
REPLACE(@ResultString, ',', CHAR(13) + CHAR(10))
+ '```' + CHAR(13) + CHAR(10)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

DECLARE @FlatTable TABLE

(

TableName NVARCHAR(128),

ColumnName NVARCHAR(128),

DataType NVARCHAR(128),

[MaxLength] INT,

[Precision] INT,

Scale INT,

Nullable BIT,

[Identity] BIT,

PrimaryKey BIT,

ForeignKeyName NVARCHAR(128),

ParentTable NVARCHAR(128),

ParentColumn NVARCHAR(128),

ReferencedTable NVARCHAR(128),

ReferencedColumn NVARCHAR(128)

);

INSERT INTO @FlatTable

SELECT tbl.[name] AS [TableName],

col.[name] AS [ColumnName],

ty.[name] AS [DataType],

col.[max_length] AS [MaxLength],

col.[precision] AS [Precision],

col.[scale] AS [Scale],

col.[is_nullable] AS [Nullable],

col.[is_identity] AS [Identity],

CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],

fk.name AS ForeignKeyName,

OBJECT_NAME(fk.parent_object_id) AS ParentTable,

COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,

OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,

COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn

FROM sys.tables tbl

INNER JOIN sys.columns col

ON col.[object_id] = tbl.[object_id]

INNER JOIN sys.types ty

ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id

LEFT JOIN (

SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]

FROM sys.index_columns ind

INNER JOIN sys.key_constraints pks

ON pks.[parent_object_id] = ind.[object_id]

AND pks.[unique_index_id] = ind.[index_id]

WHERE pks.[type] = 'PK'

) pkcol

ON pkcol.[table_object_id] = tbl.[object_id]

AND pkcol.[column_id] = col.[column_id]

LEFT JOIN

sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id

LEFT JOIN

sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id

WHERE 1=1

-- tbl.name does not start with __

AND tbl.name != '__RefactorLog'

ORDER BY tbl.[name], CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) DESC, fk.name DESC, col.[name];

DECLARE @StuffedTable TABLE (TableName NVARCHAR(128), Columns NVARCHAR(MAX))

INSERT INTO @StuffedTable

SELECT

TableName,

STUFF

(

SELECT ',' +

CASE

WHEN PrimaryKey = 1 THEN '*'

WHEN ForeignKeyName IS NOT NULL THEN '+'

ELSE '' END +

ColumnName + ' ' + [DataType] +

CASE

WHEN [DataType] IN ('varchar','nvarchar') THEN ' (' + REPLACE(CONVERT(VARCHAR, [MaxLength]), '-1', 'max') + ')'

WHEN [DataType] = 'decimal' THEN ' (' + CONVERT(VARCHAR, [Precision]) + ',' + CONVERT(VARCHAR, [Scale]) + ')'

ELSE '' END

FROM @FlatTable As T2

WHERE T2.TableName = T1.TableName

FOR XML PATH (''), TYPE

).value('.', 'nvarchar(max)')

, 1, 1, '') + ','

AS [Columns]

FROM @FlatTable As T1

GROUP BY TableName

DECLARE @ResultString NVARCHAR(MAX) =

(SELECT

STUFF

(

SELECT ',' +

'[' + TableName + ']' + ',' +

Columns

FROM @StuffedTable As T2

FOR XML PATH (''), TYPE

).value('.', 'nvarchar(max)')

, 1, 1, ''))

'```kroki' + CHAR(13) + CHAR(10) +

'erd' + CHAR(13) + CHAR(10) +

REPLACE(@ResultString, ',', CHAR(13) + CHAR(10))

+ '```' + CHAR(13) + CHAR(10)

Posted on 18. März 2024 by Andreas in SQL

SQL Query um Tabellen und Spalten auszulesen inkl. PK und FK

SELECT tbl.[name] AS [TableName],
       col.[name] AS [ColumnName],
       ty.[name] AS [DataType],
       col.[max_length] AS [MaxLength],
       col.[precision] AS [Precision],
       col.[scale] AS [Scale],
       col.[is_nullable] AS [Nullable],
       col.[is_identity] AS [Identity],
       CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],
       fk.name AS ForeignKeyName,
       OBJECT_NAME(fk.parent_object_id) AS ParentTable,
       COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,
       OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,
       COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn
FROM       sys.tables tbl
INNER JOIN sys.columns col
        ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
        ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
           SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
           FROM   sys.index_columns ind
           INNER JOIN sys.key_constraints pks
                   ON pks.[parent_object_id] = ind.[object_id]
                  AND pks.[unique_index_id] = ind.[index_id]
           WHERE  pks.[type] = 'PK'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
LEFT JOIN
    sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id
LEFT JOIN
    sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id      
ORDER BY tbl.[name], col.[name];

SELECT tbl.[name] AS [TableName],

col.[name] AS [ColumnName],

ty.[name] AS [DataType],

col.[max_length] AS [MaxLength],

col.[precision] AS [Precision],

col.[scale] AS [Scale],

col.[is_nullable] AS [Nullable],

col.[is_identity] AS [Identity],

CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey],

fk.name AS ForeignKeyName,

OBJECT_NAME(fk.parent_object_id) AS ParentTable,

COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ParentColumn,

OBJECT_NAME(fk.referenced_object_id) AS ReferencedTable,

COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumn

FROM sys.tables tbl

INNER JOIN sys.columns col

ON col.[object_id] = tbl.[object_id]

INNER JOIN sys.types ty

ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id

LEFT JOIN (

SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]

FROM sys.index_columns ind

INNER JOIN sys.key_constraints pks

ON pks.[parent_object_id] = ind.[object_id]

AND pks.[unique_index_id] = ind.[index_id]

WHERE pks.[type] = 'PK'

) pkcol

ON pkcol.[table_object_id] = tbl.[object_id]

AND pkcol.[column_id] = col.[column_id]

LEFT JOIN

sys.foreign_key_columns fkc ON tbl.object_id = fkc.parent_object_id AND col.column_id = fkc.parent_column_id

LEFT JOIN

sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id

ORDER BY tbl.[name], col.[name];

Posted on 18. März 2024 by Andreas in SQL

Host oder Webhost mit Autofac

            /*
             Web:

            var builder = WebApplication.CreateBuilder(args);

            builder.Host.UseServiceProviderFactory(new AutofacServiceProviderFactory());
             */

            var builder = Host.CreateDefaultBuilder()
                              .UseServiceProviderFactory(new AutofacServiceProviderFactory())
                              .ConfigureAppConfiguration((hostContext, config) =>
                              {
                                  config
                                    .SetBasePath(System.IO.Directory.GetCurrentDirectory())
                                    .AddJsonFile($"appsettings.json", optional: true, reloadOnChange: true)
                                    .AddJsonFile($"appsettings.{hostContext.HostingEnvironment.EnvironmentName}.json", optional: true)
                                    .AddJsonFile($"appsettings.{Environment.MachineName}.json", optional: true)
                                    .AddEnvironmentVariables();

                              })
                              .ConfigureServices((hostContext, services) =>
                              {
                                  IConfiguration config = hostContext.Configuration;
                                  Console.WriteLine(config.GetDebugView());

                                  services.AddLogging(b =>
                                  {
                                      b.AddDebug();
                                      b.AddConsole();
                                  });

                                  // Register over IServiceCollection
                              })
                            .ConfigureContainer<ContainerBuilder>(builder =>
                            {
                                builder.RegisterInstance(new LoggerFactory())
                                                        .As<ILoggerFactory>();
                                builder.RegisterGeneric(typeof(Logger<>))
                                                        .As(typeof(ILogger<>))
                                                        .SingleInstance();

                                // Register over Autofac ContainerBuilder
                            });

            App = builder.Build();
            var service = (AutofacServiceProvider)App.Services;
            this.Container = service.LifetimeScope;

            var myService = this.Container.Resolve<MyService>();

Web:

var builder = WebApplication.CreateBuilder(args);

builder.Host.UseServiceProviderFactory(new AutofacServiceProviderFactory());

var builder = Host.CreateDefaultBuilder()

.UseServiceProviderFactory(new AutofacServiceProviderFactory())

.ConfigureAppConfiguration((hostContext, config) =>

{

config

.SetBasePath(System.IO.Directory.GetCurrentDirectory())

.AddJsonFile($"appsettings.json", optional: true, reloadOnChange: true)

.AddJsonFile($"appsettings.{hostContext.HostingEnvironment.EnvironmentName}.json", optional: true)

.AddJsonFile($"appsettings.{Environment.MachineName}.json", optional: true)

.AddEnvironmentVariables();

})

.ConfigureServices((hostContext, services) =>

{

IConfiguration config = hostContext.Configuration;

Console.WriteLine(config.GetDebugView());

services.AddLogging(b =>

{

b.AddDebug();

b.AddConsole();

});

// Register over IServiceCollection

})

.ConfigureContainer<ContainerBuilder>(builder =>

{

builder.RegisterInstance(new LoggerFactory())

.As<ILoggerFactory>();

builder.RegisterGeneric(typeof(Logger<>))

.As(typeof(ILogger<>))

.SingleInstance();

// Register over Autofac ContainerBuilder

});

App = builder.Build();

var service = (AutofacServiceProvider)App.Services;

this.Container = service.LifetimeScope;

var myService = this.Container.Resolve<MyService>();

Posted on 11. März 2024 by Andreas in C#

Entity Framework EF – Tabellen Models generieren lassen

Alt + T -> „NU“ eingeben

scaffold-DbContext "Server=XXX; Database=XXX; user id=sa; password = XXX;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir "Models\XXX" -Context XXXContext -Force -UseDatabaseNames -Tables XXX

1	scaffold-DbContext "Server=XXX; Database=XXX; user id=sa; password = XXX;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir "Models\XXX" -Context XXXContext -Force -UseDatabaseNames -Tables XXX

Posted on 1. März 2024 by Andreas in Allgemein, C#

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Monthly Archives: März 2024

SQL Server Datenbank Design -> Kroki ER Diagramm

SQL Query um Tabellen und Spalten auszulesen inkl. PK und FK

Host oder Webhost mit Autofac

Entity Framework EF – Tabellen Models generieren lassen

Seiten

Neueste Beiträge

Neueste Kommentare

Archive

Denn ich habe kein Gefallen am Tod dessen, der sterben muss, spricht Gott, der Herr. So kehrt denn um, und ihr sollt leben!

Monthly Archives: März 2024

SQL Server Datenbank Design -> Kroki ER Diagramm

SQL Query um Tabellen und Spalten auszulesen inkl. PK und FK

Host oder Webhost mit Autofac

Entity Framework EF – Tabellen Models generieren lassen

Search this site

Seiten

Neueste Beiträge

Neueste Kommentare

Archive