public void del(Command cmd)
{
if (cmd.HasHelp)
{
stdio.WriteLine("command del or erase: drop tables or delete data rows");
stdio.WriteLine("del tablename : drop table");
stdio.WriteLine("del [sql where clause] : delete current table filtered rows");
stdio.WriteLine("example:");
stdio.WriteLine(@"local> del Northwind\Products : drop table [Products]");
stdio.WriteLine(@"local\Northwind\Products> del : delete all rows of table [Products]");
stdio.WriteLine(@"local\Northwind\Products> del col1=1 and col2='match' : del rows matched on columns:c1 or c2");
return;
}
var pt = mgr.current;
if (!(pt.Item is Locator) && !(pt.Item is TableName))
{
TableName[] T = null;
if (cmd.arg1 != null)
{
PathName path = new PathName(cmd.arg1);
var node = mgr.Navigate(path);
if (node != null)
{
var dname = mgr.GetPathFrom<DatabaseName>(node);
if (dname != null)
{
if (cmd.wildcard != null)
{
var m = new MatchedDatabase(dname, cmd.wildcard, new string[] { });
T = m.MatchedTableNames;
}
else
{
var _tname = mgr.GetPathFrom<TableName>(node);
if (_tname != null)
T = new TableName[] { _tname };
else
{
stdio.ErrorFormat("invalid path");
return;
}
}
}
else
{
stdio.ErrorFormat("database is unavailable");
return;
}
}
else
{
stdio.ErrorFormat("invalid path");
return;
}
}
if (T != null && T.Length > 0)
{
if (!stdio.YesOrNo("are you sure to drop {0} tables (y/n)?", T.Length))
return;
try
{
var sqlcmd = new SqlCmd(T[0].Provider, string.Empty);
sqlcmd.ExecuteNonQueryTransaction(T.Select(row => string.Format("DROP TABLE {0}", row)));
stdio.ErrorFormat("completed to drop table(s):\n{0}", string.Join<TableName>("\n", T));
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
}
}
else
stdio.ErrorFormat("table is not selected");
return;
}
TableName tname = null;
Locator locator = null;
if (pt.Item is Locator)
{
locator = mgr.GetCombinedLocator(pt);
tname = mgr.GetCurrentPath<TableName>();
if (!string.IsNullOrEmpty(cmd.args))
locator.And(new Locator(cmd.args));
}
if (pt.Item is TableName)
{
tname = (TableName)pt.Item;
if (!string.IsNullOrEmpty(cmd.args))
locator = new Locator(cmd.args);
}
if (locator == null)
stdio.Write("are you sure to delete all rows (y/n)?");
else
stdio.Write("are you sure to delete (y/n)?");
if (stdio.ReadKey() != ConsoleKey.Y)
return;
stdio.WriteLine();
try
{
int count;
if (locator == null)
count = new SqlBuilder().DELETE(tname).SqlCmd.ExecuteNonQuery();
else
count = new SqlBuilder().DELETE(tname).WHERE(locator).SqlCmd.ExecuteNonQuery();
stdio.WriteLine("{0} of row(s) affected", count);
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
}
}
public void copy(Command cmd, CompareSideType sideType)
{
if (cmd.HasHelp)
{
if (sideType == CompareSideType.copy)
{
stdio.WriteLine("copy schema or records from table1 to table2, support table name wildcards");
stdio.WriteLine("copy table1 [table2] [/s]");
}
else if (sideType == CompareSideType.sync)
{
stdio.WriteLine("synchronize schema or records from table1 to table2");
stdio.WriteLine("sync table1 [table2] [/s] : sync table1' records to table2");
}
else if (sideType == CompareSideType.compare)
{
stdio.WriteLine("compare schema or records from table1 to table2");
stdio.WriteLine("comp table1 [table2] [/s] : sync table1' records to table2");
}
stdio.WriteLine("support table name wildcards");
stdio.WriteLine("[/s] : table schema, default table records");
return;
}
CancelableWork.CanCancel(cts =>
{
PathBothSide both = new PathBothSide(mgr, cmd);
var dname2 = mgr.GetPathFrom<DatabaseName>(both.ps2.Node);
if (both.ps1.MatchedTables == null)
return;
foreach (var tname1 in both.ps1.MatchedTables)
{
if (cts.IsCancellationRequested)
return;
TableName tname2 = mgr.GetPathFrom<TableName>(both.ps2.Node);
if (tname2 == null)
{
tname2 = new TableName(dname2, tname1.SchemaName, tname1.ShortName);
}
var adapter = new CompareAdapter(both.ps1.side, both.ps2.side);
//stdio.WriteLine("start to {0} from {1} to {2}", sideType, tname1, tname2);
var sql = adapter.CompareTable(cmd.IsSchema ? ActionType.CompareSchema : ActionType.CompareData,
sideType, tname1, tname2, mgr.Configuration.PK, cmd.Columns);
if (sideType == CompareSideType.compare)
{
if (sql == string.Empty)
{
stdio.WriteLine("source {0} and destination {1} are identical", tname1, tname2);
}
continue;
}
if (sql == string.Empty)
{
stdio.WriteLine("nothing changes made on destination {0}", tname2);
}
else
{
bool exists = tname2.Exists();
try
{
var sqlcmd = new SqlCmd(both.ps2.side.Provider, sql);
int count = sqlcmd.ExecuteNonQueryTransaction();
if (exists)
{
if (count >= 0)
stdio.WriteLine("{0} row(s) changed at destination {1}", count, tname2);
else
stdio.WriteLine("command(s) completed successfully at destination {1}", count, tname2);
}
else
stdio.WriteLine("table {0} created at destination", tname2);
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
return;
}
}
} // loop for
return;
});
}
