public BaseRowAdapter(TableName tname, Locator locator)
{
this.columns = new ColumnAdapterCollection();
this.fields = new DataFieldCollection();
this.tableName = tname;
this.locator = locator;
}
/// <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 TableAdapter(DataTable dataTable, TableName tableName, Locator locator)
{
this.dataTable = dataTable;
this.tableName = tableName;
this.locator = locator;
this.columns = new ColumnAdapterCollection();
this.fields = new DataFieldCollection();
}
protected void UpdateWhere(Locator where)
{
this.locator = where;
}
public static TableAdapter WriteDataTable(DataTable dataTable,
TableName tname, Locator locator,
string[] columnNames,
RowChangedHandler rowChangedHandler, ValueChangedHandler columnHandler)
{
if (dataTable == null)
return null;
if (locator == null)
locator = new Locator(tname);
TableAdapter adapter = new TableAdapter(dataTable, tname, locator);
if(rowChangedHandler != null)
adapter.DataRowChangedHandler += rowChangedHandler;
if (columnHandler != null)
adapter.ValueChangedHandler = columnHandler;
adapter.AddFields(columnNames);
adapter.Save();
return adapter;
}
/// <summary>
/// use user defined locator to save records into database
/// </summary>
/// <param name="tableName"></param>
/// <param name="locator"></param>
public TableWriter(TableName tableName, Locator locator)
{
this.schema = tableName.GetTableSchema();
this.locator = locator;
}
public void let(Command cmd)
{
if (cmd.HasHelp)
{
stdio.WriteLine("let assignment : update key-value table row, key-value table must be defined on the sqlcon.cfg or user.cfg");
stdio.WriteLine("let key=value : update column by current table or locator");
stdio.WriteLine("example:");
stdio.WriteLine("let Smtp.Host=\"127.0.0.1\" : update key-value row, it's equivalent to UPDATE table SET [Value]='\"127.0.0.1\"' WHERE [Key]='Smtp.Host'");
return;
}
if (string.IsNullOrEmpty(cmd.args))
{
stdio.ErrorFormat("argument cannot be empty");
return;
}
var pt = mgr.current;
if (!(pt.Item is Locator) && !(pt.Item is TableName))
{
stdio.ErrorFormat("table is not selected");
return;
}
if (this.mgr.Configuration.dictionarytables.Count == 0)
{
stdio.ErrorFormat("key-value tables is undefined");
return;
}
TableName tname = mgr.GetCurrentPath<TableName>();
var setting = this.mgr.Configuration.dictionarytables.FirstOrDefault(row => row.TableName.ToUpper() == tname.Name.ToUpper());
if (setting == null)
{
stdio.ErrorFormat("current table is not key-value tables");
return;
}
string[] kvp = cmd.args.Split('=');
string key = null;
string value = null;
if (kvp.Length == 1)
{
key = kvp[0].Trim();
}
else if (kvp.Length == 2)
{
key = kvp[0].Trim();
value = kvp[1].Trim();
}
if (string.IsNullOrEmpty(key))
{
stdio.ErrorFormat("invalid assignment");
return;
}
Locator locator = new Locator(setting.KeyName.ColumnName() == key);
SqlBuilder builder = new SqlBuilder().SELECT.COLUMNS(setting.ValueName.ColumnName()).FROM(tname).WHERE(locator);
var L = new SqlCmd(builder).FillDataColumn<string>(0);
if (L.Count() == 0)
{
stdio.ErrorFormat("undefined key: {0}", key);
return;
}
if (kvp.Length == 1)
{
stdio.ErrorFormat("{0} = {1}", key, L.First());
return;
}
builder = new SqlBuilder()
.UPDATE(tname)
.SET(setting.ValueName.ColumnName() == value)
.WHERE(locator);
try
{
int count = builder.SqlCmd.ExecuteNonQuery();
stdio.WriteLine("{0} of row(s) affected", count);
}
catch (Exception ex)
{
stdio.ErrorFormat(ex.Message);
}
}
public bool Display(Command cmd)
{
SqlBuilder builder;
int top = cmd.top;
string[] columns = cmd.Columns;
if (cmd.wildcard != null)
{
string where = LikeExpr(cmd.wildcard, cmd.Columns);
builder = new SqlBuilder().SELECT.ROWID(cmd.HasRowId).COLUMNS().FROM(tname).WHERE(where);
}
else if (cmd.where != null)
{
var locator = new Locator(cmd.where);
builder = new SqlBuilder().SELECT.TOP(top).ROWID(cmd.HasRowId).COLUMNS(columns).FROM(tname).WHERE(locator);
}
else if (cmd.Has("dup"))
{
DuplicatedTable dup = new DuplicatedTable(tname, columns);
if (dup.group.Rows.Count == 0)
{
stdio.WriteLine("no duplicated record found");
return true;
}
if (cmd.IsSchema)
{
Display(cmd, dup.group, 0);
}
else
{
dup.Dispaly(dt => Display(cmd, dt, 0));
}
return true;
}
else
builder = new SqlBuilder().SELECT.TOP(top).ROWID(cmd.HasRowId).COLUMNS(columns).FROM(tname);
return Display(cmd, builder, tname, top);
}
public LocatorAttribute(string any)
{
this.locator = new Locator(any);
this.locator.Unique = this.Unique;
}
public Locator(Locator locator)
{
this.where = locator.where;
}
public bool Display(Command cmd, string columns, Locator locator)
{
SqlBuilder builder;
if (cmd.wildcard == null)
{
builder = new SqlBuilder().SELECT.TOP(cmd.top).COLUMNS(columns).FROM(tname);
if (locator != null)
builder.WHERE(locator);
}
else
{
string where = LikeExpr(cmd.wildcard, cmd.Columns);
if (locator != null)
where = string.Format("({0}) AND ({1})", locator.Path, where);
builder = new SqlBuilder().SELECT.COLUMNS(columns).FROM(tname).WHERE(where);
}
return Display(cmd, builder, tname, cmd.top);
}
public void Or(Locator locator)
{
this.where = string.Format("({0}) OR ({1})", this.where, locator.where);
}
public void And(Locator locator)
{
this.where = string.Format("({0}) AND ({1})", this.where, locator.where);
}
public TableReader(TableName tableName, Locator locator)
: this(tableName, locator.Path.Inject())
{
}
public RowAdapter(TableName tname, Locator locator, DataRow dataRow)
: base(tname, locator)
{
this.dataRow = dataRow;
//UpdateColumnValue(dataRow); //columns.Count == 0 in this moment
}
public LocatorAttribute(string[] columns)
{
this.locator = new Locator(columns);
this.locator.Unique = this.Unique;
}
//used for PersistentTable
internal RowAdapter(DataFieldCollection fields, ColumnAdapterCollection columns, TableName tname, Locator locator)
: base(tname, locator)
{
this.columns = columns;
this.fields = fields;
}
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);
}
}