C# Database.InsertAsync方法代码示例

		public async Task CreateDbAsync()
		{
			db = new Database(_connectionStringName);
			await db.ExecuteAsync(@"

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";
			await db.InsertAsync(a1);

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

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

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

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

		}

 public async Task InsertAsync(Guid jobId, Guid scriptId, string cronTab)
 {
     using (var database = new Database("ConnectionString"))
     {
         await database.InsertAsync(new ScheduledScript
         {
             Id = jobId,
             ScriptId = scriptId,
             CronTab = cronTab,
         });
     }
 }

        public async Task AddAsync(TuneInRadioStation radioStation)
        {
            var favorite = new FavoriteRadioStation
            {
                Id = radioStation.Id,
                Name = radioStation.Name,
                ImageUrl = radioStation.ImageUrl
            };

            using (var database = new Database("ConnectionString"))
            {
                await database.InsertAsync(favorite);
            }
        }

 public async Task SaveAsync(Room room)
 {
     using (var database = new Database("ConnectionString"))
     {
         if (room.Id == Guid.Empty)
         {
             room.Id = Guid.NewGuid();
             await database.InsertAsync(room);
         }
         else
         {
             await database.UpdateAsync(room);
         }
     }
 }

 public async Task SaveAsync(RoomScriptGroup group)
 {
     using (var database = new Database("ConnectionString"))
     {
         if (group.Id == Guid.Empty)
         {
             group.Id = Guid.NewGuid();
             await database.InsertAsync(group);
         }
         else
         {
             await database.UpdateAsync(group);
         }
     }
 }

        public async Task<TriggeredScript> AddTriggerAsync(Guid scriptId, string variable)
        {
            var triggeredScript = new TriggeredScript
            {
                Id = Guid.NewGuid(),
                ScriptId = scriptId,
                Variable = variable
            };

            using (var database = new Database("ConnectionString"))
            {
                await database.InsertAsync(triggeredScript);
            }

            return triggeredScript;
        }

        public async Task SaveAsync(string gatewayName, DeviceBase device)
        {
            var properties = GetProperties(device);

            var dto = new DeviceDto
            {
                Gateway = gatewayName,
                Id = $"{gatewayName}.{device.Id}",
                Name = device.Name,
                Properties = JsonConvert.SerializeObject(properties)
            };

            using (var database = new Database("ConnectionString"))
            {
                var result = await database.UpdateAsync("Device", "Id", dto, dto.Id, new[] {"Gateway", "Name", "Properties"});

                if (result == 1)
                {
                    return;
                }

                await database.InsertAsync("Device", "Id", false, dto);
            }
        }

        private async Task SaveVariables()
        {
            if (_isInMemory)
            {
                return;
            }

            using (var database = new Database("ConnectionString"))
            {
                while (_variablesToSave.Count > 0)
                {
                    var variable = _variablesToSave.Take();

                    var persistedVariable = new PersistedVariable
                    {
                        Name = variable.Name,
                        DataType = variable.Value.GetType().Name,
                        Value = variable.Value.ToString()
                    };

                    if (_persistedVariables.Contains(variable.Name))
                    {
                        await database.UpdateAsync("Variable", "Name", persistedVariable, variable.Name, new[] {"Value"});
                    }
                    else
                    {
                        await database.InsertAsync("Variable", "Name", false, persistedVariable);
                        _persistedVariables.Add(variable.Name);
                    }
                }
            }
        }

        private async Task InsertAsync(Script script)
        {
            Assert.NotNull(script);

            script.Id = Guid.NewGuid();

            using (var database = new Database("ConnectionString"))
            {
                await database.InsertAsync(script);
            }
        }

        async Task StoreHL7(Database db, XDocument xDoc, string hl7, string fileName)
        {
            var messageControlId = (from elem in xDoc.Descendants("MSH.10") select elem.Value).FirstOrDefault().ToDecimal();

            var exists = await db.ExistsAsync<HL7>("MessageControlId = @0", messageControlId);

            var record = new HL7();
            record.MessageControlId = messageControlId;
            record.MessageTimeStamp = (from elem in xDoc.Descendants("MSH.7") select elem.Value).FirstOrDefault().ToDatetime("yyyyMMddHHmmss", new DateTime(3000, 1, 1));
            record.MessageType = (from elem in xDoc.Descendants("MSH.9.1") select elem.Value).FirstOrDefault();
            record.EventType = (from elem in xDoc.Descendants("MSH.9.2") select elem.Value).FirstOrDefault();
            record.Message = hl7;
            record.FileName = fileName;

            if (record.MessageControlId == decimal.Zero || record.MessageType.IsNullOrEmpty() || record.EventType.IsNullOrEmpty())
                return;

            if (exists)
                await db.UpdateAsync("HL7v23.dbo.HL7", "MessageControlId", record);
            else
                await db.InsertAsync("HL7v23.dbo.HL7", "MessageControlId", false, record);

            if (record.MessageType == "MFN")
                await StoreMFN(db, xDoc, exists);
            else if (record.MessageType == "ADT")
                await StoreADT(db, xDoc, exists);
            else if (record.MessageType == "BAR")
                await StoreBAR(db, xDoc, exists);
            else
            {
                var message = string.Format("Need to store unsupported Message with MessageType {0}: {1}", record.MessageType, hl7);
                LogException(message, new Exception(message), null);   
            }

            await db.ExecuteAsync(@"update ZISv21.dbo.LastProcessedMessage
set MessageControlId = @0 
where [application] = @1 and MessageControlId < @0", messageControlId, "HL7v23");
        }

        async Task StoreBAR(Database db, XDocument xDoc, bool exists)
        {
            var messageControlId = (from elem in xDoc.Descendants("MSH.10") select elem.Value).FirstOrDefault();

            var record = new BAR();
            record.MessageControlId = messageControlId;
            record.PatientId = (from elem in xDoc.Descendants("PID.3") select elem.Value).FirstOrDefault();
            record.VisitNumber = (from elem in xDoc.Descendants("PV1.19.1") select elem.Value).FirstOrDefault();
            var insuranceCompanyId = (from elem in xDoc.Descendants("IN1.3") select elem.Value).FirstOrDefault();
            if (!insuranceCompanyId.IsNullOrEmpty())
            {
                if (insuranceCompanyId.Length == 7)
                {
                    record.PlanId = insuranceCompanyId.Substring(0, 1);
                    record.InsuranceCompanyNumber = insuranceCompanyId.Substring(1, 6);
                }
                else //mutualities
                {
                    record.PlanId = "2"; //for mutualities set planId to 2
                    record.InsuranceCompanyNumber = insuranceCompanyId;
                }
            }

            if (exists)
                await db.UpdateAsync("HL7v23.dbo.BAR", "MessageControlId", record);
            else
                await db.InsertAsync("HL7v23.dbo.BAR", "MessageControlId", false, record);
        }

        async Task StoreMFN(Database db, XDocument xDoc, bool exists)
        {
            var messageControlId = (from elem in xDoc.Descendants("MSH.10") select elem.Value).FirstOrDefault().ToDecimal();

            var record = new MFN();
            record.MessageControlId = messageControlId;

            record.DoctorNumber = (from elem in xDoc.Descendants("STF.1") select elem.Value).FirstOrDefault().ToDecimal();

            var insuranceCompanyId = (from elem in xDoc.Descendants("IN1.3") select elem.Value).FirstOrDefault();
            if (!insuranceCompanyId.IsNullOrEmpty())
            {
                if (insuranceCompanyId.Length == 7)
                {
                    record.PlanId = insuranceCompanyId.ToCharArray(0, 1)[0];
                    record.InsuranceCompanyNumber = insuranceCompanyId.Substring(1, 6).ToDecimal();
                }
                else //mutualities
                {
                    record.PlanId = '2'; //for mutualities set planId to 2
                    record.InsuranceCompanyNumber = insuranceCompanyId.ToDecimal();
                }
            }

            if (exists)
                await db.UpdateAsync("HL7v23.dbo.MFN", "MessageControlId", record);
            else
                await db.InsertAsync("HL7v23.dbo.MFN", "MessageControlId", false, record);
        }

        async Task StoreADT(Database db, XDocument xDoc, bool exists)
        {
            var messageControlId = (from elem in xDoc.Descendants("MSH.10") select elem.Value).FirstOrDefault().ToDecimal();

            var record = new ADT();
            record.MessageControlId = messageControlId;
            record.EventTimeStamp = (from elem in xDoc.Descendants("EVN.6") select elem.Value).FirstOrDefault().ToDatetime("yyyyMMddHHmm",
                (from elem in xDoc.Descendants("PV1.44") select elem.Value).FirstOrDefault().ToDatetime("yyyyMMddHHmm",
                    new DateTime(3000, 1, 1)));
            record.PatientId = (from elem in xDoc.Descendants("PID.3") select elem.Value).FirstOrDefault().ToDecimal();
            record.VisitNumber = (from elem in xDoc.Descendants("PV1.19.1") select elem.Value).FirstOrDefault().ToDecimal();
            record.PreadmitNumber = (from elem in xDoc.Descendants("PV1.5") select elem.Value).FirstOrDefault();
            record.AdmissionUnitNumber = (from elem in xDoc.Descendants("PV1.10") select elem.Value).FirstOrDefault().ToDecimal();
            record.CampusCode = (from elem in xDoc.Descendants("PV1.3.4") select elem.Value).FirstOrDefault();
            record.NursingUnitNumber = (from elem in xDoc.Descendants("PV1.3.1") select elem.Value).FirstOrDefault().ToDecimal();
            record.RoomNumber = (from elem in xDoc.Descendants("PV1.3.2") select elem.Value).FirstOrDefault().ToDecimal();
            record.BedNumber = (from elem in xDoc.Descendants("PV1.3.3") select elem.Value).FirstOrDefault().Maybe(s => s.PadLeft(2, '0'));
            record.DoctorNumber = (from elem in xDoc.Descendants("PV1.17.1") select elem.Value).FirstOrDefault().ToDecimal();
            record.FamilyName = (from elem in xDoc.Descendants("PID.5.1") select elem.Value).FirstOrDefault();
            record.FirstName = (from elem in xDoc.Descendants("PID.5.2") select elem.Value).FirstOrDefault();
            record.PreviousCampusCode = (from elem in xDoc.Descendants("PV1.6.4") select elem.Value).FirstOrDefault();
            record.PreviousNursingUnitNumber = (from elem in xDoc.Descendants("PV1.6.1") select elem.Value).FirstOrDefault().ToDecimal();
            record.PreviousRoomNumber = (from elem in xDoc.Descendants("PV1.6.2") select elem.Value).FirstOrDefault().ToDecimal();
            record.PreviousBedNumber = (from elem in xDoc.Descendants("PV1.6.3") select elem.Value).FirstOrDefault().Maybe(s => s.PadLeft(2, '0'));

            if (exists)
                await db.UpdateAsync("HL7v23.dbo.ADT", "MessageControlId", record);
            else
                await db.InsertAsync("HL7v23.dbo.ADT", "MessageControlId", false, record);
        }