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