public List <ColumnInfo> GetColumns(string tableName)
{
List <ColumnInfo> colInfos = null;
TableColumnInfoDict.TryGetValue(tableName, out colInfos);
return(colInfos);
}
public List <ColumnInfo> GetColumns(string tableName)
{
Db.CheckTabStuct(tableName);
tableName = (tableName ?? string.Empty).ToLower();
List <ColumnInfo> colInfos = null;
TableColumnInfoDict.TryGetValue(tableName, out colInfos);
return(colInfos);
}
public bool SetColumnComment(string tableName, string columnName, string comment)
{
Db.CheckTabStuct(tableName, columnName);
tableName = TableInfoDict[tableName].TableName;
columnName = this[tableName, columnName].ColumnName;
string upsert_sql = string.Empty;
comment = (comment ?? string.Empty).Replace("'", "");
try
{
//切换schema，更新列描述
upsert_sql = "set search_path to " + TableSchemas[tableName] + ";comment on column \"" + tableName + "\".\"" + columnName + "\" is '" + comment + "'";
Db.ExecSql(upsert_sql);
List <ColumnInfo> lstColInfo = TableColumnInfoDict[tableName];
NameValueCollection nvcColDesc = new NameValueCollection();
lstColInfo.ForEach(t =>
{
if (t.ColumnName.Equals(columnName))
{
t.DeText = comment;
}
nvcColDesc.Add(t.ColumnName, t.DeText);
});
TableColumnInfoDict.Remove(tableName);
TableColumnInfoDict.Add(tableName, lstColInfo);
TableColumnComments.Remove(tableName);
TableColumnComments.Add(tableName, nvcColDesc);
var strKey = (tableName + "@" + columnName);
ColumnInfo colInfo = DictColumnInfo[strKey];
colInfo.DeText = comment;
DictColumnInfo[strKey] = colInfo;
}
catch (Exception ex)
{
LogUtils.LogError("DB", Developer.SysDefault, ex, upsert_sql);
return(false);
}
return(true);
}
public bool SetColumnComment(string tableName, string columnName, string comment)
{
Db.CheckTabStuct(tableName, columnName);
//tableName = (tableName ?? string.Empty).ToUpper();
//columnName = (columnName ?? string.Empty).ToUpper();
string upsert_sql = string.Empty;
comment = (comment ?? string.Empty).Replace("'", "");
try
{
upsert_sql = @"comment on column " + tableName + "." + columnName + " is '" + comment + "'";
Db.ExecSql(upsert_sql);
List <ColumnInfo> lstColInfo = TableColumnInfoDict[tableName];
NameValueCollection nvcColDesc = new NameValueCollection();
lstColInfo.ForEach(t =>
{
if (t.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase))
{
t.DeText = comment;
}
nvcColDesc.Add(t.ColumnName, t.DeText);
});
TableColumnInfoDict.Remove(tableName);
TableColumnInfoDict.Add(tableName, lstColInfo);
TableColumnComments.Remove(tableName);
TableColumnComments.Add(tableName, nvcColDesc);
var strKey = (tableName + "@" + columnName).ToUpper();
ColumnInfo colInfo = DictColumnInfo[strKey];
colInfo.DeText = comment;
DictColumnInfo[strKey] = colInfo;
}
catch (Exception ex)
{
LogUtils.LogError("DB", Developer.SysDefault, ex, upsert_sql);
return(false);
}
return(true);
}
public bool SetColumnComment(string tableName, string columnName, string comment)
{
Db.CheckTabStuct(tableName, columnName);
string upsert_sql = string.Empty;
comment = (comment ?? string.Empty).Replace("'", "");
try
{
upsert_sql = @"if exists (select * from ::fn_listextendedproperty (NULL, 'schema', '{3}', 'table', '{0}', 'column', default) where objname = '{1}') EXEC sp_updateextendedproperty 'MS_Description','{2}','schema',{3},'table','{0}','column',{1} else EXEC sp_addextendedproperty @name=N'MS_Description' , @value=N'{2}' ,@level0type=N'SCHEMA', @level0name=N'{3}', @level1type=N'TABLE', @level1name=N'{0}', @level2type=N'COLUMN', @level2name=N'{1}' ";
upsert_sql = string.Format(upsert_sql, tableName, columnName, comment, this.TableSchemas[tableName]);
Db.ExecSql(upsert_sql);
List <ColumnInfo> lstColInfo = TableColumnInfoDict[tableName];
NameValueCollection nvcColDesc = new NameValueCollection();
lstColInfo.ForEach(t =>
{
if (t.ColumnName.Equals(columnName, StringComparison.OrdinalIgnoreCase))
{
t.DeText = comment;
}
nvcColDesc.Add(t.ColumnName, t.DeText);
});
TableColumnInfoDict.Remove(tableName);
TableColumnInfoDict.Add(tableName, lstColInfo);
TableColumnComments.Remove(tableName);
TableColumnComments.Add(tableName, nvcColDesc);
ColumnInfo colInfo = DictColumnInfo[tableName + "@" + columnName];
colInfo.DeText = comment;
DictColumnInfo[tableName + "@" + columnName] = colInfo;
}
catch (Exception ex)
{
LogUtils.LogError("DB", Developer.SysDefault, ex, upsert_sql);
return(false);
}
return(true);
}
public bool SetColumnComment(string tableName, string columnName, string comment)
{
Db.CheckTabStuct(tableName, columnName);
string selsql = string.Empty;
string upsert_sql = string.Empty;
comment = (comment ?? string.Empty).Replace("'", "");
try
{
var colInfo = this[tableName, columnName];
string setSql = string.Empty;
if (!colInfo.CanNull)
{
setSql += " not null ";
}
if (colInfo.IsIdentity)
{
setSql += " auto_increment ";
}
if (!string.IsNullOrWhiteSpace(colInfo.DefaultVal))
{
setSql += " default '" + colInfo.DefaultVal + "' ";
}
selsql = "use information_schema;SELECT COLUMN_TYPE,EXTRA FROM COLUMNS WHERE TABLE_SCHEMA='" + DBName + "' and TABLE_NAME = '" + tableName + "' AND COLUMN_NAME = '" + columnName + "';";
var dict = Db.GetFirstRow(selsql);
if (colInfo.DefaultVal != null && colInfo.DefaultVal.Equals("CURRENT_TIMESTAMP", StringComparison.OrdinalIgnoreCase))
{
upsert_sql = "USE `" + DBName + "`;ALTER TABLE `" + DBName + "`.`" + tableName + "` CHANGE `" + columnName + "` `" + columnName + "` TIMESTAMP DEFAULT CURRENT_TIMESTAMP " + dict["EXTRA"] + " COMMENT '" + comment + "'; ";
}
else
{
string col_type = dict["COLUMN_TYPE"].ToString();
upsert_sql = "USE `" + DBName + "`;ALTER TABLE " + tableName + " MODIFY COLUMN `" + columnName + "` " + col_type + " " + setSql + " COMMENT '" + comment + "';";
}
Db.ExecSql(upsert_sql);
List <ColumnInfo> lstColInfo = TableColumnInfoDict[tableName];
NameValueCollection nvcColDesc = new NameValueCollection();
lstColInfo.ForEach(t =>
{
if (t.ColumnName.Equals(columnName))
{
t.DeText = comment;
}
nvcColDesc.Add(t.ColumnName, t.DeText);
});
TableColumnInfoDict.Remove(tableName);
TableColumnInfoDict.Add(tableName, lstColInfo);
TableColumnComments.Remove(tableName);
TableColumnComments.Add(tableName, nvcColDesc);
var strKey = (tableName + "@" + columnName);
colInfo = DictColumnInfo[strKey];
colInfo.DeText = comment;
DictColumnInfo[strKey] = colInfo;
}
catch (Exception ex)
{
LogUtils.LogError("DB", Developer.SysDefault, ex, selsql, upsert_sql);
return(false);
}
return(true);
}
