C# Database.Fetch方法代码示例

        public static string UpdatePropertyTypesAndGroupsDo(Database database)
        {
            if (database != null)
            {
                //Fetch all PropertyTypes that belongs to a PropertyTypeGroup
                var propertyTypes = database.Fetch<PropertyTypeDto>("WHERE propertyTypeGroupId > 0");
                var propertyGroups = database.Fetch<PropertyTypeGroupDto>("WHERE id > 0");

                foreach (var propertyType in propertyTypes)
                {
                    //Get the PropertyTypeGroup that the current PropertyType references
                    var parentPropertyTypeGroup = propertyGroups.FirstOrDefault(x => x.Id == propertyType.PropertyTypeGroupId);
                    if (parentPropertyTypeGroup != null)
                    {
                        //If the ContentType is the same on the PropertyType and the PropertyTypeGroup the group is valid and we skip to the next
                        if (parentPropertyTypeGroup.ContentTypeNodeId == propertyType.ContentTypeId) continue;

                        //Check if the 'new' PropertyTypeGroup has already been created
                        var existingPropertyTypeGroup =
                            propertyGroups.FirstOrDefault(
                                x =>
                                x.ParentGroupId == parentPropertyTypeGroup.Id && x.Text == parentPropertyTypeGroup.Text &&
                                x.ContentTypeNodeId == propertyType.ContentTypeId);

                        //This should ensure that we don't create duplicate groups for a single ContentType
                        if (existingPropertyTypeGroup == null)
                        {

                            //Create a new PropertyTypeGroup that references the parent group that the PropertyType was referencing pre-6.0.1
                            var propertyGroup = new PropertyTypeGroupDto
                                                    {
                                                        ContentTypeNodeId = propertyType.ContentTypeId,
                                                        ParentGroupId = parentPropertyTypeGroup.Id,
                                                        Text = parentPropertyTypeGroup.Text,
                                                        SortOrder = parentPropertyTypeGroup.SortOrder
                                                    };

                            //Save the PropertyTypeGroup in the database and update the list of groups with this new group
                            int id = Convert.ToInt16(database.Insert(propertyGroup));
                            propertyGroup.Id = id;
                            propertyGroups.Add(propertyGroup);
                            //Update the reference to the new PropertyTypeGroup on the current PropertyType
                            propertyType.PropertyTypeGroupId = id;
                            database.Update(propertyType);
                        }
                        else
                        {
                            //Update the reference to the existing PropertyTypeGroup on the current PropertyType
                            propertyType.PropertyTypeGroupId = existingPropertyTypeGroup.Id;
                            database.Update(propertyType);
                        }
                    }
                }
            }

            return string.Empty;
        }

        public void UpdateFromQuery()
        {
            _testName = MethodInfo.GetCurrentMethod().Name.GetHashCode().ToString();
            Cleanup();

            var objs = TestResourceFactory.GetMockClassAObjects(100).ToList();
            objs.OfType<MockClassC>().ToList().ForEach(o => o.ReferenceCode = null);

            var first = objs.FirstOrDefault() as MockClassC;
            var refCode =  "R " + new Random().Next();

            var select = new ScalarSelectExpression(
                new string[] { "Location.Y" },
                new CompareToken("Id", CompareEnum.Equals, 1),
                new CompareToken("Location.X", CompareEnum.Equals, first.Location.X),
                new CompareToken("Name", CompareEnum.Like, first.Name.Substring(1, first.Name.Length - 2)));

            using (var db = new Database<int, MockClassA>(_testName + ".database", "Id"))
            {
                var eval = new ExpressionResolver<int, MockClassA>(db);

                db.Load();

                using (var t = db.BeginTransaction())
                {
                    objs.ToList().ForEach(o => o.Id = db.Add(o));

                    t.Commit();
                }

                using (var t = db.BeginTransaction())
                {
                    var count = eval.ExecuteUpdate(
                        new UpdateExpression(
                            typeof(MockClassC).AssemblyQualifiedName,
                            select,
                            new UpdateToken("Location.Y", 133.33, ValueEnum.Float),
                            new UpdateToken("Location.Z", 222.11, ValueEnum.Float),
                            new UpdateToken("ReferenceCode", refCode)));

                    Assert.AreEqual(1, count);

                    t.Commit();

                    var results = eval.ExecuteScaler(select);

                    Assert.AreEqual(1, results.Count);

                    Assert.AreEqual(133.33f, ((MockClassC)db.Fetch(1)).Location.Y);
                    Assert.AreEqual(222.11f, ((MockClassC)db.Fetch(1)).Location.Z);
                    Assert.AreEqual(refCode, ((MockClassC)db.Fetch(1)).ReferenceCode);
                }
            }
        }

        private void btnStartTest_Click(object sender, EventArgs e)
        {
            IDatabase db = new Database("as400");

            // Use NPoco Query
            List<CustomerTable> customers = db.Fetch<CustomerTable>();
            customers.ForEach(c => Console.WriteLine(c.FirstName + @" - " + c.LastName));

            var u = new CustomerTable
            {
                FirstName = "Giulia",
                LastName = "Carbonci",
                PhoneNumber = 555555
            };

            db.Insert(u);

            u.LastName = "Carboni";
            db.Update(u);

            // Use NPoco Stored Procedure Extension
            Console.WriteLine(@"START USING PROCEDURE EXTENSION");

            var ts = new SPCustomerSelect { Key = 2 };
            IEnumerable<CustomerTable> storedResult = db.QueryStoredProcedure<CustomerTable, SPCustomerSelect>(ts);
            storedResult.ToList().ForEach(c => Console.WriteLine(c.FirstName + @" - " + c.LastName));
            Console.WriteLine(ts.ErrorMessage);
            db.CloseSharedConnection();
        }

        public void Test_Query()
        {
            using (IDatabase db = new Database(@"server=localhost\OSPTTEST;database=EXPRESSLIFE;User ID=sa;Password=3Edcvfr4567ujmnb", DatabaseType.SqlServer2008))
            {
                var p1 = db.Single<Post>("select p.post_id as Id, p.*, ps.* from post p inner join post_status ps on p.post_id = ps.post_id where p.post_id = @0", 1016);
                Assert.AreEqual(p1.Id, 1016);
                
                var ps2 = db.Query<Post, PostStatus>("select p.post_id as Id, p.*, ps.* from post p inner join post_status ps on p.post_id = ps.post_id where p.post_id = @0", 1016);
                var p2 = ps2.SingleOrDefault();
                Assert.AreEqual(p2.Id, 1016);

                var ps3 = db.Fetch<Post, PostStatus>("select p.post_id as Id, p.*, ps.* from post p inner join post_status ps on p.post_id = ps.post_id where p.post_id = @0", 1016);
                var p3 = ps3.SingleOrDefault();
                Assert.AreEqual(p3.Id, 1016);

                Func<Post, PostStatus, Post> map = (p, ps) =>
                {
                    p.Status = ps;
                    return p;
                };

                var ps4 = db.Fetch<Post, PostStatus, Post>
                    (map,
                     "select p.post_id as Id, p.*, ps.* from post p inner join post_status ps on p.post_id = ps.post_id where p.post_id = @0", 1016);
                
                var p4 = ps3.SingleOrDefault();

                Assert.AreEqual(p4.Id, 1016);


                Func<Post, PostStatus, Post> pageMap = (p, ps) =>
                {
                    p.Status = ps;
                    return p;
                };

                var ps5 = db.Page<Post, PostStatus, Post>
                    (pageMap, 3, 3,
                     "select p.post_id as Id, p.*, ps.* from post p inner join post_status ps on p.post_id = ps.post_id where p.post_id > @0", 1016);

                var p5 = ps5.Items;

                Assert.AreEqual(p5.Count, 3);

            }
        }

        public void TcpListenerRecivesPublisher()
        {
            _testName = System.Reflection.MethodInfo.GetCurrentMethod().Name.GetHashCode().ToString();

            var random = new Random((int)(DateTime.Now.Ticks / 7335));

            var port = random.Next(8355, 10000);

            using (var fLock = new ManagedFileLock(_testName))
            {
                Cleanup();

                using (var pdb = new Database<int, MockClassA>(_testName + ".database", "Id")
                    .WithPublishing("Test", new TcpTransactionPublisher<int, MockClassA>(IPAddress.Parse("127.0.0.1"), port, 1, new TcpSettings())))
                {
                    pdb.Load();

                    using (var sdb = new Database<int, MockClassA>(_testName + ".subscriber" + ".database", "Id")
                        .WithSubscription("Test", new TcpTransactionSubscriber<int, MockClassA>(port)))
                    {
                        sdb.Load();

                        var obj = TestResourceFactory.CreateRandom();

                        using (var t = pdb.BeginTransaction())
                        {
                            obj.Id = pdb.Add(obj);

                            t.Commit();
                        }

                        var sw = new Stopwatch();
                        sw.Start();

                        while (sdb.Fetch(obj.Id) == null && sw.ElapsedMilliseconds < 2000)
                            Thread.Sleep(100);

                        Assert.IsNotNull(sdb.Fetch(obj.Id));

                        sdb.Flush();
                    }

                    pdb.Flush();
                }
            }
        }

        public void FetchAllProductsDto()
        {
            using (var db = new Database(DbConnection))
            {
                var productDtos = db.Fetch<ProductDto>("SELECT Product_Id as Id, Name FROM Product");

                Assert.AreEqual(3, productDtos.Count());
            }
        }

        public void FetchProductDtoByName()
        {
            using (var db = new Database(DbConnection))
            {
                ProductDto productDto = db.Fetch<ProductDto>("SELECT Product_Id as Id, Name FROM Product WHERE [email protected]", FirstProductName).Single();

                Assert.AreEqual(1, productDto.Id);
                Assert.AreEqual(FirstProductName, productDto.Name);
            }
        }

        protected override void SelectFetchEntities(int entityCount)
        {
            using (var database = new Database("SQLiteTest"))
            {
                using (var transaction = database.GetTransaction())
                {
                    var results = database.Fetch<Entity>(new Sql("SELECT * FROM Entity WHERE Id <= @0", entityCount));

                    transaction.Complete();
                }
            }
        }

        public static void BuildReconciles(Database db)
        {
            var reconciles = db.Fetch<int>("select TransactionId from TransactionReconciles");

            var transactions = db.Query<Transaction>().Where(x => !reconciles.Contains(x.TransactionId)).ToList();

            foreach (var transaction in transactions)
            {
                var rec = CategoriseTransaction(transaction);

                db.Insert(rec);
            }
        }

        public void DeletesFirst10FromQuery()
        {
            _testName = MethodInfo.GetCurrentMethod().Name.GetHashCode().ToString();
            Cleanup();

            var objs = TestResourceFactory.GetMockClassAObjects(100).ToList();
            var first = objs.FirstOrDefault() as MockClassC;

            var delete = new DeleteExpression(99, true,
                new CompareToken("Name", CompareEnum.GreaterOrEqual, first.Name));

            using (var db = new Database<int, MockClassA>(_testName + ".database", "Id"))
            {
                var eval = new ExpressionResolver<int, MockClassA>(db);

                db.Load();

                using (var t = db.BeginTransaction())
                {
                    objs.ToList().ForEach(o => o.Id = db.Add(o));

                    t.Commit();
                }

                using (var t = db.BeginTransaction())
                {
                    var results = eval.ExecuteDelete(delete);

                    Assert.LessOrEqual(1, results);

                    t.Commit();

                    Assert.IsNull(db.Fetch(first.Id));
                }

                Assert.IsNull(db.Fetch(first.Id));
            }
        }

        static void Main(string[] args)
        {
            var db = new Database("HA");

            var sql = new Sql<HouseData>("T");
            sql.Select(t => t.RegionName);
            var list = db.Fetch(sql);

            //var sql = new Sql<BaseDataDataModel>("T1");
            //sql.Select(t=>t.RowStatus).From();
            //sql.InnerJoin<BaseDataGroupDataModel>("T2").On((t1, t2) => t1.RowStatus == t2.RowStatus || t1.Name == t2.Name || 0 == t2.RowStatus);
            //sql.Where(t => t.RowStatus != 0 && !(t.Name == "eeee" || t.GroupCode == "tt"));
            //sql.Where(t => t.Id == 1000);
            //sql.Where("[email protected]", 100);
            //sql.OrderByDescending(t => t.Id, t => t.CreatedBy).OrderBy(t => t.GroupCode);

            //var list=db.Fetch(sql);

            //var group = new BaseDataGroupDataModel
            //{
            //    Code = "BD_Region",
            //    Name = "地区",
            //    CreatedBy = "system",
            //    CreatedOn = DateTime.Now
            //};
            //var bdList = new List<BaseDataDataModel>
            //{
            //    new BaseDataDataModel
            //    {
            //        Code = "051201",
            //        Name = "吴中区",
            //        CreatedBy = "system",
            //        CreatedOn = DateTime.Now,
            //        GroupCode = "BD_Region",
            //        Path = "/1/"
            //    },
            //    new BaseDataDataModel
            //    {
            //        Code = "051200",
            //        Name = "苏州市",
            //        CreatedBy = "system",
            //        CreatedOn = DateTime.Now,
            //        GroupCode = "BD_Region",
            //        Path = "/"
            //    }
            //};
            //db.Insert(group);
            //db.BulkInsert(bdList);
        }

        private static string UndoChangeDocumentTypePermissionDo(Database database)
        {
            var adminUserType = database.Fetch<UserTypeDto>("WHERE Id = 1").FirstOrDefault();
            
            if (adminUserType != null)
            {
                if (adminUserType.DefaultPermissions.Contains("7"))
                {
                    adminUserType.DefaultPermissions = adminUserType.DefaultPermissions.Replace("7", "");
                    database.Save(adminUserType);
                }
            }

            return string.Empty;
        }

        public void AddOrUpdateWithoutIdAddsWithIdZeroAndUpdatesWithNonZeroId()
        {
            _testName = MethodInfo.GetCurrentMethod().Name.GetHashCode().ToString();

            var objs = TestResourceFactory.GetMockClassAObjects(100).ToList();

            using (var fLock = new ManagedFileLock(_testName))
            {
                Cleanup();
                using (var db = new Database<int, MockClassA>(_testName + ".database", "Id"))
                {
                    db.Load();

                    objs.ToList().ForEach(o => o.Id = db.AddOrUpdate(o));

                    db.FlushAll();
                }

                using (var db = new Database<int, MockClassA>(_testName + ".database"))
                {
                    db.Load();

                    var last = db.Fetch(objs.Last().Id);

                    Assert.IsNotNull(last);

                    last.Name = "last";

                    db.AddOrUpdate(last);

                    db.FlushAll();
                }

                using (var db = new Database<int, MockClassA>(_testName + ".database", "Id"))
                {
                    db.Load();

                    var last = db.Fetch(objs.Last().Id);

                    Assert.IsNotNull(last);
                    Assert.AreEqual("last", last.Name);

                    Assert.IsNotNull(db.Fetch(objs.First().Id));

                    db.FlushAll();
                }
            }
        }

        public void DatabaseAddsUpdatesAndQueriesActiveTransaction()
        {
            _testName = MethodInfo.GetCurrentMethod().Name.GetHashCode().ToString();
            Cleanup();

            var objects = TestResourceFactory.GetMockClassAObjects(12);

            using (var db = new Database<int, MockClassA>(_testName + ".database", "Id"))
            {
                db.Load();

                db.BeginTransaction();

                foreach (var obj in objects)
                    db.AddOrUpdate(obj, 0);

                var update = db.Fetch(3);

                update.Name = "Updated " + update.Id;

                db.AddOrUpdate(update, update.Id);

                db.FlushAll();
            }

            using (var db = new Database<int, MockClassA>(_testName + ".database"))
            {
                db.Load();

                db.Update<MockClassA>(u => !u.Value<string>("Name").Contains("Updated"), m => m.Name = "batch " + m.Id);

                db.FlushAll();

                var old = db.Select(s => s.Value<string>("Name").Contains("Updated"));

                Assert.AreEqual(1, old.Count);
                Assert.AreEqual("Updated 3", old.Single().Name);

                var updates = db.SelectFirst(s => s.Value<string>("Name").Contains("batch"), 11);

                Assert.AreEqual(11, updates.Count);
                Assert.AreEqual(1, updates.First().Id);
                Assert.AreEqual(12, updates.Last().Id);
            }
        }

        public string Typeahead { get; set; } // the value supplied by the user - the current typeahead text

        public IEnumerable<EditorDataItem> GetEditorDataItems(int contextId) // supply option typeahead param
        {
            List<EditorDataItem> editorDataItems = new List<EditorDataItem>();

            Database database = new Database(this.ConnectionString);

            if (database != null)
            {
                string sql = Regex.Replace(this.SqlExpression, "\n|\r", " ")
                             .Replace("@contextId", "@0")
                             .Replace("@typeahead", "@1");


                editorDataItems = database.Fetch<EditorDataItem>(sql, contextId, this.Typeahead);
            }

            return editorDataItems;
        }