// SchemaReader.ReadSchema public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory) { var result=new Tables(); this._connection=connection; this._factory=factory; var cmd=this._factory.CreateCommand(); cmd.Connection=connection; cmd.CommandText=TABLE_SQL; //cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null); //pull the tables in a reader using(cmd) { using (var rdr=cmd.ExecuteReader()) { while(rdr.Read()) { Table tbl=new Table(); tbl.Name=rdr["name"].ToString(); tbl.Schema = ""; tbl.IsView=String.Compare(rdr["type"].ToString(), "view", true)==0; tbl.CleanName=CleanUp(tbl.Name); tbl.ClassName=Inflector.MakeSingular(tbl.CleanName); tbl.SQL = rdr["sql"].ToString(); result.Add(tbl); } } } foreach (var tbl in result) { tbl.Columns=this.LoadColumns(tbl); tbl.Indexes = this.LoadIndices(tbl.Name); tbl.ForeignKeys = this.LoadForeignKeys(tbl.Name); } return result; }
// SchemaReader.ReadSchema public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory) { var result = new Tables(); var cmd = factory.CreateCommand(); cmd.Connection = connection; cmd.CommandText = TABLE_SQL; //pull the tables in a reader using (cmd) { using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Table tbl = new Table(); tbl.Name = rdr["TABLE_NAME"].ToString(); tbl.Schema = rdr["TABLE_SCHEMA"].ToString(); tbl.IsView = String.Compare(rdr["TABLE_TYPE"].ToString(), "View", true) == 0; tbl.CleanName = CleanUp(tbl.Name); tbl.ClassName = Inflector.MakeSingular(tbl.CleanName); result.Add(tbl); } } } //this will return everything for the DB var schema = connection.GetSchema("COLUMNS"); //loop again - but this time pull by table name foreach (var item in result) { item.Columns = new List<Column>(); //pull the columns from the schema var columns = schema.Select("TABLE_NAME='" + item.Name + "'"); foreach (var row in columns) { var type = GetPropertyType(row); Column col = new Column(); col.Name = row["COLUMN_NAME"].ToString(); col.PropertyName = CleanUp(col.Name); col.PropertyType = type; col.CustomType = type == null ? row["DATA_TYPE"].ToString().ToLowerInvariant() : null; col.Size = GetDatatypeSize(row["DATA_TYPE"].ToString()); col.Precision = GetDatatypePrecision(row["DATA_TYPE"].ToString()); col.IsNullable = row["IS_NULLABLE"].ToString() == "YES"; col.IsPrimaryKey = row["COLUMN_KEY"].ToString() == "PRI"; col.IsAutoIncrement = row["extra"].ToString().ToLower().IndexOf("auto_increment") >= 0; item.Columns.Add(col); } } return result; }
// SchemaReader.ReadSchema public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory) { var result=new Tables(); this._connection=connection; this._factory=factory; var cmd=this._factory.CreateCommand(); cmd.Connection=connection; cmd.CommandText=TABLE_SQL; cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null); //pull the tables in a reader using(cmd) { using (var rdr=cmd.ExecuteReader()) { while(rdr.Read()) { Table tbl=new Table(); tbl.Name=rdr["TABLE_NAME"].ToString(); tbl.Schema = rdr["TABLE_SCHEMA"].ToString(); tbl.IsView=String.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0; tbl.CleanName=CleanUp(tbl.Name); tbl.ClassName=Inflector.MakeSingular(tbl.CleanName); result.Add(tbl); } } } foreach (var tbl in result) { tbl.Columns=this.LoadColumns(tbl); tbl.Indexes = this.LoadIndices(tbl.Name); tbl.ForeignKeys = this.LoadFKeys(tbl.Name); // Mark the primary key string PrimaryKey=this.GetPK(tbl.Name); var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim()); if(pkColumn!=null) pkColumn.IsPrimaryKey=true; } return result; }
List<Column> LoadColumns(Table tbl) { using (var cmd=this._factory.CreateCommand()) { cmd.Connection=this._connection; cmd.CommandText=COLUMN_SQL; cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null); var p = cmd.CreateParameter(); p.ParameterName = ":tableName"; p.Value=tbl.Name; cmd.Parameters.Add(p); var result=new List<Column>(); using (IDataReader rdr=cmd.ExecuteReader()) { while(rdr.Read()) { var type = this.GetPropertyType(rdr["DataType"].ToString(), (rdr["DataType"] == DBNull.Value ? null : rdr["DataType"].ToString())); Column col=new Column(); col.Name=rdr["ColumnName"].ToString(); col.PropertyName = CleanUp(col.Name); col.PropertyType = type; col.CustomType = type == null ? rdr["DataType"].ToString().ToLowerInvariant() : null; col.Size=GetDatatypeSize(rdr["DataType"].ToString()); col.Precision=GetDatatypePrecision(rdr["DataType"].ToString()); col.IsNullable=rdr["IsNullable"].ToString()=="YES"; col.IsAutoIncrement=false; result.Add(col); } } return result; } }
List<Column> LoadColumns(Table tbl) { using (var cmd=this._factory.CreateCommand()) { cmd.Connection=this._connection; cmd.CommandText=COLUMN_SQL; var p = cmd.CreateParameter(); p.ParameterName = "@tableName"; p.Value=tbl.Name; cmd.Parameters.Add(p); var result=new List<Column>(); using (IDataReader rdr=cmd.ExecuteReader()) { while(rdr.Read()) { var type = GetPropertyType(rdr["udt_name"].ToString()); Column col=new Column(); col.Name=rdr["column_name"].ToString(); col.PropertyName=CleanUp(col.Name); col.PropertyType = type; col.CustomType = type == null ? rdr["udt_name"].ToString().ToLowerInvariant() : null; col.Size=GetDatatypeSize(rdr["udt_name"].ToString()); col.Precision=GetDatatypePrecision(rdr["udt_name"].ToString()); col.IsNullable=rdr["is_nullable"].ToString()=="YES"; col.IsAutoIncrement = rdr["column_default"].ToString().StartsWith("nextval("); result.Add(col); } } return result; } }
List<Column> LoadColumns(Table tbl) { using (var cmd=this._factory.CreateCommand()) { cmd.Connection=this._connection; cmd.CommandText=String.Format(COLUMN_SQL,tbl.Name); var result=new List<Column>(); using (IDataReader rdr=cmd.ExecuteReader()) { while(rdr.Read()) { var type=this.GetPropertyType(rdr["type"].ToString(), (rdr["type"] == DBNull.Value ? null : rdr["type"].ToString())); Column col=new Column(); col.Name=rdr["name"].ToString(); col.PropertyName=CleanUp(col.Name); col.PropertyType= type; col.CustomType = type == null ? rdr["type"].ToString().ToLowerInvariant() : null; col.Size=GetDatatypeSize(rdr["type"].ToString()); col.Precision=GetDatatypePrecision(rdr["type"].ToString()); col.IsNullable=rdr["notnull"].ToString()=="0"; col.IsAutoIncrement=false; col.IsPrimaryKey=rdr["pk"].ToString()!="0"; if (col.IsPrimaryKey) col.IsAutoIncrement = tbl.SQL.ToUpper().Contains("AUTOINCREMENT"); else col.IsAutoIncrement = false; col.DefaultValue = rdr["dflt_value"] == DBNull.Value ? null : rdr["dflt_value"].ToString(); result.Add(col); } } return result; } }