public static TimeSchemaColumn GetTimeSchemaColumn(SchemaTable schemaTable, string columnName, TimeSpan minValue, TimeSpan maxValue, string schemaColumnName = "", int ordinalSequence = -1, string defaultValue = null, bool isNullable = false, bool isRequired = false)
{
TimeSchemaColumn col = new TimeSchemaColumn();
col.MaxValue = maxValue;
col.MinValue = minValue;
InitSchemaColumn(col, schemaTable, columnName, schemaColumnName, ordinalSequence, defaultValue, isNullable, isRequired);
return col;
}
private static void InitSchemaColumn(SchemaColumn col, SchemaTable schemaTable, string columnName, string schemaColumnName = "", int ordinalSequence = -1, string defaultValue = null, bool isNullable = false, bool isRequired = false)
{
col.SchemaTable = schemaTable;
col.ColumnName = columnName;
col.SchemaColumnName = schemaColumnName;
col.OrdinalSequence = ordinalSequence;
col.DefaultValue = defaultValue;
col.IsNullable = isNullable;
col.IsRequired = isRequired;
}
public static DecimalSchemaColumn GetDecimalSchemaColumn(SchemaTable schemaTable, string columnName, byte precision, byte scale, decimal minValue = Decimal.MinValue, decimal maxValue = Decimal.MaxValue, string schemaColumnName = "", int ordinalSequence = -1, string defaultValue = null, bool isNullable = false, bool isRequired = false)
{
DecimalSchemaColumn col = new DecimalSchemaColumn();
col.Precision = precision;
col.Scale = scale;
col.MaxValue = maxValue;
col.MinValue = minValue;
InitSchemaColumn(col, schemaTable, columnName, schemaColumnName, ordinalSequence, defaultValue, isNullable, isRequired);
return col;
}
public SchemaRelation(DbSchema schema, SchemaTable parentTable, SchemaTable childTable, IEnumerable<SchemaColumn> parentColumns, IEnumerable<SchemaColumn> childColumns, string relationName = null, bool isOneToOne = false, bool isRequired = true, bool isReversed = false)
{
Schema = schema;
ParentTable = parentTable;
ChildTable = childTable;
ParentColumns = new ReadOnlyObservableCollection<SchemaColumn>(new ObservableCollection<SchemaColumn>(parentColumns));
ChildColumns = new ReadOnlyObservableCollection<SchemaColumn>(new ObservableCollection<SchemaColumn>(childColumns));
RelationName = String.IsNullOrWhiteSpace(relationName) ? String.Format("{0}_{1}", parentTable.TableName, childTable.TableName) : relationName;
RelationType = isOneToOne ? RelationType.OneToOne : RelationType.OneToMany;
IsRequired = isRequired;
IsReversed = isReversed;
}
public static TextSchemaColumn GetTextSchemaColumn(SchemaTable schemaTable, string columnName, int maxLength, bool isFixedWidth = false, string schemaColumnName = "", int ordinalSequence = -1, string defaultValue = null, bool isNullable = false, bool isRequired = false)
{
TextSchemaColumn col = null;
if (isFixedWidth)
{
col = new FixedWidthTextSchemaColumn();
}
else
{
col = new TextSchemaColumn();
}
col.MaxLength = maxLength;
InitSchemaColumn(col, schemaTable, columnName, schemaColumnName, ordinalSequence, defaultValue, isNullable, isRequired);
return col;
}
public SchemaRelation(DbSchema schema, SchemaTable parentTable, SchemaTable childTable, IEnumerable<SchemaColumn> childColumns, string relationName = null, bool isOneToOne = false, bool isRequired = true, bool isReversed = false)
: this(schema, parentTable, childTable, parentTable.PrimaryKeys, childColumns, relationName, isOneToOne, isRequired, isReversed)
{
}
public string GetPrimaryKeySelectSql(SchemaTable schemaTable, bool isSafe, string companyNum = "")
{
var columns = schemaTable.PrimaryKeys;
var builder = new StringBuilder();
builder.Append("SELECT ");
if (columns != null && columns.Count() > 0)
{
foreach (var schemaCol in columns)
{
TextSchemaColumn textCol = schemaCol as TextSchemaColumn;
if (textCol == null || !isSafe)
{
builder.Append(String.Concat(IdentifierDelimiterOpen, schemaCol.SchemaColumnName, IdentifierDelimiterClose, ", "));
}
else
{
builder.Append(String.Format("SUBSTRING({0}{1}{2}, 1, {3}) AS {0}{1}{2}, ", IdentifierDelimiterOpen, schemaCol.SchemaColumnName, IdentifierDelimiterClose, textCol.MaxLength));
}
}
//
// Remove trailing comma and space
//
builder.Remove(builder.Length - 2, 2);
}
else
{
builder.Append(" *");
}
builder.AppendFormat(" FROM {0}", GetSqlTableName(schemaTable.TableName));
if (schemaTable.Columns.Count(c => c.SchemaColumnName.ToLower() == "company") > 0)
{
//builder.AppendFormat(" FROM {0}{1}", GetSqlTableName(schemaTable.TableName), !string.IsNullOrWhiteSpace(companyNum) ? string.Format(" WHERE company = '{0}'", companyNum) : string.Empty);
builder.Append(!string.IsNullOrWhiteSpace(companyNum) ? string.Format(" WHERE company = '{0}'", companyNum) : string.Empty);
}
return builder.ToString();
}
protected string GetSqlServerCreateTableOpen(SchemaTable schemaTable)
{
//CREATE TABLE [dbo].[JobHead](
return string.Format("CREATE TABLE [dbo].[{0}](", schemaTable.TableName);
}
public string GetWhere(SchemaTable schemaTable, IEnumerable<ICriteriaValue> criteria)
{
var tableName = schemaTable.TableName;
StringBuilder builder = new StringBuilder();
builder.Append(" WHERE ");
foreach (var item in criteria)
{
builder.AppendFormat("{0} = {1} AND ", GetSqlFieldName(tableName, item.SchemaColumn.SchemaColumnName), item.SchemaColumn.FormatLiteral(this, item.Value));
}
//
// Remove trailing " AND "
//
builder.Remove(builder.Length - 5, 5);
return builder.ToString();
}
public string GetSqlServerCreate(SchemaTable schemaTable)
{
var fieldList = new List<string>();
foreach (var schemaColumn in schemaTable.Columns)
{
fieldList.Add(GetSqlServerCreateTableField(schemaColumn));
}
fieldList.Add(string.Format("[{0}RecID] [bigint] IDENTITY(1,1) NOT NULL", schemaTable.TableName));
return string.Format("{0}\r\n{1}\r\n)", GetSqlServerCreateTableOpen(schemaTable),
string.Join(",\r\n", fieldList));
}
public static TimeSchemaColumn GetTimeSchemaColumn(SchemaTable schemaTable, string columnName, string schemaColumnName = "", int ordinalSequence = -1, string defaultValue = null, bool isNullable = false, bool isRequired = false)
{
return GetTimeSchemaColumn(schemaTable, columnName, new TimeSpan(0, 0, 0), new TimeSpan(23, 59, 59), schemaColumnName, ordinalSequence, defaultValue, isNullable, isRequired);
}
public static DateSchemaColumn GetDateSchemaColumn(SchemaTable schemaTable, string columnName, string schemaColumnName = "", int ordinalSequence = -1, string defaultValue = null, bool isNullable = false, bool isRequired = false)
{
return GetDateSchemaColumn(schemaTable, columnName, new DateTime(1900, 1, 1), new DateTime(2199, 12, 31), schemaColumnName, ordinalSequence, defaultValue, isNullable, isRequired);
}
public static BooleanSchemaColumn GetBooleanSchemaColumn(SchemaTable schemaTable, string columnName, string schemaColumnName = "", int ordinalSequence = -1, string defaultValue = null, bool isNullable = false, bool isRequired = false)
{
BooleanSchemaColumn col = new BooleanSchemaColumn();
InitSchemaColumn(col, schemaTable, columnName, schemaColumnName, ordinalSequence, defaultValue, isNullable, isRequired);
return col;
}