//查看视图定义
private void menuItemViewSql_Click(object sender, EventArgs e)
{
TreeNode tn = tvDatabase.SelectedNode;
SOView v = tn.Tag as SOView;
base.MainForm.NewDockDocument(v.Name, CodeType.TSQL, v.SqlText);
}
// /// <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>());
}
/// <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);
}
///// <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);
}
private void BuildCodeByViewSchema(object item)
{
SOView view = item as SOView;
List <SOColumn> columnList = DbSchemaHelper.Instance.CurrentSchema.GetViewColumnList(view);
//生成代码文件
ViewHost host = new ViewHost();
host.View = view;
host.ColumnList = columnList;
host.TemplateFile = templateFile;
foreach (object obj in listBox3.Items)
{
string[] ss = obj.ToString().Split('|');
host.SetValue(ss[0], ss[1].Replace("[<->]", "|"));
}
Engine engine = new Engine();
string fileName = string.Empty;
string separator = txtFileNamePrefix.Text.Trim();
if (separator != "")
{
fileName = string.Format("{0}{1}", view.Name.RemovePrefix(separator, 10), host.FileExtention);
}
else
{
fileName = string.Format("{0}{1}", view.Name, host.FileExtention);
}
string outputContent = engine.ProcessTemplate(File.ReadAllText(templateFile), host);
string outputFile = Path.Combine(outputPath, fileName);
StringBuilder sb = new StringBuilder();
if (host.ErrorCollection.HasErrors)
{
foreach (CompilerError err in host.ErrorCollection)
{
sb.AppendLine(err.ToString());
}
outputContent = outputContent + Environment.NewLine + sb.ToString();
outputFile = outputFile + ".error";
}
if (Directory.Exists(outputPath) == false)
{
Directory.CreateDirectory(outputPath);
}
File.WriteAllText(outputFile, outputContent, Encoding.UTF8);
}
/// <summary>
/// 获取视图列表
/// </summary>
/// <param name="db"></param>
/// <returns></returns>
public override List <SOView> GetViewList(SODatabase db)
{
string cmdText = string.Format("SELECT table_name,table_type,table_comment FROM INFORMATION_SCHEMA.`TABLES` WHERE table_schema='{0}' AND table_type='VIEW';", db.Name);
List <SOView> viewList = new List <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["table_name"].ToString(), Comment = row["table_comment"].ToString()
};
viewList.Add(view);
}
return(viewList);
}
/// <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>
/// 获取视图所拥有的索引列表
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public override List <SOIndex> GetViewIndexList(SOView view)
{
string cmdText = string.Format(@"SELECT *
FROM INFORMATION_SCHEMA.`constraints`
WHERE table_schema='{0}' AND table_name='{1}';", view.Database.Name, view.Name);
List <SOIndex> indexList = new List <SOIndex>();
List <SOColumn> columnList = GetViewColumnList(view);
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOIndex index = new SOIndex
{
Parent = view,
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}';", view.Database.Name, view.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 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>
/// 获取视图所拥有的列列表
/// </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];
string sql = string.Format(@"select col.name from syscolumns col,sysforeignkeys f where f.fkeyid=col.id and f.fkey=col.colid
and OBJECT_NAME(f.fkeyid) ='{0}'; ", view.Name);
string fkcol = this.DbProvider.ExecuteScalar <string>(CommandType.Text, sql);
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,
Computed = false,
ForeignKey = row["column_name"].ToString() == fkcol,
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);
}
public DbViewViewer(SOView view)
{
InitializeComponent();
currentView = view;
}
/// <summary>
/// 获取视图的Sql脚本
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public override string GetViewSqlText(SOView view)
{
return(view.SqlText);
}
/// <summary>
/// 获取视图所拥有的列列表
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public virtual List <SOColumn> GetViewColumnList(SOView view)
{
throw new NotImplementedException();
}
/// <summary>
/// 获取视图所拥有的索引列表
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public virtual List <SOIndex> GetViewIndexList(SOView view)
{
throw new NotImplementedException();
}
/// <summary>
/// 获取视图的Sql脚本
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public virtual string GetViewSqlText(SOView view)
{
return("该方法目前还没有实现");
}
/// <summary>
/// 获取视图的Sql脚本
/// </summary>
/// <param name="view"></param>
/// <returns></returns>
public override string GetViewSqlText(SOView view)
{
throw new NotImplementedException();
}
public static SOView ToSOView(PDView view)
{
SOView v = new SOView();
return(v);
}
