string GetColumnComment(SOColumn column)
{
string key = string.Format("{0}_{1}_{2}", column.Database.Name, column.Parent.Name, column.Name);
lock (syncObject)
{
if (dic.ContainsKey(key)) return dic[key];
else
{
string cmdText = string.Format(@"use [{0}];
SELECT major_id, minor_id, t.name AS 'table_name', c.name AS 'column_name', value AS 'comment'
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class = 1;", column.Database.Name);
DataTable dt = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
string tempKey = string.Format("{0}_{1}_{2}", column.Database.Name, row["table_name"].ToString(), row["column_name"].ToString());
if (dic.ContainsKey(tempKey)) continue;
dic.Add(tempKey, row["comment"].ToString());
}
}
if (dic.ContainsKey(key)) return dic[key];
else return column.Name;
}
}
/// <summary>
/// 获取表所拥有的列列表
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public override List<SOColumn> GetTableColumnList(SOTable table)
{
string cmdText = string.Format(@"select c.COLUMN_ID,c.COLUMN_NAME,c.DATA_TYPE,c.DATA_LENGTH,c.DATA_PRECISION,c.DATA_SCALE,c.NULLABLE,c.DATA_DEFAULT,c.CHAR_COL_DECL_LENGTH,c.CHAR_LENGTH,cc.COMMENTS
from dba_tab_columns c left join dba_col_comments cc on c.table_name=cc.table_name and c.column_name=cc.column_name
where c.owner='{0}' and cc.owner='{0}' and c.table_name='{1}' order by c.column_id", table.Database.Name, table.Name);
List<SOColumn> columnList = new List<SOColumn>();
List<string> pkList = GetPrimaryKeys(table);
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
StringCollection sc = new StringCollection();
foreach (DataRow row in dt.Rows)
{
SOColumn column = new SOColumn
{
Parent = table,
Name = row["COLUMN_NAME"].ToString(),
DefaultValue = row["DATA_LENGTH"].ToString(),
Nullable = row["NULLABLE"].ToString().ToUpper() == "Y",
NativeType = row["DATA_TYPE"].ToString().Replace(" identity", ""),
//Identify = row["type_name"].ToString().IndexOf("identity") != -1,
Identify = false,
//ForeignKey
Length = ConvertUtil.ToInt32(row["CHAR_LENGTH"], 0),
Precision = ConvertUtil.ToInt32(row["DATA_PRECISION"], 0),
Scale = ConvertUtil.ToInt32(row["DATA_SCALE"], 0),
Comment = row["COMMENTS"].ToString()
};
column.PrimaryKey = pkList.Contains(column.Name);
column.DataType = this.GetDbType(column.NativeType, column.Precision, column.Scale);
if (!sc.Contains(column.Name))
{
columnList.Add(column);
}
sc.Add(column.Name);
}
return columnList;
}
/// <summary>
/// 获取表所拥有的列列表
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public virtual List<SOColumn> GetTableColumnList(SOTable table)
{
List<SOColumn> list = new List<SOColumn>();
string[] restrictions = new string[4];
restrictions[0] = table.Database.Name;
restrictions[2] = table.Name;
DataTable dt = GetSchema(MetaDataCollectionName_Columns, restrictions);
foreach (DataRow dr in dt.Rows)
{
SOColumn c = new SOColumn();
c.Name = dr["column_name"].ToString();
c.Comment = c.Name;
c.Parent = table;
c.DefaultValue = ConvertUtil.ToString(dr["column_default"]);
c.Nullable = dr["is_nullable"].ToString() == "YES" ? true : false;
c.NativeType = dr["data_type"].ToString();
c.Length = ConvertUtil.ToInt32(dr["character_maximum_length"], -1);
c.Precision = ConvertUtil.ToInt32(dr["numeric_precision"], -1);
c.Scale = ConvertUtil.ToInt32(dr["numeric_scale"], -1);
c.DataType = this.GetDbType(c.NativeType);
list.Add(c);
}
return list;
}
/// <summary>
/// 获取表所拥有的列列表
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public override List<SOColumn> GetTableColumnList(SOTable table)
{
List<SOColumn> list = new List<SOColumn>();
string[] restrictions = new string[4];
restrictions[0] = table.Database.Name;
restrictions[2] = table.Name;
DataTable dt = GetSchema(MetaDataCollectionName_Columns, restrictions);
List<SOIndex> indexList = this.GetTableIndexList(table);
foreach (DataRow dr in dt.Rows)
{
SOColumn c = new SOColumn();
c.Name = dr["column_name"].ToString();
c.Comment = dr["DESCRIPTION"].ToString();
c.Parent = table;
if (dr["COLUMN_HASDEFAULT"].ToString().ToLower() != "false")
{
c.DefaultValue = ConvertUtil.ToString(dr["COLUMN_DEFAULT"]);
}
c.Nullable = dr["IS_NULLABLE"].ToString().ToLower() == "true" ? true : false;
c.NativeType = dr["DATA_TYPE"].ToString();
c.Length = ConvertUtil.ToInt32(dr["CHARACTER_MAXIMUM_LENGTH"], -1);
c.Precision = ConvertUtil.ToInt32(dr["NUMERIC_PRECISION"], -1);
c.Scale = ConvertUtil.ToInt32(dr["NUMERIC_SCALE"], -1);
c.DataType = this.GetDbType(c.NativeType);
c.PrimaryKey = IndexColumnIsPrimary(indexList, c.Name);
c.Identify = IndexColumnIsIdentity(indexList, c.Name);
list.Add(c);
}
return list;
}
/// <summary>
/// 获取视图所拥有的列列表
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public override List<SOColumn> GetViewColumnList(SOView view)
{
string cmdText = string.Format(@"use [{2}];exec sp_columns '{0}','{1}','{2}';", view.Name, view.Owner, view.Database.Name);
List<SOColumn> columnList = new List<SOColumn>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOColumn column = new SOColumn
{
Parent = view,
Name = row["column_name"].ToString(),
DefaultValue = row["column_def"].ToString(),
Nullable = row["is_nullable"].ToString().ToUpper() == "YES",
NativeType = row["type_name"].ToString().Replace(" identity", ""),
Identify = row["type_name"].ToString().IndexOf("identity") != -1,
//ForeignKey
Length = ConvertUtil.ToInt32(row["length"], -1),
Precision = ConvertUtil.ToInt32(row["precision"], -1),
Scale = ConvertUtil.ToInt32(row["scale"], -1),
};
column.DataType = this.GetDbType(column.NativeType);
column.Comment = GetColumnComment(column);
columnList.Add(column);
}
return columnList;
}
/// <summary>
/// 获取视图所拥有的列列表
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public override List<SOColumn> GetViewColumnList(SOView view)
{
string cmdText = string.Format(@"SELECT *
FROM INFORMATION_SCHEMA.`COLUMNS`
WHERE table_schema='{0}' AND table_name='{1}'
ORDER BY ordinal_position;", view.Database.Name, view.Name);
List<SOColumn> columnList = new List<SOColumn>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOColumn column = new SOColumn
{
Parent = view,
Name = row["column_name"].ToString(),
DefaultValue = row["column_default"].ToString(),
Nullable = row["is_nullable"].ToString().ToUpper() == "YES",
NativeType = row["data_type"].ToString(),
Identify = row["extra"].ToString().IndexOf("auto_increment") != -1,
//ForeignKey
Length = ConvertUtil.ToInt32(row["character_maximum_length"], -1),
Precision = ConvertUtil.ToInt32(row["numeric_precision"], -1),
PrimaryKey = row["column_key"].ToString() == "PRI",
Scale = ConvertUtil.ToInt32(row["numeric_scale"], -1),
Comment = row["column_comment"].ToString()
};
column.DataType = this.GetDbType(column.NativeType);
columnList.Add(column);
}
return columnList;
}
string GetDbColumnType(SOColumn c)
{
string s = c.NativeType;
if (c.Length == -1) return s;
switch (c.DataType)
{
case System.Data.DbType.AnsiString:
case System.Data.DbType.AnsiStringFixedLength:
case System.Data.DbType.String:
case System.Data.DbType.StringFixedLength:
//s = string.Format("{0}({1})", c.NativeType, c.NativeType.StartsWith("n") ? c.Size / 2 : c.Size);
s = string.Format("{0}({1})", c.NativeType, c.Length);
break;
case System.Data.DbType.Currency:
case System.Data.DbType.Decimal:
case System.Data.DbType.Single:
s = string.Format("{0}({1}, {2})", c.NativeType, c.Precision, c.Scale);
break;
default:
s = c.NativeType;
break;
}
return s;
}
public static SOColumn ToSOColumn(PDColumn column)
{
SOColumn c = new SOColumn();
c.Name = column.Code;
c.Comment = string.IsNullOrEmpty(column.Comment) ? column.Name : column.Comment;
c.PrimaryKey = column.IsPK;
c.ForeignKey = column.IsFK;
c.Identify = column.Identity;
c.Length = column.Length;
c.NativeType = column.DataType.Split('(')[0];
c.Nullable = !column.Mandatory;
c.DefaultValue = column.DefaultValue;
c.Precision = column.Length;
c.Scale = column.Precision; //pdm的精度对应数据库的小数位数
c.DataType = PDMDataTypeToDbType(column);
return c;
}