/// <summary>The get db types.</summary>
/// <param name="connection">The connection.</param>
/// <returns>A dictionary of named <see cref="DbModelType"/> objects supported by the database.</returns>
/// <exception cref="ArgumentNullException">If the <paramref name="connection"/> is null.</exception>
public virtual Dictionary <string, DbModelType> GetDbTypes(DbConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
Dictionary <string, DbModelType> dbTypes = new Dictionary <string, DbModelType>();
DataTable dataTypes = connection.GetSchema("DataTypes");
foreach (DataRow row in dataTypes.Rows)
{
string typeName = SafeGetString(row, "TypeName");
int columnSize = SafeGetInt(row, "ColumnSize");
DbModelType dbType = new DbModelType(typeName, columnSize);
// Some providers may double up on schema info, check first:
if (!dbTypes.ContainsKey(typeName.ToLower()))
{
dbTypes.Add(typeName.ToLower(), dbType);
dbType.CreateFormat = SafeGetString(row, "CreateFormat");
dbType.CreateParameters = SafeGetString(row, "CreateParameters");
dbType.LiteralPrefix = SafeGetString(row, "LiteralPrefix");
dbType.LiteralSuffix = SafeGetString(row, "LiteralSuffix");
dbType.SystemType = Type.GetType(SafeGetString(row, "DataType"));
dbType.ProviderDbType = SafeGetString(row, "ProviderDbType");
}
}
return(dbTypes);
}
/// <summary>Gets the db types for the SQL CE provider.</summary>
/// <param name="connection">The connection (not required).</param>
/// <returns></returns>
public override Dictionary <string, DbModelType> GetDbTypes(DbConnection connection)
{
Dictionary <string, DbModelType> dbTypes = new Dictionary <string, DbModelType>();
string dataTypesSql = "SELECT * FROM information_schema.provider_types";
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = dataTypesSql;
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string typeName = (string)reader["TYPE_NAME"];
int columnSize = Convert.ToInt32(reader["COLUMN_SIZE"]);
DbModelType dbType = new DbModelType(typeName, columnSize);
dbType.CreateParameters = Convert.ToString(reader["CREATE_PARAMS"]);
dbType.LiteralPrefix = Convert.ToString(reader["LITERAL_PREFIX"]);
dbType.LiteralSuffix = Convert.ToString(reader["LITERAL_SUFFIX"]);
dbType.ProviderDbType = Convert.ToString(reader["DATA_TYPE"]);
FixCreateFormat(dbType);
FixMaxLengths(dbType);
AssignSystemTypes(dbType);
dbTypes.Add(typeName, dbType);
}
}
}
return(dbTypes);
}
/// <summary>The get columns for table.</summary>
/// <param name="dbTable">The db table.</param>
/// <param name="schemaTableKeyInfo">The schema table key info.</param>
/// <param name="dbTypes">The db types.</param>
protected virtual void GetColumnsForTable(DbModelTable dbTable, DataTable schemaTableKeyInfo, Dictionary <string, DbModelType> dbTypes)
{
if (schemaTableKeyInfo == null)
{
return;
}
foreach (DataRow columnRow in schemaTableKeyInfo.Rows)
{
if (SafeGetBool(columnRow, "IsHidden"))
{
continue;
}
string columnName = SafeGetString(columnRow, "ColumnName");
string dataType = GetDataTypeNameForColumn(dbTable, schemaTableKeyInfo, columnRow);
// note - need a better work around for columns missing the data type info (e.g. access)
if (string.IsNullOrEmpty(dataType))
{
// try using the "ProviderDbType" to match
string providerDbType = SafeGetString(columnRow, "ProviderType");
foreach (var type in dbTypes.Values)
{
if (type.ProviderDbType == providerDbType)
{
dataType = type.Name;
break;
}
}
}
DbModelType dbType = DbModelType.Create(
dbTypes,
dataType,
SafeGetInt(columnRow, "ColumnSize"),
SafeGetInt(columnRow, "Precision"),
SafeGetInt(columnRow, "Scale"),
SafeGetString(columnRow, "DataType"));
// todo - FK info
DbModelColumn dbColumn = new DbModelColumn
{
Name = columnName,
// Name = MakeSqlFriendly(columnName),
Nullable = SafeGetBool(columnRow, "AllowDBNull"),
IsKey = SafeGetBool(columnRow, "IsKey"),
IsUnique = SafeGetBool(columnRow, "IsUnique"),
IsRowVersion = SafeGetBool(columnRow, "IsRowVersion"),
IsIdentity = SafeGetBool(columnRow, "IsIdentity"),
IsAutoIncrement = SafeGetBool(columnRow, "IsAutoIncrement"),
IsReadOnly = SafeGetBool(columnRow, "IsReadOnly"),
DbType = dbType,
};
dbTable.Add(dbColumn);
}
}
/// <summary>The assign system types.</summary>
/// <param name="dbType">The db type.</param>
private void AssignSystemTypes(DbModelType dbType)
{
switch (dbType.Name.ToLower())
{
case "smallint":
dbType.SystemType = typeof(byte);
break;
case "int":
dbType.SystemType = typeof(int);
break;
case "tinyint":
dbType.SystemType = typeof(byte);
break;
case "bigint":
dbType.SystemType = typeof(long);
break;
case "float":
dbType.SystemType = typeof(double); // yes, float is double ;-)
break;
case "numeric":
case "money":
case "real":
dbType.SystemType = typeof(decimal);
break;
case "bit":
dbType.SystemType = typeof(bool);
break;
case "uniqueidentifier":
dbType.SystemType = typeof(Guid);
break;
case "nvarchar":
case "nchar":
case "ntext":
dbType.SystemType = typeof(string);
break;
case "datetime":
dbType.SystemType = typeof(DateTime);
break;
case "varbinary":
case "binary":
case "image":
case "rowversion":
dbType.SystemType = typeof(byte[]);
break;
}
}
/// <summary>Copies this instance.</summary>
/// <returns>A copy of this instance.</returns>
public DbModelType Copy()
{
DbModelType copy = new DbModelType(Name, Length)
{
CreateFormat = CreateFormat,
CreateParameters = CreateParameters,
LiteralPrefix = LiteralPrefix,
LiteralSuffix = LiteralSuffix,
Precision = Precision,
Scale = Scale,
SystemType = SystemType,
};
return(copy);
}
/// <summary>The fix create format.</summary>
/// <param name="dbType">The db type.</param>
private void FixCreateFormat(DbModelType dbType)
{
switch (dbType.Name.ToLower())
{
case "nchar":
case "nvarchar":
case "binary":
case "varbinary":
dbType.CreateFormat = dbType.Name.ToLower() + "({0})";
break;
case "decimal":
case "numeric":
dbType.CreateFormat = dbType.Name.ToLower() + "({0}, {1})";
break;
}
}
/// <summary>The fix max lengths.</summary>
/// <param name="dbType">The db type.</param>
private void FixMaxLengths(DbModelType dbType)
{
switch (dbType.Name.ToLower())
{
case "nchar":
case "nvarchar":
dbType.Length = MAX_NCHAR_COLUMN_SIZE;
break;
case "ntext":
dbType.Length = MAX_NTEXT_COLUMN_SIZE;
break;
case "binary":
case "varbinary":
dbType.Length = MAX_BINARY_COLUMN_SIZE;
break;
case "image":
dbType.Length = MAX_IMAGE_COLUMN_SIZE;
break;
}
}
// public static DbModelType Create(string name, int length, int precision, int scale, string systemTypeName)
// {
// return Create(null, name, length, precision, scale, systemTypeName);
// }
/// <summary>Creates an instance of <see cref="DbModelType"/> defined by the parameers.</summary>
/// <param name="dbTypes">The db types list, if the <paramref name="name"/> is in the list it is used as a base copy of the type.</param>
/// <param name="name">The name of the type, e.g. "int", "nvarchar" etc.</param>
/// <param name="length">The length of the type.</param>
/// <param name="precision">The precision.</param>
/// <param name="scale">The scale.</param>
/// <param name="systemTypeName">Name of the system type, e.g. "System.String".</param>
/// <returns>An instance of <see cref="DbModelType"/> defined by the parameers</returns>
public static DbModelType Create(Dictionary <string, DbModelType> dbTypes, string name, int length, int precision, int scale, string systemTypeName)
{
DbModelType baseType;
string key = name.ToLower();
if (dbTypes != null && dbTypes.ContainsKey(key))
{
// the type should be here, this is used as a baseline for new instances
baseType = dbTypes[key].Copy();
baseType.Length = length;
}
else
{
baseType = new DbModelType(name, length);
baseType.SystemType = Type.GetType(systemTypeName);
}
baseType.Precision = precision;
baseType.Scale = scale;
return(baseType);
}
/// <summary>Copies this instance.</summary>
/// <returns>A copy of this instance.</returns>
public DbModelType Copy()
{
DbModelType copy = new DbModelType(Name, Length)
{
CreateFormat = CreateFormat,
CreateParameters = CreateParameters,
LiteralPrefix = LiteralPrefix,
LiteralSuffix = LiteralSuffix,
Precision = Precision,
Scale = Scale,
SystemType = SystemType,
};
return copy;
}
public void Summary_renders_a_decimal_8_6()
{
DbModelType dbType = new DbModelType("decimal", 8+6) { Precision = 8, Scale = 6, CreateFormat = "decimal({0}, {1})" };
Assert.That(dbType.Summary, Is.EqualTo("decimal(8, 6)"));
}
public void Summary_renders_a_decimal_as_default()
{
DbModelType dbType = new DbModelType("decimal", 26) { Precision = -1, Scale = -1, CreateFormat = "decimal({0}, {1})" };
Assert.That(dbType.Summary, Is.EqualTo("decimal"));
}
public void Summary_renders_a_the_type_name_if_there_is_no_CreateFormat()
{
DbModelType dbType = new DbModelType("decimal", 26);
Assert.That(dbType.Summary, Is.EqualTo("decimal"));
}
public void Summary_renders_a_varchar_10()
{
DbModelType dbType = new DbModelType("varchar", 10) { CreateFormat = "varchar({0})" };
Assert.That(dbType.Summary, Is.EqualTo("varchar(10)"));
}
/// <summary>Initializes a new instance of the <see cref="DbModelColumn"/> class.</summary>
public DbModelColumn()
{
Nullable = true;
DbType = new DbModelType("varchar", 50);
ObjectType = ObjectTypes.Column;
}
public void Summary_renders_an_int()
{
DbModelType dbType = new DbModelType("int", 4) { CreateFormat = "int" };
Assert.That(dbType.Summary, Is.EqualTo("int"));
}
/// <summary>The fix max lengths.</summary>
/// <param name="dbType">The db type.</param>
private void FixMaxLengths(DbModelType dbType)
{
switch (dbType.Name.ToLower())
{
case "nchar":
case "nvarchar":
dbType.Length = MAX_NCHAR_COLUMN_SIZE;
break;
case "ntext":
dbType.Length = MAX_NTEXT_COLUMN_SIZE;
break;
case "binary":
case "varbinary":
dbType.Length = MAX_BINARY_COLUMN_SIZE;
break;
case "image":
dbType.Length = MAX_IMAGE_COLUMN_SIZE;
break;
}
}
/// <summary>The assign system types.</summary>
/// <param name="dbType">The db type.</param>
private void AssignSystemTypes(DbModelType dbType)
{
switch (dbType.Name.ToLower())
{
case "smallint":
dbType.SystemType = typeof(byte);
break;
case "int":
dbType.SystemType = typeof(int);
break;
case "tinyint":
dbType.SystemType = typeof(byte);
break;
case "bigint":
dbType.SystemType = typeof(long);
break;
case "float":
dbType.SystemType = typeof(double); // yes, float is double ;-)
break;
case "numeric":
case "money":
case "real":
dbType.SystemType = typeof(decimal);
break;
case "bit":
dbType.SystemType = typeof(bool);
break;
case "uniqueidentifier":
dbType.SystemType = typeof(Guid);
break;
case "nvarchar":
case "nchar":
case "ntext":
dbType.SystemType = typeof(string);
break;
case "datetime":
dbType.SystemType = typeof(DateTime);
break;
case "varbinary":
case "binary":
case "image":
case "rowversion":
dbType.SystemType = typeof(byte[]);
break;
}
}
// public static DbModelType Create(string name, int length, int precision, int scale, string systemTypeName)
// {
// return Create(null, name, length, precision, scale, systemTypeName);
// }
/// <summary>Creates an instance of <see cref="DbModelType"/> defined by the parameers.</summary>
/// <param name="dbTypes">The db types list, if the <paramref name="name"/> is in the list it is used as a base copy of the type.</param>
/// <param name="name">The name of the type, e.g. "int", "nvarchar" etc.</param>
/// <param name="length">The length of the type.</param>
/// <param name="precision">The precision.</param>
/// <param name="scale">The scale.</param>
/// <param name="systemTypeName">Name of the system type, e.g. "System.String".</param>
/// <returns>An instance of <see cref="DbModelType"/> defined by the parameers</returns>
public static DbModelType Create(Dictionary<string, DbModelType> dbTypes, string name, int length, int precision, int scale, string systemTypeName)
{
DbModelType baseType;
string key = name.ToLower();
if (dbTypes != null && dbTypes.ContainsKey(key))
{
// the type should be here, this is used as a baseline for new instances
baseType = dbTypes[key].Copy();
baseType.Length = length;
}
else
{
baseType = new DbModelType(name, length);
baseType.SystemType = Type.GetType(systemTypeName);
}
baseType.Precision = precision;
baseType.Scale = scale;
return baseType;
}
public void Summary_renders_uses_CreateFormat_over_name_if_present()
{
DbModelType dbType = new DbModelType("mytype", 100) { CreateFormat = "MyType" };
Assert.That(dbType.Summary, Is.EqualTo("MyType"));
}
/// <summary>The fix create format.</summary>
/// <param name="dbType">The db type.</param>
private void FixCreateFormat(DbModelType dbType)
{
switch (dbType.Name.ToLower())
{
case "nchar":
case "nvarchar":
case "binary":
case "varbinary":
dbType.CreateFormat = dbType.Name.ToLower() + "({0})";
break;
case "decimal":
case "numeric":
dbType.CreateFormat = dbType.Name.ToLower() + "({0}, {1})";
break;
}
}
/// <summary>The get db types.</summary>
/// <param name="connection">The connection.</param>
/// <returns>A dictionary of named <see cref="DbModelType"/> objects supported by the database.</returns>
/// <exception cref="ArgumentNullException">If the <paramref name="connection"/> is null.</exception>
public virtual Dictionary<string, DbModelType> GetDbTypes(DbConnection connection)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
Dictionary<string, DbModelType> dbTypes = new Dictionary<string, DbModelType>();
DataTable dataTypes = connection.GetSchema("DataTypes");
foreach (DataRow row in dataTypes.Rows)
{
string typeName = SafeGetString(row, "TypeName");
int columnSize = SafeGetInt(row, "ColumnSize");
DbModelType dbType = new DbModelType(typeName, columnSize);
if (!dbTypes.ContainsKey(typeName.ToLower()))
{
dbTypes.Add(typeName.ToLower(), dbType);
}
dbType.CreateFormat = SafeGetString(row, "CreateFormat");
dbType.CreateParameters = SafeGetString(row, "CreateParameters");
dbType.LiteralPrefix = SafeGetString(row, "LiteralPrefix");
dbType.LiteralSuffix = SafeGetString(row, "LiteralSuffix");
dbType.SystemType = Type.GetType(SafeGetString(row, "DataType"));
dbType.ProviderDbType = SafeGetString(row, "ProviderDbType");
}
return dbTypes;
}
/// <summary>Gets the db types for the SQL CE provider.</summary>
/// <param name="connection">The connection (not required).</param>
/// <returns></returns>
public override Dictionary<string, DbModelType> GetDbTypes(DbConnection connection)
{
Dictionary<string, DbModelType> dbTypes = new Dictionary<string, DbModelType>();
string dataTypesSql = "SELECT * FROM information_schema.provider_types";
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = dataTypesSql;
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string typeName = (string)reader["TYPE_NAME"];
int columnSize = Convert.ToInt32(reader["COLUMN_SIZE"]);
DbModelType dbType = new DbModelType(typeName, columnSize);
dbType.CreateParameters = Convert.ToString(reader["CREATE_PARAMS"]);
dbType.LiteralPrefix = Convert.ToString(reader["LITERAL_PREFIX"]);
dbType.LiteralSuffix = Convert.ToString(reader["LITERAL_SUFFIX"]);
dbType.ProviderDbType = Convert.ToString(reader["DATA_TYPE"]);
FixCreateFormat(dbType);
FixMaxLengths(dbType);
AssignSystemTypes(dbType);
dbTypes.Add(typeName, dbType);
}
}
}
return dbTypes;
}
/// <summary>Initializes a new instance of the <see cref="DbModelColumn"/> class.</summary>
public DbModelColumn()
{
Nullable = true;
DbType = new DbModelType("varchar", 50);
ObjectType = ObjectTypes.Column;
}
