/*
* sys.sp_addextendedproperty @name = NULL, -- sysname
* @value = NULL, -- sql_variant
* @level0type = '', -- varchar(128)
* @level0name = NULL, -- sysname
* @level1type = '', -- varchar(128)
* @level1name = NULL, -- sysname
* @level2type = '', -- varchar(128)
* @level2name = NULL -- sysname
*
* sys.sp_updateextendedproperty @name = NULL, -- sysname
* @value = NULL, -- sql_variant
* @level0type = '', -- varchar(128)
* @level0name = NULL, -- sysname
* @level1type = '', -- varchar(128)
* @level1name = NULL, -- sysname
* @level2type = '', -- varchar(128)
* @level2name = NULL -- sysname
*
* sys.sp_dropextendedproperty @name = NULL, -- sysname
* @level0type = '', -- varchar(128)
* @level0name = NULL, -- sysname
* @level1type = '', -- varchar(128)
* @level1name = NULL, -- sysname
* @level2type = '', -- varchar(128)
* @level2name = NULL -- sysname
*/
/// <summary>
///
/// </summary>
/// <param name="db"></param>
/// <param name="id"></param>
/// <param name="description"></param>
/// <param name="metaDataType"></param>
public void CrudDescription(RdbDescriptor db, RDbMetaDataType metaDataType, string id, string description)
{
const string addProcName = "sys.sp_addextendedproperty";
const string updateProcName = "sys.sp_updateextendedproperty";
const string dropProcName = "sys.sp_dropextendedproperty";
const string propertyName = "MS_Description";
switch (metaDataType)
{
case RDbMetaDataType.Database:
break;
case RDbMetaDataType.Table:
#region Table
DbTable table;
if (!db.TryGetDbTable(id, out table))
{
throw new AnycmdException("意外的数据库表" + id);
}
if (description == null && table.Description != null)
{
db.ExecuteNonQuery(dropProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", table.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", table.Name, DbType.String));
}
else if (table.Description == null)
{
db.ExecuteNonQuery(addProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", table.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", table.Name, DbType.String));
}
else
{
db.ExecuteNonQuery(updateProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", table.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", table.Name, DbType.String));
}
table.Description = description;
#endregion
break;
case RDbMetaDataType.View:
#region View
DbView view;
if (!db.TryGetDbView(id, out view))
{
throw new AnycmdException("意外的数据库视图" + id);
}
if (description == null && view.Description != null)
{
db.ExecuteNonQuery(dropProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", view.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", view.Name, DbType.String));
}
else if (view.Description == null)
{
db.ExecuteNonQuery(addProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", view.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", view.Name, DbType.String));
}
else
{
db.ExecuteNonQuery(updateProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", view.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", view.Name, DbType.String));
}
view.Description = description;
#endregion
break;
case RDbMetaDataType.TableColumn:
#region TableColumn
DbTableColumn tableColumn;
if (!db.AcDomain.Rdbs.DbTableColumns.TryGetDbTableColumn(db, id, out tableColumn))
{
throw new AnycmdException("意外的数据库表列标识" + id);
}
if (description == null && tableColumn.Description != null)
{
db.ExecuteNonQuery(dropProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", tableColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", tableColumn.TableName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", tableColumn.Name, DbType.String));
}
else if (tableColumn.Description == null)
{
db.ExecuteNonQuery(addProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", tableColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", tableColumn.TableName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", tableColumn.Name, DbType.String));
}
else
{
db.ExecuteNonQuery(updateProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", tableColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", tableColumn.TableName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", tableColumn.Name, DbType.String));
}
tableColumn.Description = description;
#endregion
break;
case RDbMetaDataType.ViewColumn:
#region ViewColumn
DbViewColumn viewColumn;
if (!db.AcDomain.Rdbs.DbViewColumns.TryGetDbViewColumn(db, id, out viewColumn))
{
throw new AnycmdException("意外的数据库视图列标识" + id);
}
if (description == null && viewColumn.Description != null)
{
db.ExecuteNonQuery(dropProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", viewColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", viewColumn.ViewName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", viewColumn.Name, DbType.String));
}
else if (viewColumn.Description == null)
{
db.ExecuteNonQuery(addProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", viewColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", viewColumn.ViewName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", viewColumn.Name, DbType.String));
}
else
{
db.ExecuteNonQuery(updateProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", viewColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", viewColumn.ViewName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", viewColumn.Name, DbType.String));
}
viewColumn.Description = description;
#endregion
break;
default:
break;
}
}
/*
* sys.sp_addextendedproperty @name = NULL, -- sysname
@value = NULL, -- sql_variant
@level0type = '', -- varchar(128)
@level0name = NULL, -- sysname
@level1type = '', -- varchar(128)
@level1name = NULL, -- sysname
@level2type = '', -- varchar(128)
@level2name = NULL -- sysname
sys.sp_updateextendedproperty @name = NULL, -- sysname
@value = NULL, -- sql_variant
@level0type = '', -- varchar(128)
@level0name = NULL, -- sysname
@level1type = '', -- varchar(128)
@level1name = NULL, -- sysname
@level2type = '', -- varchar(128)
@level2name = NULL -- sysname
sys.sp_dropextendedproperty @name = NULL, -- sysname
@level0type = '', -- varchar(128)
@level0name = NULL, -- sysname
@level1type = '', -- varchar(128)
@level1name = NULL, -- sysname
@level2type = '', -- varchar(128)
@level2name = NULL -- sysname
*/
/// <summary>
///
/// </summary>
/// <param name="db"></param>
/// <param name="id"></param>
/// <param name="description"></param>
/// <param name="metaDataType"></param>
public void CrudDescription(RdbDescriptor db, RDbMetaDataType metaDataType, string id, string description)
{
const string addProcName = "sys.sp_addextendedproperty";
const string updateProcName = "sys.sp_updateextendedproperty";
const string dropProcName = "sys.sp_dropextendedproperty";
const string propertyName = "MS_Description";
switch (metaDataType)
{
case RDbMetaDataType.Database:
break;
case RDbMetaDataType.Table:
#region Table
DbTable table;
if (!db.TryGetDbTable(id, out table))
{
throw new AnycmdException("意外的数据库表" + id);
}
if (description == null && table.Description != null)
{
db.ExecuteNonQuery(dropProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", table.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", table.Name, DbType.String));
}
else if (table.Description == null)
{
db.ExecuteNonQuery(addProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", table.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", table.Name, DbType.String));
}
else
{
db.ExecuteNonQuery(updateProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", table.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", table.Name, DbType.String));
}
table.Description = description;
#endregion
break;
case RDbMetaDataType.View:
#region View
DbView view;
if (!db.TryGetDbView(id, out view))
{
throw new AnycmdException("意外的数据库视图" + id);
}
if (description == null && view.Description != null)
{
db.ExecuteNonQuery(dropProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", view.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", view.Name, DbType.String));
}
else if (view.Description == null)
{
db.ExecuteNonQuery(addProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", view.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", view.Name, DbType.String));
}
else
{
db.ExecuteNonQuery(updateProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", view.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", view.Name, DbType.String));
}
view.Description = description;
#endregion
break;
case RDbMetaDataType.TableColumn:
#region TableColumn
DbTableColumn tableColumn;
if (!db.AcDomain.Rdbs.DbTableColumns.TryGetDbTableColumn(db, id, out tableColumn))
{
throw new AnycmdException("意外的数据库表列标识" + id);
}
if (description == null && tableColumn.Description != null)
{
db.ExecuteNonQuery(dropProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", tableColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", tableColumn.TableName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", tableColumn.Name, DbType.String));
}
else if (tableColumn.Description == null)
{
db.ExecuteNonQuery(addProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", tableColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", tableColumn.TableName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", tableColumn.Name, DbType.String));
}
else
{
db.ExecuteNonQuery(updateProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", tableColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "TABLE", DbType.String),
CreateParameter(db, "level1name", tableColumn.TableName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", tableColumn.Name, DbType.String));
}
tableColumn.Description = description;
#endregion
break;
case RDbMetaDataType.ViewColumn:
#region ViewColumn
DbViewColumn viewColumn;
if (!db.AcDomain.Rdbs.DbViewColumns.TryGetDbViewColumn(db, id, out viewColumn))
{
throw new AnycmdException("意外的数据库视图列标识" + id);
}
if (description == null && viewColumn.Description != null)
{
db.ExecuteNonQuery(dropProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", viewColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", viewColumn.ViewName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", viewColumn.Name, DbType.String));
}
else if (viewColumn.Description == null)
{
db.ExecuteNonQuery(addProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", viewColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", viewColumn.ViewName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", viewColumn.Name, DbType.String));
}
else
{
db.ExecuteNonQuery(updateProcName, CommandType.StoredProcedure,
CreateParameter(db, "name", propertyName, DbType.String),
CreateParameter(db, "value", description, DbType.String),
CreateParameter(db, "level0type", "SCHEMA", DbType.String),
CreateParameter(db, "level0name", viewColumn.SchemaName, DbType.String),
CreateParameter(db, "level1type", "VIEW", DbType.String),
CreateParameter(db, "level1name", viewColumn.ViewName, DbType.String),
CreateParameter(db, "level2type", "COLUMN", DbType.String),
CreateParameter(db, "level2name", viewColumn.Name, DbType.String));
}
viewColumn.Description = description;
#endregion
break;
default:
break;
}
}
