List<Column> LoadColumns(Table tbl)
{
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_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())
{
Column col=new Column();
col.Name=rdr["ColumnName"].ToString();
col.PropertyName=CleanUp(col.Name);
col.PropertyType=GetPropertyType(rdr["DataType"].ToString(), (rdr["DataType"] == DBNull.Value ? null : rdr["DataType"].ToString()));
col.IsNullable=rdr["IsNullable"].ToString()=="YES";
col.IsAutoIncrement=true;
result.Add(col);
}
}
return result;
}
}
private List<Column> LoadColumns(Table tbl)
{
using (var cmd = _factory.CreateCommand())
{
cmd.Connection = _connection;
cmd.CommandText = COLUMN_SQL;
var p = cmd.CreateParameter();
p.ParameterName = "@tableName";
p.Value = tbl.Name;
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "@schemaName";
p.Value = tbl.Schema;
cmd.Parameters.Add(p);
var result = new List<Column>();
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var col = new Column();
col.Name = rdr["ColumnName"].ToString();
col.PropertyName = CleanUp(col.Name);
col.PropertyType = GetPropertyType(rdr["DataType"].ToString());
col.IsNullable = rdr["IsNullable"].ToString() == "YES";
col.IsAutoIncrement = ((int) rdr["IsIdentity"]) == 1;
result.Add(col);
}
}
return result;
}
}
List<Column> LoadColumns(Table tbl)
{
using (var cmd=_factory.CreateCommand())
{
cmd.Connection=_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())
{
Column col=new Column();
col.Name=rdr["column_name"].ToString();
col.PropertyName=CleanUp(col.Name);
col.PropertyType=GetPropertyType(rdr["udt_name"].ToString());
col.IsNullable=rdr["is_nullable"].ToString()=="YES";
col.IsAutoIncrement = rdr["column_default"].ToString().StartsWith("nextval(");
result.Add(col);
}
}
return result;
}
}
// SchemaReader.ReadSchema
public override TableCollection ReadSchema(DbConnection connection, DbProviderFactory factory)
{
var result=new TableCollection();
var cmd=factory.CreateCommand();
cmd.Connection=connection;
cmd.CommandText=TABLE_SQL;
//pull the TableCollection 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.Instance.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)
{
Column col=new Column();
col.Name=row["COLUMN_NAME"].ToString();
col.PropertyName=CleanUp(col.Name);
col.PropertyType=GetPropertyType(row);
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;
}
public override TableCollection ReadSchema(DbConnection connection, DbProviderFactory factory)
{
var result = new TableCollection();
_connection = connection;
_factory = factory;
var cmd = _factory.CreateCommand();
cmd.Connection = connection;
cmd.CommandText = TABLE_SQL;
//pull the TableCollection in a reader
using (cmd)
{
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var tbl = new Table();
tbl.Name = rdr["TABLE_NAME"].ToString();
tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
tbl.IsView =
String.Compare(rdr["TABLE_TYPE"].ToString(), "View", StringComparison.OrdinalIgnoreCase) ==
0;
tbl.CleanName = CleanUp(tbl.Name);
tbl.ClassName = Inflector.Instance.MakeSingular(tbl.CleanName);
result.Add(tbl);
}
}
}
foreach (var tbl in result)
{
tbl.Columns = LoadColumns(tbl);
// Mark the primary key
var primaryKey = GetPrimaryKey(tbl.Name);
var pkColumn = tbl.Columns.SingleOrDefault(x => x.Name.ToLower().Trim() == primaryKey.ToLower().Trim());
if (pkColumn != null)
{
pkColumn.IsPrimaryKey = true;
}
}
return result;
}