// 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;
}
}
