Beispiel #1
0
        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++;
            }
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        /*
         * 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));
        }
Beispiel #4
0
        /*
         * 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));
        }
Beispiel #5
0
        /*
         * 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));
        }