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