Exemplo n.º 1
0
        public void TestSingle()
        {
            var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
            {
                db.Open();
                var entity1 = db.From <Student>().Single();
                var entity2 = db.From <Student>().OrderByDescending(a => a.Id).Single();
                var entity3 = db.From <Student>().OrderBy("create_time").Single();
                var balance = db.From <Student>().Where(a => a.Id == 24).Single(s => s.Balance);
                var entity4 = db.From <Student>().Where(a => a.Id == 24).Single(s => new
                {
                    s.Id,
                    s.Balance,
                    s.BirthDay,
                    s.IsDelete
                });
                var entity5 = db.From <Student>().Where(a => a.Id == 24).Single(s => new Student
                {
                    Id       = s.Id,
                    Balance  = s.Balance,
                    BirthDay = s.BirthDay,
                    IsDelete = s.IsDelete
                });
            }
        }
Exemplo n.º 2
0
        public void TestSingle()
        {
            var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
            {
                db.Open();
                var entity1 = db.From <Student>().Single();
                var entity2 = db.From <Student>().OrderByDescending(a => a.Id).Single();
                var entity3 = db.From <Student>().OrderBy("create_time").Single();
                var balance = db.From <Student>().Where(a => a.Id == 24).Single(s => s.Balance);
                var entity4 = db.From <Student>().Where(a => a.Id == 24).Single(s => new
                {
                    s.Id,
                    s.Balance,
                    s.BirthDay,
                    s.IsDelete
                });
                var entity5 = db.From <Student>().Where(a => a.Id == 24).Single(s => new Student
                {
                    Id       = s.Id,
                    Balance  = s.Balance,
                    BirthDay = s.BirthDay,
                    IsDelete = s.IsDelete
                });
            }
        }
Exemplo n.º 3
0
        public void TestInsert()
        {
            var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
            {
                var row = 0;
                db.Open();//自动提交
                //1.更新所有字段(除自增列)
                row = db.From <Student>().Insert(new Student()
                {
                    BirthDay   = new DateTime(1996, 10, 20),
                    CreateTime = DateTime.Now,
                    IsDelete   = false,
                    Name       = "sqlcommon",
                    Score      = Grade.C,
                    Version    = Guid.NewGuid().ToString("N")
                });
                //2.更新所有字段,返回id(除自增列)
                var id = db.From <Student>().InsertReturnId(new Student()
                {
                    BirthDay   = new DateTime(1996, 10, 20),
                    CreateTime = DateTime.Now,
                    IsDelete   = false,
                    Score      = Grade.A,
                    Name       = "sqlcommon",
                    Version    = Guid.NewGuid().ToString("N")
                });

                //3.新增指定字段
                row = db.From <Student>().Insert(s => new Student
                {
                    Score = Grade.A,
                    Name  = "sqlcommon",
                });
                //4.排除字段
                row = db.From <Student>()
                      .Filter(s => new { s.BirthDay, s.Score })
                      .Insert(new Student()
                {
                    BirthDay   = new DateTime(1996, 10, 20),
                    CreateTime = DateTime.Now,
                    IsDelete   = false,
                    Name       = "sqlcommon",
                    Score      = Grade.C,
                    Version    = Guid.NewGuid().ToString("N")
                });
                //5.批量新增
                var students = new List <Student>();
                students.Add(new Student()
                {
                    Name = "tom", Balance = 50
                });
                students.Add(new Student()
                {
                    Name = "bob", Balance = 100
                });
                row = db.From <Student>().Insert(students);
            }
        }
Exemplo n.º 4
0
        public void TestPage()
        {
            var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();

            sb.DataSource = "D:\\sqlite.db3";
            var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
            {
                db.Open();

                //第一种方式
                var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList();
                var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList();

                //第二种方式
                var id = 24;
                var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany();
                var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany();

                //分组分页
                var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
                var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
            }
        }
Exemplo n.º 5
0
        public void TestSingle()
        {
            var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
            {
                db.Open();
                var entity1 = db.From <Student>().Single();
                var entity2 = db.From <Student>().OrderByDescending(a => a.Id).Single();
                var entity3 = db.From <Student>().OrderBy("create_time").Single();
                var balance = db.From <Student>().Where(a => a.Id == 24).Single(s => s.Balance);
                var entity4 = db.From <Student>().Where(a => a.Id == 24).Single(s => new
                {
                    s.Id,
                    s.Balance,
                    s.BirthDay,
                    s.IsDelete
                });
                var entity5 = db.From <Student>().Where(a => a.Id == 24).Single(s => new Student
                {
                    Id       = s.Id,
                    Balance  = s.Balance,
                    BirthDay = s.BirthDay,
                    IsDelete = s.IsDelete
                });
            }
        }
Exemplo n.º 6
0
        public void TestSingle()
        {
            var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();

            sb.DataSource = "D:\\sqlite.db3";
            var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
            {
                db.Open();
                var entity1 = db.From <Student>().Single();
                var entity2 = db.From <Student>().OrderByDescending(a => a.Id).Single();
                var entity3 = db.From <Student>().OrderBy("create_time").Single();
                var balance = db.From <Student>().Where(a => a.Id == 24).Single(s => s.Balance);
                var entity4 = db.From <Student>().Where(a => a.Id == 24).Single(s => new
                {
                    s.Id,
                    s.Balance,
                    s.BirthDay,
                    s.IsDelete
                });
                var entity5 = db.From <Student>().Where(a => a.Id == 24).Single(s => new Student
                {
                    Id       = s.Id,
                    Balance  = s.Balance,
                    BirthDay = s.BirthDay,
                    IsDelete = s.IsDelete
                });
            }
        }
Exemplo n.º 7
0
        public void TestInsert()
        {
            var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
            {
                var row = 0;
                db.Open(); //自动提交
                           //1.更新所有字段(除自增列)
                row = db.From <Student>().Insert(new Student()
                {
                    BirthDay   = new DateTime(1996, 10, 20),
                    CreateTime = DateTime.Now,
                    IsDelete   = false,
                    Name       = "sqlcommon",
                    Score      = Grade.C,
                    Version    = Guid.NewGuid().ToString("N")
                });
                //2.更新所有字段,返回id(除自增列)
                var id = db.From <Student>().InsertReturnId(new Student()
                {
                    BirthDay   = new DateTime(1996, 10, 20),
                    CreateTime = DateTime.Now,
                    IsDelete   = false,
                    Score      = Grade.A,
                    Name       = "sqlcommon",
                    Version    = Guid.NewGuid().ToString("N")
                });
                //3.新增指定字段
                row = db.From <Student>().Insert(s => new Student
                {
                    Score = Grade.A,
                    Name  = "sqlcommon",
                });
                //4.排除字段
                row = db.From <Student>()
                      .Filter(s => new { s.BirthDay, s.Score })
                      .Insert(new Student()
                {
                    BirthDay   = new DateTime(1996, 10, 20),
                    CreateTime = DateTime.Now,
                    IsDelete   = false,
                    Name       = "sqlcommon",
                    Score      = Grade.C,
                    Version    = Guid.NewGuid().ToString("N")
                });
                //5.批量新增
                var students = new List <Student>();
                students.Add(new Student()
                {
                    Name = "tom", Balance = 50
                });
                students.Add(new Student()
                {
                    Name = "bob", Balance = 100
                });
                row = db.From <Student>().Insert(students);
            }
        }
Exemplo n.º 8
0
        public void TestDelete()
        {
            var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
            {
                db.Open();
                var row1 = db.From <Student>().Where(a => a.Id == 23).Delete();
                var row2 = db.From <Student>().Where(a => Operator.In(a.Id, new int[] { 1, 2, 3 })).Delete();
            }
        }
Exemplo n.º 9
0
        public void TestDelete()
        {
            var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
            {
                db.Open();
                var row1 = db.From <Student>().Where(a => a.Id == 23).Delete();
                var row2 = db.From <Student>().Where(a => Operator.In(a.Id, new int[] { 1, 2, 30 })).Delete();
            }
        }
Exemplo n.º 10
0
        public void TestDelete()
        {
            var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
            {
                db.Open();
                var row1 = db.From <Student>().Where(a => a.Id == 23).Delete();
                var row2 = db.From <Student>().Where(a => Operator.In(a.Id, new int[] { 1, 2, 3 })).Delete();
            }
        }
Exemplo n.º 11
0
        public void TestDelete()
        {
            var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();

            sb.DataSource = "D:\\sqlite.db3";
            var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
            {
                db.Open();
                var row1 = db.From <Student>().Where(a => a.Id == 23).Delete();
                var row2 = db.From <Student>().Where(a => Operator.In(a.Id, new int[] { 1, 2, 3 })).Delete();
            }
        }
Exemplo n.º 12
0
        public void TestJoin()
        {
            var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();

            sb.DataSource = "D:\\sqlite.db3";
            var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
            {
                db.Open();
                var list0 = db.From <Student, Stuclass>()
                            .Join((a, b) => a.Id == b.Sid)
                            .OrderBy((a, b) => a.Id)
                            .OrderByDescending((a, b) => b.Id)
                            .Select((a, b) => new
                {
                    a.Id,
                    a.Name,
                    Class = b.Name
                }).ToList();
                var(list1, total1) = db.From <Student, Stuclass>()
                                     .Join((a, b) => a.Id == b.Sid)
                                     .Page(1, 2)
                                     .SelectMany((a, b) => new
                {
                    a.Id,
                    a.Name,
                    Class = b.Name
                });
                var(list2, total2) = db.From <Student, Stuclass>()
                                     .Join((a, b) => a.Id == b.Sid)
                                     .GroupBy((a, b) => b.Name)
                                     .Page(1, 2)
                                     .SelectMany((a, b) => new
                {
                    Class    = b.Name,
                    StuNames = SqlFun.GROUP_CONCAT(a.Name)
                });
                var list3 = db.From <Student, Stuclass, Stuid>()
                            .Join((Student a, Stuclass b) => a.Id == b.Sid)
                            .Join((Student a, Stuid b) => a.Id == b.Sid)
                            .Select((a, b, c) => new
                {
                    a.Id,
                    b.Name,
                    c.IdNum
                }).ToList();
            }
        }
Exemplo n.º 13
0
        public void TestJoin()
        {
            var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
            {
                db.Open();
                var list0 = db.From <Student, Stuclass>()
                            .Join((a, b) => a.Id == b.Sid)
                            .OrderBy((a, b) => a.Id)
                            .OrderByDescending((a, b) => b.Id)
                            .Select((a, b) => new
                {
                    a.Id,
                    a.Name,
                    Class = b.Name
                }).ToList();
                var(list1, total1) = db.From <Student, Stuclass>()
                                     .Join((a, b) => a.Id == b.Sid)
                                     .Page(2, 2)
                                     .SelectMany((a, b) => new
                {
                    a.Id,
                    a.Name,
                    Class = b.Name
                });
                var(list2, total2) = db.From <Student, Stuclass>()
                                     .Join((a, b) => a.Id == b.Sid)
                                     .GroupBy((a, b) => b.Name)
                                     .OrderBy((a, b) => b.Name)
                                     .Page(1, 2)
                                     .SelectMany((a, b) => new
                {
                    Class = b.Name,
                    // StuNames = SqlFun.GROUP_CONCAT(a.Name)
                });
                var list3 = db.From <Student, Stuclass, Stuid>()
                            .Join((Student a, Stuclass b) => a.Id == b.Sid)
                            .Join((Student a, Stuid b) => a.Id == b.Sid)
                            .Select((a, b, c) => new
                {
                    a.Id,
                    b.Name,
                    c.IdNum
                }).ToList();
            }
        }
Exemplo n.º 14
0
        public void TestJoin()
        {
            var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
            {
                db.Open();
                var list0 = db.From <Student, Stuclass>()
                            .Join((a, b) => a.Id == b.Sid)
                            .OrderBy((a, b) => a.Id)
                            .OrderByDescending((a, b) => b.Id)
                            .Select((a, b) => new
                {
                    a.Id,
                    a.Name,
                    Class = b.Name
                }).ToList();
                var(list1, total1) = db.From <Student, Stuclass>()
                                     .Join((a, b) => a.Id == b.Sid)
                                     .Page(1, 2)
                                     .SelectMany((a, b) => new
                {
                    a.Id,
                    a.Name,
                    Class = b.Name
                });
                var(list2, total2) = db.From <Student, Stuclass>()
                                     .Join((a, b) => a.Id == b.Sid)
                                     .GroupBy((a, b) => b.Name)
                                     .Page(1, 2)
                                     .SelectMany((a, b) => new
                {
                    Class = b.Name,
                    Count = SqlFun.COUNT(1L),
                    //StuNames = SqlFun.GROUP_CONCAT(a.Name)
                });
                var list3 = db.From <Student, Stuclass, Stuid>()
                            .Join((Student a, Stuclass b) => a.Id == b.Sid)
                            .Join((Student a, Stuid b) => a.Id == b.Sid)
                            .Select((a, b, c) => new
                {
                    a.Id,
                    b.Name,
                    c.IdNum
                }).ToList();
            }
        }
Exemplo n.º 15
0
        public void TestJoin()
        {
            var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
            {
                db.Open();
                var list0 = db.From <Student, Stuclass>()
                            .Join((a, b) => a.Id == b.Sid)
                            .OrderBy((a, b) => a.Id)
                            .OrderByDescending((a, b) => b.Id)
                            .Select((a, b) => new
                {
                    a.Id,
                    a.Name,
                    Class = b.Name
                }).ToList();
                var(list1, total1) = db.From <Student, Stuclass>()
                                     .Join((a, b) => a.Id == b.Sid)
                                     .Page(1, 2)
                                     .SelectMany((a, b) => new
                {
                    a.Id,
                    a.Name,
                    Class = b.Name
                });
                var(list2, total2) = db.From <Student, Stuclass>()
                                     .Join((a, b) => a.Id == b.Sid)
                                     .GroupBy((a, b) => b.Name)
                                     .Page(1, 2)
                                     .SelectMany((a, b) => new
                {
                    Class    = b.Name,
                    StuNames = SqlFun.GROUP_CONCAT(a.Name)
                });
                var list3 = db.From <Student, Stuclass, Stuid>()
                            .Join((Student a, Stuclass b) => a.Id == b.Sid)
                            .Join((Student a, Stuid b) => a.Id == b.Sid)
                            .Select((a, b, c) => new
                {
                    a.Id,
                    b.Name,
                    c.IdNum
                }).ToList();
            }
        }
Exemplo n.º 16
0
        public void TestSelect()
        {
            var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
            {
                db.Open();
                var list0 = db.From <Student>().Select(s => SqlFun.CONCAT(s.Name, s.Score)).ToList();
                var list1 = db.From <Student>().Select().ToList();
                var list2 = db.From <Student>().Where(a => a.IsDelete == false).Select().ToList();
                var list3 = db.From <Student>().OrderBy(a => a.Id).OrderByDescending(a => a.Balance).Select().ToList();
                var list4 = db.From <Student>().Take(4).Select().ToList();
                var list5 = db.From <Student>().Take(4).Skip(2, 2).Select().ToList();
                var list6 = db.From <Student>().Select(s => new { s.IsDelete, s.Id, s.Name }).ToList();
                var list7 = db.From <Student>().Select(s => new Student {
                    IsDelete = s.IsDelete, Id = s.Id, Name = s.Name
                }).ToList();
            }
        }
Exemplo n.º 17
0
        public void TestSelect()
        {
            var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
            {
                db.Open();
                var list0 = db.From <Student>().Select(s => SqlFun.CONCAT(s.Name, s.Score)).ToList();
                var list1 = db.From <Student>().Select().ToList();
                var list2 = db.From <Student>().Where(a => a.IsDelete == false).Select().ToList();
                var list3 = db.From <Student>().OrderBy(a => a.Id).OrderByDescending(a => a.Balance).Select().ToList();
                var list4 = db.From <Student>().Take(4).Select().ToList();
                var list5 = db.From <Student>().Take(4).Skip(2, 2).Select().ToList();
                var list6 = db.From <Student>().Select(s => new { s.IsDelete, s.Id, s.Name }).ToList();
                var list7 = db.From <Student>().Select(s => new Student {
                    IsDelete = s.IsDelete, Id = s.Id, Name = s.Name
                }).ToList();
            }
        }
Exemplo n.º 18
0
        public void TestSelect()
        {
            var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
            {
                db.Open();
                var list0 = db.From <Student>().Select(s => SqlFun.CONCAT(s.Name, s.Score)).ToList();
                var list1 = db.From <Student>().Select().ToList();
                var list2 = db.From <Student>().Where(a => a.IsDelete == false).Select().ToList();
                var list3 = db.From <Student>().OrderBy(a => a.Id).OrderByDescending(a => a.Balance).Select().ToList();
                var list4 = db.From <Student>().Take(4).Select().ToList();
                var list5 = db.From <Student>().Take(4).Skip(2, 2).Select().ToList();
                var list6 = db.From <Student>().Select(s => new { s.IsDelete, s.Id, s.Name }).ToList();
                var list7 = db.From <Student>().Select(s => new Student {
                    IsDelete = s.IsDelete, Id = s.Id, Name = s.Name
                }).ToList();
            }
        }
Exemplo n.º 19
0
        public void TestGroup()
        {
            var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
            {
                db.Open();
                var list = db.From <Student>()
                           .GroupBy(a => a.Name)
                           .Having(a => SqlFun.COUNT(1L) > 1)
                           .OrderByDescending(a => SqlFun.COUNT(1))
                           .Select(s => new
                {
                    s.Name,
                    Names   = SqlFun.GROUP_CONCAT(s.Name),
                    Count   = SqlFun.COUNT(1L),
                    Balance = SqlFun.SUM(s.Balance)
                }).ToList();
            }
        }
Exemplo n.º 20
0
        public void TestGroup()
        {
            var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
            {
                db.Open();
                var list = db.From <Student>()
                           .GroupBy(a => a.Name)
                           .Having(a => SqlFun.COUNT(1L) > 1)
                           .OrderByDescending(a => SqlFun.COUNT(1))
                           .Select(s => new
                {
                    s.Name,
                    //Names = SqlFun.GROUP_CONCAT(s.Name),
                    Count   = SqlFun.COUNT(1L),
                    Balance = SqlFun.SUM(s.Balance)
                }).ToList();
            }
        }
Exemplo n.º 21
0
        public void TestGroup()
        {
            var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
            {
                db.Open();
                var list = db.From <Student>()
                           .GroupBy(a => a.Name)
                           .Having(a => SqlFun.COUNT(1L) > 1)
                           .OrderByDescending(a => SqlFun.COUNT(1))
                           .Select(s => new
                {
                    s.Name,
                    //Names = SqlFun.GROUP_CONCAT(s.Name),
                    Count   = SqlFun.COUNT(1L),
                    Balance = SqlFun.SUM(s.Balance)
                }).ToList();
            }
        }
Exemplo n.º 22
0
        public void TestPage()
        {
            var connection = new MySql.Data.MySqlClient.MySqlConnection("server=localhost;user id=root;password=1024;database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Mysql))
            {
                db.Open();

                //第一种方式
                var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList();
                var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList();

                //第二种方式
                var id = 24;
                var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany();
                var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany();

                //分组分页
                var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
                var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
            }
        }
Exemplo n.º 23
0
        public void TestPage()
        {
            var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
            {
                db.Open();

                //第一种方式
                var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList();
                var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList();

                //第二种方式,只分页不计数的可以通过skip实现
                var id = 24;
                var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany();
                var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany();

                //分组分页
                var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
                var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
            }
        }
Exemplo n.º 24
0
        public void TestPage()
        {
            var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
            {
                db.Open();

                //第一种方式
                var list1 = db.From <Student>().Page(1, 2, out long total1).Select().ToList();
                var list2 = db.From <Student>().Page(2, 2, out long total2).Select().ToList();

                //第二种方式
                var id = 24;
                var(list3, total3) = db.From <Student>().Where(a => a.Id > id).Page(1, 2).SelectMany();
                var(list4, total4) = db.From <Student>().Where(a => a.Id > id).Page(2, 2).SelectMany();

                //分组分页
                var(glist1, gtotal1) = db.From <Student>().GroupBy(a => a.Name).Page(1, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
                var(glist2, gtotal2) = db.From <Student>().GroupBy(a => a.Name).Page(2, 2).SelectMany(s => new { s.Name, Count = SqlFun.COUNT(1L) });
            }
        }
Exemplo n.º 25
0
        public void TestGroup()
        {
            var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();

            sb.DataSource = "D:\\sqlite.db3";
            var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
            {
                db.Open();
                var list = db.From <Student>()
                           .GroupBy(a => a.Name)
                           .Having(a => SqlFun.COUNT(1L) > 1)
                           .OrderByDescending(a => SqlFun.COUNT(1))
                           .Select(s => new
                {
                    s.Name,
                    Names   = SqlFun.GROUP_CONCAT(s.Name),
                    Count   = SqlFun.COUNT(1L),
                    Balance = SqlFun.SUM(s.Balance)
                }).ToList();
            }
        }
Exemplo n.º 26
0
        public void TestSelect()
        {
            var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();

            sb.DataSource = "D:\\sqlite.db3";
            var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
            {
                db.Open();
                var fun = new SqliteConcat <Student>();
                fun.Concat(a => new { a.Name, a.Id });
                var list0 = db.From <Student>().Select(s => (string)fun).ToList();
                var list1 = db.From <Student>().Select().ToList();
                var list2 = db.From <Student>().Where(a => a.IsDelete == false).Select().ToList();
                var list3 = db.From <Student>().OrderBy(a => a.Id).OrderByDescending(a => a.Balance).Select().ToList();
                var list4 = db.From <Student>().Take(4).Select().ToList();
                var list5 = db.From <Student>().Take(4).Skip(2, 2).Select().ToList();
                var list6 = db.From <Student>().Select(s => new { s.IsDelete, s.Id, s.Name }).ToList();
                var list7 = db.From <Student>().Select(s => new Student {
                    IsDelete = s.IsDelete, Id = s.Id, Name = s.Name
                }).ToList();
            }
        }
Exemplo n.º 27
0
        public void TestUpdate()
        {
            var connection = new System.Data.SqlClient.SqlConnection(@"Data Source=DESKTOP-9IS2HA6\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=1024");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.SqlServer))
            {
                var row = 0;
                db.Open();//自动提交
                var entity1 = new Student()
                {
                    Id         = 22,
                    Balance    = 50,
                    BirthDay   = DateTime.Now,
                    Score      = Grade.E,
                    IsDelete   = true,
                    Name       = "faker",
                    Version    = "test",
                    CreateTime = DateTime.Now,
                };
                var entity2 = new Student()
                {
                    Id         = 24,
                    Balance    = 50,
                    BirthDay   = DateTime.Now,
                    Score      = Grade.E,
                    IsDelete   = true,
                    Name       = "faker",
                    Version    = "test",
                    CreateTime = DateTime.Now,
                };
                //1.根据主键字段更新所有列
                row = db.From <Student>().Update(entity1);
                //2.忽略指定列
                row = db.From <Student>().Filter(s => s.Version).Update(entity2);
                //3.根据指定条件更新所有列
                var oldVersion = "110";
                row = db.From <Student>()
                      .Where(a => a.Id == 25 && a.Version == oldVersion)
                      .Update(entity1);
                //4.批量修改,不推荐使用,
                var students = new List <Student>();
                students.Add(entity1);
                students.Add(entity2);
                row = db.From <Student>().Filter(a => a.CreateTime).Update(students);
                //5.更新指定字段
                row = db.From <Student>()
                      .Update(s => new Student()
                {
                    Id    = 26, //默认通过id更新,可以用where来重置默认
                    Score = Grade.F,
                    Name  = "hihi"
                });
                //6.动态更新字段
                var charat = "f";
                row = db.From <Student>()
                      .Set(a => a.Balance, 100, 1 > 2)                            //false
                      .Set(a => a.IsDelete, true)
                      .Set(a => a.Balance, a => a.Balance + 200, 1 < 2)           //true
                      .Set(a => a.Name, a => SqlFun.Replace(a.Name, "b", charat)) //true
                      .Where(a => a.Id == 27)
                      .Update();
            }
        }
Exemplo n.º 28
0
        public void TestUpdate()
        {
            var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();

            sb.DataSource = "D:\\sqlite.db3";
            var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
            {
                var row = 0;
                db.Open();//自动提交
                var entity1 = new Student()
                {
                    Id         = 22,
                    Balance    = 50,
                    BirthDay   = DateTime.Now,
                    Score      = Grade.E,
                    IsDelete   = true,
                    Name       = "faker",
                    Version    = "test",
                    CreateTime = DateTime.Now,
                };
                var entity2 = new Student()
                {
                    Id         = 24,
                    Balance    = 50,
                    BirthDay   = DateTime.Now,
                    Score      = Grade.E,
                    IsDelete   = true,
                    Name       = "faker",
                    Version    = "test",
                    CreateTime = DateTime.Now,
                };
                //1.根据主键字段更新所有列
                row = db.From <Student>().Update(entity1);
                //2.忽略指定列
                row = db.From <Student>().Filter(s => s.Version).Update(entity2);
                //3.根据指定条件更新所有列
                var oldVersion = "110";
                row = db.From <Student>()
                      .Where(a => a.Id == 25 && a.Version == oldVersion)
                      .Update(entity1);
                //4.批量修改,不推荐使用,
                var students = new List <Student>();
                students.Add(entity1);
                students.Add(entity2);
                row = db.From <Student>().Filter(a => a.CreateTime).Update(students);
                //5.更新指定字段
                db.From <Student>()
                .Update(s => new Student()
                {
                    Id    = 26, //默认通过id更新,可以用where来重置默认
                    Score = Grade.F,
                    Name  = "hihi"
                });
                //6.动态更新字段
                var charat = "f";
                db.From <Student>()
                .Set(a => a.Balance, 100, 1 > 2)                            //false
                .Set(a => a.IsDelete, true)
                .Set(a => a.Balance, a => a.Balance + 200, 1 < 2)           //true
                .Set(a => a.Name, a => SqlFun.Replace(a.Name, "b", charat)) //true
                .Where(a => a.Id == 27)
                .Update();
            }
        }
Exemplo n.º 29
0
        public void TestUpdate()
        {
            var connection = new Npgsql.NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=1024;Database=test;");

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Postgresql))
            {
                var row = 0;
                db.Open();//自动提交
                var entity1 = new Student()
                {
                    Id         = 22,
                    Balance    = 50,
                    BirthDay   = DateTime.Now,
                    Score      = Grade.E,
                    IsDelete   = true,
                    Name       = "faker",
                    Version    = "test",
                    CreateTime = DateTime.Now,
                };
                var entity2 = new Student()
                {
                    Id         = 24,
                    Balance    = 50,
                    BirthDay   = DateTime.Now,
                    Score      = Grade.E,
                    IsDelete   = true,
                    Name       = "faker",
                    Version    = "test",
                    CreateTime = DateTime.Now,
                };
                //1.根据主键字段更新所有列
                row = db.From <Student>().Update(entity1);
                //2.忽略指定列
                row = db.From <Student>().Filter(s => s.Version).Update(entity2);
                //3.根据指定条件更新所有列
                var oldVersion = "110";
                row = db.From <Student>()
                      .Where(a => a.Id == 25 && a.Version == oldVersion)
                      .Update(entity1);
                //4.批量修改,不推荐使用,
                var students = new List <Student>();
                students.Add(entity1);
                students.Add(entity2);
                row = db.From <Student>().Filter(a => a.CreateTime).Update(students);
                //5.更新指定字段
                db.From <Student>()
                .Update(s => new Student()
                {
                    Id    = 26, //默认通过id更新,可以用where来重置默认
                    Score = Grade.F,
                    Name  = "hihi"
                });
                //6.动态更新字段
                try
                {
                    var charat = "f";
                    db.From <Student>()
                    .Set(a => a.Balance, 100, 1 > 2)                                //false
                    .Set(a => a.IsDelete, true)
                    .Set(a => a.Balance, a => a.Balance + SqlFun.MONEY(200), 1 < 2) //true
                    .Set(a => a.Name, a => SqlFun.Replace(a.Name, "b", charat))     //true
                    .Where(a => a.Id == 27)
                    .Update();
                }
                catch (Exception e)
                {
                    throw;
                }
            }
        }
Exemplo n.º 30
0
        public void TestInsert()
        {
            var sb = new System.Data.SQLite.SQLiteConnectionStringBuilder();

            sb.DataSource = "D:\\sqlite.db3";
            var connection = new System.Data.SQLite.SQLiteConnection(sb.ConnectionString);

            using (IDbContext db = new DbProxyContext(connection, DbContextType.Sqlite))
            {
                var row = 0;
                db.Open();//自动提交
                //1.更新所有字段(除自增列)
                row = db.From <Student>().Insert(new Student()
                {
                    BirthDay   = new DateTime(1996, 10, 20),
                    CreateTime = DateTime.Now,
                    IsDelete   = false,
                    Name       = "sqlcommon",
                    Score      = Grade.C,
                    Version    = Guid.NewGuid().ToString("N")
                });
                //2.更新所有字段,返回id(除自增列)
                var id = db.From <Student>().InsertReturnId(new Student()
                {
                    BirthDay   = new DateTime(1996, 10, 20),
                    CreateTime = DateTime.Now,
                    IsDelete   = false,
                    Score      = Grade.A,
                    Name       = "sqlcommon",
                    Version    = Guid.NewGuid().ToString("N")
                });
                //3.新增指定字段
                row = db.From <Student>().Insert(s => new Student
                {
                    Score = Grade.A,
                    Name  = "sqlcommon",
                });
                //4.排除字段
                row = db.From <Student>()
                      .Filter(s => new { s.BirthDay, s.Score })
                      .Insert(new Student()
                {
                    BirthDay   = new DateTime(1996, 10, 20),
                    CreateTime = DateTime.Now,
                    IsDelete   = false,
                    Name       = "sqlcommon",
                    Score      = Grade.C,
                    Version    = Guid.NewGuid().ToString("N")
                });
                //5.批量新增
                var students = new List <Student>();
                students.Add(new Student()
                {
                    Name = "tom", Balance = 50
                });
                students.Add(new Student()
                {
                    Name = "bob", Balance = 100
                });
                row = db.From <Student>().Insert(students);
            }
        }