C# Database.Update方法代码示例

    public void Basics()
    {
        string path = Path.GetTempFileName();
        using (var database = new Database(path, "test"))
        {
            database.UnsafeBegin("test");
            database.Update("CREATE TABLE People(id INTEGER PRIMARY KEY, first_name, last_name, city)");

            database.Update("INSERT INTO People VALUES (1, 'joe', 'bob', 'houston')");
            database.Update("INSERT INTO People VALUES (2, 'fred', 'hansen', 'atlanta')");
            database.Update("INSERT INTO People VALUES (3, 'ted', 'bundy', 'houston')");
            database.UnsafeCommit("test");

            string[] header = null;
            List<string[]> rows = new List<string[]>();
            Database.HeaderCallback hc = (h) => {header = h;};
            Database.RowCallback rc = (r) => {rows.Add(r); return true;};

            database.Query("SELECT first_name, last_name FROM People WHERE city='houston'", hc, rc);

            Assert.AreEqual(2, header.Length);
            Assert.AreEqual("first_name", header[0]);
            Assert.AreEqual("last_name", header[1]);

            Assert.AreEqual(2, rows.Count);
            Assert.AreEqual(2, rows[0].Length);
            Assert.AreEqual(2, rows[1].Length);

            rows.Sort((lhs, rhs) => lhs[0].CompareTo(rhs[0]));
            Assert.AreEqual("joe", rows[0][0]);
            Assert.AreEqual("bob", rows[0][1]);
            Assert.AreEqual("ted", rows[1][0]);
            Assert.AreEqual("bundy", rows[1][1]);
        }
    }

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

        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 UpdateProductName()
        {
            using (var db = new Database(DbConnection))
            {
                db.Update<ProductDto>("SET [email protected] WHERE [email protected]", ChangedName, SecondProductName);

                var secondProduct = db.Single<ProductDto>("SELECT Product_Id as Id, Name FROM Product WHERE Product_Id = @0", 2);

                Assert.AreEqual(ChangedName, secondProduct.Name);
            }
        }

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

            var objs = TestResourceFactory.GetMockClassAObjects(100).ToList();
            var ids = new List<int>();

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

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

                    var t = db.BeginTransaction();

                    objs.ToList().ForEach(o => ids.Add(db.Add(o)));

                    var last = db.SelectLast(s => true, 1).LastOrDefault();

                    Assert.IsNotNull(last);

                    var count = db.Update(s => s.Value<string>("Name") == last.Name
                        , new System.Action<MockClassA>(a => a.Name = "last"));

                    Assert.AreEqual(1, count);

                    var selected = db.Select(s => true);

                    Assert.AreEqual(100, selected.Count);

                    count = db.DeleteFirst(s => true, 10);

                    Assert.AreEqual(10, count);

                    selected = db.Select(s => true);

                    Assert.AreEqual(90, selected.Count);

                    count = db.DeleteLast(s => true, 10);

                    Assert.AreEqual(10, count);

                    selected = db.Select(s => true);

                    Assert.AreEqual(80, selected.Count);

                    t.Rollback();

                }
            }
        }

    public void Commit()
    {
        string path = Path.GetTempFileName();
        using (var database = new Database(path, "test"))
        {
            database.UnsafeBegin("test");
            database.Update("CREATE TABLE People(id INTEGER PRIMARY KEY, first_name, last_name, city)");

            database.Update("INSERT INTO People VALUES (1, 'joe', 'bob', 'houston')");
            database.Update("INSERT INTO People VALUES (2, 'fred', 'hansen', 'atlanta')");
            database.Update("INSERT INTO People VALUES (3, 'ted', 'bundy', 'houston')");
            database.UnsafeCommit("test");

            List<string[]> rows = new List<string[]>();
            Database.RowCallback rc = (r) => {rows.Add(r); return false;};

            database.Query("SELECT first_name, last_name FROM People WHERE city='houston'", null, rc);

            Assert.AreEqual(1, rows.Count);
            Assert.AreEqual(2, rows[0].Length);
            Assert.IsTrue((rows[0][0] == "joe" && rows[0][1] == "bob") || (rows[0][0] == "ted" && rows[0][1] == "bundy"), "got " + rows[0][0] + rows[0][1]);
        }
    }

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

            for (var i = 0; i <= 25; i++)
            {
                _key.AppendChar((char)(((i + 25763) * i * Math.PI) % char.MaxValue));
                _vec.AppendChar((char)(((i + 41359) * i * Math.PI) % char.MaxValue));
            }

            var objects = new List<ResourceContainer>()
            {
                new Mocks.MockImageContainer(testRes.Luna_DIFF) { Name = "Luna_DIFF"},
                new Mocks.MockImageContainer(testRes.Luna_MAT) { Name = "Luna_MAT"},
                new Mocks.MockImageContainer(testRes.Luna_NRM) { Name = "Luna_NRM"}
            };

            using (var db = new Database<string, ResourceContainer>
                (_testName + ".database", "Name", new FileCore<string, long>(new SeedString(255), new Seed64()),
                new BinConverterString(),
                new QueryCryptoFormatter(new RC2Crypto(_vec), new BSONFormatter(), _key)))
            {
                db.Load();

                using (var t = db.BeginTransaction())
                {
                    foreach (var o in objects)
                        o.Name = db.Add(o);

                    t.Commit();
                }

                using (var t = db.BeginTransaction())
                {
                    foreach (var o in objects)
                        db.Update(o, o.Name);

                    t.Commit();
                }
            }
        }

 public void UpdateStudent(Student updatedStudent)
 {
     using (var db = new Database(_connectionName))
     {
         db.Update("Student", "ID", new { FirstName = updatedStudent.FirstName, LastName = updatedStudent.LastName, Age = updatedStudent.Age, GPA = updatedStudent.GPA }, updatedStudent.Id);
     }
 }

 public void UpdateClass(Class updatedClass)
 {
     using (var db = new Database(_connectionName))
     {
         db.Update("Class", "ID", new { Name = updatedClass.Name, Location = updatedClass.Location, TeacherName = updatedClass.TeacherName }, updatedClass.Id);
     }
 }

        public void SaveClass(Class updatedClass)
        {
            using (var db = new Database(_connectionName))
            {
                var sql = Sql.Builder.Append("DELETE FROM dbo.Student WHERE ClassId = @0", updatedClass.Id);
                db.Execute(sql);

                foreach (var student in updatedClass.Students)
                {
                    CreateStudent(student);
                }

                db.Update("Class", "ID", new { Name = updatedClass.Name, Location = updatedClass.Location, TeacherName = updatedClass.TeacherName }, updatedClass.Id);
            }
        }

        private void CreateSchema(Database db, Server server, TableDataSchema[] tables)
        {
            using (db = AMO2Tabular.TabularDatabaseAdd(server,
                DbName,
                SourceConnectionString,
                "Sql Server"))
            {
                var i = 0;
                foreach (var table in tables)
                {
                    if (i++ == 0)
                    {
                        AMO2Tabular.TableAddFirstTable(db, "Model", table.Name, table.Name);
                    }
                    else
                    {
                        AMO2Tabular.TableAdd(db, table.Name, table.Name);
                    }

                    foreach (var field in table.Fields)
                    {
                        if (!string.IsNullOrEmpty(field.FriendlyName))
                        {
                            AMO2Tabular.ColumnAlterColumnName(db, table.Name, field.Name, field.FriendlyName, false);
                        }

                        if (field is PartitionField)
                        {
                            AMO2Tabular.ColumnDrop(db, table.Name, field.Name, false);
                        }
                        else if (field.FieldType == FieldType.Fact)
                        {
                            var measureName = PostFixMeasureName(table, string.Format("Total {0}", field.Name));
                            AMO2Tabular.MeasureAdd(db, table.Name, measureName,
                                "SUM([" + GetFieldName(table, field.Name) + "])", updateInstance: false);

                            var isInteger = field.ValueType == typeof(int) || field.ValueType == typeof(long);
                            SetMeasureFormat(db, measureName,
                                isInteger ? CalculatedFieldFormat.Integer : CalculatedFieldFormat.Decimal);
                        }
                        else if (!string.IsNullOrEmpty(field.SortBy))
                        {
                            AMO2Tabular.ColumnAlterSortByColumnName(db, table.Name, GetFieldFriendlyName(table, field.Name), GetFieldFriendlyName(table, field.SortBy),
                                updateInstance: false);
                        }
                        else if (field.Hide)
                        {

                            AMO2Tabular.ColumnAlterVisibility(db, table.Name, GetFieldName(table, field.Name), false, false);
                        }
                    }

                    if (SqlUpdateUtil.GetPartitionField(table) != null)
                    {
                        AMO2Tabular.PartitionAdd(db, table.Name, GetTransientPartitionName(table), string.Format("SELECT * FROM [{0}] WHERE 1=0", table.Name), false);
                    }

                    if (table.TableType == "Date")
                    {
                        //AMO2Tabular doesn't make the field the time dimension's key. This code does that.
                        var dateField = table.Fields.First(f => f.ValueType == typeof(DateTime) && !f.Hide);
                        var dim = db.Dimensions.GetByName(table.Name);
                        dim.Type = DimensionType.Time;
                        var attr = db.Dimensions.GetByName(table.Name).Attributes.GetByName(GetFieldName(table, dateField.Name));
                        attr.Usage = AttributeUsage.Key;
                        attr.FormatString = "General Date";
                        var rowNumber =
                            dim.Attributes.Cast<DimensionAttribute>().First(a => a.Type == AttributeType.RowNumber);
                        rowNumber.Usage = AttributeUsage.Regular;
                        rowNumber.AttributeRelationships.Remove(attr.ID);

                        var rel = attr.AttributeRelationships.Add(rowNumber.ID);
                        rel.Cardinality = Cardinality.One;
                        attr.KeyColumns[0].NullProcessing = NullProcessing.Error;
                        attr.KeyUniquenessGuarantee = true;

                        ((RegularMeasureGroupDimension)db.Cubes[0].MeasureGroups[dim.ID].Dimensions[dim.ID]).Attributes
                            [attr.ID].KeyColumns[0].NullProcessing = NullProcessing.Error;
                        //attr.AttributeRelationships
                    }

                }


                foreach (var table in tables)
                {
                    foreach (var relation in table.RelatedTables)
                    {
                        if (relation.RelationType == RelationType.Dimension ||
                            relation.RelationType == RelationType.Parent)
                        {
                            AMO2Tabular.RelationshipAdd(db, relation.RelatedTable.Name,
                                relation.RelatedFields.First().Name, table.Name, relation.Fields.First().Name,
                                updateInstance: false);
                        }
                    }

                    foreach (var calculatedField in table.CalculatedFields)
                    {
                        var dax = CalculatedField.FormatDax(calculatedField.DaxPattern, table);
//.........这里部分代码省略.........

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

            var objects = TestResourceFactory.GetMockClassAObjects(12);

            ITransaction trans = null;

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

                using (var db = new Database<int, MockClassA>(_testName + ".database", "Id", new FileCore<int, long>(), new BinConverter32(), new BSONFormatter(),
                    new TransactionManager<int, MockClassA>() { DistributedScopeEnlistment = TransactionEnlistmentType.FullEnlistmentNotification }))
                {
                    db.Load();

                    using (var scope = new System.Transactions.TransactionScope(TransactionScopeOption.RequiresNew))
                    {
                        trans = db.BeginTransaction();

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

                        var update = db.Fetch(3);

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

                        db.AddOrUpdate(update, update.Id);

                        db.Delete(objects.Last().Id);

                        scope.Complete();
                    }

                    while (!trans.IsComplete)
                        Thread.Sleep(100);
                }

                using (var db = new Database<int, MockClassA>(_testName + ".database", "Id", new FileCore<int, long>(), new BinConverter32(), new BSONFormatter(),
                    new TransactionManager<int, MockClassA>() { DistributedScopeEnlistment = TransactionEnlistmentType.FullEnlistmentNotification }))
                {
                    db.Load();

                    using (var scope = new System.Transactions.TransactionScope(TransactionScopeOption.RequiresNew))
                    {
                        trans = db.BeginTransaction();

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

                        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(10, updates.Count);
                        Assert.AreEqual(1, updates.First().Id);
                        Assert.AreEqual(11, updates.Last().Id);

                        scope.Complete();
                    }

                    while (!trans.IsComplete)
                        Thread.Sleep(100);
                }
            }
        }

        /*
         * Create new database objects; for future use
         */
        private void CreateDataAccessObjects(Database objDB)
        {
            // Create a relational data source
            // by specifying the name and the id
            RelationalDataSource ds = new RelationalDataSource("MovieClick", Utils.GetSyntacticallyValidID("MovieClick", typeof(Database)));
            ds.ConnectionString = "Provider=SQLNCLI10.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Chapter 16";
            objDB.DataSources.Add(ds);

            // Create connection to datasource to extract schema to a dataset
            DataSet dset = new DataSet();
            SqlConnection cn = new SqlConnection("Data Source=localhost; Initial Catalog=Chapter 16; Integrated Security=true");

            // Create data adapters from database tables and load schemas
            SqlDataAdapter daCustomers = new SqlDataAdapter("SELECT * FROM Customers", cn);
            daCustomers.FillSchema(dset, SchemaType.Mapped, "Customers");
            SqlDataAdapter daChannels = new SqlDataAdapter("SELECT * FROM Channels", cn);
            daChannels.FillSchema(dset, SchemaType.Mapped, "Channels");

            // Add relationship between Customers and Channels
            DataRelation drCustomerChannels = new DataRelation("Customer_Channels", dset.Tables["Customers"].Columns["SurveyTakenID"], dset.Tables["Channels"].Columns["SurveyTakenID"]);
            dset.Relations.Add(drCustomerChannels);

            // Create the DSV, ad the dataset and add to the database
            DataSourceView dsv = new DataSourceView("SimpleMovieClick", "SimpleMovieClick");
            dsv.DataSourceID = "MovieClick";
            dsv.Schema = dset.Clone();
            objDB.DataSourceViews.Add(dsv);

            // Update the database to create the objects on the server
            objDB.Update(UpdateOptions.ExpandFull);
        }

        static void Main(string[] args)
        {
            Customer cust;
            Database<Customer> customerDB = new Database<Customer>();
            CustomerGenerator cg = new CustomerGenerator(@"..\..\Customers.txt");

            List<Customer> customerList = cg.GetAll();
            foreach (Customer c in customerList)
                customerDB.Add(c);

            // Adding som indexes...
            customerDB.AddIndex("CustomerIDIndex", new CustomerIdPrimaryIndex());
            customerDB.AddIndex("ZipcodeIndex", new ZipcodeSecondaryIndex());

            Console.WriteLine("Adding a customer...");
            customerDB.Add(new Customer(20001, "Bent Pedersen", "Torvet 3", 6700, "Esbjerg", 12345678));

            Console.WriteLine("Listing all customers...");
            foreach (Customer c in customerDB.GetAll())
                Console.WriteLine(c);

            var custIDIndex = (CustomerIdPrimaryIndex)customerDB.GetIndex("CustomerIDIndex");

            Console.WriteLine("\nChanging address for customer 1111..");
            try
            {
                cust = custIDIndex.Get(1111);
                cust.Address = "Stormgade 43";
                customerDB.Update(cust);
            }
            catch (KeyNotFoundException e)
            {
                Console.WriteLine("no customer with key 1111" + " " + e.Message);
            }

            Console.WriteLine(custIDIndex.Get(1111));
            Console.ReadKey();

            Console.WriteLine("Removing customer 20000...\n");
            try
            {
                customerDB.Remove(new Customer(20000));
            }
            catch (KeyNotFoundException)
            {
                Console.WriteLine("Customer not removed");
            }

            Console.WriteLine("Fetching customer with id=20000..");
            try
            {
                cust = custIDIndex.Get(20000);
                Console.WriteLine("Found customer: \n" + cust);
            }
            catch (KeyNotFoundException)
            {
                Console.WriteLine("no customer with key 20000");
            }

            ZipcodeSecondaryIndex zipcodeIndex = (ZipcodeSecondaryIndex)customerDB.GetIndex("ZipcodeIndex");

            Console.WriteLine("Fetching all customers with zipcode 6700...");
            foreach (Customer c in zipcodeIndex.Get(6700))
                Console.WriteLine(c);

            Console.Write("\nPress any key to continue...");
            Console.ReadKey();
        }

        /// <summary>
        /// Inserts or updates multiple instances of AppDependency class on the database table "config.app_dependencies";
        /// </summary>
        /// <param name="appDependencies">List of "AppDependency" class to import.</param>
        /// <returns></returns>
        public List<object> BulkImport(List<ExpandoObject> appDependencies)
        {
            if (!this.SkipValidation)
            {
                if (!this.Validated)
                {
                    this.Validate(AccessTypeEnum.ImportData, this._LoginId, this._Catalog, false);
                }

                if (!this.HasAccess)
                {
                    Log.Information("Access to import entity \"AppDependency\" was denied to the user with Login ID {LoginId}. {appDependencies}", this._LoginId, appDependencies);
                    throw new UnauthorizedException("Access is denied.");
                }
            }

            var result = new List<object>();
            int line = 0;
            try
            {
                using (Database db = new Database(ConnectionString.GetConnectionString(this._Catalog), Factory.ProviderName))
                {
                    using (ITransaction transaction = db.GetTransaction())
                    {
                        foreach (dynamic appDependency in appDependencies)
                        {
                            line++;

                            object primaryKeyValue = appDependency.app_dependency_id;

                            if (Cast.To<int>(primaryKeyValue) > 0)
                            {
                                result.Add(appDependency.app_dependency_id);
                                db.Update("config.app_dependencies", "app_dependency_id", appDependency, appDependency.app_dependency_id);
                            }
                            else
                            {
                                result.Add(db.Insert("config.app_dependencies", "app_dependency_id", appDependency));
                            }
                        }

                        transaction.Complete();
                    }

                    return result;
                }
            }
            catch (NpgsqlException ex)
            {
                string errorMessage = $"Error on line {line} ";

                if (ex.Code.StartsWith("P"))
                {
                    errorMessage += Factory.GetDbErrorResource(ex);

                    throw new DataAccessException(errorMessage, ex);
                }

                errorMessage += ex.Message;
                throw new DataAccessException(errorMessage, ex);
            }
            catch (System.Exception ex)
            {
                string errorMessage = $"Error on line {line} ";
                throw new DataAccessException(errorMessage, ex);
            }
        }