C# Database.Insert方法代码示例

		public void CreateDB()
		{
			db = new Database(_connectionStringName);
			db.Execute(@"

DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS authors;

CREATE TABLE posts (
	id				bigint AUTO_INCREMENT NOT NULL,
	title			varchar(127) NOT NULL,
	author			bigint NOT NULL,
	PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE authors (
	id				bigint AUTO_INCREMENT NOT NULL,
	name			varchar(127) NOT NULL,
	PRIMARY KEY (id)
) ENGINE=INNODB;

			");


			var a1 = new author();
			a1.name = "Bill";
			db.Insert(a1);

			var a2 = new author();
			a2.name = "Ted";
			db.Insert(a2);

			var p = new post();
			p.title = "post1";
			p.author = a1.id;
			db.Insert(p);

			p = new post();
			p.title = "post2";
			p.author = a1.id;
			db.Insert(p);

			p = new post();
			p.title = "post3";
			p.author = a2.id;
			db.Insert(p);

		}

        private void ProcessStats(IList<FeatureStatistics> stats)
        {
            try
            {
                Logger.Debug("Adding Stats to database");
                using (var database = new PetaPoco.Database(_connectionStringName))
                {
                    using (ITransaction transaction = database.GetTransaction())
                    {
                        foreach (var stat in stats)
                        {

                            foreach (var reading in stat.Readings)
                            {
                                var data = new FeatureData()
                                {
                                    Reading = reading.Name,
                                    Timestamp = DateTime.Now,
                                    Name = stat.Name,
                                    Group = stat.Group
                                };
                                data.ValueType = reading.GetType().Name;
                                SetValue(data, reading);
                                database.Insert("FeatureData", "Id", true, data);
                            }
                        }
                        transaction.Complete();
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error("Error saving stats to the database", ex);
            }
        }

 private static void IndexFiles(Database database, IEnumerable<CatalogedFile> files)
 {
     foreach (var catalogedFile in files)
     {
         Console.WriteLine("Adding {0} to catalog.", catalogedFile.Path);
         database.Insert(catalogedFile);
     }
 }

 public static void Save(Database db, Purchase purchase)
 {
     var existing = db.SingleOrDefault<Purchase>("SELECT * FROM purchases WHERE [email protected] ", purchase.UniqueId);
     if (existing != null)
         db.Update("purchases", "PurchaseId", existing);
     else
         db.Insert("purchases", "PurchaseId", purchase);
 }

        private void btnOK_Click(object sender, EventArgs e)
        {
            if (!dxValidationProvider1.Validate()) return;

            if (spnOrder.EditValue != null) {
                using (var db = new Database(this.ConnString))
                {
                    var count = db.ExecuteScalar<int>(" select count(1) from T_ERP_Procedure where OrderNo = @0 and Guid <> @1 ", Convert.ToInt32(spnOrder.EditValue), Entity==null?Guid.Empty:Entity.Guid);
                    if (count > 0) {
                        MsgBox.ShowMessage("当前序号已经存在");
                        return;
                    }
                }
            }

            if (EditMode == Business.Core.Define.EnViewEditMode.New)
            {
                var r = new UltraDbEntity.T_ERP_Procedure
                {
                    Guid = Guid.NewGuid(),
                    Creator = CurUser,
                    Updator = CurUser,
                    Remark = string.Empty,
                    Reserved1 = 0,
                    Reserved2 = string.Empty,
                    Reserved3 = false,
                    ProcedureName = txtName.Text,
                    LabourCost = 0,
                    OrderNo = Convert.ToInt32(spnOrder.EditValue),
                    BatchNo = Convert.ToInt32(spnBatch.EditValue),
                    IsUsing = chkusing.Checked
                };
                using (var db = new Database(this.ConnString))
                {
                    db.Insert(r);
                    Entity = r;
                    DialogResult = System.Windows.Forms.DialogResult.OK; Close();
                }
            }
            else if (EditMode == Business.Core.Define.EnViewEditMode.Edit)
            {

                Entity.IsUsing = chkusing.Checked;
                Entity.Updator = CurUser;
                Entity.ProcedureName = txtName.Text;
                Entity.LabourCost = 0;
                Entity.OrderNo = Convert.ToInt32(spnOrder.EditValue);
                Entity.BatchNo = Convert.ToInt32(spnBatch.EditValue);

                using (var db = new Database(this.ConnString))
                {
                    db.Update(Entity);
                    DialogResult = System.Windows.Forms.DialogResult.OK; Close();
                }
            }
        }

        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 IHttpActionResult Post([FromUri] string id, [FromBody]Entities.Storytime storytime)
        {
            var db = new PetaPoco.Database("AGSoftware");
            storytime.DateCreated = System.DateTime.Now;
            storytime.UserId = Storytime.Providers.UserHelper.GetUserId(this.User.Identity.Name);
            storytime.UsersNotified = false;
            db.Insert(storytime);

            if (storytime.StorytimeType == StorytimeType.Group)
            {
                StorytimeGroup storytimegroup = new StorytimeGroup();
                storytimegroup.StorytimeId = storytime.StorytimeId;
                storytimegroup.UserGroupId = storytime.UserGroupId;
                storytimegroup.UsersNotified = false;
                db.Insert(storytimegroup);

                return Ok(storytime.StorytimeId);
            }
            else if (storytime.StorytimeType == StorytimeType.User)
            {
                StorytimeUserList storytimeuserlist = new StorytimeUserList();

                foreach (AspNetUsers user in storytime.Users)
                {
                    storytimeuserlist.StorytimeId = storytime.StorytimeId;
                    storytimeuserlist.UserId = user.Id;
                    storytimeuserlist.UserNotified = false;
                    db.Insert(storytimeuserlist);
                }

                storytimeuserlist.StorytimeId = storytime.StorytimeId;
                storytimeuserlist.UserId = storytime.UserId;
                storytimeuserlist.UserNotified = false;
                db.Insert(storytimeuserlist);

                return Ok(storytime.StorytimeId);
            }
            else
                return BadRequest("StorytimeType is invalid");
        }

        public Guid CreateUser(string email, string displayName, string passwordHash)
        {
            var db = new Database(_config.ConnectionString, "System.Data.SqlClient");
            var userId = (int)db.Insert("MR.tUser", "UserId", true, new
            {
                Email = email,
                Passwd = passwordHash,
                DisplayName = displayName,
                PasswdLastChange = DateTime.UtcNow
            });

            return db.Single<Guid>("select AuthId from MR.tUser where UserId = @0", userId);
        }

        private static void addTagToTopic(int topicId, Tag tag)
        {
            var db = new Database("umbracoDbDSN");

            if (tag.Id == 0)
            {
                db.Insert(tag);
            }

            var topicTag = db.SingleOrDefault<TopicTag>("WHERE tagId = @0 and topicId = @1", tag.Id, topicId);
            if (topicTag == null)
            {
                topicTag = new TopicTag(tag.Id, topicId, tag.Weight);
                db.Insert(topicTag);
            }
        }

        static void Main(string[] args)
        {
            try
            {
                SendEMail("Started MDC OpenPermit Sync");
                Console.WriteLine("Sync Job Started on: " + DateTime.Now.ToString());              
                Console.WriteLine("Bringing data from Socrata");
                var permits = getPermitsToSync();

                Database db = new Database("openpermit");

                Console.WriteLine("Inserting Data into DB");
                foreach (Permit permit in permits.Item1)
                {
                    try
                    {
                        processMDCInpsections(permit, db);
                        db.Insert("Permit", "PermitNum", false, permit);
                    }
                    catch (Exception ex)
                    {
                        string errorMsg = String.Format("Error found Inserting Permit {0}: Error: '{1}' with Stack Trace: {2}",
                            permit.PermitNum, ex.Message, ex.StackTrace);
                        Console.WriteLine(errorMsg);
                        SendEMail(errorMsg);
                    }
                }

                SendEMail(String.Format("Inserted {0} new permit records.", permits.Item1.Count));

                foreach (Permit permit in permits.Item2)
                {
                    try
                    {

                        if (!checkIfExpired(permit))
                        {
                            processMDCInpsections(permit, db);
                        }
                        db.Update("Permit", "PermitNum", permit);
                    }
                    catch (Exception ex)
                    {
                        string errorMsg = String.Format("Error found Processing Permit {0}: Error: '{1}' with Stack Trace: {2}",
                            permit.PermitNum, ex.Message, ex.StackTrace);
                        Console.WriteLine(errorMsg);
                        SendEMail(errorMsg);
                    }
                    
                }

                SendEMail(String.Format("Processed {0} existing permit records.", permits.Item2.Count));

                Console.WriteLine("Populating Geography Field");
                db.Execute("UPDATE Permit SET Location=geography::Point(Latitude, Longitude, 4326) WHERE Location is NULL");
                Console.WriteLine("Sync Job Done on: " + DateTime.Now.ToString());  
                SendEMail("MDC OpenPermit Sync is done");
            }
            catch (Exception ex)
            {
                string errorMsg = String.Format("Error found running MDC OpenPermit Sync. Error: '{0}' with Stack Trace: {1}",
                    ex.Message, ex.StackTrace);
                Console.WriteLine(errorMsg);
                SendEMail(errorMsg);
            }

        }

        private static void processMDCInpsections(Permit permit, Database db)
        {
            //Cleanup DB to bring new points for permit
            Console.WriteLine("Cleaning Data from DB for Permit: " + permit.PermitNum);
            db.Execute(String.Format("DELETE FROM Inspection WHERE PermitNum = '{0}'", permit.PermitNum));
            db.Execute(String.Format("DELETE FROM PermitStatus WHERE PermitNum = '{0}'", permit.PermitNum));
                    
            Console.WriteLine("Getting Inspections for Permit: " + permit.PermitNum);
            List<Inspection> inspections = getMDCInspections(permit.PermitNum);
            DateTime? lastApprovedInspectionDate = permit.StatusDate;
            if (permit.AppliedDate != null)
            {
                PermitStatus status = new PermitStatus();
                status.PermitNum = permit.PermitNum;
                status.StatusPrevious = "APPLIED";
                status.StatusPreviousMapped = "Application Accepted";
                status.StatusPreviousDate = permit.AppliedDate;

                db.Insert("PermitStatus", "id", true, status);

                permit.StatusCurrent = status.StatusPrevious;
                permit.StatusCurrentMapped = status.StatusPreviousMapped;
                permit.StatusDate = status.StatusPreviousDate;
            }

            if (permit.IssuedDate != null)
            {
                PermitStatus status = new PermitStatus();
                status.PermitNum = permit.PermitNum;
                status.StatusPrevious = "ISSUED";
                status.StatusPreviousMapped = "Permit Issued";
                status.StatusPreviousDate = permit.IssuedDate;

                db.Insert("PermitStatus", "id", true, status);

                permit.StatusCurrent = status.StatusPrevious;
                permit.StatusCurrentMapped = status.StatusPreviousMapped;
                permit.StatusDate = status.StatusPreviousDate;
            }

            if (isPermitClosed(inspections))
            {
                PermitStatus status = new PermitStatus();
                status.PermitNum = permit.PermitNum;
                status.StatusPrevious = "CLOSED";
                status.StatusPreviousMapped = "Permit Finaled";
                status.StatusPreviousDate = lastApprovedInspectionDate;

                db.Insert("PermitStatus", "id", true, status);

                permit.StatusCurrent = status.StatusPrevious;
                permit.StatusCurrentMapped = status.StatusPreviousMapped;
                permit.StatusDate = status.StatusPreviousDate;
                permit.CompletedDate = status.StatusPreviousDate;

            }

            foreach (Inspection inspection in inspections)
            {
                db.Insert("Inspection", "Id", true, inspection);
            }
        }

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

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

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

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

                            object primaryKeyValue = workCenter.work_center_id;

                            if (Cast.To<int>(primaryKeyValue) > 0)
                            {
                                result.Add(workCenter.work_center_id);
                                db.Update("office.work_centers", "work_center_id", workCenter, workCenter.work_center_id);
                            }
                            else
                            {
                                result.Add(db.Insert("office.work_centers", "work_center_id", workCenter));
                            }
                        }

                        transaction.Complete();
                    }

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

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

                    throw new MixERPException(errorMessage, ex);
                }

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

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

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

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

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

                            object primaryKeyValue = currency.currency_code;

                            if (!string.IsNullOrWhiteSpace(currency.currency_code))
                            {
                                result.Add(currency.currency_code);
                                db.Update("core.currencies", "currency_code", currency, currency.currency_code);
                            }
                            else
                            {
                                result.Add(db.Insert("core.currencies", "currency_code", currency));
                            }
                        }

                        transaction.Complete();
                    }

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

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

                    throw new MixERPException(errorMessage, ex);
                }

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

        public static Ride add(
                        int bike_id,
                        int rider_id,
                        int route_id,
                        DateTime ride_date,
                        String notes,
                        decimal reward,
                        double distance)
        {
            Ride ride = new Ride();

            ride.bike_id = bike_id;
            ride.rider_id = rider_id;
            ride.route_id = route_id;
            
            //if no names supplied then look up from ids
            ride.bike = Bike.getBike(bike_id).name;
            ride.rider = Rider.getRider(rider_id).name;
            ride.route = Route.getRoute(route_id).name;
            ride.ride_date = ride_date;

            ride.notes = notes;
            ride.distance = distance;
            ride.reward = reward;

            ride.paid = false;

            using (Database db = new PetaPoco.Database(ModelConfig.connectionStringName("bikes")))
            {
                db.Insert(ride);
            }
            return ride;
        }

        public ActionResult SubmitLease()
        {
            // Pick up parameters
            var price = -1;
            int.TryParse(Request.Params["price"], out price);

            // Validation
            var hasError = false;
            var errorMessage = "";

            // Validate the email with MailMessage
            var courriel = "[email protected]";
            try
            {
                var message = new MailMessage { From = new MailAddress(courriel) };
            }
            catch
            {
                hasError = true;
                errorMessage += "Invalid email format" + "<br/>";
            }

            // Required fields
            if (string.IsNullOrWhiteSpace(courriel) || price == -1)
            {
                hasError = true;
                errorMessage += "All fields required" + "<br/>";
            }

            if (hasError)
            {
                return Content("no - validation failed - " + errorMessage);
            }

            // Database entry
            var db = new PetaPoco.Database("umbracoDbDSN");
            var lease = new Lease();
            // lease.Id = "";
            lease.Price = price;
            lease.DateCreated = DateTime.Now;
            lease.IpAddress = Request.ServerVariables["REMOTE_ADDR"];

            lease.StartDate = new DateTime(2014, 7, 1);
            lease.EndDate = new DateTime(2015, 7, 1);
            lease.AddressNbr = "";
            lease.Street = "";
            lease.AppartmentNbr = "";
            lease.Street = "";
            lease.PostalCode = "";
            lease.City = "Montréal";

            Borough borough = new Borough();
            borough.Id = 9;
            borough.Name = "Mercier–Hochelaga-Maisonneuve";
            lease.Borough = borough;
            lease.Details = "";
            lease.Inclusions = "";

            db.Insert(lease);

            return Content("ok");
        }