HLCores.Configurations.Database 2.0.0

HLCores.Configurations.Database

HLCores Configurations Database is config generate and execute script sql file.

I used the use SQL Server SMO (Server Management Object) package to do this, please refer to the documentation at Server Management Objects (SMO).

Functions

void GenerateScriptSQLFile(GenerateScriptConfig config)

void ExecuteScriptFile(string connectionString, string filePath)

Usage

GenerateScriptSQLFile

GenerateScriptConfig:

• ConnectionString: SQL connection string (has required).
• DataBaseName: DataBase name (if null or empty will be take in connection string info). This must exist in the database.
• IsCreateDataBase: Add to file line create database by DataBase name (default true).
• IsGenerateData: Export data lines (default true).
• IsGenerateAlertTable: Export alert table lines (default true).
• IsIncludeIfNotExists: Add to file line not exist (default false).
• IsNoCollation: Ignore collation schema table (default true).
• IsContinueScriptingOnError: Continue scripting on error (default false).
• Folder: Folder save file generate (default "wwwroot").
• SubPath: Sub path of Folder save file generate (default "SQLScripts").
• FileName: File name script extension ".sql" (if null or empty will be take Database name).
• Tables: Indicate specific tables (which must exist in the database).

Exam:

[HttpPost("GenerateScriptSQLFile")]
public void GenerateScriptSQLFile()
{
    DatabaseConfig.GenerateScriptSQLFile(new GenerateScriptConfig()
    {
        ConnectionString = SettingsConfigUtility.GetCurrentSettings("ConnectionStrings:DefaultConnection"),
        DataBaseName = "Demo",
        Tables = new()
        {
            "AccountType",
            "Account"
        }
    });
}

Result:

USE [master]
GO
CREATE DATABASE Demo
GO
USE [Demo]
GO
/****** Object:  Table [dbo].[AccountType]    Script Date: 9/19/2024 8:12:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AccountType](
	[Id] [uniqueidentifier] NOT NULL,
	[Code] [nvarchar](10) NOT NULL,
	[Title] [nvarchar](100) NOT NULL,
	[Status] [bit] NULL,
	[IsDelete] [bit] NULL,
	[CreateBy] [uniqueidentifier] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateBy] [uniqueidentifier] NULL,
	[UpdateTime] [datetime] NULL,
 CONSTRAINT [PK_AccountType] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Account]    Script Date: 9/19/2024 8:12:53 AM ******/
SET ANSI_NULLS ON
GO
CREATE TABLE [dbo].[Account](
	[Id] [uniqueidentifier] NOT NULL,
	[UserName] [nvarchar](1000) NOT NULL,
	[Password] [nvarchar](1000) NOT NULL,
	[Status] [bit] NULL,
	[IsDelete] [bit] NULL,
	[IsLock] [bit] NULL,
	[CreateBy] [uniqueidentifier] NOT NULL,
	[CreateTime] [datetime] NOT NULL,
	[UpdateBy] [uniqueidentifier] NULL,
	[UpdateTime] [datetime] NULL,
	[AccountTypeId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[AccountType] ([Id], [Code], [Title], [Status], [IsDelete], [CreateBy], [CreateTime], [UpdateBy], [UpdateTime]) VALUES (N'bf404ac0-f477-4693-add5-9025ab5f0a28', N'AT0003', N'Member', 1, 0, N'6f8d34c8-f126-4db5-b1bb-9dd50b5804ba', CAST(N'2024-03-30T11:23:15.480' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[AccountType] ([Id], [Code], [Title], [Status], [IsDelete], [CreateBy], [CreateTime], [UpdateBy], [UpdateTime]) VALUES (N'90e2c2ef-c147-454b-a4c9-aa899eef79b1', N'AT0001', N'Administrator', 1, 0, N'6f8d34c8-f126-4db5-b1bb-9dd50b5804ba', CAST(N'2024-03-30T10:12:34.390' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[AccountType] ([Id], [Code], [Title], [Status], [IsDelete], [CreateBy], [CreateTime], [UpdateBy], [UpdateTime]) VALUES (N'0b4e4862-64e0-4d08-ab73-b417facbd8ea', N'AT0005', N'TrainingStudent', 1, 0, N'6f8d34c8-f126-4db5-b1bb-9dd50b5804ba', CAST(N'2024-09-17T08:24:58.000' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[AccountType] ([Id], [Code], [Title], [Status], [IsDelete], [CreateBy], [CreateTime], [UpdateBy], [UpdateTime]) VALUES (N'd6793b78-ad39-4972-99e7-b73ee1997547', N'AT0006', N'TrainingLecturer', 1, 0, N'6f8d34c8-f126-4db5-b1bb-9dd50b5804ba', CAST(N'2024-09-17T08:32:08.000' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[AccountType] ([Id], [Code], [Title], [Status], [IsDelete], [CreateBy], [CreateTime], [UpdateBy], [UpdateTime]) VALUES (N'a8163258-ee4a-4066-814b-e9916ac79952', N'AT0004', N'Customer', 1, 0, N'6f8d34c8-f126-4db5-b1bb-9dd50b5804ba', CAST(N'2024-09-17T08:24:37.000' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[AccountType] ([Id], [Code], [Title], [Status], [IsDelete], [CreateBy], [CreateTime], [UpdateBy], [UpdateTime]) VALUES (N'e4ef3ad9-31f2-49f1-8458-fa79c85d8f86', N'AT0002', N'Staff', 1, 0, N'6f8d34c8-f126-4db5-b1bb-9dd50b5804ba', CAST(N'2024-03-30T11:12:27.097' AS DateTime), NULL, NULL)
GO
INSERT [dbo].[Account] ([Id], [UserName], [Password], [Status], [IsDelete], [IsLock], [CreateBy], [CreateTime], [UpdateBy], [UpdateTime], [AccountTypeId]) VALUES (N'6f8d34c8-f126-4db5-b1bb-9dd50b5804ba', N'Admin', N'9EsCC1kT1F7r6U+jC9g+0A==', 1, 0, NULL, N'6f8d34c8-f126-4db5-b1bb-9dd50b5804ba', CAST(N'2024-03-30T10:12:34.390' AS DateTime), NULL, NULL, N'90e2c2ef-c147-454b-a4c9-aa899eef79b1')
GO
SET ANSI_NULLS ON
GO
ALTER TABLE [dbo].[AccountType] ADD  CONSTRAINT [DF_AccountType_Id]  DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_Id]  DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [FK_Account_AccountType] FOREIGN KEY([AccountTypeId])
REFERENCES [dbo].[AccountType] ([Id])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_Account_AccountType]
GO

ExecuteScriptFile

• connectionString: SQL connection string.
• filePath: Path file script sql.

Exam:

[HttpPost("ExecuteScriptFile")]
public void ExecuteScriptFile()
{
    DatabaseConfig.ExecuteScriptFile(
        SettingsConfigUtility.GetCurrentSettings("ConnectionStrings:DefaultConnection"),
        Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "SQLScripts", "Demo.sql")
    );
}