C# Database.Insert方法代码示例

        public int Update(Database db)
        {
            // Add column 'characterCount' to the database.
            try
            {
                var parms = new Dictionary<string, string>();
                parms.Add("@TableName", "users");
                parms.Add("@newColumn", "characterCount");
                db.Query("ALTER TABLE @TableName ADD @newColumn NOT NULL DEFAULT ( '0' )", parms);
            }
            catch
            { } //Ignore the database error when adding a column that was already added.

            //Insert meta row into database.
            try
            {
                var data = new Dictionary<string, string>();
                data.Add("version", "100");

                db.Insert("meta", data);
            }
            catch
            { }

            return 100;
        }

 public void CreateStudent(Student student)
 {
     using (var db = new Database(_connectionName))
     {
         db.Insert("Student", "ID", new { FirstName = student.FirstName, LastName = student.LastName, Age = student.Age, GPA = student.GPA, ClassId = student.ClassId });
     }
 }

 public void CreateClass(Class newClass)
 {
     using (var db = new Database(_connectionName))
     {
         db.Insert("Class", "ID", new { Name = newClass.Name, Location = newClass.Location, TeacherName = newClass.TeacherName });
     }
 }

        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 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 InsertNewProductAttributesBinding()
        {
            using (var db = new Database(DbConnection))
            {
                var product = new ProductDto {Name = "New Product"};

                db.Insert(product);

                Assert.AreNotEqual(0, product.Id);
            }
        }

        public void InsertNewProduct()
        {
            using (var db = new Database(DbConnection))
            {
                var product = new ProductDto {Name = "New Product"};

                db.Insert("Product", "Id", product);

                Assert.AreNotEqual(0, product.Id);
            }
        }

        protected override void InsertEntities(List<Entity> entities)
        {
            using (var database = new Database("SQLiteTest"))
            {
                using (var transaction = database.GetTransaction())
                {
                    entities.ForEach(entity => database.Insert(entity));

                    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 Construct_GivenConnectionStringAndProviderFactory_ShouldBeValid()
        {
            var factory = DB.Provider.GetFactory();
            var connectionString = DB.ConnectionString;

            using (var db = new Database(connectionString, factory))
            {
                AfterDbCreate(db);
                var key = db.Insert(_note);
                var otherNote = db.SingleOrDefault<Note>(key);

                _note.ShouldBe(otherNote);
            }
        }

        public void Can_create_and_manipulate_sql_database()
        {
            using (var config = new LocalDb("test_database"))
            {
                var database = new Database(config.OpenConnection());

                database.Execute(
                @"CREATE TABLE Person
                (
                    Id int IDENTITY PRIMARY KEY,
                    Name varchar(60) NULL,
                );");

                database.Insert(new Person { Id = 1, Name = "Khalid Abuhakmeh" });
                var result = database.Query<Person>("select * from Person");
                Assert.True(result.Any());
            }
        }

        public static int SaveTransactions(Database db, List<Transaction> transactions)
        {
            var newTransactionsLoaded = 0;
            var insertedRowIds = new List<int>();

            db.BeginTransaction();

            foreach (var t in transactions)
            {
                var txn = db.Query<Transaction>().Where(x => x.AccountNumber == t.AccountNumber && x.TransactionDate == t.TransactionDate
                                                                && !insertedRowIds.Contains(x.TransactionId)).FirstOrDefault();
                if(txn == null)
                {
                    newTransactionsLoaded++;

                    insertedRowIds.Add(Convert.ToInt32(db.Insert(t)));
                }
            }

            db.CompleteTransaction();

            return newTransactionsLoaded;
        }

        //
        // GET: /Home/
        public ActionResult Index()
        {
            Database db = new Database("TEST");
            UserInfo userinfo = new UserInfo();
            userinfo.AddDate = DateTime.Now;
            userinfo.UGuid = Guid.NewGuid();
            //userinfo.Uid = 1;
            userinfo.UName = "binlyzhuo";

            object uid = db.Insert(userinfo);

            var user = db.SingleOrDefaultById<UserInfo>(1);

            //Database dbTest = new Database("TESTDB");
            //UserInfo userinfo = new UserInfo();
            //userinfo.AddDate = DateTime.Now;
            //userinfo.UGuid = Guid.NewGuid();
            ////userinfo.Uid = 1;
            //userinfo.UName = "binlyzhuo";

            //dbTest.Save(userinfo);

            return View();
        }

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

                if (!this.HasAccess)
                {
                    Log.Information("Access to import entity \"User\" was denied to the user with Login ID {LoginId}. {users}", this._LoginId, users);
                    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 user in users)
                        {
                            line++;

                            user.audit_user_id = this._UserId;
                            user.audit_ts = System.DateTime.UtcNow;

                            object primaryKeyValue = user.user_id;

                            if (Cast.To<int>(primaryKeyValue) > 0)
                            {
                                result.Add(user.user_id);
                                db.Update("account.users", "user_id", user, user.user_id);
                            }
                            else
                            {
                                result.Add(db.Insert("account.users", "user_id", user));
                            }
                        }

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

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

                if (!this.HasAccess)
                {
                    Log.Information("Access to import entity \"RefreshToken\" was denied to the user with Login ID {LoginId}. {refreshTokens}", this._LoginId, refreshTokens);
                    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 refreshToken in refreshTokens)
                        {
                            line++;



                            object primaryKeyValue = refreshToken.refresh_token_id;

                            if (refreshToken.refresh_token_id != null)
                            {
                                result.Add(refreshToken.refresh_token_id);
                                db.Update("account.refresh_tokens", "refresh_token_id", refreshToken, refreshToken.refresh_token_id);
                            }
                            else
                            {
                                result.Add(db.Insert("account.refresh_tokens", "refresh_token_id", refreshToken));
                            }
                        }

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