/// <summary>
/// 以视图对象为原型创建一个表对象实例
/// </summary>
/// <param name="view"></param>
public SOTable(SOView view)
{
this.ColumnList = view.ColumnList;
this.Comment = view.Comment ?? string.Empty;
this.Name = view.Name;
this.Owner = view.Owner;
this.Parent = view.Parent;
this.SchemaName = view.SchemaName;
this.SqlText = view.SqlText;
}
/// <summary>
/// 以视图对象为原型创建一个表对象实例
/// </summary>
/// <param name="view"></param>
public SOTable(SOView view)
{
this.ColumnList = view.ColumnList;
this.Comment = view.Comment ?? string.Empty;
this.Name = view.Name;
this.Owner = view.Owner;
this.Parent = view.Parent;
this.SchemaName = view.SchemaName;
this.SqlText = view.SqlText;
}
public SOView GetView(string viewName)
{
List <SOView> list = this.ViewList;
SOView view = null;
foreach (SOView item in list)
{
if (item.Name == viewName)
{
view = item;
break;
}
}
return(view);
}
/// <summary>
/// 获取视图的Sql脚本
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public override string GetViewSqlText(SOView view)
{
return view.SqlText;
}
/// <summary>
/// 获取视图列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List<SOView> GetViewList(SODatabase db)
{
string cmdText = string.Format("select VIEW_NAME,TEXT from dba_views where owner='{0}'", db.Name);
SortedDictionary<string, SOView> dic = new SortedDictionary<string, SOView>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOView view = new SOView { Parent = db, Name = row["VIEW_NAME"].ToString(), SqlText=row["TEXT"].ToString(), Owner = db.Name };
view.SchemaName = view.Owner;
dic.Add(view.Name, view);
}
return dic.Values.ToList<SOView>();
}
/// <summary>
/// 获取视图的Sql脚本
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public virtual string GetViewSqlText(SOView view)
{
return "该方法目前还没有实现";
}
/// <summary>
/// 获取视图所拥有的索引列表
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public virtual List<SOIndex> GetViewIndexList(SOView view)
{
throw new NotImplementedException();
}
/// <summary>
/// 获取视图所拥有的列列表
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public virtual List<SOColumn> GetViewColumnList(SOView view)
{
throw new NotImplementedException();
}
/// <summary>
/// 获取视图列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public virtual List<SOView> GetViewList(SODatabase db)
{
List<SOView> list = new List<SOView>();
string[] restrictions = new string[3];
restrictions[0] = db.Name;
DataTable dt = GetSchema(MetaDataCollectionName_Views, restrictions);
foreach (DataRow dr in dt.Rows)
{
SOView view = new SOView();
view.Name = dr["table_name"].ToString();
view.Comment = view.Name;
view.Parent = db;
list.Add(view);
}
return list;
}
/// <summary>
/// 获取视图的Sql脚本
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public override string GetViewSqlText(SOView view)
{
throw new NotImplementedException();
}
///// <summary>
///// 获取表的Sql脚本
///// </summary>
///// <param name="table"></param>
///// <returns></returns>
//public override string GetTableSqlText(SOTable table)
//{
// throw new NotImplementedException();
//}
/// <summary>
/// 获取视图的Sql脚本
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public override string GetViewSqlText(SOView view)
{
string cmdText = string.Format("use [{0}];SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE table_schema='{1}' AND table_name='{2}';", view.Database.Name, view.Owner, view.Name);
string text = this.DbProvider.ExecuteScalar(System.Data.CommandType.Text, cmdText).ToString();
return text;
}
/// <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="table"></param>
// /// <returns></returns>
// public override List<SOIndex> GetTableIndexList(SOTable table)
// {
// string cmdText = string.Format(@"SELECT *
// FROM INFORMATION_SCHEMA.`constraints`
// WHERE table_schema='{0}' AND table_name='{1}';", table.Database.Name, table.Name);
// List<SOIndex> indexList = new List<SOIndex>();
// List<SOColumn> columnList = GetTableColumnList(table);
// DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
// foreach (DataRow row in dt.Rows)
// {
// SOIndex index = new SOIndex
// {
// Parent = table,
// Name = row["constraint_name"].ToString(),
// Comment = row["constraint_name"].ToString(),
// IsCluster = false,
// IsFullText = row["constraint_type"].ToString() == "Full Text",
// IsPrimaryKey = row["constraint_type"].ToString() == "PRIMARY KEY",
// IsUnique = row["constraint_type"].ToString() == "UNIQUE"
// };
// indexList.Add(index);
// string cmdText2 = string.Format(@"SELECT column_name
// FROM INFORMATION_SCHEMA.`statistics`
// WHERE table_schema='{0}' AND table_name='{1}';", table.Database.Name, table.Name);
// DataTable dt2 = this.DbProvider.ExecuteDataSet(CommandType.Text, cmdText2).Tables[0];
// index.Columns = new List<SOColumn>();
// foreach (DataRow row2 in dt2.Rows)
// {
// foreach (SOColumn column in columnList)
// {
// if (row2[0].ToString() == column.Name) index.Columns.Add(column);
// }
// }
// }
// return indexList;
// }
/// <summary>
/// 获取视图列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List<SOView> GetViewList(SODatabase db)
{
string cmdText = string.Format("use [{0}];exec sp_tables;", db.Name);
SortedDictionary<string, SOView> dic = new SortedDictionary<string, SOView>();
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_TYPE"].ToString() != "VIEW") continue;
if (row["TABLE_OWNER"].ToString() == "sys" || row["TABLE_OWNER"].ToString() == "INFORMATION_SCHEMA") continue;
SOView view = new SOView { Parent = db, Name = row["TABLE_NAME"].ToString(), Owner = row["TABLE_OWNER"].ToString() };
view.SchemaName = view.Owner;
dic.Add(view.Name, view);
}
return dic.Values.ToList<SOView>();
}
public DbViewViewer(SOView view)
{
InitializeComponent();
currentView = view;
}
/// <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;
}
public static SOView ToSOView(PDView view)
{
SOView v = new SOView();
return v;
}