public override IList<ColumnEntity> GetColumns(TableEntity table) { IList<ColumnEntity> list = null; DataTable columns = new DataTable(); columns.Locale = CultureInfo.CurrentCulture; using (SqlConnection connection = new SqlConnection(table.DbConnectionStringBuilder.ConnectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandText = string.Format(CultureInfo.CurrentCulture, "SELECT TOP 1 * FROM {0}", table.Name); using(IDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo)) { columns = reader.GetSchemaTable(); reader.Close(); } #region //DataTable columns = connection.GetSchema(SqlClientMetaDataCollectionNames.Columns, new string[] { null, null, table.Name, null }); //if (columns != null && columns.Rows.Count > 0) //{ // list = new List<ColumnEntity>(); // DataView dv = columns.DefaultView; // dv.Sort = "ORDINAL_POSITION asc"; // foreach (DataRowView view in dv) // { // string name = string.Format("{0}", view["COLUMN_NAME"]); // string description = string.Format("{0}({1})", view["COLUMN_NAME"], view["DATA_TYPE"]); // ColumnEntity column = new ColumnEntity(table.DbConnectionStringBuilder,name); // column.Description = description; // column.DataType = DbType.SqlParse(view["DATA_TYPE"].ToString()); // column.Table = table; // list.Add(column); // } //} #endregion } if (columns != null) { list = new List<ColumnEntity>(); foreach (DataRow row in columns.Rows) { ColumnEntity column = new ColumnEntity(table.DbConnectionStringBuilder, row["ColumnName"].ToString()); column.Description = string.Format("{0}({1})", row["ColumnName"].ToString(), row["DataTypeName"].ToString()); column.AllowDBNull = bool.Parse(row["AllowDBNull"].ToString()); column.DataType = (Type)row["DataType"]; column.IsIdentity = (bool)row["IsIdentity"]; column.IsPrimaryKey = (bool)row["IsKey"]; column.IsReadOnly = (bool)row["IsReadOnly"]; column.IsUnique = (bool)row["IsUnique"]; column.Table = table; list.Add(column); } CheckForeignKeys(table, list); } return list; }
public override IList<TableEntity> GetTables(DataBaseEntity database) { IList<TableEntity> list = null; using (MySqlConnection connection = new MySqlConnection(database.DbConnectionStringBuilder.ConnectionString)) { connection.Open(); DataTable tables = connection.GetSchema(SqlClientMetaDataCollectionNames.Tables, new string[] { null, null, null, "BASE TABLE" }); if (tables != null && tables.Rows.Count > 0) { list = new List<TableEntity>(); foreach (DataRow table in tables.Rows) { string name = string.Format("{0}", table["TABLE_NAME"]); string des = string.Format("{0}.{1}", table["TABLE_SCHEMA"], table["TABLE_NAME"]); TableEntity t = new TableEntity(database.DbConnectionStringBuilder,name, des); t.DataBase = database; list.Add(t); } } } return list; }
private void CheckForeignKeys(TableEntity table,IList<ColumnEntity> Columns) { #region //string[] restrictions = new string[4] { null, null, table.Name, null }; //DataTable foreignKeys = null; //DataTable tablekeys = null; //string text1 = string.Format(CultureInfo.CurrentCulture, "SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name = '{1}'", table.Name); //using (SqlConnection connection = new SqlConnection(table.DbConnectionStringBuilder.ConnectionString)) //{ // connection.Open(); // foreignKeys = connection.GetSchema("ForeignKeys", restrictions); // SqlCommand command1 = new SqlCommand(text1,connection); // using (IDataReader reader = command1.ExecuteReader()) // { // tablekeys=reader.GetSchemaTable(); // } //} //if (foreignKeys != null && Columns != null) //{ // foreach (DataRow row in foreignKeys.Rows) // { // foreach (ColumnEntity c in Columns) // { // if (row[""].ToString().Equals(c.Name)) // { // c.IsForeignKey = true; // break; // } // } // } //} #endregion StringBuilder text = new StringBuilder(); text.Append("SELECT "); text.Append("Fk_Table = object_name(b.fkeyid) ,"); text.Append("FK_Column = (SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) ,"); text.Append("PK_Table = object_name(b.rkeyid) ,"); text.Append("PK_Column = (SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) ,"); text.Append("Cascade_Update = ObjectProperty(a.id,'CnstIsUpdateCascade') ,");//级联更新 text.Append("Cascade_Delete = ObjectProperty(a.id,'CnstIsDeleteCascade') ");//级联删除 text.Append("FROM sysobjects a "); text.Append("JOIN sysforeignkeys b ON a.id = b.constid "); text.Append("JOIN sysobjects c ON a.parent_obj = c.id "); text.Append("WHERE a.xtype = 'F' AND c.xtype = 'U' and object_name(b.fkeyid)='{0}'"); DataTable list = new DataTable(); string[] str = { "Fk_Table", "FK_Column", "PK_Table", "PK_Column", "Cascade_Update", "Cascade_Delete" }; foreach (var s in str) { list.Columns.Add(new DataColumn(s)); } using (SqlConnection connection = new SqlConnection(table.DbConnectionStringBuilder.ConnectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandText = string.Format(CultureInfo.CurrentCulture,text.ToString(), table.Name); using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) { while (reader.Read()) { DataRow row = list.NewRow(); row[0] = reader[0].ToString(); row[1] = reader[1].ToString(); row[2] = reader[2].ToString(); row[3] = reader[3].ToString(); list.Rows.Add(row); } } } if (list != null) { foreach (DataRow row in list.Rows) { foreach (ColumnEntity temp in Columns) { if (temp.Name.Equals(row["FK_Column"].ToString())) { temp.IsForeignKey = true; break; } } } } }
public abstract IList<ColumnEntity> GetColumns(TableEntity table);