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