// 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;
}
public string GetColumnDefaultValue(Column col)
{
string sysType = string.Format("\"{0}\"", col.DefaultValue);
var guid = Guid.Empty;
switch (col.PropertyType)
{
case System.Data.DbType.Byte:
case System.Data.DbType.Currency:
case System.Data.DbType.Decimal:
case System.Data.DbType.Double:
case System.Data.DbType.Boolean:
case System.Data.DbType.Int16:
case System.Data.DbType.Int32:
case System.Data.DbType.Int64:
case System.Data.DbType.Single:
case System.Data.DbType.UInt16:
case System.Data.DbType.UInt32:
case System.Data.DbType.UInt64:
sysType = col.DefaultValue.Replace("'", "").Replace("\"", "").CleanBracket();
break;
case System.Data.DbType.Guid:
if (col.DefaultValue.IsGuid(out guid))
{
if (guid == Guid.Empty)
sysType = "Guid.Empty";
else
sysType = string.Format("new System.Guid(\"{0}\")", guid);
}
break;
case System.Data.DbType.DateTime:
case System.Data.DbType.DateTime2:
case System.Data.DbType.Date:
if (col.DefaultValue.ToLower() == "current_time"
|| col.DefaultValue.ToLower() == "current_date"
|| col.DefaultValue.ToLower() == "current_timestamp")
{
sysType = "SystemMethods.CurrentDateTime";
}
else
{
sysType = "\"" + col.DefaultValue.CleanBracket() + "\"";
}
break;
default:
break;
}
return sysType;
}
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;
}
}
public string GetMigrationTypeFunctionForType(Column col)
{
var size = col.Size;
var precision = col.Precision;
string sizeStr = ((size == -1) ? "" : size.ToString());
string precisionStr = ((precision == -1) ? "" : "," + precision.ToString());
string sysType = "AsString(" + sizeStr + ")";
switch (col.PropertyType)
{
case System.Data.DbType.AnsiString:
sysType = string.Format("AsAnsiString({0})", sizeStr);
break;
case System.Data.DbType.AnsiStringFixedLength:
sysType = string.Format("AsFixedLengthAnsiString({0})", sizeStr);
break;
case System.Data.DbType.Binary:
sysType = string.Format("AsBinary({0})", size == -1 ? "Int32.MaxValue" : sizeStr);
break;
case System.Data.DbType.Boolean:
sysType = "AsBoolean()";
break;
case System.Data.DbType.Byte:
sysType = "AsByte()";
break;
case System.Data.DbType.Currency:
sysType = "AsCurrency()";
break;
case System.Data.DbType.Date:
sysType = "AsDate()";
break;
case System.Data.DbType.DateTime:
sysType = "AsDateTime()";
break;
case System.Data.DbType.Decimal:
sysType = string.Format("AsDecimal({0})", sizeStr + precisionStr);
break;
case System.Data.DbType.Double:
sysType = "AsDouble()";
break;
case System.Data.DbType.Guid:
sysType = "AsGuid()";
break;
case System.Data.DbType.Int16:
case System.Data.DbType.UInt16:
sysType = "AsInt16()";
break;
case System.Data.DbType.Int32:
case System.Data.DbType.UInt32:
sysType = "AsInt32()";
break;
case System.Data.DbType.Int64:
case System.Data.DbType.UInt64:
sysType = "AsInt64()";
break;
case System.Data.DbType.Single:
sysType = "AsFloat()";
break;
case System.Data.DbType.String:
sysType = string.Format("AsString({0})", sizeStr);
break;
case System.Data.DbType.StringFixedLength:
sysType = string.Format("AsFixedLengthString({0})", sizeStr);
break;
case null:
sysType = string.Format("AsCustom({0})", col.CustomType);
break;
default:
break;
}
return sysType;
}
