public string Compare(IPrimaryKeys pk) { var dt1 = new TableReader(schema1.TableName).Table; var dt2 = new TableReader(schema2.TableName).Table; return Compare(pk, dt1, dt2); }
public static string CREATE_TABLE(string fields, IPrimaryKeys primary, IForeignKeys foreign = null) { string primaryKey = string.Empty; if (primary.Length > 0) { primaryKey = string.Format("\tPRIMARY KEY({0})", string.Join(",", primary.Keys.Select(key => $"[{key}]"))); } string foreignKey = string.Empty; if (foreign != null && foreign.Length > 0) { foreignKey = string.Join(Environment.NewLine, foreign.Keys.Select(key => $"\tFOREIGN KEY ([{key.FK_Column}]) REFERENCES [{key.PK_Table}]([{key.PK_Column}])")); } StringBuilder x = new StringBuilder("CREATE TABLE {0}"); x.AppendLine("("); x.AppendLine(fields); if (primaryKey != string.Empty) { x.AppendLine(primaryKey); } if (foreignKey != string.Empty) { x.AppendLine(foreignKey); } x.AppendLine(")"); string SQL = x.ToString(); return(SQL); }
public string Compare(IPrimaryKeys pk) { var dt1 = new TableReader(schema1.TableName).Table; var dt2 = new TableReader(schema2.TableName).Table; return(Compare(pk, dt1, dt2)); }
public void UpdatePrimary(IPrimaryKeys primary) { var columns = this.Where(column => Array.IndexOf <string>(primary.Keys, column.ColumnName) >= 0); foreach (ColumnSchema column in columns) { column.IsPrimary = true; } }
/// <summary> /// use default locator to save records into database, primary keys must be defined /// </summary> /// <param name="tableName"></param> public TableWriter(TableName tableName) { this.schema = tableName.GetTableSchema(); IPrimaryKeys primary = schema.PrimaryKeys; if (primary.Length != 0) { this.locator = new Locator(primary); } }
public string DELETE(DataRow row, IPrimaryKeys primaryKey) { var L1 = new List <ColumnPair>(); foreach (var column in primaryKey.Keys) { L1.Add(new ColumnPair(column, row[column])); } return(template.Delete(string.Join <ColumnPair>(" AND ", L1))); }
private string Compare(IPrimaryKeys pk, DataTable table1, DataTable table2) { this.PkColumns = pk; this.compareColumns = table1.Columns .OfType <DataColumn>() .Select(row => row.ColumnName) .Except(PkColumns.Keys) .Except(ExceptColumns) .ToArray(); StringBuilder builder = new StringBuilder(); TableDataClause script = new TableDataClause(schema2); List <DataRow> R2 = new List <DataRow>(); foreach (DataRow row1 in table1.Rows) { var row2 = table2.AsEnumerable().Where(row => RowCompare.Compare(PkColumns.Keys, row, row1)).FirstOrDefault(); if (row2 != null) { if (!RowCompare.Compare(compareColumns, row1, row2)) { var compare = new RowCompare(this, row1, row2); builder.AppendLine(compare.UPDATE(schema2.TableName)); } R2.Add(row2); } else { builder.Append(script.INSERT(new ColumnPairCollection(row1))); builder.AppendLine(); } } if (SideType != CompareSideType.copy) { foreach (DataRow row2 in table2.Rows) { if (R2.IndexOf(row2) < 0) { builder.AppendLine(script.DELETE(row2, pk)); } } } if (builder.ToString() != string.Empty && SideType == CompareSideType.compare) { builder.AppendLine(SqlScript.GO); } return(builder.ToString()); }
private string Compare(IPrimaryKeys pk, DataTable table1, DataTable table2) { this.PkColumns = pk; this.compareColumns = table1.Columns .OfType<DataColumn>() .Select(row => row.ColumnName) .Except(PkColumns.Keys) .Except(ExceptColumns) .ToArray(); StringBuilder builder = new StringBuilder(); TableClause script = new TableClause(schema1); List<DataRow> R2 = new List<DataRow>(); foreach (DataRow row1 in table1.Rows) { var row2 = table2.AsEnumerable().Where(row => RowCompare.Compare(PkColumns.Keys, row, row1)).FirstOrDefault(); if (row2 != null) { if (!RowCompare.Compare(compareColumns, row1, row2)) { var compare = new RowCompare(this, row1, row2); builder.AppendLine(script.UPDATE(compare)); } R2.Add(row2); } else { builder.Append(script.INSERT(row1)); builder.AppendLine(); } } if (SideType != CompareSideType.copy) { foreach (DataRow row2 in table2.Rows) { if (R2.IndexOf(row2) < 0) { builder.AppendLine(script.DELETE(row2, pk)); } } } if (builder.ToString() != string.Empty && SideType == CompareSideType.compare) builder.AppendLine(TableClause.GO); return builder.ToString(); }
public static string CREATE_TABLE(string fields, IPrimaryKeys primary) { string primaryKey = ""; if (primary.Length > 0) primaryKey = string.Format("\tPRIMARY KEY({0})", string.Join(",", primary.Keys.Select(key => string.Format("[{0}]", key)))); string SQL = @" CREATE TABLE {0} ( {1} {2} ) "; return string.Format(SQL, "{0}", fields, primaryKey); }
public void UpdatePrimaryIdentity(IPrimaryKeys primary, IIdentityKeys identity) { foreach (string key in identity.ColumnNames) { DataField field = FindField(key); if (field != null) { field.Identity = true; } } foreach (string key in primary.Keys) { DataField field = FindField(key); if (field != null) { field.Primary = true; } } }
public static DataColumn[] PrimaryKeys(this DataTable dt, IPrimaryKeys keys) { return(PrimaryKeys(dt, keys.Keys)); }
public string ADD_PRIMARY_KEY(IPrimaryKeys primaryKey) { return(template.AddPrimaryKey(string.Join(",", primaryKey.Keys))); }
public string DELETE(DataRow row, IPrimaryKeys primaryKey) { var L1 = new List<ColumnPair>(); foreach (var column in primaryKey.Keys) { L1.Add(new ColumnPair(column, row[column])); } return string.Format(deleteCommandTemplate, string.Join<ColumnPair>(" AND ", L1)); }
public string ADD_PRIMARY_KEY(IPrimaryKeys primaryKey) { return string.Format("ALTER TABLE {0} ADD PRIMARY KEY ({1})", tableName.FormalName, string.Join(",", primaryKey.Keys)); }
public Locator(IPrimaryKeys primary) : this(primary.Keys) { }
public string DROP_PRIMARY_KEY(IPrimaryKeys primaryKey) { return(template.DropPrimaryKey(primaryKey.ConstraintName)); }
public string DROP_PRIMARY_KEY(IPrimaryKeys primaryKey) { return string.Format("ALTER TABLE {0} DROP CONSTRAINT ({1})", tableName.FormalName, primaryKey.ConstraintName); }