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);
}
public Locator(IPrimaryKeys primary)
: this(primary.Keys)
{
}
