private static string GetTestValue(Column column)
{
if (DataTypeMapper.IsNumericType(column.MappedDataType))
{
return("1");
}
if (column.MappedDataType == typeof(string))
{
return($"\"{column.Name}\"");
}
if (column.MappedDataType.IsEnum)
{
return(Enum.GetValues(column.MappedDataType).GetValue(0).ToString());
}
if (column.MappedDataType == typeof(DateTime))
{
return("DateTime.Now");
}
if (column.MappedDataType == typeof(bool))
{
return("true");
}
if (column.MappedDataType == typeof(byte))
{
return("0");
}
return(null);
}
public List <Column> GetTableDetails(Table table, string owner)
{
var columns = new List <Column>();
using (var sqlCon = new OleDbConnection(_connectionStr))
{
try
{
using (var tableDetailsCommand = sqlCon.CreateCommand())
{
var sqlText = @"SELECT
[default] default_value, c.column_name columnName,
d.domain_name DataTypeName, width ColumnSize, scale columnPrecision,
convert(bit, CASE WHEN pkey = 'Y' THEN 1 ELSE 0 END) IsKey,
convert(bit, CASE WHEN c.[nulls] = 'Y' THEN 1 ELSE 0 END) AllowDBNull,
convert(bit, CASE WHEN c.[default] = 'AUTOINCREMENT' THEN 1 ELSE 0 END) IsIdentity,
convert(bit, CASE WHEN c.[default] = 'NEWID()' THEN 1 ELSE 0 END) IsGenerated,
convert(bit, CASE WHEN fcol.foreign_column_id is null THEN 0 ELSE 1 END) IsForeignKey,
ISNULL(fk.[role],'') ConstraintName
FROM sys.syscolumn c
inner join sys.systable t on t.table_id = c.table_id
inner join sys.sysdomain d on c.domain_id = d.domain_id
inner join sysobjects o on t.object_id = o.id
inner join sys.sysuser u on u.user_id = o.uid
left join sysfkcol fcol ON c.table_id = fcol.foreign_table_id and c.column_id = fcol.foreign_column_id
left join sysforeignkey fk ON fcol.foreign_table_id = fk.foreign_table_id AND fcol.foreign_key_id = fk.foreign_key_id
WHERE t.table_name = '{0}'
and u.user_name = '{1}'
ORDER BY c.Column_id";
tableDetailsCommand.CommandText =
string.Format(sqlText, table.Name, owner);
sqlCon.Open();
var dr = tableDetailsCommand.ExecuteReader(CommandBehavior.Default);
var m = new DataTypeMapper();
while (dr.Read())
{
var name = dr["columnName"].ToString();
var isNullable = (bool)dr["AllowDBNull"];
var isPrimaryKey = dr["IsKey"] as bool?;
var isForeignKey = (bool)dr["IsForeignKey"];
var dataType = dr["DataTypeName"].ToString();
var dataLength = Convert.ToInt32(dr["columnSize"]);
var dataPrecision = Convert.ToInt32(dr["columnSize"]);
var dataScale = Convert.ToInt32(dr["columnPrecision"]);
var isIdentity = dr["IsIdentity"] as bool?;
var constraintName = dr["ConstraintName"].ToString();
var isUnique = false; //(bool) dr["IsKey"];
var mappedType = m.MapFromDBType(ServerType.Sybase, dataType, dataLength, dataPrecision,
dataScale);
if (DataTypeMapper.IsNumericType(mappedType))
{
dataLength = 0;
}
else
{
dataScale = 0;
dataPrecision = 0;
}
columns.Add(
new Column
{
Name = name,
IsNullable = isNullable,
IsPrimaryKey = isPrimaryKey.GetValueOrDefault(),
IsForeignKey = isForeignKey,
MappedDataType = mappedType.ToString(),
DataLength = dataLength,
DataScale = dataScale,
DataPrecision = dataPrecision,
DataType = dataType,
IsUnique = isUnique,
IsIdentity = isIdentity.GetValueOrDefault(),
ConstraintName = constraintName
});
}
dr.Close();
}
table.Owner = owner;
table.Columns = columns;
table.PrimaryKey = DeterminePrimaryKeys(table);
// Need to find the table name associated with the FK
foreach (var c in table.Columns)
{
c.ForeignKeyTableName = GetForeignKeyReferenceTableName(table.Name, c.Name);
}
table.ForeignKeys = DetermineForeignKeyReferences(table);
table.HasManyRelationships = DetermineHasManyRelationships(table);
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
sqlCon.Close();
}
}
return(columns);
}