public void NomalTest()
{
var dt = new DbTable
{
Columns = new[] { "Id", "Name", "CreateTime" },
Rows = new List <Object[]>
{
new Object[] { 123, "Stone", DateTime.Now },
new Object[] { 456, "NewLife", DateTime.Today }
}
};
Assert.Equal(123, dt.Get <Int32>(0, "Id"));
Assert.Equal(456, dt.Get <Int32>(1, "ID"));
Assert.Equal("NewLife", dt.Get <String>(1, "Name"));
Assert.Equal(DateTime.Today, dt.Get <DateTime>(1, "CreateTime"));
// 不存在的字段
Assert.Equal(DateTime.MinValue, dt.Get <DateTime>(0, "Time"));
Assert.False(dt.TryGet <DateTime>(1, "Time", out var time));
var idx = dt.GetColumn("Name");
Assert.Equal(1, idx);
idx = dt.GetColumn("Time");
Assert.Equal(-1, idx);
// 迭代
var i = 0;
foreach (var row in dt)
{
if (i == 0)
{
Assert.Equal(123, row["ID"]);
Assert.Equal("Stone", row["name"]);
}
else if (i == 1)
{
Assert.Equal(456, row["ID"]);
Assert.Equal("NewLife", row["name"]);
Assert.Equal(DateTime.Today, row["CreateTime"]);
}
i++;
}
}
protected static DbTable Select(DbTable ds, String name, Object value)
{
var list = new List <Object[]>();
var col = ds.GetColumn(name);
if (col >= 0)
{
for (var i = 0; i < ds.Rows.Count; i++)
{
var dr = ds.Rows[i];
if (Equals(dr[col], value))
{
list.Add(dr);
}
}
}
var ds2 = new DbTable
{
Columns = ds.Columns,
Types = ds.Types,
Rows = list
};
return(ds2);
}
/*
* insert into stat (siteid,statdate,`count`,cost,createtime,updatetime) values
* (1,'2018-08-11 09:34:00',1,123,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now()),
* (3,'2018-08-11 09:34:00',1,789,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now())
* on duplicate key update
* `count`=`count`+values(`count`),cost=cost+values(cost),
* updatetime=values(updatetime);
*/
private String GetBatchSql(IDataTable table, IDataColumn[] columns, ICollection <String> updateColumns, ICollection <String> addColumns, IEnumerable <IIndexAccessor> list)
{
var sb = Pool.StringBuilder.Get();
var db = Database as DbBase;
// 字段列表
//if (columns == null) columns = table.Columns.ToArray();
sb.AppendFormat("Insert Into {0}(", db.FormatName(table));
foreach (var dc in columns)
{
//if (dc.Identity) continue;
sb.Append(db.FormatName(dc));
sb.Append(",");
}
sb.Length--;
sb.Append(")");
// 值列表
sb.Append(" Values");
// 优化支持DbTable
if (list.FirstOrDefault() is DbRow)
{
// 提前把列名转为索引，然后根据索引找数据
DbTable dt = null;
Int32[] ids = null;
foreach (DbRow dr in list)
{
if (dr.Table != dt)
{
dt = dr.Table;
var cs = new List <Int32>();
foreach (var dc in columns)
{
//if (dc.Identity)
// cs.Add(0);
//else
cs.Add(dt.GetColumn(dc.ColumnName));
}
ids = cs.ToArray();
}
sb.Append("(");
var row = dt.Rows[dr.Index];
for (var i = 0; i < columns.Length; i++)
{
var dc = columns[i];
//if (dc.Identity) continue;
var value = row[ids[i]];
sb.Append(db.FormatValue(dc, value));
sb.Append(",");
}
sb.Length--;
sb.Append("),");
}
}
else
{
foreach (var entity in list)
{
sb.Append("(");
foreach (var dc in columns)
{
//if (dc.Identity) continue;
var value = entity[dc.Name];
sb.Append(db.FormatValue(dc, value));
sb.Append(",");
}
sb.Length--;
sb.Append("),");
}
}
sb.Length--;
// 重复键执行update
if (updateColumns != null || addColumns != null)
{
sb.Append(" On Conflict");
// 先找唯一索引，再用主键
//var table = columns.FirstOrDefault()?.Table;
var di = table.Indexes?.FirstOrDefault(e => e.Unique);
if (di != null && di.Columns != null && di.Columns.Length > 0)
{
var dcs = table.GetColumns(di.Columns);
sb.AppendFormat("({0})", dcs.Join(",", e => db.FormatName(e)));
}
else
{
var pks = table.PrimaryKeys;
if (pks != null && pks.Length > 0)
{
sb.AppendFormat("({0})", pks.Join(",", e => db.FormatName(e)));
}
}
sb.Append(" Do Update Set ");
if (updateColumns != null)
{
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (updateColumns.Contains(dc.Name) && (addColumns == null || !addColumns.Contains(dc.Name)))
{
sb.AppendFormat("{0}=excluded.{0},", db.FormatName(dc));
}
}
sb.Length--;
}
if (addColumns != null)
{
sb.Append(",");
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (addColumns.Contains(dc.Name))
{
sb.AppendFormat("{0}={0}+excluded.{0},", db.FormatName(dc));
}
}
sb.Length--;
}
}
return(sb.Put(true));
}
/*
* insert into stat (siteid,statdate,`count`,cost,createtime,updatetime) values
* (1,'2018-08-11 09:34:00',1,123,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now()),
* (3,'2018-08-11 09:34:00',1,789,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now())
* on duplicate key update
* `count`=`count`+values(`count`),cost=cost+values(cost),
* updatetime=values(updatetime);
*/
private String GetBatchSql(String tableName, IDataColumn[] columns, ICollection <String> updateColumns, ICollection <String> addColumns, IEnumerable <IIndexAccessor> list)
{
var sb = Pool.StringBuilder.Get();
var db = Database as DbBase;
// 字段列表
//if (columns == null) columns = table.Columns.ToArray();
sb.AppendFormat("Insert Into {0}(", db.FormatName(tableName));
foreach (var dc in columns)
{
// 取消对主键的过滤，避免列名和值无法一一对应的问题
//if (dc.Identity) continue;
sb.Append(db.FormatName(dc.ColumnName));
sb.Append(",");
}
sb.Length--;
sb.Append(")");
// 值列表
sb.Append(" Values");
// 优化支持DbTable
if (list.FirstOrDefault() is DbRow)
{
// 提前把列名转为索引，然后根据索引找数据
DbTable dt = null;
Int32[] ids = null;
foreach (DbRow dr in list)
{
if (dr.Table != dt)
{
dt = dr.Table;
var cs = new List <Int32>();
foreach (var dc in columns)
{
if (dc.Identity)
{
cs.Add(0);
}
else
{
cs.Add(dt.GetColumn(dc.ColumnName));
}
}
ids = cs.ToArray();
}
sb.Append("(");
var row = dt.Rows[dr.Index];
for (var i = 0; i < columns.Length; i++)
{
var dc = columns[i];
//if (dc.Identity) continue;
var value = row[ids[i]];
sb.Append(db.FormatValue(dc, value));
sb.Append(",");
}
sb.Length--;
sb.Append("),");
}
}
else
{
foreach (var entity in list)
{
sb.Append("(");
foreach (var dc in columns)
{
//if (dc.Identity) continue;
var value = entity[dc.Name];
sb.Append(db.FormatValue(dc, value));
sb.Append(",");
}
sb.Length--;
sb.Append("),");
}
}
sb.Length--;
// 重复键执行update
if ((updateColumns != null && updateColumns.Count > 0) || (addColumns != null && addColumns.Count > 0))
{
sb.Append(" On Duplicate Key Update ");
if (updateColumns != null && updateColumns.Count > 0)
{
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (updateColumns.Contains(dc.Name) && (addColumns == null || !addColumns.Contains(dc.Name)))
{
sb.AppendFormat("{0}=Values({0}),", db.FormatName(dc.ColumnName));
}
}
sb.Length--;
}
if (addColumns != null && addColumns.Count > 0)
{
sb.Append(",");
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (addColumns.Contains(dc.Name))
{
sb.AppendFormat("{0}={0}+Values({0}),", db.FormatName(dc.ColumnName));
}
}
sb.Length--;
}
}
return(sb.Put(true));
}
/*
* insert into stat (siteid,statdate,`count`,cost,createtime,updatetime) values
* (1,'2018-08-11 09:34:00',1,123,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now()),
* (3,'2018-08-11 09:34:00',1,789,now(),now()),
* (2,'2018-08-11 09:34:00',1,456,now(),now())
* on duplicate key update
* `count`=`count`+values(`count`),cost=cost+values(cost),
* updatetime=values(updatetime);
*/
private String GetBatchSql(String action, IDataTable table, IDataColumn[] columns, ICollection <String> updateColumns, ICollection <String> addColumns, IEnumerable <IExtend> list)
{
var sb = Pool.StringBuilder.Get();
var db = Database as DbBase;
// 字段列表
sb.AppendFormat("{0} {1}(", action, db.FormatName(table));
foreach (var dc in columns)
{
sb.Append(db.FormatName(dc));
sb.Append(',');
}
sb.Length--;
sb.Append(')');
// 值列表
sb.Append(" Values");
// 优化支持DbTable
if (list.FirstOrDefault() is DbRow)
{
// 提前把列名转为索引，然后根据索引找数据。外部确保数据列在数据源中都存在
DbTable dt = null;
Int32[] ids = null;
foreach (DbRow dr in list)
{
if (dr.Table != dt)
{
dt = dr.Table;
var cs = new List <Int32>();
foreach (var dc in columns)
{
var idx = dt.GetColumn(dc.Name);
if (idx < 0)
{
idx = dt.GetColumn(dc.ColumnName);
}
cs.Add(idx);
}
ids = cs.ToArray();
}
sb.Append('(');
var row = dt.Rows[dr.Index];
for (var i = 0; i < columns.Length; i++)
{
sb.Append(db.FormatValue(columns[i], row[ids[i]]));
sb.Append(',');
}
sb.Length--;
sb.Append("),");
}
}
else
{
foreach (var entity in list)
{
sb.Append('(');
foreach (var dc in columns)
{
sb.Append(db.FormatValue(dc, entity[dc.Name]));
sb.Append(',');
}
sb.Length--;
sb.Append("),");
}
}
sb.Length--;
// 重复键执行update
if ((updateColumns != null && updateColumns.Count > 0) || (addColumns != null && addColumns.Count > 0))
{
sb.Append(" On Duplicate Key Update ");
if (updateColumns != null && updateColumns.Count > 0)
{
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (updateColumns.Contains(dc.Name) && (addColumns == null || !addColumns.Contains(dc.Name)))
{
sb.AppendFormat("{0}=Values({0}),", db.FormatName(dc));
}
}
sb.Length--;
}
if (addColumns != null && addColumns.Count > 0)
{
sb.Append(',');
foreach (var dc in columns)
{
if (dc.Identity || dc.PrimaryKey)
{
continue;
}
if (addColumns.Contains(dc.Name))
{
sb.AppendFormat("{0}={0}+Values({0}),", db.FormatName(dc));
}
}
sb.Length--;
}
}
return(sb.Put(true));
}