Package Description
$ dotnet add package HLCores.Configurations.DatabaseHLCores 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).
void GenerateScriptSQLFile(GenerateScriptConfig config)
void ExecuteScriptFile(string connectionString, string filePath)
GenerateScriptConfig:
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
Exam:
[HttpPost("ExecuteScriptFile")]
public void ExecuteScriptFile()
{
DatabaseConfig.ExecuteScriptFile(
SettingsConfigUtility.GetCurrentSettings("ConnectionStrings:DefaultConnection"),
Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "SQLScripts", "Demo.sql")
);
}