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);
}
/// <summary>
/// 获取表所拥有的列列表
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public override List <SOColumn> GetTableColumnList(SOTable table)
{
string cmdText = string.Format(@"SELECT *
FROM INFORMATION_SCHEMA.`COLUMNS`
WHERE table_schema='{0}' AND table_name='{1}'
ORDER BY ordinal_position;", table.Database.Name, table.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 = table,
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>
/// 获取表所拥有的列列表
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public override List <SOColumn> GetTableColumnList(SOTable table)
{
string cmdText = string.Format(@"use {2};exec sp_columns '{0}','{1}','{2}';", table.Name, table.Owner, table.Database.Name);
List <SOColumn> columnList = new List <SOColumn>();
List <string> pkList = GetPrimaryKeys(table);
DataTable dt = this.DbProvider.ExecuteDataSet(System.Data.CommandType.Text, cmdText).Tables[0];
foreach (DataRow row in dt.Rows)
{
SOColumn column = new SOColumn
{
Parent = table,
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.PrimaryKey = pkList.Contains(column.Name);
column.DataType = this.GetDbType(column.NativeType);
column.Comment = GetColumnComment(column);
columnList.Add(column);
}
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)
{
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);
if (!IsDBA)
{
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 user_tab_columns c left join user_col_comments cc on c.table_name = cc.table_name and c.column_name = cc.column_name
where c.table_name='{0}' order by c.column_id", 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);
}
public void DoBuildCode()
{
Config.MainForm.SetStatusText("正在生成代码...");
textEditorControl1.SaveFile(gbTemplateFile.Text);
TableHost host = new TableHost();
host.Table = this.Table;
host.TemplateFile = gbTemplateFile.Text;
List <SOColumn> columnList = new List <SOColumn>();
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (row.Cells[0].FormattedValue.ToString().ToLower() == "true")
{
SOColumn c = row.DataBoundItem as SOColumn;
columnList.Add(c);
}
}
host.ColumnList = columnList;
Engine engine = new Engine();
var sw = new Stopwatch();
var content = File.ReadAllText(host.TemplateFile);
string outputContent = engine.ProcessTemplate(content, host);
StringBuilder sb = new StringBuilder();
if (host.ErrorCollection.HasErrors)
{
foreach (CompilerError err in host.ErrorCollection)
{
sb.AppendLine(err.ToString());
}
outputContent = outputContent + Environment.NewLine + sb.ToString();
}
textEditorControl2.Text = outputContent;
tabControl1.SelectedTab = tabPage2;
textEditorControl2.Refresh();
Config.MainForm.SetStatusText();
}
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);
}
/// <summary>
/// 获取表所拥有的列列表
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public override List <SOColumn> GetTableColumnList(SOTable table)
{
string cmdText = string.Format(@"use [{2}];select colm.name column_name, object_definition(colm.default_object_id) as column_def, systype.name type_name, colm.is_identity, colm.is_nullable ,
cast(colm.max_length as int) length, cast(colm.precision as int) precision, cast(colm.scale as int) scale, colm.is_computed
from sys.columns colm
inner join sys.types systype on colm.system_type_id = systype.system_type_id and colm.user_type_id = systype.user_type_id
where colm.object_id = object_id('{0}')
order by colm.column_id;", table.Name, table.Owner, table.Database.Name);
List <SOColumn> columnList = new List <SOColumn>();
List <string> pkList = GetPrimaryKeys(table);
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}'; ", table.Name);
string fkcol = this.DbProvider.ExecuteScalar <string>(CommandType.Text, sql);
foreach (DataRow row in dt.Rows)
{
SOColumn column = new SOColumn
{
Parent = table,
Name = row["column_name"].ToString(),
DefaultValue = row["column_def"].ToString(),
Nullable = row["is_nullable"].ToString() == "True",
NativeType = row["type_name"].ToString(),
Identify = row["is_identity"].ToString() == "True",
Computed = row["is_computed"].ToString() == "True",
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.PrimaryKey = pkList.Contains(column.Name);
column.DataType = this.GetDbType(column.NativeType);
column.Comment = GetColumnComment(column);
columnList.Add(column);
}
return(columnList);
}
private 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)
{
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);
c.Computed = false;
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];
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);
}
//生成数据插入脚本
private void menuItemBuildInsertSqlForTable_Click(object sender, EventArgs e)
{
TreeNode tn = tvDatabase.SelectedNode;
SOTable table = tn.Tag as SOTable;
List <SOColumn> list = dbSchema.GetTableColumnList(table);
string cmdText = string.Format("select * from {0}", table.FullName);
DataTable dt = DbSchemaHelper.Instance.CurrentSchema.ExecuteQuery(table.Database, cmdText).Tables[0];
StringBuilder sbDocText = new StringBuilder();
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO ");
sb.Append(table.FullName + " (");
for (int i = 0; i < list.Count; i++)
{
SOColumn c = list[i];
if (c.Identify)
{
continue; //忽略标识列
}
if (i != list.Count - 1)
{
sb.Append(string.Format("{0},", dbSchema.QuoteIdentifier(c.Name)));
}
else
{
sb.Append(string.Format("{0}) VALUES (", dbSchema.QuoteIdentifier(c.Name)));
}
}
for (int i = 0; i < list.Count; i++)
{
SOColumn c = list[i];
if (c.Identify)
{
continue; //忽略标识列
}
string value = "";
if (c.DataType == DbType.Boolean)
{
value = string.Format("'{0}'", Utils.DbBooleanValueToStirng(dr[c.Name]));
}
else if (c.DataType == DbType.Byte || c.DataType == DbType.Currency || c.DataType == DbType.Decimal ||
c.DataType == DbType.Double || c.DataType == DbType.Int16 || c.DataType == DbType.Int32 ||
c.DataType == DbType.Int64 || c.DataType == DbType.SByte || c.DataType == DbType.Single ||
c.DataType == DbType.UInt16 || c.DataType == DbType.UInt32 || c.DataType == DbType.UInt64)
{
value = Utils.DbNumericValueToString(dr[c.Name]);
}
else
{
value = string.Format("'{0}'", Utils.DbStringValueToString(dr[c.Name]));
}
if (i != list.Count - 1)
{
sb.Append(string.Format("{0},", value));
}
else
{
sb.Append(string.Format("{0});", value));
}
}
sbDocText.AppendLine(sb.ToString());
}
}
else
{
sbDocText.Append(string.Format("表“{0}”没有任何数据！", table.FullName));
}
base.MainForm.NewDockDocument(string.Format("{0}_InsertData", table.Name), CodeType.TSQL, sbDocText.ToString());
}
/// <summary>
/// 获取指定语言的类型字符串
/// </summary>
/// <param name="colItem"></param>
/// <returns></returns>
public String GetTypeString(SOColumn colItem)
{
return(TypeMapConfigBLL.ConvertToLanguageType(this.typeMapConfigList, colItem.NativeType, colItem.Length));
}
