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