using System.Text;
|
|
namespace HotelPms.Share.Data.Script;
|
|
public class TableCreator : IDisposable
|
{
|
public TableCreator()
|
{
|
|
}
|
|
/// <summary>
|
/// 初期化
|
/// 必ずPKがある
|
/// </summary>
|
/// <param name="name">テーブル名</param>
|
public TableCreator(string name)
|
{
|
Name = name;
|
}
|
|
/// <summary>
|
/// テーブル名
|
/// </summary>
|
public string Name { get; set; } = string.Empty;
|
|
/// <summary>
|
/// テーブルの説明
|
/// </summary>
|
public string Description { get; set; } = string.Empty;
|
|
/// <summary>
|
/// パーティションの構成
|
/// </summary>
|
public string PartitionSchema { get; set; } = string.Empty;
|
|
/// <summary>
|
/// パーティション使用列名
|
/// </summary>
|
public string PartitionKey { get; set; } = string.Empty;
|
|
/// <summary>
|
/// 項目
|
/// </summary>
|
public List<Field> Fields { get; set; } = new List<Field>();
|
|
/// <summary>
|
/// 項目
|
/// </summary>
|
/// <param name="index"></param>
|
/// <returns></returns>
|
public virtual Field this[int index]
|
{
|
get { return Fields[index]; }
|
}
|
|
/// <summary>
|
/// 名称で検索する
|
/// </summary>
|
/// <param name="name"></param>
|
/// <returns></returns>
|
public virtual Field? this[string name]
|
{
|
get { return Fields.FirstOrDefault<Field>(item => item.Name == name); }
|
}
|
|
/// <summary>
|
/// メモリー解放
|
/// </summary>
|
public void Dispose()
|
{
|
Fields.Clear();
|
}
|
|
/// <summary>
|
/// SQL文の作成
|
/// テーブルを削除してから、新規作成する
|
/// </summary>
|
/// <returns></returns>
|
public override string ToString()
|
{
|
StringBuilder desc = new StringBuilder();
|
// テーブルの説明
|
if (Description.Length > 0)
|
{
|
desc.AppendLine($"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{Description}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{Name}';");
|
}
|
|
StringBuilder cols = new StringBuilder();
|
StringBuilder pk = new StringBuilder();
|
foreach(Field item in Fields)
|
{
|
if (item.IsPrimaryKey)
|
{
|
if(pk.Length > 0) { pk.Append(","); }
|
pk.Append($"[{item.Name}] ASC");
|
}
|
|
cols.AppendLine($"[{item.Name}] {item.DataType} {(item.IsIdentity ? "IDENTITY(1,1)" : string.Empty)} {(item.IsPrimaryKey ? "NOT" : string.Empty)} NULL,");
|
|
if (item.Description.Length > 0)
|
{
|
desc.AppendLine($"EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{item.Description}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{Name}', @level2type=N'COLUMN',@level2name=N'{item.Name}';");
|
}
|
}
|
|
//PARTITION構成の場合、最後にON [PARTITION構成]([列名])
|
string partition = string.Empty;
|
if(PartitionSchema.Length > 0) { partition = $"ON [{PartitionSchema}]([{PartitionKey}])"; }
|
;
|
return $@"
|
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{Name}') AND type in (N'U'))
|
DROP TABLE {Name}
|
|
CREATE TABLE [{Name}](
|
{cols.ToString()}
|
CONSTRAINT [PK_{Name}] PRIMARY KEY CLUSTERED
|
(
|
{pk.ToString()}
|
)) {partition}
|
|
{desc.ToString()}
|
";
|
}
|
|
|
}
|
