public void CopyFromAttrs(List<AttributeInfo> attrs)
{
rows.Clear();
AttributeInfo row = null;
for (int i = 0; i < attrs.Count; i++)
{
row = new AttributeInfo();
row = attrs[i];
rows.Add(row);
}
}
/// <summary>
/// 添加一个新的外键.
/// </summary>
/// <param name="entity1"></param>
/// <param name="att1"></param>
/// <param name="entity2"></param>
/// <param name="att2"></param>
/// <returns></returns>
public ConstraintInfo AddConstraint(string Fori_NAME, EntityInfo entity1, AttributeInfo att1, EntityInfo entity2, AttributeInfo att2)
{
ConstraintInfo constraint = new ConstraintInfo();
if (Fori_NAME== "")
{
constraint.Constraint_name = "FK_" + entity1.ClassName + "_" + att1.ColumnName + "_" + entity2.ClassName + "_" + att2.ColumnName;
}
constraint.Table_Name = entity1.ClassName;
constraint.Column_Name = att1.ColumnName;
constraint.ORDINAL_POSITION = entity1.Attributes.IndexOf(att1).ToString();
constraint.POSITION_IN_UNIQUE_CONSTRAINT = entity2.Attributes.IndexOf(att2).ToString();
constraint.REFERENCED_TABLE_NAME = entity2.ClassName;
constraint.REFERENCED_COLUMN_NAME = att2.ColumnName;
entity1.constraints.Add(constraint);
return constraint;
}
private void btnAddAttr_Click(object sender, EventArgs e)
{
AttributeInfo attribute = new AttributeInfo();
//attribute.Collation = Guid.NewGuid().ToString();
attribute.Comment = tx_AttributeName.Text;
attribute.ColumnName = txColumnName.Text;
entity.Attributes.Add(attribute);
tx_AttributeName.Text = "";
txColumnName.Text = "";
this.BindAttributes();
tx_AttributeName.Focus();
}
/// <summary>
/// 获取单表列的集合.
/// </summary>
/// <param name="table_Schema">数据库名.</param>
/// <param name="tableID">表名.</param>
/// <param name="dbtype"></param>
/// <returns></returns>
public List<AttributeInfo> GetTableFields(string tableID, DBTYPE dbtype, string table_Schema)
{
switch (dbtype)
{
case DBTYPE.UNKNOW:
break;
case DBTYPE.SQLSERVER:
using (SqlConnection conn = new SqlConnection(formConf.connectionString))
{
conn.Open();
List<AttributeInfo> columns = new List<AttributeInfo>();
string cmdText = "SELECT *,syscolumns.length as max_length,systypes.name as typeName FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = '" + tableID + "'";
SqlCommand cmd = new SqlCommand(cmdText, conn);
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
AttributeInfo column = new AttributeInfo();
column.cisNull = Convert.ToBoolean(dr["isnullable"]);
column.ColumnName = dr["name"].ToString();
//column.DefaultVal
//column.DeText
//column.IsIdentity = Convert.ToBoolean(dr["is_identity"]);
//column.IsPK
//column.Length = dr["max_length"].ToString();
column.Length = dr["length"].ToString();
column.Preci = dr["prec"].ToString();
column.Scale = dr["scale"].ToString();
column.TypeName = dr["typeName"].ToString();
columns.Add(column);
}
}
return columns;
}
case DBTYPE.MYSQL_MARIADB:
//string strcc = formConf.connectionString.Replace(table_Schema, "information_schema");
using (MySqlConnection conn = new MySqlConnection(formConf.connectionString))
{
conn.Open();
List<AttributeInfo> columns = new List<AttributeInfo>();
//string cmdText = "show columns from " + tableID;
string cmdText = "SHOW FULL FIELDS from " + tableID;
//string cmdText = "select * from COLUMNS where table_schema='" + table_Schema + "' and table_name='" + tableID + "'";
//string cmdText = "SELECT *,syscolumns.length as max_length,systypes.name as typeName FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = ' " + tableID + "'";
MySqlCommand cmd = new MySqlCommand(cmdText, conn);
using (MySqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
// use information_schema;
// SELECT TABLE_NAME '表名',ORDINAL_POSITION '顺序',COLUMN_NAME '字段',DATA_TYPE '类型'
// ,CHARACTER_OCTET_LENGTH '字节长',if(COLUMN_KEY='PRI',"√","") '主键',if(EXTRA='auto_increment',"√","") '自增长'
// ,if(IS_NULLABLE='YES',"√","") '空',CHARACTER_SET_NAME '编码',COLUMN_DEFAULT '默认值',COLUMN_COMMENT '说明'
// FROM COLUMNS WHERE TABLE_SCHEMA = 'webmanagesystem' ORDER BY TABLE_NAME,ORDINAL_POSITION;
AttributeInfo column = new AttributeInfo();
column.ColumnName = dr["Field"].ToString();
column.TypeName = dr["Type"].ToString();
Regex r = new Regex(@"\d+");
Regex r1 = new Regex(@"\w+");
column.TypeName = r1.Match(dr["Type"].ToString()).Value;
column.Length = r.Match(dr["Type"].ToString()).Value;
column.Collation = dr["Collation"].ToString();
column.cisNull = dr["Null"].ToString().ToUpper().Equals("YES");
column.IsPK = dr["KEY"].ToString().Equals("PRI");
//column.IsUnique = dr["IsUnique"].ToString();
column.Extra = dr["Extra"].ToString();
column.IsIdentity = column.Extra == "auto_increment";
column.Comment = dr["Comment"].ToString().Length > 0 ? dr["Comment"].ToString() : dr["Field"].ToString();
string fdd = dr["Type"].ToString();
try
{
string lll = fdd.Split('(')[1];
if (lll.Contains(','))
{
column.Preci = lll.TrimEnd(')').Split(',')[0];
column.Scale = lll.TrimEnd(')').Split(',')[1];
}
}
catch (Exception ex)
{
MessageBox.Show(tableID + ":" + ex.Message);
}
columns.Add(column);
}
}
return columns;
}
case DBTYPE.ORACLE:
break;
default:
break;
}
return null;
}
public AttributeInfo Clone()
{
AttributeInfo attr = new AttributeInfo();
attr.Comment = this.Comment;
attr.ColumnName = this.ColumnName;
attr.TypeName = this.TypeName;
attr.Length = this.Length;
attr.IsPK = this.IsPK;
attr.IsIdentity = this.IsIdentity;
attr.cisNull = this.cisNull;
attr.IsUnique = this.IsUnique;
attr.Key = this.Key;
attr.Extra = this.Extra;
attr.DefaultVal = this.DefaultVal;
attr.Preci = this.Preci;
attr.Scale = this.Scale;
attr.Collation = this.Collation;
attr.Notes = this.Notes;
return attr;
}
