/// <summary>
/// 判断自增列
/// </summary>
/// <param name="table"></param>
/// <param name="column"></param>
/// <returns></returns>
public static int Getzzpk(string table, string column)
{
if (zzpklist == null || zzpklist.Count() == 0)
{
zzpklist = DbDescriptionHelper.GetDescription();
}
if (!string.IsNullOrWhiteSpace(table))
{
if (!string.IsNullOrWhiteSpace(column))
{
var x = zzpklist.FirstOrDefault(p => p.Name == table);
if (x != null)
{
var y = x.Column;
if (y.IsNotNull())
{
var z = y.FirstOrDefault(p => p.Name == column);
if (z != null)
{
return(z.IsZZPK);
}
}
}
return(0);
}
}
return(0);
}
/// <summary>
/// Create column comment.
/// </summary>
/// <param name="operation"></param>
/// <param name="builder"></param>
private void CreateColumnComment(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
{
//alter table a1log modify column UUID VARCHAR(26) comment '修改后的字段注释';
string tableName = string.Empty;
string columnName = string.Empty;
string columnType = string.Empty;
string description = string.Empty;
if (operation is AlterColumnOperation)
{
var t = (operation as AlterColumnOperation);
tableName = t.Table;
columnName = t.Name;
columnType = GetColumnType(t.Schema, t.Table, t.Name, t.ClrType, t.IsUnicode, t.MaxLength, t.IsFixedLength, t.IsRowVersion, model);
description = DbDescriptionHelper.GetDescription(tableName, columnName);
}
if (operation is AddColumnOperation)
{
var t = (operation as AddColumnOperation);
tableName = t.Table;
columnName = t.Name;
columnType = GetColumnType(t.Schema, t.Table, t.Name, t.ClrType, t.IsUnicode, t.MaxLength, t.IsFixedLength, t.IsRowVersion, model);
description = DbDescriptionHelper.GetDescription(tableName, columnName);
}
if (columnName.IsNullOrWhiteSpace() || tableName.IsNullOrWhiteSpace() || columnType.IsNullOrWhiteSpace())
{
throw new Exception("Create columnt comment error." + columnName + "/" + tableName + "/" + columnType);
}
var sqlHelper = Dependencies.SqlGenerationHelper;
builder
.Append($@"
if exists(
SELECT t.[name] AS 表名,c.[name] AS 字段名,cast(ep.[value]
as varchar(100)) AS [字段说明]
FROM sys.tables AS t
INNER JOIN sys.columns
AS c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties AS ep
ON ep.major_id = c.object_id AND ep.minor_id = c.column_id WHERE ep.class =1
AND t.name='{tableName}' and c.name='{columnName}'
)
begin
if(1=1)
begin
EXECUTE sp_updateextendedproperty N'MS_Description', '{description}', N'user', N'dbo', N'table','{tableName}', N'column', '{columnName}'
end
end
else
begin
EXECUTE sp_addextendedproperty N'MS_Description', '{description}', N'user', N'dbo', N'table', '{tableName}', N'column', '{columnName}'
end" )
.EndCommand();
}
/// <summary>
/// Create table comment.
/// </summary>
/// <param name="operation"></param>
/// <param name="builder"></param>
private void CreateTableComment(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
{
string tableName = string.Empty;
string description = string.Empty;
if (operation is AlterTableOperation)
{
var t = operation as AlterColumnOperation;
tableName = (operation as AlterTableOperation).Name;
}
if (operation is CreateTableOperation)
{
var t = operation as CreateTableOperation;
var addColumnsOperation = t.Columns;
tableName = (operation as CreateTableOperation).Name;
foreach (var item in addColumnsOperation)
{
CreateColumnComment(item, model, builder);
}
}
description = DbDescriptionHelper.GetDescription(tableName);
if (tableName.IsNullOrWhiteSpace())
{
throw new Exception("Create table comment error.");
}
var sqlHelper = Dependencies.SqlGenerationHelper;
builder
.Append($@"
if exists(
SELECT tbs.name 表名,ds.value 描述 FROM sys.extended_properties ds
LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id WHERE ds.minor_id=0 and tbs.name='{tableName}')
begin
if(1=1)
begin
EXEC sp_updateextendedproperty 'MS_Description','{description}','user',dbo,'table','{tableName}',null,null
end
end
else
begin
EXECUTE sp_addextendedproperty N'MS_Description','{description}', N'user', N'dbo', N'table', '{tableName}', NULL, NULL
end" )
.EndCommand();
}
