private string createSQL(TableName tname, string var, VAL val, string keys, string values) { string colKey = cmd.GetValue("key") ?? "Key"; string colValue = cmd.GetValue("value") ?? "Value"; string _colKey = $"[{colKey}]"; string _colValue = $"[{colValue}]"; string _tname = tname.ShortName; string _var = $"'{var}'"; string _val = $"'{val.ToString()}'"; string sql = $@"IF EXISTS(SELECT * FROM {_tname} WHERE {_colKey} = {_var}) UPDATE {_tname} SET {_colValue} = {_val} WHERE {_colKey}={_var} ELSE INSERT {_tname} ({_colKey},{_colValue}{keys}) VALUES({_var},{_val}{values}) "; return(sql); }
private static void Assign(ApplicationCommand cmd, IEnumerable <string> values) { string vname = cmd.GetValue("let"); if (vname == null) { return; } if (vname == string.Empty) { cerr.WriteLine("undefined variable name"); return; } string[] A = values.ToArray(); Context.DS.Add(vname, new VAL(A)); }
public TreeNode <IDataPath> TryAddWhereOrColumns(TreeNode <IDataPath> pt, ApplicationCommand cmd) { if (!(pt.Item is Locator) && !(pt.Item is TableName)) { return(pt); } if (string.IsNullOrEmpty(cmd.arg1)) { cerr.WriteLine("argument cannot be empty"); } TableName tname = GetCurrentPath <TableName>(); var locator = new Locator(cmd.arg1) { Name = cmd.GetValue("name") }; if (locator.Name == null) { locator.Name = $"filter{pt.Nodes.Count + 1}"; } var builder = new SqlBuilder().SELECT().TOP(1).COLUMNS().FROM(tname).WHERE(locator); if (builder.Invalid()) { cerr.WriteLine($"invalid path: {cmd.arg1}"); return(pt); } var xnode = new TreeNode <IDataPath>(locator); pt.Nodes.Add(xnode); return(xnode); }
public void ExportDataContract(int version) { bool last = cmd.Has("last"); List <TableNameData> list = new List <TableNameData>(); if (last) { DataSet ds = ShellHistory.LastDataSet(); if (ds != null) { string[] items = new string[] { }; string clss = cmd.GetValue("class"); if (clss != null) { items = clss.Split(','); } int i = 0; foreach (DataTable dt in ds.Tables) { list.Add(new TableNameData { Data = dt }); if (i < items.Length) { dt.TableName = items[i]; } i++; } } } else if (tname != null) { var dt = FillTable(tname); list.Add(new TableNameData { Name = tname, Data = dt }); } else if (dname != null) { TableName[] tnames = getTableNames(cmd); foreach (var tn in tnames) { var dt = FillTable(tn); list.Add(new TableNameData { Name = tn, Data = dt }); } } else { cerr.WriteLine("data table cannot find, use command type or select first"); return; } foreach (TableNameData dt in list) { ExportDataContractClass(version, dt); } }
private void ImportResourceData() { string file_name = cmd.InputPath(); ResourceFormat format = cmd.GetEnum("format", ResourceFormat.resx); string schema_name = cmd.GetValue("schema-name") ?? SchemaName.dbo; string table_name = cmd.GetValue("table-name"); string name_column = cmd.GetValue("name-column") ?? "name"; string value_column = cmd.GetValue("value-column") ?? name_column; string order_column = cmd.GetValue("order-column"); bool trim_name = cmd.Has("trim-name"); bool trim_value = cmd.Has("trim-value"); bool deleteRowNotInResource = cmd.Has("delete-rows-not-in-resource-file"); if (file_name == null) { cerr.WriteLine($"file name is not defined, use option /in:file_name"); return; } if (!File.Exists(file_name)) { cerr.WriteLine($"file doesn't exist: \"{file_name}\""); return; } if (tname == null) { if (table_name == null) { cerr.WriteLine($"/table-name is not defined"); return; } if (dname == null) { cerr.WriteLine($"required to select a database"); return; } tname = new TableName(dname, schema_name, table_name); if (!tname.Exists()) { cerr.WriteLine($"table-name doesn't exist: {tname}"); return; } } DataTable dt = new TableReader(tname) { CaseSensitive = true, }.Table; if (!ValidateColumn <string>(dt, name_column, "name-column", required: true)) { return; } if (!ValidateColumn <string>(dt, value_column, "value-column", required: true)) { return; } if (!ValidateColumn <int>(dt, order_column, "order-column", required: false)) { return; } cout.WriteLine($"{dt.Rows.Count} of entries on \"{file_name}\""); ResourceTableWriter writer = new ResourceTableWriter(file_name, tname, name_column, value_column, order_column); List <ResourceEntry> entries = writer.Differ(format, dt, trim_name, trim_value); foreach (var entry in entries) { switch (entry.Action) { case DataRowAction.Add: cout.WriteLine($"new entry: \"{entry.Name}\", \"{entry.NewValue}\""); break; case DataRowAction.Change: cout.WriteLine($"update entry: \"{entry.Name}\", \"{entry.OldValue}\" -> \"{entry.NewValue}\""); break; case DataRowAction.Delete: cout.WriteLine($"delete entry: \"{entry.Name}\""); break; } } if (entries.Count > 0) { cout.WriteLine($"{entries.Count} of entries were changed"); } else { cout.WriteLine($"no entry is changed"); } if (entries.Count == 0) { return; } bool commit = cmd.Has("submit-changes"); if (!commit) { return; } cout.WriteLine($"starting to save changes into table \"{tname}\""); try { writer.SubmitChanges(entries, deleteRowNotInResource); cout.WriteLine($"completed to save on table \"{tname}\" from \"{file_name}\""); } catch (Exception ex) { cerr.WriteLine($"failed to save in \"{tname}\" , {ex.AllMessages()}"); } }